python - 将自生成的 Excel 文件保存为 UploadedFile

Question

我有一个包含 CSV 数据的巨大字符串。我想将其转换为 Excel 文件 (.xslx) 并将其另存为 UploadedFile/SimpleUploadedFile。我尽可能地用谷歌搜索并得出以下结果。 result_data 显然是一个巨大的字符串。

from io import StringIO

import pandas

from django.core.files.uploadedfile import SimpleUploadedFile

### irrelevant code

result_data = StringIO(result_data)

df = pandas.DataFrame.from_csv(result_data, sep=';')
writer = pandas.ExcelWriter('file.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

result_file = writer.book
result_data.seek(0)

mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

object.xls_file = SimpleUploadedFile('filename.xlsx', result_data.read(), content_type=mimetype)
object.save()

我尝试了很多 result_data.read() 的替代品，例如 result_data、result_file、result_file.read() ，但到目前为止，它们都不起作用。

编辑:我根据 jmcnamara 的建议修改了我的代码，但是 writer.save() 出现错误。

output = StringIO()
result_data = StringIO(result_data)

df = pandas.DataFrame.from_csv(result_data, sep=';')

writer = pandas.ExcelWriter(output, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

回溯:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/venv/lib/python3.4/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
    utility.execute()
  File "/venv/lib/python3.4/site-packages/django/core/management/__init__.py", line 330, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/venv/lib/python3.4/site-packages/django/core/management/base.py", line 390, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/venv/lib/python3.4/site-packages/django/core/management/base.py", line 441, in execute
    output = self.handle(*args, **options)
  File "/commands/create.py", line 67, in handle
    writer.save()
  File "/venv/lib/python3.4/site-packages/pandas/io/excel.py", line 1413, in save
    return self.book.close()
  File "/venv/lib/python3.4/site-packages/xlsxwriter/workbook.py", line 296, in close
    self._store_workbook()
  File "/venv/lib/python3.4/site-packages/xlsxwriter/workbook.py", line 541, in _store_workbook
    xlsx_file.write(os_filename, xml_filename)
  File "/usr/lib/python3.4/zipfile.py", line 1373, in write
    self.fp.write(zinfo.FileHeader(zip64))
TypeError: string argument expected, got 'bytes'
Exception ignored in: <bound method ZipFile.__del__ of <zipfile.ZipFile object at 0x7fe5fa2077f0>>
Traceback (most recent call last):
  File "/usr/lib/python3.4/zipfile.py", line 1466, in __del__
    self.close()
  File "/usr/lib/python3.4/zipfile.py", line 1573, in close
    self.fp.write(endrec)
TypeError: string argument expected, got 'bytes'

Answer 1

在尝试读取数据之前，您可能需要关闭/保存 pandas 创建的 xlsx 文件:

writer.save()

此外，在 Pandas 0.17+ 中，您可以使用 StringIO/BytesIO 对象作为 pd.ExcelWriter 的文件句柄。例如:

import pandas as pd
import StringIO

output = StringIO.StringIO()

# Use the StringIO object as the filehandle.
writer = pd.ExcelWriter(output, engine='xlsxwriter')

# Write the data frame to the StringIO object.
pd.DataFrame().to_excel(writer, sheet_name='Sheet1')
writer.save()
xlsx_data = output.getvalue()

# Do something with the data...