com.alibaba.excel.metadata.Sheet Java Examples

private ExcelRuleListener partitionByRuleName(InputStream inputStream) {
    ExcelRuleListener listener = new ExcelRuleListener();
    ExcelReader excelReader = new ExcelReader(inputStream, null, listener);
    List<Sheet> sheets = excelReader.getSheets();
    for (Sheet sheet : sheets) {
        if (sheet.getSheetName().equals(ExcelSheetName.TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelTemplateRule.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.CUSTOM_RULE_NAME)) {
            sheet.setClazz(ExcelCustomRule.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelMultiTemplateRule.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        }
    }

    return listener;
}
 

private void writeExcelToOutput(List<ExcelTemplateRule> templateRules, List<ExcelCustomRule> customRules,
                                List<ExcelMultiTemplateRule> multiTemplateRules, OutputStream outputStream) throws WriteExcelException, IOException {
    try {
        LOGGER.info("Start to write excel");
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        Sheet templateSheet = new Sheet(1, 0, ExcelTemplateRule.class);
        templateSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(templateRules, templateSheet);

        Sheet customSheet = new Sheet(2, 0, ExcelCustomRule.class);
        customSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(customRules, customSheet);

        Sheet multiTemplateSheet = new Sheet(3, 0, ExcelMultiTemplateRule.class);
        multiTemplateSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(multiTemplateRules, multiTemplateSheet);

        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}
 

public ExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName,
                                BaseRowModel object) {
    this.sheetNo++;
    try {
        Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
        sheet.setSheetName(sheetName);
        this.write(list, sheet);
    } catch (Exception ex) {
        ex.printStackTrace();
        try {
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    return this;
}
 

/**
 * 异步导出 Excel ：一个 sheet，带表头
 *
 * @param
 * @param list      数据 list，每个元素为一个 BaseRowModel
 * @param sheetName 导入文件的 sheet 名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static String asyWriteExcel(List<? extends BaseRowModel> list,
                                String sheetName, BaseRowModel object) {
    // 现将数据导出excel到本地
    try {
        String fileName = URLEncoder.encode(createFileName(), "UTF-8");
        ExcelWriter writer = new ExcelWriter(getFileOutputStream(fileName), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
        // 读取该excel,并上传到oss，返回下载链接
        // File file = readFileByLines(fileName + ".xlsx");
        // return FileUploadUtil.upload(file, fileName + ".xlsx");
    } catch (UnsupportedEncodingException e) {
        throw new RuntimeException("创建excel失败!");
    }
    return null;
}
 

@Override
public Sheet getCurrentSheet() {
    Sheet sheet = new Sheet(readSheetHolder.getSheetNo() + 1);
    sheet.setSheetName(readSheetHolder.getSheetName());
    sheet.setHead(readSheetHolder.getHead());
    sheet.setClazz(readSheetHolder.getClazz());
    sheet.setHeadLineMun(readSheetHolder.getHeadRowNumber());
    return sheet;
}
 

private void readAndWrite6(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriterWithTempAndHandler(null, out, null, false, null);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1, null);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = EasyExcel.getReader(inputStream, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();
}
 

private void readAndWrite5(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriterWithTemp(null, out, null, false);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1, null);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = EasyExcel.getReader(inputStream, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();
}
 

private void readAndWrite4(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = new ExcelWriter(null, out, null, null);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1, null);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = new ExcelReader(inputStream, null, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();
}
 

private void readAndWrite3(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = new ExcelWriter(out, null);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = new ExcelReader(inputStream, null, null, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();

}
 

private void readAndWrite2(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriter(out, null, false);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    EasyExcel.readBySax(inputStream, new Sheet(1, 0), new CompatibilityDataListener());
    inputStream.close();
}
 

private void readAndWrite1(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriter(out);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    EasyExcel.readBySax(inputStream, new Sheet(1, 0), new CompatibilityDataListener());
    inputStream.close();
}
 

private ExcelProjectListener readExcel(InputStream inputStream) {
    LOGGER.info("Start to read project excel");
    ExcelProjectListener listener = new ExcelProjectListener();
    ExcelReader excelReader = new ExcelReader(inputStream, null, listener);
    List<Sheet> sheets = excelReader.getSheets();
    for (Sheet sheet : sheets) {
        if (sheet.getSheetName().equals(ExcelSheetName.TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelTemplateRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.PROJECT_NAME)) {
            sheet.setClazz(ExcelProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.CUSTOM_RULE_NAME)) {
            sheet.setClazz(ExcelCustomRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelMultiTemplateRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        }
    }


    LOGGER.info("Finish to read project excel. excel content: rule sheet {}, project sheet {}", listener.getExcelRuleContent(), listener.getExcelProjectContent());
    return listener;
}
 

/**
 * Parse the workBook get all sheets
 *
 * @return workBook all sheets
 *
 * @deprecated please use {@link #excelExecutor()}
 */
@Deprecated
public List<Sheet> getSheets() {
    List<ReadSheet> sheetList = excelExecutor().sheetList();
    List<Sheet> sheets = new ArrayList<Sheet>();
    if (sheetList == null || sheetList.isEmpty()) {
        return sheets;
    }
    for (ReadSheet readSheet : sheetList) {
        Sheet sheet = new Sheet(readSheet.getSheetNo() + 1);
        sheet.setSheetName(readSheet.getSheetName());
        sheets.add(sheet);
    }
    return sheets;
}
 

/**
 * 读取 Excel 的指定 sheet 指定数据
 *
 * @param excel       文件
 * @param baseRowModel    实体类映射，继承 BaseRowModel 类
 * @param sheetNo     sheet 的序号（从第几个 sheet 开始读）
 * @param headLineNum 表头行数（从第几行开始读）
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel,
                                     int sheetNo, int headLineNum) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    reader.read(new Sheet(sheetNo, headLineNum, baseRowModel.getClass()));
    return excelListener.getDatas();
}
 

/**
 * 读取 整个Excel(多个 sheet 需要 各个 sheet 字段相同)
 *
 * @param excel    文件
 * @param baseRowModel 实体类映射，继承 BaseRowModel 类
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    for (Sheet sheet : reader.getSheets()) {
        if (baseRowModel != null) {
            sheet.setClazz(baseRowModel.getClass());
        }
        reader.read(sheet);
    }
    return excelListener.getDatas();
}
 

/**
 * 导出 Excel ：一个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      需要导出的数据
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                              String fileName, String sheetName, BaseRowModel object) {
    // WriteModel 是 写入 Excel 的数据模型对象
    ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    // 异常处理
    writer.write(list, sheet);
    writer.finish();
}
 

private void writeExcelToOutput(List<ExcelProject> excelProjects, List<ExcelTemplateRuleByProject> excelTemplateRuleByProjects, List<ExcelCustomRuleByProject> excelCustomRuleByProjects,
                                List<ExcelMultiTemplateRuleByProject> excelMultiTemplateRuleByProjects, OutputStream outputStream) throws WriteExcelException, IOException {
    try {
        LOGGER.info("Start to write excel");
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        Sheet templateRuleSheet = new Sheet(1, 0, ExcelTemplateRuleByProject.class);
        templateRuleSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(excelTemplateRuleByProjects, templateRuleSheet);

        Sheet projectSheet = new Sheet(2, 0, ExcelProject.class);
        projectSheet.setSheetName(ExcelSheetName.PROJECT_NAME);
        writer.write(excelProjects, projectSheet);

        Sheet customRuleSheet = new Sheet(3, 0, ExcelCustomRuleByProject.class);
        customRuleSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(excelCustomRuleByProjects, customRuleSheet);

        Sheet multiRuleSheet = new Sheet(4, 0, ExcelMultiTemplateRuleByProject.class);
        multiRuleSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(excelMultiTemplateRuleByProjects, multiRuleSheet);
        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}
 

/**
 * Parse the specified sheet
 *
 * @param sheet
 *            Read sheet
 * @param clazz
 *            object parsed into each row of value
 *
 * @deprecated Set the class in the sheet before read
 */
@Deprecated
public void read(Sheet sheet, Class clazz) {
    if (sheet != null) {
        sheet.setClazz(clazz);
    }
    read(sheet);
}
 

/**
 * 导出 Excel ：多个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      需要导出的数据 list
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
                                                      String fileName, String sheetName, BaseRowModel object) {
    ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    writer.write(list, sheet);
    return writer;
}
 

/**
 * get current sheet
 *
 * @return current analysis sheet
 * @deprecated please use {@link #readSheetHolder()}
 */
@Deprecated
Sheet getCurrentSheet();
 

/**
 * Write data to a sheet
 *
 * @param data
 *            Data to be written
 * @param sheet
 *            Write to this sheet
 * @return this current writer
 * @deprecated please use {@link ExcelWriter#write(List, WriteSheet)}
 */
@Deprecated
public ExcelWriter write(List data, Sheet sheet) {
    return write(data, sheet, null);
}
 

/**
 * Write data to a sheet
 *
 * @param data
 *            Data to be written
 * @param sheet
 *            Write to this sheet
 * @return this current writer
 * @deprecated please use {@link ExcelWriter#write(List, WriteSheet)}
 */
@Deprecated
public ExcelWriter write0(List data, Sheet sheet) {
    return write(data, sheet, null);
}
 

/**
 * Write value to a sheet
 *
 * @param data
 *            Data to be written
 * @param sheet
 *            Write to this sheet
 * @param table
 *            Write to this table
 * @return this
 * @deprecated * @deprecated please use {@link ExcelWriter#write(List, WriteSheet,WriteTable)}
 */
@Deprecated
public ExcelWriter write0(List data, Sheet sheet, Table table) {
    return write(data, sheet, table);
}
 

/**
 * Write data to a sheet
 *
 * @param data
 *            Data to be written
 * @param sheet
 *            Write to this sheet
 * @return this current writer
 * @deprecated please use {@link ExcelWriter#write(List, WriteSheet)}
 */
@Deprecated
public ExcelWriter write1(List data, Sheet sheet) {
    return write(data, sheet, null);
}
 

/**
 * Write value to a sheet
 *
 * @param data
 *            Data to be written
 * @param sheet
 *            Write to this sheet
 * @param table
 *            Write to this table
 * @return this
 * @deprecated * @deprecated please use {@link ExcelWriter#write(List, WriteSheet,WriteTable)}
 */
@Deprecated
public ExcelWriter write1(List data, Sheet sheet, Table table) {
    return write(data, sheet, table);
}
 

/**
 * Parsing large file
 *
 * @param in
 *            the POI filesystem that contains the Workbook stream.
 * @param sheet
 *            read sheet.
 * @param listener
 *            Callback method after each row is parsed.
 * @deprecated please use 'EasyExcel.read(in,head,listener).sheet(sheetNo).doRead();'
 */
@Deprecated
public static void readBySax(InputStream in, Sheet sheet, AnalysisEventListener listener) {
    new ExcelReader(in, null, listener).read(sheet);
}