Example usage for org.apache.poi.ss.usermodel Cell getSheet

List of usage examples for org.apache.poi.ss.usermodel Cell getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getSheet.

Prototype

Sheet getSheet();

Source Link

Document

Returns the sheet this cell belongs to

Usage

From source file:at.mukprojects.exclycore.model.ExclyDateError.java

License:Open Source License

@Override
public void setCell(Cell cell, CellStyle cellStyle) {
    cell.setCellValue(ERRORCODE);//from   w  ww .  j  av  a  2s  . co m
    CellStyle cellStyleError = cell.getSheet().getWorkbook().createCellStyle();
    cellStyleError.cloneStyleFrom(cellStyle);
    cellStyleError.setFillForegroundColor(IndexedColors.RED.getIndex());
    cellStyleError.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(cellStyleError);
}

From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java

License:Open Source License

public static String getCellValue(Cell cell) {
    // 2016.01.20 j.amano
    // ?jxl to poi ?????
    //------------------------
    //??:\-1,000/*  w ww  .  j av  a  2  s  . co m*/
    //jxl:($1,000)?$?????????
    //poi:-1000
    //------------------------
    //??:2016/1/20
    //jxl:0020, 1 20, 2016
    //poi:2016/01/20 00:00:00
    //------------------------
    //??:#REF!???
    //jxl:#REF!
    //poi:#REF!
    //------------------------
    //??:1,000
    //jxl:" "1,000
    //poi:-1000
    //------------------------

    if (cell != null) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            return "";
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_NUMERIC:
            // ??
            if (DateUtil.isCellDateFormatted(cell)) {
                // ????
                Date dt = cell.getDateCellValue();
                // ????
                DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                String sDate = df.format(dt);
                return sDate;
            }
            // ???.0
            DecimalFormat format = new DecimalFormat("0.#");
            return format.format(cell.getNumericCellValue());
        case Cell.CELL_TYPE_FORMULA:
            Workbook wb = cell.getSheet().getWorkbook();
            CreationHelper crateHelper = wb.getCreationHelper();
            FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
            return getCellValue(evaluator.evaluateInCell(cell));
        case Cell.CELL_TYPE_ERROR:
            byte errorCode = cell.getErrorCellValue();
            FormulaError error = FormulaError.forInt(errorCode);
            String errorText = error.getString();
            return errorText;
        default:
            return "";
        }
    }
    return "";
}

From source file:br.com.objectos.way.io.WayIO.java

License:Apache License

static Workbook workbookOf(Cell cell) {
    Sheet s = cell.getSheet();
    return s.getWorkbook();
}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell/*from w w  w  .j  a  v  a  2  s  . co  m*/
 */
public void createImageCell(Cell cell, double height, String imagePath, byte[] data) throws Exception {
    if (height > cell.getRow().getHeight()) {
        cell.getRow().setHeight((short) height);
    }
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
    }
    if (StringUtils.isNotEmpty(imagePath)) {
        data = ImageCache.getImage(imagePath);
    }
    if (data != null) {
        PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
    }

}

From source file:cn.afterturn.easypoi.excel.export.base.BaseExportService.java

License:Apache License

/**
 * Cell/*w w  w  . j ava 2s  . c  om*/
 */
public void createImageCell(Cell cell, double height, int rowspan, int colspan, String imagePath, byte[] data)
        throws Exception {
    if (height > cell.getRow().getHeight()) {
        cell.getRow().setHeight((short) height);
    }
    ClientAnchor anchor;
    if (type.equals(ExcelType.HSSF)) {
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);
    } else {
        anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(),
                (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan);
    }
    if (StringUtils.isNotEmpty(imagePath)) {
        data = ImageCache.getImage(imagePath);
    }
    if (data != null) {
        PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
    }

}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?//from  w  w  w .j a  va  2 s  .  c  o  m
 *
 * @param cell
 * @param map
 * @param name
 * @throws Exception
 */
private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = !name.contains(FOREACH_NOT_CREATE);
    boolean isShift = name.contains(FOREACH_AND_SHIFT);
    name = name.replace(FOREACH_NOT_CREATE, EMPTY).replace(FOREACH_AND_SHIFT, EMPTY).replace(FOREACH, EMPTY)
            .replace(START_STR, EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
    Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper);
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
    @SuppressWarnings("unchecked")
    List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
    Row row = null;
    int rowIndex = cell.getRow().getRowNum() + 1;
    //??
    if (its.hasNext()) {
        Object t = its.next();
        setForEeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan,
                colspan, mergedRegionHelper);
        rowIndex += rowspan - 1;
    }
    //?????,??
    if (isShift && datas.size() * rowspan > 1
            && cell.getRowIndex() + rowspan < cell.getRow().getSheet().getLastRowNum()) {
        int lastRowNum = cell.getRow().getSheet().getLastRowNum();
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan, lastRowNum,
                (datas.size() - 1) * rowspan, true, true);
        mergedRegionHelper.shiftRows(cell.getSheet(), cell.getRowIndex() + rowspan,
                (datas.size() - 1) * rowspan);
        templateSumHandler.shiftRows(cell.getRowIndex() + rowspan, (datas.size() - 1) * rowspan);
        PoiExcelTempUtil.reset(cell.getSheet(), cell.getRowIndex() + rowspan + (datas.size() - 1) * rowspan,
                cell.getRow().getSheet().getLastRowNum());
    }
    while (its.hasNext()) {
        Object t = its.next();
        row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
        setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan, colspan,
                mergedRegionHelper);
        rowIndex += rowspan;
    }
}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ?Cell??set//from   w  ww.j  a  v  a  2 s  .com
 *
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
    CellType cellType = cell.getCellType();
    if (cellType != CellType.STRING && cellType != CellType.NUMERIC) {
        return;
    }
    String oldString;
    oldString = cell.getStringCellValue();
    if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
        // step 2. ???
        String params = null;
        boolean isNumber = false;
        if (isNumber(oldString)) {
            isNumber = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        boolean isStyleBySelf = false;
        if (isStyleBySelf(oldString)) {
            isStyleBySelf = true;
            oldString = oldString.replaceFirst(NUMBER_SYMBOL, "");
        }
        Object obj = PoiPublicUtil.getRealValue(oldString, map);
        // ,// 
        if (obj instanceof ImageEntity) {
            ImageEntity img = (ImageEntity) obj;
            cell.setCellValue("");
            if (img.getRowspan() > 1 || img.getColspan() > 1) {
                img.setHeight(0);
                PoiMergeCellUtil.addMergedRegion(cell.getSheet(), cell.getRowIndex(),
                        cell.getRowIndex() + img.getRowspan() - 1, cell.getColumnIndex(),
                        cell.getColumnIndex() + img.getColspan() - 1);
            }
            createImageCell(cell, img.getHeight(), img.getRowspan(), img.getColspan(), img.getUrl(),
                    img.getData());
        } else if (isNumber && StringUtils.isNotBlank(obj.toString())) {
            cell.setCellValue(Double.parseDouble(obj.toString()));
        } else {
            cell.setCellValue(obj.toString());
        }
    }
    //foreach ?
    if (oldString != null && oldString.contains(FOREACH)) {
        addListDataToExcel(cell, map, oldString.trim());
    }

}

From source file:cn.afterturn.easypoi.excel.html.css.impl.BorderCssConverImpl.java

License:Apache License

@Override
public void convertToExcel(Cell cell, CellStyle cellStyle, CellStyleEntity style) {
    if (style == null || style.getBorder() == null) {
        return;//from ww w  .jav  a2s.c  o m
    }
    CellStyleBorderEntity border = style.getBorder();
    for (String pos : new String[] { TOP, RIGHT, BOTTOM, LEFT }) {
        String posName = StringUtils.capitalize(pos.toLowerCase());
        // color
        String colorAttr = null;
        try {
            colorAttr = (String) MethodUtils.invokeMethod(border, "getBorder" + posName + "Color");
        } catch (Exception e) {
            log.error("Set Border Style Error Caused.", e);
        }
        if (StringUtils.isNotEmpty(colorAttr)) {
            if (cell instanceof HSSFCell) {
                HSSFColor poiColor = PoiCssUtils.parseColor((HSSFWorkbook) cell.getSheet().getWorkbook(),
                        colorAttr);
                if (poiColor != null) {
                    try {
                        MethodUtils.invokeMethod(cellStyle, "set" + posName + "BorderColor",
                                poiColor.getIndex());
                    } catch (Exception e) {
                        log.error("Set Border Color Error Caused.", e);
                    }
                }
            }
            if (cell instanceof XSSFCell) {
                XSSFColor poiColor = PoiCssUtils.parseColor(colorAttr);
                if (poiColor != null) {
                    try {
                        MethodUtils.invokeMethod(cellStyle, "set" + posName + "BorderColor", poiColor);
                    } catch (Exception e) {
                        log.error("Set Border Color Error Caused.", e);
                    }
                }
            }
        }
        // width
        int width = 0;
        try {
            String widthStr = (String) MethodUtils.invokeMethod(border, "getBorder" + posName + "Width");
            if (PoiCssUtils.isNum(widthStr)) {
                width = Integer.parseInt(widthStr);
            }
        } catch (Exception e) {
            log.error("Set Border Style Error Caused.", e);
        }
        String styleValue = null;
        try {
            styleValue = (String) MethodUtils.invokeMethod(border, "getBorder" + posName + "Style");
        } catch (Exception e) {
            log.error("Set Border Style Error Caused.", e);
        }
        BorderStyle shortValue = BorderStyle.NONE;
        // empty or solid
        if (StringUtils.isBlank(styleValue) || "solid".equals(styleValue)) {
            if (width > 2) {
                shortValue = BorderStyle.THICK;
            } else if (width > 1) {
                shortValue = BorderStyle.MEDIUM;
            } else {
                shortValue = BorderStyle.THIN;
            }
        } else if (ArrayUtils.contains(new String[] { NONE, HIDDEN }, styleValue)) {
            shortValue = BorderStyle.NONE;
        } else if (DOUBLE.equals(styleValue)) {
            shortValue = BorderStyle.DOUBLE;
        } else if (DOTTED.equals(styleValue)) {
            shortValue = BorderStyle.DOTTED;
        } else if (DASHED.equals(styleValue)) {
            if (width > 1) {
                shortValue = BorderStyle.MEDIUM_DASHED;
            } else {
                shortValue = BorderStyle.DASHED;
            }
        }
        // border style
        if (shortValue != BorderStyle.NONE) {
            try {
                MethodUtils.invokeMethod(cellStyle, "setBorder" + posName, shortValue);
            } catch (Exception e) {
                log.error("Set Border Style Error Caused.", e);
            }
        }
    }
}

From source file:cn.afterturn.easypoi.excel.html.css.impl.WidthCssConverImpl.java

License:Apache License

@Override
public void convertToExcel(Cell cell, CellStyle cellStyle, CellStyleEntity style) {
    if (StringUtils.isNoneBlank(style.getWidth())) {
        int width = (int) Math.round(PoiCssUtils.getInt(style.getWidth()) * 2048 / 8.43F);
        Sheet sheet = cell.getSheet();
        int colIndex = cell.getColumnIndex();
        if (width > sheet.getColumnWidth(colIndex)) {
            if (width > 255 * 256) {
                width = 255 * 256;//from  ww  w.j  av a  2s . co  m
            }
            sheet.setColumnWidth(colIndex, width);
        }
    }
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * foreach?/*from   w ww  . j a v  a  2s. c o m*/
 * @param cell 
 * @param map
 * @param name
 * @throws Exception 
 */
private void addListDataToExcel(Cell cell, Map<String, Object> map, String name) throws Exception {
    boolean isCreate = !name.contains(FOREACH_NOT_CREATE);
    boolean isShift = name.contains(FOREACH_AND_SHIFT);
    name = name.replace(FOREACH_NOT_CREATE, EMPTY).replace(FOREACH_AND_SHIFT, EMPTY).replace(FOREACH, EMPTY)
            .replace(START_STR, EMPTY);
    String[] keys = name.replaceAll("\\s{1,}", " ").trim().split(" ");
    Collection<?> datas = (Collection<?>) PoiPublicUtil.getParamsValue(keys[0], map);
    Object[] columnsInfo = getAllDataColumns(cell, name.replace(keys[0], EMPTY), mergedRegionHelper);
    if (datas == null) {
        return;
    }
    Iterator<?> its = datas.iterator();
    int rowspan = (Integer) columnsInfo[0], colspan = (Integer) columnsInfo[1];
    @SuppressWarnings("unchecked")
    List<ExcelForEachParams> columns = (List<ExcelForEachParams>) columnsInfo[2];
    Row row = null;
    int rowIndex = cell.getRow().getRowNum() + 1;
    //??
    if (its.hasNext()) {
        Object t = its.next();
        setForEeachRowCellValue(isCreate, cell.getRow(), cell.getColumnIndex(), t, columns, map, rowspan,
                colspan, mergedRegionHelper);
        rowIndex += rowspan - 1;
    }
    if (isShift && datas.size() * rowspan > 1) {
        cell.getRow().getSheet().shiftRows(cell.getRowIndex() + rowspan,
                cell.getRow().getSheet().getLastRowNum(), (datas.size() - 1) * rowspan, true, true);
        /* cell.getRow().getSheet().shiftRows(cell.getRowIndex() + 1,
        cell.getRow().getSheet().getLastRowNum(), datas.size() * rowspan - 1, true, true);*/
        templateSumHanlder.shiftRows(cell.getRowIndex(), (datas.size() - 1) * rowspan);
    }
    while (its.hasNext()) {
        Object t = its.next();
        row = createRow(rowIndex, cell.getSheet(), isCreate, rowspan);
        setForEeachRowCellValue(isCreate, row, cell.getColumnIndex(), t, columns, map, rowspan, colspan,
                mergedRegionHelper);
        rowIndex += rowspan;
    }
}