使用poi 导出Excel
1.工具方法
public static void createHeadTittle(Workbook wb, Sheet sheet, String headString, int col) { String head=headString+" （单位：万元）"; Row row = sheet.createRow(0); // 创建Excel工作表的行 Cell cell = row.createCell(0); // 创建Excel工作表指定行的单元格 row.setHeight((short) 1000); // 设置高度 cell.setCellType(Cell.CELL_TYPE_STRING); // 定义单元格为字符串类型 cell.setCellValue(head); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col)); // 指定标题合并区域 // 定义单元格格式，添加单元格表样式，并添加到工作簿 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中个对齐 cellStyle.setWrapText(true); // 指定单元格自动换行 // 设置单元格字体 Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setFontHeightInPoints((short) 16); // 字体大小 cellStyle.setFont(font); cell.setCellStyle(cellStyle); } /** * 创建表头 * * @param wb * Excel文档对象 * @param sheet * 工作表对象 * @param thead * 表头内容 * @param sheetWidth * 每一列宽度 */ public static void createThead(Workbook wb, Sheet sheet, String[] thead) { Row row1 = sheet.createRow(1); row1.setHeight((short) 600); // 定义单元格格式，添加单元格表样式，并添加到工作簿 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setWrapText(true); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColor.WHITE.index); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setRightBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setTopBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 // 设置单元格字体 Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); Date date = new Date(); String year1 = String.format("%tY", date); int year2=Integer.parseInt(year1); int yearq=year2-2; int yearp=year2-1; // 设置表头内容 for (int i = 0; i < thead.length; i++) { Cell cell1 = row1.createCell(i); cell1.setCellType(Cell.CELL_TYPE_STRING); if("企业前年收入".equals(thead[i])||"企业前年利润".equals(thead[i])){ String in=thead[i]+"("+yearq+"年)"; cell1.setCellValue(in); }else if("企业去年收入".equals(thead[i])||"企业去年利润".equals(thead[i])){ String im=thead[i]+"("+yearp+"年)"; cell1.setCellValue(im); }else{ cell1.setCellValue(thead[i]); } cell1.setCellStyle(cellStyle); sheet.autoSizeColumn(i); if("企业名称".equals(thead[i])){ sheet.setColumnWidth(i, 256*25); } } // 设置每一列宽度 /* * for(int i=0;i<sheetWidth.length;i++){ //sheet.setColumnWidth(i, * sheetWidth[i]); } */ } /** * 填入数据 * * @param wb * // Excel文档对象 * @param sheet * // 工作表对象 * @param result * // 表数据 */ public static void createTable(Workbook wb, Sheet sheet, List<Map<String, String>> result1, List<String> columNamea, String isqy) { // 定义单元格格式，添加单元格表样式，并添加到工作薄 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setWrapText(true); cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setRightBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色 cellStyle.setBorderTop(CellStyle.BORDER_THIN); // 设置右边框类型 cellStyle.setTopBorderColor(HSSFColor.BLACK.index); // 单元格字体 Font font = wb.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 10); cellStyle.setFont(font); // 循环插入数据 for (int i = 0; i < result1.size(); i++) { Row row = sheet.createRow(i + 2); row.setHeight((short) 400); // 设置高度 Cell cell = null; int j = 0; if (j == 0) { cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue(i + 1); } for (int n = 0; n < columNamea.size(); n++) { String thstring = columNamea.get(n); thstring = thstring.substring(2, thstring.length()); String string = result1.get(i).get(thstring); cell = row.createCell(j + 1); cell.setCellStyle(cellStyle); cell.setCellValue(string); j++; } } }
2.调用工具方法
/** * * @param result1 数据库查询结果 List<Map<String, String>> 最好用这个接收 子 * @param result2 数据库查询结果 List< String> 最好用这个接收 主 * @param sheetName 表名 * @param filePath 文件存储路径 传服务器路径 "G:\\test\\"; * @param thead 表各字段注释 注意与数据库查询结果顺序一致 * @param sheetWidth 表各字段列宽度 注意与数据库查询结果顺序一致 * @return */ public static Workbook OutPortExcel(List<String> columNamea,List<Map<String, String>> result1,String filePath ,String[] thead,String isqy) { String fileName = "本年度迁出企业情况汇总表.xls"; // 定义文件名 String sheetName = "本年度迁出企业情况汇总表"; // 定义工作表表名 String headString = "本年度迁出企业情况汇总表"; Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); if (isqy.equals("qy")) { ExcelUtil.createHeadTittle(wb, sheet, headString,thead.length-1); }else { ExcelUtil.createHeadTittle(wb, sheet, headString,thead.length-1); } ExcelUtil.createThead(wb, sheet, thead); ExcelUtil.createTable(wb, sheet, result1,columNamea,isqy); return wb; }
3.在控制层处理数据，输出Excel
public void outPutExcel( String thtabname,String apply_id,String filepath,HttpServletResponse response,String isqy) { String databa = cencof.getProperty("table_schema"); List<Map<String, String>> lastList=null; if (databa!=null&&!"".equals(databa)) { List<Map<String, String>> thlist = getColNameAndCommList(thtabname, databa); if (isqy.equals("qy")) { lastList = MdListUtil.getLastColum(thlist,"1"); }else { lastList=MdListUtil.getLastColum(thlist,"0"); } List<String> columComm =new ArrayList<String>(); //获取表头 if("zjsb_qcqyqk_fhjd".equals(thtabname)){ columComm = MdListUtil.getColumNaorCo(lastList,"0"); if (isqy.equals("qy")) { lastList = MdListUtil.getLastColum(thlist,"1"); }else { lastList=MdListUtil.getLastColum(thlist,"0"); } }else{ if (isqy.equals("qy")) { lastList = MdListUtil.getLastColumA(thlist,"1"); }else { lastList=MdListUtil.getLastColumA(thlist,"0"); } columComm = MdListUtil.getColumNaorCoA(lastList,"0"); } String[] columCommarr = new String[columComm.size()]; String[] columCommarray = columComm.toArray(columCommarr); List<String> columNamea = new ArrayList<String>(); String cxStra=""; List<String> columNamenoqy=new ArrayList<String>(); String cxStranoqy=""; //表格数据 List<Map<String,String>> lastLista = new ArrayList<Map<String, String>>(); if("zjsb_qcqyqk_fhjd".equals(thtabname)){ lastLista = MdListUtil.getLastColum(thlist,"2"); columNamea = MdListUtil.getColumNaorCo(lastLista,"1"); cxStra = MdListUtil.getCxStr(columNamea); columNamenoqy = MdListUtil.getColumNaorCo(lastList,"1"); cxStranoqy = MdListUtil.getCxStr(columNamenoqy); }else{ lastLista = MdListUtil.getLastColumA(thlist,"2"); columNamea = MdListUtil.getColumNaorCoA(lastLista,"1"); cxStra = MdListUtil.getCxStr(columNamea); columNamenoqy = MdListUtil.getColumNaorCoA(lastList,"1"); cxStranoqy = MdListUtil.getCxStr(columNamenoqy); } String tabtw=thtabname+" a"; List<Map<String, String>> getthResultLista = getthResultList(cxStra,tabtw,apply_id); List<Map<String, String>> getthResultListnoqy = getthResultList(cxStranoqy,tabtw,apply_id); Workbook wb=null; if (isqy.equals("qy")) { if("zjsb_qcqyqk_fhjd".equals(thtabname)){ wb=OutputUtil.OutPortExcel( columNamea,getthResultLista, filepath+"\\", columCommarray,isqy); }else{ wb=OutputUtil.OutPortExcelA( columNamea,getthResultLista, filepath+"\\", columCommarray,isqy); } }else { wb=OutputUtil.OutPortExcel( columNamenoqy,getthResultListnoqy, filepath+"\\", columCommarray,isqy); } OutputStream out = null; response.reset(); response.setContentType("application/vnd.ms-excel"); if("zjsb_qcqyqk_fhjd".equals(thtabname)){ response.setHeader("Content-disposition", "inline;filename="+toUtf8String("本年度迁出企业汇总表.xls")); }else{ response.setHeader("Content-disposition", "inline;filename="+toUtf8String("孵化培育企业迁出情况汇总表.xls")); } try { out = response.getOutputStream(); wb.write(out); out.close(); wb=null; } catch (Exception e) { e.printStackTrace(); }finally{ if(out != null){ try { out.close(); } catch (IOException e) { e.printStackTrace(); } } if(wb != null){ wb=null; } } } } public String toUtf8String(String s){ StringBuffer sb = new StringBuffer(); for (int i=0;i<s.length();i++){ char c = s.charAt(i); if (c >= 0 && c <= 255){ sb.append(c); }else{ byte[] b; try { b = Character.toString(c).getBytes("utf-8"); }catch (Exception ex) { b = new byte[0]; } for (int j = 0; j < b.length; j++) { int k = b[j]; if (k < 0) k += 256; sb.append("%" + Integer.toHexString(k).toUpperCase()); } } } return sb.toString(); } }
