使用easyExcel动态导出excel并控制样式

最新推荐文章于 2024-08-03 23:38:17 发布

段漂亮

最新推荐文章于 2024-08-03 23:38:17 发布

阅读量1.7k

点赞数

分类专栏： JAVA后端

本文链接：https://blog.csdn.net/weixin_39989962/article/details/114443114

版权

JAVA后端专栏收录该内容

45 篇文章 0 订阅

订阅专栏

本文档详细介绍了如何使用EasyExcel库来处理Excel文件，包括设置单元格样式（如居中、列宽、行高）、数据转换器、以及自定义样式策略。示例代码展示了如何根据条件导出不同列，并提供了完整的Java实体类和导出方法。

摘要由CSDN通过智能技术生成

easyExcel的使用手册 https://www.yuque.com/easyexcel/doc/write#W4u1e

本文章用到了水平居中,垂直居中,列高,行宽,头样式,内容样式等等,可参考

package com.sanzhisoft.mybatis.oa.announcement.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.*;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.sanzhisoft.mybatis.oa.constants.config.LocalDateTimeConverter;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.hibernate.validator.constraints.NotBlank;

import javax.validation.constraints.NotNull;
import java.time.LocalDateTime;

/**
 * <p>
 * 
 * </p>
 *
 * @author syetem
 * @since 2021-03-03
 */
@TableName("t_official_register")
@ApiModel
@HeadRowHeight(25)
@ColumnWidth(20)
@Data
public class OfficialRegister extends Model<OfficialRegister> {

    private static final long serialVersionUID=1L;

    @ExcelIgnore
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * 编号
     */
    @ExcelProperty("编号")
    @ApiModelProperty(value = "编号")
    @NotNull(message = "编号不能为空")
    private Integer code;

    /**
     * 来文机关
     */
    @ExcelProperty("来文机关")
    @ApiModelProperty(value = "来文机关")
    @NotBlank(message = "来文机关不能为空")
    private String handleDeptName;

    /**
     * 原文号
     */
    @ExcelProperty("原文号")
    @ApiModelProperty(value = "原文号")
    @NotBlank(message = "原文号不能为空")
    private String originalOfficialNumber;

    /**
     * 收文时间
     */
    @ExcelProperty(value = "收文时间",converter = LocalDateTimeConverter.class)
    @ApiModelProperty(value = "收文时间")
    private LocalDateTime receiveTime;

    /**
     * 份数
     */
    @ExcelProperty("份数")
    @ApiModelProperty(value = "份数")
    @NotNull(message = "份数不能为空")
    private Integer copies;

    /**
     * 密级
     */
    @ExcelProperty("密级")
    @ApiModelProperty(value = "密级")
    @NotBlank(message = "密级不能为空")
    private String securityClass;

    /**
     * 标题
     */
    @ExcelProperty("标题")
    @ColumnWidth(30)
    @ApiModelProperty(value = "标题")
    @NotBlank(message = "标题不能为空")
    private String title;

    /**
     * 科室意见
     */
    @ExcelProperty("科室意见")
    @ApiModelProperty(value = "科室意见")
    @NotBlank(message = "科室意见不能为空")
    private String deptOpinion;

    /**
     * 领导批示
     */
    @ExcelProperty("领导批示")
    @ApiModelProperty(value = "领导批示")
    private String leaderOpinion;
    /**
     * 办理情况
     */
    @ExcelProperty("办理情况")
    @ApiModelProperty(value = "办理情况")
    private String situation;

    /**
     * 办理过程
     */
    @ExcelProperty("办理过程")
    @ApiModelProperty(value = "办理过程")
    private String course;

    /**
     * 办理单位
     */
    @ExcelProperty("办理单位")
    @ApiModelProperty(value = "办理单位")
    private String unit;

    /**
     * 督办科室
     */
    @ExcelProperty("督办科室")
    @ApiModelProperty(value = "督办科室")
    private String supervisionDept;

    /**
     * 部门办理时限
     */
    @ExcelProperty(value = "部门办理时限",converter = LocalDateTimeConverter.class)
    @ApiModelProperty(value = "部门办理时限")
    private LocalDateTime deptTimeLimit;

    /**
     * 批办结果
     */
    @ExcelProperty("批办结果")
    @ApiModelProperty(value = "批办结果")
    private String transactionResult;

    /**
     * 错情登记
     */
    @ExcelProperty("错情登记")
    @ColumnWidth(50)
    @ApiModelProperty(value = "错情登记")
    private String wrongRegister;

    @ExcelIgnore
    @TableField(fill = FieldFill.INSERT)
    private Long createUserId;

    @ExcelProperty("创建人")
    @TableField(fill = FieldFill.INSERT)
    private String createUserName;

    @ExcelIgnore
    @TableField(fill = FieldFill.INSERT)
    private Long createOrgId;

    @ExcelProperty("创建部门")
    @TableField(fill = FieldFill.INSERT)
    private String createOrgName;

    @ExcelProperty(value = "创建时间",converter = LocalDateTimeConverter.class)
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    @ExcelIgnore
    private boolean deleteFlag;
}

 @Override
    public Response getOfficialRegisterExcel(Integer pageIndex, Integer pageSize, String handleDeptName, String originalOfficialNumber,
                                             String title, String status, boolean isWrong, String startTime, String endTime, HttpServletRequest request) {
        File file = null;
        InputStream inputStream = null;
        try {
            QueryWrapper<OfficialRegister> queryWrapper = getOfficialRegisterQueryWrapper(handleDeptName, originalOfficialNumber, title, status, isWrong, startTime, endTime);
            List<OfficialRegister> valueList = list(queryWrapper);
            String fileName = System.currentTimeMillis() + "." + "xls";



            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            setWriteCellStyle(headWriteCellStyle);
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)15);
            headWriteCellStyle.setWriteFont(headWriteFont);

            //处理样式
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            setWriteCellStyle(contentWriteCellStyle);
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontHeightInPoints((short)10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            if(isWrong){
                //如果为错情导出,则只导出固定的几列
                Set<String> includeColumnFiledNames = new HashSet<String>();
                includeColumnFiledNames.add("title");
                includeColumnFiledNames.add("handleDeptName");
                includeColumnFiledNames.add("receiveTime");
                includeColumnFiledNames.add("wrongRegister");
                EasyExcel.write(privatePath + "/" + fileName,OfficialRegister.class).registerWriteHandler(horizontalCellStyleStrategy)
                        .includeColumnFiledNames(includeColumnFiledNames)
                        .sheet("来文登记错误情况列表").doWrite(valueList);
            }else{
                EasyExcel.write(privatePath + "/" + fileName,OfficialRegister.class).registerWriteHandler(horizontalCellStyleStrategy)
                        .sheet("来文登记列表").doWrite(valueList);
            }

            file = new File(privatePath + "/" + fileName);
            inputStream = new FileInputStream(file);
            String fileNameD = CommonTool.getFileName(request, "来文登记列表" + DateUtils.dateToStringYYYYMMDD(new Date()));

            return Response.ok(inputStream, "application/vnd.ms-excel;charset=utf-8;")
                    .header("Access-Control-Expose-Headers", "Content-Disposition")
                    .header("Content-Disposition", "attachment; filename="+fileNameD+".xlsx")
                    .build();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Response.ok().build();
        } finally {
            if(null != file) file.delete();
        }
    }

    private void setWriteCellStyle(WriteCellStyle writeCellStyle){
        //设置边框样式
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        //水平加垂直居中
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //自动换行
        writeCellStyle.setWrapped(true);
    }

    private QueryWrapper<OfficialRegister> getOfficialRegisterQueryWrapper(String handleDeptName, String originalOfficialNumber, String title, String status, boolean isWrong, String startTime, String endTime) {
        QueryWrapper<OfficialRegister> queryWrapper = new QueryWrapper<>();
        if(StringUtils.isNotBlank(handleDeptName)){
            queryWrapper.like("handle_dept_name",handleDeptName);
        }
        if(StringUtils.isNotBlank(originalOfficialNumber)){
            queryWrapper.like("original_official_number",originalOfficialNumber);
        }
        if(StringUtils.isNotBlank(title)){
            queryWrapper.like("title",title);
        }
        if (StringUtils.isNotEmpty(startTime)) queryWrapper.ge("create_time", DateUtils.formatStartTime(startTime));
        if (StringUtils.isNotEmpty(endTime)) queryWrapper.le("create_time", DateUtils.formatEndTime(endTime));
        if(StringUtils.isNotBlank(status)){
            if(status.equalsIgnoreCase(OfficialRegisterStatus.COMPLETED.name())){
                queryWrapper.isNull("dept_time_limit");
            }else if(status.equalsIgnoreCase(OfficialRegisterStatus.UN_COMPLETED.name())){
                queryWrapper.isNotNull("dept_time_limit");
            }
        }
        if(isWrong){
            queryWrapper.isNotNull("wrong_register");
        }
        queryWrapper.eq("delete_flag",false);
        queryWrapper.orderByDesc("create_time");
        return queryWrapper;
    }