Clone sheet and populate data



  • Hello,

    In my excel template, sheet1 is the worksheet i wanted to clone since it has all the formatting and conditional formatting and data validation already builtin in excel.

    I was following this - https://playground.jsreport.net/w/ARCHAMO/GaOh4rMZ
    My problem is that if want to clone with the same attributes, i changed this

        {{#xlsxAdd "xl/_rels/workbook.xml.rels" "Relationships.Relationship"}}
           <Relationship Id="sheet{{id}}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
        {{/xlsxAdd}}
    

    With this code, i can see the newly created worksheets are exaclty cloned but there was no data in it.

    If i have this

        {{#xlsxAdd "xl/_rels/workbook.xml.rels" "Relationships.Relationship"}}
           <Relationship Id="sheet{{id}}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet{{id}}.xml"/>
        {{/xlsxAdd}}
    

    The new worksheet is created and the data is populated but the attributes from sheet1 are not retained.

    Any help is appreciated.



  • The new worksheet is created and the data is populated but the attributes from sheet1 are not retained.

    What attributes do you mean? Could you provide an example?



  • Thanks for your response.

    It essentially the row1 header with all the autofilters and A1 column with all the conditional formatting and data validation, as well as the freeze pane from Column A to Column B as you can see

    0_1685038257174_upload-a4bfc745-b65e-4311-a8c8-d5a246c8b66f

    Also, I have noticed too that if im using this
    {{#xlsxAdd "xl/_rels/workbook.xml.rels" "Relationships.Relationship"}}
    <Relationship Id="sheet{{id}}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>

    {{/xlsxAdd}}
    

    Im getting the following error

    0_1685045054511_upload-d464b827-1010-4628-bf81-fc0301b100b5



  • I see yes.
    The thing is that in this case, the sheet includes references to other files that you need to clone as well.
    For example, the filters are defined in the xl/tables xmls. So you need to clone this as well and update the references.
    I know its tedious work, but we don't have API that could do this for you at this moment.
    You gonna need to unzip the xlsx and check the references in worksheets/sheet*.xml and clone the references xmls as well.


Log in to reply
 

Looks like your connection to jsreport forum was lost, please wait while we try to reconnect.