Java Code Examples for com.alibaba.excel.metadata.Sheet#setSheetName()

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;
}
 

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();
    }
}
 

/**
 * 导出 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();
}
 

/**
 * 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;
}
 

@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 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 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 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 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 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 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();
}
 

/**
 * 导出 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;
}