Apache POI导出excel表格

项目中我们经常用到导出功能，将数据导出以便于审查和统计等。本文主要使用Apache POI实现导出数据。

POI中文文档

简介

ApachePOI是Apache软件基金会的开放源码函式库，POI提供API给java程序对Microsoft Office格式档案读和写的功能。

HSSF概况

HSSF是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯java代码来读取、写入、修改Excel文件。HSSF为读取操作提供了两类API：usermodel和eventusermodel，既”用户模型“和”事件-用户模型“。

POI Excel 文档结构类

 1     HSSFWorkbook excel文档对象  
 2     HSSFSheet excel的sheet  
 3     HSSFRow excel的行  
 4     HSSFCell excel的单元格  
 5     HSSFFont excel字体  
 6     HSSFName 名称  
 7     HSSFDataFormat 日期格式  
 8     HSSFHeader sheet头  
 9     HSSFFooter sheet尾  
10     HSSFCellStyle cell样式  
11     HSSFDateUtil 日期  
12     HSSFPrintSetup 打印  
13     HSSFErrorConstants 错误信息表  

EXCEL常用操作方法

得到Excel常用对象

 1     POIFSFileSystem fs=newPOIFSFileSystem(new  FileInputStream("d:/test.xls"));     
 2     //得到Excel工作簿对象      
 3     HSSFWorkbook wb = new HSSFWorkbook(fs);    
 4     //得到Excel工作表对象      
 5     HSSFSheet sheet = wb.getSheetAt(0);     
 6     //得到Excel工作表的行      
 7     HSSFRow row = sheet.getRow(i);    
 8     //得到Excel工作表指定行的单元格      
 9     HSSFCell cell = row.getCell((short) j);    
10     cellStyle = cell.getCellStyle();//得到单元格样式     

建立Excel常用对象

1 HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象     
2 HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象       
3 HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行     
4 cellStyle = wb.createCellStyle();//创建单元格样式     
5 row.createCell((short)0).setCellStyle(cellStyle); //创建Excel工作表指定行的单元格     
6 row.createCell((short)0).setCellValue(1); //设置Excel工作表的值 

设置表格

 1     //设置sheet名称和单元格内容  
 2     wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);            
 3     cell.setEncoding((short) 1);        
 4     cell.setCellValue("单元格内容");    
 5       
 6     //取得sheet的数目  
 7     wb.getNumberOfSheets()     
 8       
 9     //根据index取得sheet对象  
10     HSSFSheet sheet = wb.getSheetAt(0);    
11       
12     //取得有效的行数  
13     int rowcount = sheet.getLastRowNum();    
14       
15     //取得一行的有效单元格个数  
16     row.getLastCellNum();      
17       
18     //单元格值类型读写  
19     cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型     
20     cell.getNumericCellValue();//读取为数值类型的单元格内容    
21       
22     //设置列宽、行高  
23     sheet.setColumnWidth((short)column,(short)width);        
24     row.setHeight((short)height);      
25       
26     //添加区域，合并单元格  
27     Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo,(short)columnTo);//合并从第rowFrom行columnFrom列     
28     sheet.addMergedRegion(region);// 到rowTo行columnTo的区域        
29     //得到所有区域         
30     sheet.getNumMergedRegions()     
31       
32     //保存Excel文件  
33     FileOutputStream fileOut = new FileOutputStream(path);     
34     wb.write(fileOut);   

 

开始实现

pom.xml

 1     <dependency>  
 2        <groupId>org.apache.poi</groupId>  
 3        <artifactId>poi</artifactId>  
 4        <version>${poi.version</version>  
 5     </dependency>  
 6       
 7     <dependency>  
 8        <groupId>org.apache.poi</groupId>  
 9        <artifactId>poiscratchpad</artifactId>  
10        <version>${poi.version</version>  
11     </dependency>  
12       
13     <dependency>  
14         <groupId>org.apache.poi</groupId>  
15         <artifactId>poiooxml</artifactId>  
16         <version>${poi.version</version>  
17     </dependency>  

excel导出工具类

  1     public class ExcelExport {  
  2         //表头  
  3         private String title;  
  4         //各个列的表头  
  5         private String[] heardList;  
  6         //各个列的元素key值  
  7         private String[] heardKey;  
  8         //需要填充的数据信息  
  9         private List<Map> data;  
 10         //字体大小  
 11         private int fontSize = 14;  
 12         //行高  
 13         private int rowHeight = 30;  
 14         //列宽  
 15         private int columWidth = 200;  
 16         //工作表  
 17         private String sheetName = "sheet1";  
 18       
 19         public String getTitle() {  
 20             return title;  
 21         }  
 22       
 23         public ExcelExport setTitle(String title) {  
 24             this.title = title;  
 25             return this;  
 26         }  
 27       
 28         public String[] getHeardList() {  
 29             return heardList;  
 30         }  
 31       
 32         public ExcelExport setHeardList(String[] heardList) {  
 33             this.heardList = heardList;  
 34             return this;  
 35         }  
 36       
 37         public String[] getHeardKey() {  
 38             return heardKey;  
 39         }  
 40       
 41         public ExcelExport setHeardKey(String[] heardKey) {  
 42             this.heardKey = heardKey;  
 43             return this;  
 44         }  
 45       
 46         public List<Map> getData() {  
 47             return data;  
 48         }  
 49       
 50         public ExcelExport setData(List<Map> data) {  
 51             this.data = data;  
 52             return this;  
 53         }  
 54       
 55         public int getFontSize() {  
 56             return fontSize;  
 57         }  
 58       
 59         public ExcelExport setFontSize(int fontSize) {  
 60             this.fontSize = fontSize;  
 61             return this;  
 62         }  
 63       
 64         public int getRowHeight() {  
 65             return rowHeight;  
 66         }  
 67       
 68         public ExcelExport setRowHeight(int rowHeight) {  
 69             this.rowHeight = rowHeight;  
 70             return this;  
 71         }  
 72       
 73         public int getColumWidth() {  
 74             return columWidth;  
 75         }  
 76       
 77         public ExcelExport setColumWidth(int columWidth) {  
 78             this.columWidth = columWidth;  
 79             return this;  
 80         }  
 81       
 82         public String getSheetName() {  
 83             return sheetName;  
 84         }  
 85       
 86         public ExcelExport setSheetName(String sheetName) {  
 87             this.sheetName = sheetName;  
 88             return this;  
 89         }  
 90       
 91     /** 
 92      * 开始导出数据信息 
 93      * 
 94      * @throws ExcelException 抛出数据异常类 
 95      */  
 96     public byte[] exportExport(HttpServletRequest request, HttpServletResponse response) throws ExcelException {  
 97         //检查参数配置信息  
 98         checkConfig();  
 99         //创建工作簿  
100         HSSFWorkbook wb = new HSSFWorkbook();  
101         //创建工作表  
102         HSSFSheet wbSheet = wb.createSheet(this.sheetName);  
103         //在第0行创建rows  
104         HSSFRow row = wbSheet.createRow((int) 0);  
105         //创建单元格，设置表头，表头居中  
106         HSSFCellStyle style = wb.createCellStyle();  
107         //设置单元格样式  
108         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
109         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
110         HSSFFont font = wb.createFont();  
111         font.setFontHeightInPoints((short) this.fontSize);  
112       
113         //设置列头元素  
114         HSSFCell cellHead = null;  
115         for (int i = 0; i < heardList.length; i++) {  
116             cellHead = row.createCell(i);  
117             cellHead.setCellValue(heardList[i]);  
118             cellHead.setCellStyle(style);  
119         }  
120       
121         //开始写入实体数据信息  
122         style.setFont(font);  
123         for (int i = 0; i < data.size(); i++) {  
124             HSSFRow roww = wbSheet.createRow((int) i + 1);  
125             Map map = data.get(i);  
126             HSSFCell cell = null;  
127             for (int j = 0; j < heardKey.length; j++) {  
128                 cell = roww.createCell(j);  
129                 cell.setCellStyle(style);  
130                 Object valueObject = map.get(heardKey[j]);  
131                 String value = null;  
132                 if (valueObject == null) {  
133                     valueObject = "";  
134                 }  
135                 if (valueObject instanceof String) {  
136                     //取出的数据是字符串直接赋值  
137                     value = (String) map.get(heardKey[j]);  
138                 } else if (valueObject instanceof Integer) {  
139                     //取出的数据是Integer  
140                     value = String.valueOf(((Integer) (valueObject)).floatValue());  
141                 } else if (valueObject instanceof BigDecimal) {  
142                     //取出的数据是BigDecimal  
143                     value = String.valueOf(((BigDecimal) (valueObject)).floatValue());  
144                 } else {  
145                     value = valueObject.toString();  
146                 }  
147                 cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value);  
148                 }  
149             }  
150             //设置行高  
151             //设置行高的过程需要注意的一不包含标题  
152             for (int i = 0; i < data.size() + 1; i++) {  
153                 HSSFRow hssfRow = wbSheet.getRow(i);  
154                 hssfRow.setHeightInPoints(this.rowHeight);  
155             }  
156             //设置列宽  
157             if (data.size() > 0) {  
158                 for (int i = 0; i < data.get(0).size(); i++) {  
159                     wbSheet.setColumnWidth(i, MSExcelUtils.pixel2WidthUnits(this.columWidth));  
160                 }  
161             } else {  
162                 for (int i = 0; i < heardList.length; i++) {  
163                     wbSheet.setColumnWidth(i, MSExcelUtils.pixel2WidthUnits(this.columWidth));  
164                 }  
165             }  
166             //导出数据  
167             try {  
168                 //设置Http响应头告诉浏览器下载这个附件  
169                 response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");  
170                 OutputStream outputStream = response.getOutputStream();  
171                 wb.write(outputStream);  
172                 outputStream.close();  
173                 return wb.getBytes();  
174             } catch (Exception ex) {  
175                 ex.printStackTrace();  
176                 throw new ExcelException("导出Excel出现严重异常，异常信息：" + ex.getMessage());  
177             }  
178       
179         }  
180       
181         /** 
182          * 检查数据配置问题 
183          * 
184          * @throws ExcelException 抛出数据异常类 
185          */  
186         protected void checkConfig() throws ExcelException {  
187             if (heardKey == null || heardList.length == 0) {  
188                 throw new ExcelException("列名数组不能为空或者为NULL");  
189             }  
190       
191             if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {  
192                 throw new ExcelException("字体、宽度或者高度不能为负值");  
193             }  
194       
195             if (Strings.isNullOrEmpty(sheetName)) {  
196                 throw new ExcelException("工作表表名不能为NULL");  
197             }  
198         }  
199     }  

Service层

 1     /** 
 2      * 导出分类销售统计 
 3      * 
 4      * @param request 
 5      * @param response 
 6      * @param startDate 开始日期 
 7      * @param endDate   结束日期 
 8      * @param searchKey 关键字 
 9      * @param storeId   店铺id 
10      * @param organId   组织id 
11      * @return 
12      */  
13     public byte[] exportSaleCategory(HttpServletRequest request, HttpServletResponse response, String startDate,String endDate, String searchKey, String storeId, String organId) {  
14         Integer count = augeSaleMapper.countShowCategoryStatistics(storeId, organId, startDate, endDate, searchKey);  
15         List<Map> maps = augeSaleMapper.selectShowCategoryStatistics(storeId, organId, startDate, endDate,searchKey, 0, count);  
16         String[] rowsName = new String[]{"商品分类", "销售数量", "销售金额", "毛利额", "毛利率"};  
17         String[] parames = new String[]{"name", "saleCount", "itemSumPrice", "grossProfit", "grossProfitMargin"};  
18         //创建导出工具类  
19         ExcelExport excelExport = new ExcelExport();  
20         excelExport.setHeardKey(parames).setData(maps).setHeardList(rowsName);  
21         byte[] bytes = excelExport.exportExport(request, response);  
22         return bytes;  
23     }  

Controller层

 1     @RequestMapping(value = "/exportSaleCategory", method = RequestMethod.GET)  
 2     public ResponseEntity<byte[]> exportSaleCategory(HttpServletRequest request, HttpServletResponse response, String startDate, String endDate, String searchKey, String storeId) throws Exception {  
 3         AugeAdmin admin = (AugeAdmin) session.getAttribute("admin");  
 4         HttpHeaders headers = new HttpHeaders();  
 5         headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);  
 6         String fileName = new String(("品类销售统计.xls").getBytes("UTF-8"), "iso-8859-1");  
 7         headers.setContentDispositionFormData("attachment", fileName);  
 8         byte[] bytes = saleService.exportSaleCategory(request, response, Strings.emptyToNull(startDate),  
 9         Strings.emptyToNull(endDate), Strings.emptyToNull(searchKey), Strings.emptyToNull(storeId), admin.getOrganId());  
10         return new ResponseEntity<byte[]>(bytes, headers, HttpStatus.CREATED);  
11     }  

前端页面

1     $("#exportBtn").on('click', function () {  
2          var startDate = $('#startDate').val();  
3          var endDate = $('#endDate').val();  
4          var storeId = $("#storeId").val();  
5          var url = "${basePath}/sale/exportSaleCategory?startDate=" + startDate + "&endDate=" +endDate + "&storeId=" + storeId;  
6         window.location.href = url;  
7     });  

测试结果

 

转载于:https://www.cnblogs.com/PreachChen/p/9014796.html