Use the Aspose. Cell Control to merge multiple Excel files

I have written too many articles about how to use the Apose. Cell Control to create a custom template report and export data in a general Excel table. I am satisfied with the function of this control and it is convenient. Suddenly one day, a friend said: You have already generated a custom template-based report. But if I export the same report for every organization, I would not generate many files, it is not convenient to compare and view data. Is there a better way to merge them into a file? This makes it much easier for me to read reports. This article describes how to generate multiple similar reports and merge them in a file based on a custom report mode.

The introduction to querying the use of the Apose. Cell Control. The WorkBook object does have a Combine method dedicated to file merging. The implementation code is as follows.

Workbook SourceBook1 = new Workbook ();
SourceBook1.Open ("c: \ excels \ ChartTest.xls ");

Workbook SourceBook2 = new Workbook ();
SourceBook2.Open ("C: \ excels \ PictureTest.xls ");

SourceBook1.Combine (SourceBook2 );
SourceBook1.Save ("c: \ excels \ combined.xls ");

Now that this method is used to merge files, you can solve the problems raised by the customer through this method. The problem is that this method merges existing files. What the customer needs is to generate multiple similar reports based on a custom template and put them in one file. Each report has only one Sheet.

SourceBook1.Combine (SourceBook2 );
SourceBook1.Save ("c: \ excels \ combined.xls ");

 

Through the above code, we can see that the logic of file merging is actually the combination of multiple workbooks, and then save the final WorkBook as another file.

First, I will introduce the implementation idea through a simple example. First, I will design a simple custom template, as shown below.

 

In this way, we can generate a series of similar report files based on the custom template and merge them one by one. The Code implemented in the example is as follows:

Private DataTable GetCustomersTable ()
{
DataTable dt = new DataTable ("MERs ");
Dt. Columns. Add ("Address ");
Dt. Columns. Add ("City ");
Dt. Columns. Add ("CompanyName ");
Dt. Columns. Add ("ContactName ");
Dt. Columns. Add ("ContactTitle ");
Dt. Columns. Add ("Country ");
Dt. Columns. Add ("CustomerID ");
Dt. Columns. Add ("Fax ");
Dt. Columns. Add ("Phone ");
Dt. Columns. Add ("PostalCode ");
Dt. Columns. Add ("Region ");
For (int I = 0; I <10; I ++)
{
DataRow row = dt. NewRow ();
For (int j = 0; j <dt. Columns. Count; j ++)
{
Row [j] = dt. Columns [j]. ColumnName + "(" + I. ToString () + "," + j. ToString () + ")";
}
Dt. Rows. Add (row );
}
Return dt;
}

Private void btnCombind_Click (object sender, EventArgs e)
{
Workbook SourceBook1 = new Workbook ();

String path = System. IO. Path. Combine (Application. StartupPath, "SmartMarkerCombind.xls ");
DataTable dt = GetCustomersTable (); // use a DataTable object

List <string> fileList = new List <string> ();
For (int I = 0; I <3; I ++)
{
Workbook tempBook = new Workbook ();

// Create a design template object and bind the data source
WorkbookDesigner designer = new WorkbookDesigner ();
Designer. Open (path );
Designer. SetDataSource (dt );
Designer. Process ();

// Modify the Sheet name
Designer. Workbook. Worksheets [0]. Name = "test" + I. ToString ();

// Generate the corresponding report Excel file based on the data source and Custom template
String fileToSave = System. IO. Path. Combine (Application. StartupPath, string. Format ("combind1_02.16.xls", I ));
Designer. Save (fileToSave, FileFormatType. Excel2003 );
FileList. Add (fileToSave );

// The First Time
If (I = 0)
{
SourceBook1.Open (fileToSave );
}
Else
{
// Use the Combind function for the second operation
TempBook. Open (fileToSave );
SourceBook1.Combine (tempBook );
}
}

// Save the WorkBook as a file.
String soucePath = System. IO. Path. Combine (Application. StartupPath, "Combind.xls ");
SourceBook1.Save (soucePath );

// Delete a temporary file
Foreach (string file in fileList)
{
If (File. Exists (file ))
{
File. Delete (file );
}
}

// Open the file
Process. Start (soucePath );
}
}

 

Note: Because the Workbook object creates only one Sheet object for use by default, you must modify the corresponding Sheet Name one by one, as shown in the following code:

Designer. Workbook. Worksheets [0]. Name = "test" + I. ToString ();

Shows the effect of the final multi-Sheet object Excel report:

 

Of course, complicated reports may be more complicated than processing, but the general logic is to use such steps for integration. After the real reports are integrated in the project, the other party is satisfied, everything is OK.