IndexError:从CSV文件中读取至少一个工作表必须是可见的

Question

我有一个CSV文件，如下所示：

first-name,last-name,email-address
Yes,Beth,bethfat@gmail.com
Iran,Dajango,django@me.com
Paul,Walker,info@walker.com
Ray,Johnson,JJRay@gmail.com

我正在尝试通过使用Pandas将这个CSV文件转换成一个Excel文件以供进一步使用。我是如何通过从网页中读取数据来做到的：

import requests

def get_data(url):
    headers = {
        "Connection": "close",
        "User-Agent": "TBG/Converter (TBG;file-converter)"
    }
    req = requests.get(url, headers=headers)
    return req.content


def parse_html(html):
    retval = {}
    data = html.split("</head>")
    data = data[-1]
    data = data.split("\n")
    retval["header"] = data[0].strip()
    data = data[1:-1]
    emails = set()
    for email in data:
        emails.add(email.split("<br>")[-1])
    retval["userInformation"] = list(emails)
    return retval

并将其写入临时CSV文件：

import pandas as pd

def write_to_xlsx(data_dict, filename):
    tmp_filename = "/tmp/{}.csv".format(rand())
    with open(tmp_filename, "a+") as tmp:
        data = [data_dict["header"]]
        for item in data_dict["userInformation"]:
            item = item.split(",")
            item = ",".join([c.strip() for c in item])
            data.append(item)
        csv_format = "\n".join(data)
        tmp.write(csv_format)
    print tmp_filename
    with pd.ExcelWriter(filename) as ew:
        pd.read_csv(tmp_filename).to_excel(
            ew, index=False, sheet_name=tmp_filename.split(".")[0], encoding="utf-8"
        )
        ew.save()

在编写Excel文件时，我收到一个错误，告诉我：

Traceback (most recent call last):
  File "converter.py", line 70, in <module>
    write_to_xlsx(sorted_data, config["filename"].format(str(datetime.datetime.now()).split(" ")[0]))
  File "converter.py", line 63, in write_to_xlsx
    ew.save()
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/pandas/io/excel.py", line 985, in __exit__
    self.close()
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/pandas/io/excel.py", line 989, in close
    return self.save()
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/pandas/io/excel.py", line 1018, in save
    return self.book.save(self.path)
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/openpyxl/workbook/workbook.py", line 367, in save
    save_workbook(self, filename)
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/openpyxl/writer/excel.py", line 284, in save_workbook
    writer.save(filename)
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/openpyxl/writer/excel.py", line 266, in save
    self.write_data()
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/openpyxl/writer/excel.py", line 95, in write_data
    archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/site-packages/openpyxl/writer/workbook.py", line 94, in write_workbook
    active = get_active_sheet(wb)
  File "/Users/admin/bin/python/printers/venv/mailchimper/lib/python2.7/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

是什么导致了这个问题，我该如何解决呢？

Answer 1

我也有同样的问题。正如错误描述的那样，引擎创建了一个新的excel文件，其中没有任何工作表，因此有一段时间它看起来已经损坏。为了理解这个问题，我调试了我的所有代码，并对几乎所有的内容进行了注释。有时，当“作者”试图找到那张纸时，就会发生这种情况。有时，它只是在创建文件和将数据帧转换为它自己的工作表时正常地流动。因此，为了避免这种行为，在堆栈溢出中找到了解决方案。

解决方案的链接：IndexError: At least one sheet must be visible

工作得很好。唯一困扰我的是创建一个空白表，以确保文件不会被破坏。所以，我所做的是，如果文件有这个“工作表”，而且还有一个以上的工作表，删除它。

wb=openpyxl.load_workbook('the_file.xlsx')
sheets = wb.sheetnames
name = 'Sheet'
if name in sheets and len(sheets)>1:
    wb.remove(wb[name])
    wb.save('the_file.xlsx')

对于建议的需要，是好的，但我会看更多，如果我可以做这种删除在创建，或在保存文件，以避免再次加载它。一个更优雅的解决方案。

如果我找到(或任何人)，我们可以在这里张贴。:)