How to Split One Excel Worksheet to Several Excel Documents by a Custom Range of Rows

As we know, cut and paste might be the simplest way to Split a large excel worksheet to several excel documents, but it's a waste of time and energy to achieve this function manually. Spire.XLS provides users an easy way to split one excel worksheet to several excel documents by a custom range of rows.

Note: Before start, please download and install Spire.XLS correctly. Then add Spire.XLS.dll file as the reference of your project.

Here comes the detail steps, First, please see the following original excel worksheet:

How to Split One Excel Worksheet to Several Excel Documents by a Custom Range of Rows

In this sample, the original excel worksheet was split to three excel documents

Step 1: Create a new workbook instance and load the sample document from file.

Workbook bookOriginal = new Workbook();
bookOriginal.LoadFromFile("Original.xlsx");

Step 2: Create a new workbook instance named newBook1 and add a new empty worksheet to it.

Workbook newBook1 = new Workbook();
newBook1.CreateEmptySheets(1);

Step 3: Get the first worksheet of newBook1, after that get the data from the second row to the eighth row, then copy them to the first worksheet of newBook1.

Worksheet newSheet1 = newBook1.Worksheets[0];
CellRange range1 = sheet.Range[2, 1, 8, sheet.LastColumn];
newSheet1.Copy(range1, newSheet1.Range[1, 1]);

Step 4: Repeat step 2 and step 3 to create a new workbook instance named newBook2, get the data from the ninth row to the fifteenth row and copy them to newBook2.

Workbook newBook2 = new Workbook();
newBook2.CreateEmptySheets(1);
Worksheet newSheet2 = newBook2.Worksheets[0];
CellRange range2 = sheet.Range[9, 1, 15, sheet.LastColumn];
newSheet2.Copy(range2, newSheet2.Range[1, 1]);

Step 5: Delete the data from the second row to the fifteenth row from the original worksheet, next the remain rows of data will be saved as a new document. 14 represents quantities.

sheet.DeleteRow(2, 14);

Step 6: Save the three target documents as Sales.xlsx, Human Resources.xlsx, Research and Development.xlsx;

newBook1.SaveToFile("Sales.xlsx", ExcelVersion.Version2007);
newBook2.SaveToFile("Human Resources.xlsx", ExcelVersion.Version2007);
bookOriginal.SaveToFile("Research and Development.xlsx", ExcelVersion.Version2007);

Output:

How to Split One Excel Worksheet to Several Excel Documents by a Custom Range of Rows

Full codes:

using Spire.Xls;

namespace splitworksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook bookOriginal = new Workbook();
            bookOriginal.LoadFromFile("Original.xlsx");
            Worksheet sheet = bookOriginal.Worksheets[0];
            Workbook newBook1 = new Workbook();
            newBook1.CreateEmptySheets(1);
            Worksheet newSheet1 = newBook1.Worksheets[0];
            CellRange range1 = sheet.Range[2, 1, 8, sheet.LastColumn];

            newSheet1.Copy(range1, newSheet1.Range[1, 1]);

            Workbook newBook2 = new Workbook();
            newBook2.CreateEmptySheets(1);
            Worksheet newSheet2 = newBook2.Worksheets[0];
            CellRange range2 = sheet.Range[9, 1, 15, sheet.LastColumn];
            newSheet2.Copy(range2, newSheet2.Range[1, 1]);

            sheet.DeleteRow(2, 14);
            newBook1.SaveToFile("Sales.xlsx", ExcelVersion.Version2007);
            newBook2.SaveToFile("Human Resources.xlsx", ExcelVersion.Version2007);
            bookOriginal.SaveToFile("Research and Development.xlsx", ExcelVersion.Version2007);

        }
    }
}