Excel custom styles deleted

[ksak3]

Expected Behavior

Load and save a workbook without unrequested changes taking place

Actual Behavior

When I load and save a workbook my custom style is removed from my workbook.

Steps to Reproduce the Problem

1. In any xlsx file, apply some formatting, say fill and border to some cell(s). Select Cell Styles from Home menu, then New Cell Style.... Name the style if desired then click OK. Now when you select Cell Styles your custom style should appear.

2. In R: wb = loadWorkbook(xlsxFile = "~/testwb.xlsx") saveWorkbook(wb, file = "~/testwb2.xlsx")

3. Open your output workbook and check style menu for your custom style.

sessionInfo()

• Version of openxlsx: 4.0.17
• Version of R: 3.3.1

[awalker89]

Have you tried the latest dev version from github? Do the examples in ?loadWorkbook work for you?

[ksak3]

I installed the version from github and examples I get new error messages (the workbook was already loaded from testing before re-installing package):

R version 3.4.3 (2017-11-30) -- "Kite-Eating Tree"
Copyright (C) 2017 The R Foundation for Statistical Computing
Platform: x86_64-w64-mingw32/x64 (64-bit)

> packageVersion("openxlsx")
[1] ‘4.0.33’
> wb <- loadWorkbook(file = system.file("loadExample.xlsx", package= "openxlsx"))
Error in getChildlessNode(xml = workbookRelsXML, tag = "<Relationship ") : 
  object '_openxlsx_getChildlessNode' not found
> names(wb)  #list worksheets
[1] "IrisSample"      "testing"         "mtcars"          "mtCars Pivot"    "A new worksheet"
...
> saveWorkbook(wb, "loadExample.xlsx", overwrite = TRUE)
Error in file.copy(from = xlsx_file, to = file, overwrite = overwrite) : 
  more 'from' files than 'to' files
>

[ksak3]

It appears that some elements are disappearing from the header and footer as well, including header text, page numbers, and sheet name.

ETA: This does not happen uniformly - it seems to depend on the length of the header, like if it extends out of the header cell. Also &[Pages] disappears regardless.

[awalker89]

Can you please attach an example xlsx file

[ksak3]

test1.xlsx test2.xlsx

test1 is before loading and saving and test2 is after. The header text was not removed this time but other information in the header and footer was, and the custom style is removed from the menu.

[ChristopherPapanicolas87]

Hey, Hope all is well. Was this bug fixed in the current version of opexlsx 4.1.4 When I read out the excel file the formatting in my original template file is stripped. The column widths are reset, the background color is removed, and any merged cells that I had are unmerged in the final file that is generated. Thanks

[olisch]

Losing custom styles with any function of openxlsx is very annoying (as I rely heavily on them, I have to pull some tricks and go lots of extra miles not to lose them).

Same goes with the colors of the Worksheets: they mostly disappear, but sometimes remain.

I hope you can fix this bug soon ;-)

[capitantyler]

Me too, lossing the custom format. before: $ #.##0;[Rojo]-$ #.##0;$ 0; after: dd/mm/yyyy

[dimmin]

I also have issues with the format, either manually pre-defined in the excel Workbook while not modified by the R code (it does not even actually write in the same worksheet) or custom format that are lost or modified.

[tedtoal]

What is the status on this? I'm having the same problem, with openxlsx 4.2.4.

The strange thing is, it was working fine until suddenly it wasn't. I did update some packages but unfortunately I don't know if I updated openxlsx or not.

Are there any workarounds?

I just installed the developer's version according to instructions in Readme and that version is 4.1.1. It works okay. So it looks like maybe a new version has been backed out??

Subsequently at a later date I updated all of my R libraries, forgetting about this, and it updated openxlsx and the problem returned. This time, I installed the following version:

https://cran.r-project.org/src/contrib/Archive/openxlsx/openxlsx_4.1.2.tar.gz 

using the command line (on macOS terminal):

R CMD INSTALL openxlsx_4.1.2.tar.gz

after downloading that file to a directory. That again fixed the problem. I did not spend time trying to figure out at exactly which version the problem started happening.

I can provide code to illustrate this if needed. I have a multi-tab spreadsheet and am reading the first tab and rewriting it, but styles on the second tab are getting lost (not all of them, only a few).