Formatting Attributes Applied to Entire Columns not Carrying Over to Saved Workbook

[mendy-friedman]

Describe the bug If you read in a workbook with openxlsx, and it includes formatting that is applied to an entire column/row (for example, the entire column is shaded a certain color, or all columns from column X to the end of the workbook are hidden), this formatting is stripped upon saving the workbook out again.

To Reproduce Initial file: test.xlsx

I read this in and saved it out again, and got this file. As you can see, all columns are unhidden and A:F on the Sheet4 tab are no longer gray all the way down the sheet. (Hidden tabs also unhide, but this is already called out in a separate open issue: https://github.com/ycphs/openxlsx/issues/279#issue-1036796632). test-output - 4.2.4.xlsx

Expected behavior The formatting should be preserved upon loading and saving the workbook.

[JanMarvin]

Confirmed. We are somehow losing a style.

Yours looks like this:

<fills count="4">
  <fill>
    <patternFill patternType="none"/>
  </fill>
  <fill>
    <patternFill patternType="gray125"/>
  </fill>
  <fill>
    <patternFill patternType="solid">
    <fgColor theme="6" tint="0.59999389629810485"/>
    <bgColor indexed="64"/>
    </patternFill>
  </fill>
  <fill>
    <patternFill patternType="solid">
    <fgColor theme="6"/>
    <bgColor indexed="64"/>
    </patternFill>
  </fill>
</fills>

Ours looks like this:

<fills count="3">
  <fill>
    <patternFill patternType="none"/>
  </fill>
  <fill>
    <patternFill patternType="gray125"/>
  </fill>
  <fill>
    <patternFill patternType="solid">
    <fgColor theme="6"/>
    <bgColor indexed="64"/>
    </patternFill>
  </fill>
</fills>

Edit: it might be something else. We're ignoring quite a few xml nodes from styles. Most likely because nobody had the time or need to understand them.

[JanMarvin]

I dug into the code and assume I've found the issue.

The loadworksheets() function checks if the worksheet contains data.

https://github.com/ycphs/openxlsx/blob/f890014ee4fc31bce2bf8cb5daf8229ba9bec350/src/load_workbook.cpp#L58

The worksheet you provide in the example does not contain data. Therefore the styleObjects for the empty sheet are never evaluated and because of this the sheet loses the styles of the columns and rows.

https://github.com/ycphs/openxlsx/blob/f890014ee4fc31bce2bf8cb5daf8229ba9bec350/src/load_workbook.cpp#L678

Unless someone comes around and picks up the task to rewrite this main driver, to handle the corner case of empty sheets, I doubt that this will get fixed. I'm not going to be that someone, I've got better things to do :)

[JanMarvin]

Oh and the rest goes away with something like this (this recreates what the original xml file looks like). Only other issue is that it's entirely dark gray, because of the issue mentioned in the comment above (and because of the problem, that we do not import the row attributes):

library(openxlsx)
fl <- "gh_issue_280.xlsx" # your example

wb <- loadWorkbook(fl)

wb$worksheets[[3]]$dimension <- "<dimension ref=\"A1:J2\"/>" # should have fixed this a long time ago
wb$worksheets[[3]]$sheetFormatPr <- "<sheetFormatPr defaultColWidth=\"0\" defaultRowHeight=\"15\"/>" # this should be imported, but somehow isn't
wb$worksheets[[3]]$cols <- c(
  "<col min=\"1\" max=\"10\" width=\"9.140625\" style=\"1\" customWidth=\"1\"/>",
  "<col min=\"11\" max=\"16384\" width=\"9.140625\" hidden=\"1\"/>"
) # looks like another fancy problem around the corner at least I now understand this cols tag

saveWorkbook(wb, "test3.xlsx", overwrite = TRUE)

Long story short, there are a few issues, some can be circumvented and possibly should be circumvented, but others are rather unlikely to be fixed.

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.