Duplicating entire sheet

[Dexter]

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!

[Anna]

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

 

 

[Dexter]

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.

[Deyan]

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

 

 

[Dexter]

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?

[Anna]

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

 

 

[Dexter]

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.