一、導入相關jar包，pom依賴如下：
|
1
2
3
4
5
|
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>RELEASE</version></dependency> |
二、開始擼代碼
1.如果導出功能使用的比較多，可以將其做成一個工具類，對我下面貼出的代碼進行改造
|
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
|
//結果返回的是寫入的記錄數（以下用的是自己業務場景數據） public int downLoadToExcel(OutputStream outputStream,List<PaimaiMoneyVO> paimaiMoneyVOList) { //文檔對象 HSSFWorkbook wb = new HSSFWorkbook(); int rowNum = 0; Row row0 = sheet.createRow(rowNum++); //因為場景不同，titil不同，可以在外面寫成數組當參數傳進來 row0.createCell(0).setCellValue("第一列屬性名"); row0.createCell(1).setCellValue("第二列屬性名"); row0.createCell(2).setCellValue("第三列屬性名"); row0.createCell(3).setCellValue("第四列屬性名"); row0.createCell(4).setCellValue("第五列屬性名"); row0.createCell(5).setCellValue("第六列屬性名"); if (paimaiMoneyVOList != null && paimaiMoneyVOList.size() > 0) { for (PaimaiMoneyVO paimaiMoneyVO : paimaiMoneyVOList) { Row row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(paimaiMoneyVO.getPaimaiId()); row.createCell(1).setCellValue(paimaiMoneyVO.getTitle()); row.createCell(2).setCellValue(paimaiMoneyVO.getUsername()); row.createCell(3).setCellValue(paimaiMoneyVO.getMoney()+"元"); row.createCell(4).setCellValue("升價拍"); row.createCell(5).setCellValue(bidder); } } try { wb.write(outputStream); LogEnum.LAW_WARE.info("表數據寫入到excel表成功,一共寫入了"+(rowNum - 1)+"條數據"); outputStream.close(); } catch (IOException e) { LogEnum.LAW_WARE.error("流關閉異常！", e); } finally { if (outputStream != null) { try { outputStream.close(); } catch (IOException e) { LogEnum.LAW_WARE.error("流關閉異常！", e); } } } return rowNum - 1; } |
2.“工具類”寫好后，下面就開始使用它了，從上面的函數參數可以看到，我們需要傳過去兩個對象，一個是輸出流OutPutStream，通過流的方式把excel想要到瀏覽器，
另外一個就是我們需要導出的對象數組，好了，不解釋太多，看代碼。（下面的方法寫在action層，通過struts.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
|
public void exportBail(){ this.fileName = "excel文件名"; try { List<PaimaiMoneyVO> paimaiMoneyVOList = new ArrayList<>(); //下面是我的業務場景獲取對象數組 if(paimaiMoneySearchParam!=null){ paimaiMoneySearchParam.setVendorId(WebHelper.getVenderId()); paimaiMoneySearchParam.setPageSize(Constants.AUCTION_WARE_PAGE_SIZE); paimaiMoneySearchParam.setPage(page); PaimaiMoneyDto paimaiMoneyDto = auctionWareService1.searchPopPaimaiMoneyList(paimaiMoneySearchParam); if(paimaiMoneyDto!=null){ int count = paimaiMoneyDto.getCount(); int totalPage = count/ Constants.AUCTION_WARE_PAGE_SIZE + (count% Constants.AUCTION_WARE_PAGE_SIZE > 0?1:0); for(int i=1;i<=totalPage;i++){ paimaiMoneySearchParam.setPage(i); PaimaiMoneyDto paimaiMoneyResultResult = auctionWareService1.searchPopPaimaiMoneyList(paimaiMoneySearchParam); if(paimaiMoneyResultResult!=null){ paimaiMoneyVOList.addAll(paimaiMoneyResultResult.getList()); } } } } OutputStream outputStream = response.getOutputStream(); response.reset();//清空輸出流 //下面是對中文文件名的處理 response.setCharacterEncoding("UTF-8");//設置相應內容的編碼格式 //解析瀏覽器 final String userAgent = request.getHeader("USER-AGENT").toLowerCase(); if(userAgent.contains("firefox")){ //火狐瀏覽器 fileName = new String(fileName.getBytes(), "ISO8859-1"); }else{ fileName = URLEncoder.encode(fileName, "UTF-8"); //其他瀏覽器 fileName = fileName.Replace("+", "%20"); //encode后替換，解決空格問題（其中%20是空格在UTF-8下的編碼 ，如果不這么寫，瀏覽器會用+代替空格） } response.setHeader("Content-Disposition", "attachment;filename=" +fileName + ".xls");//指定輸出文件名 response.setContentType("application/msexcel");//定義輸出類型 int rouNum = ensurePriceListToExcel(outputStream,paimaiMoneyVOList); LogEnum.LAW_WARE.info("【RiseAuctionAction.downLoadEnsurePriceExcel】導出成功,一共更新了{"+rouNum+"}條記錄"); } catch (Exception e) { LogEnum.LAW_WARE.error("【RiseAuctionAction.downLoadEnsurePriceExcel】導出失敗,error is {}", e); } } |
三、拓展（詳細的工具類開發）
如果你覺得上面寫的太簡單了，可以繼續往下看，我把它整理出了“萬能”的工具類，供大家參考。
java" id="highlighter_381520">
|
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
|
package com.jd.pop.auction.util.excel;import com.jd.common.web.result.Result;import com.jd.pop.auction.util.excel.annotations.ExcelColumn;import com.jd.pop.auction.util.excel.annotations.ExcelMapping;import com.jd.pop.auction.util.excel.annotations.apt.ExcelColumnAPT;import com.jd.pop.auction.util.excel.annotations.apt.ExcelMappingAPT;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.util.CellRangeAddress;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.util.Collection;import java.util.Iterator;import java.util.List;public class GenerateExcel { private final static Logger LOG = Logger.getLogger(GenerateExcel.class); private HSSFWorkbook workbook; private HSSFCellStyle headStyle; private HSSFFont headCellFont; private HSSFCellStyle theadStyle; private HSSFFont theadCellFont; private HSSFCellStyle tbodyStyle; private HSSFFont tbodyCellFont; private HSSFFont stringFont; private static final short COLUMN_WIDTH = 15; private static final short ROW_HEIGHT = 400; public GenerateExcel() { this.workbook = new HSSFWorkbook(); //標題 this.headStyle = workbook.createCellStyle(); headStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// headStyle.setWrapText(true); this.headCellFont = workbook.createFont(); headCellFont.setFontHeightInPoints((short)13); headCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headStyle.setFont(headCellFont); this.theadStyle = workbook.createCellStyle(); theadStyle.setFillForegroundColor(HSSFColor.WHITE.index); theadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); theadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); theadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); theadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); theadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); theadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); theadCellFont = workbook.createFont(); theadCellFont.setColor(HSSFColor.BLACK.index); theadCellFont.setFontHeightInPoints((short) 12); theadCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); theadStyle.setFont(theadCellFont); tbodyStyle = workbook.createCellStyle(); tbodyStyle.setFillForegroundColor(HSSFColor.WHITE.index); tbodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); tbodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); tbodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); tbodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); tbodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); tbodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); tbodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); tbodyCellFont = workbook.createFont(); tbodyCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); tbodyStyle.setFont(tbodyCellFont); stringFont = workbook.createFont(); stringFont.setColor(HSSFColor.BLACK.index); } public <T> Result export(List<String> titles, Field[] fields, Class clazz, Collection<T> dataset, OutputStream out, boolean pager) { Result result = new Result(false); if(pager){ }else{ HSSFSheet sheet = workbook.createSheet( "第一頁"); sheet.setDefaultColumnWidth(COLUMN_WIDTH); sheet.setDefaultRowHeight(ROW_HEIGHT); //標題 for (int i = 0; i <titles.size(); i++) { HSSFRow titleRow = sheet.createRow(i); titleRow.setHeightInPoints(20f); sheet.addMergedRegion(new CellRangeAddress(i,i,0,fields.length-1)); HSSFCell titleCell =titleRow.createCell(0); titleCell.setCellValue(titles.get(i)); titleCell.setCellStyle(headStyle); } //列名 HSSFRow row = sheet.createRow(titles.size()); for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(theadStyle); if(fields[i].isAnnotationPresent(ExcelColumn.class)){ ExcelColumn an_1 = fields[i].getAnnotation(ExcelColumn.class); HSSFRichTextString text = new HSSFRichTextString(an_1.name()); cell.setCellValue(text); }else if(fields[i].isAnnotationPresent(ExcelMapping.class)){ ExcelMapping an_1 = fields[i].getAnnotation(ExcelMapping.class); HSSFRichTextString text = new HSSFRichTextString(an_1.name()); cell.setCellValue(text); } } Iterator<T> it = dataset.iterator(); int index = titles.size(); while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(tbodyStyle); Field field = fields[i]; try { String textValue; if(field.isAnnotationPresent(ExcelMapping.class)){ textValue = new ExcelMappingAPT().getColumnValue(field,t,clazz); }else{ textValue = new ExcelColumnAPT().getColumnValue(field,t,clazz); } cell.setCellValue(textValue); } catch (NoSuchMethodException e) { String errorMsg = field.getName() +"字段，第"+ index+ "條數據， NoSuchMethodException 反射錯誤！"; LOG.error(errorMsg,e); result.addDefaultModel(errorMsg); return result; } catch (IllegalAccessException e) { String errorMsg = field.getName() +"字段，第"+ index+ "條數據， IllegalAccessException "; LOG.error(errorMsg,e); result.addDefaultModel(errorMsg); return result; } catch (InvocationTargetException e) { String errorMsg = field.getName() +"字段，第"+ index+ "條數據， InvocationTargetException "; LOG.error(errorMsg,e); result.addDefaultModel(errorMsg); return result; } } } } try { workbook.write(out); result.setSuccess(true); return result; } catch (IOException e) { String errorMsg = "將導出數據寫入輸出流失敗!"; LOG.error("將導出數據寫入輸出流失敗！ ",e); result.addDefaultModel(errorMsg); return result; }finally { try { out.close(); } catch (IOException e) { String errorMsg = "關閉輸出流異常!"; LOG.error("關閉輸出流異常！ ",e); result.addDefaultModel(errorMsg); return result; } } }} |
|
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
|
public class ExportExcelUtils { private final static Logger LOG = Logger.getLogger(ExportExcelUtils.class); public static <T> Result export(List<String> titles,List<T> sourceList, OutputStream out, boolean pager){ Result result = new Result(false); if(CollectionUtils.isEmpty(sourceList)){ result.addDefaultModel("ExportExcelUtils's param sourceList is empty!"); LOG.error("ExportExcelUtils's param sourceList is empty!"); return result; } if( out == null){ LOG.error("ExportExcelUtils's param OutputStream is null!"); result.addDefaultModel("ExportExcelUtils's param OutputStream is null!"); return result; } Class clazz = null; Field[] fieldArr = null; try{ //得到需要轉換的列名 clazz = sourceList.get(0).getClass(); Field[] fields = clazz.getDeclaredFields(); List<Field> fieldList = new ArrayList<Field>(); for(Field field:Arrays.asList(fields)){ field.setAccessible(true); if(field.isAnnotationPresent(ExcelColumn.class)){ fieldList.add(field); }else if(field.isAnnotationPresent(ExcelMapping.class)){ fieldList.add(field); } } if(CollectionUtils.isEmpty(fieldList)){ LOG.error("實體類中無需要導出的字段!"); result.addDefaultModel("實體類中無需要導出的字段！"); return result; } fieldArr = fieldList.toArray(new Field[fieldList.size()]); }catch(Exception e){ LOG.error("數據拼裝異常!"); result.addDefaultModel("數據拼裝異常!"); return result; } //生成excel GenerateExcel ge = new GenerateExcel(); return ge.export(titles,fieldArr,clazz,sourceList,out,false); }} |
這一部分寫的比較粗糙，但是實現的比較詳細，僅供參考，大家可以稍微改造成為自己獨有的utils。
以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，同時也希望多多支持服務器之家！
原文鏈接：http://www.cnblogs.com/xiaoming0601/p/6628860.html
