conditionalformatting() with "databar" type creates a corrupted excel file when used more than once

[wwyws0000]

Expected Behavior

Ideally, I want to use openxlsx to create multiple instances of databars using conditionalFormatting() in a single sheet. The attached file is a simple example with two numeric matrices, each with conditional formatted data bars. test.xlsx

Actual Behavior

The .xlsx file would be corrupted if I run the R code to produce such file (see code below). "We found a problem with some content in 'test.xlsx'."

Yet producing a single matrix with conditionalFormatting() works fine.

Steps to Reproduce the Problem

This code produces a corrupt file

wb <- createWorkbook()
addWorksheet(wb, 'Test')

writeData(wb, 'Test',matrix(runif(20),4,5),startRow = 1,startCol=1)
conditionalFormatting(wb,'Test',cols = 1:5, rows =2:5,type = 'databar')
writeData(wb, 'Test',matrix(runif(20),4,5),startRow = 11,startCol=1)
conditionalFormatting(wb,'Test',cols = 1:5, rows =2:5+10,type = 'databar')

saveWorkbook(wb,'test.xlsx',overwrite = T)

###However, if only one of the conditionalFormatting() command is used, then the sheet works fine, e.g.

wb <- createWorkbook()
addWorksheet(wb, 'Test')

writeData(wb, 'Test',matrix(runif(20),4,5),startRow = 1,startCol=1)
conditionalFormatting(wb,'Test',cols = 1:5, rows =2:5,type = 'databar')
writeData(wb, 'Test',matrix(runif(20),4,5),startRow = 11,startCol=1)

saveWorkbook(wb,'test.xlsx',overwrite = T)

Or

wb <- createWorkbook()
addWorksheet(wb, 'Test')

writeData(wb, 'Test',matrix(runif(20),4,5),startRow = 1,startCol=1)
writeData(wb, 'Test',matrix(runif(20),4,5),startRow = 11,startCol=1)
conditionalFormatting(wb,'Test',cols = 1:5, rows =2:5+10,type = 'databar')

saveWorkbook(wb,'test.xlsx',overwrite = T)

sessionInfo()

• Version of openxlsx: openxlsx_4.0.17
• Version of R: R version 3.4.0

[joerg-b]

I'm also using openxlsx to create excel files with multiple conditional formattings in one sheet. They work just fine in LibreOffice 5.4.3.2 and in Excel 2011. However they appear to be corrupted when opening them in Excel 2016.

sessionInfo() R version: 3.3.1 openxlsx version 4.0.17

[bblum9]

Also have the problem of not being able to open workbooks with Excel 2016 when using multiple databars.

R version 3.4.3 (2017-11-30) openxlsx_4.1.1

[bblum9]

Tried creating an expression where the "databar" conditional formatting was called once, but cols was specified as c(16, 19, 21) (the columns I needed). It generated a spreadsheet I could open in excel but all columns 16:21 had the databar.

[joerg-b]

I looked a bit more into this issue because I'm really a big fan of data bars. And I found a very ugly fix for this issue When I unzip the .xlsx file and then go to the subfolder xl>worksheets and then open sheer1.xml and then find and replace <dataBar showValue="1"> by <dataBar> and do this also for sheet2.xml, sheet3.xml,... And then zip the folder again and rename the .zip to .xlsx Then I can open the file also in later versions of excel again. Unfortunately I haven't found the file in this github repository that need to be changed in order to implement this fix in a clean way.

[mihawk2016]

I'm also using openxlsx to create excel files with multiple conditional formattings in one sheet. They work just fine in LibreOffice 5.4.3.2 and in Excel 2011. However they appear to be corrupted when opening them in Excel 2016.

sessionInfo() R version: 3.3.1 openxlsx version 4.0.17

same. sessionInfo() R version 3.5.1 (2018-07-02) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows >= 8 x64 (build 9200)

[JustBob81]

I can confirm that the reprex still produces a corrupt excel file for excel 2016.

I tried removing showValue = "1" from the xml for the worksheet as suggested and that did not fix the problem.

However I was able to create databars by replacing sheet1.xml file inside the corrupted workbook archive with sheet1.xml from an identical workbook that I made by removing the calls to conditionalFormatting and then adding dataBars manually in excel.

Comparing the two xml files the issue is in the gen_databar_extlst , where each databar is wrapped in a separate pair of tags

<ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:conditionalFormattings>

...

</x14:conditionalFormattings></ext>

I was able to produce a non-corrupt file by overwriting the default gen_databar_exlst with a version that removed these two tags using assignInNamespace. There may be other dependencies for these tags that I am overlooking, but hopefully this points in the direction of a solution to this problem.

[iaconogi]

I do have the same problem, when adding two or more databars the file is not readable anymore in Excel.

[vdarcangelo]

Have the same problem as well. Been searching and posting for weeks now and can't seem to find any solutions. If I ever figure it out, I'll be sure to share.

[giloop]

I have the same problem. As a quick fix I open the file first with LibreOffice → save it as Excel and then it opens fine in Excel 2016

sessionInfo() R version 4.2.2 (2022-10-31 ucrt) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 19045) openxlsx_4.2.5.2