OfficeWriter 8.2: Copysheet Cross Workbook
Submitted by samh on Mon, 06/25/2012 - 12:54
My name is Sam and I am one of the developers on OfficeWriter. Today I want to talk about my favorite feature that was implemented in the newest release of OfficeWriter’s ExcelWriter. The feature I am referring to is CopySheet. While CopySheet has existed in our API for some time, it was used to copy sheets within the same workbook. With ExcelWriter 8.2 we have extended CopySheet to handle copying worksheets from multiple workbooks. This is great for a scenario where you would want to merge multiple sub reports.
The code for this is extremely simple. In my example I open up 3 sub reports and a sheet from each report into my final output. The code for this would be something like:
ExcelApplication XLApp = new ExcelApplication();
Workbook wb1 = XLApp.Open(Server.MapPath("bin\\Coversheet.xlsx"));
Workbook wb2 = XLApp.Open(Server.MapPath("bin\\PriceList.xlsx"));
Workbook wb3 = XLApp.Open(Server.MapPath("bin\\Sales.xlsx"));
Workbook finalWorkbook = XLApp.Create(ExcelApplication.FileFormat.Xlsx);
finalWorkbook.Worksheets.CopySheet(wb1, 1, wb1.Name);
finalWorkbook.Worksheets.CopySheet(wb2, 2, wb2.Name);
finalWorkbook.Worksheets.CopySheet(wb3, 3, wb3.Name);
finalWorkbook.Worksheets.Delete(0); //Delete the default empty sheet
XLApp.Save(finalWorkbook, Context.Response, "CompiledReport.xlsx", false);
If you would like to see this code running in action, you can download a copy of the demo here. Keep in mind, this demo requires OfficeWriter version 8.2, which you can download a free trial of from the Evaluation page. Read about the full list of features and fixes (including Mailmerge) from our product manager, Alison.