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

像风一样的男人@

已于 2023-07-29 01:54:11 修改

阅读量1.9k

点赞数 2

分类专栏：笔记文章标签： python django excel

于 2020-07-28 17:12:29 首次发布

本文链接：https://blog.csdn.net/weixin_44634704/article/details/107637178

版权

笔记专栏收录该内容

147 篇文章 31 订阅

订阅专栏

前端统计图展示参考
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

导出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)]

效果图
在这里插入图片描述

像风一样的男人@

关注

2
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
1
评论
Django学习-导出报表功能、xlwt库的使用、pdf、前端

安装第三方库有很多的三方库支持在Python程序中写Excel问件，包括xlwt、xlwings、openpyxl、xlswriter、pandas等，其中的xlwt虽然只支持写xls格式的Excel文件，但在性能方面的表现还是不错的。下面我们就以xlwt为例，来演示如何在Django项目中导出Excel报表，例如导出一个包含所有老师信息的Excel表格。pip install xlwt -i https://pypi.doubanio.com/simplexlwt详解官文地址搜索pypi.o
复制链接

扫一扫