easyExcel 简单的web导出与上传

https://easyexcel.opensource.alibaba.com/

导出

一次导出

public void export(GoodsPageReq req, HttpServletResponse response) throws IOException {


    EasyExcelUtils.export(response, GoodsExportBO.class, listGoods(req).stream().filter(HzObjectUtil::isNotEmpty)
            .map(obj -> new GoodsExportBO()
                         .setGoodsStateStr(HzObjectUtil.equals(1, obj.getGoodsState()) ? "上架" : "未上架")
                        .setUpdateTime(obj.getUpdateTime()))
            .collect(Collectors.toList()));

}

GoodsExportBO

package com.hz.shop.entity.goods.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.hz.base.utils.excel.DateConverter;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.experimental.Accessors;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 商品管理信息导出对象
 */
@Data
@ColumnWidth(20)
public class GoodsExportBO {

    @ExcelProperty(value = "商品类型", index = 0)
    private String goodsTypeText;

    @ExcelProperty(value = "商品名称", index = 1)
    private String goodsName;

    @ExcelProperty(value = "数量", index = 2)
    private Integer goodsStock;

    @ExcelProperty(value = "单价", index = 3)
    private BigDecimal goodsPrice;

    @ExcelProperty(value = "是否上架", index = 4)
    private String goodsStateStr;

    @ExcelProperty(value = "更新时间", index = 5, converter = DateConverter.class)
    private Date updateTime;

}

多次导出

public void export(OrderListPoliceReq req, HttpServletResponse response) throws IOException {

      TableDataInfo<ListByOrderVO> tableDataInfo;
      List<ListByOrderVO> rows;
      int pageNum = 1;
      EasyExcelUtils.setResponse(response);
      try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ListByOrderExportBO.class).build()) {
          WriteSheet writeSheet = EasyExcel.writerSheet().build();
          do {
              req.setPageNum(pageNum++);
              req.setPageSize(pageSize);
              tableDataInfo = listByOrderPolice(req);
              rows = tableDataInfo.getRows();

              excelWriter.write(rows.stream().map(obj -> {
                  ListByOrderExportBO bo = new ListByOrderExportBO();
                  return bo;
              }).collect(Collectors.toList()), writeSheet);
          } while (tableDataInfo.getTotal() > 0 && rows.size() == req.getPageSize());
      }
  }

DateConverter

package com.hz.base.utils.excel;


import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.util.Date;

/**
 * easyExcel  java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.setCellValue(Ljava/time/LocalDateTime;)V  时间导出报错处理
 **/
public class DateConverter implements Converter<Date> {

  @Override
  public Class<?> supportJavaTypeKey() {
    return Converter.super.supportJavaTypeKey();
  }

  @Override
  public CellDataTypeEnum supportExcelTypeKey() {
    return Converter.super.supportExcelTypeKey();
  }

  @Override
  public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty,
                         GlobalConfiguration globalConfiguration) {
    return new WriteCellData<>(DateUtil.format(value, DatePattern.NORM_DATETIME_PATTERN));
  }
}

EasyExcelUtils

package com.hz.base.utils.excel;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcelFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Date;
import java.util.List;

public class EasyExcelUtils {

    public static void export(HttpServletResponse response, Class clazz, List<?> list) throws IOException {
        setResponse(response);
        EasyExcelFactory.write(response.getOutputStream()).head(clazz).sheet("Sheet1").doWrite(list);
    }

    public static void setResponse(HttpServletResponse response) {
        response.setContentType("application/ms-excel;charset=UTF-8");
        String fileName = DateUtil.format(new Date(), DatePattern.PURE_DATETIME_MS_PATTERN) + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    }

}

上传

  @PostMapping(value = "import/excel")
  @ApiOperation(value = "上传")
  public Response<Void> importByExcel(@RequestPart("file") MultipartFile file) throws IOException {
      goodsServiceImpl.importByExcel(file);
      return success();
  }

public void importByExcel(MultipartFile file) throws IOException {
      // 匿名内部类 不用额外写一个DemoDataListener
      // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
      EasyExcel.read(file.getInputStream(), GoodsReadBO.class, new ReadListener<GoodsReadBO>() {
          /**
           * 单次缓存的数据量
           */
          public static final int BATCH_COUNT = 100;
          /**
           *临时存储
           */
          private List<GoodsReadBO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

          @Override
          public void invoke(GoodsReadBO data, AnalysisContext context) {
              cachedDataList.add(data);
              if (cachedDataList.size() >= BATCH_COUNT) {
                  saveData();
                  // 存储完成清理 list
                  cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
              }
          }

          @Override
          public void doAfterAllAnalysed(AnalysisContext context) {
              saveData();
          }

          /**
           * 加上存储数据库
           */
          private void saveData() {
              String loginName = CasLoginUtils.getLoginName();
              Date date = new Date();
              Map<String, String> map = RedisUtils.hashEntries(RedisConstants.REDIS_DICT_TYPE + OldConstants.DictDataType.GOODS_TYPE);
              Map<String, String> useMap = new HashMap<>(128);
              if (HzObjectUtil.isNotEmpty(map)) {
                  for (Map.Entry<String, String> entry : map.entrySet()) {
                      useMap.put(entry.getValue(), entry.getKey());
                  }
              }

              goodsMapper.batchInsertByImport(cachedDataList.stream().filter(HzObjectUtil::isNotEmpty)
                      .map(obj -> HzObjectUtil.copy(obj, GoodsP.class)
                              .setGoodsId(IdWorkUtils.nextId())
                              .setGoodsState(2)
                              .setGoodsType(Long.valueOf(useMap.get(obj.getGoodsTypeText())))
                              .setCreateBy(loginName).setCreateTime(date)
                              .setUpdateBy(loginName).setUpdateTime(date)).collect(Collectors.toList()));
          }
      }).sheet().doRead();

  }

GoodsReadBO

package com.hz.shop.entity.goods.excel;

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

import java.math.BigDecimal;

/**
 * 商品管理信息导入对象
 * 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
 */
@Data
public class GoodsReadBO {

    @ExcelProperty(index = 0)
    // @ExcelProperty("商品类型")
    private String goodsTypeText;
    //
    // @ExcelProperty("商品名称")
    @ExcelProperty(index = 1)
    private String goodsName;

    // @ExcelProperty("数量")
    @ExcelProperty(index = 2)
    private Long goodsStock;

    // @ExcelProperty("单价")
    @ExcelProperty(index = 3)
    private BigDecimal goodsPrice;


}

模板下载

    @GetMapping("download-import-template")
    @ApiOperation(value = "下载上传模版")
    public void downloadImportTemplate(HttpServletResponse response) {
        goodsServiceImpl.downloadImportTemplate(response);
    }

    public void downloadImportTemplate(HttpServletResponse response) {
        response.setContentType("application/ms-excel;charset=UTF-8");
        String fileName = DateUtil.format(new Date(), DatePattern.PURE_DATETIME_MS_PATTERN) + ".xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        try (ServletOutputStream outputStream = response.getOutputStream();
             // FileInputStream inputStream = new FileInputStream("D:\\code\\terminal-platform\\hz-shop\\excel-template\\excel-template.xlsx")) {
             FileInputStream inputStream = new FileInputStream("hz-shop/excel-template/excel-template.xlsx")) {
            byte[] bytes = new byte[1024];

            int len;
            while ((len = inputStream.read(bytes)) != -1) {
                outputStream.write(bytes, 0, len);
            }
        } catch (Exception e) {
            log.error("ERROR GoodsServiceImpl.downloadImportTemplate 文件模版下载出错 cause:", e);
        }
    }