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

Muhammad Rizwan

5y2.2kLearn iOS Programming

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Marvin Reid4y ago
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 < document.Pages.Count; 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
0
Leon D5y ago
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 < wb.Worksheets.Count; 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
0