Propagating document page properties through web spreadsheet download

[Richard]

Propagating Page properties of a server generated "Telerik.Windows.Documents.Spreadsheet.Model.Workbook" in a web rendered "Telerik.Web.Spreadsheet.Workbook"

 

Heads up, this is only for licensed developers who can access the commercial library source code.

 

At present the documents.spreadsheet workbook has a richer capacity than a web.spreadsheet workbook, especially around page setting.  I don't know the roadmap, but expect at some point the web Spreadsheet component will take on a richer feature set that aligns with Excel application.

A Telerik document workbook is converted to it's web version for viewing in a browser by using the FromDocument() method.  The features that are lost from the sheets of the original document workbook include:

• page orientation
• margins
• scaling
• headers and footers

The listed items are those I wanted to deal with, there may be more but they were of no concern to me at this time.

My project has a Controller/ViewModel

Telerik.Windows.Documents.Spreadsheet.Model.Workbook serverSideModel;
... lots of serverSideModel method calls that create a spreadsheet, including page settings ...
return View(serverSideModel);

 

and a View

@model Telerik.Windows.Documents.Spreadsheet.Model.Workbook
...
@(
   Html.Kendo().Spreadsheet()
   .Name("mySpreadsheet")
   .BindTo(Telerik.Web.Spreadsheet.Workbook.FromDocument(Model))  //  <--- convert to web version for viewing in browser
   ...
   )

 

At this point the Spreadsheet component in the browser will display the spreadsheet that was created server side.

The component has a download button and you can select to download as an .xlsx.

The downloaded Excel will have lost any page properties that were in the original server side Telerik document.  This is because the internal javascript for creating the excel download does not render any Excel xml related to page layout.

 

Why not?
Because web spreadsheet model does not implement page setup features. The FromDocument() conversion works, but all the page properties info in the document spreadsheet is ignored.

How can this be remedied ?
1. Explicitly add the original pageSetup features to the converted object.
2. Modify library source code to render pageSetup features in the Excel xml that becomes the download.

 

Step 1.
After the web spreadsheet (as a FromDocument) is rendered, the document page properties can be explicitly reattached by code in a kendo.syncReady funtion.
For example, at bottom of the View, add a script block:

<script>
    kendo.syncReady(function () {

        var book = $("#mySpreadsheet").data("kendoSpreadsheet");

        @for (var index=0; index < Model.Sheets.Count; index++)
        {
            var sheet = Model.Worksheets[index];
            var pageSetup = sheet.WorksheetPageSetup;
            var left = (pageSetup.Margins.Left > 4) ? UnitHelper.DipToInch(UnitHelper.PointToDip(pageSetup.Margins.Left)) : pageSetup.Margins.Left;
            var right = (pageSetup.Margins.Right > 4) ? UnitHelper.DipToInch(UnitHelper.PointToDip(pageSetup.Margins.Right)) : pageSetup.Margins.Right;
            var top = (pageSetup.Margins.Top > 4) ? UnitHelper.DipToInch(UnitHelper.PointToDip(pageSetup.Margins.Top)) : pageSetup.Margins.Top;
            var bottom = (pageSetup.Margins.Bottom > 4) ? UnitHelper.DipToInch(UnitHelper.PointToDip(pageSetup.Margins.Bottom)) : pageSetup.Margins.Bottom;
            var header = (pageSetup.Margins.Header > 4) ? UnitHelper.DipToInch(UnitHelper.PointToDip(pageSetup.Margins.Header)) : pageSetup.Margins.Header;
            var footer = (pageSetup.Margins.Footer > 4) ? UnitHelper.DipToInch(UnitHelper.PointToDip(pageSetup.Margins.Footer)) : pageSetup.Margins.Footer;
            var orientation = (pageSetup.PageOrientation == Telerik.Windows.Documents.Model.PageOrientation.Landscape) ? "Landscape" : "Portrait";
            var scale = pageSetup.ScaleFactor.Width * 100;@* convert from telerik scaleFactor to excel scale % *@
        <text>
        book.sheets()[@index].showGridLines(false);
        book.sheets()[@index].pageSetup = {
                viewTags: 'view="pageBreakPreview" zoomScaleNormal="85" zoomScaleSheetLayoutView="100"',  // I know the innards of Excel xml enough to to this.  I did not want to deal making changes in the library source that handled view, zoomScalNormal and zoomScaleSheetLayoutView as actual properties
                margins: {
                    left: @left,
                    right: @right,
                    top: @top,
                    bottom: @bottom,
                    header: @header,
                    footer: @footer},
                orientation: "@(orientation.ToLower())",
                scale: @scale,
                headerFooterSettings: {
                    header: {
                        leftSection: {
                            text: "@pageSetup.HeaderFooterSettings.Header.LeftSection.Text"
                        },
                        centerSection: {
                            text: "@pageSetup.HeaderFooterSettings.Header.CenterSection.Text"
                        },
                        rightSection: {
                            text: "@pageSetup.HeaderFooterSettings.Header.RightSection.Text"
                        }
                    },
                    footer: {
                        leftSection: {
                            text: "@pageSetup.HeaderFooterSettings.Footer.LeftSection.Text"
                        },
                        centerSection: {
                            text: "@pageSetup.HeaderFooterSettings.Footer.CenterSection.Text"
                        },
                        rightSection: {
                            text: "@pageSetup.HeaderFooterSettings.Footer.RightSection.Text"
                        }
                    }
                }

        };
        </text>
        }
@*
        replace standard download button response of a Export... dialog
        with a direct download as Excel
*@
        $("#mySpreadsheet .k-i-download")
            .parent(".k-button")
            .unbind()
            .on("click", function (e) {
                $("#mySpreadsheet").data("kendoSpreadsheet").saveAsExcel();
            return false;
        });
    });
</script>

 

Step 2. Modify kendo.all.js

This is for licensed developers only.

Patch your licensed copy of the commercial library source code (v 2017.3.1026) with the attached patch file.

The view must include the modified library code that has been copied into your asp-net mvc project, and can not use the minified cdn version.  The original licensed kendo-ui complete library source code can be downloaded from your account login.

Myview.cshtml

...
    <script src="~/Scripts/kendo/2017.3.1026/js/kendo.all.js"></script>  // modified version contains new magic
...

V

 Now when you download your richly featured server side generated spreadsheet from the view it will still contain those rich features.
   

[Veselin Tsvetanov]

Hi Richard,

Thank you for your input on the Kendo Spreadsheet xlsx export functionality. I believe, that your contribution will be helpful for other developers too.

As a small token of gratitude for sharing your implementation with the community, I have updated your Telerik points.

Regards,
Veselin Tsvetanov
Progress Telerik