Combine Excel Files And Keeping Worksheets/Tabs


#1

Hi, I need to combine several excel files into one using .Net Aspose.Cells (C#). I used .combine method but it creates an excel file with multiple worksheets/tabs.

For example, I have 2 excel files:
Excel1.xlsx
Excel2.xlsx
With two worksheets in each of them:
Professional
Ancillary

I need to combine those files while keeping the same number of worksheets. So, combine Excel1.xlsx Professional worksheet with Excel2.xlsx Professional worksheet and Excel1.xlsx Ancillary worksheet with Excel2.xlsx Ancillary worksheet.

Hopefully that makes sense. Let me know if you have any questions.


#2

@olegmur7,

Thanks for your query.

You may please try using Range.Copy() to combine multiple worksheets data into one sheet. Following article contains a detailed explanation for combining multiple worksheets into one worksheet.
https://docs.aspose.com/display/cellsnet/Combine+Multiple+Worksheets+into+a+Single+Worksheet (.NET)
https://docs.aspose.com/display/cellsjava/Combine+Multiple+Worksheets+into+a+Single+Worksheet (Java)


#3

Hi, thanks for the quick reply. The example provided should work for what I’m trying to accomplish with one small change.

Range sourceRange = sourceSheet.Cells.MaxDisplayRange;
This will copy everything in the source spreadsheet but I need to skip first column. How would I do that?
Thanks again.


#4

@olegmur7,

In that case, you need to adjust your code a bit by yourself to recreate the source range, so first column should be skipped. For example, you may try to change the lines of code:
i.e.,

Range sourceRange = sourceSheet.Cells.MaxDisplayRange;

Range destRange = destSheet.Cells.CreateRange(sourceRange.FirstRow + TotalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);

destRange.Copy(sourceRange);

to:


Range sourceRange = sourceSheet.Cells.MaxDisplayRange;
sourceRange = sourceSheet.Cells.CreateRange(sourceRange.FirstRow, sourceRange.FirstColumn + 1, sourceRange.RowCount, sourceRange.ColumnCount);

Range destRange = destSheet.Cells.CreateRange(sourceRange.FirstRow + TotalRowCount, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);

destRange.Copy(sourceRange);

Hope, this helps a bit.


#5

Hi, thanks for your quick response. I got everything working. You guys are awesome! This can be closed as completed if need to be.


#6

@olegmur7,

You are welcome.