| title | Working with Excel Worksheet | Syncfusion |
|---|---|
| description | In this section, you can learn about various Excel worksheet operations using Syncfusion Essential XlsIO |
| platform | document-processing |
| control | XlsIO |
| documentation | UG |
| keywords | c#, vb.net, excel, syncfusion, xlsio, read excel, extract data, data from excel, excel worksheet, data from sheet, new worksheet, add worksheet, insert new worksheet, create new worksheet, insert worksheet, create worksheet, insert worksheet, create excel sheet, delete worksheet, delete sheet in excel, remove worksheet, remove sheet in excel, move sheet from one workbook to another, copy sheet from one worksheet to another, moving between sheets in excel, moving sheet, copying between sheets in excel, copying sheets |
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.
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.
{% tabs %}
{% highlight c# tabtitle="C# [Cross-platform]" playgroundButtonLink="https://raw.githubusercontent.com/SyncfusionExamples/XlsIO-Examples/master/Worksheet%20Features/Create%20Worksheet/.NET/Create%20Worksheet/Create%20Worksheet/Program.cs,180" %}
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();
} {% endhighlight %}
{% highlight c# tabtitle="C# [Windows-specific]" %} 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"); } {% endhighlight %}
{% highlight vb.net tabtitle="VB.NET [Windows-specific]" %} 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 {% endhighlight %} {% endtabs %}
A complete working example for creating Excel worksheets in C# is present on this GitHub page.
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.
{% tabs %}
{% highlight c# tabtitle="C# [Cross-platform]" playgroundButtonLink="https://raw.githubusercontent.com/SyncfusionExamples/XlsIO-Examples/master/Worksheet%20Features/Access%20Worksheet/.NET/Access%20Worksheet/Access%20Worksheet/Program.cs,180" %}
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();
} {% endhighlight %}
{% highlight c# tabtitle="C# [Windows-specific]" %} 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"); } {% endhighlight %}
{% highlight vb.net tabtitle="VB.NET [Windows-specific]" %} 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 {% endhighlight %} {% endtabs %}
A complete working example for accessing Excel worksheets in C# is present on this GitHub page.
T>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.
{% tabs %} {% highlight c# tabtitle="C# [Cross-platform]" %} IWorkbook workbook = application.Workbooks.Open(workbookStream,ExcelParseOptions.ParseWorksheetsOnDemand); {% endhighlight %}
{% highlight c# tabtitle="C# [Windows-specific]" %} IWorkbook workbook = application.Workbooks.Open(fileName,ExcelParseOptions.ParseWorksheetsOnDemand); {% endhighlight %}
{% highlight vb.net tabtitle="VB.NET [Windows-specific]" %} Dim workbook As IWorkbook = application.Workbooks.Open(fileName, ExcelParseOptions.ParseWorksheetsOnDemand) {% endhighlight %} {% endtabs %}
The following code example illustrates how to remove a worksheet from Excel workbook.
{% tabs %}
{% highlight c# tabtitle="C# [Cross-platform]" playgroundButtonLink="https://raw.githubusercontent.com/SyncfusionExamples/XlsIO-Examples/master/Worksheet%20Features/Remove%20Worksheet/.NET/Remove%20Worksheet/Remove%20Worksheet/Program.cs,180" %}
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();
} {% endhighlight %}
{% highlight c# tabtitle="C# [Windows-specific]" %} 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"); } {% endhighlight %}
{% highlight vb.net tabtitle="VB.NET [Windows-specific]" %} 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 {% endhighlight %} {% endtabs %}
A complete working example for removing an Excel worksheet in C# is present on this GitHub page.
The following code example illustrates how to set the worksheet name.
{% tabs %}
{% highlight c# tabtitle="C# [Cross-platform]" %}
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(); } {% endhighlight %}
{% highlight c# tabtitle="C# [Windows-specific]" %} 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"); } {% endhighlight %}
{% highlight vb.net tabtitle="VB.NET [Windows-specific]" %} 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 {% endhighlight %} {% endtabs %}
A complete working example for setting an Excel worksheet name in C# is present on this GitHub page.
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.
{% tabs %}
{% highlight c# tabtitle="C# [Cross-platform]" playgroundButtonLink="https://raw.githubusercontent.com/SyncfusionExamples/XlsIO-Examples/master/Worksheet%20Features/Highlight%20Worksheet%20Tab/.NET/Highlight%20Worksheet%20Tab/Highlight%20Worksheet%20Tab/Program.cs,180" %}
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();
} {% endhighlight %}
{% highlight c# tabtitle="C# [Windows-specific]" %} 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"); } {% endhighlight %}
{% highlight vb.net tabtitle="VB.NET [Windows-specific]" %} 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 {% endhighlight %} {% endtabs %}
A complete working example to highlight an Excel worksheet tab in C# is present on this GitHub page.
Worksheet operations encompass a wide range of actions and manipulations that can be performed within an Excel worksheet to manage data.
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.
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 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.
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.