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

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

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

Introduction

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

Prototype

CellStyle getCellStyle();

Source Link

Document

Return the cell's style.

Usage

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java

License:Apache License

/**
 * Setup the data necessary for Excel Writer step
 *
 * @param fileType/*from ww w  . ja v  a  2 s  .c o m*/
 * @throws KettleException
 */
private void createStepData(String fileType) throws KettleException {
    stepData = new ExcelWriterStepData();
    stepData.inputRowMeta = step.getInputRowMeta().clone();
    stepData.outputRowMeta = step.getInputRowMeta().clone();

    // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed
    // we populate the ExcelWriterStepData with bare minimum required values
    CellReference cellRef = new CellReference(stepMeta.getStartingCell());
    stepData.startingRow = cellRef.getRow();
    stepData.startingCol = cellRef.getCol();
    stepData.posX = stepData.startingCol;
    stepData.posY = stepData.startingRow;

    int numOfFields = stepData.inputRowMeta.size();
    stepData.fieldnrs = new int[numOfFields];
    stepData.linkfieldnrs = new int[numOfFields];
    stepData.commentfieldnrs = new int[numOfFields];
    for (int i = 0; i < numOfFields; i++) {
        stepData.fieldnrs[i] = i;
        stepData.linkfieldnrs[i] = -1;
        stepData.commentfieldnrs[i] = -1;
    }

    // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed
    // create Excel workbook object
    stepData.wb = stepMeta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
    stepData.sheet = stepData.wb.createSheet();
    stepData.file = null;
    stepData.clearStyleCache(numOfFields);

    // we avoid reading template file from disk
    // so set beforehand cells with custom style and formatting
    DataFormat format = stepData.wb.createDataFormat();
    Row xlsRow = stepData.sheet.createRow(0);

    // Cell F1 has custom style applied, used as template
    Cell cell = xlsRow.createCell(5);
    CellStyle cellStyle = stepData.wb.createCellStyle();
    cellStyle.setBorderRight(BorderStyle.THICK);
    cellStyle.setFillPattern(FillPatternType.FINE_DOTS);
    cell.setCellStyle(cellStyle);

    // Cell G1 has same style, but also a custom data format
    cellStyle = stepData.wb.createCellStyle();
    cellStyle.cloneStyleFrom(cell.getCellStyle());
    cell = xlsRow.createCell(6);
    cellStyle.setDataFormat(format.getFormat("##0,000.0"));
    cell.setCellStyle(cellStyle);
}

From source file:org.pentaho.reporting.engine.classic.core.RotationTest.java

License:Open Source License

@Test
public void testXLS() throws ResourceException, IOException {

    URL url = getClass().getResource("BACKLOG-6818.prpt");
    final File testOutputFile = File.createTempFile("test", ".xls");
    MasterReport report = (MasterReport) new ResourceManager().createDirectly(url, MasterReport.class)
            .getResource();//  www .ja  va 2s.  c o  m

    try (FileOutputStream stream = new FileOutputStream(testOutputFile)) {
        ExcelReportUtil.createXLS(report, stream);
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(testOutputFile));
        assertNotNull(workbook);
        final HSSFSheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        final Iterator<Row> rowIterator = sheet.rowIterator();
        assertNotNull(rowIterator);
        final Row next = rowIterator.next();
        assertNotNull(next);
        int k = 1;
        for (int i = 0; i < 5; i++, k = -k) {
            final Cell cell = next.getCell(i);
            assertNotNull(cell);
            assertTrue(cell.getCellStyle().getRotation() == k * 90);
        }
        for (int i = 6; i < 9; i++, k = -k) {
            final Cell cell = next.getCell(i);
            assertNull(cell);
        }
    } catch (IOException | ReportProcessingException e) {
        fail();
    } finally {
        assertTrue(testOutputFile.delete());
    }
}

From source file:org.pentaho.reporting.engine.classic.core.RotationTest.java

License:Open Source License

@Test
public void testXLSX() throws ResourceException, IOException {

    URL url = getClass().getResource("BACKLOG-6818.prpt");
    final File testOutputFile = File.createTempFile("test", ".xlsx");
    MasterReport report = (MasterReport) new ResourceManager().createDirectly(url, MasterReport.class)
            .getResource();//from   w w  w.  j  a  v  a  2  s .  com

    try (FileOutputStream stream = new FileOutputStream(testOutputFile)) {
        ExcelReportUtil.createXLSX(report, stream);
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(testOutputFile));
        assertNotNull(workbook);
        final XSSFSheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        final Iterator<Row> rowIterator = sheet.rowIterator();
        assertNotNull(rowIterator);
        final Row next = rowIterator.next();
        assertNotNull(next);
        int k = 1;
        for (int i = 0; i < 5; i++, k = -k) {
            final Cell cell = next.getCell(i);
            assertNotNull(cell);
            assertTrue(cell.getCellStyle().getRotation() == (k > 0 ? 90 : 180));
        }
        for (int i = 6; i < 9; i++, k = -k) {
            final Cell cell = next.getCell(i);
            assertNull(cell);
        }
    } catch (IOException | ReportProcessingException e) {
        fail();
    } finally {
        assertTrue(testOutputFile.delete());
    }
}

From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java

License:Open Source License

private void importFromFile(final File file, final boolean firstRowIsHeader) {
    final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length()));
    try {//w  ww  .j  ava 2s .c o  m
        final InputStream fin = new FileInputStream(file);
        try {
            IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout);
        } finally {
            fin.close();
        }

        if (Thread.currentThread().isInterrupted()) {
            return;
        }

        final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
        int sheetIndex = 0;
        if (workbook.getNumberOfSheets() > 1) {
            final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent);
            if (selectorDialog.performSelection()) {
                sheetIndex = selectorDialog.getSelectedIndex();
            } else {
                return;
            }
        }

        final TypedTableModel tableModel = new TypedTableModel();
        final Sheet sheet = workbook.getSheetAt(sheetIndex);
        final Iterator rowIterator = sheet.rowIterator();

        if (firstRowIsHeader) {
            if (rowIterator.hasNext()) {
                final Row headerRow = (Row) rowIterator.next();
                final short cellCount = headerRow.getLastCellNum();
                for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                    final Cell cell = headerRow.getCell(colIdx);
                    if (cell != null) {
                        while (colIdx > tableModel.getColumnCount()) {
                            tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                    String.valueOf(tableModel.getColumnCount())), Object.class);
                        }

                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            tableModel.addColumn(string.getString(), Object.class);
                        } else {
                            tableModel.addColumn(
                                    Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)),
                                    Object.class);
                        }
                    }
                }
            }
        }

        Object[] rowData = null;
        while (rowIterator.hasNext()) {
            final Row row = (Row) rowIterator.next();
            final short cellCount = row.getLastCellNum();
            if (cellCount == -1) {
                continue;
            }
            if (rowData == null || rowData.length != cellCount) {
                rowData = new Object[cellCount];
            }

            for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                final Cell cell = row.getCell(colIdx);

                final Object value;
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            value = string.getString();
                        } else {
                            value = null;
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        final CellStyle hssfCellStyle = cell.getCellStyle();
                        final short dataFormat = hssfCellStyle.getDataFormat();
                        final String dataFormatString = hssfCellStyle.getDataFormatString();
                        if (isDateFormat(dataFormat, dataFormatString)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cell.getNumericCellValue();
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        value = cell.getBooleanCellValue();
                    } else {
                        value = cell.getStringCellValue();
                    }
                } else {
                    value = null;
                }

                if (value != null && "".equals(value) == false) {
                    while (colIdx >= tableModel.getColumnCount()) {
                        tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                String.valueOf(tableModel.getColumnCount())), Object.class);
                    }
                }

                rowData[colIdx] = value;
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            tableModel.addRow(rowData);
        }

        final int colCount = tableModel.getColumnCount();
        final int rowCount = tableModel.getRowCount();
        for (int col = 0; col < colCount; col++) {
            Class type = null;
            for (int row = 0; row < rowCount; row += 1) {
                final Object value = tableModel.getValueAt(row, col);
                if (value == null) {
                    continue;
                }
                if (type == null) {
                    type = value.getClass();
                } else if (type != Object.class) {
                    if (type.isInstance(value) == false) {
                        type = Object.class;
                    }
                }
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            if (type != null) {
                tableModel.setColumnType(col, type);
            }
        }

        parent.importComplete(tableModel);
    } catch (Exception e) {
        parent.importFailed(e);
        logger.error("Failed to import spreadsheet", e); // NON-NLS
    }
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }/*ww w .j  a v  a2  s  .  c om*/

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    return newRow;
}

From source file:org.seasar.fisshplate.wrapper.CellWrapper.java

License:Apache License

public CellWrapper(Cell cell, RowWrapper row) {
    this.row = row;
    this.hssfCell = cell;
    if (cell != null) {
        this.cellStyle = cell.getCellStyle();
        this.cellType = cell.getCellType();
    }/*from   w w w .j  a  v  a  2 s  .  c  o  m*/
}

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * Gets the row style./*  www  . j a  v a  2 s.  co m*/
 *
 * @param wb
 *            the wb
 * @param poiCell
 *            the poi cell
 * @param inputType
 *            the input type
 * @param rowHeight
 *            the row height
 * @param rowspan
 *            the rowspan
 * @return the row style
 */
public static String getRowStyle(final Workbook wb, final Cell poiCell, final String inputType,
        final float rowHeight, final int rowspan) {

    CellStyle cellStyle = poiCell.getCellStyle();
    if ((cellStyle != null) && (rowspan == 1)) {
        short fontIdx = cellStyle.getFontIndex();
        Font font = wb.getFontAt(fontIdx);
        float maxHeight = rowHeight;
        if (!inputType.isEmpty()) {
            maxHeight = Math.min(font.getFontHeightInPoints() + 8f, rowHeight);
        }
        return "height:" + WebSheetUtility.pointsToPixels(maxHeight) + "px;";
    }
    return "";
}

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * Gets the cell font style.//from   w w w  . j ava  2 s.c o m
 *
 * @param wb
 *            the wb
 * @param poiCell
 *            the poi cell
 * @return the cell font style
 */
public static String getCellFontStyle(final Workbook wb, final Cell poiCell) {

    CellStyle cellStyle = poiCell.getCellStyle();
    StringBuilder webStyle = new StringBuilder();
    if (cellStyle != null) {
        short fontIdx = cellStyle.getFontIndex();
        Font font = wb.getFontAt(fontIdx);
        if (font.getItalic()) {
            webStyle.append("font-style: italic;");
        }
        if (font.getBold()) {
            webStyle.append("font-weight: bold;");
        }
        webStyle.append("font-size: " + font.getFontHeightInPoints() + "pt;");
        String decoration = getCellFontDecoration(font);
        if (decoration.length() > 0) {
            webStyle.append("text-decoration:" + decoration + ";");
        }
        webStyle.append(getCellFontColor(font));

    }
    return webStyle.toString();

}

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * Gets the cell style./*from ww  w.  j a va 2s .c o  m*/
 *
 * @param wb
 *            the wb
 * @param poiCell
 *            the poi cell
 * @param inputType
 *            the input type
 * @return the cell style
 */
public static String getCellStyle(final Workbook wb, final Cell poiCell, final String inputType) {

    CellStyle cellStyle = poiCell.getCellStyle();
    StringBuilder webStyle = new StringBuilder();
    if (cellStyle != null) {
        if (!inputType.isEmpty()) {
            webStyle.append(getAlignmentFromCell(poiCell, cellStyle));
            webStyle.append(getVerticalAlignmentFromCell(cellStyle));
        }

        webStyle.append(ColorUtility.getBgColorFromCell(wb, poiCell, cellStyle));
    }
    return webStyle.toString();

}

From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java

License:MIT License

/**
 * Gets the column style.//from   w ww  . j  a  va2 s . c  om
 *
 * @param wb
 *            the wb
 * @param fcell
 *            the fcell
 * @param poiCell
 *            the poi cell
 * @param rowHeight
 *            the row height
 * @return the column style
 */
public static String getColumnStyle(final Workbook wb, final FacesCell fcell, final Cell poiCell,
        final float rowHeight) {

    String inputType = fcell.getInputType();
    CellStyle cellStyle = poiCell.getCellStyle();
    StringBuilder webStyle = new StringBuilder();
    if (cellStyle != null) {
        if (fcell.isContainPic() || fcell.isContainChart()) {
            webStyle.append("vertical-align: top;");
        } else {
            webStyle.append(getAlignmentFromCell(poiCell, cellStyle));
            webStyle.append(getVerticalAlignmentFromCell(cellStyle));
        }
        webStyle.append(ColorUtility.getBgColorFromCell(wb, poiCell, cellStyle));
        webStyle.append(getRowStyle(wb, poiCell, inputType, rowHeight, fcell.getRowspan()));
    } else {
        webStyle.append(getAlignmentFromCellType(poiCell));
    }
    return webStyle.toString();

}