一、手写原因
1.1现有技术Autopoi
项目基于JeecgBoot开发,该低代码平台有对POI封装后的文档技术-Autopoi,能够操作Excel,Word等Office文件。
其实现方式有:
1,基于注解实现(Excel)
2,基于模板实现 (Excel,Word)
文档手册在,不做多介绍:1.前传 - JeecgBoot 文档中心
1.2Autopoi无法满足动态表头需求
1,现在需要导出一个动态表头的表格,需要根据数据的返回来动态添加表头数据,我的数据考官名称作为表头,所以这是多个动态数据
2, 可以根据表头最大长度,来匹配最外层表头的长度,然后添加背景等方法。 这些都是无法满足的现有的导出方法无法满足,被迫手写......
二、需求和实现
2.1 需求
根据考核成绩详情导出内容,包括评分表,考官签名(图片),等信息。
完成图如下
2.2 实现代码
2.2.1 业务方法
先循环遍历评分表,把考核信息基础数据的行数预留出来,将每一个评分表导出
public Workbook exportGradeDetails(ExaminationDetailsResponse result) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet spreadsheet = workbook.createSheet(result.getExamName());
//设置5是因为,评分表头至少包含5项。
int longestColumn = OsceConstant.SCORE_EXPORT_EXCEL_MIN_HEAD_LENGTH;
//循环遍历评分表
List<StationScoreVO> stationList = result.getStationList();
List<QuestionScoreDetailsVO> questionList = stationList.stream()
.map(StationScoreVO::getQuestionList)
.flatMap(List::stream)
.collect(Collectors.toList());
for (QuestionScoreDetailsVO scoreDetailsVO : questionList) {
int startRow = spreadsheet.getLastRowNum() == -1 ? 0 : spreadsheet.getLastRowNum() + 1;
try {
Row row = spreadsheet.createRow(startRow++);
row.setHeight((short) 800);
int i = ExportXlsUtils.exportSoreTable(scoreDetailsVO, workbook, spreadsheet, startRow, 0);
longestColumn = Math.max(longestColumn, i);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
//设置表头
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);//字体加粗
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
List<String> outputs = Arrays.asList(
OsceConstant.EXAM_NAME + ":" + result.getExamName(),
OsceConstant.EXAM_TIME + ":" + cn.hutool.core.date.DateUtil.format(result.getStartTime(), "yyyy-MM-dd HH:mm:ss") + " - " + DateUtil.format(result.getEndTime(), "yyyy-MM-dd HH:mm:ss"),
OsceConstant.EXAM_SCORE + ":" + result.getSumScore(),
OsceConstant.EXAMINEE_NAME + ":" + result.getExamieeName());
ExportXlsUtils.exportExcelHead(spreadsheet, outputs, cellStyle, 0, longestColumn);
return workbook;
}
2.2.2 导出Excel代码(轮子)
都是一行行导出,写入,可操控性强。
public static int exportSoreTable(QuestionScoreDetailsVO question, Workbook workbook, Sheet sheet, int startRow, int startColumn) throws IOException {
Row head = sheet.createRow(startRow++);
int columnIndex = startColumn;
//1.导出表头 1 评分表格式
Row row = sheet.createRow(startRow++);
row.createCell(columnIndex++).setCellValue(OsceConstant.SCORE_TABLE_HEAD_QUESTION_ITEM);
row.createCell(columnIndex++).setCellValue(OsceConstant.SCORE_TABLE_HEAD_QUESTION_STANDARD);
row.createCell(columnIndex++).setCellValue(OsceConstant.SCORE_TABLE_QUESTION_STANDARD_SCORE);
//1.导出表头 1.2 拼接考官信息做表头
List<QuestionScoreItemUser> items = question.getItems();
List<StandardScoreRateVO> raters = items.get(0).getStandards().get(0).getRaters();
for (StandardScoreRateVO rater : raters) {
row.createCell(columnIndex++).setCellValue(rater.getRaterName());
sheet.setColumnWidth(columnIndex - 1, 256 * 20);//考官评分列宽 ,第一个与题目得分重复设置,无影响
}
//2.导出数据
for (QuestionScoreItemUser item : items) {
List<QuestionScoreStandardUser> standards = item.getStandards();
if (standards.size() > 1) {
sheet.addMergedRegion(new CellRangeAddress(startRow, startRow + standards.size() - 1, startColumn, startColumn));
}
for (QuestionScoreStandardUser standard : standards) {
row = sheet.createRow(startRow++);
row.setHeight((short) 400);
List<String> outputs = new ArrayList<>(Arrays.asList(item.getItemName(), standard.getStandardName(), standard.getStandardScore().toString()));
// row.createCell(columnIndex++).setCellValue(item.getItemName());
// row.createCell(columnIndex++).setCellValue(standard.getStandardName());
// row.createCell(columnIndex++).setCellValue(standard.getStandardScore().toString());
for (StandardScoreRateVO rater : standard.getRaters()) {
outputs.add(rater.getScore() == null ? "0" : rater.getScore().toString());
// row.createCell(columnIndex++).setCellValue(rater.getScore() == null ? "0" : rater.getScore().toString());
}
exportRow(row, outputs, null, startColumn);
}
}
short lastCellNum = row.getLastCellNum();
//3.导出考官签名
row = sheet.createRow(startRow);
row.setHeight((short) 500);
columnIndex = startColumn;
Cell cell4 = row.createCell(columnIndex++);
CellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
row.setRowStyle(cellStyle1);
cell4.setCellValue(OsceConstant.SCORE_TABLE_RATERS_SIGN + ":");
for (StandardScoreRateVO rater : raters) {
// 构造 URL
String path = rater.getSignPath();
if (StringUtils.isBlank(path)) {
continue;
}
URL url = new URL(path);
// 打开连接
URLConnection con = url.openConnection();
//设置请求超时为5s
con.setConnectTimeout(5 * 1000);
// 输入流
InputStream is = con.getInputStream();
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); // 添加图片到工作簿中
CreationHelper helper = workbook.getCreationHelper(); // 获取创建帮助类
Drawing<?> drawing = sheet.createDrawingPatriarch(); // 获取绘图父对象
ClientAnchor anchor = helper.createClientAnchor(); // 创建客户端锚点
// 图片插入坐标
anchor.setCol1(lastCellNum - raters.size() + raters.indexOf(rater)); // 设置图片左上角的列数
anchor.setRow1(startRow); // 设置图片左上角的行数
anchor.setDx1(0);
anchor.setDx2(100);
anchor.setDy2(100);
// 插入图片
Picture pict = drawing.createPicture(anchor, pictureIdx); // 在指定位置插入图片
//
// // 设置 单元格高度
// cell4.getRow().setHeight((short) 1000); // 将单元格所在行的高度设置为1000
// // 设置 单元格宽度
// sheet.setColumnWidth(cell4.getColumnIndex(), 1000); // 将单元格所在列的宽度设置为2600
// 设置图片宽、高放缩比例
pict.resize(1, 1); // 这行代码,可以将值设置成 pict.resize(0.5, 0.5)、pict.resize(2, 2),看看效果如何。
}
//4.导出表头 基础信息。设置评分表格式列宽等
exportTableHead(workbook, sheet, question, head, startColumn, lastCellNum);
return lastCellNum;
}
public static void exportRow(Row row, List<String> outputs, CellStyle style, int startColumn) {
for (String output : outputs) {
Cell cell = row.createCell(startColumn++);
cell.setCellStyle(style);
cell.setCellValue(output);
}
}
public static void exportTableHead(Workbook workbook, Sheet sheet, QuestionScoreDetailsVO question, Row row, int startColumn, int longestColumn) {
row.setHeight((short) 500);
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);//字体加粗
cellStyle.setFillForegroundColor(IndexedColors.TURQUOISE1.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
int columnIndex = startColumn;
Cell cell = row.createCell(columnIndex++);
cell.setCellStyle(cellStyle);
cell.setCellValue(OsceConstant.SCORE_TABLE_HEAD_STATION_NAME + ":" + question.getStationName());
sheet.setColumnWidth(columnIndex - 1, 256 * 50);//评分项目列宽
Cell cell1 = row.createCell(columnIndex++);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(OsceConstant.EXAM_STATION_QUESTION_NAME + ":" + question.getQuestionName());
sheet.setColumnWidth(columnIndex - 1, 256 * 80);//评分标准列宽
Cell cell2 = row.createCell(columnIndex++);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(OsceConstant.SCORE_TABLE_HEAD_TABLE_NAME + ":" + question.getScoreTableName());
sheet.setColumnWidth(columnIndex - 1, 256 * 20);//评分表名列宽
Cell cell3 = row.createCell(columnIndex++);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(OsceConstant.SCORE_TABLE_HEAD_QUESTION_SCORE + ":" + question.getQuestionScore());
sheet.setColumnWidth(columnIndex - 1, 256 * 20);//题目总分列宽
Cell cell4 = row.createCell(columnIndex);
cell4.setCellStyle(cellStyle);
cell4.setCellValue(OsceConstant.SCORE_TABLE_HEAD_SCORE + ":" + question.getScore());
sheet.setColumnWidth(columnIndex, 256 * 20);//题目得分列宽
for (int i = 0; i < longestColumn - columnIndex; i++) {
Cell cell5 = row.createCell(columnIndex + i);
cell5.setCellStyle(cellStyle);
}
}
public static void exportExcelHead(Sheet sheet, List<String> outputs, CellStyle style, int startColumn, int longestColumn) {
Row row = sheet.createRow(0);
row.setHeight((short) 1000);
outputs = new ArrayList<>(outputs);
int blank = longestColumn - outputs.size();
for (int i = 0; i < blank; i++) {
outputs.add("");
}
exportRow(row, outputs, style, startColumn);
}
注意的点:
1,每一次指定下一次的开始行lastCellNum = startColumn,我这里想每一个表隔开一行,lastCellNum是++后的得到的结果所以不需要+1。
2,我按照了数据 -->表头的顺序来导出的,我觉得可以先确定数据的columns然后去合并表头,表头可以随着数据的变化来合并表头,这样一个表显得是个整体,会好看一点