can not save excel through writer.save

Question

I try to add a data frame to an exist excel file, I read a answer under this question:How to write to an existing excel file without overwriting data (using pandas)?

filename = 'template.xlsx'
wb_target = load_workbook(filename)

writer = pd.ExcelWriter(filename, engine='openpyxl')
writer.wb_target = wb_target
writer.sheets = dict((ws.title, ws) for ws in wb_target.worksheets)

df_selection2.to_excel(writer,sheet_name='Selection',startrow=7, startcol=6,header=False, index=False)
writer.save()

but there stilll have some issue:

IndexError                                Traceback (most recent call last)
<ipython-input-3-3ca40cf2bef3> in <module>()
     17 
     18 df_selection2.to_excel(writer,sheet_name='Selection',startrow=7, startcol=6,header=False, index=False)
---> 19 writer.save()
     20 
     21 #df_selection2.to_excel(writer,sheet_name='Selection',startrow=7, startcol=6,header=False, index=False)

C:\Anaconda3\lib\site-packages\pandas\io\excel.py in save(self)
    725         Save workbook to disk.
    726         """
--> 727         return self.book.save(self.path)
    728 
    729     def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):

C:\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py in save(self, filename)
    337             save_dump(self, filename)
    338         else:
--> 339             save_workbook(self, filename)
    340 
    341 

C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py in save_workbook(workbook, filename)
    266     archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
    267     writer = ExcelWriter(workbook, archive)
--> 268     writer.save(filename)
    269     return True
    270 

C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py in save(self, filename)
    248     def save(self, filename):
    249         """Write data into the archive."""
--> 250         self.write_data()
    251         self.archive.close()
    252 

C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py in write_data(self)
     91         archive.writestr(ARC_STYLE, tostring(stylesheet))
     92 
---> 93         archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
     94         archive.writestr(ARC_WORKBOOK_RELS, write_workbook_rels(self.workbook))
     95 

C:\Anaconda3\lib\site-packages\openpyxl\writer\workbook.py in write_workbook(workbook)
     86 
     87     # book views
---> 88     active = get_active_sheet(wb)
     89     view = BookView(activeTab=active)
     90     root.bookViews =[view]

C:\Anaconda3\lib\site-packages\openpyxl\writer\workbook.py in get_active_sheet(wb)
     58     """
     59     idx = wb._active_sheet_index
---> 60     sheet = wb.active
     61     if sheet.sheet_state == "visible":
     62         return idx

C:\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py in active(self)
    128     def active(self):
    129         """Get the currently active sheet"""
--> 130         return self._sheets[self._active_sheet_index]
    131 
    132     @active.setter

IndexError: list index out of range

is that because the tabs I want to modified is at hiden status?

Did you try unhiding tabs and then save dataframe to Excel? – Parfait Dec 24 '16 at 18:56 — Parfait, Dec 24 '16 at 18:56

piRSquared · Accepted Answer · 2016-12-30T22:26:23.087

the problem is that you load_workbook to a variable wb_target and then try to assign to an attribute of writer named wb_target. writer doesn't have that attribute by default. So when you assigned it, you created it. This is going to lead no where good.

Instead you need to assign it to the correct attribute which is book. It was confusing in the the example because the variable they used was book so you may have assumed that those two need to be the same. They do not!

This is your code rewritten and should work fine.

import pandas as pd
import numpy as np
from openpyxl import load_workbook

filename = 'template.xlsx'
wb_target = load_workbook(filename)

writer = pd.ExcelWriter(filename, engine='openpyxl')
# my key change
writer.book = wb_target
writer.sheets = dict((ws.title, ws) for ws in wb_target.worksheets)

df_selection2.to_excel(
    writer, sheet_name='Selection', startrow=7, 
    startcol=6, header=False, index=False)
writer.save()

can not save excel through writer.save

1 Answers1