Examples with Sheet com.alibaba.excel.metadata.Sheet used on opensource projects

Example 1 with Sheet

use of com.alibaba.excel.metadata.Sheet in project springboot by lbshold.

the class CoordinateExcelController method template.

@ApiOperation("Excel导入模板下载")
@GetMapping("/not-auth/template")
public void template(HttpServletResponse response) throws Exception {
    List<MyCoordinate> result = new ArrayList<>();
    response.setCharacterEncoding("UTF-8");
    String name = URLEncoder.encode("坐标转换模板.xlsx", "UTF-8");
    response.setContentType("application/x-msdownload");
    response.addHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + name);
    try (OutputStream out = response.getOutputStream()) {
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        Sheet sheet1 = new Sheet(1, 0, MyCoordinate2.class);
        sheet1.setSheetName("坐标转换模板");
        writer.write(result, sheet1);
        writer.finish();
    } catch (Exception e) {
        log.error(e.getMessage(), e);
    }
}

Example 2 with Sheet

use of com.alibaba.excel.metadata.Sheet in project springboot by lbshold.

the class CoordinateExcelController method downLoad.

@ApiOperation(value = "转换后的坐标Excel导出", notes = "Excel批量导入坐标后，复制该请求链接浏览器访问下载Excel(转换后的坐标)，转换失败Excel会有相应提示")
@GetMapping("/not-auth/export")
public void downLoad(HttpServletResponse response) throws Exception {
    Cache cache = cacheManager.getCache("file");
    Cache.ValueWrapper cacheValue = cache.get("result");
    if (cacheValue != null) {
        List<MyCoordinate> result = (List<MyCoordinate>) cacheValue.get();
        // 写入excel
        response.setCharacterEncoding("UTF-8");
        String name = URLEncoder.encode("转换后的Gcj02坐标.xlsx", "UTF-8");
        response.setContentType("application/x-msdownload");
        response.addHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + name);
        try (OutputStream out = response.getOutputStream()) {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, MyCoordinate.class);
            sheet1.setSheetName("转换后的Gcj02坐标");
            writer.write(result, sheet1);
            writer.finish();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }
    cache.clear();
}

Example 3 with Sheet

use of com.alibaba.excel.metadata.Sheet in project Qualitis by WeBankFinTech.

the class RuleBatchServiceImpl method writeExcelToOutput.

private void writeExcelToOutput(List<ExcelTemplateRuleByProject> templateRules, List<ExcelCustomRuleByProject> customRules, List<ExcelMultiTemplateRuleByProject> multiTemplateRules, List<ExcelTemplateFileRuleByProject> templateFileRules, 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, ExcelTemplateRuleByProject.class);
        templateSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(templateRules, templateSheet);
        Sheet customSheet = new Sheet(2, 0, ExcelCustomRuleByProject.class);
        customSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(customRules, customSheet);
        Sheet multiTemplateSheet = new Sheet(3, 0, ExcelMultiTemplateRuleByProject.class);
        multiTemplateSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(multiTemplateRules, multiTemplateSheet);
        Sheet templateFileSheet = new Sheet(4, 0, ExcelTemplateFileRuleByProject.class);
        templateFileSheet.setSheetName(ExcelSheetName.TEMPLATE_FILE_RULE_NAME);
        writer.write(templateFileRules, templateFileSheet);
        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage(), 500);
    } finally {
        outputStream.close();
    }
}

Example 4 with Sheet

use of com.alibaba.excel.metadata.Sheet in project Qualitis by WeBankFinTech.

the class ProjectBatchServiceImpl method writeExcelToOutput.

private void writeExcelToOutput(List<ExcelProject> excelProjects, List<ExcelTemplateRuleByProject> excelTemplateRuleByProjects, List<ExcelCustomRuleByProject> excelCustomRuleByProjects, List<ExcelMultiTemplateRuleByProject> excelMultiTemplateRuleByProjects, List<ExcelTemplateFileRuleByProject> excelTemplateFileRuleByProject, 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);
        Sheet templateFileSheet = new Sheet(5, 0, ExcelTemplateFileRuleByProject.class);
        templateFileSheet.setSheetName(ExcelSheetName.TEMPLATE_FILE_RULE_NAME);
        writer.write(excelTemplateFileRuleByProject, templateFileSheet);
        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}

Example 5 with Sheet

use of com.alibaba.excel.metadata.Sheet in project Qualitis by WeBankFinTech.

the class ProjectBatchServiceImpl method readExcel.

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);
        } else if (sheet.getSheetName().equals(ExcelSheetName.TEMPLATE_FILE_RULE_NAME)) {
            sheet.setClazz(ExcelTemplateFileRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.RULE_METRIC_NAME)) {
            sheet.setClazz(ExcelRuleMetric.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;
}