EasyExcel 读写 Excel

一、导入EasyExcel依赖

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

二、使用步骤

1.创建一个实体类并使用 EasyExcel 提供的注解

@Data
@AllArgsConstructor
@NoArgsConstructor
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER,verticalAlignment = VerticalAlignment.CENTER, shrinkToFit = true)//标题样式,垂直水平居中
@HeadFontStyle(fontName = "微软雅黑",fontHeightInPoints = 11,bold = false)//表头字体样式
@HeadRowHeight(value = 35)//表头行高
@ContentFontStyle(fontName = "微软雅黑",fontHeightInPoints = 11)//内容字体样式
@ContentRowHeight(value = 30)//内容行高
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,verticalAlignment = VerticalAlignment.CENTER, wrapped = true)//内容样式,垂直水平居中
public class ExcelDate  implements Serializable {

    private static final long serialVersionUID = 1L;
 
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelIgnore
    private Long id;
    
    @ExcelProperty(value = "用户姓名")
    String userName;
 
    @ExcelProperty(value = "用户手机号")
    String userPhone;
 
    @ExcelProperty(value = "用户年龄")
    Integer userAge;

}

@ColumnWith 设置列宽 , 作用于成员属性

@ExcelIgnore 不将该字段转换成Excel

@ExcelProperty(“xxx”)指定列名的映射

@HeadFontStyle 定制标题字体格式

@ContentRowHeight 设置行高

@HeadStyle / @ContentStyle 设置标题 / 内容样式

2.创建一个启动类，运行，即可读取

public class EasyExcelImportMain {
 
    public static void main(String[] args) throws Exception {
 
        // 创建一个输入流，将 Excel 文件读取出来
        InputStream inputStream = new FileInputStream("C:\\Users\\admin\\Desktop\\1.xls");
 
        List<ExcelDate> list = EasyExcel.read(inputStream)
                // 设置与Excel表映射的类
                .head(ExcelDate.class)
                // 设置sheet,默认读取第一个
                .sheet()
                // 设置标题所在行数
                .headRowNumber(1)
                // 异步读取
                .doReadSync();
 
 
        for (ExcelDate e: list) {
            System.out.println(e);
        }
    }
}

3.写操作

public class ExcelTest {
    public static void main(String[] args) {
        List<ExcelData> list = parseData();
        //list写入Excel文件
        EasyExcel.write("C:\\Users\\admin\\Desktop\\1.xls")
                .head(ExcelData.class)
                .excelType(ExcelTypeEnum.XLS)
                .sheet("用户数据表")
                .doWrite(list);
    }
 
    public static List<ExcelData> parseData(){
        List<ExcelData> list = new LinkedList<>();
        // 写的数据都封装进list中
        return list;
    }
}

4. easyexcel报错

com.alibaba.excel.exception.ExcelAnalysisException: java.lang.NoClassDefFoundError: org/apache/poi/poifs/filesystem/FileMagic

【解决办法】引包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

详解请到EasyExcel官方文档

链接地址：https://easyexcel.opensource.alibaba.com/

---