问题\场景\需求
python 读取 Excel 之后，格式就复原了，那么该怎么让格式不变
解决\目标
目标：python 写数据到 Excel，不改变文档原有样式
解决方案：在打开 Excel 的时候，加入该参数： formatting_info=True
from xlrd import open_workbook
r_xls = open_workbook(fileName,formatting_info=True) # 读取excel文件
row = r_xls.sheets()[sheet].nrows # 获取已有的行数
excel.save(fileName) # 保存
def table_data_list(self):
"""获取table数据，返回一个json"""
par_ids_list, sec_ids_list, dep_ids_list, job_list, major_list = [], [], [], [], []
en_master_qty_list, en_un_qty_list, ex_master_qty_list, ex_un_qty_list, notes_list = [], [], [], [], []
count_num = 0
if self:
for line in self.line_ids:
major_name = line.major_ids.mapped('name')
par_ids_list.append(line.parent_company_id.name)
sec_ids_list.append(line.secondary_company_id.name)
dep_ids_list.append(line.department_name)
job_list.append(line.job)
major_list.append(','.join(major_name))
en_master_qty_list.append(line.en_master_qty)
en_un_qty_list.append(line.en_undergraduate_qty)
ex_master_qty_list.append(line.ex_master_qty)
ex_un_qty_list.append(line.ex_undergraduate_qty)
notes_list.append(line.notes)
count_num += 1
data_array = {
'id': self.id,
'tab_name': self.name,
'count_num': count_num,
'sec_ids_list': sec_ids_list,
'par_ids_list': par_ids_list,
'dep_ids_list': dep_ids_list,
'job_list': job_list,
'major_list': major_list,
'en_master_qty_list': en_master_qty_list,
'en_un_qty_list': en_un_qty_list,
'ex_master_qty_list': ex_master_qty_list,
'ex_un_qty_list': ex_un_qty_list,
'notes_list': notes_list,
}
return data_array
def btn_excl_method(self):
"""导出excel入口函数"""
data_array = self.table_data_list()
context = dict(self._context or {})
wiz_obj = self.env['hd.export.export.data.wizard']
filename = '计划编制-%s' % (datetime.datetime.today())
wiz_id = wiz_obj.sudo().create({
'file_data': self.file_data_excel(data_array)
})
value = dict(
type='ir.actions.act_url',
target='self',
url='/web/content?model=%s&id=%s&field=file_data&download=true&filename=%s.xls' % (
'hd.export.export.data.wizard', wiz_id.id, filename),
)
return value
def file_data_excel(self, data_array):
# 1、使用xlrd打开Excel
workbook1 = open_workbook(
"E:\\HD_Settled\\dtcloud360\\appstore\\dtcloud_hd_graduate\\static\\src\\download\\hd.graduate.application.line.xlsx")
# 2、使用xlutils模块的copy复制打开的文件，并保留原格式
open_mb_file_cp = copy.copy(workbook1)
# 3、使用下标定位的方式定位到Excel工作簿里的工作表
worksheet = open_mb_file_cp.get_sheet(0)
count_num = data_array['count_num']
if count_num > 0:
for row in range(1, count_num + 1):
rews = row - 1
worksheet.write(row, 0, data_array['sec_ids_list'][rews] or '')
worksheet.write(row, 1, data_array['par_ids_list'][rews] or '')
worksheet.write(row, 2, data_array['dep_ids_list'][rews] or '')
worksheet.write(row, 3, data_array['job_list'][rews] or '')
worksheet.write(row, 4, data_array['major_list'][rews] or '')
worksheet.write(row, 5, data_array['en_master_qty_list'][rews])
worksheet.write(row, 6, data_array['en_un_qty_list'][rews])
worksheet.write(row, 7, data_array['ex_master_qty_list'][rews])
worksheet.write(row, 8, data_array['ex_un_qty_list'][rews])
worksheet.write(row, 9, data_array['notes_list'][rews] or '')
buffer = BytesIO()
open_mb_file_cp.save(buffer)
return base64.encodebytes(buffer.getvalue())
