关于EasyExcel 的一些生成模板,导入导出的使用心得(优化版)

这里就简单说了,直接贴代码

@HeadFontStyle(color =2)  维护表头第2行的注解
/**
 * @author: kuangql@fadada.com
 * @date: 2020/11/27 19:53
 * @description: TODO
 */
@Data
public class DemoExportEntity {

    public static final String bigTitle= "填写须知: \n" +
            "1.第1、2行为固定结构,不可更改;以下示例行,导入前请先删除\n" +
            "2.请严格按照填写规则输入数据,不合规的数据无法成功导入 \n" +
            "3.测试换行 \n";


    /**
     *
     * org.apache.poi.ss.usermodel.IndexedColors 这个类有所有的颜色枚举值    2是红色
     * @ HeadFontStyle  注解默认是宋体,黑色,加粗
     *
     */
    @ExcelProperty(value = {"姓名(必填)"}, index = 0)
    @ColumnWidth(30)
    @HeadFontStyle(color =2)
    private String userName;

    @ExcelProperty(value = {"性别(必填)"}, index = 1)
    @ColumnWidth(20)
    private String userSexName;

    @ExcelProperty(value = {"手机号码(必填)"}, index = 2)
    @ColumnWidth(30)
    private String userMobile;

    @ExcelProperty(value = {"出生年月(必填)"}, index = 3)
    @ColumnWidth(30)
    private String userBirthday;

    @ExcelProperty(value = {"工作单位(必填)"}, index = 4)
    @ColumnWidth(20)
    private String deptName;

    @ExcelProperty(value = {"职务(必填)"}, index = 5)
    @ColumnWidth(20)
    private String unitPosition;

    @ExcelProperty(value = {"*类别(必填)"}, index = 6)
    @ColumnWidth(20)
    private String leaderTypeName;

    @ExcelProperty(value = {"用户状态(必填)"}, index = 7)
    @ColumnWidth(20)
    private String userStatusName;




    /**
     * 每个模板的首行高度, 换行数目+2 乘以400
     */
    public   static int getHeadHeight(){
        return  (StringUtils.getCharCounts(bigTitle,"\n")+2)*400;
    }
}

  

 

 

维护第一行表头样式

/**
 *
 *
 * 创建模板
 * @author: kuangql@fadada.com
 * @date: 2020/11/30 13:48
 * @description: TODO
 */
public class CreateTemplateWriteHandler implements SheetWriteHandler {


    /**
     * 第一行内容
     */
    private String firstTitle;


    /**
     * 实体模板类的行高
     */
    private int height;


    /**
     * 实体类 最大的列坐标 从0开始算
     */
    private int  lastCellIndex;



    public CreateTemplateWriteHandler(String firstTitle, int height, int cellCounts) {
        this.firstTitle = firstTitle;
        this.height = height;
        this.lastCellIndex = cellCounts;

    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) height);
        //字体样式
        Font font = workbook.createFont();
        font.setColor((short)2);
        Cell cell = row1.createCell(0);

        //单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);

        //设置单元格内容
        cell.setCellValue(firstTitle);


        //合并单元格  --> 起始行, 终止行   ,起始列,终止列
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCellIndex));

    }

  

/**
     * 根据实体类生成模板
     *
     * @param response
     * @param fileName      下 载的文件名,
     * @param sheetName     sheet名
     * @param model         实体类
     * @param height        第一行行高
     * @param title         第一行表头内容
     * @param lastCellIndex 列数
     */
    public static void createTemplate(HttpServletResponse response, String fileName,
                                      String sheetName,
                                      Class<?> model, int height, String title, int lastCellIndex) {

        EasyExcel.write(getOutputStream(fileName, response, ExcelTypeEnum.XLSX), model).
                excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
                .registerWriteHandler(new CreateTemplateWriteHandler(title, height, lastCellIndex))
                .head(model)
                .useDefaultStyle(true).relativeHeadRowIndex(1)
                .doWrite(null);

    }

  

 

 

 

 

 

 

 

上一篇:EasyExcel操作


下一篇:什么?你还在用POI导出数据?EasyExcel解决大数据量导出OOM(内存溢出)