Java_EasyExcel_导入_导出Java-js

easyExcel导入

• 从easyexcel官网中拷贝过来，使用到的，这是使用监听器的方法。

EasyExcel.read(file.getInputStream(), BaseStoreDataExcelVo.class, new ReadListener<BaseStoreDataExcelVo>() {/*** 单次缓存的数据量*/public static final int BATCH_COUNT = 100;/***临时存储*/private List<BaseStoreDataExcelVo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);@Overridepublic void invoke(BaseStoreDataExcelVo data, AnalysisContext analysisContext) {cachedDataList.add(data);if (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {saveData();}/*** 存储数据库*/private void saveData() {saveAndUpdate(cachedDataList, result);}
}).headRowNumber(3).sheet().doRead();

• 其中headRowNumber是指表头为第三行，数据从第四行开始读取

使用easyExcel导出数据

• 封装了一个导出的工具类，也是使用easyexcel

/*** 方法描述: 浏览器点击导出后导出文件** @param response 响应* @param list  导出数据集合* @param fileName 文件名 不含后缀* @param clazz 导出数据的数据类型* @return void*/public static void exportExcel(HttpServletResponse response, List<?> list,String fileName, Class<?> clazz) throws IOException {if (CollectionUtils.isEmpty(list)) {throw new RuntimeException();}if (StringUtils.isEmpty(fileName)) {fileName = new Date().toString();}String sheetName = fileName;// 使用swagger 会有问题，请直接用浏览器或者用postmantry {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");//防止中文乱码fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//表头字体颜色map 1为user中索引位置Map<Integer,Short> colorMap=new HashMap<>();
//            colorMap.put(1, IndexedColors.BLUE.index);ExcelCellWidthStyleStrategy widthStyleStrategy = new ExcelCellWidthStyleStrategy();// 这里需要设置不关闭流EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(widthStyleStrategy).registerWriteHandler(new XCellStyleUtils(colorMap)).autoCloseStream(Boolean.FALSE).sheet(sheetName).doWrite(list);} catch (Exception e) {}}

• 控制层使用

    @GetMapping("/downloadStoreSelect")@ApiOperation("数据下载")public void downloadStoreSelect(HttpServletResponse response) {Page<BaseStoreData> baseStoreDataPage = storeDataService.selectPage(null);try {EasyExcelUtils.exportExcel(response, changeData(baseStoreDataPage.getRecords()), "库存.xlsx", BaseStoreDataExcelVo.class);} catch (IOException e) {throw new RuntimeException(e);}}

使用easyExcel下载对应前端代码

  downloadStoreSelect().then(response => {const url = window.URL.createObjectURL(new Blob([response.data]));const link = document.createElement('a');link.href = url;link.setAttribute('download', 'export.xlsx'); // 下载文件名document.body.appendChild(link);link.click();})