How to split a large excel file into smaller ones using c# OpenXML?

Question

i want to split a very large excel files into smaller files using c# OpenXML libaray.

Marvin Reid · Answer

You can use the DocumentConverter class to load and convert large Excel files to smaller ones. Enabling MultiPageSheet will also allow you to control the size of each output page. You can try the following code to do this: var page = 1; var options = new LoadDocumentOptions(); DocumentFactory.RasterCodecsTemplate.Options.Xls.Load.MultiPageSheet = true ; using var document = DocumentFactory.LoadFromFile(@ "D:\Input\input.xlsx" , options); using var documentConverter = new DocumentConverter(); for ( int i = 0; i { string outputfile = string.Format( "File_{0}.xls" , i + 1); var jobData = new DocumentConverterJobData { Document = document, DocumentFormat = DocumentFormat.Xls, OutputDocumentFileName = outputfile, InputDocumentFirstPageNumber = page, InputDocumentLastPageNumber = page }; var job = documentConverter.Jobs.CreateJob(jobData); documentConverter.Jobs.RunJob(job); page = page + 1; } https://www.leadtools.com/help/sdk/tutorials/dotnet-console-convert-files-with-the-document-converter.html https://www.leadtools.com/help/sdk/v22/dh/co/codecsxlsloadoptions-multipagesheet.html

Leon D · Answer

You can install Free Spire.XLS library via nuget and then use the following code to split a lart excel file into multiple excel files in c#. using Spire.Xls; using System; namespace SplitWorkbook { class Program { static void Main( string [] args) { //Create a Workbook object Workbook wb = new Workbook(); //Load an Excel document wb.LoadFromFile( "C:\Users\Administrator\Desktop\data.xlsx" ); //Declare a new Workbook variable Workbook newWb; //Declare a String variable String sheetName; //Specify the folder path, which is used to store the generated Excel files String folderPath = "C:\Users\Administrator\Desktop\Output\" ; //Loop through the worksheets in the source file for ( int i = 0; i { //Initialize the Workbook object newWb = new Workbook(); //Remove the default sheets newWb.Worksheets.Clear(); //Add the the specific worksheet of the source document to the new workbook newWb.Worksheets.AddCopy(wb.Worksheets[i]); //Get the worksheet name sheetName = wb.Worksheets[i].Name; //Save the new workbook to the specified folder newWb.SaveToFile(folderPath + sheetName + ".xlsx" , ExcelVersion.Version2013); } } } } For more information, visit this link: https://www.e-iceblue.com/Tutorials/Spire.XLS/Program-Guide/Document-Operation/Split-a-Workbook-into-Multiple-Excel-Documents-in-C-VB.NET.html