file is corrupted when file size exceeds '1000kb'

[craig-harbottle]

Hi @ghiscoding 👋 , me again 😅 . I get this when an excel file exceeds 1000kb :

And then when I open it, the styles and formatting has been 'thrown away' but the data 'recovered'.

Have you seen this before ?

Had a look through the repo and couldn't see anywhere a limit would have been set.

Set this as a discussion as I'm sure it might be a PEBCAK.

Cheers

[ghiscoding]

nope never had any problems like this and I've been using it for well over a year in my other open source projects without anyone complaining, so it's probably an issue in your code somewhere.

I just tried in my datagrid open source project via this Example, if you go over there and try it, then click on the "50K rows" button and then click on export to Excel. It's a bit slow to get the export but it works and I checked the size and it ended up being 2Kb.

The only other thing I could think of though is that I never use or insert any images in my exports (it's rarely used for datagrid exports), could that be related to that?

[craig-harbottle]

Seems to be the fill property, e.g.

I'm looping through my data and applying a different fill to alternate rows :

const style = stylesheet.createFormat({
        alignment: {
          horizontal: 'left',
          vertical: 'center'
        },
         fill: {
         type: 'pattern',
         patternType: 'solid',
       
        fgColor: i % 2 === 0 ? 'FFfff' : 'FFf2f2f2'
         },
        format: column.downloadFormatKey
      }).id


      datum.push({
        value: value === undefined ? '' : value,
        metadata: {
          style
        }
      });

With the property, the file becomes broken. Without it, it's fine.

Can you see anything iffy here ?

Perhaps you could try applying alternate fills to the rows in your example to see if it works / breaks for you ?

Cheers

[ghiscoding]

well a quick look at it and this 'FFfff' is wrong, it's missing a char and it's also missing the 2 extra chars prefix that all colors need to have in order to work in Excel, in other words a color should always be 8 chars long and this one is certainly not.

[craig-harbottle]

sorry, that's a typo. It should be this FFfffff. That works when I throttle the data so it doesn't exceed 1000kb. And isn't FF the prefix? That replaces the # (from https://ghiscoding.gitbook.io/excel-builder-vanilla/cookbook/background-fillers.

What also seems peculiar is just an empty object set as the value for the fill property will result in the file being corrupted, e.g.

fill: {
}

[ghiscoding]

And isn't FF the prefix?

Yes that is what it seems, as per the docs:

Note: HTML color requires the # prefix to be escaped as FF, for example the HTML color #0000FF (blue) must be converted to FF0000FF

What also seems peculiar is just an empty object set as the value for the fill property will result in the file being corrupted, e.g.

but why would you add an empty fill object? That seems wrong and possibly looking for troubles. Also make sure to create your format only once and before looping through the data, formats and others should be created on the top before looping.

Also, the thing is that this project comes from a fork of the original excel-builder project and I don't (and probably never will) know all the intricacies of this project and how it deals with Excel file creation (it needs a bunch of XML files with specific content). All I really did here is to keep the project alive and make it a bit more modern by dropping old libraries that are no longer needed (like Q for Promises). So I'm afraid that I can't help much into understanding why and how it fails. You should read what the original author himself wrote about this creation of Excel files, in short it's pretty hard to understand and to put everything together, see introduction doc about what he wrote.

You can clone the project and try to debug it yourself if possible, the debugger is already setup and works properly if you use VSCode to debug, so that is probably the best we can do to troubleshoot this kind of problem