xlsx-populate: Applying a lot of styles causes spreadsheet to need repair

When applying a ton of styles to a large amount of cells in a workbook, I then get the message “Excel could not open filename because some content is unreadable. Do you want to open and repair this workbook?”

As an example, I am using this code:

lineItemSheet.range(1, 2, lineItemRowOffset + 1, 2).style('numberFormat', 'mm/dd/yy');
lineItemSheet.range(1, 3, lineItemRowOffset + 1, 3).style('numberFormat', 'h:mm AM/PM');
lineItemSheet.range(1, 13, lineItemRowOffset + 1, 21).style('numberFormat', '$#,##0.00');

lineItemRowOffset in this case is around 15,000 so styles are being applied to tens of thousands of cells.

Is there a better way to apply styles or is this a bug?

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 19 (8 by maintainers)

Most upvoted comments

For anyone interested: if you need per-column custom styles, there’s an easy way to prevent style overflow. I left a comment in this issue #188

Sleeper9 on Jan 25, 2022

Hmmm… it seems that Excel has an undocumented limit of about 65k styles. xlsx-populate currently creates a new style for each cell, which isn’t the most efficient. There are 3 style items on the roadmap that each should help with your problem (in order of priority):

Because a style is created per cell, there are many duplicate styles in place. Instead we should be reusing nodes across styles if they are identical. That’s a little tricky to do, but it’s the right approach.
We currently create font, border, etc. nodes per style even if they aren’t used. We’d save some space in the XML output by not emitting those nodes if they are empty.
Setting row/column default styles. Then you could do something like sheet.column('A').style('numberFormat', 'mm/dd/yy') to apply the style to all of the cells in the column.

Unfortunately, none of these are in place right now. The only suggestion I have for the moment is to style a workbook in Excel, save it in your repo, and then populate the template with xlsx-populate.

dtjohnson on Apr 1, 2017