使用PIO自定义每一个格子的属性和值，完全DIY--Excel，不整齐也可以实现

常规表格样式的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;
    }

over--就是创建每一行每一列，填充值的操作------类似需求可以自己参考

顺带多一嘴

整齐地方式也可以这样操作

@Override
    public XSSFWorkbook creatWorkbookTaskTestItemSampleRepair(List<TaskTestItemSampleRepair> itemSampleRepairs) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        String[] titleArray = {
                "样品编号", "产品型号", "生产企业", "批次号", "故障日期", "修复日期", "修复人",
                "故障原因", "修复情况"
        };
        int[] needAddColumnWidth = {7, 8};
        XSSFSheet sheet = getInitXSSFSheet(workbook, "样品维修表", titleArray.length - 1);
        int rownumIndex = 1;
        XSSFRow row2 = sheet.createRow(rownumIndex);
        this.setSheetBeginTitleRow(titleArray, sheet, workbook, row2, needAddColumnWidth);
        rownumIndex++;
        CellStyle cellStyle2 = getCellStyle2(workbook, 10);
        for (TaskTestItemSampleRepair itemSampleRepair : itemSampleRepairs) {
            XSSFRow row3 = sheet.createRow(rownumIndex);
            for (int columnIndex = 0; columnIndex < titleArray.length; columnIndex++) {
                XSSFCell cell1_2 = row3.createCell(columnIndex);
                switch (columnIndex) {
                    case 0:
                        cell1_2.setCellValue(itemSampleRepair.getSampleCode());
                        break;
                    case 1:
                        cell1_2.setCellValue(itemSampleRepair.getProductModel());
                        break;
                    case 2:
                        cell1_2.setCellValue(itemSampleRepair.getProduceCompany());
                        break;
                    case 3:
                        cell1_2.setCellValue(itemSampleRepair.getBatch());
                        break;
                    case 4:
                        if (itemSampleRepair.getFaultDate() != null) {
                            String faultDate = DateFormatUtils.format(itemSampleRepair.getFaultDate(), "yyyy-MM-dd");
                            cell1_2.setCellValue(faultDate);
                        } else {
                            cell1_2.setCellValue(itemSampleRepair.getFaultDate());
                        }
                        break;
                    case 5:
                        if (itemSampleRepair.getRepairDate() != null) {
                            String repairDate = DateFormatUtils.format(itemSampleRepair.getRepairDate(), "yyyy-MM-dd");
                            cell1_2.setCellValue(repairDate);
                        } else {
                            cell1_2.setCellValue(itemSampleRepair.getRepairDate());
                        }
                        break;
                    case 6:
                        cell1_2.setCellValue(itemSampleRepair.getRepairer());
                        break;
                    case 7:
                        cell1_2.setCellValue(itemSampleRepair.getFaultRemark());
                        break;
                    case 8:
                        cell1_2.setCellValue(itemSampleRepair.getRepairRemark());
                        break;
                    default:
                        break;
                }
                cell1_2.setCellStyle(cellStyle2);
            }
        }
        return workbook;
    }
	
	
    private XSSFSheet getInitXSSFSheet(XSSFWorkbook workbook, String sheetName, int cellRangeLastCol) {
        // 创建工作表并设置表名
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置字体样式
        CellStyle cellStyle = getCellStyle2(workbook, 14);
        // 【第一行】 创建行，下标从0开始
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(sheetName);
        cell.setCellStyle(cellStyle);
        // 合并第一行数据 前10个单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, cellRangeLastCol);
        sheet.addMergedRegion(region);
        return sheet;
    }
	
	    private void setSheetBeginTitleRow(String[] titleArray, XSSFSheet sheet, XSSFWorkbook workbook, XSSFRow row2, int[] needAddColumnWidth) {
        CellStyle cellStyle = getCellStyle2(workbook, 13);
        for (int i = 0; i < titleArray.length; i++) {
            XSSFCell var0001 = row2.createCell(i);
            var0001.setCellValue(titleArray[i]);
            var0001.setCellStyle(cellStyle);
            if (ArrayUtils.contains(needAddColumnWidth, i)) {
                sheet.setColumnWidth(i, 35 * 256);
            } else {
                sheet.setColumnWidth(i, 15 * 256);
            }
        }
    }
	
	    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;
    }