Heading style names are lost when opening and saving xlsx file

[DIPAD-Fran-Bryden]

Describe the bug When opening an existing xlsx file containing header styles in cells and then resaving it, the appearance of the cell is preserved, but the naming of this style as a header style (e.g. Heading 1 or Heading 2) is lost. This is necessary for accessibility of xlsx documents with technology such as screen readers.

To Reproduce

1. Create an Excel document containing text.
2. Format a cell as a heading (Home -> Styles -> Cell Styles -> "Heading 2")
3. Save the Excel file.
4. Load Excel file in to R as a workbook and save back out without editing:

library(openxlsx)

wb <- loadWorkbook("test_heading.xlsx")

openxlsx::saveWorkbook(wb, "test_heading.xlsx")

5. Text appears with the same formatting appearance (bold, font size, etc) but is no longer recognised as the Heading 2 style.

Expected behavior Would expect to retain both the formatting appearance and recognise this as Heading 2 style.

Example files See test_heading.xlsx file to run example code.

Additional context Looking at the underlying XML of the Excel files before and after, the Heading 2 style is being removed from the formatting:

Relevant XML prior to read: <cellStyles count="2"><cellStyle name="Heading 2" xfId="1" builtinId="17"/><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>

Relevant XML after read: <cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>

[JanMarvin]

Hi @DIPAD-Fran-Bryden , afaik the information is simply not available in openxlsx style objects. The entire styles.xml file is imported into style objects and exported when saving. The style name is in <cellstyles>... or similar, which is not necessary for openxlsx. I guess it would be possible to add this, but might require a larger patch. You can have a look at the in development package openxlsx2, which has a different logic of handling styles. It should preserve all styles.

Edit: Didn't want to close this, even though it's unlikely to be picked up.

[DIPAD-Fran-Bryden]

Cheers @JanMarvin, really appreciate the helpful response! Will check out openxlsx2 as an alternative 😺

[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.