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 });
测试结果
