String _currentPage = request.getParameter("currentPage"); Integer currentPage = 0; if(_currentPage == null){ currentPage = 1; }else{ currentPage = Integer.valueOf(_currentPage); } log.info("导出excel页码: " + currentPage); /**map为空时查询全部的配件信息*/ Map map = new HashMap(); map.put("flag",10); map.put("unionFlag",0); //0表示单件商品,而非组合商品。 Integer totalSize = partsMgrService.totalPartsInfo(map); /**封装pageUtil对象*/ PageUtil pageUtil = new PageUtil(totalSize,Integer.valueOf(currentPage)); //读取第一页,与partsmgr.jsp查询保持一致。 map.put("pageUtil",pageUtil); //map.put("flag",10); list = partsMgrService.listPartsInfo(map); try { HSSFWorkbook wb = partsMgrService.export(list); //调用service方法~! response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=partsList.xls"); OutputStream ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { e.printStackTrace(); log.error("配件导出excel错误:", e); }
@Override public HSSFWorkbook export(List<PartsInfoDTO> list) throws Exception{ String[] excelHeader = {"ID", "编码", "名称","库存","成本价","市场价","销售价","重量","上架","所属品牌","所属分类","所属供应商","图片"}; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("养车之家后台配件列表"); // sheet.setDefaultRowHeight((short)300); // sheet.setDefaultColumnWidth((short)3000); sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值 参考 :"2012-08-10"的宽度为2500 // sheet.setColumnWidth(0, 1000); /**没有效果 ~!!! 140123 huangjing * 是设置了sheet.autoSizeColumn(i); 的原因 * */ sheet.setColumnWidth(1, 3766); sheet.setColumnWidth(2, 3766); sheet.setColumnWidth(3, 3766); sheet.setColumnWidth(4, 3766); sheet.setColumnWidth(5, 3766); sheet.setColumnWidth(6, 3766); sheet.setColumnWidth(7, 3766); sheet.setColumnWidth(8, 3766); sheet.setColumnWidth(9, 3766); sheet.setColumnWidth(10, 3766); sheet.setColumnWidth(11, 3766); sheet.setColumnWidth(12, 3766); HSSFCellStyle style = wb.createCellStyle(); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 style.setWrapText(true); //设置是否能够换行,能够换行为true HSSFRow row = sheet.createRow((int) 0); for (int i = 0; i < excelHeader.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(style); // sheet.autoSizeColumn(i); } for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); PartsInfoDTO dto = list.get(i); HSSFCell cell = null; cell = row.createCell(0); cell.setCellValue(dto.getId()); cell.setCellStyle(style); cell = row.createCell(1); if(dto.getPartsCode() != null){ cell.setCellValue(dto.getPartsCode()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(2); if(dto.getPartsName() != null){ cell.setCellValue(dto.getPartsName()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(3); if(dto.getStoreNum() != null){ cell.setCellValue(dto.getStoreNum()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(4); if(dto.getCostPrice() != null){ cell.setCellValue(dto.getCostPrice()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(5); if(dto.getMarketPrice() != null){ cell.setCellValue(dto.getMarketPrice()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(6); if(dto.getRetailPrice() != null){ cell.setCellValue(dto.getRetailPrice()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(7); if(dto.getWeight() != null){ cell.setCellValue(dto.getWeight()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(8); if(dto.getUpdown() == 0){ cell.setCellValue("否"); }else{ cell.setCellValue("是"); } cell.setCellStyle(style); cell = row.createCell(9); if(dto.getPartsBrandDto().getBrandName() != null){ cell.setCellValue(dto.getPartsBrandDto().getBrandName()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); String typeName = ""; if(dto.getPartsType2Dto().getTypeName() != null){ typeName = dto.getPartsType2Dto().getTypeName(); }else{ typeName = "-"; } if(dto.getPartsTypeDto().getTypeName() != null){ typeName += "-" + dto.getPartsTypeDto().getTypeName(); }else{ typeName += "-"; } cell = row.createCell(10); cell.setCellValue(typeName); cell.setCellStyle(style); cell = row.createCell(11); if(dto.getSupplyInfoDto().getSupplyName() != null){ cell.setCellValue(dto.getSupplyInfoDto().getSupplyName()); }else{ cell.setCellValue("-"); } cell.setCellStyle(style); cell = row.createCell(12); if(dto.getPic().equals("none.jpg")){ cell.setCellValue("无"); }else{ cell.setCellValue("有"); } cell.setCellStyle(style); } return wb; }
实现从数据库中查询数据按当前分页来导出~!!!
导出效果图: