Managing Excel documents can be challenging, especially when they are large or contain multiple sheets with diverse data. Navigating and managing the information within a single document can become increasingly difficult. A practical solution to this issue is to divide these extensive Excel documents into several smaller ones, for manageability and efficiency.
The Syncfusion Excel Library (XlsIO) is a powerful tool designed to streamline the creation, reading, and editing of Excel documents using C#. It provides a simple and efficient way to handle Excel documents. With just a few lines of code, you can open an Excel document and split it into multiple smaller documents. This automation saves valuable time and ensures the precision of data. This library is particularly useful for those who frequently work with large datasets in Excel and are looking for ways to improve their productivity and data management.
So, let’s see how to split an Excel file into multiple Excel files using the Syncfusion Excel Library in C#.
Follow these steps to split an Excel file into multiple files.
First, we will start by launching Visual Studio.
The next step is to establish a new project. This will be a .NET Core console application. You can accomplish this by adhering to the instructions in this getting started document. It will navigate you through the process, ensuring your project is set up accurately.
Refer to the following image.
Before you delve into coding, install the Syncfusion Excel Library. Navigate to Tools -> NuGet Package Manager -> Manage NuGet Packages for Solution. In the NuGet Package Manager’s search bar, input Syncfusion.XlsIO.Net.Core and press Enter. Once the library pops up in the search results, click Install to integrate it into your project.
With everything in place, it’s time to commence coding. You will need to inject the necessary code into the Program.cs file to split an Excel document from a specified folder path. This involves incorporating a suitable namespace and the function that executes the splitting operation.
Refer to the following code example.
using Syncfusion.XlsIO;
namespace SplitExcel
{
class Program
{
private static string inputPath = @"../../../Data/";
private static string outputPath = @"../../../Output/";
static void Main(string[] args)
{
string fileName = "Report.xlsx";
//Split the Excel document.
SplitExcelDocument(inputPath + fileName);
}
/// <summary>
/// Split the Excel document from the given path.
/// </summary>
/// <param name="filePath">Excel file path</param>
private static void SplitExcelDocument(string filePath)
{
FileStream inputData = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open(inputData);
IWorksheets worksheets = workbook.Worksheets;
workbook.Version = ExcelVersion.Xlsx;
//Loop through each Excel worksheet and save it as a new workbook.
foreach (IWorksheet worksheet in worksheets)
{
IWorkbook newBook = application.Workbooks.Create(0);
newBook.Worksheets.AddCopy(worksheet);
FileStream outputData = new FileStream(outputPath + worksheet.Name + ".xlsx", FileMode.Create, FileAccess.ReadWrite);
newBook.SaveAs(outputData);
outputData.Close();
}
}
}
}
} The following image shows the input Excel document.
After executing the previous code example, the output documents will look like the following images. See the number of Excel documents created from the original Excel file.
You can download the example for splitting Excel documents in C# on this GitHub page.
Thanks for reading! We’ve explored how the Syncfusion Excel Library can simplify splitting an Excel document into multiple Excel documents using C#. I encourage you to explore the documentation further, where you’ll find various import options and features such as data tables, collection objects, grid view, data columns, and HTML, all accompanied by helpful code samples.
The Excel Library also allows you to export Excel data to various file formats, including PDFs, images, data tables, CSV, TSV, HTML, collections of objects, ODS, JSON, and more.
If you’re new to our Excel Library, we recommend following our Getting Started guide. For existing Syncfusion users, you can download the product setup directly here. If you’re not yet a Syncfusion user, we offer a free 30-day trial for you to explore and evaluate our products.
If you have any questions or need further clarification on these features, feel free to leave a comment below. You can also contact us through our support forum, support portal, or feedback portal. We’re always here to help you!