spring boot excel 文件导出
目标：
实现excel文件的直接导出下载，后续开发不需要开发很多代码，直接继承已经写好的代码，增加一个xml配置就可以直接导出。
实现：
1、抽象类 baseexcelview 继承 webmvc 的 abstractxlsxstreamingview 抽象类， abstractxlsxstreamingview 是webmvc继承了最顶层view接口，是可以直接大量数据导出的不会造成内存泄漏问题，即 sxssfworkbook 解决了内存问题， 导出只支持xlsx类型文件。
抽象类代码 baseexcelview ：
|
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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
public abstract class baseexcelview extends abstractxlsxstreamingview { private static final logger logger = loggerfactory.getlogger(baseexcelview.class); /** * 获取导出文件名 * * @return */ abstract protected string getfilename(); /** * 获取表单名称 * * @return */ abstract protected string getsheetname(); /** * 获取标题栏名称 * * @return */ abstract protected string[] gettitles(); /** * 获取列宽 * * @return */ abstract protected short[] getcolumnwidths(); /** * 构造内容单元格 * * @param sheet */ abstract protected void buildcontentcells(sheet sheet); @override protected void buildexceldocument( map<string, object> model, workbook workbook, httpservletrequest request, httpservletresponse response) throws exception { // 构造标题单元格 sxssfworkbook sheet sheet = buildtitlecells(workbook); // 构造内容单元格 buildcontentcells(sheet); // 设置响应头 setresponsehead(request, response); } /** * 设置响应头 * * @param response * @throws ioexception */ protected void setresponsehead(httpservletrequest request, httpservletresponse response) throws ioexception { // 文件名 string filename = getfilename(); string useragent = request.getheader("user-agent").tolowercase(); logger.info("客户端请求头内容:"); logger.info("user-agent\t值: {}", useragent); if (useragent != null) { if (useragent.contains("firefox")) { // firefox有默认的备用字符集是西欧字符集 filename = new string(filename.getbytes("utf-8"), "iso8859-1"); } else if (useragent.contains("webkit") && (useragent.contains("chrome") || useragent.contains("safari"))) { // webkit核心的浏览器,主流的有chrome,safari,360 filename = new string(filename.getbytes("utf-8"), "iso8859-1"); } else { // 新老版本的ie都可直接用url编码工具编码后输出正确的名称,无乱码 filename = urlencoder.encode(filename, "utf-8"); } } //响应头信息 response.setcharacterencoding("utf-8"); response.setcontenttype("application/ms-excel; charset=utf-8"); response.setheader("content-disposition", "attachment; filename=" + filename + ".xlsx"); } /** * 构造标题单元格 * * @param * @return */ protected sheet buildtitlecells(workbook workbook) { // 表单名称 string sheetname = getsheetname(); // 标题名称 string[] titles = gettitles(); // 列宽 short[] colwidths = getcolumnwidths(); // 创建表格 sheet sheet = workbook.createsheet(sheetname); // 标题单元格样式 cellstyle titlestyle = getheadstyle(workbook); // 默认内容单元格样式 cellstyle contentstyle = getbodystyle(workbook); // 标题行 row titlerow = sheet.createrow(0); // 创建标题行单元格 for (int i = 0; i < titles.length; i++) { // 标题单元格 cell cell = titlerow.createcell((short) i); cell.setcelltype(celltype.string); cell.setcellvalue(new xssfrichtextstring(titles[i])); cell.setcellstyle(titlestyle); // 设置列宽 sheet.setcolumnwidth((short) i, (short) (colwidths[i] * 256)); // 设置列默认样式 sheet.setdefaultcolumnstyle((short) i, contentstyle); } return sheet; } /** * 设置表头的单元格样式 */ public cellstyle getheadstyle(workbook workbook) { // 创建单元格样式 cellstyle cellstyle = workbook.createcellstyle(); // 设置单元格的背景颜色为淡蓝色 cellstyle.setfillforegroundcolor(indexedcolors.pale_blue.index); // 设置填充字体的样式 cellstyle.setfillpattern(fillpatterntype.solid_foreground); // 设置单元格居中对齐 cellstyle.setalignment(horizontalalignment.center); // 设置单元格垂直居中对齐 cellstyle.setverticalalignment(verticalalignment.center); // 创建单元格内容显示不下时自动换行 cellstyle.setwraptext(true); // 设置单元格字体样式 font font = workbook.createfont(); // 字号 font.setfontheightinpoints((short) 12); // 加粗 font.setbold(true); // 将字体填充到表格中去 cellstyle.setfont(font); // 设置单元格边框为细线条（上下左右） cellstyle.setborderleft(borderstyle.thin); cellstyle.setborderbottom(borderstyle.thin); cellstyle.setborderright(borderstyle.thin); cellstyle.setbordertop(borderstyle.thin); return cellstyle; } /** * 设置表体的单元格样式 */ public cellstyle getbodystyle(workbook workbook) { // 创建单元格样式 cellstyle cellstyle = workbook.createcellstyle(); // 设置单元格居中对齐 cellstyle.setalignment(horizontalalignment.center); // 设置单元格居中对齐 cellstyle.setverticalalignment(verticalalignment.center); // 创建单元格内容不显示自动换行 cellstyle.setwraptext(true); //设置单元格字体样式字体 font font = workbook.createfont(); // 字号 font.setfontheightinpoints((short) 10); // 将字体添加到表格中去 cellstyle.setfont(font); // 设置单元格边框为细线条 cellstyle.setborderleft(borderstyle.thin); cellstyle.setborderbottom(borderstyle.thin); cellstyle.setborderright(borderstyle.thin); cellstyle.setbordertop(borderstyle.thin); return cellstyle; }} |
excel导出实现 1: 可以直接继承 baseexcelview 实现定义的方法 eg:
|
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
|
public class checkexcelview extends baseexcelview { private list<t> vo; public checkexcelview(list<t> vo) { this.vo= vo; } @override protected string getfilename() { string time = dateutils.getlocalfulldatetime14(); return "导出文件" + time; } @override protected string getsheetname() { return "报表"; } @override protected string[] gettitles() { return new string[] { "申请时间"}; } @override protected short[] getcolumnwidths() { return new short[] { 20}; } @override protected void buildcontentcells(sheet sheet) { decimalformat df = new decimalformat("0.00"); int rownum = 1; for (t o : vo) { row crow = sheet.createrow(rownum++); crow.createcell(0).setcellvalue(o.getapplicationdate())); } }} |
导出实现 2： xml配置导出
1、需要定义xml的配置 export-config.xml
|
1
2
3
4
5
6
7
8
|
<?xml version="1.0" encoding="utf-8"?><configuration> <table id="demo" name="测试"> <columns> <column id="name" name="名称" width="40"></column> </columns> </table></configuration> |
2、xml解析配置
|
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
68
69
70
71
|
@rootpublic class export { @elementlist(entry = "table", inline = true) private list<table> table; public list<table> gettable() { return table; } public void settable(list<table> table) { this.table = table; } public static class table { @attribute private string id; @attribute private string name; @elementlist(entry = "column") private list<column> columns; public string getid() { return id; } public void setid(string id) { this.id = id; } public string getname() { return name; } public void setname(string name) { this.name = name; } public list<column> getcolumns() { return columns; } public void setcolumns(list<column> columns) { this.columns = columns; } } public static class column { @attribute private string id; @attribute private string name; @attribute private short width; @attribute(required = false) private string mapping; public string getid() { return id; } public void setid(string id) { this.id = id; } public string getname() { return name; } public void setname(string name) { this.name = name; } public string getmapping() { return mapping; } public void setmapping(string mapping) { this.mapping = mapping; } public short getwidth() { return width; } public void setwidth(short width) { this.width = width; } }} |
3、解析xml方法配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
@servicepublic class iexportservice { private export tables; private map<string, export.table> tablemap; @suppresswarnings("rawtypes") @postconstruct public void init() throws exception { inputstream inputstream = this.getclass().getclassloader().getresourceasstream("export-config.xml"); serializer serializer = new persister(); tables = serializer.read(export.class, inputstream); tablemap = new hashmap<>(); for (export.table table : tables.gettable()) { tablemap.put(table.getid(), table); } } public export.table gettable(string key) { return tablemap.get(key); }} |
4、导出基础 excelexportview 代码实现
|
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
|
public class excelexportview extends baseexcelview { private string[] titles; private short[] columnwidths; list<map<string, object>> results; private export.table table; private iexportservice iexportservice; @override protected string getfilename() { return table.getname(); } @override protected string getsheetname() { return table.getname(); } @override protected string[] gettitles() { return this.titles; } @override protected short[] getcolumnwidths() { return this.columnwidths; } public excelexportview() { this.iexportservice = applicationcontextprovider.getbean(iexportservice.class); } @override protected void buildcontentcells(sheet sheet) { int dataindex = 1; if(collectionutils.isempty(results)){ return; } for (map<string, object> data : results) { row row = sheet.createrow(dataindex++); for (int i = 0; i < table.getcolumns().size(); i++) { export.column column = table.getcolumns().get(i); cell cell = row.createcell(i); object value = data.get(column.getid()); if (value == null) { value = ""; } cell.setcellvalue(new xssfrichtextstring(value.tostring())); } } } public void exportexcel(string key, list<map<string, object>> results) { this.table = iexportservice.gettable(key); if (null == table) { return; } this.results = results; this.titles = new string[table.getcolumns().size()]; this.columnwidths = new short[table.getcolumns().size()]; for (int i = 0; i < table.getcolumns().size(); i++) { export.column column = table.getcolumns().get(i); titles[i] = column.getname(); columnwidths[i] = column.getwidth(); } }} |
最后：导出controller代码实现
|
1
2
3
4
5
6
7
8
9
|
@requestmapping(path = "/export", method = requestmethod.get, produces = "application/octet-stream;charset=utf-8")public @responsebodymodelandview export(){ long logincomid = logincontext.getcompany().getid(); list<t> list = new arraylist<>(); excelexportview exportview = new excelexportview(); exportview.exportexcel("xml中表的id", beanutils.objecttomaplist(list)); return new modelandview(exportview);<em id="__mcedel"><em id="__mcedel">}</em></em> |
总结
以上所述是小编给大家介绍的spring boot excel文件导出下载实现代码，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
原文链接：http://www.cnblogs.com/cuigd/p/9968477.html
