使用xlwt

  • 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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

  • 示例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# 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)