search

ycphs / openxlsx

openxlsx - a fast way to read and write complex xslx files
https://ycphs.github.io/openxlsx/
Other
220 stars 74 forks source link

Corrupted xlsx file when a string contains \024 #486

Closed Mkranj closed 1 month ago

Mkranj commented 1 month ago

Describe the bug I'm creating an Excel file based on data in a database. I have some strings describing addresses, so UTF8 characters are to be expected. For a certain subset, the exported xlsx file gets corrupted. I've narrowed it down so that the problem occurs when any string contains the characters \024.

To Reproduce

mydf <- data.frame(address = "Trg dr.\024Zarka Dolinara")

write.xlsx(mydf, "errors.xlsx")

And try to open the resulting file.

Expected behavior File opens with the provided data.

Additional context I'm getting the data from a database, so I can't really control if such characters ocurr. Ideally I'd like to avoid string substitutions before saving as an Excel file.
I also use the data as part of a Shiny application and there are no issues rendering to a table there.

JanMarvin commented 1 month ago

Hi @Mkranj , please try if this is resolved with openxlsx2. There are known issues with openxlsx.

Mkranj commented 1 month ago

Hi, thank you for the suggestion. Changing the second line in my example to openxlsx2::write_xlsx(mydf, "errors2.xlsx") does work, the Excel file contains the column name and the string without the troublesome character.

I'll have to do some research for applying this to other programs, since I depend on other openxlsx functionalities that I hope are also available in openxlsx2, as well as taking the time to rewrite code to use the new package API.

JanMarvin commented 1 month ago

Great that it works! As a fallback you could write the file with openxlsx2 and read it with openxlsx. But lots of efforts went into openxlsx2 and it’s very unlikely that they get backported to openxlsx. Personally openxlsx is more or less in just maintenance mode. A few PR that are simple can be merged, but other than that, it’s now legacy software.

Mkranj commented 1 month ago

I see, thank you for your help once more :)