Duplicating entire sheet

7 posts, 1 answers
  1. Dexter
    Dexter avatar
    34 posts
    Member since:
    Dec 2012

    Posted 29 Apr 2015 Link to this post

    Hello, is there any way to duplicate an entire sheet completely to another sheet within the same SpreadSheet? I have a sheet with merged cells, different row heights and row widths everywhere. Cells.Copy and Paste doesn't seem to be enough.

    Thanks for your attention!
  2. Anna
    Admin
    Anna avatar
    99 posts

    Posted 01 May 2015 Link to this post

    Hello Dexter,

    Unfortunately, at this point we don't have a method to duplicate an entire worksheet and Copy and Paste is the only way to achieve this. Can you tell me what issues you encounter when you try copy and paste? Perhaps it would be best if you could send us the problematic file so that we can investigate in more detail.

    In the meantime, we have an item in our Feedback Portal about implementing methods for copying an entire worksheet, which you can like and follow in order to receive any updates on the matter.

    Regards,
    Anna
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  3. UI for WPF is Visual Studio 2017 Ready
  4. Dexter
    Dexter avatar
    34 posts
    Member since:
    Dec 2012

    Posted 02 May 2015 in reply to Anna Link to this post

    Hi Anna, yes the one in the feedback portal is what I need.
    The problem with Copy and Paste is it doesn't seem to do everything I need like rows height and column width. Is there any workaround for this? Basically I have a fixed worksheet template prepared. However, I do not know how many I need, so it will be generated as many as needed during runtime.
  5. Deyan
    Admin
    Deyan avatar
    135 posts

    Posted 06 May 2015 Link to this post

    Hello Dexter,

    When you copy and paste worksheet content, the row heights and column widths are preserved unchanged. Until we implement the functionality for copying entire worksheets, you can workaround this behaviour by simply setting the row heights and column widths after the paste operation is performed. The following code snippet shows a sample implementations of methods that copy the widths and heights from one worksheet to another:
    private static void CopyColumnWidths(Worksheet fromWorksheet, Worksheet toWorksheet)
    {
        int columnsCount = fromWorksheet.UsedCellRange.ColumnCount;
     
        for(int column = 0; column < columnsCount; column++)
        {
            toWorksheet.Columns[column].SetWidth(fromWorksheet.Columns[column].GetWidth().Value);
        }
    }
     
    private static void CopyRowHeights(Worksheet fromWorksheet, Worksheet toWorksheet)
    {
        int rowsCount = fromWorksheet.UsedCellRange.RowCount;
     
        for (int row = 0; row < rowsCount; row++)
        {
            toWorksheet.Rows[row].SetHeight(fromWorksheet.Rows[row].GetHeight().Value);
        }
    }

    I hope this is helpful. If you have any other questions or concerns please do not hesitate to contact us again.

    Regards,
    Deyan
    the Telerik team
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  6. Dexter
    Dexter avatar
    34 posts
    Member since:
    Dec 2012

    Posted 08 May 2015 in reply to Deyan Link to this post

    Thanks a lot for your help! That does work, however I found out 1 more obstacle to getting exactly what I need.
    When I print or export to PDF any run-time generated sheets, they are oversized. Only my first sheet (which is prepared in actual Excel) is printed or exported to PDF correctly.
    I have the following codes when generating new sheets, to which none helped:
    worksheets[index].WorksheetPageSetup.Margins = new PageMargins(0, 0, 0, 0);
    worksheets[index].WorksheetPageSetup.CenterVertically = true;
    worksheets[index].WorksheetPageSetup.CenterHorizontally = true;
    worksheets[index].WorksheetPageSetup.PageOrder = PageOrder.DownThenOver;
    worksheets[index].WorksheetPageSetup.PageOrientation = PageOrientation.Portrait;
    worksheets[index].WorksheetPageSetup.PaperType = PaperTypes.A4;

    Here are my printing and export to PDF codes:

    PrintQueue pq = LocalPrintServer.GetDefaultPrintQueue();
                pq.UserPrintTicket.PageOrientation = System.Printing.PageOrientation.Portrait;
                pq.UserPrintTicket.PageMediaType = PageMediaType.Plain;
                pq.UserPrintTicket.PageMediaSize = new PageMediaSize(PageMediaSizeName.ISOA4);
                var pd = new PrintDialog
                {
                    PrintQueue = pq
                };
     
    for (int i = 0; i != currWSIndex + 1; i++)
                {
                    //setting no. of sheets (total)
                    worksheets[i].Cells[7, 8].SetValue(currWSIndex >= 9 ? "" : "0" + (currWSIndex + 1));
                    sheet1211.ActiveSheet = worksheets[i];
                    sheet1211.Print(new PrintWhatSettings(ExportWhat.ActiveSheet, false), pd);
                }
    var pdf = new PdfFormatProvider
                        {
                            ExportSettings = new PdfExportSettings(ExportWhat.EntireWorkbook, false)
                        };


    Note that the printing results look totally exactly like the PDF. The only thing I can think of is that, at the real Excel where I saved the xlsx template, there's a print option "shrink sheet to 1 page". I thought this should applies to the workbook such that when I generate new sheets at runtime, this print option will also apply. Is there a workaround or am I missing anything else?

  7. Answer
    Anna
    Admin
    Anna avatar
    99 posts

    Posted 08 May 2015 Link to this post

    Hello,

    While I am unable to verify this without having the actual file, most likely the problem is caused by a different scale factor of the worksheets. If your initial worksheet is shrunk to fit the page, its scale factor would be different from 100%. The scale factor is not copied during copy and paste, so your final result would be worksheets with different scale factors. You can find the ScaleFactor property in the WorksheetPageSetup class and you can compare the value of the first worksheet and that of the others and adjust them accordingly.

    As a side note, we have an item in our Feedback portal regarding the Fit to Page functionality. If you are interested in it, you can like and follow the item in order to receive any updates there might be on its development.

    Regards,
    Anna
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

     
  8. Dexter
    Dexter avatar
    34 posts
    Member since:
    Dec 2012

    Posted 08 May 2015 in reply to Anna Link to this post

    Thanks Anna, that solved the problem!

    I have experimented with ScaleFactor before, but I was messing with the wrong one: ActiveSheetEditor.ScaleFactor, which only affects zooming in / out of the UI.
Back to Top
UI for WPF is Visual Studio 2017 Ready