easyExcel的读写

读Excel的两种方式:

方法一:同步读

• 创建文件流，并读取文件到dataList

InputStream fileStream = new FileInputStream(localFilePath);//文件流
List<Object> dataList = EasyExcelFactory.read(fileStream,new Sheet(1,1,Table.class));//读取文件流
System.out.println(dataList);//读取结果

• Table.class需要继承BaseRowModel并设置在表格中的index

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

@Data
public class Table extends BaseRowModel implements Serializable {
    private static final long serialVersionUID = -7601964684105378541L;
    /**
     * 英文名
     */
    @ExcelProperty(value = "英文名",index = 0)
    private String eName;

    /**
     * 中文名
     */
    @ExcelProperty(value = "中文名",index = 1)
    private String zName;

}

方法二:异步读

• 异步读取方法

//异步读取加解析插入数据库
EasyExcel.read(localFilePath, Table.class,new Listener(parmas,service)).sheet().doRead();

• 监听类Listener

  import com.alibaba.excel.context.AnalysisContext;
  import com.alibaba.excel.event.AnalysisEventListener;
  import Table;
  import Service;
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Map;
  
  public class Listener extends AnalysisEventListener<Table> {
      private Service service;
      private static final Logger LOGGER = LoggerFactory.getLogger(Listener.class);
      private List<Table> table = new ArrayList<>();
  
  
      public TableInfoListener(Map parmas,Service service) {
          this.service = service;
      }
      @Override
      public void invoke(Table data, AnalysisContext context) {
           //对data进行处理
      }
  
      @Override
      //最后执行一次+
      public void doAfterAllAnalysed(AnalysisContext context) {
          saveData();
          LOGGER.info("所有数据解析完成！");
      }
  
      /**
       * 加上存储
       */
      private void saveData() {
          System.out.println("{ "+ tableInfos.size() +" }条数据，开始存储数据库！" + tableInfos.size());
          service.save(table);
      }

  Excel写并提供下载:

• 后台入口类

    @PostMapping(value = "/local/export")
    public void export(@RequestParam("Ids") String Ids, HttpServletResponse response) {
            List<Table> data = Service.getTable(Ids);
        try {
            String fileName = new String(("EXCEL导出").getBytes(),"UTF-8");
            String sheetName = "export";
            ExcelUtil.writeExcel(response, data, fileName, sheetName, Table.class);
        } catch (Exception e) {
            RetInfo.error().put("message","下载失败");
        }
    }

• ExcelUtil类

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {

    public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
            throws Exception{
        try{
            fileName = URLEncoder.encode(fileName,"utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e){
            throw new Exception("导出文件失败！");
        }
    }

    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
                                  String sheetName, Class clazz) throws Exception {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, clazz);
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
    }

}

• vue前端写法

async exportIt() {
      try {
        let that = this;
        let formData = { Ids: Ids };
        axios({
            method: "post",
            responseType: "blob",
            url: url,
            params: that.$format(formData)
          })
          .then(function(response) {
            let blob = new Blob([response.data], {
              type: "application/ms-excel;charset=UTF-8"
            });
            let fileName = decodeURI(
              response.headers["content-disposition"].split("filename=")[1]
            );
            if ("download" in document.createElement("a")) {
              // 非IE下载
              const elink = document.createElement("a");
              elink.download = fileName;
              elink.style.display = "none";
              elink.href = URL.createObjectURL(blob);
              document.body.appendChild(elink);
              elink.click();
              URL.revokeObjectURL(elink.href); // 释放URL 对象
              document.body.removeChild(elink);
            } else {
              // IE10+下载
              navigator.msSaveBlob(blob, fileName);
            }
            this.$notice("导出成功");
          })
          .catch(function(error) {
            console.log(error);
          });
      } catch (error) {
        console.log(error);
        that.$notice("导出出错");
      }
   }