Example usage for org.apache.poi.hssf.usermodel HSSFCell getCellStyle

List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCell
  6. getCellStyle

Introduction

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

Prototype

public HSSFCellStyle getCellStyle()

Source Link

Document

get the style for the cell.

Usage

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>.
 * Only the sheet on the given sheetIndex is copied.
        //www  .j  ava2 s. c om
 * @param igrid the XMA model where to copy the data
 * @param book the POI represntation of the data
 * @param sheetIndex the index of the sheet to copy
 * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges
 */
public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) {
    GridWM grid = (GridWM) igrid;
    try {
        List errorList = new ArrayList();
        grid.setSheetName(book.getSheetName(sheetIndex));

        grid.colors.clear();
        grid.initBuildInColors();
        short ic = GridWM.HSSF_FIRST_COLOR_INDEX;
        HSSFPalette palette = book.getCustomPalette();
        for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) {
            grid.colors.add(ic, new GridColor(color.getTriplet()));
        }

        grid.fonts.clear();
        int numFonts = book.getNumberOfFonts();
        if (numFonts > 4) {
            // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt()
            numFonts += 1;
        }
        for (short i = 0; i < numFonts; i++) {
            HSSFFont font = book.getFontAt(i);
            byte fontstyle = GridFont.FONT_NORML;
            if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD)
                fontstyle |= GridFont.FONT_BOLD;
            if (font.getItalic())
                fontstyle |= GridFont.FONT_ITALIC;
            grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor()));
        }

        grid.styles.clear();
        for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) {
            HSSFCellStyle style = book.getCellStyleAt(i);
            grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor()));
        }

        grid.namedRanges.clear();
        for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) {
            HSSFName name = book.getNameAt(i);
            String rangeName = name.getNameName();
            String rangeRef = null;
            try { // ranges not defined but referenced by formulas have a name but no reference in HSSF
                rangeRef = name.getReference();
            } catch (Exception exc) {
                errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName))
                        .setCode(GridWM.CODE_inconsistentRange));
            }
            if (rangeRef != null) {
                try {
                    GridRange range = grid.getJeksDelegate().toRange(rangeRef);
                    range.setKey(rangeName);
                    grid.namedRanges.put(rangeName, range);
                } catch (Exception exc) {
                    errorList.add(new SysException(exc,
                            ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef))
                                    .setCode(GridWM.CODE_unsupportedReference));
                }
            }
        }

        grid.rows.clear();
        grid.cols.clear();
        grid.cells.clear();
        grid.delegate = new GridJeksDelegate(grid);
        HSSFSheet sheet = book.getSheetAt(sheetIndex);
        int firstColNum = Integer.MAX_VALUE;
        int lastColNum = Integer.MIN_VALUE;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null)
                continue;
            if (row.getFirstCellNum() >= 0)
                firstColNum = Math.min(firstColNum, row.getFirstCellNum());
            lastColNum = Math.max(lastColNum, row.getLastCellNum());
            if (lastColNum > 255)
                lastColNum = 255;
            for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                HSSFCell hssfcell = row.getCell(j);
                if (hssfcell == null)
                    continue;
                GridCell gridcell = grid.getOrCreateCell(i, j);
                switch (hssfcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    gridcell.setValue(hssfcell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    // TODO: recherche error text
                    byte errorCode = hssfcell.getErrorCellValue();
                    //                    gridcell.setValue(errorCode);
                    gridcell.setValue("#ERROR");
                    errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord",
                            grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode)))
                                    .setCode(GridWM.CODE_errorRecord));
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    String formula = null;
                    try {
                        formula = hssfcell.getCellFormula();
                        gridcell.setFormula(formula);
                    } catch (SysException e) {
                        if (formula != null)
                            gridcell.setValue("=" + formula); //set it as text without interpretation
                        errorList.add(e);
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (isDateCell(book, hssfcell)) {
                        gridcell.setValue(hssfcell.getDateCellValue());
                    } else {
                        gridcell.setValue(hssfcell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    gridcell.setValue(hssfcell.getStringCellValue());
                    break;
                default:
                    throw new SysException("unknown cell type " + hssfcell.getCellType());
                }
                gridcell.setEditable(!hssfcell.getCellStyle().getLocked());
                gridcell.setStyle(hssfcell.getCellStyle().getIndex());
            }
        }

        final int scalefactor = 256 / 7; // empirically testet
        //        int width = sheet.getDefaultColumnWidth();  // returns nonsense
        //        width = width/scalefactor;
        //        grid.setDefaultColumnWidth(width);
        for (short i = (short) firstColNum; i <= lastColNum; i++) {
            int width = sheet.getColumnWidth(i);
            width = width / scalefactor;
            grid.getOrCreateColumn(i).setWidth(width);
        }

        if (firstColNum == Integer.MAX_VALUE)
            firstColNum = 0;
        if (lastColNum == Integer.MIN_VALUE)
            lastColNum = 0;
        grid.setMaxRange(
                new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum));
        grid.setVisibleRange(grid.getMaxRange());
        return errorList;
    } finally {
        grid.handle(grid.new GridReloadEvent());
    }
}

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

private static boolean isDateCell(HSSFWorkbook book, HSSFCell cell) {
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // knows only excel buildin date formats
        return true;
    } else {/* w w w. ja va2s .c o  m*/
        short format = cell.getCellStyle().getDataFormat();
        String formatString = book.createDataFormat().getFormat(format);
        if (formatString.indexOf("d") >= 0 && formatString.indexOf("m") >= 0
                && formatString.indexOf("yy") >= 0) {
            return true; // day, month and year are contained -> must be a date
        } else {
            return false;
        }
    }
}

From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java

License:Open Source License

/**
 * Calculates the alignement of each column of the given grid.
 * In Excel alignements are defined per cell, in SWT alignement are defined per column.
 * So the alignment for SWT is calculated by choosing the most used alignement of the visible
 * cells of each column./*from  w ww .  jav a2  s. co m*/
 *
 * @param igrid the XMA model where to set the alignemnets
 * @param sheet the POI representation from where to read the alignements
 */
static public void calcAlignements(IGridWM igrid, HSSFSheet sheet) {
    GridWM grid = (GridWM) igrid;
    GridRange range = grid.getVisibleRange();
    for (int col = range.getFirstColumn(), lastCol = range.getLastColumn(); col <= lastCol; col++) {
        GridColumn gridColumn = grid.getOrCreateColumn(col);
        if (gridColumn.isHidden())
            continue;
        int left = 0, right = 0, center = 0;
        for (int i = range.getFirstRow(), lastRow = range.getLastRow(); i <= lastRow; i++) {
            GridRow gridRow = grid.getRow(i);
            if (gridRow != null && gridRow.isHidden())
                continue;
            HSSFRow hrow = sheet.getRow(i);
            if (hrow == null)
                continue;
            HSSFCell hcell = hrow.getCell((short) col);
            if (hcell == null)
                continue;
            HSSFCellStyle hstyle = hcell.getCellStyle();
            if (hstyle == null)
                continue;
            switch (hstyle.getAlignment()) {
            case HSSFCellStyle.ALIGN_CENTER:
            case HSSFCellStyle.ALIGN_CENTER_SELECTION:
                center++;
                break;
            case HSSFCellStyle.ALIGN_LEFT:
            case HSSFCellStyle.ALIGN_FILL:
            case HSSFCellStyle.ALIGN_JUSTIFY:
                left++;
                break;
            case HSSFCellStyle.ALIGN_RIGHT:
                right++;
                break;
            case HSSFCellStyle.ALIGN_GENERAL:
                switch (hcell.getCellType()) {
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    center++;
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                case 42: // CELL_TYPE_DATE:
                    right++;
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    left++;
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                case HSSFCell.CELL_TYPE_ERROR:
                case HSSFCell.CELL_TYPE_FORMULA:
                default:
                    break;
                }
                break;
            default:
                break;
            }
        }
        if (left >= right && left >= center) {
            gridColumn.setAlignement(GridColumn.ALIGN_LEFT);
        } else if (right > left && right >= center) {
            gridColumn.setAlignement(GridColumn.ALIGN_RIGHT);
        } else if (center > left && center > right) {
            gridColumn.setAlignement(GridColumn.ALIGN_CENTER);
        }
    }
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

void copyCellFormatting(HSSFCell c0, HSSFCell c1) {
    if (c0.getCellComment() != null)
        c1.setCellComment(c0.getCellComment());
    //   c1.setCellNum(c0.getCellNum());
    if (c0.getCellStyle() != null)
        c1.setCellStyle(c0.getCellStyle());
}

From source file:citibob.reports.PoiXlsWriter.java

License:Open Source License

int replaceOneHolder(HSSFSheet sheet, int row, int col, Map<String, Object> models, String rsname) {
    // Do the replacement
    Object mod = (models.size() == 1 ? models.values().iterator().next() : models.get(rsname));
    if (mod == null)
        return NOROWCHANGE;
    if (mod instanceof TableModel)
        return replaceOneHolder(sheet, row, col, (TableModel) mod);

    // It's just a simple item; put it in
    HSSFRow row0 = sheet.getRow(row);/*from   www. j  a  va 2 s  .  c  o  m*/
    HSSFCell c0 = row0.getCell((short) col);
    HSSFComment comment = c0.getCellComment();
    HSSFCellStyle style = c0.getCellStyle();
    row0.removeCell(c0);
    HSSFCell c1 = row0.createCell((short) col);
    if (comment != null)
        c1.setCellComment(comment);
    if (style != null)
        c1.setCellStyle(style);
    setValue(c1, mod);
    return NOROWCHANGE;
}

From source file:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

static ITableMetaData createMetaData(String tableName, HSSFRow sampleRow, HSSFWorkbook workbook) {
    logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);

    List<Column> columnList = new ArrayList<Column>();
    List<String> primaryKeyList = new ArrayList<String>();
    for (int i = 0;; i++) {
        HSSFCell cell = sampleRow.getCell(i);
        if (cell == null) {
            break;
        }//from   w  w  w . j  ava2 s. com

        String columnName = cell.getRichStringCellValue().getString();
        if (columnName != null) {
            columnName = columnName.trim();
        }

        // Bugfix for issue ID 2818981 - if a cell has a formatting but no
        // name also ignore it
        if (columnName.length() <= 0) {
            logger.debug(
                    "The column name of column # {} is empty - will skip here assuming the last column was reached",
                    String.valueOf(i));
            break;
        }

        Column column = new Column(columnName, DataType.UNKNOWN);
        columnList.add(column);

        // Unique identification key
        byte underline = cell.getCellStyle().getFont(workbook).getUnderline();
        if (underline == 1) {
            primaryKeyList.add(columnName);
        }

    }
    Column[] columns = columnList.toArray(new Column[0]);

    if (!primaryKeyList.isEmpty()) {
        return new DefaultTableMetaData(tableName, columns,
                primaryKeyList.toArray(new String[primaryKeyList.size()]));
    } else {
        return new DefaultTableMetaData(tableName, columns);
    }

}

From source file:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

protected BigDecimal getNumericValue(HSSFCell cell) {
    logger.debug("getNumericValue(cell={}) - start", cell);

    String formatString = cell.getCellStyle().getDataFormatString();
    String resultString = null;/*from   www  . j a v a  2  s.c  o  m*/
    double cellValue = cell.getNumericCellValue();

    if ((formatString != null)) {
        if (!formatString.equals("General") && !formatString.equals("@")) {
            logger.debug("formatString={}", formatString);
            DecimalFormat nf = new DecimalFormat(formatString, symbols);
            resultString = nf.format(cellValue);
        }
    }

    BigDecimal result;
    if (resultString != null) {
        try {
            result = new BigDecimal(resultString);
        } catch (NumberFormatException e) {
            logger.debug("Exception occurred while trying create a BigDecimal. value={}", resultString);
            // Probably was not a BigDecimal format retrieved from the
            // excel. Some
            // date formats are not yet recognized by HSSF as DateFormats so
            // that
            // we could get here.
            result = toBigDecimal(cellValue);
        }
    } else {
        result = toBigDecimal(cellValue);
    }
    return result;
}

From source file:cn.baiweigang.qtaf.toolkit.dbunit.data.excel.XlsTable.java

License:Open Source License

public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);/*  www .  jav  a  2s .com*/

    int columnIndex = getColumnIndex(column);
    HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
        }

    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

    case HSSFCell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column);

    case HSSFCell.CELL_TYPE_BLANK:
        return null;

    case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

    case HSSFCell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row + ", column=" + column);

    default:
        throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column);
    }
}

From source file:cn.trymore.core.util.excel.PoiExcelParser.java

License:Open Source License

public String getCellAlignment(HSSFCell cell) {
    HSSFCellStyle style = cell.getCellStyle();
    if ((cell != null) && (style != null)) {
        switch (style.getAlignment()) {
        case 2://from www.  j  a v  a  2 s  .  com
            return "text-align:center;";
        case 1:
            return "text-align:left;";
        case 3:
            return "text-align:right;";
        }

    }

    return "";
}

From source file:cn.trymore.core.util.excel.PoiExcelParser.java

License:Open Source License

public String getCellBackgroundStyle(HSSFCell cell) {
    HSSFCellStyle style = cell.getCellStyle();
    if ((cell != null) && (style != null)) {
        return new StringBuilder().append("background-color:")
                .append((this.cfmCells.containsKey(cell)) ? (String) this.cfmCells.get(cell)
                        : tripleToRGBString(style.getFillForegroundColor()))
                .append(";").toString();
    }//from  ww w.ja  v a2  s  . c om

    return "";
}