EasyPoi大資料匯入匯出百萬級實體-有解無憂

EasyPoi介紹:

利用注解的方式簡化了Excel、Word、PDF等格式的匯入匯出，而且是百萬級資料的匯入匯出，EasyPoi官方網址：EasyPoi教程_V1.0 (mydoc.io)，下面我寫了一個測驗用例，真的是很方便，可以利用注解自動完成單元格的合并，設定單元格寬度、設定字符替換、并且可以很好的完成物體類之間一對一、一對多關系的處理

不賣關子，事先說明百萬級大資料操作使用：匯入（importExcelBySax），匯出（exportBigExcel）

匯入依賴

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>

物體物件

/**
 * 免打擾手機號
 *
 * @author Mark [email protected]
 * @since 1.0.0
 */
@Data
public class NonIntrusiveExcel {
    @Excel(name = "手機號碼", width = 20)
    @NotNull
    private String phone;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        NonIntrusiveExcel that = (NonIntrusiveExcel) o;
        return phone.equals(that.phone);
    }

    @Override
    public int hashCode() {
        return Objects.hash(phone);
    }
}

匯入匯出工具類

/**
 * excel工具類
 *
 * excel中xls和xlsx的區別是：
 * 1、檔案格式不同，xls是一個特有的二進制格式，其核心結構是復合檔案型別的結構，而xlsx的核心結構是XML型別的結構，采用的是基于 XML 的壓縮方式，使其占用的空間更小，xlsx 中最后一個 x 的意義就在于此，
 * 2、版本不同，xls是excel2003及以前版本生成的檔案格式，而xlsx是excel2007及以后版本生成的檔案格式，
 * 3、兼容性不同，xlsx格式是向下兼容的，可兼容xls格式，
 *
 * @author Mark [email protected]
 */
public class ExcelUtils {

    /**
     * Excel匯入
     *
     * @param request       request
     * @param pojoClass     物件Class
     */
    public static List importExcel(HttpServletRequest request, Class<?> pojoClass) throws IOException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("file");
        if (file == null) {
            throw new RenException("未找到上傳的檔案！");
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerify(true); // 開啟校驗規則
        List targetList = null;

        try {
            System.out.println("正在讀取檔案: " + file.getOriginalFilename() + "，開始匯入資料，");
            targetList = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            file.getInputStream().close();
        }

        return targetList;
    }

    /**
     * Excel大資料匯入
     *
     * @param request       request
     * @param pojoClass     物件Class
     */
    public static Set importBigExcel(HttpServletRequest request, Class<?> pojoClass) throws IOException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("file");
        if (file == null) {
            throw new RenException("未找到上傳的檔案！");
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerify(true); // 開啟校驗規則
        Set targetList = new HashSet(); // 添加set集合過濾去重元素

        try {
            System.out.println("正在讀取檔案: " + file.getOriginalFilename() + "，開始匯入資料，");
            ExcelImportUtil.importExcelBySax(file.getInputStream(), pojoClass, params, new IReadHandler() {
                @Override
                public void handler(Object o) {
                    targetList.add(o);
                }

                @Override
                public void doAfterAll() {

                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            file.getInputStream().close();
        }

        return targetList;
    }

    /**
     * Excel匯出
     *
     * @param response      response
     * @param fileName      檔案名
     * @param list          資料List
     * @param pojoClass     物件Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //當前日期
            fileName = DateUtil.formatDatetime(new Date());
        }

        // 設定匯出格式為xlsx，默認xlsx
        ExportParams exportParams = new ExportParams();
        exportParams.setType(ExcelType.XSSF);

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel");
//        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * Excel大資料匯出
     *
     * @param list          資料List
     * @param pojoClass     物件Class
     */
    public static byte[] exportBigExcelByte(Collection<?> list, Class<?> pojoClass) throws IOException {
        Workbook workbook;

        // 設定匯出單sheet頁最大一百萬行資料
        ExportParams exportParams = new ExportParams();
        exportParams.setMaxNum(1000000);
        exportParams.setType(ExcelType.XSSF);

        workbook = ExcelExportUtil.exportBigExcel(exportParams, pojoClass, (queryParams, num) -> {
            // 只匯出一次，第二次回傳null終止回圈
            if (((int) queryParams) == num) {
                return null;
            }
            System.out.println("正在進行大資料量匯出，條數: " + list.size());
            return Arrays.asList(list.toArray());
        }, 2);

        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        workbook.write(bos);
        bos.close();

        byte[] bytes = bos.toByteArray();

        return bytes;
    }

    /**
     * Excel匯出，先sourceList轉換成List<targetClass>，再匯出
     *
     * @param response      response
     * @param fileName      檔案名
     * @param sourceList    原資料List
     * @param targetClass   目標物件Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                           Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, targetList, targetClass);
    }

    /**
     * Excel生成
     *
     * @param response      response
     * @param fileName      檔案名
     */
    public static void mkdirExcel(HttpServletResponse response, String fileName,
                                  Workbook workbook) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //當前日期
            fileName = DateUtil.formatDatetime(new Date());
        }

        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * XSSF
     * excel添加下拉資料校驗
     *
     * @param workbook   哪個 sheet 頁添加校驗
     * @param dataSource 資料源陣列
     * @param col        第幾列校驗（0開始）
     * @return
     */
    public static void createXssfSelected(Workbook workbook, String[] dataSource, int col) {
        Sheet sheet = workbook.getSheetAt(0);
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, col, col);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(dataSource);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        //處理Excel兼容性問題
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        dataValidation.setEmptyCellAllowed(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.createPromptBox("提示", "只能選擇下拉框里面的資料");
        sheet.addValidationData(dataValidation);
    }

}

介面呼叫

@RestController
@RequestMapping("/app/nonIntrusive")
@Slf4j
public class NonIntrusiveController {
    @Autowired
    private NonIntrusiveService appNonIntrusiveService;

    @PostMapping(value = "https://www.cnblogs.com/importAndExportFilter")
    @LogOperation("免打擾過濾")
    @RequiresPermissions("app:nonIntrusive:filter")
    public byte[] importFilter(HttpServletRequest request) throws Exception {
        long startTime = System.currentTimeMillis();

        // 讀取excel檔案內容轉成集合
        Set<NonIntrusiveExcel> importSet = ExcelUtils.importBigExcel(request, NonIntrusiveExcel.class);

        log.info("已讀取檔案內容條數：{}, 總共耗時：{} 毫秒", importSet.size(), System.currentTimeMillis() - startTime);

        startTime = System.currentTimeMillis();

        // 讀取資料表串列集合
        Set<NonIntrusiveExcel> phoneSet = appNonIntrusiveService.listExcel();

        log.info("已加載存盤的免打擾號碼庫總條數：{}, 總共耗時：{} 毫秒", phoneSet.size(), System.currentTimeMillis() - startTime);

        startTime = System.currentTimeMillis();

        // Set排除已存在的集合資料
        importSet.removeAll(phoneSet);

        log.info("已完成過濾免打擾號碼后的總條數：{}, 總共耗時：{} 毫秒", importSet.size(), System.currentTimeMillis() - startTime);

        // 進行大資料excel匯出
        return ExcelUtils.exportBigExcelByte(importSet, NonIntrusiveExcel.class);
    }

    @GetMapping(value = "https://www.cnblogs.com/exportTemplate")
    public void export(HttpServletResponse response) throws Exception {
        ExcelUtils.exportExcelToTarget(response, null, new ArrayList<>(), NonIntrusiveExcel.class);
    }

}

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/528705.html

標籤：Java

上一篇：死磕Java面試系列：深拷貝與淺拷貝的實作原理

下一篇：京東云開發者｜深入JDK中的Optional