Worksheet (19)
This section will demonstrate a nice solution to use C#, VB.NET to move worksheet within workbook. Within this solution, moving worksheet in C#, VB.NET is as easy as ABC. Apply Spire.Xls for .NET in your application, and you can easily move worksheet within Excel using C#, VB.NET.
Spire.XLS for .NET is a professional Excel component for .NET which enables developers/programmers to fast generate, read, write and modify Excel document in their applications. It supports C#, VB.NET, ASP.NET, ASP.NET MVC. Spire.XLS for .NET embed a method - Spire.Xls.WorkShee.MoveWorksheet(int destIndex) in its class design used to move a worksheet to another location in the spreadsheet. The method takes the target worksheet index as a parameter.
Below is the whole code samples used to move worksheet in Excel via C#, VB.NET.
using Spire.Xls; namespace moveSheet { class Program { static void Main(string[] args) { //open Excel Workbook mywbk=new Workbook(); mywbk.LoadFromFile(@"..\test.xls"); // Locate the Worksheet Worksheet mysht=mywbk.Worksheets[0]; //Move Worksheet mysht.MoveWorksheet(2); //Save and Launch mywbk.SaveToFile("result.xls",ExcelVersion.Version97to2003); System.Diagnostics.Process.Start("result.xls"); } } }
Imports Spire.Xls Namespace moveSheet Class Program Private Shared Sub Main(args As String()) 'open Excel Dim mywbk As New Workbook() mywbk.LoadFromFile("..\test.xls") ' Locate the Worksheet Dim mysht As Worksheet = mywbk.Worksheets(0) 'Move Worksheet mysht.MoveWorksheet(2) 'Save and Launch mywbk.SaveToFile("result.xls",ExcelVersion.Version97to2003) System.Diagnostics.Process.Start("result.xls") End Sub End Class End Namespace
When working with an existing Excel file or creating an Excel file from scratch, we may need to add one or more worksheets to record data. In this article, we will demonstrate how to add worksheets to Excel in C# and VB.NET using Spire.XLS for .NET library.
- Add a Worksheet to an Existing Excel file
- Add a Worksheet to a New Excel file
- Add Multiple Worksheets to a New Excel file
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Add a Worksheet to an Existing Excel file in C# and VB.NET
The following are the steps to add a worksheet to an existing Excel file:
- Create an instance of Workbook class.
- Load an Excel workbook using Workbook.LoadFromFile() method.
- Add a worksheet to the workbook using Workbook.Worksheets.Add(sheetName) method.
- Add data to a cell using Worksheet.Range[rowIndex, columnIndex].Value property.
- Save the result workbook using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace AddWorksheet { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.LoadFromFile("Sample.xlsx"); //Add a worksheet Worksheet sheet = workbook.Worksheets.Add("New_Sheet"); //Add data to cell (1, 1) sheet.Range[1, 1].Value = "New Sheet"; //Save the result file workbook.SaveToFile("AddWorksheets.xlsx", ExcelVersion.Version2016); } } }
Add a Worksheet to a New Excel File in C# and VB.NET
The following steps show how to create a new Excel file and add a worksheet to it:
- Create an instance of Workbook class.
- Clear the default worksheets using Workbook.Worksheets.Clear() method.
- Add a worksheet to the workbook using Workbook.Worksheets.Add(sheetName) method.
- Add data to a cell using Worksheet.Range[rowIndex, columnIndex].Value property.
- Save the result workbook using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace AddWorksheetToNewExcel { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Clear the default worksheets workbook.Worksheets.Clear(); //Add a worksheet with name Worksheet sheet = workbook.Worksheets.Add("Sheet1"); //Add data to cell (1, 1) sheet.Range[1, 1].Value = "Sheet 1"; //Save the result file workbook.SaveToFile("AddWorksheets.xlsx", ExcelVersion.Version2016); } } }
Add Multiple Worksheets to a New Excel File in C# and VB.NET
The following steps show how to create a new Excel file and add 3 worksheets to it:
- Create an instance of Workbook class.
- Add 3 worksheets to the workbook using Workbook.CreateEmptySheets(sheetCount) method.
- Loop through the worksheets in the workbook, add data to cell (1, 1) in each worksheet using Worksheet.Range[rowIndex, columnIndex].Value property.
- Save the result workbook using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace AddWorksheetsToNewExcel { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Add 3 worksheets workbook.CreateEmptySheets(3); //Loop through the worksheets for (int i = 0; i < workbook.Worksheets.Count; i++) { Worksheet sheet = workbook.Worksheets[i]; //Add data to cell (1, 1) in each worksheet sheet.Range[1, 1].Value = "Sheet " + (i + 1); } //Save the result file workbook.SaveToFile("AddWorksheetsToNewExcel.xlsx", ExcelVersion.Version2016); } } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
In Excel, a page break is a separator that divides a worksheet into several different pages/segments for the purpose of better printing. By inserting page breaks where necessary, you can avoid misalignment of data and ensure a desired print result, which is especially useful when working with a large data set. This article will demonstrate how to programmatically insert horizontal or vertical page breaks in Excel using Spire.XLS for .NET.
- Insert Horizontal Page Breaks in an Excel Worksheet
- Insert Vertical Page Breaks in an Excel Worksheet
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Insert Horizontal Page Breaks in an Excel Worksheet
A horizontal page break is inserted between a selected row and the row above it. After insertion, the selected row will become the top row of the new page. With Spire.XLS for .NET, developers are allowed to use the Worksheet.HPageBreaks.Add(CellRange) method to insert horizontal page breaks. The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Add horizontal page break to a specified cell range using Worksheet.HPageBreaks.Add(CellRange) method.
- Set view mode to Preview mode using Worksheet.ViewMode property.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace EditExcelComment { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load a sample Excel document workbook.LoadFromFile("input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; //Set Excel page break horizontally sheet.HPageBreaks.Add(sheet.Range["A7"]); sheet.HPageBreaks.Add(sheet.Range["A18"]); //Set view mode to Preview mode sheet.ViewMode = ViewMode.Preview; //Save the result document workbook.SaveToFile("SetHorizontalPageBreak.xlsx"); } } }
Insert Vertical Page Breaks in an Excel Worksheet
A vertical page break is inserted between a selected column and the column to its left. After insertion, the selected column will become the left most column of the new page. To insert vertical page breaks, developers can use the Worksheet.VPageBreaks.Add(CellRange) method offered by Spire.XLS for .NET offers. The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Add vertical page break to a specified cell range using Worksheet.VPageBreaks.Add(CellRange) method.
- Set view mode to Preview mode using Worksheet.ViewMode property.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; namespace EditExcelComment { class Program { static void Main(string[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load a sample Excel document workbook.LoadFromFile("input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.Worksheets[0]; //Set Excel page break vertically sheet.VPageBreaks.Add(sheet.Range["B1"]); //Set view mode to Preview mode sheet.ViewMode = ViewMode.Preview; //Save the result document workbook.SaveToFile("SetVerticalPageBreak.xlsx"); } } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
An Excel file can contain dozens of sheets, and sometimes you may need to rename these sheets to make the whole workbook more organized. Meanwhile, setting different tab colors also seems to be a good way to highlight certain important sheets. This article will introduce how to programmatically rename Excel sheets and set tab colors using Spire.XLS for .NET.
Install Spire.XLS for.NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Rename Excel Sheets and Set Tab Colors
Spire.XLS for .NET offers a simple solution for you to rename sheets and set tab colors in Excel. The detailed steps are as follows.
- Create a Workbook object.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[int] property.
- Rename the specified worksheet using Worksheet.Name property.
- Set tab color for the specified worksheet using Worksheet.TabColor property.
- Save the document to another file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls; using System.Drawing; namespace RenameWorksheet { class Program { static void Main(string[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load a sample Excel file workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\input.xlsx"); //Get the specified worksheet Worksheet worksheet = workbook.Worksheets[0]; Worksheet worksheet1 = workbook.Worksheets[1]; Worksheet worksheet2 = workbook.Worksheets[2]; //Rename Excel worksheet worksheet.Name = "Data"; worksheet1.Name = "Chart"; worksheet2.Name = "Summary"; //Set tab color worksheet.TabColor = Color.DarkGreen; worksheet1.TabColor = Color.Gold; worksheet2.TabColor = Color.Blue; //Save to file workbook.SaveToFile("Rename.xlsx", ExcelVersion.Version2010); } } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Excel copy function enables you to not only copy worksheets within Excel workbook but also copy worksheets between different Excel workbooks. This article will introduce solutions to copy worksheets within one Excel workbook and among different workbooks via Spire.XLS for .NET in C#, VB.NET. Besides, all the cell formats in the original Excel worksheets will be completely remained.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.
- Package Manager
PM> Install-Package Spire.XLS
Copy Excel Worksheets within Excel Workbook
The following are the steps to duplicate worksheets within an Excel workbook.
- Initialize an instance of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Add a new blank sheet to the workbook using WorksheetCollection.Add() method.
- Copy the original worksheet to the new sheet using Worksheet.CopyFrom() method.
- Use Workbook.SaveToFile() method to save the changes to another file.
- C#
- VB.NET
using Spire.Xls; namespace CopyExcelworksheet { class Program { static void Main(string[] args) { //Load the sample Excel Workbook workbook = new Workbook(); workbook.LoadFromFile("Sample.xlsx"); //Add worksheet and set its name workbook.Worksheets.Add("Sheet1_Copy"); //copy worksheet to the new added worksheets workbook.Worksheets[1].CopyFrom(workbook.Worksheets[0]); //Save the Excel workbook. workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013); System.Diagnostics.Process.Start("Duplicatesheet.xlsx"); } } }
Imports Spire.Xls Namespace CopyExcelworksheet Class Program Private Shared Sub Main(ByVal args() As String) 'Load the sample Excel Dim workbook As Workbook = New Workbook workbook.LoadFromFile("Sample.xlsx") 'Add worksheet and set its name workbook.Worksheets.Add("Sheet1_Copy") 'copy worksheet to the new added worksheets workbook.Worksheets(1).CopyFrom(workbook.Worksheets(0)) 'Save the Excel workbook. workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013) System.Diagnostics.Process.Start("Duplicatesheet.xlsx") End Sub End Class End Namespace
Copy Excel Worksheets between Excel Workbooks
The following are the steps to duplicate worksheets within an Excel workbook.
- Initialize an instance of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the first worksheet.
- Load another Excel sample document
- Add a new blank sheet to the second workbook using WorksheetCollection.Add() method.
- Copy the original worksheet to the new sheet using Worksheet.CopyFrom() method.
- Use Workbook.SaveToFile() method to save the changes to another file.
- C#
- VB.NET
using Spire.Xls; namespace CopyExcelworksheet { class Program { static void Main(string[] args) { //Load the sample Excel and get the first worksheet Workbook workbook = new Workbook(); workbook.LoadFromFile("Sample.xlsx"); Worksheet sheet = workbook.Worksheets[0]; //Load the second Excel workbook Workbook workbook2 = new Workbook(); workbook2.LoadFromFile("New.xlsx"); //Add a new worksheet and set its name Worksheet targetWorksheet = workbook2.Worksheets.Add("added"); //Copy the original worksheet to the new added worksheets targetWorksheet.CopyFrom(sheet); //Save the Excel workbook. workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013); System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx"); } } }
Imports Spire.Xls Namespace CopyExcelworksheet Class Program Private Shared Sub Main(ByVal args() As String) 'Load the sample Excel and get the first worksheet Dim workbook As Workbook = New Workbook workbook.LoadFromFile("Sample.xlsx") Dim sheet As Worksheet = workbook.Worksheets(0) 'Load the second Excel workbook Dim workbook2 As Workbook = New Workbook workbook2.LoadFromFile("New.xlsx") 'Add a new worksheet and set its name Dim targetWorksheet As Worksheet = workbook2.Worksheets.Add("added") 'Copy the original worksheet to the new added worksheets targetWorksheet.CopyFrom(sheet) 'Save the Excel workbook. workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013) System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx") End Sub End Class End Namespace
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.