maven用到的包
1 2 3 4 5 6 7 8 9 10 <dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-core</artifactId> <version> 1.0 . 6 </version> </dependency> <dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-reader</artifactId> <version> 1.0 . 6 </version> </dependency>Service代码
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 public void exportChargeCount(String yearMonth, String buildId, HttpServletRequest request,HttpServletResponse response) { String templateFile = request.getSession().getServletContext().getRealPath( "/" )+ "/doc/reportTmp/billingInfo.xls" ; Date date = BillingDateUtil.getBillingEndTime(Integer.valueOf(yearMonth.substring( 0 , 4 )),Integer.valueOf(yearMonth.substring( 5 , 7 ))); String buildName = dataCenter.getBuildInfoById(buildId).getBuildName(); String fileName = buildName+ "(" +yearMonth+ ")客户汇总报表.xls" ; List<ChargeCountVO> list = this .reportDao.getChargeCount(DateUtil.getFormatNormal(date), buildId); Map<String, Object> context = new HashMap<>(); XLSTransformer transformer = new XLSTransformer(); float sumMoney = 0 ; for (ChargeCountVO chargeCountVO : list) { sumMoney += chargeCountVO.getEnergyMoney(); } context.put( "billingList" , list); context.put( "sumMoney" , sumMoney); context.put( "billingDate" , yearMonth); context.put( "buildName" ,buildName ); try { response.setContentType( "application/vnd.ms-excel" ); response.setHeader( "Content-disposition" , "attachment;filename=" + new String( fileName.getBytes( "GBK" ), "ISO8859-1" )); Workbook workbook = transformer.transformXLS( new FileInputStream(templateFile), context); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (ParsePropertyException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }采用键值对的形式
xls模板
导出效果
大家可能会遇到的问题
1、web下载中文名称不显示的问题
2、模板使用公式,下载下来不计算的问题
3、合并单元格的问题
下面逐一解答
1、中文字符转码
1 response.setHeader( "Content-disposition" , "attachment;filename=" + new String( fileName.getBytes( "GBK" ), "ISO8859-1" ));2、重新加载模板公式(通用方法)
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 /** * * 重新设置单元格计算公式 * * */ private void resetCellFormula(HSSFWorkbook wb) { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb); int sheetNum = wb.getNumberOfSheets(); for ( int i = 0 ; i < sheetNum; i++) { HSSFSheet sheet = wb.getSheetAt(i); int rows = sheet.getLastRowNum() + 1 ; for ( int j = 0 ; j < rows; j++) { HSSFRow row = sheet.getRow(j); if (row == null ) continue ; int cols = row.getLastCellNum(); for ( int k = 0 ; k < cols; k++) { HSSFCell cell = row.getCell(k); // if (cell != null) // System.out.println("cell["+j+","+k+"]=:"+cell.getCellType()); if (cell == null ) continue ; if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { cell.setCellFormula(cell.getCellFormula()); // System.out.println("----公式:"+cell.getCellFormula()); cell = e.evaluateInCell(cell); // System.out.println("-----------"+cell.getNumericCellValue()); } } } } }使用方法
3、合并单元格
1 2 3 HSSFWorkbook workbook = (HSSFWorkbook )transformer.transformXLS( new FileInputStream(templateFile), context); HSSFSheet sheet = workbook.getSheetAt( 0 ); sheet.addMergedRegion( new CellRangeAddress(起始行号,截至行号,起始列号,截至列号));四个参数均从0开始