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