spring boot excel 文件導(dǎo)出
目標：
實現(xiàn)excel文件的直接導(dǎo)出下載，后續(xù)開發(fā)不需要開發(fā)很多代碼，直接繼承已經(jīng)寫好的代碼，增加一個xml配置就可以直接導(dǎo)出。
實現(xiàn)：
1、抽象類 baseexcelview 繼承 webmvc 的 abstractxlsxstreamingview 抽象類， abstractxlsxstreamingview 是webmvc繼承了最頂層view接口，是可以直接大量數(shù)據(jù)導(dǎo)出的不會造成內(nèi)存泄漏問題，即 sxssfworkbook 解決了內(nèi)存問題， 導(dǎo)出只支持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); /** * 獲取導(dǎo)出文件名 * * @return */ abstract protected string getfilename(); /** * 獲取表單名稱 * * @return */ abstract protected string getsheetname(); /** * 獲取標題欄名稱 * * @return */ abstract protected string[] gettitles(); /** * 獲取列寬 * * @return */ abstract protected short[] getcolumnwidths(); /** * 構(gòu)造內(nèi)容單元格 * * @param sheet */ abstract protected void buildcontentcells(sheet sheet); @override protected void buildexceldocument( map<string, object> model, workbook workbook, httpservletrequest request, httpservletresponse response) throws exception { // 構(gòu)造標題單元格 sxssfworkbook sheet sheet = buildtitlecells(workbook); // 構(gòu)造內(nèi)容單元格 buildcontentcells(sheet); // 設(shè)置響應(yīng)頭 setresponsehead(request, response); } /** * 設(shè)置響應(yīng)頭 * * @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("客戶端請求頭內(nèi)容:"); 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"); } } //響應(yīng)頭信息 response.setcharacterencoding("utf-8"); response.setcontenttype("application/ms-excel; charset=utf-8"); response.setheader("content-disposition", "attachment; filename=" + filename + ".xlsx"); } /** * 構(gòu)造標題單元格 * * @param * @return */ protected sheet buildtitlecells(workbook workbook) { // 表單名稱 string sheetname = getsheetname(); // 標題名稱 string[] titles = gettitles(); // 列寬 short[] colwidths = getcolumnwidths(); // 創(chuàng)建表格 sheet sheet = workbook.createsheet(sheetname); // 標題單元格樣式 cellstyle titlestyle = getheadstyle(workbook); // 默認內(nèi)容單元格樣式 cellstyle contentstyle = getbodystyle(workbook); // 標題行 row titlerow = sheet.createrow(0); // 創(chuàng)建標題行單元格 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); // 設(shè)置列寬 sheet.setcolumnwidth((short) i, (short) (colwidths[i] * 256)); // 設(shè)置列默認樣式 sheet.setdefaultcolumnstyle((short) i, contentstyle); } return sheet; } /** * 設(shè)置表頭的單元格樣式 */ public cellstyle getheadstyle(workbook workbook) { // 創(chuàng)建單元格樣式 cellstyle cellstyle = workbook.createcellstyle(); // 設(shè)置單元格的背景顏色為淡藍色 cellstyle.setfillforegroundcolor(indexedcolors.pale_blue.index); // 設(shè)置填充字體的樣式 cellstyle.setfillpattern(fillpatterntype.solid_foreground); // 設(shè)置單元格居中對齊 cellstyle.setalignment(horizontalalignment.center); // 設(shè)置單元格垂直居中對齊 cellstyle.setverticalalignment(verticalalignment.center); // 創(chuàng)建單元格內(nèi)容顯示不下時自動換行 cellstyle.setwraptext(true); // 設(shè)置單元格字體樣式 font font = workbook.createfont(); // 字號 font.setfontheightinpoints((short) 12); // 加粗 font.setbold(true); // 將字體填充到表格中去 cellstyle.setfont(font); // 設(shè)置單元格邊框為細線條（上下左右） cellstyle.setborderleft(borderstyle.thin); cellstyle.setborderbottom(borderstyle.thin); cellstyle.setborderright(borderstyle.thin); cellstyle.setbordertop(borderstyle.thin); return cellstyle; } /** * 設(shè)置表體的單元格樣式 */ public cellstyle getbodystyle(workbook workbook) { // 創(chuàng)建單元格樣式 cellstyle cellstyle = workbook.createcellstyle(); // 設(shè)置單元格居中對齊 cellstyle.setalignment(horizontalalignment.center); // 設(shè)置單元格居中對齊 cellstyle.setverticalalignment(verticalalignment.center); // 創(chuàng)建單元格內(nèi)容不顯示自動換行 cellstyle.setwraptext(true); //設(shè)置單元格字體樣式字體 font font = workbook.createfont(); // 字號 font.setfontheightinpoints((short) 10); // 將字體添加到表格中去 cellstyle.setfont(font); // 設(shè)置單元格邊框為細線條 cellstyle.setborderleft(borderstyle.thin); cellstyle.setborderbottom(borderstyle.thin); cellstyle.setborderright(borderstyle.thin); cellstyle.setbordertop(borderstyle.thin); return cellstyle; }} |
excel導(dǎo)出實現(xiàn) 1: 可以直接繼承 baseexcelview 實現(xiàn)定義的方法 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 "導(dǎo)出文件" + 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())); } }} |
導(dǎo)出實現(xiàn) 2： xml配置導(dǎo)出
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、導(dǎo)出基礎(chǔ) excelexportview 代碼實現(xiàn)
|
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(); } }} |
最后：導(dǎo)出controller代碼實現(xiàn)
|
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> |
總結(jié)
以上所述是小編給大家介紹的spring boot excel文件導(dǎo)出下載實現(xiàn)代碼，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對服務(wù)器之家網(wǎng)站的支持！
原文鏈接：http://www.cnblogs.com/cuigd/p/9968477.html
