Error loading and saving workbook with pivot tables

[isabelgc]

Expected Behavior

Loading the workbook in the environment, saving it and opening it without errors or without loss of information such as dynamic tables.

Actual Behavior

Error 1: If the number of pivot tables is not a multiple of pivotCacheDefinition's number, an error is returned.

Error 2: But once I solve this problem creating the required number of pivot tables (multiple of pivotCacheDefinition length), when I save the workbook and I try to open it, there is an error that is deleting random pivot tables that were at the workbook before. This happens because there are more than 9 pivotCacheDefinition.

Steps to Reproduce the Problem

(please attach an example xlsx file if possible)

1. template_error.xlsx template_error_save.xlsx

2. Loading the file _"templateerror.xlsx" and saving it, you can reproduce the error on file _"template_errorsave.xlsx"

3. Adding one pivot table in _"templateerror.xlsx" and loading the workbook, you will have the first error I described about multiples

Possible solution

We realized that making some little changes, error 2 is resolved. We only had to add "+" in regular expression of:

1. File loadWorkbook.R
   • Line 262.

cache_keep <- unlist(regmatches(wb$pivotTables.xml.rels, gregexpr("(?<=pivotCache/pivotCacheDefinition)[0-9](?=\\.xml)", wb$pivotTables.xml.rels, perl = TRUE, ignore.case = TRUE)))

2. File WorkbookClass.R
   • Line 1249.

if(length(tables) > 0){ table_inds <- which(grepl("tables/table[0-9].xml", ws_rels))

• Line 1893.

pivotNode <- workbook.xml.rels[grepl("pivotCache/pivotCacheDefinition[0-9].xml", workbook.xml.rels)]

sessionInfo()

• Version of openxlsx: 4.0.17
• Version of R: 3.4.3 (2017-11-30)

[Havimo]

I think error 1 might be the same as my open issue (#377). Could you please explain how you bypassed it?

[isabelgc]

Yes, it's the same problem.

You can avoid the error by creating an aditional sheet whit the extra pivot tables that you need (multiple of pivotCacheDefinition) to load the workbook and before saving it again, delete this sheet. It's hard to know how many tables are necessary, it depends on the number of caches. Usually, there is one pivotCacheDefinition per sheet that is the source of at least one dynamic table.

I hope that this helps you with your problem. If not, do not hesitate to ask again.

[pabloalicante]

I have experienced exactly the same issues as @isabelgc . I think that error 1 could be solved using paste() or similar function instead of sprintf() function. I have tried @isabelgc 's possible solution to error 2 and it worked like a charm!

Please, I would like to request it to be included in next releases.

Thank you for helping me to solve the problem!

[stiberger]

Experienced same issue. Applied pull request https://github.com/awalker89/openxlsx/pull/417 locally. Seems to work now

Thanks @isabelgc