问题简介:
python利用openpyxl 将数据写入excel文件时,以前都正常运行,现在忽然报错如下:
ew = ExcelWriter(workbook=wb) TypeError: __init__() takes exactly 3 arguments
问题细节:
Traceback (most recent call last):
File "/Users/a6/Downloads/PycharmProjects/hive/logserver_new/get_interact_data_from_hive.py", line 317, in <module>
obj_handle_excel.run_main_for_change_txt_to_excel(in_txt_filename,in_head_row_name_list,out_save_excel_filename)
File "/Users/a6/Downloads/PycharmProjects/hive/logserver_new/save_txt_to_excel.py", line 125, in run_main_for_change_txt_to_excel
self.__write_to_excel_with_openpyxl(dataset,out_save_excel_filename,in_head_row_name_list)
File "/Users/a6/Downloads/PycharmProjects/hive/logserver_new/save_txt_to_excel.py", line 91, in __write_to_excel_with_openpyxl
ew = ExcelWriter(workbook=wb)
TypeError: __init__() takes exactly 3 arguments (2 given)
报错代码:
import sys
reload(sys)
sys.setdefaultencoding("utf-8")
# workbook相关
from openpyxl.workbook import Workbook
# 万恶的ExcelWriter,妹的封装好了不早说,封装了很强大的excel写的功能
from openpyxl.writer.excel import ExcelWriter
# 一个eggache的数字转为列字母的方法
#from openpyxl.cell import get_column_letter
from openpyxl.utils import get_column_letter
from openpyxl.reader.excel import load_workbook
def write_to_excel_with_openpyxl(records_list,save_excel_name,head_row_stu_arrive_star):
# 新建一个workbook
wb = Workbook()
# add 1225
'''
from zipfile import ZipFile, ZIP_DEFLATED
archive = ZipFile(save_excel_name, 'w', ZIP_DEFLATED)
ew = ExcelWriter(workbook=wb,archive=archive)
'''
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
# 设置文件输出路径与名称
dest_filename = save_excel_name.decode('utf-8')
# 第一个sheet是ws
ws = wb.worksheets[0]
# 设置ws的名称
ws.title = "range names"
# 写第一行,标题行
for h_x in range(1,len(head_row_stu_arrive_star)+1):
h_col=get_column_letter(h_x)
#print h_col
ws.cell('%s%s' % (h_col, 1)).value = '%s' % (head_row_stu_arrive_star[h_x-1])
# 写第二行及其以后的那些行
i = 2
for record in records_list:
#print record
#print type(record)
record_list=str(record).strip().split("\t")
#return -2
for x in range(1,len(record_list)+1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, i)).value = '%s' % (record_list[x-1].decode('utf-8'))
i += 1
# 写文件
ew.save(filename=dest_filename)
解决方案
方案(一):
将以下两行代码:
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
换成如下3行代码:
from zipfile import ZipFile, ZIP_DEFLATED
archive = ZipFile(save_excel_name, 'w', ZIP_DEFLATED)
ew = ExcelWriter(workbook=wb,archive=archive)
即可运行成功
方案(二)
其实不建议用创建ExcelWriter对象去写文件了
可以将以下部分注释:
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
和
# 写文件
ew.save(filename=dest_filename)
然后将 # 写文件
ew.save(filename=dest_filename)
直接换成
wb.save(filename=dest_filename)
就可以了
原因分析:
通过读了一下openpyxl.writer.excel的源码,应该是新版的ExcelWriter对象初始化时多了一个archive参数,所以要写成上面这样。源码如下:
class ExcelWriter(object):
"""Write a workbook object to an Excel file."""
def __init__(self, workbook, archive):
self._archive = archive
self.workbook = workbook
self.manifest = Manifest()
self.vba_modified = set()
self._tables = []
self._charts = []
self._images = []
self._drawings = []
self._comments = []
参考网址:
http://www.codes51.com/itwd/4451357_2.html