首页 > 资讯 > 后端开发 > Python >Java利用EasyExcel读取写入Excel详情

183

分享到

Java利用EasyExcel读取写入Excel详情

2024-04-02 19:04:59 183人浏览安东尼

Python 官方文档：入门教程 => 点击学习

摘要

目录Easyexcel介绍为什么使用EasyExcel？封装使用例子EasyExcel介绍 EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具

EasyExcel介绍

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。

为什么使用EasyExcel？

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的api可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。

封装使用

引入EasyExcel依赖

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

Excel文档的自动列宽设置

public class CustomColumnWidthHandler extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private static final int PADDING_WIDTH = 6;
 
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        if (isHead) {
            int columnWidth = cell.getStrinGCellValue().length() * 2 + PADDING_WIDTH;
            columnWidth = Math.min(columnWidth, MAX_COLUMN_WIDTH);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
 
}

消费监听器：

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
 
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;
 
public class EasyExcelConsumerListener<T> extends AnalysisEventListener<T> {
    private int pageSize;
    private List<T> list;
    private Consumer<List<T>> consumer;
 
    public EasyExcelConsumerListener(int pageSize, Consumer<List<T>> consumer) {
        this.pageSize = pageSize;
        this.consumer = consumer;
        list = new ArrayList<>(pageSize);
    }
 
    @Override
    public void invoke(T data, AnalysisContext context) {
        list.add(data);
        if (list.size() >= pageSize) {
            consumer.accept(list);
            list.clear();
        }
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        consumer.accept(list);
    }
 
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        exception.printStackTrace();
        throw exception;
    }
}

ExcelSheet

public class ExcelSheet<T> {
    private String sheetName;
    private T clazz;
    private List<T> data;
    public ExcelSheet() {
    }
    public ExcelSheet(String sheetName, T clazz, List<T> data) {
        this.sheetName = sheetName;
        this.clazz = clazz;
        this.data = data;
    }
    public String getSheetName() {
        return sheetName;
    }
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }
    public T getClazz() {
        return clazz;
    }
    public void setClazz(T clazz) {
        this.clazz = clazz;
    }
    public List<T> getData() {
        return data;
    }
 
    public void setData(List<T> data) {
        this.data = data;
    }
 
    @Override
    public String toString() {
        return "CreateExcelSheet{" +
                "sheetName='" + sheetName + '\'' +
                ", clazz=" + clazz +
                ", data=" + data +
                '}';
    }
}

LocalDateConverter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import java.text.SimpleDateFORMat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.GreGorianCalendar;
public class LocalDateConverter implements Converter<LocalDate> {
 
    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }
 
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    @Override
    public LocalDate convertToJavaData(ReadConverterContext<?> context) {
        Calendar calendar = new GregorianCalendar(1900, 0, -1);
        Date gregorianDate = calendar.getTime();
        return LocalDate.parse((new SimpleDateFormat("yyyy-MM-dd")).format(
                aDDDay(gregorianDate, context.getReadCellData().getNumberValue().intValue())),
                DateTimeFormatter.ofPattern("yyyy-MM-dd")
        );
    }
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<LocalDate> context) {
        return new WriteCellData<>(context.getValue().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }
    public static Date addDay(Date date, int day) {
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(date);
        calendar.add(5, day);
        date = calendar.getTime();
        return date;
    }
}

LocalDateTimeConverter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
 
    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class;
    }
 
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
 
    @Override
    public LocalDateTime convertToJavaData(ReadConverterContext<?> context) {
        return LocalDateTime.parse(context.getReadCellData().getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<LocalDateTime> context) {
        return new WriteCellData<>(context.getValue().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
}

ByteArrayConverter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import java.NIO.charset.StandardCharsets;
 
public class ByteArrayConverter implements Converter<byte[]> {
    public ByteArrayConverter() {
    }
 
    @Override
    public Class<byte[]> supportJavaTypeKey() {
        return byte[].class;
    }
 
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
 
    @Override
    public byte[] convertToJavaData(ReadConverterContext<?> context) {
        String stringValue = context.getReadCellData().getStringValue();
        return stringValue.getBytes(StandardCharsets.UTF_8);
    }
 
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<byte[]> context) {
        return new WriteCellData((byte[])context.getValue());
    }
}

EasyExcel工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
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.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import javax.servlet.Http.httpservletResponse;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
 
public class ExcelUtil extends EasyExcel {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
 
    private ExcelUtil() {}
 
    
    public static <T> ExcelReaderBuilder read(String pathName, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
        return read(pathName, head, new EasyExcelConsumerListener<>(pageSize, consumer));
    }
 
    
    public static <T> ExcelReaderBuilder read(File file, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
        return read(file, head, new EasyExcelConsumerListener<>(pageSize, consumer));
    }
 
    
    public static <T> ExcelReaderBuilder read(InputStream inputStream, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
        return read(inputStream, head, new EasyExcelConsumerListener<>(pageSize, consumer));
    }
 
    
    public static ExcelWriterBuilder write(String pathName, Class head) {
        return EasyExcel.write(pathName, head)
                .excelType(ExcelTypeEnum.XLSX)
                .reGISterWriteHandler(buildCellStyle())
                .registerWriteHandler(new CustomColumnWidthHandler());
    }
 
    
    public static void write(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class clazz) throws Exception {
        EasyExcel.write(getOutputStream(fileName, response), clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(buildCellStyle())
                .registerWriteHandler(new CustomColumnWidthHandler())
                .doWrite(data);
    }
 
    
    public static void write(OutputStream outputStream, List<?> data, String sheetName, Class clazz) {
        EasyExcel.write(outputStream, clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(buildCellStyle())
                .registerWriteHandler(new CustomColumnWidthHandler())
                .doWrite(data);
    }
 
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        return response.getOutputStream();
    }
 
    
    public static Map<String, String> getHeadMap(Class<?> aClass) {
        Map<String, String> HeadMap = new LinkedHashMap<>();
        Field[] declaredFields = aClass.getDeclaredFields();
        ExcelProperty excelProperty;
        for (Field field : declaredFields) {
            if (field != null) {
                field.setAccessible(true);
                if (field.isAnnotationPresent(ExcelProperty.class)) {
                    excelProperty = field.getAnnotation(ExcelProperty.class);
                    HeadMap.put(field.getName(), StringUtils.join(Arrays.asList(excelProperty.value()), ","));
                }
            }
        }
        return HeadMap;
    }
 
    
    public static HorizontalCellStyleStrategy buildCellStyle(){
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        WriteFont font = new WriteFont();
        font.setFontName("Microsoft YaHei Light");
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontHeightInPoints((short) 11);
        headWriteCellStyle.setWriteFont(font);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
 
    
    public static void createExcel(List<ExcelSheet> sheetList, OutputStream os){
        ExcelWriter excelWriter = null;
        WriteSheet writeSheet = null;
        int count = 0;
        try {
            excelWriter = EasyExcel.write(os)
                    .registerWriteHandler(buildCellStyle())
                    .registerWriteHandler(new CustomColumnWidthHandler())
                    .build();
 
            for (ExcelSheet sheet : sheetList) {
                writeSheet = EasyExcel.writerSheet(count++, sheet.getSheetName()).head((Class) sheet.getClazz()).build();
                excelWriter.write(sheet.getData(),writeSheet);
            }
        } catch (Exception e) {
            LOGGER.error("创建一个Excel文件多个Sheet失败", e);
        }finally {
            if (null != excelWriter){
                excelWriter.finish();
            }
        }
    }
}

例子

UserVo实体

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.time.LocalDate;
@Data
public class UserVo {
 
    @ExcelProperty(value = "姓名")
    private String name;
 
    @ExcelProperty(value = "年龄")
    private int age;
 
    @ExcelProperty(value = "出生日期", converter = LocalDateConverter.class)
    private LocalDate birthdate;
 
}

导出用户信息

 ExcelUtil.write(httpServletResponse, list, "用户信息.xlsx", "用户信息", UserVo.class);

读取用户信息

 ExcelUtil.read(filePath, UserVo.class, 1000, pageList -> {
    pageList.forEach(user -> {
        // 业务逻辑
    });
}).sheet().doRead();

到此这篇关于Java利用EasyExcel读取写入Excel详情的文章就介绍到这了,更多相关Java 读取Excel内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:

--结束END--

本文标题: Java利用EasyExcel读取写入Excel详情

本文链接: https://lsjlt.com/news/168303.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

Java利用EasyExcel读取写入Excel详情

目录

EasyExcel介绍

为什么使用EasyExcel？

封装使用

例子

Java利用EasyExcel读取写入Excel详情

Python利用pdfplumber实现读取PDF写入Excel

Java利用poi读取Excel详解实现

Python实例详解pdfplumber读取PDF写入Excel

java使用EasyExcel导入导出excel

在python中读取和写入CSV文件详情

使用java解析和读取excel表格（EasyExcel的简单使用）

Java简单使用EasyExcel操作读写excel的步骤与要点

python怎么读取pdf并写入excel

利用Python实现读取Word表格计算汇总并写入Excel

java如何使用EasyExcel导入导出excel

python实现读取excel写入mysql的小工具详解

详解Python如何实现Excel数据读取和写入

Python 操作pdf pdfplumber读取PDF写入Excel

Python如何读取和写入Excel数据

Python中怎么使用pdfplumber读取PDF写入Excel

Java利用FileUtils读取数据和写入数据到文件

怎么利用Python实现读取Word表格计算汇总并写入Excel

Java利用Easyexcel导出excel表格的示例代码

python读写excel利器：xlwings从入门到精通

python分析数据的方法是什么

如何使用Python实现抽奖小程序

python copy函数的作用是什么

python ffmpeg模块怎么安装和使用

python进程池创建队列的方法是什么

python无法运行文件的原因有哪些

python can't open file报错怎么解决

python keyerror错误怎么解决

python字符串处理与应用的方法有哪些

python全局变量如何定义