Example usage for org.apache.poi.ss.usermodel Cell setCellStyle

List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellStyle

Introduction

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

Prototype

void setCellStyle(CellStyle style);

Source Link

Document

Set the style for the cell.

Usage

From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java

License:Open Source License

public void writeRange(List<Marker> markers, Sheet sheet, Row valueRow, List<Value> range) {

    CreationHelper createHelper = this.getWorkBook().getCreationHelper();
    CellStyle cellStyle = this.getWorkBook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm"));

    // Styles for markers.
    // CellStyle markerStyleRed = this.getWorkBook().createCellStyle();
    // markerStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
    // markerStyleRed.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle markerStyleRed = createRedBorderStyle();
    CellStyle markerStyleAmber = createAmberBorderStyle();

    // CellStyle markerStyleAmber = this.getWorkBook().createCellStyle();
    // markerStyleAmber.setFillPattern(CellStyle.SOLID_FOREGROUND);
    // markerStyleAmber
    // .setFillForegroundColor(IndexedColors.ORANGE.getIndex());

    // Write the values.
    for (Value v : range) {

        // lookup the value.
        int valueIndex = tsColumnForValue(v);
        if (valueIndex == -1) {
            continue;
        }/*ww  w  . j a v a 2 s  .c om*/

        Cell valueCell = valueRow.createCell(valueIndex);
        valueCell.setCellValue(v.getValue());

        // Adapt the width of the column for this value.
        sheet.setColumnWidth(valueIndex, 14 * 256);

        // Set the markers.
        if (markers != null) {
            Marker m;
            if ((m = StudioUtils.markerForValue(markers, v)) != null) {
                if (m instanceof ToleranceMarker) {
                    switch (((ToleranceMarker) m).getLevel().getValue()) {
                    case LevelKind.RED_VALUE: {
                        valueCell.setCellStyle(markerStyleRed);
                    }
                        break;
                    case LevelKind.AMBER_VALUE: {
                        valueCell.setCellStyle(markerStyleAmber);
                    }
                        break;
                    }
                }
            }
        }
    }
}

From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java

License:Open Source License

/**
 * Has a side effect of populating the columnTSMap with the index and date.
 * // w ww  . j a v a  2 s.com
 */
public int writeTS(Map<Integer, Date> columnTSMap, Sheet sheet, Row tsRow, List<Date> range, int weekNumber,
        int columnIndex) {

    CreationHelper createHelper = this.getWorkBook().getCreationHelper();
    CellStyle dateStyle = this.getWorkBook().createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm"));

    // Write the values.
    int valueIndex = columnIndex;
    for (Date d : range) {
        Cell tsCell = tsRow.createCell(valueIndex);
        // sheet.setColumnWidth(valueIndex, 14 * 256);
        tsCell.setCellValue(d);
        tsCell.setCellStyle(dateStyle);
        valueIndex++;
        columnTS.put(valueIndex, d);
    }
    return valueIndex;
}

From source file:com.netxforge.netxstudio.server.logic.reporting.RFSServiceDashboardReportingLogic.java

License:Open Source License

/**
 * Write each Node per NodeType column, starting
 *///from   w w  w.jav a 2 s .c o m
@Override
protected void writeContent(Sheet sheet, Service service, Node node, int row, int column) {

    // Write the NODE.ID box.
    int newRow = NODE_ROW + (row * NODE_HEIGHT);
    int nodeColumn = NODE_COLUMN + (column * NODE_WIDTH);

    sheet.setColumnWidth(nodeColumn, 10 * 256);

    CellStyle nodeStyle = this.getWorkBook().createCellStyle();
    nodeStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
    nodeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    nodeStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
    nodeStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    nodeStyle.setAlignment(CellStyle.ALIGN_CENTER);
    nodeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    {
        Row cellRow = sheet.getRow(newRow);
        if (cellRow == null) {
            cellRow = sheet.createRow(newRow);
        }
        Cell c1 = cellRow.createCell(nodeColumn);
        c1.setCellValue(node.getNodeID());
        c1.setCellStyle(nodeStyle);
    }
    {
        Row cellRow = sheet.getRow(newRow + 1);
        if (cellRow == null) {
            cellRow = sheet.createRow(newRow + 1);
        }
        Cell c1 = cellRow.createCell(nodeColumn);
        c1.setCellStyle(nodeStyle);
    }
    {
        Row cellRow = sheet.getRow(newRow + 2);
        if (cellRow == null) {
            cellRow = sheet.createRow(newRow + 2);
        }
        Cell c1 = cellRow.createCell(nodeColumn);
        c1.setCellStyle(nodeStyle);
    }

    sheet.addMergedRegion(new CellRangeAddress(newRow, newRow + NODE_HEIGHT - 2, nodeColumn, nodeColumn));

    // In between column.
    sheet.setColumnWidth(nodeColumn + 1, 2 * 256);

    // Write the RAG

    CellStyle ragStyle = this.getWorkBook().createCellStyle();

    ragStyle.setBorderTop(CellStyle.BORDER_THIN);
    ragStyle.setBorderBottom(CellStyle.BORDER_THIN);
    ragStyle.setBorderLeft(CellStyle.BORDER_THIN);
    ragStyle.setBorderRight(CellStyle.BORDER_THIN);
    ragStyle.setAlignment(CellStyle.ALIGN_CENTER);
    ragStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    ragStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    int ragColumn = nodeColumn + 2;
    sheet.setColumnWidth(ragColumn, 2 * 256);

    IMonitoringSummary summary = monStateModel.summary(new NullProgressMonitor(), node,
            new IComputationContext[] { new ObjectContext<Service>(service),
                    new ObjectContext<DateTimeRange>(getPeriod()) });
    if (summary == null) {
        return;
    }
    int[] rag = summary.rag();
    {
        Row cellRow = sheet.getRow(newRow);
        if (cellRow == null) {
            cellRow = sheet.createRow(newRow);
        }
        Cell c1 = cellRow.createCell(ragColumn);

        c1.setCellValue("R");

        CellStyle rStyle = this.getWorkBook().createCellStyle();
        rStyle.cloneStyleFrom(ragStyle);
        rStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        c1.setCellStyle(rStyle);

        if (rag != null) {
            c1.setCellValue(rag[0]);
        }
    }
    {
        Row cellRow = sheet.getRow(newRow + 1);
        if (cellRow == null) {
            cellRow = sheet.createRow(newRow + 1);
        }
        Cell c1 = cellRow.createCell(ragColumn);

        c1.setCellValue("A");

        CellStyle aStyle = this.getWorkBook().createCellStyle();
        aStyle.cloneStyleFrom(ragStyle);
        aStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        c1.setCellStyle(aStyle);

        if (rag != null) {
            c1.setCellValue(rag[1]);
        }
    }
    {
        Row cellRow = sheet.getRow(newRow + 2);
        if (cellRow == null) {
            cellRow = sheet.createRow(newRow + 2);
        }
        Cell c1 = cellRow.createCell(ragColumn);
        c1.setCellValue("G");
        CellStyle gStyle = this.getWorkBook().createCellStyle();
        gStyle.cloneStyleFrom(ragStyle);
        gStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        c1.setCellStyle(gStyle);

        if (rag != null) {
            c1.setCellValue(rag[2]);
        }

    }
    // Clean our adapted summary.
    node.eAdapters().remove(summary);

}

From source file:com.netxforge.netxstudio.server.logic.reporting.RFSServiceSummaryReportingLogic.java

License:Open Source License

private void writeSummary(Sheet sheet) {

    // Title//from   w  ww  . j  a  v a  2 s .co m
    Row summaryRow = sheet.createRow(CONTENT_ROW);
    Cell summaryCell = summaryRow.createCell(2);
    summaryCell.setCellValue("Executive Summary");
    sheet.addMergedRegion(new CellRangeAddress(CONTENT_ROW, CONTENT_ROW, 2, 4));

    // Table
    CellStyle borderStyle = this.getWorkBook().createCellStyle();
    borderStyle.setBorderTop(CellStyle.BORDER_THIN);
    borderStyle.setBorderBottom(CellStyle.BORDER_THIN);
    borderStyle.setBorderLeft(CellStyle.BORDER_THIN);
    borderStyle.setBorderRight(CellStyle.BORDER_THIN);
    borderStyle.setAlignment(CellStyle.ALIGN_CENTER);
    borderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    Row headerRow = sheet.createRow(HEADER_ROW);
    {
        Cell c1 = headerRow.createCell(4);
        c1.setCellValue("Quantity");
        c1.setCellStyle(borderStyle);
    }

    {
        Cell c1 = headerRow.createCell(5);
        c1.setCellValue("RED");
        c1.setCellStyle(borderStyle);
    }

    {
        Cell c1 = headerRow.createCell(6);
        c1.setCellValue("AMBER");
        c1.setCellStyle(borderStyle);
    }

    {
        Cell c1 = headerRow.createCell(7);
        c1.setCellValue("GREEN");
        c1.setCellStyle(borderStyle);
    }

    writeServicesSummary(sheet, borderStyle);
    writeNodesSummary(sheet, borderStyle);
    writeResourcesSummary(sheet, borderStyle);

}

From source file:com.netxforge.netxstudio.server.logic.reporting.RFSServiceSummaryReportingLogic.java

License:Open Source License

private void writeServicesSummary(Sheet sheet, CellStyle borderStyle) {
    Row servicesRow = sheet.createRow(SERVICES_ROW);

    {//from  w  w  w  .  j a  v  a2  s.c om
        Cell c1 = servicesRow.createCell(2);
        c1.setCellValue("#Services");

    }
    { // QUANTITY
        Cell c1 = servicesRow.createCell(4);
        c1.setCellStyle(borderStyle);

        // CB TODO.
        // c1.setCellValue(opSummary.totalServices());
        this.getServices().size();
    }

    { // RED
        Cell c1 = servicesRow.createCell(5);
        c1.setCellStyle(borderStyle);
        c1.setCellValue(opSummary.totalRag(RAG.RED));
    }

    { // AMBER
        Cell c1 = servicesRow.createCell(6);
        c1.setCellStyle(borderStyle);
        c1.setCellValue(opSummary.totalRag(RAG.AMBER));
    }

    { // GREEN
        Cell c1 = servicesRow.createCell(7);
        c1.setCellStyle(borderStyle);
        c1.setCellValue(opSummary.totalRag(RAG.GREEN));
    }
}

From source file:com.netxforge.netxstudio.server.logic.reporting.RFSServiceSummaryReportingLogic.java

License:Open Source License

private void writeNodesSummary(Sheet sheet, CellStyle borderStyle) {
    Row nodesRow = sheet.createRow(NODES_ROW);

    {/*  w  w w  .j  ava 2 s  .c  om*/
        Cell c1 = nodesRow.createCell(2);
        c1.setCellValue("#Nodes");
    }
    {
        Cell c1 = nodesRow.createCell(4);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNodes());
    }

    {
        Cell c1 = nodesRow.createCell(5);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNodeRag(RAG.RED));
    }

    {
        Cell c1 = nodesRow.createCell(6);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNodeRag(RAG.AMBER));
    }

    {
        Cell c1 = nodesRow.createCell(7);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNodeRag(RAG.GREEN));
    }
}

From source file:com.netxforge.netxstudio.server.logic.reporting.RFSServiceSummaryReportingLogic.java

License:Open Source License

private void writeResourcesSummary(Sheet sheet, CellStyle borderStyle) {
    Row resourcesRow = sheet.createRow(RESOURCES_ROW);

    {/*from   w ww.ja  v a  2s .  c o m*/
        Cell c1 = resourcesRow.createCell(2);
        c1.setCellValue("#Resources");
    }
    {
        Cell c1 = resourcesRow.createCell(4);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalResources());
    }

    {
        Cell c1 = resourcesRow.createCell(5);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNetXResourceRag(RAG.RED));
    }

    {
        Cell c1 = resourcesRow.createCell(6);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNetXResourceRag(RAG.AMBER));
    }

    {
        Cell c1 = resourcesRow.createCell(7);
        c1.setCellStyle(borderStyle);
        // CB TODO
        // c1.setCellValue(opSummary.totalNetXResourceRag(RAG.GREEN));
    }
}

From source file:com.oneis.jsinterface.generate.KGenerateXLS.java

License:Mozilla Public License

@Override
protected void writeRow(int rowNumber, ArrayList<Object> row, ArrayList<Object> rowOptions, boolean isHeaderRow,
        boolean pageBreakBefore) {
    Row r = this.sheet.createRow(rowNumber);

    if (pageBreakBefore && rowNumber > 0) {
        this.sheet.setRowBreak(rowNumber - 1);
    }//w  w w  . j  a  v a 2  s  .  c om

    int rowSize = row.size();
    for (int i = 0; i < rowSize; ++i) {
        Object value = row.get(i); // ConsString is checked
        if (value != null) {
            Cell c = r.createCell(i);
            if (value instanceof Number) {
                c.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof CharSequence) {
                c.setCellValue(((CharSequence) value).toString());
            } else if (value instanceof Date) {
                c.setCellValue((Date) value);
                // Check to see if option is for dates only
                boolean dateAndTimeStyle = true;
                String options = (String) getOptionsFromArrayList(rowOptions, i, String.class); // ConsString is checked by getOptionsFromArrayList()
                if (options != null && options.equals("date")) {
                    dateAndTimeStyle = false;
                }
                if (dateCellStyle == null) {
                    // Only create one each of the date cell styles per workbook to save space.
                    dateCellStyle = workbook.createCellStyle();
                    dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm"));
                    dateOnlyCellStyle = workbook.createCellStyle();
                    dateOnlyCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
                }
                c.setCellStyle(dateAndTimeStyle ? dateCellStyle : dateOnlyCellStyle);
                // Set column width so the dates don't come out as ########## on causal viewing
                setMinimumWidth(i, dateAndTimeStyle ? DATE_AND_TIME_COLUMN_WIDTH : DATE_COLUMN_WIDTH);
            }
        }
    }

    if (isHeaderRow) {
        // Make sure the row is always on screen
        this.sheet.createFreezePane(0, 1, 0, 1);
        // Style the row
        CellStyle style = this.workbook.createCellStyle();
        style.setBorderBottom(CellStyle.BORDER_THIN);
        Font font = this.workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setFont(font);
        r.setRowStyle(style);
        // Style the cells
        for (int s = 0; s < rowSize; ++s) {
            Cell c = r.getCell(s);
            if (c == null) {
                c = r.createCell(s);
            }
            c.setCellStyle(style);
        }
    }
}

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

/**
 * See http://thinktibits.blogspot.co.uk/2012/12/Java-POI-XLS-XLSX-Change-Cell-Font-Color-Example.html
 * Currently only for xlsx//from   ww w.ja  v  a2  s.co  m
 * @param wb
 * @param sheet
 */
private static void styleHeader(Workbook wb, Sheet sheet) {
    if (XSSFWorkbook.class.isInstance(wb) && XSSFSheet.class.isInstance(sheet)) {
        XSSFWorkbook my_workbook = (XSSFWorkbook) wb;
        XSSFCellStyle my_style = my_workbook.createCellStyle();
        XSSFFont my_font = my_workbook.createFont();
        my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        my_style.setFont(my_font);

        Row row = sheet.getRow(0);
        if (row != null && row.getFirstCellNum() >= 0) {
            for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(my_style);
                }
            }
        }
    }
}

From source file:com.opendoorlogistics.speedregions.excelshp.io.ExcelWriter.java

License:Apache License

public static void writeSheets(File file, ExportTable... tables) {
    // create empty workbook with a bold font style
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle headerStyle = wb.createCellStyle();
    XSSFFont boldfont = wb.createFont();
    boldfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    boldfont.setFontHeight(12);/*from  w w  w . ja  va 2 s.co  m*/
    headerStyle.setFont(boldfont);

    // fill workbook
    for (ExportTable table : tables) {
        Sheet sheet = wb.createSheet(table.getName());

        Row headerRow = sheet.createRow(0);
        for (int c = 0; c < table.getHeader().size(); c++) {
            Cell cell = headerRow.createCell(c);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(table.getHeader().get(c).getName());
        }

        List<List<String>> rows = table.getRows();

        int nr = rows.size();
        for (int r = 0; r < nr; r++) {
            Row row = sheet.createRow(r + 1);
            List<String> srcRow = rows.get(r);
            int nc = srcRow.size();
            for (int c = 0; c < nc; c++) {
                //JsonFormatTypes type = table.getColumnType(c);
                Cell cell = row.createCell(c);
                String value = srcRow.get(c);

                writeToCell(value, c < table.getHeader().size() ? table.getHeader().get(c).getFormatType()
                        : JsonFormatTypes.STRING, cell);

            }
        }
    }

    // try saving
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
    } catch (Exception e) {
        // TODO: handle exception
    } finally {

        try {
            if (fileOut != null) {
                fileOut.close();
            }
        } catch (Exception e2) {
            throw new RuntimeException(e2);
        }

        try {
            if (wb != null) {
                wb.close();
            }
        } catch (Exception e2) {
            throw new RuntimeException(e2);
        }
    }

    LOGGER.info("Wrote Excel file " + file.getAbsolutePath());
}