loadWorkbook removing formatting

[mdorchuckuva]

When using loadWorkbook, formatting that has been applied by a user in excel directly is being stripped.

With the attached excel file in my working directory, running the following code opens a file with cell C4 in black, unbolded text instead of the red, bold text as originally in the file. The formatting remains stripped if I save the wb object back out and then open from file explorer.

library(openxlsx)
wb = loadWorkbook("testopenxlsx.xlsx")
openXL(WB)

System information
Windows 10 Enterprise 10.0.19042
Have tested on R 4.0.3 and R 4.1.0, in both cases the issue persists
Openxlsx version 4.2.4
Office 365 version 2102
testopenxlsx.xlsx

[mdorchuckuva]

I found that the issue no longer occurs if I back up my openxlsx version to 4.0.0, in case that helps narrow it down.

[ycphs]

In your example is you open a different workbook than the one you loaded

library(openxlsx) 
wb = loadWorkbook("testopenxlsx.xlsx")
openXL(WB)

Please try the following:

library(openxlsx) 
wb = loadWorkbook("testopenxlsx.xlsx")
openXL(wb)

[mdorchuckuva]

Sorry the wb got capitalized when I pasted over for some reason. I was running the same workbook. see screenshot below and thanks so much for taking a look!

Top workbook is what opens from OpenXL, bottom workbook is the workbook that was originally saved.

[JanMarvin]

I've seen similar issues before. What I have found is that the style order is changed by openxlsx. We always add our default style to styles.xml, which is picked here and all the other styles are re-sorted and do no longer match with the style ids in the worksheet. Iirc the styles file has no style id field and they are picked in the order of appearance. Kinda the same issue as #81.

[Seviks]

I can confirm this issue on 4.2.4. When I loadWorkbook, do my changes (or not) an then saveWorkbook, the formatting is gone.

Formats persisted in 4.2.3. After roll back to 4.2.3 the issue is gone.

Tested on:
openxlsx 4.2.4
Windows 10
R 4.1.0
Office 2019

[patrick-gerland]

Indeed, by upgrading to version 4.2.4 this new issue happens. Rolling back to the previous version fixes the problem.
I spent most of 1 day troubleshooting what happened to all my workbooks (>200 with multiple worksheets).

Just loading and saving back the file causes the problem with loosing the existing settings for the default column headers.
wb <- loadWorkbook(input.file)
saveWorkbook(wb, input.file, overwrite = T)

The initial wb simply contains 1 worksheet like
addWorksheet(wb, sheet = "myInput")
writeDataTable(wb, sheet = "myInput", x = myInput, colNames = TRUE, rowNames = FALSE, headerStyle = hs1, tableStyle = "TableStyleLight2", withFilter = FALSE, bandedRows = TRUE)

with
hs1 <- createStyle(fgFill = "#4F81BD", halign = "CENTER", textDecoration = "Bold", border = "Bottom", fontColour = "white")

with the newer version fontColour setting gets lost to some dark default instead of white.

[derekunderwood]

Similar issue here although format is being altered, not removed entirely. Seemed appropriate to post here but please move if separate issue.

Cells formatted single accounting underline using this option:

Appear to load with normal underline formatting:

Tested on:
openxlsx 4.2.3
Windows 10
R 3.6.3
Office 2016

underline_test.xlsx

[JanMarvin]

A potential fix is in #237 could any of you test this please?

[patrick-gerland]

Thanks lot. I just installed the latest development version packageVersion("openxlsx") ‘4.2.4.9000’ Unfortunately the problem remains.

…

On Fri, Jul 30, 2021 at 11:04 AM Jan Marvin Garbuszus < ***@***.***> wrote: A potential fix is in #237 <#237> could any of you test this please? — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#207 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACSJOTPDOCQ5EZNDKEPXFCDT2K5PZANCNFSM47ITCGMA> .

[mdorchuckuva]

Thanks so much @JanMarvin. Sorry for the delayed response here.

I built #237 on Windows. Results are shown in the screenshot. Left book is the input, right book is what opens from openxlsx::openXL. We keep font changes, bolding and underlines. We still lose merged cell, color changes, and strikethrough. Input workbook also attached for your reference.

Please let me know if I can do anything to help that involves R (do not know cpp, sorry).

[testopenxlsx.xlsx](https://github.com/ycphs/openxlsx/files/6941882/testopenxlsx.xlsx

[JanMarvin]

Thanks for testing (the pull request isn't yet merged with the master branch, I wanted to get feedback first).

@mdorchuckuva do you remember if these used to work with prior releases or did you list them for being important?

[mdorchuckuva]

@JanMarvin thanks!

Yes we had a report that contained some of these features (particularly merged cells) that broke after an update to openxlsx.

I can confirm the test workbook works correctly across all items except strikethrough if I roll back to 4.0.0.

[mdorchuckuva]

Edited above comment for clarity.

[ycphs]

The commit for version 4.0.0 is e99f707
At the previous repo (awalker89/openxlsx) at 5 Jan 2017.

It will be tricky to find the cause, but it is good to know when it worked the last time.

[JanMarvin]

I assume it might be a more recent regression. I'll try to look into it.

[JanMarvin]

Hi @mdorchuckuva, I assume I've fixed it. Please have another look. I've even added <strike/> (because it was easy). Both files look identical for me. Could you please have a look?

[mdorchuckuva]

@JanMarvin you looking at PR #237? I checked that PR out - can build on windows but am getting a fatal error in RStudio w/ no message or other info when I try to execute loadworkbook. Any advice? Am I in the right PR?

Initial (uninformed) guess is that it may be related to which version of rcpp I'm using - I'm on 1.0.7.

[JanMarvin]

yeah, that's the one. not sure what's going on. it works on the ci (all green on GitHub actions). No clue why it should fail locally and I assume it's related to your setup. 😞

Something like this should work

library(remotes)
install_github("ycphs/openxlsx", ref = github_pull(237))

library(openxlsx)
wb <- loadWorkbook(file = system.file("extdata", "loadExample.xlsx", package = "openxlsx"))

Edit: Okay, I can confirm the crash on windows 10.

[JanMarvin]

I've rebased the pull request on master and can now run it on windows (maybe the issue was caused by some unrelated pending fix or an RStudio bug?). I freshly installed R and Rstudio and a bunch of packages (usually use my Windows only for gaming 😄 ).

> sessionInfo()
R version 4.1.0 (2021-05-18)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

Matrix products: default

locale:
[1] LC_COLLATE=German_Germany.1252  LC_CTYPE=German_Germany.1252    LC_MONETARY=German_Germany.1252
[4] LC_NUMERIC=C                    LC_TIME=German_Germany.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] openxlsx_4.2.4.9000

loaded via a namespace (and not attached):
[1] compiler_4.1.0 tools_4.1.0    Rcpp_1.0.7     stringi_1.7.3  zip_2.2.0

[JanMarvin]

Should be fixed in 0fc1c08

[mdorchuckuva]

@JanMarvin sorry didn't see this over the weekend. FYI I can execute the prior test code successfully after installing via remotes, w/out updating any of the referenced packages or re-installing R/Rstudio/packages. So it seems to me that the fail issue was isolated to building that PR via Rstudio on Win10, not should occur if installed normally once pushed to CRAN.

Thanks so much for your help w/ this, really appreciate it!

[JanMarvin]

Thanks for the feedback! I assume that a new Cran release will follow soon

[neros]

Thanks for the feedback! I assume that a new Cran release will follow soon

Thank you @JanMarvin and others for the fix. Is there a timeline for this fix on Cran? Having upgraded to 4.2.4, existing code breaks that uses styles (filtering data using FontColour format, specifically). This is an important fix for existing code base within the package community.

[JanMarvin]

Hi @neros , I'm not involved in the CRAN releasing process. That's @ycphs part, but he was involved with personal matters. For now the current master is what I consider the next release, therefore if you need it, you can build from there. Otherwise a new release will follow soon™.

[mendy-friedman]

I downloaded the latest master branch. Most of the formatting issues are now fixed, but there are still a few formatting pieces that are not carried over. Can you please look into this before the next release?

• Tab formatting: If a tab in the original workbook has a color instead of the default gray, this color is lost upon loading and then saving the workbook. Additionally, hidden tabs become unhidden upon loading and saving the workbook.
• Form controls (action buttons): some of these resize upon saving the workbook
• Hidden columns: If you hide a single column, it remains hidden upon loading and saving the workbook. But if you hide all columns from column X to the end of the workbook, those columns unhide after saving.

Some screenshots are below. I can't share the full workbook for confidentiality reasons, but it should be easy to create a reproducible example based on these comments.

Original (tabs are colored, some tabs are hidden, buttons are a certain size, and some columns are hidden):

After Saving (tabs are all gray, and all are unhidden. Columns unhide, and buttons are wider):

[JanMarvin]

Hi @mendy-friedman , I currently don't have time to look into this and personally think these can be fixed in follow up releases. It might be a good idea to open additional issues for these and just out of curiosity, did any of these work in previous releases? For instance the hidden/unhidden part seems like it never worked and the button size is afaik unhandled too. Also it would be helpful if you could provide pre and post xlsx-files.

[JanMarvin]

Late night test. I created a simple workbook with colored tabs with MS 365 in the web editor. Downloaded the xlsx file. Ran the following lines and the resulting test.xlsx sheet had colored tabs with LibreOffice and MS 365. Meaning I couldn't reproduce the issue. Same thing happend with hidden columns. If I hid all from C:XFD in MS 365 and ran the lines below, everything looked the same (with expected errors on LibreOffice, but fine in MS 365). Therefore if something isn't working please open new issues and provide steps and files to reproduce the issues.

library(openxlsx)
fl <- "~/ColorTabs.xlsx"

wb <- loadWorkbook(fl)
saveWorkbook(wb, "/tmp/test.xlsx")

Edit: hidden sheets unhide. That's unfortunate, but nothing to serious.

[mendy-friedman]

appreciate the quick reply - I'll try to create a reproducible example that I can share. I'll reply to the separate issue that you opened.

[mendy-friedman]

In my actual use case, the worksheet tabs do lose their colors, but when I create a (simpler) reproducible example, the tabs maintain their colors. Wondering if one of the steps that I do before saving (writing new data to the workbook, etc.) is impacting this. I'll keep researching.

The issue with attributes that apply to an entire column or row being deleted is still happening unfortunately. I will open a new issue for this and share an example. Thank you for your help.

[mdorchuckuva]

@mendy-friedman not a dev on this project, but a frequent user. I have experienced what you're experiencing (tabs losing colors, some columns/rows losing attributes) if there is an xml error w/ the workbook generally. when you do saveworkbook on the one you're seeing issues w/ in R are you getting any warning messages back?

do you have comments in the workbook (the new office 365 style)?

[mendy-friedman]

@mdorchuckuva - I'm not getting any warning messages back. No office 365 comments in the workbook, although it does include some other attributes such as some vba code, a user form, etc.

@JanMarvin - I was able to create a reproducible example that doesn't include company confidential information. When I read in the first file and save it back out, I get the second file (the tab color of the first tab is stripped). It's an xlsm file, so I can't upload the file the normal way to this chat (only .xls and .xlsx are supported), but I was able to put the input and output files into a .zip file and uploaded that below. Let me know if you have troubles accessing the files, or if you'd like me to open this as a separate issue.
testfiles.zip

[JanMarvin]

Hi @mendy-friedman , long time no see. I opened the file with openxlsx2 which should be able to handle your file just fine. I ran the following code and the files look identical, but the sheets are not colored. If you want to give it a try, see the code below. I was using the current openxlsx2 main branch.

# remotes::install_github ...
library(openxlsx2)
library(curl) # for the download

### prepare
tmp <- tempdir()
input_zip <- paste0(tmp, "/Desktop.zip")

input <- paste0(tmp, "/test2.xlsm")
output <- paste0(tmp, "/test_openxlsx.xlsm")

### get file
curl::curl_download(
  "https://github.com/ycphs/openxlsx/files/7427381/Desktop.zip",
  destfile = input_zip)

unzip(input_zip, exdir = tmp)
dir(tmp)

### actual file work
#  load the workbook
wb <- wb_load(input)
# wb$open()
wb$save(output)

### compare
xl_open(input)
xl_open(output)

[mendy-friedman]

Thanks @JanMarvin - I'll check it out. I didn't even know that openxlsx2 existed (we've still been using openxlsx for our existing pipelines). I'll have to explore it. Reading the vignette the enhancements look exciting. Thanks for the heads up!

[JanMarvin]

Development is still quite active. Our first CRAN release 0.3 is available since last month and a bugfix 0.3.1 release shall follow this month. For all openxlsx2 questions please use the other bug tracker and discussion boards.