corrupted excel if template pivot created with add to data model

Issue #1247 new
Antti Kärki
created an issue

If I create pivot table into my template, then I can load and open...no problem BUT If I create pivot table into my template and check Add this data to the Data Model in pivot creation, then I can't open the excel anymore : Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view) Removed Records: Workbook properties from /xl/workbook.xml part (Workbook) Pivot table is plain text

I have tried to put #refresh pivot for ws in workbook.worksheets: for pi in ws._pivots: pi.cache.refreshOnLoad = True that helps with "ordinary" pivot but not with pivot with data model

Antti

Comments (10)

  1. Antti Kärki reporter

    This is how I load the template (the fields in this are from Odoo):

        if self.excel_template:
            templatefile = self.template_name.strip()
            template_end = templatefile[templatefile.rfind('.', 0, len(templatefile)):]
            file_end = filename[filename.rfind('.', 0, len(filename)):]
            if not file_end == template_end:
                filename = filename.replace(file_end, template_end)
                self.file_name = filename
    
            output = io.BytesIO()
            file = base64.b64decode(self.excel_template)
            excel_fileobj = TemporaryFile('w+b')
            excel_fileobj.write(file)
            excel_fileobj.seek(0)
            if template_end == '.xlsm' or template_end == '.xltm':
                workbook = openpyxl.load_workbook(excel_fileobj, keep_vba=True)
            else:
                workbook = openpyxl.load_workbook(excel_fileobj)
        else:
            # generate empty excel
            output = io.BytesIO()
            workbook = Workbook()
    

    Then I do:

        for ws in workbook.worksheets:
            for pi in ws._pivots:
                pi.cache.refreshOnLoad = True
        #refresh pivot
        for pi in workbook._pivots:
            pi.cache.refreshOnLoad = True
    
        # save the virtual workbook
        workbook.save(output)
        self.excel_report = base64.b64encode(output.getvalue())
    
  2. Antti Kärki reporter

    During execution I remove existing sheet , then create new one. The tables I define like -- define table column_letter = get_column_letter(j) _reference = 'A1:'+column_letter+str(i+1) tab = Table(displayName=worksheet.title, ref=_reference) # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style worksheet.add_table(tab)

    that because I don't know how to update table range for existing tables. When I create the pivot model, I select the table and then Add to model...

  3. CharlieC

    Sorry, but I don't understand the question. I haven't had a chance to look at the file yet but the code looks like it just sets refreshOnLoad to True.

  4. Antti Kärki reporter

    I tried to load from file excelin.xlsx the saved it to excelout.xlsx with openpyxl load and save commands ( I did nothing else)

    Then I opened both excels with 7-zip. I noticed that at least 'xl/model/item.data' file is missing from the archive. Well I tried to add that (read from the in file and wrote to new one with ZipFile functions) but that didn't help....it's still corrupted

  5. Log in to comment