Example usage for org.apache.poi.ss.usermodel Cell 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.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static void formatNumericCell(Cell cell, String s, String[] excelFormats) {
    CellStyle origStyle = cell.getCellStyle();
    CellStyle newStyle = cell.getRow().getSheet().getWorkbook().createCellStyle();
    DataFormat newFormat = cell.getRow().getSheet().getWorkbook().createDataFormat();
    newStyle.cloneStyleFrom(origStyle);//from w  w w  .  jav  a 2s .c o  m

    newStyle.setAlignment(CellStyle.ALIGN_LEFT);

    int numDecimals = -1;
    if (s != null) {
        int decimalIndex = s.indexOf(".");
        if (decimalIndex >= 0)
            numDecimals = s.length() - decimalIndex - 1;
    }

    if (numDecimals < 0)
        numDecimals = 0;

    if ((numDecimals >= 0) && (numDecimals <= 8))
        newStyle.setDataFormat(newFormat.getFormat(excelFormats[numDecimals]));

    cell.setCellStyle(newStyle);
}

---

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy cell./*from w  w  w.jav  a2 s .com*/
 *
 * @param _oldCell the old cell
 * @param _newCell the new cell
 * @param _styleMap the style map
 */
protected void copyCell(final Cell _oldCell, final Cell _newCell, final Map<Integer, CellStyle> _styleMap) {
    if (_styleMap != null) {
        if (_oldCell.getSheet().getWorkbook() == _newCell.getSheet().getWorkbook()) {
            _newCell.setCellStyle(_oldCell.getCellStyle());
        } else {
            final int stHashCode = _oldCell.getCellStyle().hashCode();
            CellStyle newCellStyle = _styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = _newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(_oldCell.getCellStyle());
                _styleMap.put(stHashCode, newCellStyle);
            }
            _newCell.setCellStyle(newCellStyle);
        }
    }
    switch (_oldCell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        _newCell.setCellValue(_oldCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        _newCell.setCellValue(_oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        _newCell.setCellType(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(_oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

---

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java

License:Open Source License

private void loadConfig(Workbook _xlsWorkbook) {
    final SpreadsheetBuilder spreadsheetBuilder = new SpreadsheetBuilder(ComputationMode.EXCEL);
    final int numberOfSheets = _xlsWorkbook.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets; i++) {
        spreadsheetBuilder.beginSheet(_xlsWorkbook.getSheetAt(i).getSheetName());
        spreadsheetBuilder.endSheet();//from  ww  w. j  av a 2  s .c  om
    }
    final BaseSpreadsheet spreadsheet = spreadsheetBuilder.getSpreadsheet();

    final Cell gtFormatCell = getCellByName("GlobalTimeFormat", _xlsWorkbook, spreadsheet);
    if (null != gtFormatCell) {
        this.globalTimeFormat = gtFormatCell.getCellStyle().getDataFormatString();
    }
    final Cell gtZoneNameCell = getCellByName("GlobalTimeZoneName", _xlsWorkbook, spreadsheet);
    if (null != gtZoneNameCell) {
        this.globalTimeZone = TimeZone.getTimeZone(gtZoneNameCell.getStringCellValue());
    }
}

---

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java

License:Open Source License

private boolean isTime(Cell cell) {
    final CellStyle style = cell.getCellStyle();
    if (style != null) {
        final String format = cell.getCellStyle().getDataFormatString();
        if (format != null) {
            final Matcher dtMatcher = TIME_PATTERN.matcher(format);
            return dtMatcher.find();
        }//from  w  w  w.  ja  va2s  .co  m
    }
    return false;
}

---

From source file:org.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java

License:Open Source License

private Object getNumberValue(Cell _xlsCell) {
    final double value = _xlsCell.getNumericCellValue();
    final boolean isDate = DateUtil.isCellDateFormatted(_xlsCell);
    final boolean isTime = isTime(_xlsCell);
    if (isDate || isTime) {
        if (null != this.globalTimeFormat
                && this.globalTimeFormat.equals(_xlsCell.getCellStyle().getDataFormatString())) {
            return RuntimeDouble_v2.dateFromNum(_xlsCell.getNumericCellValue(), this.globalTimeZone,
                    ComputationMode.EXCEL);
        }//  w w  w . j  a  va2  s .  co m
        if ((isDate && value < 1) || (isTime && value < 365)) {
            return new Duration(value);
        } else {
            return new LocalDate(value);
        }
    } else {
        return value;
    }
}

---

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

private static void cloneCell(final Cell cNew, final Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;/*from   w  w  w.  j a  v  a  2 s. c  o  m*/
    }
    case Cell.CELL_TYPE_NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case Cell.CELL_TYPE_FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    }

}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

private static InnerRow getTemplateRow(Map<Integer, InnerRow> cache, Sheet sheet,
        ExcelWriteSheetProcessor<?> sheetProcessor, int rowIndex) {
    InnerRow cachedRow = cache.get(rowIndex);
    if (cachedRow != null || cache.containsKey(rowIndex)) {
        return cachedRow;
    }/* ww w .ja va 2  s  .  com*/
    InnerRow templateRow = null;
    if (sheetProcessor.getTemplateStartRowIndex() != null && sheetProcessor.getTemplateEndRowIndex() != null) {
        if (rowIndex <= sheetProcessor.getTemplateEndRowIndex()) {
            return null;
        }
        int tempRowIndex = (rowIndex - sheetProcessor.getTemplateEndRowIndex() - 1)
                % (sheetProcessor.getTemplateEndRowIndex() - sheetProcessor.getTemplateStartRowIndex() + 1)
                + sheetProcessor.getTemplateStartRowIndex();
        Row tempRow = sheet.getRow(tempRowIndex);
        if (tempRow != null) {
            templateRow = new InnerRow();
            templateRow.setHeight(tempRow.getHeight());
            templateRow.setHeightInPoints(tempRow.getHeightInPoints());
            templateRow.setRowStyle(tempRow.getRowStyle());
            templateRow.setZeroHeight(tempRow.getZeroHeight());
            for (int i = tempRow.getFirstCellNum(); i <= tempRow.getLastCellNum(); i++) {
                Cell cell = tempRow.getCell(i);
                if (cell != null) {
                    InnerCell innerCell = new InnerCell();
                    innerCell.setCellStyle(cell.getCellStyle());
                    innerCell.setCellType(cell.getCellType());
                    templateRow.setCell(i, innerCell);
                }
            }
        }
    }
    cache.put(rowIndex, templateRow);
    return templateRow;
}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

public static void writeCell(Cell cell, Object val) {
    if (cell.getCellStyle() != null && cell.getCellStyle().getDataFormat() > 0) {
        writeCell(cell, val, true, null, null);
    } else {//from w w  w  .ja va 2  s . c  o  m
        writeCell(cell, val, false, null, null);
    }
}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("unused")
private static void writeCell(Cell cell, Object val, boolean userTemplate,
        ExcelWriteFieldMappingAttribute attribute, Object bean) {
    if (attribute != null && attribute.getLinkField() != null) {
        String addressFieldName = attribute.getLinkField();
        String address = null;//from  w w  w.j  av a 2 s  . c  o m
        if (bean != null) {
            address = (String) getFieldValue(bean, addressFieldName, true);
        }
        Workbook wb = cell.getRow().getSheet().getWorkbook();

        Hyperlink link = wb.getCreationHelper().createHyperlink(attribute.getLinkType());
        link.setAddress(address);
        cell.setHyperlink(link);
        // Its style can't inherit from cell.
        CellStyle style = wb.createCellStyle();
        Font hlinkFont = wb.createFont();
        hlinkFont.setUnderline(Font.U_SINGLE);
        hlinkFont.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(hlinkFont);
        if (cell.getCellStyle() != null) {
            style.setFillBackgroundColor(cell.getCellStyle().getFillBackgroundColor());
        }
        cell.setCellStyle(style);
    }
    if (val == null) {
        cell.setCellValue((String) null);
        return;
    }
    Class<?> clazz = val.getClass();
    if (val instanceof Byte) {// Double
        Byte temp = (Byte) val;
        cell.setCellValue((double) temp.byteValue());
    } else if (val instanceof Short) {
        Short temp = (Short) val;
        cell.setCellValue((double) temp.shortValue());
    } else if (val instanceof Integer) {
        Integer temp = (Integer) val;
        cell.setCellValue((double) temp.intValue());
    } else if (val instanceof Long) {
        Long temp = (Long) val;
        cell.setCellValue((double) temp.longValue());
    } else if (val instanceof Float) {
        Float temp = (Float) val;
        cell.setCellValue((double) temp.floatValue());
    } else if (val instanceof Double) {
        Double temp = (Double) val;
        cell.setCellValue((double) temp.doubleValue());
    } else if (val instanceof Date) {// Date
        Date dateVal = (Date) val;
        long time = dateVal.getTime();
        // read is based on 1899/12/31 but DateUtil.getExcelDate is base on
        // 1900/01/01
        if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_01_00_00_00_000) {
            Date incOneDay = new Date(time + 24 * 60 * 60 * 1000);
            double d = DateUtil.getExcelDate(incOneDay);
            cell.setCellValue(d - 1);
        } else {
            cell.setCellValue(dateVal);
        }

        if (!userTemplate) {
            Workbook wb = cell.getRow().getSheet().getWorkbook();
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle == null) {
                cellStyle = wb.createCellStyle();
            }
            DataFormat dataFormat = wb.getCreationHelper().createDataFormat();
            // @see #BuiltinFormats
            // 0xe, "m/d/yy"
            // 0x14 "h:mm"
            // 0x16 "m/d/yy h:mm"
            // {@linke https://en.wikipedia.org/wiki/Year_10,000_problem}
            /** [1899/12/31 00:00:00:000~1900/01/01 00:00:000) */
            if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_02_00_00_00_000) {
                cellStyle.setDataFormat(dataFormat.getFormat("h:mm"));
                // cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm"));
            } else {
                // if ( time % (24 * 60 * 60 * 1000) == 0) {//for time
                // zone,we can't use this way.
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dateVal);
                int hour = calendar.get(Calendar.HOUR_OF_DAY);
                int minute = calendar.get(Calendar.MINUTE);
                int second = calendar.get(Calendar.SECOND);
                int millisecond = calendar.get(Calendar.MILLISECOND);
                if (millisecond == 0 && second == 0 && minute == 0 && hour == 0) {
                    cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy"));
                } else {
                    cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm"));
                }
            }
            cell.setCellStyle(cellStyle);
        }
    } else if (val instanceof Boolean) {// Boolean
        cell.setCellValue(((Boolean) val).booleanValue());
    } else {// String
        cell.setCellValue((String) val.toString());
    }
}

---

From source file:org.is.jxlpoi.JXLPOIWorkbook.java

License:Apache License

public String getCellContentAsString(Cell c) {

    if (c == null)
        return null;

    switch (c.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        if (c.getBooleanCellValue())
            return "Y";
        else//w  ww  .j a  va  2  s  . c o m
            return "N";
    case Cell.CELL_TYPE_NUMERIC:
        String result = "";
        int datatype = c.getCellStyle().getDataFormat();

        String formatString = c.getCellStyle().getDataFormatString();
        if (datatype == 174 && "yyyy/mm/dd".equals(formatString)) {
            java.util.Date date = c.getDateCellValue();
            return fmter.format(date);
        } else if (datatype == 49 || datatype == 0) {
            int d = (int) c.getNumericCellValue();
            result = Integer.toString(d);
        } else {
            result = Double.toString(c.getNumericCellValue());
        }

        //return Double.toString(c.getNumericCellValue());
        //System.out.println(" number = "+c.getNumericCellValue()+" *** value ="+twoPlaces.format(c.getNumericCellValue())+"");

        //return twoPlaces.format(c.getNumericCellValue())+"";
        return result;
    case Cell.CELL_TYPE_STRING:
        return c.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return "";
    case Cell.CELL_TYPE_ERROR:
        return "#ERROR" + c.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:

        String formulaCellValue;

        if (formulaEvaluator == null) {
            formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            //formulaEvaluator.setIgnoreFormulaException();
            //System.out.println(formulaEvaluator);
        }

        //formulaEvaluator.evaluateFormulaCell(c);
        //formulaEvaluator.evaluateInCell(c);

        CellValue cv = formulaEvaluator.evaluate(c);

        switch (cv.getCellType()) {
        //switch (formulaEvaluator.evaluateInCell(c).getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            if (cv.getBooleanValue())
                formulaCellValue = "Y";
            else
                formulaCellValue = "F";
            break;
        case Cell.CELL_TYPE_NUMERIC:
            formulaCellValue = Double.toString(cv.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            formulaCellValue = cv.getStringValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            formulaCellValue = "";
            break;
        case Cell.CELL_TYPE_ERROR:
            formulaCellValue = Byte.toString(cv.getErrorValue());
            break;
        default:
            formulaCellValue = "";
            break;
        }//switch

        return formulaCellValue;
    default:
        return "";
    }//switch

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• »