一、POI 表格框架
1.POI ：POI提供API给Java程序对Microsoft Office格式档案读和写的功能
2.HSSF：Horrible SpreadSheet Format，为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”
3.POI 文档结构类
HSSFWorkbook 文档对象，HSSFSheet 页，HSSFRow 行，HSSFCell 单元格，HSSFFont 字体，HSSFName 名称，HSSFDataFormat 日期格式
HSSFHeader 表头，HSSFFooter 表尾，HSSFCellStyle 单元格样式，HSSFDateUtil 日期，HSSFPrintSetup 打印，HSSFErrorConstants 错误信息表
二、POI 文件类型
类 文件 jar
HSSF －- 提供读写Microsoft Excel XLS格式档案的功能poi
XSSF －- 提供读写Microsoft Excel OOXML XLSX格式档案的功能 poi--ooxml
HWPF －- 提供读写Microsoft Word DOC格式档案的功能poi-scratchpad
HSLF －- 提供读写Microsoft PowerPoint格式档案的功能poi-scratchpad
HDGF －- 提供读Microsoft Visio格式档案的功能poi-scratchpad
HPBF －- 提供读Microsoft Publisher格式档案的功能poi-scratchpad
HSMF －- 提供读Microsoft Outlook格式档案的功能poi-scratchpad
原Excel模板 脱产培训与网络培训两个列表不相同，并且为动态，数据条数未知，所以要动态添加数据与动态合并单元格。
需求如此，多以抛弃使用模板填充方式，改为从第一行构建到最后一行，并且将excel添加水印转换成pdf。
POI Jar包 版本之间差异看官网，尽量用新的
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.15</version></dependency>
PDF转换Jar包 了解工具直接搜索jar包名称就行
<dependency><groupId>com.bc.ext</groupId><artifactId>spire.xls.free</artifactId><version>5.1.0</version></dependency>
业务代码，具体行都有注释。
public File createExcel(HashMap<String, Object> resultMap, List<Map<String, Object>> actualList, List<Map<String, Object>> netWorkList, HttpServletResponse response){/** 第一步，创建一个Workbook，对应一个Excel文件 */XSSFWorkbook wb = new XSSFWorkbook();/** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet */XSSFSheet sheet = wb.createSheet("sheet1");//设置每个列有多宽 100为单位好计算sheet.setColumnWidth(0,100 * 12);sheet.setColumnWidth(1,100 * 19);sheet.setColumnWidth(2,100 * 38);sheet.setColumnWidth(3,100 * 38);sheet.setColumnWidth(4,100 * 38);sheet.setColumnWidth(5,100 * 38);sheet.setColumnWidth(6,100 * 38);/** 第三步，设置样式以及字体样式*/XSSFCellStyle titleStyle = createTitleCellStyle(wb);XSSFCellStyle headerStyle = createHeadCellStyle(wb);XSSFCellStyle contentStyle = createContentCellStyle(wb);/** 第四步，创建标题 ,合并标题单元格 */// 行号int rowNum = 0;// 创建第一页的第一行，索引从0开始XSSFRow row0 = sheet.createRow(rowNum++);row0.setHeight((short) 800);// 设置行高String title = "公务员培训情况备案表";XSSFCell c00 = row0.createCell(0);c00.setCellValue(title);c00.setCellStyle(titleStyle);// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));//标题合并单元格操作，6为总列数// 第二行XSSFRow row1 = sheet.createRow(rowNum++);row1.setHeight((short) 700);String[] row_first = {"年度","", "", "", "", "", ""};for (int i = 0; i < row_first.length; i++) {XSSFCell tempCell = row1.createCell(i);tempCell.setCellStyle(headerStyle);if (i == 0) {tempCell.setCellValue(Convert.toStr(resultMap.get("year")) + row_first[i]);}else {tempCell.setCellValue(row_first[i]);}}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));//标题合并单元格操作，7为总列数//第三行XSSFRow row2 = sheet.createRow(rowNum++);row2.setHeight((short) 700);String[] row_2_str = {"姓名","", Convert.toStr(resultMap.get("name")), "性别", Convert.toStr(resultMap.get("sex")), "政治面貌", Convert.toStr(resultMap.get("politicVal"))};for (int i = 0; i < row_2_str.length; i++) {XSSFCell tempCell = row2.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_2_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));//标题合并单元格操作，7为总列数//第四行XSSFRow row3 = sheet.createRow(rowNum++);row3.setHeight((short) 700);String[] row_3_str = {"学历","",Convert.toStr(resultMap.get("educationVal")), "行政级别",Convert.toStr(resultMap.get("administrationVal")), "",""};for (int i = 0; i < row_3_str.length; i++) {XSSFCell tempCell = row3.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_3_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 1));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 6));//标题合并单元格操作，7为总列数//第五行XSSFRow row4 = sheet.createRow(rowNum++);row4.setHeight((short) 700);String[] row_4_str = {"单位名称","",Convert.toStr(resultMap.get("companyName")), "内设机构",Convert.toStr(resultMap.get("mechanism")), "职务",Convert.toStr(resultMap.get("studentDuties"))};for (int i = 0; i < row_4_str.length; i++) {XSSFCell tempCell = row4.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_4_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 1));//标题合并单元格操作，7为总列数//第六行XSSFRow row5 = sheet.createRow(rowNum++);row5.setHeight((short) 700);String[] row_5_str = {"脱产培训","培训班名称","", "培训时间","", "主办单位","学分"};for (int i = 0; i < row_5_str.length; i++) {XSSFCell tempCell = row5.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_5_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(5, 5, 1, 2));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(5, 5, 3, 4));//标题合并单元格操作，7为总列数//第六行带的数据 循环插入for (Map<String, Object> actuaLData : actualList) {XSSFRow tempRow = sheet.createRow(rowNum++);tempRow.setHeight((short) 700);// 循环单元格填入数据for (int j = 0; j < 7; j++) {XSSFCell tempCell = tempRow.createCell(j);tempCell.setCellStyle(contentStyle);String tempValue;if (j == 0) {tempValue = "";} else if (j == 1) {tempValue = Convert.toStr(actuaLData.get("actualName"));} else if (j == 2) {tempValue = "";} else if (j == 3) {tempValue = Convert.toStr(actuaLData.get("actualTime"));} else if (j == 4) {tempValue = "";} else if (j == 5) {tempValue = Convert.toStr(actuaLData.get("actualOrgan"));} else {tempValue = Convert.toStr(actuaLData.get("actualPoint"));}tempCell.setCellValue(tempValue);}sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 2));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 3, 4));//标题合并单元格操作，7为总列数}//培训班小计行XSSFRow rowxj = sheet.createRow(rowNum++);rowxj.setHeight((short) 700);String[] row_xj_str = {"","小计","", "","", "",Convert.toStr(resultMap.get("totalActualPoint"))};for (int i = 0; i < row_5_str.length; i++) {XSSFCell tempCell = rowxj.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_xj_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 1, 5));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(5, rowNum - 1, 0, 0));//标题合并单元格操作，7为总列数} catch (Exception e){}//网络培训标题行XSSFRow rowNetworkTitle = sheet.createRow(rowNum++);rowNetworkTitle.setHeight((short) 700);String[] row_networkTitle_str = {"网络培训","所学课程类别","", "","", "课程数","学分"};for (int i = 0; i < row_networkTitle_str.length; i++) {XSSFCell tempCell = rowNetworkTitle.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_networkTitle_str[i]);}try {// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 4));//标题合并单元格操作，7为总列数} catch (Exception e){}//做标记，动态计算坐标合并int startIndex = rowNum - 1;//网络培训list数据填充for (Map<String, Object> networkData : netWorkList) {XSSFRow tempRow = sheet.createRow(rowNum++);tempRow.setHeight((short) 700);// 循环单元格填入数据for (int j = 0; j < 7; j++) {XSSFCell tempCell = tempRow.createCell(j);tempCell.setCellStyle(contentStyle);String tempValue;if (j == 0) {tempValue = "";} else if (j == 1) {tempValue = Convert.toStr(networkData.get("name"));} else if (j == 2) {tempValue = "";} else if (j == 3) {tempValue = "";} else if (j == 4) {tempValue = "";} else if (j == 5) {tempValue = Convert.toStr(networkData.get("networkCount"));} else {tempValue = Convert.toStr(networkData.get("point"));}tempCell.setCellValue(tempValue);}sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 4));//标题合并单元格操作，7为总列数}//网络培训专题班行XSSFRow rowNetworkZtb = sheet.createRow(rowNum++);rowNetworkZtb.setHeight((short) 700);String[] row_NetworkZtb_str = {"","专题班","", "","", Convert.toStr(resultMap.get("lessonCount")),Convert.toStr(resultMap.get("pointSum"))};for (int i = 0; i < row_NetworkZtb_str.length; i++) {XSSFCell tempCell = rowNetworkZtb.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_NetworkZtb_str[i]);}//合并try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 1, 4));//标题合并单元格操作，7为总列数} catch (Exception e){}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样//网络培训小计行XSSFRow rowNetworkXj = sheet.createRow(rowNum++);rowNetworkXj.setHeight((short) 700);String[] row_NetworkXj_str = {"","小计","", "","", Convert.toStr(resultMap.get("networkCount")),Convert.toStr(resultMap.get("networkPoint"))};for (int i = 0; i < row_NetworkXj_str.length; i++) {XSSFCell tempCell = rowNetworkXj.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_NetworkXj_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 1, 4));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(startIndex, rowNum - 1, 0, 0));//标题合并单元格操作，7为总列数} catch (Exception e){}//年度完成专题班行XSSFRow rowYearNetwork = sheet.createRow(rowNum++);rowYearNetwork.setHeight((short) 700);String yearNetwork = Convert.toStr(resultMap.get("yearNetwork"));String[] row_YearNetwork_str;if (yearNetwork == null){row_YearNetwork_str = new String[]{"年度完成专题班", "", "", "", "", "个", ""};}else {row_YearNetwork_str = new String[]{"年度完成专题班", "", "", "", "", Convert.toStr(resultMap.get("yearNetworkCount")) + "个", ""};}for (int i = 0; i < row_YearNetwork_str.length; i++) {XSSFCell tempCell = rowYearNetwork.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_YearNetwork_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 0, 4));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 5, 6));//标题合并单元格操作，7为总列数} catch (Exception e){}//学分合计行XSSFRow rowPointCount = sheet.createRow(rowNum++);rowPointCount.setHeight((short) 700);String[] row_PointCount_str = new String[]{"学分合计", "", "", "", "", Convert.toStr(resultMap.get("yearPoint")), ""};for (int i = 0; i < row_PointCount_str.length; i++) {XSSFCell tempCell = rowPointCount.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_PointCount_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 0, 4));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 5, 6));//标题合并单元格操作，7为总列数} catch (Exception e){}//excel底部两行XSSFRow rowUnder2 = sheet.createRow(rowNum++);rowUnder2.setHeight((short) 900);String[] row_Under2_str = new String[]{"主管部门意见", "", "", Convert.toStr(resultMap.get("is")), "", "", ""};for (int i = 0; i < row_Under2_str.length; i++) {XSSFCell tempCell = rowUnder2.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_Under2_str[i]);}try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 3, 6));//标题合并单元格操作，7为总列数} catch (Exception e){}XSSFRow rowUnder1 = sheet.createRow(rowNum++);rowUnder1.setHeight((short) 800);String[] row_Under1_str = new String[]{"", "", "", "", "", Convert.toStr(resultMap.get("pdfTime")), ""};for (int i = 0; i < row_Under1_str.length; i++) {XSSFCell tempCell = rowUnder1.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_Under1_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 3, 4));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 5, 6));//标题合并单元格操作，7为总列数sheet.addMergedRegion(new CellRangeAddress(rowNum-2, rowNum - 1, 0, 2));//标题合并单元格操作，7为总列数} catch (Exception e){}//备注行XSSFRow rowremarks = sheet.createRow(rowNum++);rowremarks.setHeight((short) 1300);String[] row_remarks_str = new String[]{"注：根据《干部教育培训工作条例》，干部参加教育培训，每年累计不少于12天或者90学时，因故未按规定参加教育培训或者未达到教育培训要求的，应当及时补训。干部教育培训考核不合格的，年度考核不得确定为优秀等次。", "", "", "", "","", ""};for (int i = 0; i < row_remarks_str.length; i++) {XSSFCell tempCell = rowremarks.createCell(i);tempCell.setCellStyle(headerStyle);tempCell.setCellValue(row_remarks_str[i]);}// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）//此处没有任何问题，程序不让运行，就给捕获了，不用操作别的,之后的代码也是一样try {sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum - 1, 0, 6));//标题合并单元格操作，7为总列数} catch (Exception e){}try {//添加图片水印FileOutputStream fileOut = null;BufferedImage bufferImg = null;ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();//加载图片bufferImg = ImageIO.read(new File("src/main/resources/static/template/peqk.png"));ImageIO.write(bufferImg, "png", byteArrayOut);XSSFDrawing patriarch = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,5, rowNum-3, 7,rowNum-1 );//插入图片 1patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));// // 输出文件// wb.write(fileOut);} catch (Exception e){e.printStackTrace();}File fileExcel = xssfWorkbookToFile(wb, "src/main/resources/static/template/peqk.xlsx");ExcelToPdf excelToPdf = new ExcelToPdf();excelToPdf.sheetToPdf("src/main/resources/static/template/peqk.xlsx", "src/main/resources/static/template/peqk.pdf");File filePdf = new File("src/main/resources/static/template/peqk.pdf");ServletOutputStream out = null;try{out = response.getOutputStream();/** 导出pdf文件流 */response.setCharacterEncoding("UTF-8");response.setContentType("application/pdf");response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "inline; filename="+ URLEncoder.encode("公务员培训情况备案表.pdf","UTF-8"));FileInputStream inputStream = new FileInputStream(filePdf);// 读取文件流int len = 0;byte[] buffer = new byte[1024 * 10];while ((len = inputStream.read(buffer)) != -1) {out.write(buffer, 0, len);}out.close();}catch (Exception e){e.printStackTrace();}finally {fileExcel.delete();filePdf.delete();}return null;}
下面是工具方法，样式为自定义，如果想添加表格背景颜色，字体，都是可以定义的，具体查询poi的api
/*** 讲ex对象转换成文件* @param wb* @param name* @return*/public static File xssfWorkbookToFile(XSSFWorkbook wb, String name) {File toFile = new File(name);try {OutputStream os = new FileOutputStream(toFile);wb.write(os);os.close();} catch (Exception e) {e.printStackTrace();}return toFile;}/*** 创建标题样式* @param wb* @return*/private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {XSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// cellStyle.setBorderBottom(BorderStyle.THIN); //下边框// cellStyle.setBorderLeft(BorderStyle.THIN); //左边框// cellStyle.setBorderRight(BorderStyle.THIN); //右边框// cellStyle.setBorderTop(BorderStyle.THIN); //上边框// cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式// headerFont1.setBold(true); //字体加粗headerFont1.setFontName("宋体"); // 设置字体类型headerFont1.setFontHeightInPoints((short) 17); // 设置字体大小cellStyle.setFont(headerFont1); // 为标题样式设置字体样式return cellStyle;}/*** 创建表头样式* @param wb* @return*/private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {XSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setWrapText(true);// 设置自动换行// cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN); //左边框cellStyle.setBorderRight(BorderStyle.THIN); //右边框cellStyle.setBorderTop(BorderStyle.THIN); //上边框XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式// headerFont.setBold(true); //字体加粗headerFont.setFontName("宋体"); // 设置字体类型headerFont.setFontHeightInPoints((short) 16); // 设置字体大小// cellStyle.setFont(headerFont); // 为标题样式设置字体样式return cellStyle;}/*** 创建内容样式* @param wb* @return*/private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {XSSFCellStyle cellStyle = wb.createCellStyle();cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中cellStyle.setWrapText(true);// 设置自动换行cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN); //左边框cellStyle.setBorderRight(BorderStyle.THIN); //右边框cellStyle.setBorderTop(BorderStyle.THIN); //上边框// 生成12号字体XSSFFont font = wb.createFont();font.setColor((short)8);font.setFontHeightInPoints((short) 12);cellStyle.setFont(font);return cellStyle;}
最终结果为 pdf 向导出excel把转换pdf的语句删掉就可以了
