XlsxWriter是我特别喜欢使用的用于导出excel表的python库，支持多种功能，比如样式、图片、图表等。现在我来总结一下其中的常用用法
XlsxWriter地址
先贴一下官方的最简示例：
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()
# Some data we want to write to the worksheet.
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0
# Iterate over the data and write it out row by row.
for item, cost in (expenses):
worksheet.write(row, col, item)
worksheet.write(row, col + 1, cost)
row += 1
# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')
workbook.close()
其他的所有功能都是在这个基础商店扩展
比如：
样式调整
bold = workbook.add_format({'bold': True})
worksheet.write('A1', 'Hello'，bold)
worksheet.write(2, 0, 123)
设置列宽行高
worksheet.set_row(0, 43.5)
worksheet.set_column('A:A', 20)
特殊格式
# 会计专用
accounting_style = workbook.add_format({"num_format": '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ '})
orksheet.write(2, 0, 100, accounting_style)
居中
align = workbook.add_format({"align": "center", "valign": "vcenter"})
背景色
bg_color = workbook.add_format({"bg_color": "#BDD7EE"})
合并单元格
style=workbook.add_format({"font_size": "16", "valign": "vcenter"})
worksheet.merge_range("B1:G1", 'hello', style)
冻结
worksheet.freeze_panes(2, 1)
最后，官方有一个结合django导出的代码例子，可直接套用
##############################################################################
#
# A simple Django view class to write an Excel file using the XlsxWriter
# module.
#
# Copyright 2013-2020, John McNamara, jmcnamara@cpan.org
#
import io
from django.http import HttpResponse
from django.views.generic import View
import xlsxwriter
def get_simple_table_data():
# Simulate a more complex table read.
return [[1, 2, 3],
[4, 5, 6],
[7, 8, 9]]
class MyView(View):
def get(self, request):
# Create an in-memory output file for the new workbook.
output = io.BytesIO()
# Even though the final file will be in memory the module uses temp
# files during assembly for efficiency. To avoid this on servers that
# don't allow temp files, for example the Google APP Engine, set the
# 'in_memory' Workbook() constructor option as shown in the docs.
workbook = xlsxwriter.Workbook(output)
worksheet = workbook.add_worksheet()
# Get some data to write to the spreadsheet.
data = get_simple_table_data()
# Write some test data.
for row_num, columns in enumerate(data):
for col_num, cell_data in enumerate(columns):
worksheet.write(row_num, col_num, cell_data)
# Close the workbook before sending the data.
workbook.close()
# Rewind the buffer.
output.seek(0)
# Set up the Http response.
filename = 'django_simple.xlsx'
response = HttpResponse(
output,
content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response['Content-Disposition'] = 'attachment; filename=%s' % filename
return response
这是我在掘金上的第18篇文章，欢迎拍砖~
