Example usage for org.apache.poi.hssf.usermodel HSSFCell 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:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

/**
 * ?Excel?MapList?Excel??Java./* w  w  w.  j  a v  a  2  s . co m*/
 * 
 * @param file
 * @return
 * @throws IOException
 */
public static List<Map<String, Object>> parseHSSFMapList(MultipartFile file) throws IOException {//
    POIFSFileSystem poiFs = new POIFSFileSystem(file.getInputStream());
    HSSFWorkbook wb = new HSSFWorkbook(poiFs);

    HSSFSheet sheet = wb.getSheetAt(0);

    int rowNum = sheet.getLastRowNum();
    HSSFRow titleRow = sheet.getRow(0);

    int colNum = titleRow.getLastCellNum();

    //?17
    List<String> titleList = new ArrayList<String>();
    for (int i = 0; i < colNum; i++) {
        String title = titleRow.getCell(i).getStringCellValue();
        titleList.add(trimTitle(title));
    }

    List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
    for (int i = 1; i <= rowNum; i++) {
        HSSFRow row = sheet.getRow(i);
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = row.getCell(j);
            if (cell != null) {
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    double d = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    //?
                    if (HSSFDateUtil.isCellDateFormatted(cell) || (style != null
                            && (style.getDataFormat() == 57 || style.getDataFormat() == 58))) {
                        map.put(titleList.get(j), HSSFDateUtil.getJavaDate(d));
                    } else {
                        map.put(titleList.get(j), d);
                    }
                    break;

                default:
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    map.put(titleList.get(j), row.getCell(j).getStringCellValue());
                    break;
                }
            } else {
                map.put(titleList.get(j), null);
            }
        }
        mapList.add(map);
    }

    return mapList;
}

---

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixLeftCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFCell leftCell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (leftCell != null) {
        HSSFCellStyle leftCellStyle = leftCell.getCellStyle();
        if (leftCellStyle.getBorderRight() != cellStyle.getBorderLeft()
                || leftCellStyle.getRightBorderColor() != cellStyle.getLeftBorderColor()) {
            HSSFCellStyle draftLeftStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
            XslStyleHelper.cloneStyleRelations(leftCellStyle, draftLeftStyle);
            draftLeftStyle.setBorderRight(cellStyle.getBorderLeft());
            draftLeftStyle.setRightBorderColor(cellStyle.getLeftBorderColor());
            HSSFCellStyle newLeftStyle = styleCache.getCellStyleByTemplate(draftLeftStyle);
            if (newLeftStyle == null) {
                newLeftStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftLeftStyle);
                styleCache.processCellStyle(newLeftStyle);
            }/*from  w w  w  .  j av  a 2  s  . c o m*/

            leftCell.setCellStyle(newLeftStyle);
        }
    }
}

---

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixRightCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFCell rightCell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (rightCell != null) {
        HSSFCellStyle rightCellStyle = rightCell.getCellStyle();

        if (rightCellStyle.getBorderLeft() != cellStyle.getBorderRight()
                || rightCellStyle.getLeftBorderColor() != cellStyle.getRightBorderColor()) {
            HSSFCellStyle draftRightStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
            XslStyleHelper.cloneStyleRelations(rightCellStyle, draftRightStyle);
            draftRightStyle.setBorderLeft(cellStyle.getBorderRight());
            draftRightStyle.setLeftBorderColor(cellStyle.getRightBorderColor());

            HSSFCellStyle newRightStyle = styleCache.getCellStyleByTemplate(draftRightStyle);
            if (newRightStyle == null) {
                newRightStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftRightStyle);
                styleCache.processCellStyle(newRightStyle);
            }/*from  w  w  w.  j  a va 2  s .  c  om*/

            rightCell.setCellStyle(newRightStyle);
        }
    }
}

---

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixUpCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFCell upCell = sheet.getRow(rowIndex).getCell(columnIndex);
    if (upCell != null) {
        HSSFCellStyle upCellStyle = upCell.getCellStyle();

        if (upCellStyle.getBorderBottom() != cellStyle.getBorderTop()
                || upCellStyle.getBottomBorderColor() != cellStyle.getTopBorderColor()) {
            HSSFCellStyle draftUpStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
            XslStyleHelper.cloneStyleRelations(upCellStyle, draftUpStyle);
            draftUpStyle.setBorderBottom(cellStyle.getBorderTop());
            draftUpStyle.setBottomBorderColor(cellStyle.getTopBorderColor());

            HSSFCellStyle newUpStyle = styleCache.getCellStyleByTemplate(draftUpStyle);
            if (newUpStyle == null) {
                newUpStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(), draftUpStyle);
                styleCache.processCellStyle(newUpStyle);
            }//from  w  w  w .  j  a  va  2 s .  co m

            upCell.setCellStyle(newUpStyle);
        }
    }
}

---

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixDownCell(HSSFSheet sheet, int rowIndex, int columnIndex, HSSFCellStyle cellStyle) {
    HSSFRow nextRow = sheet.getRow(rowIndex);
    if (nextRow != null) {
        HSSFCell downCell = nextRow.getCell(columnIndex);
        if (downCell != null) {
            HSSFCellStyle downCellStyle = downCell.getCellStyle();

            if (downCellStyle.getBorderTop() != cellStyle.getBorderBottom()
                    || downCellStyle.getTopBorderColor() != cellStyle.getBottomBorderColor()) {
                HSSFCellStyle draftDownStyle = HSSFWorkbookHelper.createDetachedCellStyle(sheet.getWorkbook());
                XslStyleHelper.cloneStyleRelations(downCellStyle, draftDownStyle);
                draftDownStyle.setBorderTop(cellStyle.getBorderBottom());
                draftDownStyle.setTopBorderColor(cellStyle.getBottomBorderColor());

                HSSFCellStyle newDownStyle = styleCache.getCellStyleByTemplate(draftDownStyle);
                if (newDownStyle == null) {
                    newDownStyle = HSSFWorkbookHelper.adoptDetachedCellStyle(sheet.getWorkbook(),
                            draftDownStyle);
                    styleCache.processCellStyle(newDownStyle);
                }/*from  ww  w . j  a v a  2s  .  co  m*/

                downCell.setCellStyle(newDownStyle);
            }
        }
    }
}

---

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method writes horizontal band/*ww w  .  j  a  v a 2s. com*/
 * Note: Only one band for row is supported. Now we think that many bands for row aren't usable.
 *
 * @param band          - band to write
 * @param templateSheet - template sheet
 * @param resultSheet   - result sheet
 */
protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) {
    String rangeName = band.getName();
    AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName);
    if (templateRange == null) {
        throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName));
    }
    CellReference[] crefs = templateRange.getAllReferencedCells();

    CellReference topLeft, bottomRight;
    AreaReference resultRange;

    int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand;
    int rownumBackup = rownum;

    if (crefs != null) {
        addRangeBounds(band, crefs);

        ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>();

        int currentRowNum = -1;
        int currentRowCount = -1;
        int currentColumnCount = 0;
        int offset = 0;

        topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0);
        // no child bands - merge regions now
        if (band.getChildrenList().isEmpty()) {
            copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand,
                    getCellFromReference(crefs[0], templateSheet).getColumnIndex());
        }

        for (CellReference cellRef : crefs) {
            HSSFCell templateCell = getCellFromReference(cellRef, templateSheet);
            HSSFRow resultRow;
            if (templateCell.getRowIndex() != currentRowNum) { //create new row
                resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand);
                copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum());
                rowsAddedByHorizontalBand += 1;

                //todo move to options
                if (templateCell.getCellStyle().getParentStyle() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null
                        && templateCell.getCellStyle().getParentStyle().getUserStyleName()
                                .equals(DYNAMIC_HEIGHT_STYLE)) {
                    //resultRow.setHeight(templateCell.getRow().getHeight());
                } else {
                    resultRow.setHeight(templateCell.getRow().getHeight());
                }
                resultRows.add(resultRow);

                currentRowNum = templateCell.getRowIndex();
                currentRowCount++;
                currentColumnCount = 0;
                offset = templateCell.getColumnIndex();
            } else { // or write cell to current row
                resultRow = resultRows.get(currentRowCount);
                currentColumnCount++;
            }

            copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band);
        }

        bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount);
        resultRange = new AreaReference(topLeft, bottomRight);

        areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange),
                new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange));
        bandsToResultRanges.put(band,
                new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1,
                        resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1,
                        resultRange.getLastCell().getRow() + 1));
    }

    for (BandData child : band.getChildrenList()) {
        writeBand(child);
    }

    // scheduled merge regions
    if (!band.getChildrenList().isEmpty() && crefs != null) {
        copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup,
                getCellFromReference(crefs[0], templateSheet).getColumnIndex());
    }

    rownum += rowsAddedByHorizontalBand;
    rowsAddedByHorizontalBand = 0;
    rownum += rowsAddedByVerticalBand;
    rowsAddedByVerticalBand = 0;
}

---

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * copies template cell to result row into result column. Fills this cell with data from band
 *
 * @param templateCell - template cell//ww  w .j  a  v  a 2s.co m
 * @param resultRow    - result row
 * @param resultColumn - result column
 * @param band         - band
 */
private HSSFCell copyCellFromTemplate(HSSFCell templateCell, HSSFRow resultRow, int resultColumn,
        BandData band) {
    if (templateCell == null)
        return null;

    HSSFCell resultCell = resultRow.createCell(resultColumn);

    HSSFCellStyle templateStyle = templateCell.getCellStyle();
    HSSFCellStyle resultStyle = copyCellStyle(templateStyle);
    resultCell.setCellStyle(resultStyle);

    String templateCellValue = "";
    int cellType = templateCell.getCellType();

    if (cellType != HSSFCell.CELL_TYPE_FORMULA && cellType != HSSFCell.CELL_TYPE_NUMERIC) {
        HSSFRichTextString richStringCellValue = templateCell.getRichStringCellValue();
        templateCellValue = richStringCellValue != null ? richStringCellValue.getString() : "";

        templateCellValue = extractStyles(templateCell, resultCell, templateCellValue, band);
    }

    if (cellType == HSSFCell.CELL_TYPE_STRING && containsJustOneAlias(templateCellValue)) {
        updateValueCell(rootBand, band, templateCellValue, resultCell,
                drawingPatriarchsMap.get(resultCell.getSheet()));
    } else {
        String cellValue = inlineBandDataToCellString(templateCell, templateCellValue, band);
        setValueToCell(resultCell, cellValue, cellType);
    }

    return resultCell;
}

---

From source file:com.nokia.s60tools.swmtanalyser.model.ExcelCreator.java

License:Open Source License

private void createOverviewFields(HSSFRow row) {
    HSSFCell cell = row.createCell(0);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Threads"));

    cell = row.createCell(1);/*w  ww. java2  s  .  c  om*/
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Status"));

    cell = row.createCell(2);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Max Heap Size"));

    cell = row.createCell(3);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Change in Heap \n size (in Bytes)"));

    cell = row.createCell(4);
    cell.setCellStyle(styles.get("header1"));
    cell.setCellValue(new HSSFRichTextString("Change in  Heap Allocated  \n space (in Bytes) "));

    cell = row.createCell(5);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Change in Heap \n Free space (in Bytes)"));

    cell = row.createCell(6);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Change in Allocated \n Cell Count"));

    cell = row.createCell(7);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Change in Free \n Cell Count"));

    cell = row.createCell(8);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Change in Slack \n space size (in Bytes) "));

    cell = row.createCell(9);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("Stack size"));

    cell = row.createCell(10);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("No. of Files \n opened"));

    cell = row.createCell(11);
    cell.setCellStyle(styles.get("header1"));
    cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cell.setCellValue(new HSSFRichTextString("No. of \n P&S Handles"));

}

---

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

/**
 * This is a helper method to retrieve the value of a cell regardles of its
 * type, which will be converted into a String.
 * /*from   w  w  w. j a v a2  s.com*/
 * @param cell
 * @return
 */
private Object getCellValue(HSSFCell cell) {
    if (cell == null) {
        return null;
    }
    Object result = null;
    int cellType = cell.getCellType();
    switch (cellType) {
    case HSSFCell.CELL_TYPE_BLANK:
        result = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        result = "ERROR: " + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:

        result = cell.getCellFormula();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle cellStyle = cell.getCellStyle();
        short dataFormat = cellStyle.getDataFormat();

        // assumption is made that dataFormat = 14,
        // when cellType is HSSFCell.CELL_TYPE_NUMERIC
        // is equal to a DATE format.
        if (dataFormat == 164) {
            result = cell.getDateCellValue();
        } else {
            result = cell.getNumericCellValue();
        }
        break;
    case HSSFCell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    default:
        break;
    }
    if (result instanceof Double) {
        return String.valueOf(((Double) result).longValue());
    }
    if (result instanceof Date) {
        return result;
    }
    return result.toString();
}

---

From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java

private Object getCellValue(HSSFCell cell, boolean resolveFormula) {
    if (cell == null) {
        return null;
    }// w w w  .  j av  a2 s  .c  o m
    Object result = null;
    int cellType = cell.getCellType();
    switch (cellType) {
    case HSSFCell.CELL_TYPE_BLANK:
        result = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        result = "ERROR: " + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:

        switch (cell.getCachedFormulaResultType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            /*System.out.println("Last evaluated as: " + cell.getNumericCellValue());
            result = cell.getNumericCellValue();
            break;*/
            if (DateUtil.isCellDateFormatted(cell)) {
                result = cell.getDateCellValue();
            } else {
                result = cell.getNumericCellValue();
            }
            System.out.println("Numeric cell value == " + result);
            break;
        case HSSFCell.CELL_TYPE_STRING:
            System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
            result = cell.getRichStringCellValue();
            break;
        }

        //result = cell.getCellFormula();

        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle cellStyle = cell.getCellStyle();
        short dataFormat = cellStyle.getDataFormat();

        System.out.println("Data format for " + cell.getColumnIndex() + " = " + dataFormat);
        // assumption is made that dataFormat = 14,
        // when cellType is HSSFCell.CELL_TYPE_NUMERIC
        // is equal to a DATE format.
        //if (dataFormat == 165 || dataFormat == 164 || dataFormat == 14) {
        if (DateUtil.isCellDateFormatted(cell)) {
            result = cell.getDateCellValue();
        } else {
            result = cell.getNumericCellValue();
        }

        if (dataFormat == 0) { // alternative way of getting value : can this be replaced for the entire block
            result = new HSSFDataFormatter().formatCellValue(cell);
        }
        System.out.println("Numeric cell value == " + result);

        break;
    case HSSFCell.CELL_TYPE_STRING:
        //result = cell.getStringCellValue();
        result = cell.getRichStringCellValue();
        System.out.println("String -> " + result);
        break;
    default:
        break;
    }

    if (result instanceof Integer) {
        return String.valueOf((Integer) result);
    } else if (result instanceof Double) {
        return String.valueOf(((Double) result)); //.longValue());
    }
    if (result instanceof Date) {
        return result;
    }
    return result.toString();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• »