[FIXED] IndexError: At least one sheet must be visible

Issue

 def multiple_dfs(sheet, row=2):

    writer = pd.ExcelWriter("testing.xlsx", engine='openpyxl')

    f1 = {
        'user': ['Bob', 'Jane', 'Alice'], 
        'income': [40000, 50000, 42000],
    }

    f2 = {
        'amount': ['Chest', 'Bras', 'Braa'], 
        'income': [40000, 50000, 42000]
    }

    frames = [f1, f2]


    for f in frames:
        try:
            wb = load_workbook("testing.xlsx")
            ws = wb.get_sheet_by_name("aaa")

            writer.wb = wb
            writer.sheets = dict((ws.title, ws) for ws in wb.worksheets)

            row = ws.max_row + 2
        except:
            pass 

        df = pd.DataFrame(f)
        df.to_excel(writer, sheet, startrow=row, index=False)
        writer.save()
    # writer.close()

multiple_dfs('aaa')

I got this error, but I can't fix it. I adapt this short representation of what is happening in my code, but it is hard to see where is the real problem. Here is the

Traceback (most recent call last):
  File "create_and_update_xlsx_sheets.py", line 144, in <module>
    create_and_update_worksheets()
  File "create_and_update_xlsx_sheets.py", line 140, in create_and_update_worksheets
    writer.save()
  File "/home/jeremie/.virtualenvs/NHL/lib/python3.5/site-packages/pandas/io/excel.py", line 824, in save
    return self.book.save(self.path)
  ...
  File "/home/jeremie/.virtualenvs/NHL/lib/python3.5/site-packages/openpyxl/writer/workbook.py", line 61, in get_active_sheet
    raise IndexError("At least one sheet must be visible")
IndexError: At least one sheet must be visible

How could I fix that issue?

P.S. Be aware that error is displayed half the time.

Solution

It seems like what you want to do is just write each DataFrame to the same sheet (appending it below the last), so I think you can write this as:

start_row = 1
for df in frames:  # assuming they're already DataFrames
    df.to_excel(writer, sheet, startrow=start_row, index=False)
    start_row += len(df) + 1  # add a row for the column header?
writer.save()  # we only need to save to disk at the very end!

Answered By - Andy Hayden