devbox
AI数据结构灵魂
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SpringBoot 2.2.5 整合EasyExcel 2.1.6，附带Excel操作工具类，Excel文件转CSV格式工具类_easyexcel工具类

作者：AI数据结构灵魂 | 2024-02-05 15:33:07

赞

踩

easyexcel工具类

说明

  1. EasyExcel重写了poi对07版Excel的解析，降低了内存消耗，对模型转换进行了封装，然后写了下简单案例。
  2. 完整代码地址在结尾！！

第一步，导入maven依赖

<!-- 阿里开源框架EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>
<!-- 将excel转成csv格式的poi依赖 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

第二步，创建模型解析监听器，以下提供两种方式

第一种，创建模型解析监听器类，ModelExcelListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;
/**
 * @Description: 模型解析监听器 -- 每解析一行会回调invoke()方法，整个excel解析结束会执行doAfterAllAnalysed()方法
 * @Author: jinhaoxun
 * @Date: 2020/1/14 15:51
 * @Version: 1.0.0
 */
public class ModelExcelListener<E> extends AnalysisEventListener<E> {

    private List<E> dataList = new ArrayList<E>();

    @Override
    public void invoke(E object, AnalysisContext context) {
        dataList.add(object);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }

    public List<E> getDataList() {
        return dataList;
    }

    public void setDataList(List<E> dataList) {
        this.dataList = dataList;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32

第二种，创建StringList解析监听器类，StringExcelListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

/**
 * @Description: StringList 解析监听器
 * @Author: jinhaoxun
 * @Date: 2020/1/15 11:31
 * @Version: 1.0.0
 */
public class StringExcelListener extends AnalysisEventListener {
    /**
     * 自定义用于暂时存储data
     * 可以通过实例获取该值
     */
    private List<List<String>> datas = new ArrayList<List<String>>();

    /**
     * 每解析一行都会回调invoke()方法
     *
     * @param object
     * @param context
     */
    @Override
    public void invoke(Object object, AnalysisContext context) {
        List<String> stringList= (List<String>) object;
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        datas.add(stringList);
        //根据自己业务做处理
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
        //注意不要调用datas.clear(),否则getDatas为null
    }

    public List<List<String>> getDatas() {
        return datas;
    }

    public void setDatas(List<List<String>> datas) {
        this.datas = datas;
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48

第三步，分别创建三个模型类，ExcelModel，ExcelModel1，ExcelModel2

ExcelModel

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * @Description: 生成excel文件数据字段模板
 * @Author: jinhaoxun
 * @Date: 2020/1/14 15:45
 * @Version: 1.0.0
 */
@Data
public class ExcelModel extends BaseRowModel {

    public ExcelModel(){
    }

    public ExcelModel(String dateJuly, String onDuty, String offDuty, String overtime, String last){
        this.dateJuly = dateJuly;
        this.onDuty = onDuty;
        this.offDuty = offDuty;
        this.overtime = overtime;
        this.last = last;
    }

    @ExcelProperty(value = "日期", index = 0)
    private String dateJuly;
    @ExcelProperty(value = "上班时间", index = 1)
    private String onDuty;
    @ExcelProperty(value = "下班时间", index = 2)
    private String offDuty;
    @ExcelProperty(value = "加班时长", index = 3)
    private String overtime;
    @ExcelProperty(value = "备注", index = 4)
    private String last;

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

ExcelModel1

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * @Description: 生成excel文件数据字段模板
 * @Author: jinhaoxun
 * @Date: 2020/1/14 15:45
 * @Version: 1.0.0
 */
@Data
public class ExcelModel1 extends BaseRowModel {

    public ExcelModel1(){
    }

    public ExcelModel1(String dateJuly, String onDuty, String offDuty, String overtime, String last){
        this.dateJuly = dateJuly;
        this.onDuty = onDuty;
        this.offDuty = offDuty;
        this.overtime = overtime;
        this.last = last;
    }

    @ExcelProperty(value = "日期", index = 0)
    private String dateJuly;
    @ExcelProperty(value = "上班时间", index = 1)
    private String onDuty;
    @ExcelProperty(value = "下班时间", index = 2)
    private String offDuty;
    @ExcelProperty(value = "加班时长", index = 3)
    private String overtime;
    @ExcelProperty(value = "备注", index = 4)
    private String last;

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

ExcelModel2

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * @Description: 生成excel文件数据字段模板
 * @Author: jinhaoxun
 * @Date: 2020/1/14 15:45
 * @Version: 1.0.0
 */
@Data
public class ExcelModel2 extends BaseRowModel {

    public ExcelModel2(){
    }

    public ExcelModel2(String dateJuly, String onDuty, String offDuty, String overtime, String last){
        this.dateJuly = dateJuly;
        this.onDuty = onDuty;
        this.offDuty = offDuty;
        this.overtime = overtime;
        this.last = last;
    }

    @ExcelProperty(value = "日期", index = 0)
    private String dateJuly;
    @ExcelProperty(value = "上班时间", index = 1)
    private String onDuty;
    @ExcelProperty(value = "下班时间", index = 2)
    private String offDuty;
    @ExcelProperty(value = "加班时长", index = 3)
    private String overtime;
    @ExcelProperty(value = "备注", index = 4)
    private String last;

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

第四步，创建工具类，DataConvertUtil，ExcelUtil，ExcelConvertCsvUtil

DataConvertUtil

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * @Description:
 * @Author: jinhaoxun
 * @Date: 2020/4/14 下午5:26
 * @Version: 1.0.0
 */
public class DataConvertUtil {

    /**
     * @Author: jinhaoxun
     * @Description: 将inputStream转byte[]
     * @param inputStream 输入流
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static byte[] inputStreamTobyte2(InputStream inputStream) throws IOException {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        byte[] buff = new byte[100];
        int rc = 0;
        while ((rc = inputStream.read(buff, 0, 100)) > 0) {
            byteArrayOutputStream.write(buff, 0, rc);
        }
        return byteArrayOutputStream.toByteArray();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 将byte[]转inputStream
     * @param bytes byte数组
     * @Date: 2020/1/16 21:43
     * @Return: InputStream
     * @Throws: Exception
     */
    public static InputStream byte2ToInputStream(byte[] bytes) {
        return new ByteArrayInputStream(bytes);
    }

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

ExcelUtil

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.jinhaoxun.easyexcel.listener.ModelExcelListener;
import com.jinhaoxun.easyexcel.listener.StringExcelListener;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @Description: excel导入导出工具类
 * 1.支持按行导入字符串方式
 * 2.支持导入实体类映射
 * 3.支持按行导出字符串方式
 * 4.支持导出实体类映射
 * @Author: jinhaoxun
 * @Date: 2020/1/15 11:20
 * @Version: 1.0.0
 */
public class ExcelUtil {

    /**
     * @Author: jinhaoxun
     * @Description: 使用 StringList 来读取Excel
     * @param inputStream Excel的输入流
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:40
     * @Return: java.util.List<java.util.List<java.lang.String>>
     * @Throws: Exception
     */
    public static List<List<String>> readExcel(InputStream inputStream, ExcelTypeEnum excelTypeEnum) throws Exception{
        StringExcelListener listener = new StringExcelListener();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        excelReader.read();
        return listener.getDatas();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用模型来读取Excel
     * @param inputStream Excel的输入流
     * @param clazz 模型的类
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:41
     * @Return: java.util.List<E>
     * @Throws: Exception
     */
    public static <E> List<E> readExcel(InputStream inputStream, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
        // 解析每行结果在listener中处理
        ModelExcelListener<E> listener = new ModelExcelListener<E>();
        ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        //默认只有一列表头
        excelReader.read(new Sheet(1, 1, clazz));
        return listener.getDataList();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用StringList来写入Excel，单sheet，单table
     * @param outputStream Excel的输出流
     * @param data 要写入的以StringList为单位的数据
     * @param table 配置Excel的表的属性
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:42
     * @Return: void
     * @Throws: Exception
     */
    public static void writeExcel(OutputStream outputStream, List<List<String>> data, Table table, ExcelTypeEnum excelTypeEnum) throws Exception {
        /**
         * @Author: jinhaoxun
         * @Description:
         * @param outputStream
         * @param data
         * @param table
         * @param excelTypeEnum
         * @Date: 2020/1/16 21:42
         * @Return: void
         * @Throws:
         */
        //这里指定不需要表头，因为String通常表头已被包含在data里
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,false);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系,无表头
        Sheet sheet1 = new Sheet(0, 0);
        writer.write0(data, sheet1,table);
        writer.finish();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用StringList来写入Excel，单sheet，单table（返回byte数组）
     * @param outputStream Excel的输出流
     * @param data 要写入的以StringList为单位的数据
     * @param table 配置Excel的表的属性
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static byte[] writeExcel(ByteArrayOutputStream outputStream, List<List<String>> data, Table table, ExcelTypeEnum excelTypeEnum) throws Exception {
        //这里指定不需要表头，因为String通常表头已被包含在data里
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,false);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系,无表头
        Sheet sheet1 = new Sheet(0, 0);
        writer.write0(data, sheet1,table);
        writer.finish();
        return outputStream.toByteArray();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用模型来写入Excel，单sheet，单table
     * @param outputStream Excel的输出流
     * @param data 要写入的以 模型 为单位的数据
     * @param clazz 模型的类
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static void writeExcel(OutputStream outputStream, List<? extends BaseRowModel> data,
                                  Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
        //这里指定需要表头，因为model通常包含表头信息
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
        Sheet sheet1 = new Sheet(1, 0, clazz);
        writer.write(data, sheet1);
        writer.finish();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用模型来写入Excel，单sheet，单table（返回字节数组）
     * @param outputStream Excel的输出流
     * @param data 要写入的以 模型 为单位的数据
     * @param clazz 模型的类
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static byte[] writeExcel(ByteArrayOutputStream outputStream, List<? extends BaseRowModel> data,
                                    Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
        //这里指定需要表头，因为model通常包含表头信息
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
        Sheet sheet1 = new Sheet(1, 0, clazz);
        writer.write(data, sheet1);
        writer.finish();
        return outputStream.toByteArray();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用模型来写入Excel，多sheet，单table （返回字节数组）
     * @param outputStream Excel的输出流
     * @param sheetName  sheet名集合
     * @param datas  要写入的以 模型 为单位的数据
     * @param clazzs  模型的类
     * @param excelTypeEnum  Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static byte[] writeExcel(ByteArrayOutputStream outputStream,List<String> sheetName,List<List<? extends BaseRowModel>> datas,
                                    List<Class<? extends BaseRowModel>> clazzs, ExcelTypeEnum excelTypeEnum) throws Exception {
        //这里指定需要表头，因为model通常包含表头信息
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);
        if (sheetName.size()!=datas.size()||datas.size()!=clazzs.size()){
            throw new ArrayIndexOutOfBoundsException();
        }
        int i = 0;
        //写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
        for (String name:sheetName){
            Sheet sheet1 = new Sheet(1, 0, clazzs.get(i));
            sheet1.setSheetName(name);
            writer.write(datas.get(i), sheet1);
        }
        writer.finish();
        return outputStream.toByteArray();
    }

    /**
     * @Author: jinhaoxun
     * @Description: 使用模型来写入Excel，多sheet，多table
     * @param outputStream  Excel的输出流
     * @param sheetAndTable sheet和table名，格式：<sheet名，<table名集合>>
     * @param data   <sheet名，<table名，table数据集>>
     * @param clazz  <sheet名，<table名，table数据集实体class类型>>
     * @param excelTypeEnum Excel的格式(XLS或XLSX)
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static byte[] writeExcel(ByteArrayOutputStream outputStream,Map<String,List<String>> sheetAndTable,
                                    Map<String,Map<String,List<? extends BaseRowModel>>> data,Map<String,Map<String,Class<? extends BaseRowModel>>> clazz,
                                    ExcelTypeEnum excelTypeEnum) throws Exception {

        //这里指定需要表头，因为model通常包含表头信息
        ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum,true);

        Iterator<Map.Entry<String, List<String>>> iterator = sheetAndTable.entrySet().iterator();
        int sheetNo = 1;
        //遍历sheet
        while (iterator.hasNext()){
            Map.Entry<String, List<String>> next = iterator.next();
            //当前sheet名
            String sheetName = next.getKey();
            //当前sheet对应的table的实体类class对象集合
            Map<String, Class<? extends BaseRowModel>> tableClasses = clazz.get(sheetName);
            //当前sheet对应的table的数据集合
            Map<String, List<? extends BaseRowModel>> dataListMaps = data.get(sheetName);
            Sheet sheet = new Sheet(sheetNo, 0);
            sheet.setSheetName(sheetName);
            int tableNo = 1;
            Iterator<Map.Entry<String, Class<? extends BaseRowModel>>> iterator1 = tableClasses.entrySet().iterator();
            //遍历table
            while (iterator1.hasNext()){
                Map.Entry<String, Class<? extends BaseRowModel>> next1 = iterator1.next();
                //当前table名
                String tableName = next1.getKey();
                //当前table对应的class
                Class<? extends BaseRowModel> tableClass = next1.getValue();
                //当前table对应的数据集
                List<? extends BaseRowModel> tableData = dataListMaps.get(tableName);
                Table table = new Table(tableNo);
                table.setClazz(tableClass);
                writer.write(tableData, sheet, table);
                tableNo++;
            }
            sheetNo++;
        }
        writer.finish();
        return outputStream.toByteArray();
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242

ExcelConvertCsvUtil

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description: excel文件转成csv格式
 * @Author: jinhaoxun
 * @Date: 2020/4/14 上午10:07
 * @Version: 1.0.0
 */
public class ExcelConvertCsvUtil {

    /**
     * @Author: jinhaoxun
     * @Description: 将excel字节码数组转成csv字节码数组
     * @param bytes excel字节码数组
     * @Date: 2020/1/16 21:43
     * @Return: byte[]
     * @Throws: Exception
     */
    public static byte[] convertExcelToCsv(byte[] bytes) throws Exception {
        InputStream inputStream = new ByteArrayInputStream(bytes);
        Workbook wb = WorkbookFactory.create(inputStream);

        String buffer = "";
        Sheet sheet = null;
        Row row = null;
        List<Map<String,String>> list = null;
        String cellData = null;

        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 0; i<rownum; i++) {
                row = sheet.getRow(i);
                for (int j = 0; j < colnum; j++) {
                    cellData = (String) getCellFormatValue(row.getCell(j));
                    buffer +=cellData;
                }
                buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
                buffer += "\n";
            }

            return buffer.getBytes();
        }
        return null;
    }

    /**
     * @Author: jinhaoxun
     * @Description: 读取excel
     * @param filePath 本地文件路径
     * @Date: 2020/1/16 21:43
     * @Return: Workbook
     * @Throws: Exception
     */
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * @Author: jinhaoxun
     * @Description:
     * @param cell
     * @Date: 2020/1/16 21:43
     * @Return: Workbook
     * @Throws: Exception
     */
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";
                    break;
                }
                case FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = String.valueOf(cell.getDateCellValue()).replaceAll("\n", " ") + ",";;
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";;
                    }
                    break;
                }
                case STRING:{
                    cellValue = cell.getRichStringCellValue().getString().replaceAll("\n", " ") + ",";;
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134

第五步，编写单元测试类，EasyexcelApplicationTests，并进行测试，使用方法基本都有注释

import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.jinhaoxun.easyexcel.entity.ExcelModel;
import com.jinhaoxun.easyexcel.entity.ExcelModel1;
import com.jinhaoxun.easyexcel.entity.ExcelModel2;
import com.jinhaoxun.easyexcel.util.DataConvertUtil;
import com.jinhaoxun.easyexcel.util.ExcelConvertCsvUtil;
import com.jinhaoxun.easyexcel.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
// 获取启动类，加载配置，确定装载 Spring 程序的装载方法，它回去寻找 主配置启动类（被 @SpringBootApplication 注解的）
@SpringBootTest
class EasyexcelApplicationTests {

    @Test
    void readExcelTest() throws Exception {
        //读取excel
        File file = new File("E:\\2.xlsx");
        InputStream inputStream = new FileInputStream(file);
        //导入excle
        List<ExcelModel> datas = ExcelUtil.readExcel(inputStream, ExcelModel.class, ExcelTypeEnum.XLSX);
        log.info(datas.toString());
    }

    @Test
    void writeExcelTest() throws Exception {
        //单sheet,单table导出测试
        List<ExcelModel> excelModelList = new ArrayList<ExcelModel>();
        for (int i = 0;i<5;i++){
            ExcelModel excelModel = new ExcelModel("日期"+i, "上班时间" + i,
                    "下班时间" + i, "加班时长" + i, "备注" + i);
            excelModelList.add(excelModel);
        }
        File file1 = new File("E:\\2.xlsx");
        ByteArrayOutputStream outputStream1 = new ByteArrayOutputStream();
        byte[] bytes = ExcelUtil.writeExcel(outputStream1, excelModelList, ExcelModel.class, ExcelTypeEnum.XLSX);
        FileOutputStream outputStream = new FileOutputStream(file1);
        outputStream.write(bytes);
    }

    @Test
    void writeExcelTest1() throws Exception {
        //多sheet，多table导出测试，数据集制作
        List<ExcelModel> excelModelList = new ArrayList<ExcelModel>();
        List<ExcelModel1> excelModel1List = new ArrayList<ExcelModel1>();
        List<ExcelModel2> excelModel2List = new ArrayList<ExcelModel2>();
        for (int i = 0;i<5;i++){
            ExcelModel excelModel = new ExcelModel("日期"+i, "上班时间" + i,
                    "下班时间" + i, "加班时长" + i, "备注" + i);
            ExcelModel1 excelModel1 = new ExcelModel1("日期"+i, "上班时间" + i,
                    "下班时间" + i, "加班时长" + i, "备注" + i);
            ExcelModel2 excelModel2 = new ExcelModel2("日期"+i, "上班时间" + i,
                    "下班时间" + i, "加班时长" + i, "备注" + i);

            excelModelList.add(excelModel);
            excelModel1List.add(excelModel1);
            excelModel2List.add(excelModel2);
        }
        Map<String,List<String>> sheetAndTable = new HashMap<String, List<String>>();
        //构造第一个sheet，此sheet内有两个table
        List<String> sheet1tableNames = new ArrayList();
        sheet1tableNames.add("表一");
        sheet1tableNames.add("表二");
        //构造第二个sheet，此sheet内有一个table
        List<String> Sheet2tableNames = new ArrayList<String>();
        Sheet2tableNames.add("表三");
        sheetAndTable.put("sheet1",sheet1tableNames);
        sheetAndTable.put("sheet2",Sheet2tableNames);

        Map<String,Map<String,Class<? extends BaseRowModel>>> clazz = new HashMap<String, Map<String, Class<? extends BaseRowModel>>>();
        //第一个sheet
        Map<String,Class<? extends BaseRowModel>> tables = new HashMap<String, Class<? extends BaseRowModel>>();
        tables.put("表一",ExcelModel.class);
        tables.put("表二",ExcelModel1.class);
        clazz.put("sheet1",tables);
        Map<String,Class<? extends BaseRowModel>> tables1 = new HashMap<String, Class<? extends BaseRowModel>>();
        tables1.put("表三",ExcelModel2.class);
        clazz.put("sheet2",tables1);

        Map<String,Map<String,List<? extends BaseRowModel>>> data= new HashMap<String, Map<String, List<? extends BaseRowModel>>>();
        //第一个sheet
        Map<String,List<? extends BaseRowModel>> map1 = new HashMap<String, List<? extends BaseRowModel>>();
        map1.put("表一",excelModelList);
        map1.put("表二",excelModel1List);
        data.put("sheet1",map1);
        Map<String,List<? extends BaseRowModel>> map2 = new HashMap<String, List<? extends BaseRowModel>>();
        map2.put("表三",excelModel2List);
        data.put("sheet2",map2);

        File file1 = new File("E:\\3.xlsx");
        ByteArrayOutputStream outputStream1 = new ByteArrayOutputStream();
        byte[] bytes = ExcelUtil.writeExcel(outputStream1,sheetAndTable,data,clazz,ExcelTypeEnum.XLSX);
        FileOutputStream outputStream = new FileOutputStream(file1);
        outputStream.write(bytes);
    }

    @Test
    void convertExcelToCsvTest() throws Exception {
        //读取excel
        File file = new File("/Users/ao/Desktop/activitycode_20200414102350.xlsx");
        InputStream inputStream = new FileInputStream(file);
        byte[] bytes = DataConvertUtil.inputStreamTobyte2(inputStream);
        ExcelConvertCsvUtil.convertExcelToCsv(bytes);
    }

    @BeforeEach
    void testBefore(){
        log.info("测试开始!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
    }

    @AfterEach
    void testAfter(){
        log.info("测试结束!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
    }

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127

完整代码地址：https://github.com/Jinhx128/springboot-demo

注：此工程包含多个module，本文所用代码均在easyexcel-demo模块下

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AI数据结构灵魂/article/detail/61079
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号