Combine workbooks

[Adrian]

is it possible to combine multiple workbooks into one workbook with multiple sheets? In code, I have created three workbooks from three separate DB calls. I would like to, in code, combine the three books so that I will have one workbook with 3 sheets (one with data from each of the calls).

Is this possible??


Thanks!!

[Andrew]

Hello Adrian,

You have stumbled upon an interesting scenario. The way you can achieve that is to enumerate the worksheets of every workbook and copy the contents to another workbook.

Lets say you have three workbooks that you retrieved from the database - workbook1, workbook2, workbook3. Here is some code that will copy all worksheets from one workbook and add them to another:

private void CopyWorkSheetsTo(Workbook sourceWorkbook, Workbook destinationWorkbook)
        {
            foreach (Worksheet worksheet in sourceWorkbook.Worksheets)
            {
                Worksheet lastAddedWorksheet = destinationWorkbook.Worksheets.Add();
                CellRange usedCellRange = worksheet.UsedCellRange;
                WorksheetFragment fragment = worksheet.Cells[usedCellRange].Copy();
                lastAddedWorksheet.Cells[0, 0].Paste(fragment, PasteOptions.All);
            }
        }

 Using this method the following code will do that for all of the three workbooks:

Workbook workBookCombined = new Workbook();
workBookCombined.History.IsEnabled = false;
workBookCombined.SuspendLayoutUpdate();
this.CopyWorkSheetsTo(workBook1,workBookCombined);
this.CopyWorkSheetsTo(workBook2, workBookCombined);
this.CopyWorkSheetsTo(workBook3, workBookCombined);
workBookCombined.ResumeLayoutUpdate();
workBookCombined.History.IsEnabled = true;

this.radSpreadsheet.Workbook = workBookCombined;

To get better performance you can suspend layout update and disable the history stack. On the last line you just have to assign the new workbook to the RadSpreadsheet.

Hope that helps.
Kind regards,
Andrew
the Telerik team

Workbook workBookCombined = new Workbook();

            workBookCombined.History.IsEnabled = false;

            workBookCombined.SuspendLayoutUpdate();

            this.CopyWorkSheetsTo(workBook1, workBookCombined);

            this.CopyWorkSheetsTo(workBook2, workBookCombined);

            this.CopyWorkSheetsTo(workBook3, workBookCombined);

            workBookCombined.ResumeLayoutUpdate();

            workBookCombined.History.IsEnabled = true;

 

            this.radSpreadsheet.Workbook = workBookCombined;




Workbook workBookCombined = new Workbook();

            workBookCombined.History.IsEnabled = false;

            workBookCombined.SuspendLayoutUpdate();

            this.CopyWorkSheetsTo(workBook1, workBookCombined);

            this.CopyWorkSheetsTo(workBook2, workBookCombined);

            this.CopyWorkSheetsTo(workBook3, workBookCombined);

            workBookCombined.ResumeLayoutUpdate();

            workBookCombined.History.IsEnabled = true;

 

            this.radSpreadsheet.Workbook = workBookCombined;




[Mark]

This is what I had to do to preserve the cell width
       

private void CopyWorkSheetsTo(Workbook sourceWorkbook, Workbook destinationWorkbook)
{
    var pasteOptions = new PasteOptions(
                                        PasteType.ColumnWidths |
                                        PasteType.Formats |
                                        PasteType.Formulas |
                                        PasteType.Values |
                                        PasteType.FormulasAndNumberFormats |
                                        PasteType.ValuesAndNumberFormats);

    foreach (Worksheet ws in sourceWorkbook.Worksheets)
    {
        Worksheet lastAddedWorksheet = destinationWorkbook.Worksheets.Add();
        lastAddedWorksheet.Name = ws.Name;

        CellRange usedCellRange = ws.UsedCellRange;
        WorksheetFragment fragment = ws.Cells[usedCellRange].Copy();

        lastAddedWorksheet.Cells[0, 0].Paste(fragment, pasteOptions);

        //-------------------------
        lastAddedWorksheet.DefaultColumnWidth = ws.DefaultColumnWidth;
        lastAddedWorksheet.DefaultRowHeight = ws.DefaultRowHeight;

        CellRange lastCellRange = lastAddedWorksheet.UsedCellRange;
        ColumnSelection newColumnSelection = lastAddedWorksheet.Columns[lastCellRange];
        newColumnSelection.ClearWidth();

        ColumnSelection usedColumnSelection = ws.Columns[usedCellRange];
        newColumnSelection.SetWidth(usedColumnSelection.GetWidth().Value);

        for (int col = 0; col < usedCellRange.ColumnCount; col++)
        {
            usedColumnSelection = ws.Columns[col];
            newColumnSelection = lastAddedWorksheet.Columns[col];
            newColumnSelection.ClearWidth();
            newColumnSelection.SetWidth(usedColumnSelection.GetWidth().Value);
        }
    }
}
        //-------------------------------------------------------------------------------
        private void Initialize()
        {
            this.workbook = new Workbook();

            this.workbook.SuspendLayoutUpdate();
            this.workbook.History.IsEnabled = false;
        }

        //-------------------------------------------------------------------------------
        public void CombineWorkbooks(ExcelPackage source1, ExcelPackage source2)
        {
            this.workbook.SuspendLayoutUpdate();
            this.workbook.History.IsEnabled = false;

            this.CopyWorkSheetsTo(source1.workbook, workbook);
            this.CopyWorkSheetsTo(source2.workbook, workbook);

            this.workbook.History.IsEnabled = true;
            this.workbook.ResumeLayoutUpdate();
        }

[Nikolay Demirev]

Hi Mark,

The RadSpreadsheet behaves like MS Excel in the case of Copy and Paste. If you copy something and paste it the entire content is pasted except column widths. That is why you have to paste the column widths separately. Here is the modified CopyWorksheetsTo method:

private void CopyWorkSheetsTo(Workbook sourceWorkbook, Workbook destinationWorkbook)
{
    foreach (Worksheet ws in sourceWorkbook.Worksheets)
    {
        Worksheet lastAddedWorksheet = destinationWorkbook.Worksheets.Add();
        lastAddedWorksheet.Name = ws.Name;

        CellRange usedCellRange = ws.UsedCellRange;
        WorksheetFragment fragment = ws.Cells[usedCellRange].Copy();

        lastAddedWorksheet.Cells[0, 0].Paste(fragment, new PasteOptions(PasteType.All));
        lastAddedWorksheet.Cells[0, 0].Paste(fragment, new PasteOptions(PasteType.ColumnWidths));

        //-------------------------
        lastAddedWorksheet.DefaultColumnWidth = ws.DefaultColumnWidth;
        lastAddedWorksheet.DefaultRowHeight = ws.DefaultRowHeight;
    }
}

Regards,
Nikolay Demirev
Telerik