EasyExcel2.1.7版本简单实现导入导出

简介：

---

EasyExcel 2.1.7实现导入导出

---

一 、Excel导出的模样^ _ ^

二 、直接开始_maven依赖

       <!-- poi 相关-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- esayexcel 2.1.7  -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.7</version>
        </dependency>

三 、工具类及映射实体

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
版本2.1.7 工具类(仅导出)中可自定义样式格式等
 **/
@Component
public class EasyExcelUtil {
    /**
     * 导出 Excel ：一个 sheet，带表头.
     *
     * @param response  HttpServletResponse
     * @param  list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param model     映射实体类，Excel 模型
     * @throws Exception 异常
     */

    public void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class model) throws Exception {

        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        // 字体
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setWrapped(true);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(getOutputStream(fileName, response), model).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy)
                //最大长度自适应 目前没有对应算法优化 建议注释掉不用 会出bug
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(data);

    }

    /**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

}

三 、控制层及实体类 超简单导出

	// 控制层
    @GetMapping("/export")
    @ApiOperation(value = "导出全部字段excel")
    public void exportExcel(HttpServletResponse resp){
        try {
            modelStandardFieldService.export(resp);
        }catch (Exception e) {
            log.error(e.getMessage(),e);
        }
    }

	// 服务层
    @Autowired
    private EasyExcelUtil excelUtil;

    public void export(HttpServletResponse resp) {
        List<StandardFieldExcelDto> datas = getDatas();
        try {
            excelUtil.writeExcel(resp,datas,"字段"+System.currentTimeMillis(),"sheet1", StandardFieldExcelDto.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

// 映射实体 可自定义属性高度宽度等
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;

/**
 * @description:
 **/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ContentRowHeight(15)
@HeadRowHeight(30)
public class StandardFieldExcelDto  extends BaseRowModel implements Serializable {

    @ColumnWidth(35)
    @ExcelProperty(value = "字段ID",index = 0)
    private String fieldId;

    @ColumnWidth(15)
    @ExcelProperty(value = "字段名称" ,index = 1)
    private String fieldName;

    @ColumnWidth(10)
    @ExcelProperty(value = "字段编码",index = 2 )
    private String fieldCode;

    @ColumnWidth(15)
    @ExcelProperty(value = "字段编号",index = 3 )
    private String fieldNumber;

    @ColumnWidth(10)
    @ExcelProperty(value = "字段类型",index = 4 )
    private String fieldType;

    @ColumnWidth(10)
    @ExcelProperty(value = "字段长度",index = 5 )
    private Short fieldLength;

    @ColumnWidth(40)
    @ExcelProperty(value = "归属目录" ,index = 6)
    private String fieldCatalogId;

    @ColumnWidth(10)
    @ExcelProperty(value = "字段种类" ,index = 7)
    private String fieldKind;

    @ColumnWidth(10)
    @ExcelProperty(value = "字段来源",index = 8 )
    private String fieldSource;

    @ColumnWidth(18)
    @ExcelProperty(value = "备注" ,index = 9)
    private String fieldNote;

    @ColumnWidth(10)
    @ExcelProperty(value = "是否上线",index = 10 )
    private Boolean online;

    @ColumnWidth(24)
    @ExcelProperty(value = "创建时间",index = 11 )
    private Date createTime;

    @ColumnWidth(24)
    @ExcelProperty(value = "修改时间",index = 12 )
    private Date updateTime;

}

四 、导入及监听(监听可用于校验,业务操作等)

	// 控制层
    @PostMapping("/import")
    @ApiOperation(value = "导入全部字段excel")
    public Response importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request){
        Response response = null;
        try {
//            List<StandardFieldExcelDto> objects =
//                    EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(StandardFieldExcelDto.class).sheet().doReadSync();

            ExcelListener excelListener = new ExcelListener<StandardFieldExcelDto>();

            EasyExcel.read(new BufferedInputStream(file.getInputStream()),excelListener).head(StandardFieldExcelDto.class).sheet().doReadSync();

            response = new Response(excelListener);
        } catch (ParamException e) {
            log.error(e.getMessage(),e);
            response = new Response(e.getCode(), e.getMessage());
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            response = new Response(TkResponseCode.ERROR_NORMAL);
        }
        return response;
    }

	// 监听 *** 重点
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;

/**
 * 解析监听器，
 * 每解析一行会回调invoke()方法。
 * 整个excel解析结束会执行doAfterAllAnalysed()方法
 */
@Slf4j
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 自定义存储表格数据
     */
    private List<T> dataList = new ArrayList<>();
    /**
     * 自定义存储标题结果
     */
    private T  titleMap;


    @Override
    public void invoke(T result, AnalysisContext context) {
        //获取当前行号
        Integer rowIndex = context.readRowHolder().getRowIndex();
        if (rowIndex == 0) {
            this.titleMap = result;
        } else {
            //数据存储到list，供批量处理，或后续自己业务逻辑处理。
            if (result instanceof StandardFieldExcelDto){
                // 校验等等
            }
            dataList.add(result);
        }
        log.info("解析数据第{}行，数据为：{}", rowIndex, result);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // list.clear();//解析结束销毁不用的资源
        // 处理业务数据插入表 还是校验等等
        log.info("解析完成！");
    }
	

五 、希望对一些小伙伴有用 over ^ _ ^