Working with Excel Worksheet 

14 Feb 202424 minutes to read

A workbook contains a collection of worksheets where the actual contents resides. It is possible to add and manipulate worksheets and IWorksheet instance represents an Excel worksheet.

Create a Worksheet 

A new worksheet can be added into the workbook through Create method of IWorksheets interface. It is also possible to specify the required number of worksheets and if not specified, XlsIO creates three worksheets by default.

The following code snippet shows how to create worksheets within a workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  //The new workbook will have 5 worksheets
  IWorkbook workbook = application.Workbooks.Create(5);
  //Creating a Sheet
  IWorksheet sheet = workbook.Worksheets.Create();
  //Creating a Sheet with name “Sample”
  IWorksheet namedSheet = workbook.Worksheets.Create("Sample");

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  //The new workbook will have 5 worksheets
  IWorkbook workbook = application.Workbooks.Create(5);
  //Creating a Sheet
  IWorksheet sheet = workbook.Worksheets.Create();
  //Creating a Sheet with name “Sample”
  IWorksheet namedSheet = workbook.Worksheets.Create("Sample");

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx

  'The new workbook will have 5 worksheets
  Dim workbook As IWorkbook = application.Workbooks.Create(5)
  'Creating a sheet
  Dim sheet As IWorksheet = workbook.Worksheets.Create()
  'Creating a Sheet with name “Sample”
  Dim namedSheet As IWorksheet = workbook.Worksheets.Create("Sample")

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for creating Excel worksheets in C# is present on this GitHub page.

Access a Worksheet 

Worksheets collection holds one or more worksheets present in a workbook. Accessing a particular worksheet can be done by the following ways.

  1. Specifying the index
  2. Specifying the sheet name.

The below codes illustrate how to access a worksheet from its worksheets collection.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);

  //Accessing via index
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing via sheet Name
  IWorksheet NamedSheet = workbook.Worksheets["Sample"];

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);

  //Accessing via index
  IWorksheet sheet = workbook.Worksheets[0];

  //Accessing via sheet Name
  IWorksheet NamedSheet = workbook.Worksheets["Sample"];

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(2)

  'Accessing via index
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Accessing via Sheet Name
  Dim NamedSheet As IWorksheet = workbook.Worksheets("Sample")

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for accessing Excel worksheets in C# is present on this GitHub page.

TIPS

If the workbook contains multiple worksheets, then the parsing of the workbook will consume time. ParseWorksheetsOnDemand of ExcelParseOptions can be used in Open method of IWorkbooks to parse the worksheet only when it is accessed. This option can be used in a scenario where workbook contains multiple worksheets but you are going to use only few worksheets among them.

IWorkbook workbook = application.Workbooks.Open(workbookStream,ExcelParseOptions.ParseWorksheetsOnDemand);
IWorkbook workbook = application.Workbooks.Open(fileName,ExcelParseOptions.ParseWorksheetsOnDemand);
Dim workbook As IWorkbook = application.Workbooks.Open(fileName, ExcelParseOptions.ParseWorksheetsOnDemand)

Remove a Worksheet

The following code snippet explains how to remove a worksheet from Excel workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);

  //Removing the sheet
  workbook.Worksheets[0].Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);

  //Removing the sheet
  workbook.Worksheets[0].Remove();

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(2)

  'Removing the sheet
  workbook.Worksheets(0).Remove()

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for removing an Excel worksheet in C# is present on this GitHub page.

Move or Copy a Worksheet

Essential XlsIO allows to move or create a copy of Excel worksheet, and insert that worksheet before or after an existing worksheet in the workbook.

Copying Worksheets

It is possible to copy a worksheet to one another workbook or within the same workbook.

The following code example illustrates how to copy a sheet with its entire contents to another workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream sourceStream = new FileStream("SourceWorkbookTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook sourceWorkbook = application.Workbooks.Open(sourceStream);
  FileStream destinationStream = new FileStream("DestinationWorkbookTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook destinationWorkbook = application.Workbooks.Open(destinationStream);

  //Copy first worksheet from the Source workbook to the destination workbook
  destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[0]);
  destinationWorkbook.ActiveSheetIndex = 1;

  //Saving the workbook as stream
  FileStream copiedStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  destinationWorkbook.SaveAs(copiedStream);
  copiedStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook sourceWorkbook = application.Workbooks.Open("SourceWorkbookTemplate.xlsx");
  IWorkbook destinationWorkbook = application.Workbooks.Open("DestinationWorkbookTemplate.xlsx");

  //Copy first worksheet from the Source workbook to the destination workbook
  destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[0]);

  destinationWorkbook.ActiveSheetIndex = 1;
  destinationWorkbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim sourceWorkbook As IWorkbook = application.Workbooks.Open("SourceWorkbookTemplate.xlsx")
  Dim destinationWorkbook As IWorkbook = application.Workbooks.Open("DestinationWorkbookTemplate.xlsx")

  'Copy first worksheet from the Source workbook to the destination workbook
  destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets(0))

  destinationWorkbook.ActiveSheetIndex = 1
  destinationWorkbook.SaveAs("Output.xlsx")
End Using

A complete working example for copying Excel worksheets in C# is present on this GitHub page.

Specific copy options can be chosen while copying a worksheet, which helps to achieve customized copying by ignoring certain formatting. For more information about copy options, please refer ExcelWorksheetCopyFlags.

Moving a Worksheet

XlsIO allows moving worksheets from one position to another by using the Move method. The following code example illustrates how a worksheet is moved.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(3);
  IWorksheet sheet = workbook.Worksheets[0];

  //Move the Sheet
  sheet.Move(1);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(3);
  IWorksheet sheet = workbook.Worksheets[0];

  //Move the Sheet
  sheet.Move(1);

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(3)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Move the sheet
  sheet.Move(1)

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for moving Excel worksheets in C# is present on this GitHub page.

Highlight Worksheet Tabs 

A particular worksheet tab can be highlighted to denote its importance. Tab color can be set through the TabColor property, as given below.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Highlighting sheet tab
  sheet.TabColor = ExcelKnownColors.Red;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Highlighting sheet tab
  sheet.TabColor = ExcelKnownColors.Red;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Highlighting sheet tab
  sheet.TabColor = ExcelKnownColors.Red

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to highlight an Excel worksheet tab in C# is present on this GitHub page.

Freeze Panes 

It is possible to freeze a portion of the sheet to keep it visible while you scroll through the rest of the sheet. The following code snippet shows how to freeze panes through the FreezePanes method of IRange.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Applying Freeze Pane to the sheet by specifying a cell
  sheet.Range["B2"].FreezePanes();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Applying Freeze Pane to the sheet by specifying a cell
  sheet.Range["B2"].FreezePanes();
  
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Applying Freeze Pane to the sheet by specifying a cell
  sheet.Range("B2").FreezePanes()

  workbook.SaveAs("Output.xlsx")
End Using

You can set first visible row and first visible column in non-frozen area, through the FirstVisibleRow and FirstVisibleColumn properties as shown below

NOTE

FirstVisibleColumn and FirstVisibleRow indexes are “zero-based”.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["B2"].FreezePanes();

  //Set first visible row in the bottom pane
  sheet.FirstVisibleRow = 2;
  //Set first visible column in the right pane
  sheet.FirstVisibleColumn = 2;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["B2"].FreezePanes();

  //Set first visible row in the bottom pane
  sheet.FirstVisibleRow = 2;
  //Set first visible column in the right pane
  sheet.FirstVisibleColumn = 2;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  sheet.Range("B2").FreezePanes()

  'Set first visible row in the bottom pane
  sheet.FirstVisibleRow = 2
  'Set first visible column in the right pane
  sheet.FirstVisibleColumn = 2

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to freeze panes in C# is present on this GitHub page.

Unfreeze Panes

It is possible to unfreeze panes in an Excel worksheet using the RemovePanes method of IWorksheet interface. Refer to the following complete code snippet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Unfreeze panes in the worksheet
  worksheet.RemovePanes();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Unfreeze panes in the worksheet
  worksheet.RemovePanes();

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Unfreeze panes in the worksheet
  worksheet.RemovePanes()

  workbook.SaveAs("Output.xlsx")
End Using

Split Panes 

The window into can be divided into different panes that scroll separately each. The following code snippets illustrates how to split the window through the HorizontalSplit and VerticalSplit properties.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //split panes
  sheet.FirstVisibleColumn = 5;
  sheet.FirstVisibleRow = 11;
  sheet.VerticalSplit = 1100;
  sheet.HorizontalSplit = 1000;
  sheet.ActivePane = 1;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //split panes
  sheet.FirstVisibleColumn = 5;
  sheet.FirstVisibleRow = 11;
  sheet.VerticalSplit = 1100;
  sheet.HorizontalSplit = 1000;
  sheet.ActivePane = 1;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Split Panes
  sheet.FirstVisibleColumn = 5
  sheet.FirstVisibleRow = 11
  sheet.VerticalSplit = 1100
  sheet.HorizontalSplit = 1000
  sheet.ActivePane = 1

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to split panes in C# is present on this GitHub page.

Page Setup Settings

Fit all rows on one page.

FitToPagesTall enables the functionality of fitting all rows on one printed page.

The following code snippet shows how to use FitToPagesTall.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the fit to page tall
  sheet.PageSetup.FitToPagesTall = 1;
  sheet.PageSetup.FitToPagesWide = 0;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the fit to page tall
  sheet.PageSetup.FitToPagesTall = 1;
  sheet.PageSetup.FitToPagesWide = 0;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  ' Sets the fit to page tall
  sheet.PageSetup.FitToPagesTall = 1
  sheet.PageSetup.FitToPagesWide = 0

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to fit all rows on one page in C# is present on this GitHub page.

Fit all columns on one page.

FitToPagesWide enables the functionality of fitting all columns on one printed page.

The following code snippet shows how to use FitToPagesWide.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the fit to page wide 
  sheet.PageSetup.FitToPagesWide = 1;
  sheet.PageSetup.FitToPagesTall = 0;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the fit to page wide
  sheet.PageSetup.FitToPagesWide = 1;
  sheet.PageSetup.FitToPagesTall = 0;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  ' Sets the fit to page wide
  sheet.PageSetup.FitToPagesWide = 1
  sheet.PageSetup.FitToPagesTall = 0

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to fit all columns on one page in C# is present on this GitHub page.

Fit the page content.

IsFitToPage enables the functionality of fitting the page content before printing.

The following code snippet shows how to use IsFitToPage.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  // True to fit the content before printing
  sheet.PageSetup.IsFitToPage = true;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  // True to fit the content before printing
  sheet.PageSetup.IsFitToPage = true;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'True to fit the content before printing
  sheet.PageSetup.IsFitToPage = true;

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to fit page content before printing in C# is present on this GitHub page.

Conditions for Enabling SummaryColumnRight.

To enable the IsSummaryColumnRight property, the page orientation must be Portrait, the FitToPagesTall property value must be 0 and the IsFitToPage property must be true.

The following code snippet shows how to use IsSummaryColumnRight.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to summary columns will appear right of the detail in outlines
  sheet.PageSetup.IsSummaryColumnRight = true;
  sheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;  
  sheet.PageSetup.FitToPagesTall = 0;
  sheet.PageSetup.IsFitToPage = true;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to summary columns will appear right of the detail in outlines
  sheet.PageSetup.IsSummaryColumnRight = true;
  sheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;  
  sheet.PageSetup.FitToPagesTall = 0;
  sheet.PageSetup.IsFitToPage = true;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next
  
  'True to summary columns will appear right of the detail in outlines
  sheet.PageSetup.IsSummaryColumnRight = true
  sheet.PageSetup.Orientation = ExcelPageOrientation.Portrait
  sheet.PageSetup.FitToPagesTall = 0
  sheet.PageSetup.IsFitToPage = true

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to enable IsSummaryColumnRight in C# is present on this GitHub page.

Conditions for Enabling SummaryRowBelow.

To enable the IsSummaryRowBelow property, the page orientation must be Portrait, the FitToPagesWide property value must be 0 and the IsFitToPage property must be true.

The following code snippet shows how to use IsSummaryRowBelow.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to summary rows will appear below detail in outlines
  sheet.PageSetup.IsSummaryRowBelow = true; 
  sheet.PageSetup.FitToPagesWide = 0; 
  sheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
  sheet.PageSetup.IsFitToPage = true;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to summary rows will appear below detail in outlines
  sheet.PageSetup.IsSummaryRowBelow = true; 
  sheet.PageSetup.FitToPagesWide = 0; 
  sheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
  sheet.PageSetup.IsFitToPage = true;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'True to summary rows will appear below detail in outlines
  sheet.PageSetup.IsSummaryRowBelow = true 
  sheet.PageSetup.FitToPagesWide = 0
  sheet.PageSetup.Orientation = ExcelPageOrientation.Portrait
  sheet.PageSetup.IsFitToPage = true

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to enable IsSummaryRowBelow in C# is present on this GitHub page.

The PrintArea functionality allows you to set the range to be printed.

The following code snippet shows how to use the PrintArea.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the range to be printed
  sheet.PageSetup.PrintArea = "A1:M20";
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the range to be printed
  sheet.PageSetup.PrintArea = "A1:M20";
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'Sets the range to be printed
  sheet.PageSetup.PrintArea = "A1:M20"

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for set the range to be printed in C# is present on this GitHub page.

The PrintGridlines functionality allows you to set the gridlines to be printed.

The following code snippet shows how to use PrintGridlines.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to cell gridlines are printed on the page
  sheet.PageSetup.PrintGridlines = true;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to cell gridlines are printed on the page
  sheet.PageSetup.PrintGridlines = true;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'True to cell gridlines are printed on the page
  sheet.PageSetup.PrintGridlines = true

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to set the gridlines to be printed in C# is present on this GitHub page.

The PrintHeadings functionality allows you to set the row and column headings to be printed.

The following code snippet shows how to use PrintHeadings.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to row and column headings are printed on page
  sheet.PageSetup.PrintHeadings = true;
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //True to row and column headings are printed on page
  sheet.PageSetup.PrintHeadings = true;
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'True to row and column headings are printed on page
  sheet.PageSetup.PrintHeadings = true

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to set the row and column headings to be printed in C# is present on this GitHub page.

The PrintTitleColumns functionality allows you to specify the columns containing cells that will be repeated on the left side of each printed page.

The following code snippet shows how to use PrintTitleColumns.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the columns to be repeated on the left side of each page
  sheet.PageSetup.PrintTitleColumns = "C1:C50";
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the columns to be repeated on the left side of each page
  sheet.PageSetup.PrintTitleColumns = "C1:C50";
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'Sets the columns to be repeated on the left side of each page
  sheet.PageSetup.PrintTitleColumns = "C1:C50"

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to set the PrintTitleColumns in C# is present on this GitHub page.

The PrintTitleRows functionality allows you to specify the rows containing cells that will be repeated on the top side of each printed page.

The following code snippet shows how to use PrintTitleRows.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the rows to be repeated at the top of each page
  sheet.PageSetup.PrintTitleRows = "A1:AX1";
    
  //Saving the workbook
  FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.Write);
  workbook.SaveAs(outputStream);

  //Dispose streams
  outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  for (int i = 1; i <= 50; i++)
  {
    for (int j = 1; j <= 50; j++)
    {
      sheet.Range[i, j].Text = sheet.Range[i, j].AddressLocal;
    }
  }

  //Sets the rows to be repeated at the top of each page
  sheet.PageSetup.PrintTitleRows = "A1:AX1";
    
  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  For i As Integer = 1 To 50
    For j As Integer = 1 To 50
      sheet.Range(i, j).Text = sheet.Range(i, j).AddressLocal
    Next
  Next

  'Sets the rows to be repeated at the top of each page
  sheet.PageSetup.PrintTitleRows = "A1:AX1";

  'Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example for how to set the PrintTitleRows in C# is present on this GitHub page.

Headers and Footers

The following code snippet illustrates how to add headers and footers for Excel document.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding values in worksheet
  worksheet.Range["A1:A600"].Text = "HelloWorld";

  //Adding text with formatting to page headers 
  worksheet.PageSetup.LeftHeader = "&KFF0000 Left Header";
  worksheet.PageSetup.CenterHeader = "&KFF0000 Center Header";
  worksheet.PageSetup.RightHeader = "&KFF0000 Right Header";

  //Adding text with formatting and image to page footers
  worksheet.PageSetup.LeftFooter = "&B &18 &K0000FF Left Footer";
  FileStream imageStream = new FileStream("Image.jpg", FileMode.Open);
  worksheet.PageSetup.CenterFooter = "&G";
  worksheet.PageSetup.CenterFooterImage = Image.FromStream(imageStream);
  worksheet.PageSetup.RightFooter = "&P &K0000FF Right Footer";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding values in worksheet
  worksheet.Range["A1:A600"].Text = "HelloWorld";

  //Adding text with formatting to page headers 
  worksheet.PageSetup.LeftHeader = "&KFF0000 Left Header";
  worksheet.PageSetup.CenterHeader = "&KFF0000 Center Header";
  worksheet.PageSetup.RightHeader = "&KFF0000 Right Header";

  //Adding text with formatting and image to page footers
  worksheet.PageSetup.LeftFooter = "&B &18 &K0000FF Left Footer";
  worksheet.PageSetup.CenterFooter = "&G";
  worksheet.PageSetup.CenterFooterImage = Image.FromFile("Image.jpg");
  worksheet.PageSetup.RightFooter = "&P &K0000FF Right Footer";

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Adding values in worksheet
  worksheet.Range("A1:A600").Text = "HelloWorld"

  'Adding text with formatting to page headers
  worksheet.PageSetup.LeftHeader = "&KFF0000 Left Header"
  worksheet.PageSetup.CenterHeader = "&KFF0000 Center Header"
  worksheet.PageSetup.RightHeader = "&KFF0000 Right Header"

  'Adding text with formatting and image to page footers
  worksheet.PageSetup.LeftFooter = "&B &18 &K0000FF Left Footer"
  worksheet.PageSetup.CenterFooter = "&G"
  worksheet.PageSetup.CenterFooterImage = Image.FromFile("Image.jpg")
  worksheet.PageSetup.RightFooter = "&P &K0000FF Right Footer"

  workbook.SaveAs("Output.xlsx")
End Using

Show or Hide Worksheet 

The following code snippet shows how to hide the worksheets using Visibility property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["A1:M20"].Text = "visibility";
  //Set visibility
  sheet.Visibility = WorksheetVisibility.Hidden;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["A1:M20"].Text = "visibility";
  //Set visibility
  sheet.Visibility = WorksheetVisibility.Hidden;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(2)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  sheet.Range("A1:M20").Text = "Visibility"
  'Set visibility
  sheet.Visibility = WorksheetVisibility.Hidden

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to show or hide an Excel worksheet in C# is present on this GitHub page.

Activate a Worksheet

A worksheet in an Excel workbook can be activated through Activate method. The following code snippet explains this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["A1:M20"].Text = "Activate";
  //Activate the sheet
  sheet.Activate();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(2);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["A1:M20"].Text = "Activate";
  //Activate the sheet
  sheet.Activate();

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(2)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  sheet.Range("A1:M20").Text = "Activate"
  'Activate the sheet
  sheet.Activate()

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to activate an Excel worksheet in C# is present on this GitHub page.

Show or Hide Worksheet Tabs 

The following code snippet shows how to hide the worksheet tabs using DisplayWorkbookTabs property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(3);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Tabs";
	
  //Hide the tab
  workbook.DisplayWorkbookTabs = false;
  //set the display tab
  workbook.DisplayedTab = 2;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(3);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Tabs";
	
  //Hide the tab
  workbook.DisplayWorkbookTabs = false;
  //set the display tab
  workbook.DisplayedTab = 2;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(3)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  sheet.Range("A1:M20").Text = "Tabs"

  'Hide the tab
  workbook.DisplayWorkbookTabs = False
  'set the display tab
  workbook.DisplayedTab = 2

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to hide Excel worksheet tab in C# is present on this GitHub page.

View Settings

Show or Hide Row and Column Headers 

Row and column headings can be displayed or hidden through IsRowColumnHeadersVisible property of IWorksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["A1:M20"].Text = "RowColumnHeader";
  sheet.IsRowColumnHeadersVisible = false;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  sheet.Range["A1:M20"].Text = "RowColumnHeader";
  sheet.IsRowColumnHeadersVisible = false;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  sheet.Range("A1:M20").Text = "RowColumnHeader"
  sheet.IsRowColumnHeadersVisible = False

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to hide row and column headers in an Excel worksheet in C# is present on this GitHub page.

Show or Hide Grid Lines 

The following code snippet shows how to hide the grid lines using IsGridLinesVisible property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Gridlines";

  //Hide grid line
  sheet.IsGridLinesVisible = false;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Gridlines";

  //Hide grid line
  sheet.IsGridLinesVisible = false;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  sheet.Range("A1:M20").Text = "GridLines"

  'Hide grid line
  sheet.IsGridLinesVisible = False

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to hide gridlines in an Excel worksheet in C# is present on this GitHub page.

Set Zoom Level

The following code snippet shows how to set the zoom level by using Zoom property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Zoom level";

  //set zoom percentage
  sheet.Zoom = 70;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Zoom level";

  //set zoom percentage
  sheet.Zoom = 70;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  sheet.Range("A1:M20").Text = "Zoom level"

  'set Zoom percentage
  sheet.Zoom = 70

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to set zoom percentage in an Excel worksheet in C# is present on this GitHub page.

Open a CSV File

A CSV file has to be opened by specifying the delimiter set in it. Specifying the delimiter can be ignored, if it is Comma(,), as Syncfusion XlsIO considers the default delimiter as Comma(,).

The delimiters used in CSV file are Comma (,), Tab (\t), SemiColon (;), Colon (:), Space ( ), Equals Sign (=) etc..

The following complete code snippet explains how to open a Tab (\t) delimited CSV file using XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("Sample.csv", FileMode.Open, FileAccess.Read);
  
  //Open the Tab delimited CSV file
  IWorkbook workbook = application.Workbooks.Open(inputStream, "\t");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  //Open the Tab delimited CSV file
  IWorkbook workbook = application.Workbooks.Open("Sample.csv", "\t");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  
  'Open the Tab delimited CSV file
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.csv", "\t")
End Using

Maximum Rows and Columns for CSV

By default, XlsIO allows only 1048576 rows and 16256 columns while loading or saving a CSV document. This limit can be increased by modifying the MaximumRowsForCsv and MaximumColumnsForCsv properties. The following code snippet explains this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  
  application.MaximumRowsForCsv = 3000000;
  application.MaximumColumnsForCsv = 20000;
  
  FileStream inputStream = new FileStream("Sample.csv", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];
  
  sheet.Range[2000000, 1].Text = "Syncfusion";
  sheet.Range[20, 18000].Text = "Syncfusion";
  
  FileStream outputStream = new FileStream("Output.csv", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(outputStream,",");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  
  application.MaximumRowsForCsv = 3000000;
  application.MaximumColumnsForCsv = 20000;
  
  IWorkbook workbook = application.Workbooks.Open("Sample.csv");
  IWorksheet sheet = workbook.Worksheets[0];
  
  sheet.Range[2000000, 1].Text = "Syncfusion";
  sheet.Range[20, 18000].Text = "Syncfusion";
  
  workbook.SaveAs("Output.csv", ",");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  
  application.MaximumRowsForCsv = 3000000
  application.MaximumColumnsForCsv = 20000
  
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.csv")
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  
  sheet.Range(2000000, 1).Text = "document"
  sheet.Range(20, 18000).Text = "Syncfusion"
  
  workbook.SaveAs("Output.csv", ",")
End Using

Save Worksheet as CSV

The following code example illustrates how to save a worksheet as CSV file.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "document";

  //Saving the sheet and workbook as streams
  FileStream sheetStream = new FileStream("Sample.csv", FileMode.Create, FileAccess.ReadWrite);
  sheet.SaveAs(sheetStream,",");
  
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  sheetStream.Dispose();
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "document";

  //Save the sheet as CSV
  sheet.SaveAs("Sample.csv", ",");

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  sheet.Range("A1:M20").Text = "document"
  
  'Save the sheet as CSV 
  sheet.SaveAs("Sample.csv", ",")

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to read and save a CSV file in C# is present on this GitHub page.

Save worksheet as text (*.txt)

Essential XlsIO allows to save worksheet as a text file. This can be done by leaving the delimiter with a space as shown in the below codes.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Text document";

  //Saving the sheet and workbook as streams
  FileStream sheetStream = new FileStream("Sample.txt", FileMode.Create, FileAccess.ReadWrite);
  sheet.SaveAs(sheetStream," ");
  
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  sheetStream.Dispose();
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Text document";

  //Save the sheet as text
  sheet.SaveAs("Sample.txt", " ");

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  sheet.Range("A1:M20").Text = "Text document"

  'Save the sheet as text
  sheet.SaveAs("Sample.txt", " ")

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to save an Excel worksheet as text file in C# is present on this GitHub page.

Save Worksheet as HTML

XlsIO provides support to convert a worksheet or entire workbook to HTML with basic formatting preserved. The supporting formats in this conversion are:

  1. Styles.
  2. Hyperlinks.
  3. Images.
  4. 2D Charts.

The following code example illustrates this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Initialize excel engine and open workbook
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];
  worksheet.Range["A1:M20"].Text = "Html Document";

  //Create stream to store HTML file.
  Stream stream = new MemoryStream();

  //Save an Excel sheet as HTML file
  worksheet.SaveAsHtml(stream);
  
  //Save a workbook as HTML file
  workbook.SaveAsHtml(stream, Syncfusion.XlsIO.Implementation.HtmlSaveOptions.Default);
  stream.Dispose();
  workbook.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  sheet.Range["A1:M20"].Text = "Html Document";

  //Save an Excel sheet as HTML file
  sheet.SaveAsHtml("Sample.html");

  //Save the workbook as HTML file
  workbook.SaveAsHtml("Sample.html", Syncfusion.XlsIO.Implementation.HtmlSaveOptions.Default);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  sheet.Range("A1:M20").Text = "Html Document"

  'Save an Excel sheet as HTML file
  sheet.SaveAsHtml("Sample.html")

  'Save a workbook as HTML file
  workbook.SaveAsHtml("Sample.html", Syncfusion.XlsIO.Implementation.HtmlSaveOptions.Default)
End Using

Save Options

XlsIO also provides options to save a worksheet with the displayed text or value in the cell to HTML file. The following code example illustrates this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  //Initialize excel engine and open workbook
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create stream to store HTML file.
  Stream stream = new MemoryStream();

  //Create the instant for SaveOptions
  HtmlSaveOptions saveOptions = new HtmlSaveOptions();
  saveOptions.TextMode = HtmlSaveOptions.GetText.DisplayText;

  //Save and Dispose
  worksheet.SaveAsHtml(stream, saveOptions);
  stream.Dispose();
  workbook.Close();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Create the instant for SaveOptions
  HtmlSaveOptions options = new HtmlSaveOptions();
  options.TextMode = HtmlSaveOptions.GetText.DisplayText;
  options.ImagePath = "../../Images/";

  //Save the sheet as HTML
  sheet.SaveAsHtml("Sample.html", options);

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Create the instant for SaveOptions
  Dim options As New HtmlSaveOptions()
  options.TextMode = HtmlSaveOptions.GetText.DisplayText
  options.ImagePath = "../../Images/"

  'Save the sheet as HTML
  sheet.SaveAsHtml("Sample.html", options)

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to save an Excel worksheet as HTML file using HtmlSaveOptions in C# is present on this GitHub page.