Django学习-导出报表功能、xlwt库的使用、pdf、前端

前端统计图展示参考
https://www.makeapie.com/explore.html

安装第三方库

有很多的三方库支持在Python程序中写Excel问件，包括
xlwt、xlwings、openpyxl、xlswriter、pandas等，其中的xlwt虽然只支持写xls格式的Excel文件，但
在性能方面的表现还是不错的。下面我们就以xlwt为例，来演示如何在Django项目中导出Excel报表，
例如导出一个包含所有老师信息的Excel表格。

pip install xlwt -i https://pypi.doubanio.com/simple

xlwt详解官文地址搜索pypi.org

实例1

#导出之前项目中老师信息表



def export_teachers_excel(request):
 # 创建工作簿
 wb = xlwt.Workbook()
 # 添加工作表
 sheet = wb.add_sheet('老师信息表')
 # 查询所有老师的信息
 queryset = Teacher.objects.all()
 # 向Excel表单中写入表头
 colnames = ('姓名', '介绍', '好评数', '差评数', '学科')
 for index, name in enumerate(colnames):
 sheet.write(0, index, name)
 # 向单元格中写入老师的数据
 props = ('name', 'detail', 'good_count', 'bad_count', 'subject')
 for row, teacher in enumerate(queryset):
	 for col, prop in enumerate(props):
		 value = getattr(teacher, prop, '')
		 if isinstance(value, Subject):
		 value = value.name
		 sheet.write(row + 1, col, value)
 # 保存Excel
 buffer = BytesIO()
 wb.save(buffer)
 # 将二进制数据写⼊响应的消息体中并设置MIME类型
 resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.msexcel')
 # 中文文件名需要处理成百分号编码
 filename = quote('老师.xls')
 # 通过响应头告知浏览器下载该⽂件以及对应的文件名
 resp['content-disposition'] = f'attachment; filename*=utf-8''{filename}'
 return resp

此处省略url映射

实例2

#导出之前项目汽车违章信息表

def export_excel(request: HttpRequest) -> HttpResponse:
    queryset = Record.objects.filter(is_deleted=False) \
        .defer('is_deleted', 'deleted_time', 'updated_time') \
        .select_related('car').order_by('no')
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('违章记录表')
    titles = ('编号', '车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '是否受理')
    # style = xlwt.easyxf('font: height 720, color-index red')
    for col_index, title in enumerate(titles):
        sheet.write(0, col_index, title)
    for row_index, record in enumerate(queryset):
        sheet.write(row_index + 1, 0, record.no)
        sheet.write(row_index + 1, 1, record.car.carno)
        sheet.write(row_index + 1, 2, record.car.owner)
        sheet.write(row_index + 1, 3, record.reason)
        sheet.write(row_index + 1, 4, record.makedate.strftime('%Y-%m-%d'))
        sheet.write(row_index + 1, 5, record.punish)
        sheet.write(row_index + 1, 6, '已受理' if record.dealt else '未受理')
    buffer = io.BytesIO()
    # str - 字符串 - 'hello' ---> io.StringIO
    # bytes - 字节串 - b'\xff\xe0\x9a' ---> io.BytesIO ---> 内存区域
    wb.save(buffer)
    # 创建HTTP响应对象并指定MIME类型（给浏览器的内容的类型）
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    # 将中文文件名处理成百分号编码
    filename = quote('违章记录汇总统计表.xls')
    # 设置HTTP响应头（设置下载文件并指定文件名）
    # resp['content-type'] = 'application/vnd.ms-excel'
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp

实例3:

@api_view(['POST', 'GET'])
def export_excel(request: HttpRequest, *args, **kwargs) -> HttpResponse:
    '''
    导出excel,传data_ids     GET | POST(推荐)  均可
    示例:
    {
        "data_ids": 1,2,3,4,5,6,
        "ext": xlsx(可选)
    }
    :param {参数名: data_ids, 类型: str, 说明: 选中的id,英文逗号分割
    :return 默认返回 filestream (默认xlsx, 支持xls)
    '''

    data_ids: str = request.data.get('data_ids')
    if not data_ids:
        data_ids: str = request.GET.get('data_ids')
        if not data_ids:
            return Response({'code': 500, 'msg': '传值不可为空'})

    data_ids: list = data_ids.split(',')
    result = Data.objects.filter(data_id__in=data_ids).defer('data_id').values_list()
    df = pd.DataFrame(result, columns=(
        '序号', '业务类型', '收寄日期', '邮件号', '寄件人', '国家', '省', '市', '重量', '计费重量', '应收', '实收', '收寄员', '资费', '创建日期'
    ))
    del df['序号']  # 去掉id
    df['创建日期'] = df['创建日期'].map(lambda x: str(x))  # 时间列转为str类型

    buffer = io.BytesIO()  # 写入内存
    df.to_excel(buffer, sheet_name='Sheet1', index=False)  # 转为excel
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    filename = quote('数据汇总表.xlsx')
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp

openpyxl 导出 版本3.1.3

from openpyxl import Workbook


def write_to_multiple_sheets(data_sheet1, data_sheet2, filename="output.xlsx"):
    wb = Workbook()
    
    # 使用默认创建的"Sheet"作为第一个工作表，并可重命名
    ws1 = wb.active  # 获取默认工作表
    ws1.title = "Sheet1"  # 重命名为Sheet1（可选）
    
    # 写入第一个工作表数据
    for row_idx, row_data in enumerate(data_sheet1, start=1):
        for col_idx, value in enumerate(row_data, start=1):
            ws1.cell(row=row_idx, column=col_idx, value=value)
    
    # 创建第二个工作表
    ws2 = wb.create_sheet(title="Sheet2")
    # 写入第二个工作表数据
    for row_idx, row_data in enumerate(data_sheet2, start=1):
        for col_idx, value in enumerate(row_data, start=1):
            ws2.cell(row=row_idx, column=col_idx, value=value)
    
    # 无需删除默认表，因为我们已经使用了它
    wb.save(filename)
    print(f"数据已成功写入 {filename}，包含 {ws1.title} 和 {ws2.title}")


# 测试数据
data1 = [["姓名", "年龄"], ["张三", 25], ["李四", 30]]
data2 = [["产品", "价格"], ["手机", 3999], ["电脑", 5999]]

write_to_multiple_sheets(data1, data2)

示例

from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, Alignment

@action(methods=['GET', ], detail=False)
def export_excel(self, request, *args, **kwargs) -> HttpResponse:
    '''导出 跟随导出参数'''
    queryset = self.filter_queryset(self.get_queryset())

    wb = Workbook()
    ws1 = wb.active  # 获取默认工作表
    ws1.title = "Sheet1"  # 重命名为Sheet1（可选）

    title = ['订单号', '客户姓名', '客户手机号', '客户地址',
             '沟通方案-公司收取', '沟通方案-现场支付', '沟通方案-回填',
             '店员姓名', '店员手机号', '工人姓名', '工人手机号']

    for index, value in enumerate(title, start=1):  # openpyxl 中行和列索引都是从1开始
        cell = self.set_cell_text(sheet=ws1, row=1, col=index, value=value, border=True, font_bold=True)

        col_letter = cell.column_letter  # 直接通过当前单元格获取列字母
        ws1.column_dimensions[col_letter].width = 23  # 统一宽度
    ws1.row_dimensions[1].height = 15  # 高度值，数值越大行越高

    for row_idx, v in enumerate(queryset, start=2):  # 写入正文
        dianyuan_xingming, dianyuan_shoujihao = self.get_dianyuan(v.dianyuan_id)
        gongren_xingming, gongren_shoujihao = self.get_gongren(v.gongren_id)
        self.set_cell_text(sheet=ws1, row=row_idx, col=1, value=v.dingdanhao)
        self.set_cell_text(sheet=ws1, row=row_idx, col=2, value=v.kehuxingming)
        self.set_cell_text(sheet=ws1, row=row_idx, col=3, value=v.kehushoujihao)
        self.set_cell_text(sheet=ws1, row=row_idx, col=4, value=v.kehudizhi)
        self.set_cell_text(sheet=ws1, row=row_idx, col=5, value=v.goutongfangan_gongsishouqu)
        self.set_cell_text(sheet=ws1, row=row_idx, col=6, value=v.goutongfangan_xianchangzhifu)
        self.set_cell_text(sheet=ws1, row=row_idx, col=7, value=v.goutongfangan_huitian)
        self.set_cell_text(sheet=ws1, row=row_idx, col=8, value=dianyuan_xingming)
        self.set_cell_text(sheet=ws1, row=row_idx, col=9, value=dianyuan_shoujihao)
        self.set_cell_text(sheet=ws1, row=row_idx, col=10, value=gongren_xingming)
        self.set_cell_text(sheet=ws1, row=row_idx, col=11, value=gongren_shoujihao)
        ws1.row_dimensions[row_idx].height = 15  # 高度值，数值越大行越高

    buffer = io.BytesIO()
    wb.save(buffer)
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    now = time.strftime(f'%Y%m%d%H%M%S')
    filename = quote(f'地暖__{now}.xlsx')
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp

def set_cell_text(self, sheet, row: int, col: int, value: str, border=False, font_bold=False):
    '''
    给表格插入正文
    @params  sheet              -->  sheet对象;
    @params  row                -->  行索引(openpyxl从1开始);
    @params  col                -->  列索引(openpyxl从1开始);
    @params  value              -->  单元格填入内容;
    @params  border             -->  是否给单元格添加边框;
    @params  font_bold          -->  字体加粗;
    '''
    cell = sheet.cell(row=row, column=col, value=value)
    cell.alignment = Alignment(horizontal='center', vertical='center') # 设置垂直和水平居中

    if border:
        border = Border(  # 边框（细边框）
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.border = border

    if font_bold: # 字体加粗
        cell.font = Font(bold=True)
    return cell

或

@action(methods=['GET', ], detail=False)
def export_excel(self, request, *args, **kwargs) -> HttpResponse:
    '''导出 跟随导出参数'''
    # queryset = self.filter_queryset(self.get_queryset())
    queryset = [["张三", 25], ["李四", 30]]
    wb = Workbook()
    ws1 = wb.active  # 获取默认工作表
    ws1.title = "Sheet1"  # 重命名为Sheet1（可选）

    title = ['订单号', '客户姓名', '客户手机号', '客户地址',
             '沟通方案-公司收取', '沟通方案-现场支付', '沟通方案-回填',
             '店员姓名', '店员手机号', '工人姓名', '工人手机号']

    border = Border(  # 边框（细边框）
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    for index, value in enumerate(title, start=1):  # openpyxl 中行和列索引都是从1开始
        cell = ws1.cell(row=1, column=index, value=value)
        cell.font = Font(bold=True)  # 文字加粗
        cell.alignment = Alignment(horizontal='center', vertical='center')  # 设置表头水平和垂直居中
        cell.border = border  # 边框
        col_letter = cell.column_letter  # 直接通过当前单元格获取列字母
        ws1.column_dimensions[col_letter].width = 23  # 统一宽度

    for row_idx, row_data in enumerate(queryset, start=2):  # 写入正文
        for col_idx, value in enumerate(row_data, start=1):
            cell = ws1.cell(row=row_idx, column=col_idx, value=value)
            cell.alignment = Alignment(horizontal='center', vertical='center')  # 设置表头水平和垂直居中


    ws1.row_dimensions[1].height = 30  # 高度值，数值越大行越高
    buffer = io.BytesIO()
    wb.save(buffer)
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    now = time.strftime(f'%Y%m%d%H%M%S')
    filename = quote(f'地暖__{now}.xlsx')
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp

添加数据验证

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

def add_dropdown_to_d_column():
    # 创建工作簿和默认工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "D列下拉框示例"
    
    # 定义下拉选项（可根据实际需求修改）
    dropdown_options = ["通过", "不通过", "待审核", "返工"]
    
    # 创建数据验证对象
    data_validation = DataValidation(
        type="list",  # 验证类型为列表（下拉选项）
        formula1=f'"{",".join(dropdown_options)}"',  # 选项拼接为字符串
        showDropDown=True,  # 强制显示下拉箭头
        # 错误提示配置
        errorTitle="输入无效",
        error="请从下拉列表中选择选项",
        errorStyle="stop"  # 阻止无效输入
    )
    
    # 关键：设置应用范围为D列从第2行开始的所有单元格
    # 语法："D2:D1048576" 覆盖Excel最大行数（适用于xlsx格式）
    data_validation.ranges = "D2:D1048576"
    
    # 将数据验证添加到工作表
    ws.add_data_validation(data_validation)
    
    # 可选：添加表头
    ws["D1"] = "状态（请选择）"  # D列第一行作为表头，不添加下拉框
    
    # 保存文件
    wb.save("D列下拉框示例.xlsx")
    print("已生成D列带下拉框的Excel文件")

if __name__ == "__main__":
    add_dropdown_to_d_column()

导出

导出PDF报表

在Django项目中，如果需要导出PDF报表，可以借助三方库reportlab来生成PDF文件的内容，再将文件的二进制数据输出给浏览器并指定MIME类型为 application/pdf ，具体的代码如下所示。

def export_pdf(request: HttpRequest) -> HttpResponse:
	 buffer = io.BytesIO()
	 pdf = canvas.Canvas(buffer)
	 pdf.setFont("Helvetica", 80)
	 pdf.setFillColorRGB(0.2, 0.5, 0.3)
	 pdf.drawString(100, 550, 'hello, world!')
	 pdf.showPage()
	 pdf.save()
	 resp = HttpResponse(buffer.getvalue(), content_type='application/pdf')
	 resp['content-disposition'] = 'inline; filename="demo.pdf"'
	 return resp

前端统计表

参考https://echarts.apache.org

#前端页面

<!DOCTYPE html>
<html lang="en"> 
<head>
	<meta charset="UTF-8">
	<title>老师评价统计</title>
</head>
	 <body>
		 <div id="main" style="width: 600px; height: 400px"></div>
		 <p>
		 <a href="/">返回首页</a>
		 </p>
		 <script src="https://cdn.bootcss.com/echarts/4.2.1-rc1/echarts.min.js">
	</script>
	 	<script>
	 		var myChart = echarts.init(document.querySelector('#main'))
	 		fetch('/teachers_data/')
	 			.then(resp => resp.json())
	 			.then(json => {
	 				var option = {
	 					color: ['#f00', '#00f'],
	 					title: {
	 						text: '老师评价统计图'
	 					},
	 					tooltip: {},
	 					legend: {
	 						data:['好评', '差评']
	 					},
	 					xAxis: {
	 						data: json.names
	 					},
	 					yAxis: {},
	 						series: [
	 					{
	 						name: '好评',
	 						type: 'bar',
	 						data: json.good
	 					},
	 					{
	 						name: '差评',
	 						type: 'bar',
	 						data: json.bad
					 	}
	 				]
	 			}
	 			myChart.setOption(option)
	 		})
	 	</script>
	</body>
</html>

#后端页面

def get_teachers_data(request):
	 queryset = Teacher.objects.all()
	 names = [teacher.name for teacher in queryset]
	 good_counts = [teacher.good_count for teacher in queryset]
	 bad_counts = [teacher.bad_count for teacher in queryset]
	 return JsonResponse({'names': names, 'good': good_counts, 'bad': bad_counts})

xlwt

安装

$ pip install xlwt

例子:

import xlwt
# 创建一个workbook 设置编码
workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个worksheet
worksheet = workbook.add_sheet('My Worksheet')

# 写入excel
# 参数对应 行, 列, 值
worksheet.write(1,0, label = 'this is test')

# 保存
workbook.save('Excel_test.xls')
运行后 会在当前目录生成一个Excel_test.xls

import xlwt

workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet')
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = 'Times New Roman' 
font.bold = True # 黑体
font.underline = True # 下划线
font.italic = True # 斜体字
style.font = font # 设定样式
worksheet.write(0, 0, 'Unformatted value') # 不带样式的写入

worksheet.write(1, 0, 'Formatted value', style) # 带样式的写入


font2 = xlwt.Font()
font2.bold = True
font2.colour_index = xlwt.Style.colour_map['red']    # 字体颜色

workbook.save('formatting.xls') # 保存文件

设置单元格宽度:

import xlwt

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0,'My Cell Contents')

# 设置单元格宽度
worksheet.col(0).width = 3333
workbook.save('cell_width.xls')

输入一个日期到单元格:

import xlwt
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls')

向单元格添加一个公式:

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])
workbook.save('Excel_Workbook.xls')

向单元格添加一个超链接:

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')) # Outputs the text "Google" linking to http://www.google.com
workbook.save('Excel_Workbook.xls')

合并列和行:

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.
workbook.save('Excel_Workbook.xls')

设置单元格内容的对其方式:

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')

alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER  # 设置水平居中
alignment.vert = xlwt.Alignment.VERT_CENTER  # 设置垂直居中

为单元格议添加边框:

# Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines.
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED 
    DASHED虚线
    NO_LINE没有
    THIN实线
    
# May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle() # Create Style
style.borders = borders # Add Borders to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')

为单元格设置背景色:

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')

设置特定行的行高

有时候我们需要单独设置某一行的高度，可以使用xlwt库提供的height属性来单独设置某行的高度。并且height方法需要设置为True，才能生效。

下面的代码片段演示了如何设置Excel工作表中特定行的高度：

import xlwt
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('Sheet1')

# 将第 2 行设置为高度为500
worksheet.row(2).height=True #必须设置True才能生效
worksheet.row(2).height_mismatch=True
worksheet.row(2).height = 500

workbook.save('test.xls')   # 批量就用for循环

如何设置单元格内文本的自动换行

import xlwt
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('Sheet1')

# 自动换行
style = xlwt.easyxf('align: wrap on')

# 在单元格A1中设置自动换行文本
text = "Python 是一种动态解释型语言，Python 具有丰富和强大的库。这使得它在日常编程工作中变得显然更加容易和快速."
worksheet.write(0, 0, text, style)

workbook.save('test.xls')

心理咨询项目实战

def export_excel(request: HttpRequest) -> HttpResponse:
    '''
    GET
    /?user_id
    '''
    if request.method == 'GET':
        user_id = request.GET.get('user_id')
        queryset = Journal.objects.filter(user_id=user_id).select_related('user')
        wb = xlwt.Workbook()
        sheet = wb.add_sheet('咨询日志表')

        sheet.col(0).width = 5555  # 第一列的列宽
        sheet.col(3).width = 3999
        sheet.col(4).width = 13999
        sheet.col(5).width = 13999
        sheet.col(6).width = 5555

        al = xlwt.Alignment()

        borders = xlwt.Borders()
        al.horz = 0x02  # 设置水平居中
        al.vert = 0x01

        style1 = xlwt.XFStyle()  # 字体初始化
        font = xlwt.Font()
        font.bold = True

        style1.alignment = al
        style1.font = font

        borders.left = 2
        borders.right = 2  # 边框
        borders.top = 2
        borders.bottom = 2

        style2 = xlwt.XFStyle()
        style2.alignment = al
        style2.borders = borders

        titles = ('咨询时间', '姓名', '性别', '手机号', '住址', '工作单位', '录入时间')
        queryset1 = queryset.first()
        for col_index, title in enumerate(titles):  # 写入表头
            sheet.write(0, col_index, title, style1)

        tis = (queryset1.write_time,
               queryset1.user.username,
               queryset1.user.sex,
               queryset1.user.tel,
               queryset1.user.address,
               queryset1.user.work_address,
               queryset1.user.write_time
               )
        for i, value in enumerate(tis):  # 写入人员对象信息
            sheet.write(1, i, value, style=style2)

        sheet.write_merge(2, 2, 0, 6, label='咨询日志', style=style1)  # 合并单元格加表头

        a = index_excel(3, 5, queryset.count())  # 生成合并索引

        for row_index, user in enumerate(queryset):  # 写入日志信息
            sheet.write_merge(a[row_index][0], a[row_index][1], 0, 6, label=user.info, style=style2)

        buffer = io.BytesIO()  # 写入内存
        wb.save(buffer)
        resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
        filename = quote('日志记录汇总统计表.xls')
        resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
        return resp
    else:
        response_500['msg'] = '不支持该请求方式'
        return Response(response_500)





# 获取合并单元格的索引
def index_excel(start, step, number):
    '''
    :param start: 开始位置
    :param step: 步长
    :param number: 生成的个数
    :return: list
    '''
    try:
        if isinstance(start, int) and isinstance(step, int) and isinstance(number, int):
            assert start <= number * step + step
            a = [i for i in range(start, number * step + step + 1, step)]
            b = [k - 1 for i, k in enumerate(a) if i != 0]
            return list(zip(a, b))
        raise TypeError
    except TypeError as ext:
        print('参数格式有误', ext)
        
if __name__ == '__main__':
	print(index_excel(3, 3, 5))  # 生成5个步长为3的
	输出: [(3, 5), (6, 8), (9, 11), (12, 14), (15, 17)]
	
	print(index_excel(3, 3, 10))  #  生成10个步长为3的
	[(3, 5), (6, 8), (9, 11), (12, 14), (15, 17), (18, 20), (21, 23), (24, 26), (27, 29), (30, 32)]

效果图