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:guru.qas.martini.report.DefaultState.java

License:Apache License

protected void colorCompromisedThemes() {
    Collection<Cell> failed = statii.get("FAILED");

    if (!failed.isEmpty()) {
        List<Row> rows = Lists.newArrayListWithExpectedSize(failed.size());
        for (Cell cell : failed) {
            Row row = cell.getRow();//from   w w  w. j  a  va2  s .co m
            rows.add(row);
        }

        Set<Cell> compromisedThemeCells = Sets.newHashSet();

        Map<String, Collection<Cell>> themeMap = themes.asMap();
        for (Map.Entry<String, Collection<Cell>> mapEntry : themeMap.entrySet()) {
            Collection<Cell> themeCells = mapEntry.getValue();

            boolean compromised = false;
            for (Iterator<Cell> iterator = themeCells.iterator(); !compromised && iterator.hasNext();) {
                Cell themeCell = iterator.next();
                Row row = themeCell.getRow();
                compromised = rows.contains(row);
            }

            if (compromised) {
                compromisedThemeCells.addAll(themeCells);
            }
        }

        Set<String> compromisedThemes = Sets.newHashSet();
        for (Cell themeCell : compromisedThemeCells) {
            String contents = themeCell.getStringCellValue();
            if (null != contents) {
                Iterable<String> themes = Splitter.onPattern("\\s+").omitEmptyStrings().split(contents);
                Iterables.addAll(compromisedThemes, themes);
            }
        }

        for (String theme : compromisedThemes) {
            Collection<Cell> cells = themes.get(theme);
            for (Cell cell : cells) {
                CellStyle cellStyle = cell.getCellStyle();
                Sheet sheet = cell.getSheet();
                Workbook workbook = sheet.getWorkbook();

                int originalFontIndex = cellStyle.getFontIndexAsInt();
                Font originalFont = workbook.getFontAt(originalFontIndex);

                CellStyle clone = workbook.createCellStyle();
                clone.cloneStyleFrom(cellStyle);

                Font font = workbook.findFont(true, IndexedColors.DARK_RED.getIndex(),
                        originalFont.getFontHeight(), originalFont.getFontName(), originalFont.getItalic(),
                        originalFont.getStrikeout(), originalFont.getTypeOffset(), originalFont.getUnderline());

                if (null == font) {
                    font = workbook.createFont();
                    font.setBold(true);
                    font.setColor(IndexedColors.DARK_RED.getIndex());
                    font.setFontHeight(originalFont.getFontHeight());
                    font.setFontName(originalFont.getFontName());
                    font.setItalic(originalFont.getItalic());
                    font.setStrikeout(originalFont.getStrikeout());
                    font.setTypeOffset(originalFont.getTypeOffset());
                    font.setUnderline(originalFont.getUnderline());
                }
                clone.setFont(font);
                cell.setCellStyle(clone);
            }
        }
    }
}

From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java

License:Apache License

protected void addResult(State state, Sheet sheet, JsonObject object) {
    int index = sheet.getLastRowNum();
    Row row = sheet.createRow(index + 1);

    for (int i = 0; i < columns.size(); i++) {
        Cell cell = row.createCell(i);
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
        TraceabilityColumn column = columns.get(i);
        column.addResult(state, cell, object);
    }/* w  w  w . j a v  a2  s .  co m*/
}

From source file:invoiceapplication.CopyRowOriginal.java

public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exists in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true);
    } else {/*from w  ww .  j  ava  2  s  .co  m*/
        newRow = worksheet.createRow(destRowNum);
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
    // Loops through source column 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 = newRow.createCell(i);

        // if the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Use old cell style
        newCell.setCellStyle(oldCell.getCellStyle());

        // If there is a cell comment, copy
        if (newCell.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_FORMULA:
            newCell.setCellValue(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        }
    }
}

From source file:itpreneurs.itp.report.archive.CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {

    //     if(args.length == 0) {
    //        throw new IllegalArgumentException("Filename must be given");
    //     }//from  w ww .j av a2s  . c  o m

    String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx";

    Workbook wb = WorkbookFactory.create(new File(filename));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ???//from   w  w  w .j  av  a  2 s.c o m
 *
 * @param c ?
 * @return
 */
private String getCellStringValue(Cell c) {
    if (c == null) {
        return "";
    }
    String value = null;
    NumberFormat nf = NumberFormat.getInstance();
    nf.setGroupingUsed(false);
    nf.setMaximumFractionDigits(12);
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(c.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
        } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
            value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                    c.getCellStyle().getDataFormatString());
        } else {
            value = nf.format(c.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = c.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        value = c.getCellFormula();
        break;
    }
    return value == null ? "" : value.trim();
}

From source file:javacommon.excel.ExcelReader.java

/**
 * ???/*w  w w  .j a va  2 s .  c o m*/
 *
 * @param c ?
 * @return
 */
private String getCellStringFormatValue(Cell c) {
    if (c == null) {
        return "";
    }
    String value = null;
    NumberFormat nf = NumberFormat.getInstance();
    nf.setGroupingUsed(false);
    nf.setMaximumFractionDigits(12);
    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        value = String.valueOf(c.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(c)) {
            return DateFormatUtils.ISO_DATE_FORMAT.format(c.getDateCellValue());
        } else if ("@".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if ("General".equals(c.getCellStyle().getDataFormatString())) {
            value = nf.format(c.getNumericCellValue());
        } else if (ArrayUtils.contains(ExcelConstants.DATE_PATTERNS, c.getCellStyle().getDataFormatString())) {
            value = DateFormatUtils.format(HSSFDateUtil.getJavaDate(c.getNumericCellValue()),
                    c.getCellStyle().getDataFormatString());
        } else {
            value = nf.format(c.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        value = c.getStringCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        return c.getCellFormula();
    }
    return value == null ? "" : value.trim();
}

From source file:jp.ryoyamamoto.poiutils.Cells.java

License:Apache License

private static void copyCellStyle(Cell source, Cell target) {
    target.setCellStyle(source.getCellStyle());
}

From source file:logic.Export.java

public boolean convertXls()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;//w ww . j  ava  2  s  .  com
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        //formate sheets
        sheet.getPrintSetup().setLandscape(true);
        sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        //setPrintArea 
        workbook.setPrintArea(i + 1, //sheet index
                0, //start column Spalte
                6, //end column
                0, //start row zeile
                convert.size() + 8 //end row
        );
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell);
                if (Cell == 3) {
                    if ("true".equals(convert.get(Row)[Cell])) {
                        XSSFCellStyle style1 = workbook.createCellStyle();
                        style1 = (XSSFCellStyle) cell.getCellStyle();
                        style1 = (XSSFCellStyle) style1.clone();
                        style1.setFillBackgroundColor(HSSFColor.RED.index);
                        style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                        XSSFColor myColor = new XSSFColor(Color.RED);
                        style1.setFillForegroundColor(myColor);
                        sheet.getRow(9 + Row).getCell(6).setCellStyle(style1);
                    }
                } else {
                    cell.setCellValue(convert.get(Row)[Cell]);
                }
            }
        }
    }

    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:lucee.runtime.poi.Excel.java

License:Open Source License

public void setValue(int rowNumber, int columnNumber, String value) throws CasterException {
    if (value == null)
        value = "";
    Sheet sheet = workbook.getSheet(getExcelSheetName());

    // get Row/*from   w  w w . j  av a 2  s  .co  m*/
    Row row = sheet.getRow(rowNumber);
    if (row == null)
        row = sheet.createRow(rowNumber);

    // get Cell
    Cell cell = row.getCell(columnNumber);
    CellStyle style = null;
    if (cell != null) {
        style = cell.getCellStyle();
        row.removeCell(cell);
    }
    cell = row.createCell(columnNumber);
    if (style != null)
        cell.setCellStyle(style);

    CreationHelper createHelper = workbook.getCreationHelper();
    boolean isFormula = style != null && style.getDataFormatString().equals("@");

    if (!isFormula && Decision.isNumeric(value)) {
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        double dbl = Caster.toDoubleValue(value);
        cell.setCellValue(dbl);
        _expandColumnWidth(sheet, Caster.toString(dbl), columnNumber);
    } else if (StringUtil.isEmpty("")) {
        cell.setCellType(Cell.CELL_TYPE_BLANK);
        cell.setCellValue(createHelper.createRichTextString(""));
    } else {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(createHelper.createRichTextString(value));
        _expandColumnWidth(sheet, value, columnNumber);
    }

}

From source file:midas.sheeco.type.adapter.SpreadsheetBooleanAdapterTest.java

License:Apache License

@Test(expected = InvalidCellValueException.class)
public void testInvalidNumeric() {
    SpreadsheetBooleanAdapter adapter = new SpreadsheetBooleanAdapter();

    CellStyle style = mock(CellStyle.class);
    when(style.getDataFormat()).thenReturn((short) 0x0e);
    Cell cell = mock(Cell.class);
    when(cell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
    when(cell.getDateCellValue()).thenReturn(new Date(111111));
    when(cell.getCellStyle()).thenReturn(style);

    adapter.fromSpreadsheet(cell);//from   w  w w .j  a v  a  2  s.  c  o m
}