OpenOfficeXML: Copy worksheet from one work book to another

Question

We are using OpenOfficeXML and so far working well. We now have a scenario where we need to copy multiple worksheets from different workbooks and put them into a single workbook (as multiple worksheets).

1.The option is not present in the Office Open XML 2.When we tried using the Inner xml copying from one work book to another, it will not throw error while running the application, but while opening the excel file, it throws corrupted sheet error.

xlpackage.Workbook.Worksheets["Sheet1"].WorksheetXml.InnerXml = xcelNewWorkSheet.WorksheetXml.InnerXml;)

Answer 1

Probably this function was added after Jesper's anwser :

ExcelWorkbook wb = pk.Workbook;
ExcelWorkbook wbRef = pkRef.Workbook;

ExcelWorksheet wsRef = wbRef.Worksheets["Referentials"];
wb.Worksheets.Add("New Referentials", wsRef);

OfficeOpenXml : public ExcelWorksheet Add(string Name, ExcelWorksheet Copy);

Answer 2

There is no way (sorry) that this will work for you with a simple approach like you are presenting here.

The WorkSheet is not an isolated instance. It contains references to Shared strings tables, to styles, to referenced elements etc, etc, etc.

I am not saying that it cannot be done - naturally it can. But you need to set your expectations to the complexity a lot higher than what you have done here.

For the WorkSheet you need to copy, you will need to identify and analyze all references to other parts of the package. You will then need to copy those too and merge them into the already existing styles, formats, SharedStrings-tables in the spreadsheet package you want to copy them to.

And in the existing target spreadsheet, you will need to create all necessary references to the new content you are adding.

You could also try to find a 3rd party tool that will help you with this - but others will have to kick in with names of those - I don't know of any.