• 项目实战 | Excel导出（三）——Excel导出样式

    项目实战 | Excel导出（三）——Excel导出样式

    章节
    一、Excel导出方式
    二、Excel返回内容优化
    三、Excel导出样式

    1、填充Excel的样式

    仅需要在Excel上改变导出模板的样式，如：内容字体样式和大小、单元格边框、单元格底色等等
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pea3pSJc-1660275981772)(E:\AppData\Roaming\Typora\typora-user-images\image-20220811101558860.png)]
    将采用模板导出方式，即可按照设置的模板样式格式进行导出
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-udu4LqZb-1660275981775)(E:\AppData\Roaming\Typora\typora-user-images\image-20220811101808491.png)]
    代码：

    @Override
public String exportGradeTPList(String schoolID) throws IOException {

    List<GradeTpList> gradeTpLists =teachPlanMgrBaseInfoMapper.selectGradeTpListBySchoolId(schoolID);
    Date date=new Date();
    String excelName =  "个人教案库-年级详细统计数据(" + DateUtils.getTime(date) + ")";
    String fileName = "个人教案库-年级详细统计数据(" + DateUtils.getExcelTime(date) + ")" + ".xlsx";
    
    File file = new File(ResourceUtils.getURL("classpath:").getPath()+"static/outExcel/" + fileName);
    File parentFile = file.getParentFile();
    if (!parentFile.exists()) {
        parentFile.mkdir();
    }
    InputStream is = getGradeTemplateIn("excel/GradeTeachPlanData.xlsx",gradeTpLists);
    ExcelWriter excelWriter = EasyExcel.write(file)
        .withTemplate(is)
        .registerConverter(new FileSizeConverter())
        .registerConverter(new RateConverter())
        .build();

    for (int i = 0; i < gradeTpLists.size(); i++) {
        WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
        excelWriter.fill(gradeTpLists.get(i).getGradeTpExcelInfoList(), writeSheet);
        Map<String, Object> map = new HashMap<>(5);
        map.put("excelName", excelName);
        map.put("gradeName", gradeTpLists.get(i).getGradeName());
        map.put("countTeachPlan", gradeTpLists.get(i).getCountTeachPlan());
        map.put("tpSize", gradeTpLists.get(i).getTpSize());
        map.put("frozenCount", gradeTpLists.get(i).getFrozenCount());
        excelWriter.fill(map, writeSheet);
    }
    excelWriter.finish();
    String filePath = baseInfo.getResMgrRootUrl()+"/outExcel/"+fileName;
    return filePath;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    2、通过类结构导出Excel的样式

    样图：
    在这里插入图片描述

    思路：通过类属性和注解去映射出Excel的布局，再去通过拦截器修改样式和相关内容。

    ①类结构：

    @Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("共享教案库教师统计数据")
public class ShareTpExcelInfo {


    @ExcelProperty(value = { "共享教案-教师统计数据","教师姓名"})
    @ColumnWidth(20)
    @JSONField(name = "SharerName")
    @ApiModelProperty("共享者名称")
    private String sharerName;

    @ExcelProperty(value = { "共享教案-教师统计数据","所属学科"})
    @ColumnWidth(35)
    @JSONField(serialize = false)
    @ApiModelProperty(value = "所属学科",hidden = true)
    private String sharerSubject;

    @ExcelProperty(value = { "共享教案-教师统计数据","教案共享数量"})
    @ColumnWidth(20)
    @JSONField(name = "SharerCount")
    @ApiModelProperty("教案共享数量")
    private Integer sharerCount;

    @NumberFormat("#.###%")
    @ExcelProperty(value = { "共享教案-教师统计数据", "教案使用率"})
    @ColumnWidth(15)
    @JSONField(name="UsageRate",format = "#0.000")
    @ApiModelProperty("教案使用率")
    private Double usageRate;

    @NumberFormat("#0.0")
    @ExcelProperty(value = { "共享教案-教师统计数据","综合值"})
    @ColumnWidth(15)
    @ApiModelProperty("综合值" +
            "综合值= 上传教案数*5%+被使用教案数*75% +使用次数*20%")
    @JSONField(name="CompositeIndex",format = "#0.0")
    private Double compositeIndex;

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    ②行列的高度宽度设置

    @HeadRowHeight(35) ：表头每行高度为35

@ContentRowHeight(28)：内容每行高度为28

@ColumnWidth(22)：每列宽度为22

    • 1
    • 2
    • 3
    • 4
    • 5

    ③值格式的调整

    @NumberFormat("#.###%")：值保存三位小数，再转换

@NumberFormat("#0.0")：值保存一位小数点
    
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒") ：将Date转换为标准日期格式

    • 1
    • 2
    • 3
    • 4
    • 5

    ④两行表头

    //第一行表头为：共享教案-教师统计数据
//第二行表头为：教师姓名|所属学科
@ExcelProperty({ "共享教案-教师统计数据","教师姓名"})
@ExcelProperty({ "共享教案-教师统计数据","所属学科"})

    • 1
    • 2
    • 3
    • 4

    ⑤导出代码

    EasyExcel.write(file, ShareTpExcelInfo.class)
    .registerWriteHandler(new LevelCellStyleStrategy())
    .registerWriteHandler(
   		 new ShareWorkbookWriteHandlerUtil(excelName,4,
                                      list.size()+4,"综合值= 上传教案数*5%+被使用教案数*75% +使用次数*20%")
	)
    .sheet("统计数据").doWrite(list);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    ⑥样式策略类：

    public class LevelCellStyleStrategy extends AbstractVerticalCellStyleStrategy {

    @Override
    protected WriteCellStyle headCellStyle(Head head) {
        // 头的策略  样式调整
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 头背景 白
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        // 头字号
        headWriteFont.setFontHeightInPoints((short) 14);
        // 字体样式
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 自动换行
        headWriteCellStyle.setWrapped(true);

        // 设置细边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 设置边框颜色 黑
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return headWriteCellStyle;
    }

    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        //内容样式策略
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        // 根据不同字段名，采用不同的对其方式
        if (
                head.getFieldName().equals("uploadNum")
                || head.getFieldName().equals("frozenCount")
                || head.getFieldName().equals("usageRate")
                || head.getFieldName().equals("sharerCount")
                || head.getFieldName().equals("compositeIndex")
        ){
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        }else if(head.getFieldName().equals("tpSize")){
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.RIGHT);
        }else {
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        }
        // 自动换行
        writeCellStyle.setWrapped(true);
        // 水平对齐方式
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置细边框
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        // 设置边框颜色 黑
        writeCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        writeCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        writeCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        writeCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        return writeCellStyle;
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69

    ⑦通过拦截器对文档进行切面处理

    EasyExcel常用的两种拦截器：WorkbookWriteHandler 和 SheetWriteHandler

    WorkbookWriteHandler 拦截器：可编写EasyExcel创建完Excel的WorkBook单元格需要执行的前置方法和后置方法

    SheetWriteHandler拦截器：可编写EasyExcel创建完Excel的Sheet需要执行的前置方法和后置方法

    public class ShareWorkbookWriteHandlerUtil implements WorkbookWriteHandler{

    private final String title;
    private final Integer size;
    private final Integer outputSize;
    private final String remark;

    public ShareWorkbookWriteHandlerUtil(String title, Integer size,Integer outputSize,String remark) {
        this.title = title;
        this.size =size;
        this.remark = remark;
        this.outputSize = outputSize;
    }

    @Override
    public void beforeWorkbookCreate() {

    }

    @Override
    public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {

    }

    @Override
    public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        //设置第一行表头内容
        Row row1 = sheet.getRow(0);
        row1.setHeight((short) 1000);

        for (int i = 0; i < size; i++) {
            Cell cell = row1.getCell(i);
            if (cell!=null){
                //设置单元格内容
                cell.setCellValue(title);
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);

                // 设置细边框
                cellStyle.setBorderBottom(BorderStyle.NONE);
                cellStyle.setBorderLeft(BorderStyle.NONE);
                cellStyle.setBorderRight(BorderStyle.NONE);
                cellStyle.setBorderTop(BorderStyle.NONE);

                Font font = workbook.createFont();
                font.setBold(true);
                font.setFontHeight((short) 320);
                font.setFontName("宋体");

                cellStyle.setFont(font);
                cellStyle.setWrapText(true);
                cell.setCellStyle(cellStyle);
            }
        }

        //设置表末尾的备注提示
        Row row = sheet.createRow(outputSize);
        row.setHeight((short) 400);
        Cell cell = row.createCell(1);
        //设置单元格内容
        cell.setCellValue(remark);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(false);
        font.setFontHeight((short) 250);
        font.setColor(IndexedColors.RED.getIndex());

        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
  • 相关阅读:
    CentOS修改root用户密码
    丢弃法Dropout(Pytorch)
    Linux 进程创建,进程状态,优先级
    NOIP2023模拟19联测40 诡异键盘
    c语言-实用调试技巧
    每天一个注解之@WebService
    C#多线程编程技术——多线程操作（没看懂）
    KF、EKF、IEKF、UKF卡尔曼滤波器
    Android R 11.x quickstep 手势导航架构和详细实现
    国外资源加速下载器，代码全部开源
  • 原文地址：https://blog.csdn.net/weixin_44593275/article/details/126301237