Examples with CellRangeAddress org.apache.poi.ss.util.CellRangeAddress used on opensource projects

Example 91 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestXSSFCell method test56170.

@Test
public void test56170() throws IOException {
    final Workbook wb1 = XSSFTestDataSamples.openSampleWorkbook("56170.xlsx");
    final XSSFSheet sheet = (XSSFSheet) wb1.getSheetAt(0);
    Workbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wb1);
    Cell cell;
    // add some contents to table so that the table will need expansion
    Row row = sheet.getRow(0);
    Workbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2);
    cell = row.createCell(0);
    Workbook wb4 = XSSFTestDataSamples.writeOutAndReadBack(wb3);
    cell.setCellValue("demo1");
    Workbook wb5 = XSSFTestDataSamples.writeOutAndReadBack(wb4);
    cell = row.createCell(1);
    Workbook wb6 = XSSFTestDataSamples.writeOutAndReadBack(wb5);
    cell.setCellValue("demo2");
    Workbook wb7 = XSSFTestDataSamples.writeOutAndReadBack(wb6);
    cell = row.createCell(2);
    Workbook wb8 = XSSFTestDataSamples.writeOutAndReadBack(wb7);
    cell.setCellValue("demo3");
    Workbook wb9 = XSSFTestDataSamples.writeOutAndReadBack(wb8);
    row = sheet.getRow(1);
    cell = row.createCell(0);
    cell.setCellValue("demo1");
    cell = row.createCell(1);
    cell.setCellValue("demo2");
    cell = row.createCell(2);
    cell.setCellValue("demo3");
    Workbook wb10 = XSSFTestDataSamples.writeOutAndReadBack(wb9);
    // expand table
    XSSFTable table = sheet.getTables().get(0);
    final CellReference startRef = table.getStartCellReference();
    final CellReference endRef = table.getEndCellReference();
    table.getCTTable().setRef(new CellRangeAddress(startRef.getRow(), 1, startRef.getCol(), endRef.getCol()).formatAsString());
    Workbook wb11 = XSSFTestDataSamples.writeOutAndReadBack(wb10);
    assertNotNull(wb11);
    wb11.close();
    wb10.close();
    wb9.close();
    wb8.close();
    wb7.close();
    wb6.close();
    wb5.close();
    wb4.close();
    wb3.close();
    wb2.close();
    wb1.close();
}

Example 92 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project hutool by looly.

the class CellUtil method mergingCells.

/**
 * 合并单元格，可以根据设置的值来合并行和列
 *
 * @param sheet 表对象
 * @param firstRow 起始行，0开始
 * @param lastRow 结束行，0开始
 * @param firstColumn 起始列，0开始
 * @param lastColumn 结束列，0开始
 * @param cellStyle 单元格样式
 * @return 合并后的单元格号
 */
public static int mergingCells(Sheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn, CellStyle cellStyle) {
    final CellRangeAddress cellRangeAddress = new // 
    CellRangeAddress(// first row (0-based)
    firstRow, // last row (0-based)
    lastRow, // first column (0-based)
    firstColumn, // last column (0-based)
    lastColumn);
    if (null != cellStyle) {
        RegionUtil.setBorderTop(cellStyle.getBorderTopEnum(), cellRangeAddress, sheet);
        RegionUtil.setBorderRight(cellStyle.getBorderRightEnum(), cellRangeAddress, sheet);
        RegionUtil.setBorderBottom(cellStyle.getBorderBottomEnum(), cellRangeAddress, sheet);
        RegionUtil.setBorderLeft(cellStyle.getBorderLeftEnum(), cellRangeAddress, sheet);
    }
    return sheet.addMergedRegion(cellRangeAddress);
}

Example 93 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project pmph by BCSquad.

the class ExcelHelper method generateDeclarationEtcBOHeader.

private Sheet generateDeclarationEtcBOHeader(List<MaterialExtension> extensions, Sheet sheet) {
    Field[] fields = DeclarationEtcBO.class.getDeclaredFields();
    Row r1 = sheet.createRow(0);
    Row r2 = sheet.createRow(1);
    Cell numcell = r1.createCell(0);
    numcell.setCellValue("序号");
    CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0);
    sheet.addMergedRegion(region);
    int count = 1;
    for (Field field : fields) {
        // 可访问性设置
        field.setAccessible(true);
        /* 仅查找与ExcelHeader注解匹配的表头 */
        if (field.isAnnotationPresent(ExcelHeader.class)) {
            ExcelHeader excelHeader = (ExcelHeader) field.getAnnotation(ExcelHeader.class);
            String headerName = excelHeader.header();
            if (StringUtil.notEmpty(headerName)) {
                switch(headerName) {
                    case "学习经历":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 3);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("起止时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("学校名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("所学专业");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("学历");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            break;
                        }
                    case "工作经历":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("起止时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("工作单位");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("职位");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            break;
                        }
                    case "教学经历":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("起止时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("学校名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("教学科目");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "个人成就":
                        {
                            Cell r1cell = r1.createCell(count);
                            r2.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 1, count, count);
                            sheet.setColumnWidth(count, 15 * 512);
                            sheet.addMergedRegion(region);
                            count++;
                            break;
                        }
                    case "学术兼职":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("兼职学术组织");
                            sheet.setColumnWidth(count, 7 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("级别");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("职务");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            break;
                        }
                    case "本套上版教材参编情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 4);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("教材名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("编写职务");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("是否数字编辑");
                            sheet.setColumnWidth(count, 7 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版单位");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "精品课程建设情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("课程名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("课程级别");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("全年课时");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "主编国家级规划教材情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("教材名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("标准书号");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("教材级别");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "人卫社教材编写情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 5);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("教材名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("级别");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("编写职务");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("是否数字编辑");
                            sheet.setColumnWidth(count, 7 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("标准书号");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "其他社教材编写情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 6);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("教材名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("级别");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("编写职务");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("是否数字编辑");
                            sheet.setColumnWidth(count, 7 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版社");
                            sheet.setColumnWidth(count, 4 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("标准书号");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "参加人卫慕课、数字教材编写情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r2.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 1, count, count);
                            sheet.setColumnWidth(count, 15 * 512);
                            sheet.addMergedRegion(region);
                            count++;
                            break;
                        }
                    case "科研情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("课题名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("审批单位");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("获奖情况");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "学术专著":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 4);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("专著名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("专著发表日期");
                            sheet.setColumnWidth(count, 7 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版方式");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版单位");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("出版时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "出版行业获奖情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("奖项名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("评奖单位");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("获奖时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "SCI论文投稿及影响因子情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 3);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("论文名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("期刊名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("期刊SCI影响因子");
                            sheet.setColumnWidth(count, 9 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("发表时间");
                            sheet.setColumnWidth(count, 3 * 512);
                            count++;
                            break;
                        }
                    case "临床医学获奖情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("奖项名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("奖项级别");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("获奖时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "学术荣誉授予情况":
                        {
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 0, count, count + 2);
                            sheet.addMergedRegion(region);
                            Cell r2cell = r2.createCell(count);
                            r2cell.setCellValue("荣誉名称");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("荣誉级别");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            r2cell = r2.createCell(count);
                            r2cell.setCellValue("授予时间");
                            sheet.setColumnWidth(count, 5 * 512);
                            count++;
                            break;
                        }
                    case "编写内容意向":
                        {
                            Cell r1cell = r1.createCell(count);
                            r2.createCell(count);
                            r1cell.setCellValue(headerName);
                            region = new CellRangeAddress(0, 1, count, count);
                            sheet.setColumnWidth(count, 15 * 512);
                            sheet.addMergedRegion(region);
                            count++;
                            break;
                        }
                    case "作家扩展项":
                        {
                            if (extensions == null || extensions.isEmpty()) {
                                break;
                            }
                            Cell r1cell = r1.createCell(count);
                            r1cell.setCellValue(headerName);
                            if (extensions.size() > 1) {
                                region = new CellRangeAddress(0, 0, count, count + extensions.size() - 1);
                                sheet.addMergedRegion(region);
                            }
                            for (MaterialExtension extension : extensions) {
                                Cell cell = r2.createCell(count);
                                String extensionName = extension.getExtensionName() == null ? "" : extension.getExtensionName();
                                cell.setCellValue(extensionName);
                                int length = extension.getExtensionName().length() > 10 ? 10 : extension.getExtensionName().length();
                                sheet.setColumnWidth(count, length * 512);
                                count++;
                            }
                            // count++;
                            break;
                        }
                    default:
                        Cell cell = r1.createCell(count);
                        r2.createCell(count);
                        // 设置基本列宽度
                        sheet.setColumnWidth(count, (headerName.length() + 1) * 512);
                        cell.setCellValue(headerName);
                        region = new CellRangeAddress(0, 1, count, count);
                        sheet.addMergedRegion(region);
                        count++;
                        break;
                }
            }
        }
    }
    return sheet;
}

Example 94 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project jgnash by ccavanaugh.

the class BudgetResultsExport method exportBudgetResultsModel.

/**
     * Exports a {@code BudgetResultsModel} to a spreadsheet.
     * 
     * @param file File to save to
     * @param model Results model to export
     * @return Error message
     */
public static String exportBudgetResultsModel(final Path file, final BudgetResultsModel model) {
    String message = null;
    final ResourceBundle rb = ResourceUtils.getBundle();
    final String extension = FileUtils.getFileExtension(file.toString());
    try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
        final CreationHelper createHelper = wb.getCreationHelper();
        // create a new sheet
        final Sheet s = wb.createSheet(model.getBudget().getName());
        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();
        // create 2 fonts objects
        final Font amountFont = wb.createFont();
        final Font headerFont = wb.createFont();
        amountFont.setFontHeightInPoints((short) 10);
        amountFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        // Set the other cell style and formatting
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        DataFormat df_header = wb.createDataFormat();
        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        int row = 0;
        Row r = s.createRow(row);
        // create period headers
        for (int i = 0; i < model.getDescriptorList().size(); i++) {
            Cell c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
            c.setCellStyle(headerStyle);
            s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
        }
        {
            int col = model.getDescriptorList().size() * 3 + 1;
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
            c.setCellStyle(headerStyle);
            s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
        }
        // create results header columns
        row++;
        r = s.createRow(row);
        {
            Cell c = r.createCell(0);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
            c.setCellStyle(headerStyle);
            for (int i = 0; i <= model.getDescriptorList().size(); i++) {
                c = r.createCell(i * 3 + 1);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
                c.setCellStyle(headerStyle);
                c = r.createCell(i * 3 + 2);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Actual")));
                c.setCellStyle(headerStyle);
                c = r.createCell(i * 3 + 3);
                c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
                c.setCellStyle(headerStyle);
            }
        }
        // must sort the accounts, otherwise child structure is not correct
        List<Account> accounts = new ArrayList<>(model.getAccounts());
        accounts.sort(Comparators.getAccountByTreePosition(Comparators.getAccountByCode()));
        // create account rows
        for (final Account account : accounts) {
            final DataFormat df = wb.createDataFormat();
            final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
            final String pattern = format.toLocalizedPattern().replace("¤", account.getCurrencyNode().getPrefix());
            final CellStyle amountStyle = wb.createCellStyle();
            amountStyle.setFont(amountFont);
            amountStyle.setDataFormat(df.getFormat(pattern));
            // Sets cell indentation, only impacts display if users changes the cell formatting to be left aligned.
            amountStyle.setIndention((short) (model.getDepth(account) * 2));
            row++;
            int col = 0;
            r = s.createRow(row);
            CellStyle cs = wb.createCellStyle();
            cs.cloneStyleFrom(headerStyle);
            cs.setAlignment(CellStyle.ALIGN_LEFT);
            cs.setIndention((short) (model.getDepth(account) * 2));
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(account.getName()));
            c.setCellStyle(cs);
            List<CellReference> budgetedRefList = new ArrayList<>();
            List<CellReference> changeRefList = new ArrayList<>();
            List<CellReference> remainingRefList = new ArrayList<>();
            for (int i = 0; i < model.getDescriptorList().size(); i++) {
                BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getBudgeted().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference budgetedRef = new CellReference(row, col);
                budgetedRefList.add(budgetedRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getChange().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference changeRef = new CellReference(row, col);
                changeRefList.add(changeRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_FORMULA);
                c.setCellStyle(amountStyle);
                c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
                CellReference remainingRef = new CellReference(row, col);
                remainingRefList.add(remainingRef);
            }
            // add summary columns                               
            addSummaryCell(r, ++col, budgetedRefList, amountStyle);
            addSummaryCell(r, ++col, changeRefList, amountStyle);
            addSummaryCell(r, ++col, remainingRefList, amountStyle);
        }
        // add group summary rows
        for (final AccountGroup group : model.getAccountGroupList()) {
            final DataFormat df = wb.createDataFormat();
            final CellStyle amountStyle = wb.createCellStyle();
            amountStyle.setFont(amountFont);
            amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
            amountStyle.setBorderTop(CellStyle.BORDER_THIN);
            amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
            amountStyle.setBorderRight(CellStyle.BORDER_THIN);
            final DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
            final String pattern = format.toLocalizedPattern().replace("¤", model.getBaseCurrency().getPrefix());
            amountStyle.setDataFormat(df.getFormat(pattern));
            row++;
            int col = 0;
            r = s.createRow(row);
            CellStyle cs = wb.createCellStyle();
            cs.cloneStyleFrom(headerStyle);
            cs.setAlignment(CellStyle.ALIGN_LEFT);
            Cell c = r.createCell(col);
            c.setCellValue(createHelper.createRichTextString(group.toString()));
            c.setCellStyle(cs);
            List<CellReference> budgetedRefList = new ArrayList<>();
            List<CellReference> changeRefList = new ArrayList<>();
            List<CellReference> remainingRefList = new ArrayList<>();
            for (int i = 0; i < model.getDescriptorList().size(); i++) {
                BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getBudgeted().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference budgetedRef = new CellReference(row, col);
                budgetedRefList.add(budgetedRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(results.getChange().doubleValue());
                c.setCellStyle(amountStyle);
                CellReference changeRef = new CellReference(row, col);
                changeRefList.add(changeRef);
                c = r.createCell(++col);
                c.setCellType(Cell.CELL_TYPE_FORMULA);
                c.setCellStyle(amountStyle);
                c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());
                CellReference remainingRef = new CellReference(row, col);
                remainingRefList.add(remainingRef);
            }
            // add summary columns                               
            addSummaryCell(r, ++col, budgetedRefList, amountStyle);
            addSummaryCell(r, ++col, changeRefList, amountStyle);
            addSummaryCell(r, ++col, remainingRefList, amountStyle);
        }
        // force evaluation
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();
        short columnCount = s.getRow(1).getLastCellNum();
        // autosize all of the columns + 10 pixels
        for (int i = 0; i <= columnCount; i++) {
            s.autoSizeColumn(i);
            s.setColumnWidth(i, s.getColumnWidth(i) + 10);
        }
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.INFO, "{0} cell styles were used", wb.getNumCellStyles());
        // Save
        String filename = file.toString();
        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(filename) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(filename) + ".xls";
        }
        try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
            message = e.getLocalizedMessage();
        }
    } catch (IOException e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
    return message;
}

Example 95 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project Gargoyle by callakrsos.

the class FxExcelUtil method createExcel.

/**
	 *  엑셀파일을 생성한다.
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 6.
	 *  
	 * @param exportExcelFile 
	 *   	export하려는 파일 확장자는 .xlsx 사용 권고
	 *   
	 * @param dataSource 
	 *      데이터셋.  개발자 혼동(순서)을 피하기 위해 LinkedMap(순서유지)을 강제적으로 사용
	 *      값은 Map<Sheet명, Map<컬럼메타,List<값>> 순 매핑이되어있다.
	 * 
	 * @param handler
	 *     데이터 컨버터, 데이터내에 특화가 필요한 처리가 필요한경우 구현하여 사용하도록한다.
	 *     
	 * @throws Exception
	 */
public static void createExcel(File exportExcelFile, LinkedHashMap<String, LinkedHashMap<ExcelColumnExpression, List<Object>>> dataSource, IExcelDataSetHandler<Sheet, LinkedHashMap<ExcelColumnExpression, List<Object>>> handler, Map<String, Map<String, String>> metadata, boolean overwrite) throws Exception {
    //파일이 이미 존재하는 상황에서 overrite하려는 경우 에러.
    if (exportExcelFile.exists() && !overwrite) {
        throw new GargoyleException(String.format("output File : {%s} already exists.!", exportExcelFile.getName()));
    }
    Workbook createNewWorkBookXlsx = createNewWorkBookXlsx();
    //시트를 의미하는 iterator.
    Iterator<String> iterator = dataSource.keySet().iterator();
    int sheetIndex = 0;
    while (iterator.hasNext()) {
        String sheetName = iterator.next();
        Sheet createSheet = createNewWorkBookXlsx.createSheet(sheetName);
        Map<String, String> meta = metadata.get(sheetName);
        if (meta == null || meta.isEmpty())
            continue;
        //각 헤더컬럼의 개수(높이)
        String columnMaxHeight = meta.get($$META_COLUMN_MAX_HEIGHT$$);
        int maxColumnRowSize = ValueUtil.decode(columnMaxHeight, val -> Integer.parseInt(val), () -> 0);
        LinkedHashMap<ExcelColumnExpression, List<Object>> linkedHashMap = dataSource.get(sheetName);
        createHeaders(handler, createSheet, linkedHashMap, maxColumnRowSize);
        //컬럼 + 데이터리스트로 이루어진 데이터셋
        LinkedHashMap<ExcelColumnExpression, List<Object>> dataSet = dataSource.get(sheetName);
        drawBody(handler, createSheet, maxColumnRowSize, dataSet);
        //(short) (createSheet.getRow(maxColumnRowSize + 1).getLastCellNum() - 1);
        short lastColumnNum = (short) dataSet.size();
        IExcelScreenHandler screenHandler = handler.getExcelScreenHandler();
        if (handler.isApplyAutoFit()) {
            for (int i = 0; i < lastColumnNum; i++) {
                createSheet.autoSizeColumn(i);
            }
        }
        if (screenHandler != null) {
            //사용자 정의 sheet 처리를 지원한다.
            screenHandler.customSheetHandle(sheetIndex, createSheet);
            sheetIndex++;
        }
        //로고 이미지 처리.
        Utils.createDefaultLogo(createSheet);
        createSheet.setAutoFilter(new CellRangeAddress((maxColumnRowSize), (maxColumnRowSize), START_COLUMN_INDEX, lastColumnNum));
        createSheet.createFreezePane(0, (maxColumnRowSize + 1));
    }
    //사용자 정의 workbook 처리를 지원함.
    IExcelScreenHandler excelScreenHandler = handler.getExcelScreenHandler();
    if (excelScreenHandler != null)
        excelScreenHandler.customWorkbookHandle(createNewWorkBookXlsx);
    //파일 write처리.
    try (FileOutputStream fileOutputStream = new FileOutputStream(exportExcelFile)) {
        createNewWorkBookXlsx.write(fileOutputStream);
    }
}