C # splits an Excel worksheet into multiple Excel files based on a specified range

C # Add an Excel worksheets are split into multiple Excel based on a specified range file

Microsoft Excel does not provide a direct way to split Excel files, so the simplest way to split an Excel file is to cut and paste it manually, in addition to other methods such as VBA and Visual Studio languages such as C #, VB. NET, and so on) programming the way. VBA is not very familiar to me, so this article is about how to use C # to split an Excel worksheet into multiple Excel files based on a specified range.

The following is an Excel Human Resources information table containing three departments and some employee information:

Then we split the Excel worksheet, and in this example, I split the table into three separate Excel files, each of which is a departmental message.

Detailed steps:

Use a namespace:

Create a new Visual C # project, add references, and use the following namespaces:

Using Spire.xls;

Step 1 : Create an object of the workbook class, load the source Excel file, and get the worksheet that you want to split in the file, which is the first worksheet in the example.

Workbook bookoriginal = new Workbook (); Bookoriginal.loadfromfile ("Information sheet. xlsx"); Worksheet sheet = bookoriginal.worksheets[0];

Step 2 : Create a new Workbook object NewBook1 and add an empty worksheet to it.

Workbook NewBook1 = new Workbook (); newbook1.createemptysheets (1);

Step 3 : Gets the first sheet of NewBook1, and then gets the data from the second row to the eighth row (Sales department) on the source Excel worksheet, and copies them to the first sheet 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 steps 2 and 3 to create a new Workbook object Newbook2, get the data from line Nineth to line 15th (human resources) in the source Excel worksheet, and copy them to Newbook2.

Workbook newBook2 = new Workbook (); newbook2.createemptysheets (1); Worksheet NewSheet2 = newbook2.worksheets[0]; CellRange Range2 = sheet. Range[9, 1, sheet. Lastcolumn];newsheet2.copy (Range2, newsheet2.range[1, 1]);

Step 5 : Delete the data from the second row to the 15th row in the source Excel file, and the data for the remaining lines (Research and Development department) will be saved as another new Excel file. Note that the 14 here represents not the line number but the number of rows.

Sheet. DeleteRow (2, 14);

Step 6 : Save the three Excel files and name them as a department.

Newbook1.savetofile ("sales department. xlsx", excelversion.version2007), Newbook2.savetofile ("Human resources department. Xlsx", excelversion.version2007); Bookoriginal.savetofile ("Research and development department, xlsx", excelversion.version2007);

：

All code:

Using Spire.xls;namespace splitworksheet{class Program {static void Main (string[] args) {            Workbook bookoriginal = new Workbook ();            Bookoriginal.loadfromfile ("Information sheet. 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, sheet.            Lastcolumn];            Newsheet2.copy (Range2, newsheet2.range[1, 1]); Sheet.            DeleteRow (2, 14);            Newbook1.savetofile ("sales department. xlsx", excelversion.version2007);     Newbook2.savetofile ("Human resources department. Xlsx", excelversion.version2007);       Bookoriginal.savetofile ("Research and development department, xlsx", excelversion.version2007); }    }}

Summarize:

In the example above, I'm using the free Spire.xls, splitting an Excel file is based on the line of the specified department, in addition to the department's name can be split, and to retrieve data and export to a new Excel document similar to, I have written in the previous article, if necessary can be consulted.

Thank you for watching!

C # splits an Excel worksheet into multiple Excel files based on a specified range