Python使用xlwt、xlsxwriter生成Excel

使用xlwt

• 示例

  def excel_(headers, result, file_name, horizontal=True): f = xlwt.Workbook() default_easyxf = xlwt.easyxf(num_format_str='0.00') sheet1 = f.add_sheet('sheet1') # 标红加粗 # style = "font:colour_index 10, bold False" # style = xlwt.easyxf(style) if horizontal: for i in range(0, len(headers)): if isinstance(headers[i], tuple): sheet1.write(0, i, headers[i][0], xlwt.easyxf(headers[i][1])) continue sheet1.write(0, i, headers[i]) row_index = 0 else: r = [] for k, v in zip(headers, result): r.append([k, v]) result = r row_index = -1 for rows in result: row_index += 1 for j in range(0, len(rows)): try: if not rows[j]: r = '' elif '.' in str(rows[j]): r = float(rows[j]) else: r = int(rows[j]) except ValueError: r = rows[j] if isinstance(r, (int, float)): # 数值格式 if r < 10000000000: sheet1.write(row_index, j, r, default_easyxf) else: sheet1.write(row_index, j, str(r)) else: sheet1.write(row_index, j, str(r)) # 保存到本地 # f.save("/User/name/{}".format(file_name)) excel_stream = io.BytesIO() # BytesIO流（在内存中读写） f.save(excel_stream) res = excel_stream.getvalue() excel_stream.close() response = HttpResponse(content_type='application/vnd.ms-excel') from urllib import parse response['Content-Disposition'] = 'attachment;filename=' + parse.quote(file_name) response.write(res) return response headers = ["姓名", ("手机号", "font:colour_index 10, bold False")] results = [["李白", "1888888888"], ["杜甫", "19999999999"]] file_name = "test.xlsx" excel_(headers, results, file_name)

使用xlsxwriter

• 示例

  # excel下拉列表导出,数据校验 def xlsx_excel(headers, results, filename): excel_stream = io.BytesIO() workbook = xlsxwriter.Workbook(excel_stream) worksheet = workbook.add_worksheet() # 数字格式 header_format = workbook.add_format({ 'num_format': '@', }) # 下拉列表校验格式 data_validate = {'validate': 'list', 'source': ['是', '否']} data_validate1 = {'validate': 'list', 'source': ['有', '无']} # 日期格式校验 date_validate = {'validate': 'date', 'criteria': 'between', 'minimum': date(2018, 1, 1), 'maximum': date(9999, 12, 12)} for i in range(len(headers)): # 写入校验规则 if isinstance(headers[i], tuple): # 写入计算公式 if "自动计算" in headers[i][0]: worksheet.write(0, i, headers[i][0]) worksheet.write_formula(1, i, headers[i][1], header_format) # 使用之前定义的header_format数字格式 elif "text" in headers[i][1]: r = headers[i][1].replace('text', '') worksheet.write(0, i, headers[i][0], workbook.add_format({'font_color': 'red'})) worksheet.set_column("{}:{}".format(r, r), None, header_format) # 使用之前定义的date_validate日期格式 elif "date" in headers[i][1]: r = headers[i][1].replace('date', '') worksheet.write(0, i, headers[i][0], workbook.add_format({'font_color': 'red'})) worksheet.data_validation('{}2:{}1048576'.format(r, r), date_validate) # 使用之前定义的data_validate下拉规则 else: worksheet.write(0, i, headers[i][0], workbook.add_format({'font_color': 'red'})) worksheet.data_validation('{}2:{}1048576'.format(headers[i][1], headers[i][1]), data_validate) else: worksheet.write(0, i, headers[i], workbook.add_format({'font_color': 'red'})) # 整行写入数据 for x in range(len(results)): worksheet.write_row("A{}".format(x + 2), results[x]) workbook.close() xlsx_data = excel_stream.getvalue() response = HttpResponse(content_type='application/ms-excel') response['Content-Disposition'] = 'attachment; filename=%s.xlsx' % filename response.write(xlsx_data) return response headers = [("日期", 'dateA'), "姓名", ("手机号", "textC"), "数字一", "数字二", ("合计（自动计算）", "=D2+E2")] results = [["2019/1/1", "张三", "18888888888", "1000", 1000]] filename = "test_excel" xlsx_excel(headers, results, filename)

• 参考链接：python生成Excel的两种方式