POI动态生成Excel
根据操蛋需求写的代码，新手可以参考，大神勿喷！！！
package com.epipe.plm.pdc; import java.io.IOException; import java.io.OutputStream; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.Region; import com.rh.core.base.Bean; /** * 利用开源组件POI3.0.2动态导出EXCEL文档 * * @author liujunzhe * @param <T> * 应用泛型，代表任意一个符合javabean风格的类 * 注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx() * byte[]表jpg格式的图片数据 */ public class ExportExcel<T> { public void exportExcel(String title, List<Bean> beans, OutputStream out) { exportExcel(title, beans, out, "yyyy-MM-dd"); } /** * 根据集合Bean动态生成Excel */ @SuppressWarnings("unchecked") public void exportExcel(String title, List<Bean> beans, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为20个字节 sheet.setDefaultColumnWidth(20); // 标题样式 HSSFCellStyle style = workbook.createCellStyle(); // 边框设置 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 字体居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 16); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 样式 1 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 指定当单元格内容显示不下时自动换行 style2.setWrapText(true); // 样式2 HSSFCellStyle style3 = workbook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderLeft(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); style3.setBorderTop(HSSFCellStyle.BORDER_THIN); style3.setAlignment(HSSFCellStyle.ALIGN_CENTER); style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font3 = workbook.createFont(); font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style3.setFont(font3); // 指定当单元格内容显示不下时自动换行 style3.setWrapText(true); // 标题行 sheet.addMergedRegion(new Region(0, (short) (0), 0, (short) 6)); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); row.setHeight((short) 800); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(title); cell.setCellValue(text); // 数据行 int index = 1; int r = 1; int rf = 3; int j = 1; for (Bean b : beans) { sheet.addMergedRegion(new Region(r, (short) (0), rf, (short) 0)); sheet.addMergedRegion(new Region(r, (short) (2), r, (short) 6)); sheet.addMergedRegion(new Region(rf, (short) (2), rf, (short) 6)); r += 3; rf += 3; row = sheet.createRow(index++); row.setHeight((short) 500); sheet.setColumnWidth(0, 30 * 35); for (short i = 0; i < 7; i++) { cell = row.createCell(i); if (i == 1) { cell.setCellStyle(style3); } else { cell.setCellStyle(style2); } if (i == 1) { cell.setCellValue("工作任务"); } else if (i == 2) { cell.setCellValue(b.getStr("WORK_TASK")); } else if (i == 0) { cell.setCellValue(j++); } } row = sheet.createRow(index++); row.setHeight((short) 500); for (short i = 0; i < 7; i++) { cell = row.createCell(i); if (i == 1 || i == 3 || i == 5) { cell.setCellStyle(style3); } else { cell.setCellStyle(style2); } if (i == 1) { cell.setCellValue("主办部门"); } else if (i == 2) { cell.setCellValue(b.getStr("RESPON_DEPT")); } else if (i == 3) { cell.setCellValue("布置时间"); } else if (i == 4) { cell.setCellValue(b.getStr("FACT_START")); } else if (i == 5) { cell.setCellValue("落实时间"); } else if (i == 6) { cell.setCellValue(b.getStr("FACT_FINISH")); } } row = sheet.createRow(index++); row.setHeight((short) 1000); for (short i = 0; i < 7; i++) { cell = row.createCell(i); if (i == 1) { cell.setCellStyle(style3); } else { cell.setCellStyle(style2); } if (i == 1) { cell.setCellValue("落实情况"); } else if (i == 2) { cell.setCellValue(b.getStr("CARRY_SITUATION")); } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } /** * 导出 Excel * @param param * @throws IOException */ public void export(ParamBean param) { HttpServletResponse response = Context.getResponse(); HttpServletRequest request = Context.getRequest(); String paramStr = param.getStr("param"); Bean paramBean = JsonUtils.toBean(paramStr); String dataId = paramBean.getStr("pkCodes"); String dataIdArrayList = "('" + dataId.replaceAll(",", "','") + "')"; String sql = "select t.type, t.WORK_TASK,t.RESPON_DEPT,t.FACT_START,t.FACT_FINISH,t.CARRY_SITUATION ,m.issue from PDC_WORK_TASK t, PDC_WEEK_MEET m WHERE t.WEEK_MEET_ID = m.ID and t.ID in " + dataIdArrayList; // 任务集合 List<Bean> beans = Context.getExecutor().query(sql); String fileName = "";// 文件名称 String title = "";// 标题 int issue = beans.get(0).getInt("issue");// 期号 if (beans.get(0).getStr("type").equals("1")) { fileName = "第" + issue + "期任务落实情况"; title = "上周例会落实情况"; } else { fileName = title = "其他工作落实情况"; } response.setContentType("application/x-zip-compressed;charset=utf-8"); RequestUtils.setDownFileName(request, response, fileName + ".xls");// 设置文件名称 javax.servlet.ServletOutputStream outPutStream = null; try { // 工作表对象 ExportExcel<Bean> ex = new ExportExcel<Bean>(); outPutStream = response.getOutputStream(); ex.exportExcel(title, beans, outPutStream); } catch (IOException e) { e.printStackTrace(); } finally { try { outPutStream.close(); } catch (IOException e) { e.printStackTrace(); } } } }
