使用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;
}
作者：隔壁老郭
个性签名：独学而无友，则孤陋而寡闻。做一个灵魂有趣的人！
如果觉得这篇文章对你有小小的帮助的话，记得在右下角点个“推荐”哦，博主在此感谢！
Java入门到入坟
万水千山总是情，打赏一分行不行，所以如果你心情还比较高兴，也是可以扫码打赏博主，哈哈哈(っ•̀ω•́)っ✎⁾⁾！
