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
