EasyExcel解析数据文

北漂的追风少年

已于 2024-07-02 11:00:01 修改

阅读量204

点赞数 9

文章标签： java 服务器前端

于 2024-07-02 10:54:56 首次发布

本文链接：https://blog.csdn.net/cjz_huateng/article/details/140119478

版权

一、引入依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.1.0</version>
</dependency>

二、创建Excel解析工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
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.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

public class EasyExcelUtils {
    private static final Log log = LogFactory.getLog(EasyExcelUtils.class);
    /**
     * 单sheet版本Excel读取
     * 从Excel中读取文件，读取的文件是一个DTO类
     * @param inputStream 文件流
     * @param sheetNo sheet的序号，从0开始
     * @param headFlag 是否存在表头
     * @param clazz       行数据类型
     */
    public static <T> List<T> readExcelOneSheet(InputStream inputStream,Integer sheetNo,boolean headFlag, final Class<?> clazz) {
        // 1.创建监听类
        ExcelListener<T> listener = new ExcelListener<>();
        // 2.构建工作簿对象的输入流
        ExcelReader excelReader = EasyExcel.read(inputStream, clazz, listener).build();
        if(sheetNo==null){
            sheetNo = 0;
        }
        // 3.构建工作表对象的输入流，默认是第一张工作表
        int headRow = 0;
        if(headFlag){
            headRow = 1;
        }
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).headRowNumber(headRow).build();
        // 4.读取信息，每读取一行都会调用监听类的 invoke 方法
        excelReader.read(readSheet);
        // 5.关闭流，如果不关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
        return listener.getDataList();
    }

    /**
     * 多sheet版本Excel读取
     * @param <T>      行数据的类型
     * @param filePath 文件路径
     * @param clazz    行数据的类型
     * @return 所有信息
     */
    public static <T> List<T> readExcelAllSheet(String filePath, final Class<?> clazz) {
        ExcelListener<T> listener = new ExcelListener<>();
        // 读取全部sheet
        // 这里需要注意 ExcelListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
        EasyExcel.read(filePath, clazz, listener).doReadAll();
        return listener.getDataList();
    }
    /**
     * 网页上的下载导出，只有一个工作表
     *
     * @param fileName  文件名
     * @param clazz     类的字节码文件，行数据的类型
     * @param dataList  导出的数据
     * @param sheetName 工作表名
     * @param response  响应体
     * @throws IOException 异常对象
     */
    public static void writeWeb(String fileName, final Class<?> clazz, List<?> dataList, String sheetName, HttpServletResponse response) throws IOException {
        // 1.指定响应体内容类型
        response.setContentType("application/vnd.ms-excel");
        // 2.指定编码方式
        response.setCharacterEncoding("utf-8");
        // 3.URLEncoder.encode可以防止中文乱码：import java.net.URLEncoder
        fileName = URLEncoder.encode(fileName, "UTF-8");
        // 4.指定响应标头
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        // 5.获取工作簿对象的输出流
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        // 6.设置工作表的名称
        if (!StringUtils.hasText(sheetName)) {
            sheetName = "sheet1";
        }
        // 7.指定写用哪个class去写
        WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(clazz).build();
        // 8.将 dataList 中的数据逐行写入工作表中
        excelWriter.write(dataList, writeSheet);
        // 9.finish关闭流
        excelWriter.finish();
        // 10.关闭流
        response.getOutputStream().close();
    }

    public static void writeExcelList(List<SheetInfoVo> sheetList,String filePath) throws Exception {
        try{
            ExcelWriter excelWriter = EasyExcel.write(new File(filePath)).build();
            WriteSheet writeSheet;
            for (SheetInfoVo bean : sheetList) {
                // 构建sheet对象
                writeSheet = EasyExcel.writerSheet(bean.getSheetName()).head(bean.getHeadClass())
                        .registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
                        .registerWriteHandler(new ColumnWidthStyleStrategy())
                        .registerWriteHandler(new RowHeightStyleStrategy())
                        .build();
                // 写出sheet数据
                excelWriter.write(bean.getDataList(), writeSheet);
            }
            // 关流
            excelWriter.finish();
        } catch (Exception e) {
            log.error("系统异常：{}",e);
        }
    }

    public static void writeExcelListAutoCell(List<SheetInfoVo> sheetList,String filePath) throws Exception {
        try{
            ExcelWriter excelWriter = EasyExcel.write(new File(filePath)).build();
            WriteSheet writeSheet;
            for (SheetInfoVo bean : sheetList) {
                // 构建sheet对象
                writeSheet = EasyExcel.writerSheet(bean.getSheetName()).head(bean.getHeadClass())
                        .registerWriteHandler(getDefaultHorizontalCellStyleStrategy())
                        .registerWriteHandler(new ColumnAutoWidthStyleStrategy())
                        .registerWriteHandler(new RowHeightStyleStrategy())
                        .build();
                // 写出sheet数据
                excelWriter.write(bean.getDataList(), writeSheet);
            }
            // 关流
            excelWriter.finish();
        } catch (Exception e) {
            log.error("系统异常：{}",e);
        }
    }

    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        return response.getOutputStream();
    }
    /**
     * 获取默认表头内容的样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
		//设置内容垂直居中对齐
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//		//设置内容水平居中对齐
//		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 单sheet版本Excel读取
     * 从Excel中读取文件，读取的文件是一个DTO类
     * @param inputStream 文件流
     * @param sheetNo sheet的序号，从0开始
     * @param clazz       行数据类型
     */
    public static void readExcelOneSheet(InputStream inputStream,Integer sheetNo, final Class<?> clazz, final ReadListener listener) {
        // 1.构建工作簿对象的输入流
        ExcelReader excelReader = EasyExcel.read(inputStream, clazz, listener).build();
        if(sheetNo==null){
            sheetNo = 0;
        }
        // 3.构建工作表对象的输入流，默认是第一张工作表
        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
        // 4.读取信息，每读取一行都会调用监听类的 invoke 方法
        excelReader.read(readSheet);
        // 5.关闭流，如果不关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
    }
}

三、sheet实体对象

@Data
public class SheetInfoVo {

    /**
     * sheet页名称
     */
    private String sheetName;

    /**
     * sheet标题bean
     */
    private Class<?> headClass;

    /**
     * sheet页数据
     */
    private List<?> dataList;

}

四、sheet实体对象

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
 * excel通用读取监听类
 */

@Slf4j
@Getter
@NoArgsConstructor
public class ExcelListener<T> extends AnalysisEventListener<T> {
    /**
     * 自定义用于暂时存储data 可以通过实例获取该值
     */
    private final List<T> dataList = new ArrayList<>();
    /**
     * 每解析一行都会回调invoke()方法
     *
     * @param data 每一行的数据
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        dataList.add(data);
        //log.info("读取的一条信息：{}", data);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        log.info("{}条数据，解析完成", dataList.size());
    }
}

五、Excel设置自适应列宽

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.CollectionUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *Excel设置列宽
 **/
public class ColumnAutoWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 50;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }

                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

六、Excel设置固定列宽和行高

mport com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;
/**
 *Excel设置列宽
 **/
public class ColumnWidthStyleStrategy extends AbstractHeadColumnWidthStyleStrategy {

    @Override
    protected Integer columnWidth(Head head, Integer columnIndex) {
        if(head!=null){
            switch (columnIndex) {
                case 0 : return 20;
                case 1 : return 16;
                case 2 : return 18;
                case 3 : return 12;
                case 4 : return 10;
                case 5 : return 14;
                case 6 : return 12;
                case 7 : return 17;
                case 8 : return 12;
                case 9 : return 18;
                case 10 : return 12;
                case 11: return 14;
                case 12: return 15;
                case 13: return 12;
                case 14: return 12;
            }
            return 20;
        }else{
            return 30;
        }
    }
}

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;
/**
 *Excel设置行高
 **/
public class RowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        // 设置 头 行高
        row.setHeightInPoints((float) 20);
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        // 设置 内容 行高
        row.setHeightInPoints((float) 16);
    }
}

七、Excel数据解析

InputStream inputStream = new FileInputStream(new File("C:\temp\1.xlsx"));
List<Vo> voList = EasyExcelUtils.readExcelOneSheet(inputStream,1,false, Vo.class);

八、Excel生成

List<SheetInfoVo> sheetList = new ArrayList<>();
SheetInfoVo sheetInfo1 = new SheetInfoVo();
sheetInfo1.setDataList(tmpList);
sheetInfo1.setHeadClass(VO.class);
sheetInfo1.setSheetName("sheet1");

sheetList.add(sheetInfo1);

String fileName = "result.xlsx";
String filePath = "C:\temp";
EasyExcelUtils.writeExcelList(sheetList,String.join(File.separator,filePath ,fileName));