How to split one Excel file into multiple Excel files
Syncfusion Essential XlsIO is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can split one Excel file into multiple files or combine several Excel files into a single file in C# and VB.NET.
Steps to split one Excel file into multiple Excel files programmatically:
- Create a new C# console application project.
- Install Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.
- Include the following namespaces in the Program.cs file.
using Syncfusion.XlsIO; using System.IO; using System.Reflection;
Imports Syncfusion.XlsIO Imports System.IO Imports System.Reflection
- Add the following code snippet to split one Excel file into multiple Excel files.
//Initialize ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Initialize Application
IApplication application = excelEngine.Excel;
//Set default application version
application.DefaultVersion = ExcelVersion.Excel2013;
//Open existing workbook with data entered
Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("SplitExcelFile.Sample.xlsx");
IWorkbook workbook = application.Workbooks.Open(fileStream);
//Access first worksheet from the workbook
IWorksheet worksheet = workbook.Worksheets[0];
//Create two new workbooks
IWorkbook workbook1 = application.Workbooks.Create(1);
IWorkbook workbook2 = application.Workbooks.Create(1);
//The content in the range A10:C17 and A18:C25 from the source workbook is split into workbook1 and workbook2 respectively
worksheet.Range[10, 1, 17, 3].CopyTo(workbook1.Worksheets[0].Range[1, 1]);
workbook1.Worksheets[0].UsedRange.AutofitColumns();
worksheet.Range[18, 1, 25, 3].CopyTo(workbook2.Worksheets[0].Range[1, 1]);
workbook2.Worksheets[0].UsedRange.AutofitColumns();
worksheet.Range[10, 1, 25, 3].Clear();
//Save the workbooks
workbook.SaveAs("Owner.xlsx");
workbook1.SaveAs("Sales Manager.xlsx");
workbook2.SaveAs("Sales Representative.xlsx");
}
'Initialize ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
'Initialize Application
Dim application As IApplication = excelEngine.Excel
'Set default application version
application.DefaultVersion = ExcelVersion.Excel2013
'Open existing workbook with data entered
Dim assembly As Assembly = GetType(Program).GetTypeInfo.Assembly
Dim fileStream As Stream = assembly.GetManifestResourceStream("SplitExcelFile.Sample.xlsx")
Dim workbook As IWorkbook = application.Workbooks.Open(fileStream)
'Access first worksheet from the workbook
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Create two new workbooks
Dim workbook1 As IWorkbook = application.Workbooks.Create(1)
Dim workbook2 As IWorkbook = application.Workbooks.Create(1)
'The content in the range A10:C17 and A18:C25 from the source workbook is split into workbook1 and workbook2 respectively
worksheet.Range(10, 1, 17, 3).CopyTo(workbook1.Worksheets(0).Range(1, 1))
workbook1.Worksheets(0).UsedRange.AutofitColumns()
worksheet.Range(18, 1, 25, 3).CopyTo(workbook2.Worksheets(0).Range(1, 1))
workbook2.Worksheets(0).UsedRange.AutofitColumns()
worksheet.Range(10, 1, 25, 3).Clear()
'Save the workbooks
workbook.SaveAs("Owner.xlsx")
workbook1.SaveAs("Sales Manager.xlsx")
workbook2.SaveAs("Sales Representative.xlsx")
End Using
A complete working example to split one Excel file into multiple Excel files along with the input file used can be downloaded from Split-Excel-file.zip
The input file used in this sample looks like,
By executing the program, you will get the Excel files as follows.
Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.
Refer here to explore the rich set of Syncfusion Essential XlsIO features.
See Also:
Merge or combine multiple Excel files to single file
Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.
