0

I have a script that takes in excel documents, cleans them up, and then attempts to replace a sheet in a workbook. I am using jupyter notebooks and everything runs fine up until I use this function called write_excel, shown below. Working path is already defined earlier in the script and the proper libraries are imported. I'm not used to seeing an error message this long and my google searches haven't turned up anything so far. This is the part of the script which is causing the error:

def write_excel(filename,sheetname,dataframe):
with pd.ExcelWriter(filename, engine='openpyxl', mode='a') as writer: 
    workBook = writer.book
    try:
        workBook.remove(workBook[sheetname])
    except:
        print("Worksheet does not exist")
    finally:
        dataframe.to_excel(writer, sheet_name=sheetname,index=False)
        writer.save()

write_excel(working_path + 'CombinedCases.xlsx', 'src_data', df3)

Here is the error:

ValueError                                Traceback (most recent call last)
<ipython-input-26-96dd16293639> in <module>
     10             writer.save()
     11 
---> 12 write_excel(working_path + 'CombinedCases.xlsx', 'src_data', df3)

<ipython-input-26-96dd16293639> in write_excel(filename, sheetname, dataframe)
      1 def write_excel(filename,sheetname,dataframe):
----> 2     with pd.ExcelWriter(filename, engine='openpyxl', mode='a') as writer:
      3         workBook = writer.book
      4         try:
      5             workBook.remove(workBook[sheetname])

C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in __init__(self, path, engine, mode, **engine_kwargs)
     23             from openpyxl import load_workbook
     24 
---> 25             book = load_workbook(self.path)
     26             self.book = book
     27         else:

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    313     reader = ExcelReader(filename, read_only, keep_vba,
    314                         data_only, keep_links)
--> 315     reader.read()
    316     return reader.wb

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in read(self)
    278         self.read_theme()
    279         apply_stylesheet(self.archive, self.wb)
--> 280         self.read_worksheets()
    281         self.parser.assign_names()
    282         if not self.read_only:

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\excel.py in read_worksheets(self)
    253             drawings = rels.find(SpreadsheetDrawing._rel_type)
    254             for rel in drawings:
--> 255                 charts, images = find_images(self.archive, rel.target)
    256                 for c in charts:
    257                     ws.add_chart(c, c.anchor)

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\reader\drawings.py in find_images(archive, path)
     37     charts = []
     38     for rel in drawing._chart_rels:
---> 39         cs = get_rel(archive, deps, rel.id, ChartSpace)
     40         chart = read_chart(cs)
     41         chart.anchor = rel.anchor

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\packaging\relationship.py in get_rel(archive, deps, id, cls)
    166     src = archive.read(path)
    167     tree = fromstring(src)
--> 168     obj = cls.from_tree(tree)
    169 
    170     rels_path = get_rels_path(path)

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\chart\plotarea.py in from_tree(cls, node)
    138     @classmethod
    139     def from_tree(cls, node):
--> 140         self = super(PlotArea, cls).from_tree(node)
    141         axes = dict((axis.axId, axis) for axis in self._axes)
    142         for chart in self._charts:

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     85                 if hasattr(desc.expected_type, "from_tree"):
     86                     #complex type
---> 87                     obj = desc.expected_type.from_tree(el)
     88                 else:
     89                     #primitive

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
    101                 attrib[tag] = obj
    102 
--> 103         return cls(**attrib)
    104 
    105 

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\drawing\effect.py in __init__(self, blurRad, dist, dir, sx, sy, kx, ky, algn, rotWithShape, **kw)
    283         self.kx = kx
    284         self.ky = ky
--> 285         self.algn = algn
    286         self.rotWithShape = rotWithShape
    287         super(OuterShadow, self).__init__(**kw)

C:\ProgramData\Anaconda3\lib\site-packages\openpyxl\descriptors\base.py in __set__(self, instance, value)
    126     def __set__(self, instance, value):
    127         if value not in self.values:
--> 128             raise ValueError(self.__doc__)
    129         super(Set, self).__set__(instance, value)
    130 

ValueError: Value must be one of {'l', 't', 'tl', 'bl', 'ctr', 'b', 'br', 'r', 'tr'}

One other thing to note. {'l', 't', 'tl', 'bl', 'ctr', 'b', 'br', 'r', 'tr'} seems to stand for left, top, top-left, bottom-left, center, bottom, bottom-right, right, top-right. What this has to do with excel and data frame manipulation is beyond me.

Anthony Stokes
  • 23
  • 6
  • long shot, but maybe try `mode=A` instead of `mode=a` based on this question [pandas.ExcelWriter ValueError: Append mode is not supported with xlsxwriter](https://stackoverflow.com/questions/54863238/pandas-excelwriter-valueerror-append-mode-is-not-supported-with-xlsxwriter) – a11 Mar 26 '21 at 20:10
  • Using `mode=A` does not return the error which answers this question. Unfortunately the function deletes all sheets in `CombinedCases.xlsx`, adds the src_data sheet, and then prints`"Worksheet does not exist"`. I believe this is a different issue though. – Anthony Stokes Mar 29 '21 at 14:36
  • I only see the flag. How do I upvote it? – Anthony Stokes Mar 29 '21 at 19:05
  • One other thing to note. `{'l', 't', 'tl', 'bl', 'ctr', 'b', 'br', 'r', 'tr'}` seems to stand for left, top, top-left, bottom-left, center, bottom, bottom-right, right, top-right. What this has to do with excel and data frame manipulation is beyond me. – Anthony Stokes Mar 29 '21 at 19:17
  • 1
    no triangle above the flag unfortunately – Anthony Stokes Mar 29 '21 at 19:18

0 Answers0