SpringBoot整合EasyExcel开源框架

<!-- 阿里开源框架EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>
<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>

package cn.jxd.caixibei.units;
import cn.jxd.caixibei.listener.ModelExcelListener;
import cn.jxd.caixibei.listener.StringExcelListener;
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 org.springframework.stereotype.Component;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
 * excel导入导出工具类<br>
 * 1.支持按行导入字符串方式<br>
 * 2.支持导入实体类映射<br>
 * 3.支持按行导出字符串方式<br>
 * 4.支持导出实体类映射<br>
 * @author caixibei
 */
@Component
public class ExcelUtils {

    /*---------------------------读操作开始--------------------------------*/

    /**
     * 使用 StringList 来读取Excel
     * @param inputStream
     * @param excelTypeEnum
     * @return
     * @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();
    }

    /**
     * 使用模型来读取Excel
     * @param inputStream
     * @param clazz
     * @param excelTypeEnum
     * @param <E>
     * @return
     * @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();
    }
    /*---------------------------读操作结束--------------------------------*/


    /*---------------------------写操作开始--------------------------------*/
    /**
     * 使用StringList来写入Excel，单sheet，单table
     * @param outputStream
     * @param data
     * @param table
     * @param excelTypeEnum
     * @throws Exception
     */
    public static void writeExcel(OutputStream 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();
    }

    /**
     * 使用StringList来写入Excel，单sheet，单table（返回byte数组）
     * @param outputStream
     * @param data
     * @param table
     * @param excelTypeEnum
     * @return
     * @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();
    }

    /**
     * 使用模型来写入Excel，单sheet，单table
     * @param outputStream
     * @param data
     * @param clazz
     * @param excelTypeEnum
     * @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();
    }

    /**
     * 使用模型来写入Excel，单sheet，单table（返回字节数组）
     * @param outputStream
     * @param data
     * @param clazz
     * @param excelTypeEnum
     * @return
     * @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();
    }

    /**
     * 使用模型来写入Excel，多sheet，单table （返回字节数组）
     * @param outputStream
     * @param sheetName
     * @param datas
     * @param clazzs
     * @param excelTypeEnum
     * @return
     * @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();
    }

    /**
     * 使用模型来写入Excel，多sheet，多table
     * @param outputStream
     * @param sheetAndTable
     * @param data
     * @param clazz
     * @param excelTypeEnum
     * @return
     * @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();
    }
    /*---------------------------写操作结束--------------------------------*/
}

package cn.jxd.caixibei.units;

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

/**
 * 数据转化类
 * @author caixibei
 */
public class DataConvertUtil {
    /**
     * 将inputStream转byte[]
     * @param inputStream
     * @return
     * @throws IOException
     */
    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();
    }

    /**
     * 将byte[]转inputStream
     * @param bytes
     * @return
     */
    public static InputStream byte2ToInputStream(byte[] bytes) {
        return new ByteArrayInputStream(bytes);
    }
}

package cn.jxd.caixibei.units;
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;
/**
 * 导入Excel时需要将csv文件做处理
 * @author caixibei
 */
public class ExcelConvertCsvUtil {
    /**
     * 将excel字节码数组转成csv字节码数组
     * @param bytes
     * @return
     * @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;
    }

    /**
     * 读取excel
     * @param filePath
     * @return
     */
    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;
    }

    /**
     * 单元格数据
     * @param cell
     * @return
     */
    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;
    }
}

package cn.jxd.caixibei.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
 * 模型解析监听器 每解析一行会回调invoke()方法，整个excel解析结束会执行doAfterAllAnalysed()方法
 * @author caixibei
 * @param <E>
 */
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;
    }
}

package cn.jxd.caixibei.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
 * StringList 解析监听器
 * @author caixibei
 */
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;
        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;
    }
}

package cn.jxd.caixibei.pojo;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
 * 处分表
 * @author caixibei
 */
@ApiModel("处分表")
@Component
@Data
public class Punishment extends BaseRowModel {

    /**
     * 序号
     */
    @ApiModelProperty("序号")
    @ExcelProperty(value = "序号",index = 0)
    @ColumnWidth(value = 10)
    private Integer id;
    /**
     * 处分等级
     */
    @ApiModelProperty("处分等级")
    @ExcelProperty(value = "处分等级",index = 1)
    @ColumnWidth(value = 12)
    private String punish_level;
    /**
     * 处理人
     */
    @ApiModelProperty("处理人")
    @ExcelProperty(value = "处理人",index = 4)
    @ColumnWidth(value = 18)
    private String punish_agent;
    /**
     * 内容
     */
    @ApiModelProperty("内容")
    @ColumnWidth(value = 64)
    @ExcelProperty(value = "处分理由",index = 3)
    private String punish_content;
    /**
     * 处分时间
     */
    @ApiModelProperty("处分时间")
    @ColumnWidth(value = 18)
    @ExcelProperty(value = "处分时间",index = 2)
    private Date punish_date;

    /**
     * 用户ID
     */
    @ApiModelProperty("用户ID")
    @ExcelIgnore
    private Date user_id;
}

/**
 * 导出处分信息
 * @param response
 * @param session
 * @return
 * @throws IOException
 */
@GetMapping("/exportPunishments")
@ApiOperation(value = "查询处分信息",notes = "查询处分信息")
public void exportPunishments(HttpServletResponse response, HttpSession session) throws Exception {
    Map<String,String> maps = new HashMap<>();
    UserVo userVo = (UserVo)session.getAttribute("user");
    List<Punishment> punishments = null;
    if(userVo!=null){
        maps.put("user_id",String.valueOf(userVo.getId()));
        punishments = indexMapper.queryPunishments(maps);
        String fileName = URLEncoder.encode("处分表("+userVo.getNickName()+").xlsx", "UTF-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        ExcelUtils.writeExcel(response.getOutputStream(),  punishments,Punishment.class, ExcelTypeEnum.XLSX);
    }
}

/**
* 最简单的读
* <p>1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
* <p>3. 直接读即可
*/
@Test
 public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
     // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
     EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

/**
 * 最简单的写
 * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
 * <p>2. 直接写即可
 */
 @Test
 public void simpleWrite() {
    String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
     // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
 }

 /**
 * 文件下载（失败了会返回一个有部分数据的Excel）
 * <p>1. 创建excel对应的实体对象 参照{@link DownloadData}
 * <p>2. 设置返回的 参数
 * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
 */
 @GetMapping("download")
 public void download(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("测试", "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
   EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
 }
 
 /**
 * 文件上传
 * <p>1. 创建excel对应的实体对象 参照{@link UploadData}
 * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
 * <p>3. 直接读即可
 */
 @PostMapping("upload")
 @ResponseBody
 public String upload(MultipartFile file) throws IOException {
  EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
   return "success";
}