python操作excel
pip install xlwt import xlwt 创建一个worlkbook,设置编码 workbook = xlwt.Workbook(encoding="utf8") 创建一个worksheet worksheet =workbook.add_sheet("sheetName") 写入excel #参数对应 行，列，值 worksheet.write(1,0,"nihao") 保存 workbook.save("name.exl")
举例：
定义方法:excel 表头加粗,excel内容常体
def kn_write_sheet_row(self, sheet, row_index, value_array, is_bold):
bold_style = xlwt.easyxf('font: bold 1')
column_index = 0
for value in value_array:
if is_bold:
sheet.write(row_index, column_index, value, bold_style)
else:
sheet.write(row_index, column_index, value)
column_index += 1
写入excel并导出：
1 def list_request(self, args): 2 """ 3 表格到文件，并返回相应下载URL 4 :param args: HTTP 参数 5 :return: 返回是否处理成功，如成功则返回相应下载URL 6 :rtype: {status, openUrl} 7 """ 8 try: 9 title = u"逻辑核查" 10 project_id = args["project_id"] if args["project_id"] else "" 11 logical_verify_dict_array, total = self.logicalVerifyDao.get_logical_verifies(project_id, args) 12 wb = xlwt.Workbook() 13 ws = wb.add_sheet(title) 14 15 head_list = [u'名称', u'脚本', u'类型'] 16 row_index = 0 17 self.kn_write_sheet_row(ws, row_index, head_list, True) 18 row_index += 1 19 for logical_verify_dict in logical_verify_dict_array: 20 value_array = [] 21 value_array.append(logical_verify_dict["name"]) 22 value_array.append(logical_verify_dict["script"]) 23 value_array.append(logical_verify_dict["type"]) 24 self.kn_write_sheet_row(ws, row_index, value_array, False) 25 row_index += 1 26 config_export_name = "logical_verify%s" % Util().getDateTime().replace(' ', '-').replace(":", "-") #创建下载文件的名字. 27 export_file_path = os.path.normpath("%s/port_files/export/%s.xls" % (PDFormApp.DATA_PATH, config_export_name.encode("utf-8"))) 28 wb.save(export_file_path) 29 30 print(666666666666,config_export_name) #(666666666666L, 'logical_verify2019-05-29-18-18-52') 31 32 print(777777,export_file_path) #(777777, 'G:\\knkj\\pdform_py\\dataservices\\data\\port_files\\export\\logical_verify2019-05-29-18-18-52.xls') 33 34 web_path = "%s/%s.xls" % (PDFormApp.DIR_UPLOAD_EXP, config_export_name) 35 36 print(88888888,web_path) #(88888888, '/ds/data/pdform/exp/logical_verify2019-05-29-18-18-52.xls') 37 38 39 export_file_data = file(export_file_path, "rb").read() #获取文件数据 40 41 # mem_file = StringIO.StringIO() 42 # wb.save(mem_file) 43 # wb_data = mem_file.getvalue() 44 45 response = Response() 46 response.data = export_file_data #写入响应对象里。 47 response.mimetype = "application/vnd.ms-excel" 48 response.headers['Content-disposition'] = "inline; filename=\"%s.xls\"" % title.encode("gbk") #设置响应头. 49 return response 50 except Exception, ex: 51 app.logger.error(traceback.format_exc()) 52 return Response(None, status=404)
第二种方法(上面第三十九行开始替换）:
1 mem_file = StringIO.StringIO() 2 wb.save(mem_file) 3 wb_data = mem_file.getvalue() 4 5 response = Response() 6 response.data = wb_data 7 response.mimetype = "application/vnd.ms-excel" 8 response.headers['Content-disposition'] = "inline; filename=\"%s.xls\"" % title.encode("gbk") 11 return response 12 except Exception, ex: 13 app.logger.error(traceback.format_exc()) 14 return Response(None, status=404)
