做了不少Excel导出 终于弄能够灵活运用POI实现Excel导出了，下面是我的实践案例。可以根据下面代码进行整合：
String[] titles = new String[]{"序号","档号","文号","责任者","题名","日期","密级","页数","备注"}; String[] titlesvar = new String[]{"order","dh","wh","zrz","tm","rq","hdsdlb","ys","bz"}; List<Map<String,String>> rows = new ArrayList<>(); StringBuffer querySql = new StringBuffer("SELECT ELEARCHIVEFILECODE,BT,ARCHIVEYEAR,DEOTORPROBLEM,BMQX_TEXT,MJ_TEXT,ISPUBLIC_TEXT,NGDW,FWH,CWRQ,GWYS,NGR,NGRQ,ZTC,PUBLICTYPE_TEXT,QZH,HAVEPAPERS,HAVEPAPERS_TEXT,DYFS FROM OA_ARCHIVES WHERE ROWSTATE = 1 "); if (StringUtil.isNotBlank(bmqx)){ querySql.append(" AND bmqx IN (" + SqlUtil.dealCondition(bmqx) + ")"); } if (StringUtil.isNotBlank(deotorproblem)){ querySql.append(" AND deotorproblem like concat('%','"+deotorproblem+"','%') "); } if (StringUtil.isNotBlank(archiveyear)){ querySql.append(" AND archiveyear like concat('"+archiveyear+"','%') "); } DataTable dataTable = DBManager.getDataTable(new Sql(querySql.toString())); List<ArchivesSheet> sheetEntities = DataTableKit.parseList(dataTable, ArchivesSheet.class); sheetEntities.stream().forEach(e->{ if (StringUtil.isNotBlank(e.getCwrq())){ String cwrq = e.getCwrq(); e.setCwrq(cwrq.substring(0,4)+cwrq.substring(5,7)+cwrq.substring(8,10)); } }); Map<String, String> rowItem = null; if (sheetEntities != null && sheetEntities.size() > 0) { for (int i = 0; i < sheetEntities.size(); i++) { ArchivesSheet sheet = sheetEntities.get(i); rowItem = new HashMap<>(); rowItem.put("order", String.valueOf(i + 1)); rowItem.put("dh", sheet.getElearchivefilecode()); rowItem.put("wh", sheet.getFwh()); rowItem.put("zrz", sheet.getNgdw()); rowItem.put("tm", sheet.getBt()); rowItem.put("rq", sheet.getCwrq()); rowItem.put("bz", sheet.getHavepapers() == 1 ? "" : "电子"); rowItem.put("hdsdlb", sheet.getMjText()); rowItem.put("ys", sheet.getGwys()); rows.add(rowItem); } } //创建工作簿对象 HSSFWorkbook workbook = new HSSFWorkbook(); //创建工作表对象 HSSFSheet sheet = workbook.createSheet(); //列表头样式 HSSFFont font = ExcelUtils.getFont(workbook, "宋体", (short)20, false); HSSFCellStyle titleStyle = ExcelUtils.getTitleStyle(workbook, font, HorizontalAlignment.CENTER); //列表体样式 font = ExcelUtils.getFont(workbook, "宋体", (short)14, false); HSSFCellStyle bodyStyle = ExcelUtils.getStyle(workbook, font,HorizontalAlignment.LEFT); //工作表对象设置宽度setColumnWidth这个方法宽度的单位是字符数的256分之一 sheet.setColumnWidth(0, 20*256+184); //设置表格最上面标题 HSSFRow row1 = sheet.createRow(0); HSSFCell cell_10 = row1.createCell(0); row1.setHeightInPoints(40); cell_10.setCellStyle(titleStyle); cell_10.setCellValue("归档文件目录"); CellRangeAddress region = new CellRangeAddress(0, 0, 0,titles.length-1 ); sheet.addMergedRegion(region); for (int i = 0; i < titles.length ; i++) { //设置列宽 sheet.setColumnWidth(i, 20*256+184); //设置表头 HSSFRow row = sheet.getRow(1); if (row == null) { row = sheet.createRow(1); } //设置行高度 row.setHeightInPoints(20); //创建单元格 HSSFCell cell = row.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(bodyStyle); //设置内容 for (int j = 0; j < rows.size(); j++) { //数据内容从第三行开始 row = sheet.getRow(j + 2); if (row == null) { row = sheet.createRow(j + 2); } row.setHeightInPoints(20); //在当前行创建第i个单元格 cell = row.createCell(i); //rows.get(i)为第几行；rows.get(j).get(titles.get(i))为第几行的啥字段 这里可以考虑用对象反射 cell.setCellValue(rows.get(j).get(titlesvar[i])); cell.setCellStyle(bodyStyle); } } ExcelUtils.doDownload(workbook, "归档文件目录下载", response);
工具类ExcelUtils.doDownLoad的源码为下面的doDownLoad方法：
//// Source code recreated from a .class file by IntelliJ IDEA// (powered by Fernflower decompiler)//package cn.dreamit.p1000.util;import java.io.IOException;import java.net.URLEncoder;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;public class ExcelUtils { public ExcelUtils() { } public static HSSFFont getFont(HSSFWorkbook workbook, String name, short size, boolean bold) { HSSFFont font = workbook.createFont(); font.setFontName(name); font.setFontHeightInPoints(size); font.setBold(bold); return font; } public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook, HSSFFont font, HorizontalAlignment alignment) { HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(alignment); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setLocked(true); style.setWrapText(true); return style; } public static HSSFCellStyle getStyle(HSSFWorkbook workbook, HSSFFont font, HorizontalAlignment alignment) { HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(alignment); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setLocked(true); style.setWrapText(true); style.setBorderBottom(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); return style; } public static void doDownload(HSSFWorkbook workbook, String fileName, HttpServletResponse response) { response.setContentType("application/x-download;charset=UTF-8"); try { fileName = URLEncoder.encode(fileName, "UTF-8"); } catch (Exception var15) { var15.printStackTrace(); } response.addHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + fileName + ".xls"); ServletOutputStream out = null; try { out = response.getOutputStream(); workbook.write(out); out.flush(); } catch (IOException var14) { var14.printStackTrace(); } finally { if (out != null) { try { out.close(); } catch (IOException var13) { var13.printStackTrace(); } } } }}//下载的Excel内容如下