Working with Excel Worksheet
14 Oct 202413 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 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 example illustrates how to create worksheets within a workbook.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
#region Create
//The new workbook is created with 5 worksheets
IWorkbook workbook = application.Workbooks.Create(5);
//Creating a new sheet
IWorksheet worksheet = workbook.Worksheets.Create();
//Creating a new sheet with name “Sample”
IWorksheet namedSheet = workbook.Worksheets.Create("Sample");
#endregion
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/CreateWorksheet.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//Dispose streams
outputStream.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
Worksheets collection holds one or more worksheets present in a workbook. Accessing a particular worksheet can be done by the following ways.
- Specifying the index
- Specifying the sheet name.
The following code example illustrates how to access a worksheet from its worksheets collection.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
#region Access
//Accessing via index
IWorksheet sheet = workbook.Worksheets[0];
//Accessing via sheet name
IWorksheet NamedSheet = workbook.Worksheets["Sample"];
#endregion
//Dispose streams
inputStream.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
The following code example illustrates how to remove a worksheet from Excel workbook.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
#region Remove
//Removing the sheet
workbook.Worksheets[0].Remove();
#endregion
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/RemoveWorksheet.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//Dispose streams
outputStream.Dispose();
inputStream.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.
Set Worksheet Name
The following code example illustrates how to set the worksheet name.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set sheet name
worksheet.Name = "Sample";
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
//Dispose streams
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set sheet name
worksheet.Name = "Sample";
//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 worksheet As IWorksheet = workbook.Worksheets(0)
' Set sheet name
worksheet.Name = "Sample"
' Saving the workbook
workbook.SaveAs("Output.xlsx")
End Using
A complete working example for setting an Excel worksheet name 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.
The following code example illustrates how to highlight worksheet tabs.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
#region Highlight Worksheet Tab
//Highlighting sheet tab
sheet.TabColor = ExcelKnownColors.Green;
#endregion
#region Save
//Saving the workbook
FileStream outputStream = new FileStream(Path.GetFullPath("Output/HighlightSheetTab.xlsx"), FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);
#endregion
//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];
//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.
Worksheet Operations
Worksheet operations encompass a wide range of actions and manipulations that can be performed within an Excel worksheet to manage data.
Move or Copy
Moving or copying in Excel refers to the actions of relocating or duplicating cells, rows, columns, or entire sheets within the same workbook or to another workbook.
With the Syncfusion Excel Library, you can move or copy cells, rows, columns, or entire worksheets within an Excel workbook using C#. Click here for more details.
Freeze Panes
Freezing panes in Excel allows you to lock specific rows or columns so that they remain visible while scrolling through the rest of the worksheet.
With the Syncfusion Excel Library, you can freeze panes in an Excel worksheets using C#. Click here for more details.
Page Setup Options
Page setup options in Excel include settings related to printing, such as adjusting margins, setting paper size and orientation, adding headers and footers, and scaling the worksheet to fit on a specified number of pages.
With the Syncfusion Excel Library, you can manage page setup options in an Excel worksheets using C#. Click here for more details.
Show or Hide
Showing or hiding in Excel refers to making rows, columns, or specific elements visible or invisible within the worksheet.
With the Syncfusion Excel Library, you can show or hide rows, columns, or specific elements within an Excel worksheets using C#. Click here for more details.