When dealing with an Excel worksheet containing a large amount of data, splitting it into several separate Excel files based on specific criteria can be beneficial. By dividing the worksheet into smaller, more manageable files, you can improve your work efficiency and make data analysis easier. This article will demonstrate how to programmatically split an Excel worksheet into multiple Excel files 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
Split an Excel Sheet into Multiple Files in C# and VB.NET
The Worksheet.Copy(CellRange sourceRange, CellRange destRange) method provided by Spire.XLS for .NET allows you to split a worksheet by copying a specified cell range from the original Excel file to a new Excel file. The following are the detailed steps.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[] property.
- Get the header row and the specified cell ranges using Worksheet.Range property.
- Create a new workbook and get its first worksheet.
- Copy the header row and specified cell range to the first worksheet of the new workbook using Worksheet.Copy(CellRange sourceRange, CellRange destRange) method.
- Copy the column width from the original workbook to the new workbook, and then save the new workbook to an Excel file using Workbook.SaveToFile() method.
- Create another new workbook, and then repeat the above steps to copy the header row and specified cell range into the new workbook.
- Save the new workbook to another Excel file.
- C#
- VB.NET
using Spire.Xls;
namespace splitworksheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook originalBook= new Workbook();
//Load the original Excel document from file
originalBook.LoadFromFile("Info.xlsx");
//Get the first worksheet
Worksheet sheet = originalBook.Worksheets[0];
//Get the header row
CellRange headerRow = sheet.Range[1, 1, 1,5];
//Get two cell ranges
CellRange range1 = sheet.Range[2, 1, 6, sheet.LastColumn];
CellRange range2 = sheet.Range[7, 1, sheet.LastRow, sheet.LastColumn];
//Create a new workbook
Workbook newBook1 = new Workbook();
//Get the first worksheet of new workbook
Worksheet newSheet1 = newBook1.Worksheets[0];
//Copy the header row and range 1 to the first worksheet of the new workbook
sheet.Copy(headerRow, newSheet1.Range[1, 1]);
sheet.Copy(range1, newSheet1.Range[2, 1]);
//Copy the column width from the original workbook to the new workbook
for (int i = 0; (i < sheet.LastColumn); i++)
{
newBook1.Worksheets[0].SetColumnWidth(i + 1, sheet.GetColumnWidth(i + 1));
}
//Save the new workbook to an Excel file
newBook1.SaveToFile("Sales Depart.xlsx", ExcelVersion.Version2016);
//Create another new workbook
Workbook newBook2 = new Workbook();
//Get the first worksheet of new workbook
Worksheet newSheet2 = newBook2.Worksheets[0];
//Copy the header row and range 2 to the first worksheet of the new workbook
sheet.Copy(headerRow, newSheet2.Range[1, 1]);
sheet.Copy(range2, newSheet2.Range[2, 1]);
//Copy the column width from the original workbook to the new workbook
for (int i = 0; (i < sheet.LastColumn); i++)
{
newBook2.Worksheets[0].SetColumnWidth(i + 1, sheet.GetColumnWidth(i + 1));
}
//Save the new workbook to another Excel file
newBook2.SaveToFile("Development Depart.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.
