Combine Excel documents into one workbook

Can AsPose help me to combine Excel documents into one
Excel workbook and rename Sheets according to source workbook name?

Hi,

Well, you may use number of ways to add/merge different sheets of workbooks into on file. You may try to brows different sheets of a workbook and copy them to other workbook using Worksheet.Copy() method, see the document for reference: https://docs.aspose.com/display/cellsnet/Copying+and+Moving+Worksheets

Alternatively you can use Workbook.Copy or Workbook.Combine methods if it fits your need, see the following sample code:

Workbook wb2 = new Workbook();

wb2.Open("f:\\test\\Book2.xls");

Workbook wb3 = new Workbook();

wb3.Open("f:\\test\\Book3.xls");

wb3.Combine(wb2);

wb3.Save("f:\\test\\MyBook.xls");

For renaming a sheet, you may use Worksheet.Name property, see the document: https://docs.aspose.com/display/cellsnet/Manage+Worksheets

Thank you.

Hi Amjad,

I looked at the sample VB.NET codes.

But this looks like a regular VB.NEt code.

Where is your Aspose component coming into play?

Hi,

Well, I am not sure what you are looking for and your actual requirements. But, to tell you more about the component, Aspose.Cells for .NET is pure .NET component/library used to manage MS Excel (97-2007) files (xls,xlsx, csv, spreadsheetml, tab delimited etc.). To use the component, you need to utilize it in some language e.g, C#,VB.NET, J#, vbscript, javascript etc. For your info, Aspose.Cells for .NET component comprises of a single .dll file (Aspose.Cells.dll). You may get its installer @: `http://www.aspose.com/community/files/51/file-format-components/aspose.cells-for-.net-and-java/default.aspx` so you may install the component on your machine. For reference on how to install/use the component, check the topics in the sections: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/deployment.html and http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/installation.html Moreover, please check how to implement MS Excel's different features using Aspose.Cells for a programmer's reference: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/programmers-guide.html

And, you don't need any type of registration or special configuration, you may use even xcopy method to add reference to Aspose.Cells.dll file only to work with the component in your project.

To get further reference about the component, see the featured demos: http://www.aspose.com/demos/file-format-components/aspose.cells/default.aspx

The codes you find in the document(s) (previously I mentioned) or the code segment I wrote in my previous post do use the APIs of Aspose.Cells for .NET. see the API Reference: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/aspose.cells-for-.net-api-reference.html

Feel free to contact us if you need further help or clarifications, we would be happy to assist you.

Thank you.

Hi Amjad,

I've already explained what we're trying to do.
We need to combine multiple Excel workooks into one workbook
and name sheets according to the source workbook names.

Sample:

Source workbooks
--------------------
GDSR.xls
LimitMonitoring.xls

Combined workbook
----------------------
Combined.xls
(Sheet1.Name = GDSR; Sheet2.Name = LimitMonitoring)

And I am trying to understand if Aspose.Cells component would be useful to implement it
or we just need to program everything in Excel VBA or VB.NET.

Hope this explains my intentions.

Thank you,
Robert

Hi Robert,

Yes, I think Aspose.Cells for .NET can serve your need. Please refer to the codes and document links which I suggested you to check.

If you still find any issue, please provide your sample source excel files and your expected combined file, we will tell you how you can implement the task and get the resultant book using Aspose.Cells APIs.

Thank you.

I attached two Excel workbooks.

Limit_Monitoring.xls
Limit_Monitoring_2.xls

So we would like to have one Excel workbook (e.g. "Limit_Monitoring_Summary.xls")
with data from the first workbook copied to Sheet1
and data from the second workbook copied to Sheet2.

Sheet1.Name = Limit_Monitoring
Sheet2.Name = Limit_Monitoring_2

Hi,

Thanks for providing us the template files.

I have created the sample code to achieve your desired task using Aspose.Cells API for your requirement, kindly refer to it. Attached is the final workbook named "Limit_Monitoring_Summary.xls".

Sample code:

[C#]

//Define the first source book.
Workbook SourceBook1 = new Workbook();
//Open the first excel file.
SourceBook1.Open("f:\\test\\Limit_Monitoring.xls");
//Define the second source book.
Workbook SourceBook2 = new Workbook();
//Open the second excel file.
SourceBook2.Open("f:\\test\\Limit_Monitoring_2.xls");
//Define the target book.
Workbook FinalBook = new Workbook();
//Delete its default sheet.
FinalBook.Worksheets.Clear();
//Add a new sheet named "Sheet1" to it.
int SheetIndex = FinalBook.Worksheets.Add();
FinalBook.Worksheets[SheetIndex].Name = "Sheet1";
//Copy the first sheet from the sourcebook1 to sheet1(targetbook)
FinalBook.Worksheets[SheetIndex].Copy(SourceBook1.Worksheets[0]);
//Add a new sheet named "Sheet2" to it.
SheetIndex = FinalBook.Worksheets.Add();
FinalBook.Worksheets[SheetIndex].Name = "Sheet2";
//Copy the first sheet from the sourcebook2 to sheet2(targetbook)
FinalBook.Worksheets[SheetIndex].Copy(SourceBook2.Worksheets[0]);
//Save the target book file.
FinalBook.Save("f:\\test\\Limit_Monitoring_Summary.xls");



[VB.NET]

'Define the first source book.
Dim SourceBook1 As New Workbook()
'
Open the first excel file.
SourceBook1.Open("f:\test\Limit_Monitoring.xls")
'Define the second source book.
Dim SourceBook2 As New Workbook()
'
Open the second excel file.
SourceBook2.Open("f:\test\Limit_Monitoring_2.xls")
'Define the target book.
Dim FinalBook As New Workbook()
'
Delete its default sheet.
FinalBook.Worksheets.Clear()
'Add a new sheet named "Sheet1" to it.
Dim SheetIndex As Integer = FinalBook.Worksheets.Add()
FinalBook.Worksheets(SheetIndex).Name = "Sheet1"
'
Copy the first sheet from the sourcebook1 to sheet1(targetbook)
FinalBook.Worksheets(SheetIndex).Copy(SourceBook1.Worksheets(0))
'Add a new sheet named "Sheet2" to it.
SheetIndex = FinalBook.Worksheets.Add()
FinalBook.Worksheets(SheetIndex).Name = "Sheet2"
'
Copy the first sheet from the sourcebook2 to sheet2(targetbook)
FinalBook.Worksheets(SheetIndex).Copy(SourceBook2.Worksheets(0))
'Save the target book file.
FinalBook.Save("f:\test\Limit_Monitoring_Summary.xls")
Kindly let us know if you have further queries, we will be happy to assist you.
Thank you.

@riga,

We recommend to use newer Aspose.Cells for .NET, which can directly merge/combine multiple workbooks into one final book. See the following sample code to accomplish the task:
e.g
Sample code:

Workbook SourceBook1 = new Workbook(dataDir + "charts.xlsx");

// Define the second source book.
// Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir + "data.xlsx");

// Combining the two workbooks
SourceBook1.Combine(SourceBook2);

// Save the target book file.
SourceBook1.Save(dataDir + "combined.xlsx");

Also, see the document for your reference:
Combine multiple worksheets into single workbook

Use the link to download/get latest versions of Aspose.Cells:
Aspose.Cells for .NET (Latest Version)

You may also browse different examples on different features for your reference.