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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.