search

awalker89 / openxlsx

R package for .xlsx file reading and writing.
Other
364 stars 79 forks source link

Error loading and saving workbook with filters #456

Open ColinTB opened 5 years ago

ColinTB commented 5 years ago

Expected Behavior

When loading a workbook with filters and then resaving the workbook the file should not become corrupt. I think it might be simplest to just have the filter removed, rather than attempt to apply to filter to possibly new data.

Actual Behavior

File becomes corrupt and any sheets with filters appear blank.

These are the excel corruption warning when trying to open the file.

image

image

Steps to Reproduce the Problem

  1. Set path wb_path <- "PATH TO FOLDER/test.xlsx"

  2. Create simple workbook wb <- createWorkbook() addWorksheet(wb, sheetName = "test") writeData(wb, sheet = "test", data.frame(a = 1:2)) saveWorkbook(wb,file = wb_path, overwrite = T)

  3. Open the workbook, add a filter to Column A and from the dropdown filter for for only 2

  4. Load the worbook and try to update the data on the tab with the filter wb <- loadWorkbook(wb_path) writeData(wb, sheet = "test", data.frame(a = 1:3)) saveWorkbook(wb,file = wb_path, overwrite = T)

  5. When I try to open the update workbook I get the warnings shared above and the "test" tab is now blank (Example corrupted workbook)

sessionInfo()

R version 3.4.2 (2017-09-28) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 7 x64 (build 7601) Service Pack 1

other attached packages: [1] openxlsx_4.1.0

mschwartsman commented 5 years ago

I wish there were a proper solution, but I've found a workaround. Load the workbook and call openxlsx::removeFilter(wb, sheet = <sheets_with_filters>) before proceeding.