/** * 写入excel表格数据 * * @param tableList * @param sourceVerText * @param targetVerText * @return */ public byte[] excelByte(List<Map<String, Object>> tableList, String sourceVerText, String targetVerText) { // 第一步，创建一个webbook，对应一个Excel文件 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet XSSFSheet xssfSheet = xssfWorkbook.createSheet("抽取数据项结果"); // 第三步，第一行表头 XSSFFont xssfFontTitle = xssfWorkbook.createFont(); xssfFontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示 xssfFontTitle.setFontHeight(13);// 字体大小 XSSFRow row = xssfSheet.createRow(0); XSSFCellStyle style0 = xssfWorkbook.createCellStyle(); style0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 style0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style0.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框 style0.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 style0.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 style0.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 style0.setFont(xssfFontTitle); XSSFCell cell = row.createCell(0); cell.setCellValue(sourceVerText); cell.setCellStyle(style0); row.createCell(1).setCellStyle(style0); row.createCell(2).setCellStyle(style0); row.createCell(3).setCellStyle(style0); xssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); cell = row.createCell(4); cell.setCellValue(targetVerText); cell.setCellStyle(style0); row.createCell(5).setCellStyle(style0); row.createCell(6).setCellStyle(style0); row.createCell(7).setCellStyle(style0); row.createCell(8).setCellStyle(style0); xssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 8)); // 第四步，第二行表头 XSSFCellStyle style1 = xssfWorkbook.createCellStyle(); style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框 style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 style1.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 style1.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 style1.setFont(xssfFontTitle); row = xssfSheet.createRow(1); cell = row.createCell(0); cell.setCellValue("字段名"); cell.setCellStyle(style1); cell = row.createCell(1); cell.setCellValue("字段CODE"); cell.setCellStyle(style1); cell = row.createCell(2); cell.setCellValue("数据类型"); cell.setCellStyle(style1); cell = row.createCell(3); cell.setCellValue("使用表"); cell.setCellStyle(style1); cell = row.createCell(4); cell.setCellValue("表CODE"); cell.setCellStyle(style1); cell = row.createCell(5); cell.setCellValue("字段名"); cell.setCellStyle(style1); cell = row.createCell(6); cell.setCellValue("字段CODE"); cell.setCellStyle(style1); cell = row.createCell(7); cell.setCellValue("数据类型"); cell.setCellStyle(style1); cell = row.createCell(8); cell.setCellValue("备注"); cell.setCellStyle(style1); // 第五步，写入表格数据 XSSFFont xssfFontBody = xssfWorkbook.createFont(); xssfFontBody.setFontHeight(12); // 居中样式 XSSFCellStyle styleCenter = xssfWorkbook.createCellStyle(); styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框 styleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 styleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 styleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 styleCenter.setWrapText(true);// 自动换行 styleCenter.setFont(xssfFontBody); // 靠左样式 XSSFCellStyle styleLeft = xssfWorkbook.createCellStyle(); styleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); styleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框 styleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 styleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 styleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 styleLeft.setWrapText(true);// 自动换行 styleLeft.setFont(xssfFontBody); int beginRow = 2; int cntRow = 0; for (int i = 0; i < tableList.size(); i++) { row = xssfSheet.createRow(i + 2); Map<String, Object> map = tableList.get(i); // 每行都要写入数据 cell = row.createCell(0); cell.setCellStyle(styleCenter); cell.setCellValue(map.get("sourceColumnName").toString()); cell = row.createCell(1); cell.setCellStyle(styleCenter); cell.setCellValue(map.get("sourceColumnCode").toString()); cell = row.createCell(2); cell.setCellStyle(styleCenter); cell.setCellValue(map.get("sourceColumnType").toString()); cell = row.createCell(3); cell.setCellStyle(styleLeft); cell.setCellValue(map.get("sourceTables").toString()); cell = row.createCell(4); cell.setCellStyle(styleLeft); cell.setCellValue(map.get("targetTables").toString()); cell = row.createCell(5); cell.setCellStyle(styleCenter); cell.setCellValue(map.get("targetColumnName").toString()); cell = row.createCell(6); cell.setCellStyle(styleCenter); cell.setCellValue(map.get("targetColumnCode").toString()); cell = row.createCell(7); cell.setCellStyle(styleCenter); cell.setCellValue(map.get("targetColumnType").toString()); cell = row.createCell(8); cell.setCellStyle(styleLeft); cell.setCellValue(map.get("targetComment").toString()); if (i < tableList.size() - 1) { Map<String, Object> mapNext = tableList.get(i + 1); if (map.get("sourceColumnName").toString().equals(mapNext.get("sourceColumnName").toString()) && map.get("sourceColumnCode").toString().equals(mapNext.get("sourceColumnCode").toString()) && map.get("sourceColumnType").toString().equals(mapNext.get("sourceColumnType").toString()) && map.get("sourceTables").toString().equals(mapNext.get("sourceTables").toString())) { cntRow++; continue; } } if (beginRow == tableList.size() + 2) { break; } // 合并单元格 xssfSheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + cntRow, 0, 0)); xssfSheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + cntRow, 1, 1)); xssfSheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + cntRow, 2, 2)); xssfSheet.addMergedRegion(new CellRangeAddress(beginRow, beginRow + cntRow, 3, 3)); beginRow += cntRow + 1; cntRow = 0; } // 第七步，调整某几列宽度 xssfSheet.setColumnWidth(0, 25 * 256); xssfSheet.setColumnWidth(1, 25 * 256); xssfSheet.setColumnWidth(2, 25 * 256); xssfSheet.setColumnWidth(3, 50 * 256); xssfSheet.setColumnWidth(4, 30 * 256); xssfSheet.setColumnWidth(5, 25 * 256); xssfSheet.setColumnWidth(6, 25 * 256); xssfSheet.setColumnWidth(7, 25 * 256); xssfSheet.setColumnWidth(8, 30 * 256); xssfSheet.getRow(0).setHeightInPoints(30); // 第八步，将文件存到指定位置 ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); try { xssfWorkbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); } return outputStream.toByteArray(); }
另外，输入输出流可通过byte[]转换，参考如下：
BusinessFileRelImpl dm = new BusinessFileRelImpl(); ByteArrayInputStream stream = new ByteArrayInputStream(excelByte(tableList, sourceVerText, targetVerText)); Date currentTime = new Date(); SimpleDateFormat formatter = new SimpleDateFormat("yyyymmdd"); String dateString = formatter.format(currentTime); String fileName = sourceVerText + "_" + sourceVerText + "_" + dateString + new Date().getTime() + ".xlsx"; String logicId = ""; try { // 抽取数据项对比结果 String treeId = "uxyrr3ofvtdmg3t4a1ymkkuz"; LogicFile logicFile = dm.saveBusinessFile(treeId, authInfo, fileName, stream); logicId = logicFile.getFileId().toString(); } catch (IOException e) { e.printStackTrace(); }