EsayExcle的简单使用

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>1.1.2-beta5</version>
</dependency>复制代码

public class OrderExcelBO extends BaseRowModel {
 
    @ExcelProperty(value = {"订单ID"}, index = 0)
    private String id;
 
    /**
     * 订单描述
     */
    @ExcelProperty(value = {"订单描述"}, index = 2)
    private String description;
 
    /**
     * 订单对应产品id
     */
    @ExcelProperty(value = {"产品ID"}, index = 2)
    private Integer productId;
 
    /**
     * 支付方式描述，如：apple pay
     */
    @ExcelProperty(value = {"支付方式"}, index = 3)
    private String payMethod;
 
    /**
     * create_time
     */
    @ExcelProperty(value = {"时间"}, index = 4)
    private String createTime;
 
    /**
     * update_time
     */
    private String updateTime;
 
    /**
     * 产生订单的用户
     */
    @ExcelProperty(value = {"用户ID"}, index = 5)
    private Integer userId;
 
    /**
     * 支付状态：0 未支付、1支付成功支付完成、-1支付失败
     */
    @ExcelProperty(value = {"支付状态"}, index = 6)
    private String status;
 
    /**
     * 订单来源描述，如：ios 安卓
     */
    @ExcelProperty(value = {"手机型号"}, index = 7)
    private String platform;
 
    /**
     * 订单流水
     */
    @ExcelProperty(value = {"订单流水号"}, index = 8)
    private String flowNum;
 
    /**
     * 订单金额
     */
    @ExcelProperty(value = {"金额"}, index = 9)
    private BigDecimal price;
 
    // @ExcelProperty(value = {"收据字段"}, index = 10)
    private String receipt;
 
    @ExcelProperty(value = {"APP来源"}, index = 10)
    private String sources;
}

public class ExcelListener extends AnalysisEventListener {
 
   /**
    * 自定义用于暂时存储data。
    * 可以通过实例获取该值
    */
   private List<Object> datas = new ArrayList<>();
 
   /**
    * 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
    */
   @Override
   public void invoke(Object object, AnalysisContext context) {
      //数据存储到list，供批量处理，或后续自己业务逻辑处理。
      datas.add(object);
      //根据业务自行 do something
      doSomething();
        /*
        如数据过大，可以进行定量分批处理
        if(datas.size()<=100){
            datas.add(object);
        }else {
            doSomething();
            datas = new ArrayList<Object>();
        }
         */
   }
 
   /**
    * 根据业务自行实现该方法
    */
   private void doSomething() {
   }
 
   @Override
   public void doAfterAllAnalysed(AnalysisContext context) {
        /*
            datas.clear();
            解析结束销毁不用的资源
         */
   }
 
   public List<Object> getDatas() {
      return datas;
   }
 
   public void setDatas(List<Object> datas) {
      this.datas = datas;
   }
}

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.mochu.exception.ExcelException;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;
 
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
 
public class ExcelUtil {
    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel 文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
 
        if (reader == null) {
            return null;
        }
 
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            reader.read(sheet);
        }
 
        return excelListener.getDatas();
    }
 
    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel 文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
        return readExcel(excel, rowModel, sheetNo, 1);
    }
 
    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel 文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
 
        if (reader == null) {
            return null;
        }
 
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
 
        return excelListener.getDatas();
    }
 
    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list 数据 list，每个元素为一个 BaseRowModel
     * @param fileName 导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object 映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
                                  String sheetName, BaseRowModel object) {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
 
        TableStyle tableStyle = new TableStyle();
        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setFontHeightInPoints((short) 9);
        tableStyle.setTableHeadFont(font);
        tableStyle.setTableContentFont(font);
        sheet.setTableStyle(tableStyle);
 
        writer.write(list, sheet);
        writer.finish();
    }
 
    /**
     * 导出 Excel ：多个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list 数据 list，每个元素为一个 BaseRowModel
     * @param fileName 导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object 映射实体类，Excel 模型
     */
    public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response,
                                                          List<? extends BaseRowModel> list, String fileName,
                                                          String sheetName, BaseRowModel object) {
        ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        sheet.setTableStyle(getTableStyle());
        writer.write(list, sheet);
 
        return writer;
    }
 
    /**
     * 导出融资还款情况表
     *
     * @param response
     * @param list
     * @param fileName
     * @param sheetName
     * @param object
     */
    public static void writeFinanceRepayment(HttpServletResponse response, List<? extends BaseRowModel> list,
                                             String fileName, String sheetName, BaseRowModel object) {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        sheet.setTableStyle(getTableStyle());
        writer.write(list, sheet);
 
        for (int i = 1; i <= list.size(); i += 4) {
            writer.merge(i, i + 3, 0, 0);
            writer.merge(i, i + 3, 1, 1);
        }
 
        writer.finish();
    }
 
    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        //创建本地文件
        fileName = fileName + ".xls";
 
        try {
            fileName = new String(fileName.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
 
            return response.getOutputStream();
        } catch (Exception e) {
 
            throw new ExcelException("导出异常！");
        }
    }
 
    /**
     * 返回 ExcelReader
     *
     * @param excel 需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
 
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new ExcelException("文件格式错误！");
        }
        InputStream inputStream;
 
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
 
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }
 
        return null;
    }
 
    /**
     * 资金收支导出 Excel ：一个 sheet，带表头
     *
     * @param response HttpServletResponse
     * @param list 数据 list，每个元素为一个 BaseRowModel
     * @param fileName 导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object 映射实体类，Excel 模型
     */
    public static void exportFundBudgetExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                                             String fileName, String sheetName, BaseRowModel object) throws IOException {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        sheet.setTableStyle(getTableStyle());
 
        writer.write(list, sheet);
        writer.merge(2, 3, 0, 0);
        writer.merge(4, 13, 0, 0);
        writer.merge(14, 14, 0, 1);
        writer.finish();
    }
 
    /**
     * 读取Excel表格数据，封装成实体
     *
     * @param inputStream
     * @param clazz
     * @param sheetNo
     * @param headLineMun
     * @return
     */
    public static Object readExcel(InputStream inputStream, Class<? extends BaseRowModel> clazz, Integer sheetNo,
                                   Integer headLineMun) {
        if (null == inputStream) {
 
            throw new NullPointerException("the inputStream is null!");
        }
 
        ExcelListener listener = new ExcelListener();
        ExcelReader reader = new ExcelReader(inputStream, valueOf(inputStream), null, listener);
        reader.read(new Sheet(sheetNo, headLineMun, clazz));
 
        return listener.getDatas();
    }
 
    /**
     * 根据输入流，判断为xls还是xlsx，该方法原本存在于easyexcel 1.1.0 的ExcelTypeEnum中。
     */
    public static ExcelTypeEnum valueOf(InputStream inputStream) {
        try {
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
 
            if (FileMagic.OLE2.equals(fileMagic)) {
                return ExcelTypeEnum.XLS;
            }
 
            if (FileMagic.OOXML.equals(fileMagic)) {
                return ExcelTypeEnum.XLSX;
            }
 
            throw new ExcelException("excelTypeEnum can not null");
 
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
 
    /**
     * 设置全局样式
     *
     * @return
     */
    private static TableStyle getTableStyle() {
        TableStyle tableStyle = new TableStyle();
 
        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setBold(true);
        font.setFontHeightInPoints((short) 9);
        tableStyle.setTableHeadFont(font);
        Font fontContent = new Font();
        fontContent.setFontHeightInPoints((short) 9);
        tableStyle.setTableContentFont(fontContent);
 
        return tableStyle;
    }
}

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
 
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
 
public class ExcelWriterFactory extends ExcelWriter {
   private OutputStream outputStream;
   private int sheetNo = 1;
 
   public ExcelWriterFactory(OutputStream outputStream, ExcelTypeEnum typeEnum) {
      super(outputStream, typeEnum);
      this.outputStream = outputStream;
   }
 
   public ExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName, BaseRowModel object) {
      this.sheetNo++;
      try {
         Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
         sheet.setSheetName(sheetName);
         this.write(list, sheet);
      }
      catch(Exception ex) {
         ex.printStackTrace();
         try {
            outputStream.flush();
         }
         catch(IOException e) {
            e.printStackTrace();
         }
      }
      return this;
   }
 
   @Override
   public void finish() {
      super.finish();
      try {
         outputStream.flush();
      }
      catch(IOException e) {
         e.printStackTrace();
      }
   }
}复制代码