C#/VB.NET: Split Excel Sheets into Separate Files

2022-08-31 07:10:00 Written by  support iceblue
Rate this item
(0 votes)

A workbook containing multiple worksheets helps to centrally manage relevant information, but sometimes we have to split the worksheets into separate Excel files so that individual worksheets can be distributed without disclosing other information. In this article, you will learn how to split Excel worksheets into separate workbooks in C# and VB.NET using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Split Excel Sheets into Separate Files

The following are the main steps to split Excel sheets into separate workbooks using Spire.XLS for .NET.

  • 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.
  • C#
  • VB.NET
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("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 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);
            }
        }
    }
}

C#/VB.NET: Split Excel Sheets into Separate Files

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Additional Info

  • tutorial_title:
Last modified on Thursday, 01 September 2022 01:28