Excel工具类以及OSS使用_com.alibaba.excel.metadata.celldata

1 maven依赖添加

<!-- easyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>
        <!-- oss文件上传下载 -->
        <dependency>
            <groupId>com.aliyun.oss</groupId>
            <artifactId>aliyun-sdk-oss</artifactId>
            <version>3.10.2</version>
        </dependency>

2 实体类注解设置

@Data
@ApiModel("用户表")
// 忽略未添加@ExcelProperty的字段
@ExcelIgnoreUnannotated
public class UserEntity {
 
    @TableId()
    @ApiModelProperty("主键id")
    @ExcelProperty(value = {"操作"}, index = 0, converter = NullValueConvert.class)
    private String id;
 
    @ApiModelProperty("家庭地址")
    private String address;
}

3，NullValueConvert  格式转换

解释：当id字段为空时，excel上此字段显示空字符串，只需要实现Converter接口就可以了

public class NullValueConvert implements Converter<String> {
 
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }
 
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
 
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return cellData.getStringValue();
    }
 
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return new CellData("");
    }
}

eg: 数据库存储 0 代表否  1代表是 那么转换如下

public class YesOrNoConvert implements Converter<Integer> {
 
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }
 
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
 
    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return "是".equals(cellData.getStringValue()) ? 1 : 0;
    }
 
    @Override
    public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return new CellData(value.equals(1) ? "是" : "否");
    }
}

4， excel工具类编写

import cn.hutool.core.io.IoUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import com.aliyun.oss.model.OSSObject;
import com.config.AliyunConfig;
import com.google.common.collect.Lists;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Component;
 
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
 
/**
 * @author guiJia
 * @date 2021/5/31 19:41
 */
@Component
@Slf4j
public class ExcelUtil {
 
    @Resource
    private HttpServletRequest request;
 
    @Resource
    private HttpServletResponse response;
 
    @Resource
    private AliyunConfig aliyunConfig;
 
    /**
     * 导出是生成 OutputStream
     */
    public static OutputStream getOutputStream(String fileName,
                                               HttpServletRequest request,
                                               HttpServletResponse response) throws IOException {
        if (null != request) {
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        }
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        response.setHeader("content-type","application/x-msdownload");
        return response.getOutputStream();
    }
 
    /**
     * 导出excel
     * @param fileName 文件名
     * @param clazz 表头类
     * @param clazzList 数据列表
     */
    public void export(String fileName, Class<?> clazz, List<?> clazzList) {
        export(fileName, clazz, clazzList, Lists.newArrayList(), true);
    }
 
    /**
     * 导出excel
     */
    public void export(String fileName, Class<?> clazz, List<?> clazzList, List<String> excludeColumnList) {
        export(fileName, clazz, clazzList, excludeColumnList, false);
    }
 
    /**
     * 导出excel
     */
    private void export(String fileName, Class<?> clazz, List<?> clazzList, List<String> excludeColumnList, boolean isAuto) {
        try {
            fileName = fileName + DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now());
            fileName = URLEncoder.encode(fileName, "UTF-8");
            EasyExcelFactory.write(ExcelUtil.getOutputStream(fileName, request, response))
                    .excelType(ExcelTypeEnum.XLSX)
                    .head(clazz)
                    .sheet()
                    .registerWriteHandler(isAuto ? new ExcelWidthStyleStrategy() : null)
                    .excludeColumnFiledNames(excludeColumnList)
                    .doWrite(clazzList);
        } catch (Exception e) {
            log.error("下载失败：", e);
        }
    }
 
    /**
     * 自动设置列宽
     */
    public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy{
        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.setColumnWidth(cell.getColumnIndex(), 3500);
        }
    }
 
    /**
     * 上传文件到oss平台
     *
     */
    public String uploadOss(String fileName, Class<?> clazz, List<?> clazzList) {
        fileName = fileName + DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now())
                + ExcelTypeEnum.XLSX.getValue();
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        EasyExcelFactory.write(out)
                .excelType(ExcelTypeEnum.XLSX)
                .head(clazz)
                .sheet()
                .registerWriteHandler(new ExcelWidthStyleStrategy())
                .doWrite(clazzList);
 
        OSS ossClient = new OSSClientBuilder().build(aliyunConfig.getEndpoint(), aliyunConfig.getAccessKeyId(), aliyunConfig.getAccessKeySecret());
        ossClient.putObject(aliyunConfig.getBucketName(), fileName, new ByteArrayInputStream(out.toByteArray()));
        ossClient.shutdown();
        return fileName;
    }
 
    /**
     * 登录oss
     */
    private InputStream openOss(String objectName) {
        OSS ossClient = new OSSClientBuilder().build(aliyunConfig.getEndpoint(), aliyunConfig.getAccessKeyId(), aliyunConfig.getAccessKeySecret());
        OSSObject ossObject = ossClient.getObject(aliyunConfig.getBucketName(), objectName);
        return ossObject.getObjectContent();
    }
 
    /**
     * 从oss下载文件
     */
    public void exportOss(String objectName) throws IOException {
        @Cleanup InputStream objectContent = openOss(objectName);
        String fileName = objectName.substring(objectName.lastIndexOf("/") + 1, objectName.lastIndexOf(ExcelTypeEnum.XLSX.getValue()));
        fileName = URLEncoder.encode(fileName, "UTF-8");
        @Cleanup OutputStream outputStream = ExcelUtil.getOutputStream(fileName, request, response);
        outputStream.write(IoUtil.readBytes(objectContent));
    }
 
    /**
     * 从oss下载图片
     */
    public void download(String objectName) throws IOException {
        @Cleanup InputStream objectContent = openOss(objectName);
        String fileName = objectName.substring(objectName.lastIndexOf("/") + 1, objectName.lastIndexOf("png") - 1);
        fileName = URLEncoder.encode(fileName, "UTF-8");
        @Cleanup OutputStream outputStream = getResponse(fileName);
        outputStream.write(IoUtil.readBytes(objectContent));
    }
 
    /**
     * 图片解析
     */
    private OutputStream getResponse(String fileName) throws IOException {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileName);
        response.setHeader("content-type", "image/png");
        return response.getOutputStream();
    }
}

5，阿里云配置类

@Data
@Configuration
@ConfigurationProperties(prefix = "aliyun.oss.file")
public class AliyunConfig {
 
    private String endpoint;
 
    private String accessKeyId;
 
    private String accessKeySecret;
 
    private String bucketName;
}

6，使用以及测试

excelUtil.export("用户列表导出", UserEntity.class, userEntityList);