java easypoi 多sheet导入与导出

easypoi 多sheet导入与导出

---

一、easypoi 环境

1.pom依赖

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.3.0</version>
        </dependency>

2.工具类ExcelExport

public class ExcelExport {

    private HSSFWorkbook wb;
    private String fileName;
	
	public ExcelExport(String fileName) {
        if (wb == null)
            this.wb = new HSSFWorkbook();
        this.fileName = fileName;
    }

	 //导出参数
    public static ExportParams getExportParams(String name) {
        //表格名称,sheet名称,导出版本
        return new ExportParams(name, name, ExcelType.HSSF);
    }

    public void setWorkBook(Workbook wb) {
        this.wb = (HSSFWorkbook) wb;
    }

	public void doExportTwo(String path) throws Exception {
        final OutputStream os = new FileOutputStream(new File(path + "/" + fileName + ".xls"));
        this.wb.write(os);
        os.flush();
        os.close();
    }
}

二、多sheet导出

public class Test {

    public static void main(String[] args) throws Exception {
        //多个map，对应了多个sheet
        List<Map<String, Object>> listMap = new ArrayList<>();
        for (int i = 0; i < 4; i++) {
            Map<String, Object> map = new HashMap<>();
            //表格title
            map.put("title", ExcelExport.getExportParams("测试" + i));
            //表格对应实体
            map.put("entity", TestEntity.class);

            List<Test.TestEntity> data = new ArrayList<Test.TestEntity>();
            for (int j = 0; j < 100; j++) {
                Test.TestEntity testEntity = new Test.TestEntity();
                testEntity.setUsername("张三" + j);
                testEntity.setAge(18 + j);
                data.add(testEntity);
            }
            map.put("data", data);
            listMap.add(map);
        }

        Workbook wb = ExcelExportUtil.exportExcel(listMap, ExcelType.HSSF);
        final ExcelExport export = new ExcelExport("基础物料");
        export.setWorkBook(wb);
        export.doExportTwo("E:\\temp");
    }

    @Data
    static class TestEntity {

        @Excel(name = "姓名", width = 15)
        private String username;

        @Excel(name = "年龄", width = 15)
        private int age;
    }
}

二、多sheet导入

1.功能方法

@Override
    public void importFile(MultipartFile file) throws Exception {
        final String filename = file.getOriginalFilename();
        final Workbook workBook = ExcelUtil.getWorkBook(file);
		// 此多线程方法仅供测试，如上正式环境 需要封装，会导致系统CPU炸裂
        // 多sheet导入用线程池
//        int nThreads = 4;
//        ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(nThreads, nThreads, 0L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(51), r -> {
//            Thread t = new Thread(r);
//            t.setUncaughtExceptionHandler((t1, e) -> log.error("线程异常：thread={},异常e={}", t1, e.getMessage()));
//            return t;
//        });

        for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
            byte[] bytes = file.getBytes();
            final InputStream inputStream = new ByteInputStream(bytes, bytes.length);
            final String sheetName = workBook.getSheetName(sheetIndex);
            final List<BaseMaterialImportDTO> materialImportList = new ArrayList<>();
            final List<BaseMaterialImportDTO> materialImportFailList = new ArrayList<>();
            final ImportParams params = new ImportParams();
            params.setHeadRows(1);
            params.setTitleRows(0);
            params.setStartSheetIndex(sheetIndex);
//            threadPoolExecutor.submit(() -> {
            if (filename.lastIndexOf(FormatExcel.FORMAT_CSV) > -1) {
                final CsvImportParams csvImportParams = new CsvImportParams();
                csvImportParams.setEncoding(CsvImportParams.GBK);
                csvImportParams.setHeadRows(1);
                CsvImportUtil.importCsv(inputStream, BaseMaterialImportDTO.class, csvImportParams, new IReadHandler<BaseMaterialImportDTO>() {
                    /**
                     * 行数据处理
                     *
                     * @param baseMaterialImportDTO
                     */
                    @Override
                    public void handler(final BaseMaterialImportDTO baseMaterialImportDTO) {
                        BaseMaterialServiceImpl.this.verifyRowData(baseMaterialImportDTO, materialImportFailList, materialImportList);
                    }

                    @Override
                    public void doAfterAll() {
                        BaseMaterialServiceImpl.this.doAfter(materialImportList, materialImportFailList, sheetName);
                    }
                });
            } else if (filename.lastIndexOf(FormatExcel.FORMAT_XLSX) > -1) {

                ExcelImportUtil.importExcelBySax(inputStream, BaseMaterialImportDTO.class, params, new IReadHandler<BaseMaterialImportDTO>() {
                    @Override
                    public void handler(final BaseMaterialImportDTO baseMaterialImportDTO) {
                        BaseMaterialServiceImpl.this.verifyRowData(baseMaterialImportDTO, materialImportFailList, materialImportList);
                    }

                    @Override
                    public void doAfterAll() {
                        BaseMaterialServiceImpl.this.doAfter(materialImportList, materialImportFailList, sheetName);
                    }
                });
            } else if (filename.lastIndexOf(FormatExcel.FORMAT_XLS) > -1) {
                params.setNeedVerify(true);
                try {
                    final ExcelImportResult<BaseMaterialImportDTO> excelImportResult = ExcelImportUtil.importExcelMore(inputStream, BaseMaterialImportDTO.class, params);
                    this.doAfter(excelImportResult.getList(), excelImportResult.getFailList(), sheetName);
                } catch (Exception e) {
                    log.error("解析或插入数据异常：异常e={}", e.getMessage());
                }
            } else {
                throw new ServiceException("仅支持上传excel、csv文件");
            }
//            });
        }
    }

2.实体类BaseMaterialImportDTO.java

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import javax.validation.constraints.NotBlank;

/**
 * 物料导入类
 */
@Data
public class BaseMaterialImportDTO {

    @Excel(name = "成品编码")
    @NotBlank(message = "[成品编码]不能为空")
    private String parentMaterialNo;

    @Excel(name = "子项物料代码")
    @NotBlank(message = "[子项物料代码]不能为空")
    private String materialNo;

    @Excel(name = "物料名称")
    @NotBlank(message = "[物料名称]不能为空")
    private String materialName;

    @Excel(name = "规格型号")
    @NotBlank(message = "[规格型号]不能为空")
    private String materialSpecification;

    @Excel(name = "物料属性")
    @NotBlank(message = "[物料属性]不能为空")
    private String materialProperty;

    @Excel(name = "工位")
    @NotBlank(message = "[工位]不能为空")
    private String materialWorkstation;
}

3.verifyRowData方法是校验方法，doAfter是入数据库方法