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:fitlibrary.runner.SpreadsheetRunner.java

License:Open Source License

private String format(HSSFCell cell, HSSFWorkbook workbook) {
    if (cell == null)
        return "";
    String value = value(cell);/*from   w  w w .  j a v a2 s.co m*/
    HSSFCellStyle style = cell.getCellStyle();
    HSSFFont font = workbook.getFontAt(style.getFontIndex());
    // System.err.println("Formatting "+value(cell)+"= "+font.getFontHeight());
    if (font.getItalic())
        value = tag("i", value);
    if (font.getBoldweight() > 400)
        value = tag("b", value);
    if (font.getUnderline() > 0)
        value = tag("u", value);
    if (font.getFontHeight() >= 480)
        value = tag("h1", value);
    else if (font.getFontHeight() >= 280)
        value = tag("h2", value);
    else if (font.getFontHeight() > 200)
        value = tag("h3", value);
    return value;
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

/**
 * (Each Excel sheet cell becomes an HTML table cell) Generates an HTML
 * table cell which has the same font styles, alignments, colours and
 * borders as the Excel cell./*w  ww .j  a v  a  2 s  . co m*/
 *
 * @param cell
 *            The Excel cell.
 */

private void td(final HSSFCell cell) {
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;
        mergeEnd = -1;
        return;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return;
    }
    //KLO 05022018
    //out.append("<td ");

    out.append("<td height=\"15\" ");
    if (colspan > 1) {
        out.append("colspan='").append(colspan).append("' ");
    }
    if (cell == null) {
        out.append("/>\n");
        return;
    }

    out.append("style='");
    final HSSFCellStyle style = cell.getCellStyle();
    // Text alignment
    switch (style.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        out.append("text-align: left; ");
        break;
    case CellStyle.ALIGN_RIGHT:
        out.append("text-align: right; ");
        break;
    case CellStyle.ALIGN_CENTER:
        out.append("text-align: center; ");
        break;
    default:
        break;
    }

    // Font style, size and weight
    final HSSFFont font = style.getFont(book);
    if (font == null)
        return;
    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
        out.append("font-weight: bold; ");
    }
    if (font.getItalic()) {
        out.append("font-style: italic; ");
    }
    if (font.getUnderline() != HSSFFont.U_NONE) {
        out.append("text-decoration: underline; ");
    }
    out.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; ");
    // Cell background and font colours
    final short[] backRGB = style.getFillForegroundColorColor().getTriplet();
    final HSSFColor foreColor = palette.getColor(font.getColor());
    if (foreColor != null) {
        final short[] foreRGB = foreColor.getTriplet();
        if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) {
            out.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',')
                    .append(foreRGB[2]).append(");");
        }
    }
    if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) {
        out.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',')
                .append(backRGB[2]).append(");");
    }
    // Border
    if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-top-style: solid; ");
    }
    if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-right-style: solid; ");
    }
    if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-bottom-style: solid; ");
    }
    if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) {
        out.append("border-left-style: solid; ");
    }
    out.append("'>");
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                out.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                out.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                out.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    if (pix.containsKey(rowIndex)) {
        if (pix.get(rowIndex).containsKey(colIndex)) {
            for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) {
                out.append("<img alt='Image in Excel sheet' src='data:");
                out.append(pic.getMimeType());
                out.append(";base64,");
                try {
                    out.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII"));
                } catch (final UnsupportedEncodingException e) {
                    throw new RuntimeException(e);
                }
                out.append("'/>");
            }
        }
    }

    if (isCode(val) && this.URL != null) {
        val = getHyperLink(val);
    }

    out.append(val);
    out.append("</td>\n");
}

From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java

License:Open Source License

private void td(final HSSFCell cell, StringBuffer buf) {
    int colspan = 1;
    if (colIndex == mergeStart) {
        // First cell in the merging region - set colspan.
        colspan = mergeEnd - mergeStart + 1;
    } else if (colIndex == mergeEnd) {
        // Last cell in the merging region - no more skipped cells.
        mergeStart = -1;//from   w  w w.  ja  va  2 s  .c om
        mergeEnd = -1;
        return;
    } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) {
        // Within the merging region - skip the cell.
        return;
    }

    //KLO 05022018
    //buf.append("<td ");

    buf.append("<td height=\"15\" ");

    if (colspan > 1) {
        buf.append("colspan='").append(colspan).append("' ");
    }
    if (cell == null) {
        buf.append("/>");
        return;
    }
    buf.append("style='");
    final HSSFCellStyle style = cell.getCellStyle();
    // Text alignment
    switch (style.getAlignment()) {
    case CellStyle.ALIGN_LEFT:
        buf.append("text-align: left; ");
        break;
    case CellStyle.ALIGN_RIGHT:
        buf.append("text-align: right; ");
        break;
    case CellStyle.ALIGN_CENTER:
        buf.append("text-align: center; ");
        break;
    default:
        break;
    }
    // Font style, size and weight
    final HSSFFont font = style.getFont(book);
    if (font == null)
        return;
    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
        buf.append("font-weight: bold; ");
    }
    if (font.getItalic()) {
        buf.append("font-style: italic; ");
    }
    if (font.getUnderline() != HSSFFont.U_NONE) {
        buf.append("text-decoration: underline; ");
    }
    buf.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; ");

    // Cell background and font colours
    final short[] backRGB = style.getFillForegroundColorColor().getTriplet();
    final HSSFColor foreColor = palette.getColor(font.getColor());
    if (foreColor != null) {
        final short[] foreRGB = foreColor.getTriplet();
        if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) {
            buf.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',')
                    .append(foreRGB[2]).append(");");

        }
    }
    if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) {
        buf.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',')
                .append(backRGB[2]).append(");");

    }
    // Border
    if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-top-style: solid; ");
    }
    if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-right-style: solid; ");
    }
    if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-bottom-style: solid; ");
    }
    if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) {
        buf.append("border-left-style: solid; ");
    }
    buf.append("'>");
    String val = "";
    try {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            val = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            // POI does not distinguish between integer and double, thus:
            final double original = cell.getNumericCellValue(), rounded = Math.round(original);
            if (Math.abs(rounded - original) < 0.00000000000000001) {
                val = String.valueOf((int) rounded);
            } else {
                val = String.valueOf(original);
            }
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            final CellValue cv = evaluator.evaluate(cell);
            if (cv == null)
                return;
            switch (cv.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                buf.append(cv.getBooleanValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                buf.append(cv.getNumberValue());
                break;
            case Cell.CELL_TYPE_STRING:
                buf.append(cv.getStringValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                break;
            }
            break;
        default:
            // Neither string or number? Could be a date.
            try {
                val = sdf.format(cell.getDateCellValue());
            } catch (final Exception e1) {
            }
        }
    } catch (final Exception e) {
        val = e.getMessage();
    }
    if ("null".equals(val)) {
        val = "";
    }
    if (pix.containsKey(rowIndex)) {
        if (pix.get(rowIndex).containsKey(colIndex)) {
            for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) {
                buf.append("<img alt='Image in Excel sheet' src='data:");
                buf.append(pic.getMimeType());
                buf.append(";base64,");

                try {
                    buf.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII"));

                } catch (final UnsupportedEncodingException e) {
                    throw new RuntimeException(e);
                }
                buf.append("'/>");
            }
        }
    }

    if (isCode(val) && this.URL != null) {
        val = getHyperLink(val);
    }
    buf.append(val);
    buf.append("</td>");

}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static boolean getIsBold(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) {
    HSSFCell cell = sheet.getRow(row).getCell(col);
    HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex());

    return font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD;
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static boolean getIsItalic(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) {
    HSSFCell cell = sheet.getRow(row).getCell(col);
    HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex());

    return font.getItalic();
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static short getFontColor(HSSFWorkbook workbook, HSSFSheet sheet, int row, short col) {
    HSSFCell cell = sheet.getRow(row).getCell(col);
    HSSFFont font = workbook.getFontAt(cell.getCellStyle().getFontIndex());

    return font.getColor();
}

From source file:guineu.data.parser.impl.LCMSParserXLS.java

License:Open Source License

/**
 * Reads lipid information of one row./*from   www.j  a va 2s .  c o  m*/
 * @param row
 * @param numberCols
 * @return
 */
public void readRow(HSSFRow row) {
    HSSFCell cell;
    SimplePeakListRowLCMS lipid = new SimplePeakListRowLCMS();
    for (int i = 0; i < row.getLastCellNum(); i++) {
        try {
            String title = head.get(i);
            if (title == null) {
                continue;
            }
            cell = row.getCell((short) i);
            boolean isfound = false;
            for (LCMSColumnName field : LCMSColumnName.values()) {
                if (title.matches(field.getRegularExpression())) {
                    if (field == LCMSColumnName.RT) {
                        double rt = cell.getNumericCellValue();
                        if (rt < 20) {
                            rt *= 60;
                            lipid.setVar(field.getSetFunctionName(), rt);
                        } else {
                            lipid.setVar(field.getSetFunctionName(), cell.getNumericCellValue());
                        }
                    } else {
                        lipid.setVar(field.getSetFunctionName(),
                                this.getType(cell.toString(), field.getType()));
                    }
                    isfound = true;
                    break;
                }
            }

            if (!isfound) {
                try {
                    lipid.setPeak(title, cell.getNumericCellValue());
                } catch (Exception e) {
                    if (cell.toString().matches(".*null.*|.*NA.*|.*N/A.*")) {
                        lipid.setPeak(title, 0.0);
                    } else if (cell.toString() != null) {
                        lipid.setPeak(title, cell.toString());
                    }
                }
            }

            if (i == 0 && (cell.getCellStyle().getFillForegroundColor() == 13)) {
                lipid.setStandard(1);
            }
            int DataType = this.v_type(book, row, cell);
            if (DataType == 0) {
                lipid.setControl(false);
                lipid.setName("z-non valid");
            } else {
                lipid.setControl(true);
            }

            if (lipid.getName() == null) {
                lipid.setName("unknown");
            }
            lipid.setLipidClass(String.valueOf(this.LipidClassLib.get_class(lipid.getName())));
        } catch (Exception exception) {
            //exception.printStackTrace();
        }
    }
    this.dataset.addRow(lipid);
}

From source file:guineu.data.parser.impl.ParserXLS.java

License:Open Source License

public int v_type(HSSFWorkbook wb, HSSFRow row, HSSFCell cell) {
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_BLANK:
        System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex()
                + "  - Cell type Blank  - " + cell.toString());
        return 0;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex()
                + "  - Cell type boolean  - " + cell.toString());
        return 0;
    case HSSFCell.CELL_TYPE_FORMULA:
        System.out.println(" Error - Row: " + row.getRowNum() + " Cell: " + cell.getColumnIndex()
                + "  - Cell type formula  - " + cell.toString());
        return 1;
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        HSSFFont font = wb.getFontAt(style.getFontIndex());
        if (font.getColor() == (new HSSFColor.RED().getIndex())) {
            return 0;
        }//from  w ww.  j  a va 2s.c  o  m
        return 1;
    case HSSFCell.CELL_TYPE_STRING:
        style = cell.getCellStyle();
        font = wb.getFontAt(style.getFontIndex());
        if (font.getColor() == (new HSSFColor.RED().getIndex())) {
            return 0;
        }
        return 2;
    default:
        return 0;
    }
}

From source file:hr.restart.sisfun.frmReportxList.java

License:Apache License

void fillDataProc(File orig, HSSFWorkbook wb) {
    DataSet logo = dM.getDataModule().getLogotipovi();
    DataSet orgs = dM.getDataModule().getOrgstruktura();
    String corg = jpc.getCorg();/*from   w  w  w.ja  va2 s.  c  om*/
    while (!ld.raLocate(logo, "CORG", corg)) {
        if (!ld.raLocate(orgs, "CORG", corg)) {
            JOptionPane.showMessageDialog(this.getWindow(), "Greka u organizacijskim jedinicama!", "Greka",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (orgs.getString("PRIPADNOST").equals(corg)) {
            JOptionPane.showMessageDialog(this.getWindow(), "Nije definiran logotip za knjigovodstvo!",
                    "Greka", JOptionPane.ERROR_MESSAGE);
            return;
        }
        corg = orgs.getString("PRIPADNOST");
    }
    raProcess.checkClosing();

    StorageDataSet gk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP");
    raProcess.fillScratchDataSet(gk, "SELECT brojkonta,id,ip FROM gkstavke WHERE "
            + jpc.getCondition().and(Condition.between("DATUMKNJ", fld, "DATFROM", "DATTO")));
    StorageDataSet ogk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP");
    Timestamp old = Util.getUtil().addYears(fld.getTimestamp("DATFROM"), -1);
    raProcess.fillScratchDataSet(ogk,
            "SELECT brojkonta,id,ip FROM gkstavke WHERE " + jpc.getCondition().and(Condition.between("DATUMKNJ",
                    Util.getUtil().getFirstDayOfYear(old), Util.getUtil().getLastDayOfYear(old))));
    gk.enableDataSetEvents(false);
    gk.setSort(new SortDescriptor(new String[] { "BROJKONTA" }));
    ogk.enableDataSetEvents(false);
    ogk.setSort(new SortDescriptor(new String[] { "BROJKONTA" }));

    HSSFDataFormat df = wb.createDataFormat();

    HSSFSheet sh = wb.getSheetAt(0);
    if (sh == null)
        throw new RuntimeException("Greka u plahti!");

    DataSet rep = Repxdata.getDataModule().getTempSet(Condition.equal("CREP", reps));
    rep.open();

    raProcess.checkClosing();
    for (rep.first(); rep.inBounds(); rep.next()) {
        HSSFRow hr = sh.getRow((short) (rep.getInt("RED") - 1));
        HSSFCell cell = hr.getCell((short) (rep.getInt("KOL") - 1));
        if ("S".equals(rep.getString("TIP"))) {
            fillString(cell, logo, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("text"));
        } else if ("2".equals(rep.getString("TIP"))) {
            fillNum(cell, gk, ogk, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
        } else if ("D".equals(rep.getString("TIP"))) {
            fillDate(cell, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("dd.mm.yyyy"));
        }
        raProcess.checkClosing();
    }
    String oname = orig.getAbsolutePath();
    oname = oname.substring(0, oname.length() - 4);

    FileOutputStream out = null;

    try {
        out = new FileOutputStream(oname + "-RA.xls");
        wb.write(out);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (out != null)
            try {
                out.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

From source file:Import.Utils.XSSFConvert.java

/**
 * @param oldCell/*from w  ww .j  a v  a2 s  . c o  m*/
 * @param newCell
 * @param styleMap
 */
public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        int stHashCode = oldCell.getCellStyle().hashCode();
        HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode);
        XSSFCellStyle destnCellStyle = newCell.getCellStyle();
        if (sourceCellStyle == null) {
            sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle();
        }
        destnCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        styleMap.put(stHashCode, sourceCellStyle);
        newCell.setCellStyle(destnCellStyle);
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}