常规表格样式的Excel导出，
有一种不是常规表格样式的Excel导出，
比如如下这种怎么办
快速的excel框架API肯定不支持这种
所以我们需要自定义格子的内容
private CellStyle getCellStyle2(XSSFWorkbook workbook, int x) {
Font font2 = workbook.createFont();
font2.setBold(true);
font2.setFontHeightInPoints((short) x);
CellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
cellStyle2.setFont(font2);
// 居中
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
return cellStyle2;
}
@Override
public XSSFWorkbook createSampleInboundAndOutboundXSSFWorkbook(List<SampleInboundAndOutbound> sampleInboundAndOutbounds) {
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表并设置表名
XSSFSheet sheet = workbook.createSheet("样品出入库登记表");
// 设置字体样式
CellStyle cellStyle = getCellStyle2(workbook, 12);
// 【第一行】 创建行，下标从0开始
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
// 设置每一列宽度
for (int i = 0; i < 4; i++) {
sheet.setColumnWidth(i, 15 * 256);
}
cell.setCellValue("样品出入库登记表");
cell.setCellStyle(cellStyle);
// 合并第一行数据 前4个单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(region);
int rownumIndex = 1;
for (SampleInboundAndOutbound sampleInboundAndOutbound : sampleInboundAndOutbounds) {
SampleInfoVo sampleInfoVo = sampleInboundAndOutbound.getSampleInfoVo();
if (sampleInfoVo == null) {
continue;
}
// 创建表头行
XSSFRow row2 = sheet.createRow(rownumIndex);
XSSFCell cell1 = row2.createCell(0);
cell1.setCellValue("样品编号");
XSSFCell cell1_value = row2.createCell(1);
cell1_value.setCellValue(sampleInfoVo.getSampleCode());
XSSFCell cell2 = row2.createCell(2);
cell2.setCellValue("样品名称");
XSSFCell cell2_value = row2.createCell(3);
cell2_value.setCellValue(sampleInfoVo.getSampleName());
rownumIndex++;
XSSFRow row3 = sheet.createRow(rownumIndex);
XSSFCell cell1_2 = row3.createCell(0);
cell1_2.setCellValue("产品型号");
XSSFCell cell1_value_2 = row3.createCell(1);
cell1_value_2.setCellValue(sampleInfoVo.getProductModel());
XSSFCell cell2_2 = row3.createCell(2);
cell2_2.setCellValue("型号类型");
XSSFCell cell2_value_2 = row3.createCell(3);
Dictionary varFive1 = dictionaryRemoteService.getByTypeAndCode("sample.product.type", String.valueOf(sampleInfoVo.getProductType()));
cell2_value_2.setCellValue(varFive1.getName());
rownumIndex++;
XSSFRow row4 = sheet.createRow(rownumIndex);
XSSFCell cell1_3 = row4.createCell(0);
cell1_3.setCellValue("负责部门");
XSSFCell cell1_value_3 = row4.createCell(1);
cell1_value_3.setCellValue(sampleInfoVo.getDutyDepartment());
XSSFCell cell2_3 = row4.createCell(2);
cell2_3.setCellValue("负责人");
XSSFCell cell2_value_3 = row4.createCell(3);
cell2_value_3.setCellValue(sampleInfoVo.getDutyPersonName());
rownumIndex++;
XSSFRow row5 = sheet.createRow(rownumIndex);
XSSFCell cell1_4 = row5.createCell(0);
cell1_4.setCellValue("项目平台");
XSSFCell cell1_value_4 = row5.createCell(1);
Dictionary varFive4 = dictionaryRemoteService.getByTypeAndCode("sample.model.platform", sampleInfoVo.getProjectPlatform());
cell1_value_4.setCellValue(varFive4.getName());
XSSFCell cell2_4 = row5.createCell(2);
cell2_4.setCellValue("具体车型项目");
XSSFCell cell2_value_4 = row5.createCell(3);
Dictionary varFive2 = dictionaryRemoteService.getByTypeAndCode("task.project.name", String.valueOf(sampleInboundAndOutbound.getProjectName()));
sampleInboundAndOutbound.setProjectNameStr(varFive2.getName());
cell2_value_4.setCellValue(varFive2.getName());
rownumIndex++;
XSSFRow row6 = sheet.createRow(rownumIndex);
XSSFCell cell1_5 = row6.createCell(0);
cell1_5.setCellValue("试验类型");
XSSFCell cell1_value_5 = row6.createCell(1);
cell1_value_5.setCellValue(sampleInboundAndOutbound.getTestItemTypeName());
XSSFCell cell2_5 = row6.createCell(2);
cell2_5.setCellValue("试验代号");
XSSFCell cell2_value_5 = row6.createCell(3);
cell2_value_5.setCellValue(sampleInboundAndOutbound.getTestItemCode());
rownumIndex++;
XSSFRow row7 = sheet.createRow(rownumIndex);
XSSFCell cell1_7 = row7.createCell(0);
cell1_7.setCellValue("试验名称");
XSSFCell cell1_value_7 = row7.createCell(1);
cell1_value_7.setCellValue(sampleInboundAndOutbound.getTestItemName());
CellRangeAddress region2 = new CellRangeAddress(rownumIndex, rownumIndex, 1, 3);
sheet.addMergedRegion(region2);
List<SampleReceiver> sampleReceiverList = sampleInboundAndOutbound.getSampleReceiverList();
if (CollectionUtils.isNotEmpty(sampleReceiverList)) {
for (SampleReceiver sampleReceiver : sampleReceiverList) {
rownumIndex++;
XSSFRow row8 = sheet.createRow(rownumIndex);
XSSFCell cell1_8 = row8.createCell(0);
cell1_8.setCellValue("接收人");
XSSFCell cell1_value_8 = row8.createCell(1);
cell1_value_8.setCellValue(sampleReceiver.getReceiver());
XSSFCell cell2_8 = row8.createCell(2);
cell2_8.setCellValue("接收时间");
XSSFCell cell2_value_8 = row8.createCell(3);
String receiveDate = DateFormatUtils.format(sampleReceiver.getReceiveDate(), "yyyy-MM-dd");
cell2_value_8.setCellValue(receiveDate);
rownumIndex++;
XSSFRow row9 = sheet.createRow(rownumIndex);
XSSFCell cell1_9 = row9.createCell(0);
cell1_9.setCellValue("实验室");
XSSFCell cell1_value_9 = row9.createCell(1);
cell1_value_9.setCellValue(sampleReceiver.getLaboratory());
XSSFCell cell2_9 = row9.createCell(2);
cell2_9.setCellValue("接收数量");
XSSFCell cell2_value_9 = row9.createCell(3);
cell2_value_9.setCellValue(sampleReceiver.getReceiverCount());
rownumIndex++;
XSSFRow row10 = sheet.createRow(rownumIndex);
XSSFCell cell1_10 = row10.createCell(0);
cell1_10.setCellValue("检查结果");
XSSFCell cell1_value_10 = row10.createCell(1);
Dictionary varFive3 = dictionaryRemoteService.getByTypeAndCode("sample.check.result", String.valueOf(sampleReceiver.getCheckResult()));
cell1_value_10.setCellValue(varFive3.getName());
CellRangeAddress region4 = new CellRangeAddress(rownumIndex, rownumIndex, 1, 3);
sheet.addMergedRegion(region4);
}
}
List<SampleAccepter> sampleAccepterList = sampleInboundAndOutbound.getSampleAccepterList();
if (CollectionUtils.isNotEmpty(sampleAccepterList)) {
for (SampleAccepter sampleAccepter : sampleAccepterList) {
rownumIndex++;
XSSFRow row9 = sheet.createRow(rownumIndex);
XSSFCell cell1_9 = row9.createCell(0);
cell1_9.setCellValue("归还接收人");
XSSFCell cell1_value_9 = row9.createCell(1);
cell1_value_9.setCellValue(sampleAccepter.getAccepter());
XSSFCell cell2_9 = row9.createCell(2);
cell2_9.setCellValue("归还时间");
XSSFCell cell2_value_9 = row9.createCell(3);
String acceptDate = DateFormatUtils.format(sampleAccepter.getAcceptDate(), "yyyy-MM-dd");
cell2_value_9.setCellValue(acceptDate);
rownumIndex++;
XSSFRow row10 = sheet.createRow(rownumIndex);
XSSFCell cell1_10 = row10.createCell(0);
cell1_10.setCellValue("归还原因");
XSSFCell cell1_value_10 = row10.createCell(1);
cell1_value_10.setCellValue(sampleAccepter.getRevertReason() + sampleAccepter.getRemark());
CellRangeAddress region4 = new CellRangeAddress(rownumIndex, rownumIndex, 1, 3);
sheet.addMergedRegion(region4);
}
}
}
return workbook;
}