vue+SpringBoot的easyExcel导入导出

vue+SpringBoot的easyExcel导入导出

导入依赖：

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.0.1</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
   <version>4.0.1</version>
</dependency>
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.1.6</version>
</dependency>

实体类及注解：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MonitorNoise {
    @ExcelIgnore
    private String id;

    @ExcelProperty(value = "设备名称",index = 0)
    private String name;

    @ExcelProperty(value = "时间",index = 1)
    private String time;

    @ExcelProperty(value = "设备位置",index = 2)
    private String place;
}

1.导入

controller层：

@RestController
@RequestMapping(value = "/import")
public class ImportController {

    @Autowired
    ImportNoiseListener noiseListener;
    
    //噪声监测数据导入
    @RequestMapping("/importNoise")
    public void importNoise(@RequestParam(name = "file",required = true) MultipartFile excl)
    {
        try {
            InputStream inputStream=excl.getInputStream();
            EasyExcel.read(inputStream, noiseListener)
                    // 设置sheet,默认读取第一个
                    .sheet()
                    .head(MonitorNoise.class)
                    // 设置标题所在行数
                    .headRowNumber(1)
                    .doReadSync();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

监听器及调用service:

@Component
public class ImportNoiseListener extends AnalysisEventListener<MonitorNoise> {
    /**
     * 每隔5120条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 128;
    private List<String> errMessage;

    List<MonitorNoise> noises = new ArrayList<>();
    private ImportService importService;

    public ImportNoiseListener(ImportService importService) {
        this.importService = importService;
        errMessage = new ArrayList<>();
    }

    @Override
    public void invoke(MonitorNoise monitorNoise, AnalysisContext context) {
        noises.add(monitorNoise);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (noises.size() >= BATCH_COUNT) {
            errMessage.addAll(saveData());
            // 存储完成清理 list
            noises.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        errMessage.addAll(saveData());
        noises.clear();
    }
    /**
     * 加上存储数据库
     */
    private List<String> saveData() {
        return importService.saveNoises(noises);
    }

    public List<String> getErrMessage() {
        return errMessage;
    }

    public void setErrMessage(List<String> errMessage) {
        this.errMessage = errMessage;
    }

}

service层接口：

@Service
public interface ImportService {
    List<String> saveNoises(List<MonitorNoise> list);
}

service层实现类：

@Service
public class ImportServiceImpl implements ImportService {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<String> saveNoises(List<MonitorNoise> noises) {
        ArrayList<String> errMessage = new ArrayList<>();
        MonitorNoise noise = new MonitorNoise();
        Map<String,Object> params = new HashMap<>();
        StringBuilder sql = new StringBuilder("INSERT INTO xxx (name, time, place) VALUES ( :name, :time, :place)");
        params.put("name", noise.getName());
        params.put("time", noise.getTime());
        params.put("place", noise.getPlace());
        SqlParameterSource[] paramss = SqlParameterSourceUtils.createBatch(noises.toArray());

        namedParameterJdbcTemplate.batchUpdate(sql.toString(), paramss);
        return errMessage;
    }
}

导出

controller层：

@RestController
@RequestMapping("/export")
public class ExportController {

    @Autowired
    ExportService exportService;

    @RequestMapping("/exportNoise")
    public void a(HttpServletResponse response){
        try{
            exportService.exportNoise(response);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

service层接口：

@Service
public interface ExportService {
    void exportNoise(HttpServletResponse response);
}

service层实现类：

@Service
public class ExportServiceImpl implements ExportService {

    @Autowired
    NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public void exportNoise(HttpServletResponse response) {
        try {
            List<MonitorNoise> list = new ArrayList<MonitorNoise>();
            String name = "噪声监测数据导入模板.xlsx";
            // 导出时候会出现中文无法识别问题，需要转码
            String fileName = new String(name.getBytes("gb2312"),"ISO8859-1");
            response.setContentType("application/vnd.ms-excel;chartset=utf-8");
            response.setHeader("Content-Disposition","attachment;filename=" + fileName);
            //调用工具类
            ExcelWriter writer = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellWriteHandler()).build();
            WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(MonitorNoise.class).build();
            writer.write(list,sheet);
            writer.finish(); // 使用完毕之后关闭
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

涉及工具类：
导出excel自动列宽工具类：

/**
 * Excel 导出列宽度自适应
 */
public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

vue前端导入时：

html代码:

	<el-dialog
      :title="title"
      :visible.sync="this.dialogVisibleNoise"
      width="600px"
      top="8vh"
      :before-close="colseDialog"
      :close-on-click-modal="false"
    >
      <el-row class="import-dialog-row">
        <el-col :span="12">
          <el-upload
            class="uploadFile"
            ref="upload"
            :action=""
            accept=".xls,.xlsx"
            async="false"
            :limit="1"
            :on-success="handleSuccess"
            :file-list="fileList"
            :before-upload="beforeUpload"
            :auto-upload="false"
            :on-remove="handleRemove"
            :on-progress="onProgress"
            element-loading-text="数据导入中，请稍等..."
            v-loading.fullscreen="loading"
          >
            <el-button slot="trigger" size="small" type="primary"
              >选择文件</el-button
            >
            <el-tooltip placement="right-start" style="padding-left: 20px">
              <div slot="content">单次导入，文件数据条数不能超过10000条</div>
              <i class="el-icon-warning"></i>
            </el-tooltip>
          </el-upload>
        </el-col>
      </el-row>
      <el-row style="line-height: 35px">
        <el-col :span="12">
          <div v-show="showInf">
            <span style="color: red">导入失败</span>
          </div>
        </el-col>
        <el-col :span="12"> </el-col>
      </el-row>
      <span slot="footer" class="dialog-footer">
        <el-button size="small" @click="handleClose">{{
          $t("base.button.cancel")
        }}</el-button>
        <el-button size="small" type="primary" @click="handleSave()"
          >导入</el-button
        >
      </span>
    </el-dialog>

js func代码：

	//取消按钮
    handleClose() {
      this.$refs.upload.clearFiles();
      this.isShow = true;
      this.showInf = false;
      this.dialogVisibleNoise = false;
      this.initNoiseData();
    },
    //导入按钮
    handleSave() {
      // debugger;
      let formFile = new FormData();
      formFile.append("file", this.$refs.upload.uploadFiles);
      // this.fileList = this.$refs.upload.uploadFiles;
      for (const key in this.fileList[0]) {
        // debugger;
        if (this.fileList[0].hasOwnProperty(key)) {
          if (this.fileList[0][key]) {
            formFile.append(key, this.fileList[0][key]);
          }
        }
      }
      this.$refs.upload.submit(formFile);
      this.isShow = false;
    },
    //上传成功事件
    handleSuccess(response) {
      var _this = this;
      _this.loading = false;
      console.log(response);
      _this.information = response;
      if (response.code === 500) {
        _this.showInf = true;
        _this.$message.error(response.message || "文件导入失败！");
        return;
      } else {
        _this.$message.success("文件导入成功！");
        _this.handleClose();
      }
    },
    colseDialog() {
      this.$refs.upload.clearFiles();
      this.isShow = true;
      this.showInf = false;
      this.dialogVisibleNoise = false;
    },
    //上传文件前，判断文件类型
    beforeUpload(file) {
      if (file.name.indexOf(".xls") == -1) {
        this.$message.error("只允许上传xls格式文件！");
        return false;
      }
      return true;
    },
    //文件移除事件
    handleRemove() {
      this.isShow = true;
      this.showInf = false;
    },
    onProgress() {
      this.loading = true;
    },

vue前端导出时：

html代码：

	<el-col :span="4">
          <el-button type="primary" size="small" @click="downloadTemplate">下载模板</el-button>
    </el-col>

js func代码：

//下载导入模板
    downloadTemplate(){
      var that = this;
      this.axios[""]
        .request({
          url: "",
          method: "get",
          responseType: "blob"
        })
        .then(function (response) {
          console.log(response)
          console.log(new Blob([response]));
          var blob = new Blob([response], { type: "application/x-xls" }); //创建一个blob对象
          var a = document.createElement("a"); //创建一个<a></a>标签
          a.href = URL.createObjectURL(blob); // response is a blob
          a.download = "000.xlsx"; //文件名称
          a.style.display = "none";
          document.body.appendChild(a);
          a.click();
          a.remove();
          that.dialogVisibleNoise = false
        })
        .catch(function (err) {
          console.log(err);
        });
    },

完毕