Combine workbooks

4 posts, 0 answers
  1. Adrian
    Adrian avatar
    7 posts
    Member since:
    Jan 2013

    Posted 09 Apr 2013 Link to this post

    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!!
  2. Andrew
    Admin
    Andrew avatar
    140 posts

    Posted 10 Apr 2013 Link to this post

    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

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.


    Workbook workBookCombinednew 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 workBookCombinednew 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;

  3. DevCraft banner
  4. Mark
    Mark avatar
    1 posts
    Member since:
    Jun 2014

    Posted 12 Nov 2014 Link to this post

    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();
            }
            
  5. Nikolay Demirev
    Admin
    Nikolay Demirev avatar
    103 posts

    Posted 14 Nov 2014 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
Back to Top