Quickly Export Excel Files in Web Development (with Code)

In web development you often run into the need to export data. This post mainly covers how to quickly export data and save it as an Excel file.

1. Frontend

In web development, formatted data is usually displayed as a table. Below is an employee salary table; we will use exporting this data as the example.

Name	Position	Age	Salary
Tiger NixonTiger Nixon	System Architect	61	$320,800
Garrett Winters	Accountant	63	$170,750
Ashton Cox	Junior Technical Author	66	$86,000
Cedric Kelly	Senior Javascript Developer	22	$433,060
Herrod Chandler	Sales Assistant	59	$327,900

1.1 tableExport

tableExport is a jQuery table-export plugin that supports exporting to: JSON, XML, PNG, CSV, TXT, SQL, MS-Word, Ms-Excel, Ms-Powerpoint, PDF.

Note that if the table headers contain Chinese, the original project’s jquery.base64.js will throw an error: Uncaught INVALID_CHARACTER_ERR: DOM Exception 5 VM2832 jquery.base64.js:136, and it needs to be updated. The plugin from the official jQuery site is recommended, go here. The native tableExport project is not friendly to exporting Chinese data.

Include the js

1
2
<script type="text/javascript" src="tableExport.js"></script>
<script type="text/javascript" src="jquery.base64.js"></script>

Export PNG

1
<script type="text/javascript" src="html2canvas.js"></script>

Export PDF

1
2
3
<script type="text/javascript" src="jspdf/libs/sprintf.js"></script>
<script type="text/javascript" src="jspdf/jspdf.js"></script>
<script type="text/javascript" src="jspdf/libs/base64.js"></script>

Usage

Just call the plugin’s tableExport method and set the export type. It is recommended to set escape to true, otherwise Chinese text will come out garbled.

1
2
3
4
5
6
7
8
<script src='js/jquery.base64.js'></script>
<script src='js/tableExport.js'></script>
<script>
function tableexport_export(){
    $('#tableID').tableExport({type:'excel',excape:'true'});
}
</script>
<button onClick="tableexport_export()">tableExport导出Excel</button>

1.2 kendoGrid

Kendo UI is a powerful framework for rapidly developing HTML5 UIs. It is based on the HTML5, CSS3, and JavaScript standards. Kendo UI includes everything needed for modern JavaScript development, including: a powerful data source, general drag-and-drop support, templates, and UI controls. kendoGrid can export table data to an Excel file with just a little configuration.

 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="styles/kendo.common.min.css"/>
<script src='js/kendo.all.min.js'></script>
<script src='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.3 DataTables

kendoGrid is a commercial frontend table tool, so its use is somewhat restricted. If your project team permits the DataTables table tool, it is a good choice too. DataTables likewise provides a rich set of tools for exporting data.
Note that if the table data contains special characters such as $, the customizeData function needs to handle them specially, otherwise the exported data will come out garbled.

 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="extensions/Buttons/css/buttons.dataTables.min.css"/>
<script src='js/jquery.dataTables.min.js'></script>
<script src='js/dataTables.buttons.min.js'></script>
<script type="text/javascript" src="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>

2. Backend

When the volume of data is large and the backend paginates, the frontend cannot export the complete dataset. In that case you can export from the backend; here we use Django as the example and return an excel file directly to the frontend.

 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