java下载excel,excel从数据库获取数据
<!-- poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.14</version> </dependency>
controller.java
@RequestMapping("/exportFile") public void exportFile(TbCdeShAgentRoadRateInBean form,HttpServletResponse response){ OutputStream os=null; try { Workbook wb=wbglService.exportFile(form); ByteArrayOutputStream bos=new ByteArrayOutputStream(); wb.write(bos); response.setBufferSize(512); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); SimpleDateFormat dfs=new SimpleDateFormat("yyyy年mm月dd日HH时mm分ss秒"); //Date end=dfs.parse(); String fileName="委办公路"+dfs.format(new Date())+".xlsx"; response.addHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1")); response.addHeader("Content-Length", String.valueOf(bos.toByteArray().length)); os=response.getOutputStream(); IOUtils.write(bos.toByteArray(),os); } catch (Exception e) { e.printStackTrace(); }finally{ IOUtils.closeQuietly(os); } }
service.java
public Workbook exportFile(TbCdeShAgentRoadRateInBean form) throws Exception{ TbCdeShAgentRoadRateInBean param=getSearchParam(form); int count=iwbglDao.getCount(param); int pageSize=1000; List<TbCdeShAgentRoadRateOutBean> list=null; //防止一次查询的数据量过大,导出服务器内存崩了 int totalPage=(new Double(Math.ceil(count/(pageSize*1.0)))).intValue(); Workbook wb=new SXSSFWorkbook(); Sheet sheet=wb.createSheet("委办公路"); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 CellStyle style =wb.createCellStyle(); //workbook.createCellStyle(); // 设置这些样式 Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); //标题行 Row rowTitle=sheet.createRow(0); Cell cell=rowTitle.createCell(0); cell.setCellStyle(style); cell.setCellValue("序号"); //rowTitle.createCell(1).setCellValue("邮路名称"); cell=rowTitle.createCell(1); cell.setCellStyle(style); cell.setCellValue("邮路名称"); //rowTitle.createCell(2).setCellValue("邮路代码"); cell=rowTitle.createCell(2); cell.setCellStyle(style); cell.setCellValue("邮路代码"); //rowTitle.createCell(3).setCellValue("开行方式"); cell=rowTitle.createCell(3); cell.setCellStyle(style); cell.setCellValue("开行方式"); //rowTitle.createCell(4).setCellValue("代理商"); cell=rowTitle.createCell(4); cell.setCellStyle(style); cell.setCellValue("代理商"); //rowTitle.createCell(5).setCellValue("5T(元/车)"); cell=rowTitle.createCell(5); cell.setCellStyle(style); cell.setCellValue("5T(元/车)"); //rowTitle.createCell(6).setCellValue("8T(元/车)"); cell=rowTitle.createCell(6); cell.setCellStyle(style); cell.setCellValue("8T(元/车)"); //rowTitle.createCell(7).setCellValue("15T(元/车)"); cell=rowTitle.createCell(7); cell.setCellStyle(style); cell.setCellValue("15T(元/车)"); //rowTitle.createCell(8).setCellValue("25T(元/车)"); cell=rowTitle.createCell(8); cell.setCellStyle(style); cell.setCellValue("25T(元/车)"); //rowTitle.createCell(9).setCellValue("起始日期"); cell=rowTitle.createCell(9); cell.setCellStyle(style); cell.setCellValue("起始日期"); //rowTitle.createCell(10).setCellValue("结束日期"); cell=rowTitle.createCell(10); cell.setCellStyle(style); cell.setCellValue("结束日期"); int rowNum=1; for(int currentPage=1;currentPage<=totalPage;currentPage++){ param.setSqlCurrentPage(currentPage); param.setSqlPageSize(pageSize); param.setDet(1L); list=iwbglDao.selectList(param); createExcelDataRow(sheet,list,rowNum); } return wb; } private int createExcelDataRow(Sheet sheet,List<TbCdeShAgentRoadRateOutBean> list,int rowNum){ for(TbCdeShAgentRoadRateOutBean b:list){ Row dataRow = sheet.createRow(rowNum); dataRow.createCell(0).setCellValue(b.getRn()); dataRow.createCell(1).setCellValue(getDataValue(b.getMwayName())); dataRow.createCell(2).setCellValue(getDataValue(b.getMwayCode())); dataRow.createCell(3).setCellValue(getDataValue(b.getTransType())); dataRow.createCell(4).setCellValue(getDataValue(b.getAgentCop())); dataRow.createCell(5).setCellValue(getDataValue(b.getFiveT())); dataRow.createCell(6).setCellValue(getDataValue(b.getEightT())); dataRow.createCell(7).setCellValue(getDataValue(b.getFifteenT())); dataRow.createCell(8).setCellValue(getDataValue(b.getTwentyFiveT())); dataRow.createCell(9).setCellValue(getDataValue(b.getStartDate())); //dataRow.getCell(9).setCellType(Cell.CELL_TYPE_STRING); dataRow.createCell(10).setCellValue(getDataValue(b.getEndDate())); //dataRow.getCell(10).setCellType(Cell.CELL_TYPE_STRING); rowNum++; } return rowNum; } private String getDataValue(Object o){ return o==null?"":o.toString(); }
html
//导出数据 window.location.href="${path}/wbgl/exportFile?a=b&c=d"