实现SheetWriteHandler
package cn.hsa.tps.rupu.Listener;import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;public class RupuDataWriteHandler implements SheetWriteHandler {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)* firstRow 行x轴* lastRow 行y轴* firstCol 列x轴* lastCol 列y轴* 多列字段在同一行需要用同一Row* @param writeWorkbookHolder writeWorkbookHolder* @param writeSheetHolder writeSheetHolder*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet sheet = workbook.getSheetAt(0);//设置标题getTitle(workbook, sheet, 0,"医疗机构预计采购量填报表", (short) 800,(short) 400);sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 7));getTitle(workbook, sheet,1, "为保障广大人民群众诊疗需求,降低群众费用负担,提升临床医用耗材质量,本医疗机构自愿参与广东联盟非药物涂层冠脉球囊类医用耗材带量采购工作,依托江西省医保公共服务药品和医用耗材招采管理子系统,线上填报预计采购量。本医疗机构郑重承诺如下:\n" +"一、根据江西省药品医用耗材集中采购联席会议办公室发布的通知要求,结合本医疗机构上一年度相关品种的实际采购和使用情况,填报预计采购量。\n" +"二、本次预计采购量填报真实、有效。本医疗机构将严格遵守承诺,愿意承担相应的责任。", (short) 1000,(short) 250);sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 7));Row row2 = sheet.createRow(2);getTitle(row2, workbook, "院长(签字):", (short) 800,(short) 250,0);sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 0, 2));getTitle(row2, workbook, "医疗卫生机构(盖章):", (short) 800,(short) 250,3);sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 3, 4));getTitle(row2, workbook, "时间:2022年 月 日", (short) 800,(short) 250,5);sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 5, 7));}/*** @param workbook workbook* @param sheet sheet* @param rowNum rowNum* @param titleName titleName* @param height height* @param fontHeight fontHeight*/private void getTitle(Workbook workbook, Sheet sheet, int rowNum, String titleName, short height, short fontHeight) {Row row2 = sheet.createRow(rowNum);getTitle(row2, workbook, titleName, height, fontHeight, 0);}/*** 同一Row对象(多个字段在同一行)* @param row2 row对象* @param workbook workbook* @param titleName 标题名* @param height 高* @param fontHeight 字高* @param cellNum 字体所在列位置*/private void getTitle(Row row2, Workbook workbook, String titleName, short height, short fontHeight, int cellNum) {row2.setHeight(height);Cell cell1 = row2.createCell(cellNum);cell1.setCellValue(titleName);CellStyle cellStyle = workbook.createCellStyle();// 上下对其方式cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直对齐在单元格的高度上居中。// 左右对其方式cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平对齐居中,这意味着文本在单元格中居中cellStyle.setWrapText(true);Font font = workbook.createFont();font.setBold(true);font.setFontHeight(fontHeight);cellStyle.setFont(font);cell1.setCellStyle(cellStyle);}
}
调用方法
File tmpFile = TmpFileUtil.create(".xls");EasyExcelFactory.write(tmpFile, RupuDataExcelDTO.class).registerWriteHandler(new RupuDataWriteHandler()).sheet("采购数据").useDefaultStyle(true).relativeHeadRowIndex(3).doWrite(rupuDataExcelDTOS);