导出与导入

今天涉及到了一种新的导出与导入方式。留下记录。

导出

Service类：

/**
 *  库容曲线导出
 *
 * @param vo
 * @return
 * @throws ParseException
 * @author shun
 * @date 2021年8月21日
 */
public Workbook writeWithoutHead(BaseCapacityVo vo) throws ParseException {
    Workbook workbook = new SXSSFWorkbook(100);
    String sheetName = "sheet1";
    Sheet sheet1 = workbook.createSheet(sheetName);

    PageData<BaseCapacityVo> dateRainMonthVOPageData = selectFlowList(vo);
    // 设置列宽, 列宽单位1/256
    sheet1.setColumnWidth(0, 11 * 256);
    sheet1.setColumnWidth(1, 20 * 256);
    sheet1.setColumnWidth(2, 15 * 256);
    sheet1.setColumnWidth(3, 15 * 256);
    sheet1.setColumnWidth(4, 15 * 256);

    CellStyle style = workbook.createCellStyle();
    // 水平居中
    style.setAlignment(HorizontalAlignment.CENTER);
    // 垂直居中
    style.setVerticalAlignment(VerticalAlignment.CENTER);

    if (dateRainMonthVOPageData != null && dateRainMonthVOPageData.getTotal() > 0) {
        Row title1 = sheet1.createRow(0); // 创建第一行标题
        // 固定列
        Cell cell = title1.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);

        cell = title1.createCell(1);
        cell.setCellValue("测站名称");
        cell.setCellStyle(style);

        cell = title1.createCell(2);
        cell.setCellValue("库水位");
        cell.setCellStyle(style);

        cell = title1.createCell(3);
        cell.setCellValue("蓄水量");
        cell.setCellStyle(style);

        cell = title1.createCell(4);
        cell.setCellValue("水平面积");
        cell.setCellStyle(style);

        // 数据处理
        // 遍历map
        for (int i=0;i<dateRainMonthVOPageData.getData().size();i++) {
            // 固定列数据
            Row createRow = sheet1.createRow(i+1);
            cell = createRow.createCell(0);
            cell.setCellStyle(style);
            cell.setCellValue(i+1);

            cell = createRow.createCell(1);
            cell.setCellStyle(style);
            cell.setCellValue(dateRainMonthVOPageData.getData().get(i).getName());
            cell = createRow.createCell(2);
            cell.setCellStyle(style);
            cell.setCellValue(String.valueOf(dateRainMonthVOPageData.getData().get(i).getReservoirWaterLevel()));
            cell = createRow.createCell(3);
            cell.setCellStyle(style);
            cell.setCellValue(String.valueOf(dateRainMonthVOPageData.getData().get(i).getStorageLevel()));
            cell = createRow.createCell(4);
            cell.setCellStyle(style);
            cell.setCellValue(String.valueOf(dateRainMonthVOPageData.getData().get(i).getWaterArea()));
        }
    }
    return  workbook;
}

 controller类：

/**
 * 导出-库容曲线表
 *
 * @author shun
 * @date 2021年8月21日
 */
@GetMapping("/write")
public void purchaseDetailExportWaterDay(BaseCapacityVo filter, HttpServletResponse response) {
    try {
        Workbook workbook = base.writeWithoutHead(filter);
        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);

        // 文件名
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileDate = StringUtils.isEmpty(filter.getBeginTime()) ? format.format(new Date())
                : filter.getEndTime() + "_" + filter.getEndTime();
        String fileName = "库容曲线表" + fileDate;
        downloadUtil.download(bos, response, fileName + ".xlsx");
        // 关闭
        workbook.close();
    } catch (Exception e) {
        throw new CustomException("导出异常, 请联系管理员!");
    }
}

导入

先导出模板

service:

public void houseDownload() {
    try {
        // 读到流中
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("static/exceltemplates/库容曲线导出模板.xlsx");

        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        // 导出文件下载
        DownloadUtil downloadUtil = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        byte[] b = new byte[1024];
        int len;
        try {
            while ((len = in.read(b)) > 0) {
                bos.write(b, 0, len);
            }

            in.close();
        } catch (IOException e) {

        }

        downloadUtil.download(bos, response, "库容曲线导出模板.xlsx");
    } catch (IOException e) {

    }
}

/**
 * 导入数据
 * @param file
 * @return
 */
public List<BaseCapacity> readEasyExcel(MultipartFile file) {
    try{
        InputStream in = file.getInputStream();
        List<BaseCapacity> resultLst = new ArrayList<>();
        CapacityListener listener = new CapacityListener(resultLst, this);
        ExcelReader excelReader = EasyExcel.read(in, BaseCapacity.class, listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        //调整读取开始行数 设置0 为第一行， 默认是从第二行读取
        readSheet.setHeadRowNumber(1);
        excelReader.read(readSheet);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
        listener.save();
        return resultLst;
    } catch (IOException e) {
        e.printStackTrace();
    }

    return Collections.emptyList();
}

controller:

/**
 * 库容曲线表模板
 */
@GetMapping("/write1")
public void houseDownload() {
    base.houseDownload();
}

/**
 * 导入
 * @param file
 * @return
 */
@PostMapping("/red")
public Result readEasyExcel(@RequestParam("file") MultipartFile file) {
    base.readEasyExcel(file);
    return Result.success();
}