Monday, 14 December 2020 07:10

Split a Workbook into Multiple Excel Documents in C#, VB.NET

Written by  support iceblue
Rate this item
(0 votes)

When you receive a large Excel document that has multiple worksheets, you may want to save each worksheet out as a separate Excel file. This article will introduce how to split a workbook by using Spire.XLS with C# or VB.NET.

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);
            }
        }
    }
}
VB.NET
Imports Spire.Xls
Imports System
 
Namespace SplitWorkbook
    Class Program
        Shared  Sub Main(ByVal args() As String)
            'Create a Workbook object
            Dim wb As Workbook =  New Workbook() 
 
            'Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx")
 
            'Declare a new Workbook variable
            Dim NewWb As Workbook
 
            'Declare a String variable
            Dim sheetName As String
 
            'Specify the folder path, which is used to store the generated Excel files
            Dim folderPath As String =  "C:\\Users\\Administrator\\Desktop\\Output\\" 
 
            'Loop through the worksheets in the source file
            Dim i As Integer
            For  i = 0 To  wb.Worksheets.Count- 1  Step  i + 1
 
                '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)
            Next
        End Sub
    End Class
End Namespace

Output

Split a Workbook into Multiple Excel Documents in C#, VB.NET