Example usage for org.apache.poi.ss.usermodel VerticalAlignment CENTER

List of usage examples for org.apache.poi.ss.usermodel VerticalAlignment CENTER

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. VerticalAlignment
  6. CENTER

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel VerticalAlignment CENTER.

Prototype

VerticalAlignment CENTER

To view the source code for org.apache.poi.ss.usermodel VerticalAlignment CENTER.

Click Source Link

Document

The vertical alignment is centered across the height of the cell.

Usage

From source file:com.catexpress.util.FormatosPOI.java

public CellStyle estiloHeader(Workbook wb, int tipo) {
    Font fuente = wb.createFont();
    CreationHelper createHelper = wb.getCreationHelper();
    fuente.setFontName("Calibri");
    fuente.setBold(true);/*from www  .  j  av  a2 s  .c om*/

    if (tipo == TITULO || tipo == SUCURSAL || tipo == LABEL || tipo == FECHA) {
        fuente.setFontHeightInPoints((short) 16);
    } else if (tipo == USUARIO) {
        fuente.setFontHeightInPoints((short) 20);
    }

    CellStyle estiloCelda = wb.createCellStyle();
    estiloCelda.setFont(fuente);

    if (tipo == TITULO || tipo == USUARIO || tipo == LABEL || tipo == FECHA) {
        estiloCelda.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    } else if (tipo == SUCURSAL) {
        estiloCelda.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    }
    estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND);

    if (tipo == LABEL || tipo == FECHA) {
        estiloCelda.setWrapText(false);
    } else if (tipo == TITULO || tipo == SUCURSAL || tipo == USUARIO) {
        estiloCelda.setWrapText(true);
    }

    estiloCelda.setAlignment(HorizontalAlignment.CENTER);

    if (tipo == LABEL || tipo == FECHA) {
        estiloCelda.setVerticalAlignment(VerticalAlignment.BOTTOM);
    } else if (tipo == TITULO || tipo == SUCURSAL || tipo == USUARIO) {
        estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER);
    }

    if (tipo == SUCURSAL) {
        estiloCelda.setBorderBottom(BorderStyle.MEDIUM);
        estiloCelda.setBorderTop(BorderStyle.MEDIUM);
        estiloCelda.setBorderLeft(BorderStyle.MEDIUM);
        estiloCelda.setBorderRight(BorderStyle.MEDIUM);
    }

    if (tipo == FECHA) {
        estiloCelda.setDataFormat(createHelper.createDataFormat().getFormat("dd \"de\" mmmm \"de\" yyyy"));
    }

    return estiloCelda;
}

From source file:com.funtl.framework.smoke.core.commons.excel.ExportExcel.java

License:Apache License

/**
 * ?/*w  w  w. j a v a  2s .  co  m*/
 *
 * @param wb 
 * @return ?
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    //      style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    //      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBold(true);
    //      titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    //      style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(BorderStyle.THIN);
    //      style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    //      style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    //      style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(BorderStyle.THIN);
    //      style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.LEFT);
    //      style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.CENTER);
    //      style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(HorizontalAlignment.RIGHT);
    //      style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    //      style.setWrapText(true);
    style.setAlignment(HorizontalAlignment.CENTER);
    //      style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //      style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBold(true);
    //      headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

/**
 * cell styles used for formatting calendar sheets
 *//*from  www  . j  a  va  2 s  . co  m*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 48);
    titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("month", style);

    Font dayFont = wb.createFont();
    dayFont.setFontHeightInPoints((short) 14);
    dayFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("weekend_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("weekend_right", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("workday_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("workday_right", style);

    style = wb.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_left", style);

    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_right", style);

    return styles;
}

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

public void exportTable(Table<Entity> table, List<Table.Column> columns, Boolean exportExpanded,
        ExportDisplay display, List<String> filterDescription, String fileName, ExportMode exportMode) {

    if (display == null) {
        throw new IllegalArgumentException("ExportDisplay is null");
    }/*  w  w  w. j  a  v a  2  s  .co m*/

    createWorkbookWithSheet();
    createFonts();
    createFormats();

    int r = 0;
    if (filterDescription != null) {
        for (r = 0; r < filterDescription.size(); r++) {
            String line = filterDescription.get(r);
            HSSFRow row = sheet.createRow(r);
            if (r == 0) {
                HSSFRichTextString richTextFilterName = new HSSFRichTextString(line);
                richTextFilterName.applyFont(boldFont);
                row.createCell(0).setCellValue(richTextFilterName);
            } else {
                row.createCell(0).setCellValue(line);
            }
        }
        r++;
    }
    HSSFRow row = sheet.createRow(r);
    createAutoColumnSizers(columns.size());

    float maxHeight = sheet.getDefaultRowHeightInPoints();

    CellStyle headerCellStyle = wb.createCellStyle();
    headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    for (Table.Column column : columns) {
        String caption = column.getCaption();

        int countOfReturnSymbols = StringUtils.countMatches(caption, "\n");
        if (countOfReturnSymbols > 0) {
            maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints());
            headerCellStyle.setWrapText(true);
        }
    }
    row.setHeightInPoints(maxHeight);

    for (int c = 0; c < columns.size(); c++) {
        Table.Column column = columns.get(c);
        String caption = column.getCaption();

        HSSFCell cell = row.createCell(c);
        HSSFRichTextString richTextString = new HSSFRichTextString(caption);
        richTextString.applyFont(boldFont);
        cell.setCellValue(richTextString);

        ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer();
        sizer.notifyCellValue(caption, boldFont);
        sizers[c] = sizer;

        cell.setCellStyle(headerCellStyle);
    }

    CollectionDatasource datasource = table.getDatasource();
    if (exportMode == ExportMode.SELECTED_ROWS && table.getSelected().size() > 0) {
        Set<Entity> selected = table.getSelected();
        List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains)
                .collect(Collectors.toList());
        for (Entity item : ordered) {
            createRow(table, columns, 0, ++r, item.getId());
        }
    } else {
        if (table instanceof TreeTable) {
            TreeTable treeTable = (TreeTable) table;
            HierarchicalDatasource ds = treeTable.getDatasource();
            if (table.isAggregatable()) {
                r = createAggregatableRow(table, columns, ++r, 1, datasource);
            }
            for (Object itemId : ds.getRootItemIds()) {
                r = createHierarhicalRow(treeTable, columns, exportExpanded, r, itemId);
            }
        } else if (table instanceof GroupTable && datasource instanceof GroupDatasource
                && ((GroupDatasource) datasource).hasGroups()) {
            GroupDatasource ds = (GroupDatasource) datasource;
            if (table.isAggregatable()) {
                r = createAggregatableRow(table, columns, ++r, 1, datasource);
            }
            for (Object item : ds.rootGroups()) {
                r = createGroupRow((GroupTable) table, columns, ++r, (GroupInfo) item, 0);
            }
        } else {
            if (table.isAggregatable()) {
                r = createAggregatableRow(table, columns, ++r, 1, datasource);
            }
            for (Object itemId : datasource.getItemIds()) {
                createRow(table, columns, 0, ++r, itemId);
            }
        }
    }

    for (int c = 0; c < columns.size(); c++) {
        sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC);
    }

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
    } catch (IOException e) {
        throw new RuntimeException("Unable to write document", e);
    }
    if (fileName == null) {
        fileName = messages.getTools().getEntityCaption(datasource.getMetaClass());
    }

    display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS);
}

From source file:com.haulmont.cuba.gui.export.ExcelExporter.java

License:Apache License

public void exportDataGrid(DataGrid<Entity> dataGrid, List<DataGrid.Column> columns, ExportDisplay display,
        List<String> filterDescription, String fileName, ExportMode exportMode) {
    if (display == null) {
        throw new IllegalArgumentException("ExportDisplay is null");
    }/*ww w .j a  va2s. c om*/

    createWorkbookWithSheet();
    createFonts();
    createFormats();

    int r = 0;
    if (filterDescription != null) {
        for (r = 0; r < filterDescription.size(); r++) {
            String line = filterDescription.get(r);
            HSSFRow row = sheet.createRow(r);
            if (r == 0) {
                HSSFRichTextString richTextFilterName = new HSSFRichTextString(line);
                richTextFilterName.applyFont(boldFont);
                row.createCell(0).setCellValue(richTextFilterName);
            } else {
                row.createCell(0).setCellValue(line);
            }
        }
        r++;
    }
    HSSFRow row = sheet.createRow(r);
    createAutoColumnSizers(columns.size());

    float maxHeight = sheet.getDefaultRowHeightInPoints();

    CellStyle headerCellStyle = wb.createCellStyle();
    headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    for (DataGrid.Column column : columns) {
        String caption = column.getCaption();

        int countOfReturnSymbols = StringUtils.countMatches(caption, "\n");
        if (countOfReturnSymbols > 0) {
            maxHeight = Math.max(maxHeight, (countOfReturnSymbols + 1) * sheet.getDefaultRowHeightInPoints());
            headerCellStyle.setWrapText(true);
        }
    }
    row.setHeightInPoints(maxHeight);

    for (int c = 0; c < columns.size(); c++) {
        DataGrid.Column column = columns.get(c);
        String caption = column.getCaption();

        HSSFCell cell = row.createCell(c);
        HSSFRichTextString richTextString = new HSSFRichTextString(caption);
        richTextString.applyFont(boldFont);
        cell.setCellValue(richTextString);

        ExcelAutoColumnSizer sizer = new ExcelAutoColumnSizer();
        sizer.notifyCellValue(caption, boldFont);
        sizers[c] = sizer;

        cell.setCellStyle(headerCellStyle);
    }

    CollectionDatasource datasource = dataGrid.getDatasource();
    if (exportMode == ExportMode.SELECTED_ROWS && dataGrid.getSelected().size() > 0) {
        Set<Entity> selected = dataGrid.getSelected();
        List<Entity> ordered = ((Collection<Entity>) datasource.getItems()).stream().filter(selected::contains)
                .collect(Collectors.toList());
        for (Entity item : ordered) {
            createDataGridRow(dataGrid, columns, 0, ++r, item.getId());
        }
    } else {
        for (Object itemId : datasource.getItemIds()) {
            createDataGridRow(dataGrid, columns, 0, ++r, itemId);
        }
    }

    for (int c = 0; c < columns.size(); c++) {
        sheet.setColumnWidth(c, sizers[c].getWidth() * COL_WIDTH_MAGIC);
    }

    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        wb.write(out);
    } catch (IOException e) {
        throw new RuntimeException("Unable to write document", e);
    }
    if (fileName == null) {
        fileName = messages.getTools().getEntityCaption(datasource.getMetaClass());
    }

    display.show(new ByteArrayDataProvider(out.toByteArray()), fileName + ".xls", ExportFormat.XLS);
}

From source file:com.l3.info.magenda.emplois_du_temps.Workbook.java

public void addCellStyle(String nom, Categorie cat) {
    XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
    XSSFColor border = new XSSFColor(cat.getBorder());
    style.setBorderBottom(BorderStyle.MEDIUM);
    style.setBottomBorderColor(border);/*from   www. j a  va  2s  .  c o m*/

    style.setBorderLeft(BorderStyle.MEDIUM);
    style.setLeftBorderColor(border);

    style.setBorderRight(BorderStyle.MEDIUM);
    style.setRightBorderColor(border);

    style.setBorderTop(BorderStyle.MEDIUM);
    style.setTopBorderColor(border);

    style.setWrapText(true);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(new XSSFColor(cat.getBackground()));

    XSSFFont font = workbook.createFont();
    font.setColor(new XSSFColor(cat.getForeground()));
    style.setFont(font);

    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    liste_des_styles.put("Style_" + nom, style);

}

From source file:com.lufs.java.apache.poi.example.CalendarDemo.java

License:Apache License

/**
 * cell styles used for formatting calendar sheets
 *///  ww w.ja va2  s .c  o  m
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 48);
    titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("month", style);

    Font dayFont = wb.createFont();
    dayFont.setFontHeightInPoints((short) 14);
    dayFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("weekend_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("weekend_right", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("workday_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("workday_right", style);

    style = wb.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_left", style);

    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_right", style);

    return styles;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Row headRow = sh.createRow(row);/*from   w  ww  . ja  va  2  s  .  co  m*/
    headRow.setHeight((short) 700);

    int cell = 0;

    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getBgColor()));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getFontColor()));

    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    int cols = 12;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 4000);
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(
                vision.getTitle() + "\nscore: " + BscReportSupportUtils.parse2(vision.getScore()));
        headCell1.setCellStyle(cellHeadStyle);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    // ------------------------------------------------------------------------
    bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor()));
    fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor()));

    cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);

    row++;
    headRow = sh.createRow(row);
    cell = 0;
    int titleCols = 4;
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getKpiTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
    sh.addMergedRegion(new CellRangeAddress(row, row, 4, 7));
    sh.addMergedRegion(new CellRangeAddress(row, row, 8, 11));

    // ------------------------------------------------------------------------

    return 2;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Map<String, String> managementMap = BscKpiCode.getManagementMap(false);
    //Map<String, String> calculationMap = BscKpiCode.getCalculationMap(false);
    int itemCols = 4;
    int mrRow = row;
    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);

        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);

            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                KpiVO kpi = objective.getKpis().get(kx);

                Row contentRow = sh.createRow(row++);
                contentRow.setHeight((short) 4000);

                int cell = 0;

                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(perspective.getName(), perspective.getScore(),
                            perspective.getWeight(), perspective.getTarget(), perspective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(/*from   w  ww .ja v  a  2  s  .  com*/
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0 && ox == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("PERSPECTIVES",
                                perspective.getTarget(), perspective.getMin(), perspective.getScore(), "", "",
                                0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }
                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(objective.getName(), objective.getScore(),
                            objective.getWeight(), objective.getTarget(), objective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0 && kx == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("OBJECTIVES",
                                objective.getTarget(), objective.getMin(), objective.getScore(), "", "", 0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }
                for (int i = 0; i < itemCols; i++) {
                    //String content = this.getKpisContent(kpi, managementMap, calculationMap);
                    String content = this.getKpisContent(kpi, managementMap);
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("KPI", kpi.getTarget(),
                                kpi.getMin(), kpi.getScore(), kpi.getCompareType(), kpi.getManagement(),
                                kpi.getQuasiRange());
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }

            }

        }

    }

    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);
        sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow() - 1, 0, 3));

        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);
            sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow() - 1, 4, 7));

            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                sh.addMergedRegion(new CellRangeAddress(mrRow + kx, mrRow + kx, 8, 11));
            }

            mrRow += objective.getKpis().size();
        }

    }

    return row++;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createDateRange(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {
    String frequency = (String) context.get("frequency");
    String startYearDate = StringUtils.defaultString((String) context.get("startYearDate")).trim();
    String endYearDate = StringUtils.defaultString((String) context.get("endYearDate")).trim();
    String startDate = StringUtils.defaultString((String) context.get("startDate")).trim();
    String endDate = StringUtils.defaultString((String) context.get("endDate")).trim();
    String date1 = startDate;//from w  w  w .  j  a va  2 s  .co m
    String date2 = endDate;
    if (BscMeasureDataFrequency.FREQUENCY_QUARTER.equals(frequency)
            || BscMeasureDataFrequency.FREQUENCY_HALF_OF_YEAR.equals(frequency)
            || BscMeasureDataFrequency.FREQUENCY_YEAR.equals(frequency)) {
        date1 = startYearDate + "/01/01";
        date2 = endYearDate + "/12/" + SimpleUtils.getMaxDayOfMonth(Integer.parseInt(endYearDate), 12);
    }
    Map<String, Object> headContentMap = new HashMap<String, Object>();
    this.fillHeadContent(context, headContentMap);

    XSSFCellStyle cellStyleLabel = wb.createCellStyle();
    cellStyleLabel.setFillForegroundColor(
            new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor())));
    cellStyleLabel.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFFont cellFontLabel = wb.createFont();
    cellFontLabel.setBold(false);
    cellFontLabel
            .setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor())));
    cellStyleLabel.setFont(cellFontLabel);
    cellStyleLabel.setWrapText(true);
    //cellStyleLabel.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyleLabel.setBorderBottom(BorderStyle.THIN);
    cellStyleLabel.setBorderTop(BorderStyle.THIN);
    cellStyleLabel.setBorderRight(BorderStyle.THIN);
    cellStyleLabel.setBorderLeft(BorderStyle.THIN);

    int cols = 4 + vision.getPerspectives().get(0).getObjectives().get(0).getKpis().get(0).getDateRangeScores()
            .size();
    int cell = 0;
    for (int i = 0; i < cols; i++) {
        String content = "Frequency: " + BscMeasureDataFrequency.getFrequencyMap(false).get(frequency)
                + " Date range: " + date1 + " ~ " + date2 + "\n"
                + StringUtils.defaultString((String) headContentMap.get("headContent"));
        Row headRow = sh.createRow(row);
        headRow.setHeight((short) 700);
        Cell headCell1 = headRow.createCell(cell);
        headCell1.setCellValue(content);
        headCell1.setCellStyle(cellStyleLabel);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;

    int kpiCols = 4;
    int kpiRows = 2;
    for (PerspectiveVO perspective : vision.getPerspectives()) {
        for (ObjectiveVO objective : perspective.getObjectives()) {
            for (KpiVO kpi : objective.getKpis()) {
                cell = 0;

                for (int r = 0; r < kpiRows; r++) {
                    Row contentRow = sh.createRow(row++);
                    contentRow.setHeight((short) 400);

                    for (int c = 0; c < kpiCols; c++) {
                        XSSFCellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setFillForegroundColor(
                                new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        XSSFFont cellFont = wb.createFont();
                        cellFont.setBold(false);
                        cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                        cellStyle.setFont(cellFont);
                        cellStyle.setWrapText(true);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        Cell contentCell1 = contentRow.createCell(c);
                        contentCell1.setCellValue(kpi.getName());
                        contentCell1.setCellStyle(cellStyle);

                    }

                    cell = 4;
                    if (r == 0) { // date

                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(dateRangeScore.getDate());
                            contentCell1.setCellStyle(cellStyle);
                        }

                    }
                    if (r == 1) { // score

                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(
                                    "      " + BscReportSupportUtils.parse2(dateRangeScore.getScore()));
                            contentCell1.setCellStyle(cellStyle);

                            byte[] imgBytes = BscReportSupportUtils.getByteIcon(kpi, dateRangeScore.getScore());
                            if (null != imgBytes) {
                                SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                        contentCell1.getColumnIndex());
                            }

                        }

                    }

                }

                sh.addMergedRegion(new CellRangeAddress(row - 2, row - 1, 0, kpiCols - 1));

            }
        }
    }

    return row++;
}