1

I am working on an xlsx with two sheets: the first one containing raw data and the second one containing a pivot table. I cannot manage to modify the data-sheet without getting the pivot one in plain text: I've seen many people have the same issue but I couldn't find a working solution.

I am working with version 2.6.2 of openpyxl which according to the documentation (https://openpyxl.readthedocs.io/en/stable/pivot.html) should preserve pivot table.

Below my code which works without preserving the pivot.

sheet_name='Data'
book = load_workbook(local_path + 'file_name'+ '.xlsx')

wrs = book["my_pivot_table"]
pivot = wrs ._pivots[0]
pivot.cache.refreshOnLoad = True

writer = pd.ExcelWriter(local_path + 'file_name'+ '.xlsx', engine='openpyxl')

sh = book[sheet_name]
sh.delete_cols(1, 20)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df_tb_exp.to_excel(writer, sheet_name=sheet_name, index=False)

writer.save()

Thanks

UPDATE

I tried without using Pandas for reading and writing the xlsx but unfortunately still have the pivot table in plain text - moreover this code empties the Data sheet but it doesn't fill it with the new data, I am probably missing smtg...

sheet_name='Data'
book = load_workbook(local_path + 'file_name'+ '.xlsx')

ws = book["my_pivot_table"]
pivot = ws._pivots[0]
pivot.cache.refreshOnLoad = True

sh = book[sheet_name]
sh.delete_cols(1, 20)

for r in dataframe_to_rows(df_tb_exp, index=False, header=True):
  sh.append(r)

book.save(local_path + 'file_name'+ '.xlsx')

Thanks!

Ema_py
  • 11
  • 3
  • I'm pretty certain that the problem is using Pandas to write to the workbook. Try opening and saving the workbook just using openpyxl. BTW. you will **need** to update to openpyxl 3.x because 2.6 is no longer supported and there are some important updates for pivot tables in the 3,x branch. – Charlie Clark Oct 30 '20 at 16:52
  • Thanks a lot Charlie!I tried skipping the pandas reading and writing using the openpyxl dataframe_to_rows and caching the pivot table but still it results in a "broken" pivot, in plain text. Unfortunately I cannot update the library in the short time because these kind of things are managed by a specific team in my organization, but I'll for sure ask them. In the meantime I'll have to look for another workaround. – Ema_py Nov 02 '20 at 10:41
  • What is the range for the pivot table? – Charlie Clark Nov 02 '20 at 10:58
  • Thanks again! The pivot takes the data from the Data sheet - which should be updated each time with same number of cols, different number of rows - and is located in the my_pivot_table sheet [A3:C26] - but the number of columns will vary with the number of rows in the Data sheet. I hope this fully answer to your question :) – Ema_py Nov 02 '20 at 14:01
  • Can you try opening and saving the ile **without** making any changes? If the cells covered by the pivot are changed, then you will probably need to change the pivot reference. – Charlie Clark Nov 02 '20 at 14:22
  • The reference seems to work fine, as a matter of fact if I try to print the pivot variable I get the correct cells as argument of "Parameters ref". – Ema_py Nov 02 '20 at 15:36
  • I'm not sure what you mean. What happens if you just do `wb = load_workbook(); wb.save("new.xlsx")`Does the pivot in the new file work okay? – Charlie Clark Nov 02 '20 at 16:43
  • No, unfortunately it does not. I've tryed to change the ws._pivots[0] index in 1 or 2 but I got an index out of range error. Moreover trying to inspect the object ws._pivots[0] I can find a ref parameter which points correctly to the pivot area... – Ema_py Nov 02 '20 at 17:02
  • I can't really do anything more without a file. Please submit a bug report with a file. – Charlie Clark Nov 03 '20 at 10:23
  • sure, tnx a lot! – Ema_py Nov 03 '20 at 13:48

0 Answers0