EasyExcel导入导出

跟我说谢谢小猫

已于 2022-04-18 15:26:01 修改

阅读量423

点赞数

分类专栏：日常记录文章标签： excel

于 2021-11-02 11:22:15 首次发布

本文链接：https://blog.csdn.net/weixin_44765388/article/details/121096552

版权

日常记录专栏收录该内容

37 篇文章 1 订阅

订阅专栏

官方文档：https://alibaba-easyexcel.github.io/index.html

pom依赖

        <!-- Excel解析工具 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
        </dependency>

导出：controller


    @GetMapping(value = "/export")
    @ApiOperation(value = "导出", notes = "")
    @ApiImplicitParams({@ApiImplicitParam(paramType = Constants.HEADER, dataType = Constants.STRING, name = Constants.AUTHORIZATION, value = "授权token", required = true)})
    public void exportExcel(HttpServletResponse response, QueryDTO query) throws IOException {
        //此处为带条件分页查询获取list
        QueryPage<ExcelVO> page = service.findList(query);
        Assert.notNull(page.getResult(),"导出失败：查询列表为空");
        String name = DateUtil.format(LocalDateTime.now(), Constants.DATE_FORMAT) + "文件名" ;
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        //单sheet导出
        EasyExcel.write(response.getOutputStream(), ExcelVO.class).sheet("导出").doWrite(page.getResult());
    }

工具类导出：

    @GetMapping("/export")
    @CheckPermissions(value = "security:family:export")
    @ApiOperation(value = "家庭组导出", notes = "permissions = security:family:export")
    @OperationLog(value = logName + "导出", operateType = LogConstants.EXPORT, saveRequestData = false)
    @ApiOperationSupport(order = 7)
    public void export(HttpServletResponse response, FamilyQuery query) {
        List<FamilyExport> list = familyService.findExport(query);
        ExcelUtil.writeExcel(response, list, "家庭组_" + DateUtil.format(new Date(), "yyyyMMddHHmmss"), "家庭组", FamilyExport.class);
    }



import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

/**
 * excel工具类
 *
 * @author: tc
 * @date: 2021-02-04 18:11
 */
@Slf4j
public class ExcelUtil {

    /**
     * excel文件类型
     */
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";


    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     *
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }

        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet readSheet : readSheetList) {
            excelReader.read(readSheet);
        }
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }


    /**
     * 读取一个sheet
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcelOneSheet(InputStream excelInputStream, String fileName, Class<T> clazz) {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
        if (excelReader == null) {
            return Lists.newArrayList();
        }

        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        if(CollectionUtil.isNotEmpty(readSheetList)){
            //读取1个
            excelReader.read(readSheetList.get(0));
        }
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }

    /**
     * 读取Excel 指定sheet
     *
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param sheetIndex
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz, int sheetIndex) {
        ExcelListener<T> excelListener = new ExcelListener<>();
        ExcelReader excelReader = getReader(excelInputStream, fileName, clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }

        excelReader.read(excelReader.excelExecutor().sheetList().get(sheetIndex));
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }

    /**
     * 导出Excel(一个sheet)
     *
     * @param response  HttpServletResponse
     * @param list      数据list
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的sheet名
     * @param clazz     实体类
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {
        OutputStream outputStream = getOutputStream(response, fileName);
        ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).registerWriteHandler(cellStyle()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).registerWriteHandler(cellStyle()).build();
        excelWriter.write(list, writeSheet);
        excelWriter.finish();
    }


    /**
     * 导出Excel(多个sheet)
     *
     * @param response  HttpServletResponse
     * @param list      数据list
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的sheet名
     * @param clazz     实体类
     */
    public static <T> ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {
        OutputStream outputStream = getOutputStream(response, fileName);
        WriteWorkbook writeWorkbook = new WriteWorkbook();
        writeWorkbook.setOutputStream(outputStream);
        writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
        ExcelWriterFactory writerFactory = new ExcelWriterFactory(writeWorkbook, outputStream);
        WriteSheet writeSheet = EasyExcel.writerSheet(1, sheetName).head(clazz).registerWriteHandler(cellStyle()).build();
        writerFactory.write(list, writeSheet);
        return writerFactory;
    }


    /**
     * 导出时生成OutputStream
     */
    private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
        try {
            //创建本地文件
            String filePath = fileName + ".xlsx";
            File file = new File(filePath);

            FileUtil.mkParentDirs(file);

            fileName = new String(filePath.getBytes(), StandardCharsets.ISO_8859_1);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            log.error("导出io异常", e);
            return null;
        }
    }


    /**
     * 返回ExcelReader
     *
     * @param inputStream
     * @param filename
     * @param clazz
     * @param excelListener
     * @param <T>
     * @return
     */
    private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener<T> excelListener) {
        try {
            if (StrUtil.isBlank(filename)) {
                return null;
            }

            if (!filename.toLowerCase().endsWith(XLS) && !filename.toLowerCase().endsWith(XLSX)) {
                return null;
            }

            return EasyExcel.read(inputStream, clazz, excelListener).build();
        } catch (Exception e) {
            log.error("excel读取异常", e);
            return null;
        } finally {
            IoUtil.close(inputStream);
        }
    }

    /**
     * EasyExcel异常信息
     * @param excelDataConvertException
     * @return
     */
    public static String getIndexErrorMsg(ExcelDataConvertException excelDataConvertException) {
        String cellMsg = "";
        CellData cellData = excelDataConvertException.getCellData();
        //这里有一个celldatatype的枚举值,用来判断CellData的数据类型
        CellDataTypeEnum type = cellData.getType();
        if (type.equals(CellDataTypeEnum.NUMBER)) {
            cellMsg = cellData.getNumberValue().toString();
        } else if (type.equals(CellDataTypeEnum.STRING)) {
            cellMsg = cellData.getStringValue();
        } else if (type.equals(CellDataTypeEnum.BOOLEAN)) {
            cellMsg = cellData.getBooleanValue().toString();
        }
        return String.format("excel表格:第%s行,第%s列,数据值为:%s,该数据值不符合要求,请检验后重新导入!", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex() + 1, cellMsg);
    }


    /**
     * 设置样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy cellStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        /*// 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        // 设置底边框;
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 设置底边框颜色;
        headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 设置左边框;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // 设置左边框颜色;
        headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 设置右边框;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        // 设置右边框颜色;
        headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置顶边框;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 设置顶边框颜色;
        headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        // 设置自动换行
        headWriteCellStyle.setWrapped(true);
        // 设置垂直居中
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置水平居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 15);
        headWriteCellStyle.setWriteFont(headWriteFont);*/
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置底边框;
        /*contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 设置底边框颜色;
        contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        // 设置左边框;
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // 设置左边框颜色;
        contentWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 设置右边框;
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        // 设置右边框颜色;
        contentWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        // 设置顶边框;
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 设置顶边框颜色;
        contentWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());*/
        // 设置自动换行
        contentWriteCellStyle.setWrapped(true);
        // 设置垂直居中
        /*contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);*/
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

}

导出：vo

@Data
@ColumnWidth(20)
@HeadRowHeight(30)
@ApiModel("菜品池")
public class CategoryCuisineDishesVO {

    @ApiModelProperty(value = "id")
    @ExcelIgnore
    private Long id;

    @ApiModelProperty(value = "菜品编码")
    @ColumnWidth(30)
    @ExcelProperty(value = "菜品编码", index = 0)
    private String code;
    @ApiModelProperty(value = "菜品名称")
    @ExcelProperty(value = "菜品名称", index = 1)
    private String name;

}

导入：controller


    @PostMapping(value = "/import")
    @ApiOperation(value = "导入", notes = "")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = Constants.HEADER, dataType = Constants.STRING, name = Constants.AUTHORIZATION, value = "授权token", required = true),
            @ApiImplicitParam(paramType = Constants.FORM, name = Constants.FILE, dataType = Constants.DATE_TYPE_FILE, value = "文件对象", required = true)
    })
    public Result importExcel(HttpServletRequest request,@RequestParam("file") MultipartFile file){
        Assert.notNull(file, "请选择文件");
        String name = file.getOriginalFilename().toLowerCase();
        Assert.isTrue(name.endsWith(".xls") || name.endsWith(".xlsx"), "格式不支持！");
        //解析获取用户登录信息
        BaseToken baseToken = TokenUtil.current(request);
        try {
            //建对应的实体类导入监听器和实体类对应的service
            EasyExcel.read(file.getInputStream(), ExcelVO.class,new xxxExcelListener(xxxService,baseToken)).sheet().doRead();
        }catch (IllegalArgumentException e){
            throw e;
        }catch (ExcelAnalysisException e) {
            if (e.getCause() instanceof ExcelDataConvertException) {
                return Result.fail(ExcelUtil.getIndexErrorMsg((ExcelDataConvertException)e.getCause()));
            }
        }catch(Exception e){
            log.error("[导入失败]",e);
            throw new IllegalArgumentException("导入失败");
        }
        return Result.ok();
    }

导入：监听器Listener

public class XxxExcelListener extends AnalysisEventListener<XxxExcelVO> {

    private static final Logger LOGGER = LoggerFactory.getLogger(XxxExcelListener .class);


    private XxxService xxxService;
    private BaseToken baseToken;
    private List<XxxExcelVO> list = new ArrayList<>();

    public XxxExcelListener (XxxService xxxService, BaseToken baseToken){
        this.xxxService= xxxService;
        this.baseToken = baseToken;
    }

    @Override
    public void invoke(XxxExcelVOdata, AnalysisContext analysisContext) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        //每获取一条数据则存到list
        list.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //所有数据存储完成后调用对应的service去新增list
        saveData();
    }

    private void saveData() {
        if (!list.isEmpty()){
            //根据自己的业务新增
            xxxService.importXXX(list,baseToken);
        }
    }
}