IndexError：至少有一张纸必须可见

Question

 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')

我收到此错误，但无法修复。我对我的代码中发生的事情进行了简短表示，但很难看出真正的问题在哪里。这里是

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

我该如何解决这个问题？

PS 请注意，有一半时间会显示错误。

原文由 user8802531 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

看起来您想要做的只是将每个 DataFrame 写入同一张表（将其附加到最后一张表下方），所以我认为您可以将其写为：

 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!

原文由 Andy Hayden 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

或者，如果您不需要加载工作簿，您可以只使用 xlsxwriter 而不是 openpyxl；它没有这个问题。您还可以使用常规创建工作簿

from openpyxl import Workbook
#...
wb= Workbook()
ws=wb.active
with pd.ExcelWriter(output_filepath, engine="openpyxl") as writer:
    writer.book=wb
    writer.sheets = dict((ws.title, ws) for ws in wb.worksheets)
    #useful code
    df.to_excel(writer, sheet, ...)
    writer.save()

现在你可以同时使用 pandas.to_excel 和 openpyxl 的有趣方法，这些方法没有被翻译成它（那不是我的技巧；在这里找到但找不到位置）

原文由 Ando Jurai 发布，翻译遵循 CC BY-SA 4.0 许可协议