SpreadsheetGear - Issue with copying sheets from one Workbook to another

Question

I would like to merge all sheets from several Workbooks and save them in one excel file. I am trying to copy all the sheets in one Workbook and then save the workbook to a file. The problem is - only the last sheet is saved in the file. Below is some sample code.

public static void ExportSheets(string documentFilePath, List<IWorkbook> workbooks)
        {
            if (workbooks.Count > 1)
            {
                var destinationSourceBook = workbooks[0];
                for (int i = 1; i < workbooks.Count; i++)
                {
                    var sourceWorkBook = workbooks[i];
                    for (int j = 0; j < sourceWorkBook.Sheets.Count; j++)
                    {
                        sourceWorkBook.Sheets[j].CopyAfter(destinationSourceBook.Sheets[destinationSourceBook.Sheets.Count - 1]);
                    }
                }
                destinationSourceBook.SaveAs(documentFilePath, FileFormat.OpenXMLWorkbook);
            }
            else if (workbooks.Count == 1)
            {
                workbooks[0].SaveAs(documentFilePath, FileFormat.OpenXMLWorkbook);
            }
        }

While debugging, I could see that the sheets are getting added to the destinationSourceBook.Sheets collection, but only the last sheet is present in the saved excel file. What is the right way to do this?

Thanks

score 0 · Accepted Answer · answered Aug 08 '18 at 10:58

The sheets were actually getting added, but the problem was the Sheet tabs were not visible (in excel - File -> Options -> Advanced -> Display Options -> Show sheet tabs checkbox). And the solution was to set Workbook.WindowInfo.DisplayWorkbookTabs to true.

        public static void ExportSheets(string documentFilePath, List<IWorkbook> workbooks)
        {
            if (workbooks.Count > 0)
            {
                var destinationWorkbook = workbooks[0];

                for (var i = 1; i < workbooks.Count; i++)
                {
                    var sourceWorkBook = workbooks[i];
                    for (var j = 0; j < sourceWorkBook.Sheets.Count; j++)
                    {
                        sourceWorkBook.Sheets[j].CopyAfter(destinationWorkbook.Sheets[destinationWorkbook.Sheets.Count - 1]);
                    }
                }
                destinationWorkbook.WindowInfo.DisplayWorkbookTabs = true;
                destinationWorkbook.SaveAs(documentFilePath, FileFormat.OpenXMLWorkbook);
            }
        }

SpreadsheetGear - Issue with copying sheets from one Workbook to another

1 Answers1