C#/VB.NET ：Split an Excel Worksheet into Separate Excel Files

 With the continuous expansion of data, the scale of some data tables will increase geometrically. When the data reaches a certain scale, the query and reading performance of the data will become slow. At this time, the data table needs to be split. Next In this article, you will learn in detail how to split an Excel worksheet into separate Excel files through C#/VB.NET code. The following are the specific steps and codes I have compiled for your reference, I hope it will be helpful to you.

Programming Environment

In this test, Free Spire.XLS for .NET is introduced into the program. The Spire.XLS.dll file can be referenced by:

Method 1: Download Free Spire.XLS for .NET locally, unzip it, and install it. After the installation is complete, find Spire.XLS.dll in the BIN folder under the installation path. Then open the "Solution Explorer" in Visual Studio, right-click "References", "Add Reference", and add a reference to the dll file in the BIN folder of the local path to the program.

Method 2: Install via NuGet. It can be installed by the following 2 methods:

(1) You can open the "Solution Explorer" in Visual Studio, right-click "References", "Manage NuGet Packages", then search for "Free Spire.XLS", and click "Install". Wait for the program installation to complete.

(2) Copy the following content to the PM console installation.

Install-Package FreeSpire.XLS -Version 12.7.0

Specific Steps:

• Create a Workbook object
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Declare a new Workbook variable, which is used to create new Excel workbooks.
• Loop through the worksheets in the source document.
• Initialize the Workbook object, and add the copy of a specific worksheet of source document into it.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

Full Code:

【C#】

using Spire.Xls;
using System;


namespace SplitWorksheets
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook wb = new Workbook();


            //Load an Excel document
            wb.LoadFromFile("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 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


Namespace SplitWorksheets
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook object
            Dim wb As Workbook = New Workbook()


            'Load an Excel document
            wb.LoadFromFile("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 = "C:\Users\ Administrator \Desktop\Output\"


            'Loop through the worksheets in the source file
            For i As Integer = 0 To wb.Worksheets.Count - 1


                'Initialize the Workbook object
                newWb = New Workbook()


                'Remove the default sheets
                newWb.Worksheets.Clear()


                'Add 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




Effective Shot







Conclusion:
In this post, you have learned how to split an Excel worksheet into separate Excel files in C#/VB.NET. Not only that, we also have other functions, such as, Merge Excel Files into One , Split One Excel Worksheet to Several Excel Documents and so on. Apart from that, if you'd like to learn more, you can visit this link to explore more about for Spire.XLS for .NET.