[Bug]: Large sample data leads to corrupt spreadsheet file. Smaller sample data OK

[W77587]

OData Version

OData V4

Draft

Yes

Scenario

List Report

Environment

BAS

UI5 Spreadsheet Component

0.31.3

What happened?

Hello! Thanks for this tool.

When passing a large sampleData array, the generated template file is corrupt. We first thought it was the data we were passing into sampleData but then replaced it with a synthetic array and noticed that we can pass 400 records to it, but when we try 1000 records it generates a corrupt file. Anything beyond 1000 leads to a corrupt excel file. I've debugged your component and don't see any issue with the sample data workbook generation so maybe the issue is on sheetjs when book_append_sheet is called?

This is the quick and dirty code we use to generate sample data.

generateSampleData: function() {
          // const len = 400; works
          const len = 1000; // fails
          // const len = 2000; fails
          const res = [];
          for (let i = 0; i < len ; i++) {
            res.push({
              A: "1001",
              B: "10000",
              C: `${i}`,
              D: "0000",
              E: "2023",
              F: "A",
              G: "",
              H: "",
              i: "",
              J: "",
              K: "",
              L: "",
              M: "",
              N: "",
              O: "",
              P: "",
              Q: "",
              R: "",
              S: ""
            });

          }
          return res;
        },

We have confirmed the columns are matching with what the component expects.

We have debugged onTempDownload and it follows a predictable path: No spreadsheet template file, but sampleData so iterates on sample data, generates a cell for each one (all properties in this entityset are strings) and then writes the file.

We have also tried passing { compression: true } when it is calling XLSX.writeFile as the third opts argument but that didn't help. In any case probably something to enable to make the files shorter.

Thank you

Relevant log output

Nothing in the logs.

Spreadsheet Component Init

let sampleData = this.generateSampleData();

await sap.ui.core.Component.create({
  name: "excelUploadObjectInfo",
  url: sPrefix + "/ui5-cc-spreadsheetimporter/webapp",
  componentData: {
    fieldMatchType: "label",
    context: this.base,
    activateDraft: true,
    strict: false,
    tableId: "__redacted__::LineItem-innerTable",
    spreadsheetFileName: "myfile.xlsx",
    hidePreview: true,
    hideSampleData: false,
    sampleData: sampleData,
    columns: this._getColumnsForExcelEdit()
  }
})

### Manifest

```json
We don't have them. We load from BTP URL once it is deployed.

[marianfoo]

Hi @W77587 , interessting bug report. I didn't think that more than a few lines would be used there and therefore didn't test any more. Just out of interest, why is so much sample data needed?

Lots to do right now. I'll have to see when I get around to it

[marianfoo]

Hi @W77587 I have tried to reproduce the error but unfortunately have not succeeded. I was able to add 2000 lines without any problems and the excel file is fine.

Which browser do you use?

Can you try it with a minimal example, i.e. just pass the context and the sampleData.

Alternatively, you can also provide an Excel file directly with https://docs.spreadsheet-importer.com/pages/Configuration/#spreadsheettemplatefile

[marianfoo]

Hi @W77587, are you able to provide are better reproducable example?

[marianfoo]

Hi @W77587 i´m closing for now. You can reopen it if you have more information for me.