Web开发中快速导出Excel文件(附代码)

1
2

<script type="text/javascript" src="https://www.chenshaowen.com/blog/tableExport.js"></script>
<script type="text/javascript" src="https://www.chenshaowen.com/blog/jquery.base64.js"></script>

1

<script type="text/javascript" src="https://www.chenshaowen.com/blog/html2canvas.js"></script>

1
2
3

<script type="text/javascript" src="https://www.chenshaowen.com/blog/jspdf/libs/sprintf.js"></script>
<script type="text/javascript" src="https://www.chenshaowen.com/blog/jspdf/jspdf.js"></script>
<script type="text/javascript" src="https://www.chenshaowen.com/blog/jspdf/libs/base64.js"></script>

1
2
3
4
5
6
7
8

<script src='https://www.chenshaowen.com/blog/js/jquery.base64.js'></script>
<script src='https://www.chenshaowen.com/blog/js/tableExport.js'></script>
<script>
function tableexport_export(){
    $('#tableID').tableExport({type:'excel',excape:'true'});
}
</script>
<button onClick="tableexport_export()">tableExport导出Excel</button>

 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

<div id="kendogrid_table"></div>
<link rel="stylesheet" type="text/css" href="https://www.chenshaowen.com/blog/styles/kendo.common.min.css"/>
<script src='https://www.chenshaowen.com/blog/js/kendo.all.min.js'></script>
<script src='https://www.chenshaowen.com/blog/js/jszip.min.js'></script>
$(document).ready(function() {
    $('#tableID').kendoGrid({
        pageable: false,
        sortable: true,
        dataSource: [
            {'name': 'Tiger Nixon', 'position': 'System Architect', 'age': '61', 'salary': '$320,800'},
            {'name': 'Garrett Winters', 'position': 'Accountant', 'age': '63', 'salary': '$170,750'},
            {'name': 'Ashton Cox', 'position': 'Junior Technical Author', 'age': '66', 'salary': '$86,000'},
            {'name': 'Cedric Kelly', 'position': 'Senior Javascript Developer', 'age': '22', 'salary': '$433,060'},
            {'name': 'Herrod Chandler', 'position': 'Sales Assistant', 'age': '59', 'salary': '	$137,500'},
            {'name': 'Rhona Davidson', 'position': 'Integration Specialist', 'age': '55', 'salary': '$327,900'},
        ],
        toolbar: ['excel'],
        columns: [
            {
                field: 'name',
                title: '姓名'
            },
            {
                field: 'position',
                title: '职位'
            },
            {
                field: 'age',
                title: '年龄'
            },
            {
                field: 'salary',
                title: '薪水'
            }
        ]
    })
});

 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

<link rel="stylesheet" type="text/css" href="https://www.chenshaowen.com/blog/extensions/Buttons/css/buttons.dataTables.min.css"/>
<script src='https://www.chenshaowen.com/blog/js/jquery.dataTables.min.js'></script>
<script src='https://www.chenshaowen.com/blog/js/dataTables.buttons.min.js'></script>
<script type="text/javascript" src="https://www.chenshaowen.com/blog/extensions/Buttons/js/buttons.html5.min.js"></script>
<script>
    $(document).ready(function () {
        $('#tableID').DataTable({
            dom: 'Bfrtip',
            buttons: [{
                'extend': 'excel',
                'text': '导出',//定义导出excel按钮的文字
                customizeData: function (data) {
                    for (var i = 0; i < data.body.length; i++) {
                        for (var j = 0; j < data.body[i].length; j++) {
                            data.body[i][j] = '\u200C' + data.body[i][j];
                        }
                    }
                }
            }],
            paging: true, //隐藏分页
            ordering: false, //关闭排序
            info: false, //隐藏左下角分页信息
            searching: false, //关闭搜索
            lengthChange: false,
        });
    });
</script>

 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
57
58
59
60
61
62
63
64
65
66
67

# coding=utf-8
from django.http import HttpResponse
import xlwt

def get_export_excel_response(dic_data_list, filename='export'):
    '''
    :param dic_data_list = [{
            u"数据1": randint(0, 999),
            u"数据2": randint(0, 999),
            u"数据3": randint(0, 999),
            u"数据4": randint(0, 999),
            u"数据5": randint(0, 999),
            u"数据6": randint(0, 999),
            u"数据7": randint(0, 999),
            u"数据8": randint(0, 999),
        },{
            ......
        }]: 
    :param filename: 文件名
    :return: HttpResponse excel文件
    '''
    head_list = dic_data_list[0].keys() if dic_data_list else []
    # 创建excel表
    excel = xlwt.Workbook(encoding='utf-8', style_compression=2)
    worksheet = excel.add_sheet(filename)
    # 写入表头
    for col in range(0, len(head_list)):
        worksheet.col(col).width = 256 * 20
        worksheet.write(0, col, head_list[col], set_style('Times New Roman', 220, True))
    # 写入数据
    for row in range(1, len(dic_data_list) + 1):
        for col, single_head in enumerate(head_list):
            worksheet.write(row, col, dic_data_list[row - 1][single_head], right())

    response = HttpResponse(content_type="application/ms-excel")
    filename = filename.encode('utf-8')
    response['Content-Disposition'] = 'attachment;filename=%s.xls' % filename
    excel.save(response)
    return response


def set_style(name, height, bold=False):
    style = xlwt.XFStyle()  # 初始化样式
    font = xlwt.Font()  # 为样式创建字体
    font.name = name  # 'Times New Roman'
    font.bold = bold  # 是否粗体
    font.color_index = 4
    font.height = height
    al = xlwt.Alignment()
    al.horz = xlwt.Alignment.HORZ_CENTER  # 设置水平居中
    al.vert = xlwt.Alignment.VERT_CENTER  # 设置垂直居中
    al.wrap = xlwt.Alignment.WRAP_AT_RIGHT  # 设置文字可以换行
    style.alignment = al
    style.font = font
    # style.borders = borders
    return style


def right():
    style = xlwt.XFStyle()  # 初始化样式
    al = xlwt.Alignment()
    al.horz = xlwt.Alignment.HORZ_RIGHT  # 设置水平靠右
    al.vert = xlwt.Alignment.VERT_CENTER  # 设置垂直居中
    al.wrap = xlwt.Alignment.WRAP_AT_RIGHT  # 设置文字可以换行
    style.alignment = al

    return style