Java Code Examples for org.apache.poi.ss.usermodel.Cell#getSheet()

The following examples show how to use org.apache.poi.ss.usermodel.Cell#getSheet() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: AbstractExcelWriteExecutor.java    From easyexcel with Apache License 2.0 7 votes vote down vote up
private void setImageValue(CellData cellData, Cell cell) {
    Sheet sheet = cell.getSheet();
    int index = sheet.getWorkbook().addPicture(cellData.getImageValue(), HSSFWorkbook.PICTURE_TYPE_PNG);
    Drawing drawing = sheet.getDrawingPatriarch();
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
    }
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setDx1(0);
    anchor.setDx2(0);
    anchor.setDy1(0);
    anchor.setDy2(0);
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
    drawing.createPicture(anchor, index);
}
 
Example 2
Source File: HSSFSheet.java    From lams with GNU General Public License v2.0 6 votes vote down vote up
@Override
public CellRange<HSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
    if (!(rec instanceof FormulaRecordAggregate)) {
        String ref = new CellReference(cell).formatAsString();
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
    }
    FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
    CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());

    CellRange<HSSFCell> result = getCellRange(range);
    // clear all cells in the range
    for (Cell c : result) {
        c.setCellType(CellType.BLANK);
    }
    return result;
}
 
Example 3
Source File: POIImage.java    From excel2pdf with Apache License 2.0 5 votes vote down vote up
public POIImage getCellImage(Cell cell) {
    Sheet sheet = cell.getSheet();
    if (sheet instanceof HSSFSheet) {
        HSSFSheet hssfSheet = (HSSFSheet) sheet;
        if (hssfSheet.getDrawingPatriarch() != null) {
            List<HSSFShape> shapes = hssfSheet.getDrawingPatriarch().getChildren();
            for (HSSFShape shape : shapes) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    PictureData data = pic.getPictureData();
                    String extension = data.suggestFileExtension();
                    int row1 = anchor.getRow1();
                    int row2 = anchor.getRow2();
                    int col1 = anchor.getCol1();
                    int col2 = anchor.getCol2();
                    if (row1 == cell.getRowIndex() && col1 == cell.getColumnIndex()) {
                        dimension = pic.getImageDimension();
                        this.anchor = anchor;
                        this.bytes = data.getData();
                    }
                }
            }
        }
    }
    return this;
}
 
Example 4
Source File: AbstractExcelExtractor.java    From wandora with GNU General Public License v3.0 5 votes vote down vote up
public void associateToSheet(Cell cell, TopicMap tm) throws TopicMapException {
    if(cell.getSheet() != null) {
        Topic sheetTypeTopic = getSheetTypeTopic(tm);
        Topic sheetTopic = getSheetTopic(cell, tm);
        Topic cellTypeTopic = getCellTypeTopic(tm);
        Topic cellTopic = getCellTopic(cell, tm);

        if(sheetTypeTopic != null && sheetTopic != null && cellTypeTopic != null && cellTopic != null) {
            Association a = tm.createAssociation(sheetTypeTopic);
            a.addPlayer(cellTopic, cellTypeTopic);
            a.addPlayer(sheetTopic, sheetTypeTopic);
        }
    }
}
 
Example 5
Source File: AbstractExcelExtractor.java    From wandora with GNU General Public License v3.0 5 votes vote down vote up
public Topic getCellTopic(Cell cell, TopicMap tm) throws TopicMapException {
    String cellIdentifier = null;
    switch(CELL_TOPIC_IS_BASED_ON) {
        case CELL_VALUE: {
            cellIdentifier = getCellValueAsString(cell);
            break;
        }
        case CELL_SHEET_AND_LOCATION: {
            Sheet sheet = cell.getSheet();
            String sheetName = sheet.getSheetName();
            cellIdentifier = sheetName+"-"+cell.getColumnIndex()+"-"+cell.getRowIndex();
            break;
        }
        case CELL_LOCATION: {
            cellIdentifier = cell.getColumnIndex()+"-"+cell.getRowIndex();
            break;
        }
        case CELL_HASH: {
            cellIdentifier = Integer.toString(cell.hashCode());
            break;
        }
    }
    if(cellIdentifier != null) {
        String si = EXCEL_CELL_SI_PREFIX +"/"+ urlEncode(cellIdentifier);
        Topic cellTopic = getOrCreateTopic(tm, si, cellIdentifier);
        cellTopic.addType(getCellTypeTopic(tm));
        return cellTopic;
    }
    return null;
}
 
Example 6
Source File: AbstractExcelExtractor.java    From wandora with GNU General Public License v3.0 5 votes vote down vote up
public Topic getSheetTopic(Cell cell, TopicMap tm) throws TopicMapException {
    Sheet sheet = cell.getSheet();
    if(sheet != null) {
        String sheetName = sheet.getSheetName();
        Topic topic=getOrCreateTopic(tm, EXCEL_SHEET_SI_PREFIX+"/"+urlEncode(sheetName), sheetName);
        topic.addType(getSheetTypeTopic(tm));
        return topic;
    }
    return null;
}
 
Example 7
Source File: DefaultCellCommentHandler.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * 結合を考慮したセルのコメントを取得する。
 * @param cell 元となるセル。
 * @return コメント。コメントが設定されていなければ、nullを返す。
 */
private Comment getMergedCellComment(final Cell cell) {
    Comment comment = cell.getCellComment();
    if(comment != null) {
        return comment;
    }
    
    final Sheet sheet = cell.getSheet();
    final int size = sheet.getNumMergedRegions();
    
    for(int i=0; i < size; i++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(!range.isInRange(cell)) {
            continue;
        }
        
        // nullでないセルを取得する。
        for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            final Row row = sheet.getRow(rowIdx);
            if(row == null) {
                continue;
            }

            for(int colIdx=range.getFirstColumn(); colIdx <= range.getLastColumn(); colIdx++) {
                final Cell valueCell = row.getCell(colIdx);
                if(valueCell == null) {
                    continue;
                }

                comment = valueCell.getCellComment();
                if(comment != null) {
                    return comment;
                }
            }
        }
    }
    
    return null;
    
}
 
Example 8
Source File: VerticalRecordsProcessor.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 縦方向に結合されている場合、結合情報のコピーする。(XlsArrayColumns用)
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getRowSize(mergedRegion);

    if(mergedSize >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                toCell.getColumnIndex(), mergedRegion.getFirstRow(), toCell.getColumnIndex(), mergedRegion.getLastRow());

        // 結合先のセルの書式も設定する
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, fromCell.getColumnIndex(), toCell.getRowIndex()+i);

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex(), toCell.getRowIndex()+i);
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }
    }

}
 
Example 9
Source File: HorizontalRecordsProcessor.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 横方向に結合されている場合、結合情報のコピーする。(XlsArrayColumns用)
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getColumnSize(mergedRegion);

    if(POIUtils.getColumnSize(mergedRegion) >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                mergedRegion.getFirstColumn(), toCell.getRowIndex(), mergedRegion.getLastColumn(), toCell.getRowIndex());

        // 結合先のセルの書式も設定する
        // 中間のセルの設定
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, fromCell.getRowIndex());

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, toCell.getRowIndex());
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }

    }

}
 
Example 10
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * 結合を考慮してセルの罫線(上部)を取得する。
 *
 * @param cell セル
 * @return {@literal BorderStyle}
 * @throws IllegalArgumentException {@literal cell is null.}
 */
public static BorderStyle getBorderTop(final Cell cell) {

    ArgUtils.notNull(cell, "cell");

    final Sheet sheet = cell.getSheet();
    CellRangeAddress mergedRegion = getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());

    final Cell target;
    if(mergedRegion == null) {
        // 結合されていない場合
        target = cell;

    } else {
        if(mergedRegion.getFirstRow() == cell.getRowIndex()) {
            // 引数のCellが上部のセルの場合
            target = cell;
        } else {
            target = getCell(sheet, cell.getColumnIndex(), mergedRegion.getFirstRow());
        }

    }

    final CellStyle style = target.getCellStyle();
    if(style == null) {
        return BorderStyle.NONE;
    } else {
        return style.getBorderTopEnum();
    }

}
 
Example 11
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * 結合を考慮してセルの罫線(下部)を取得する。
 *
 * @param cell セル
 * @return {@literal BorderStyle}
 * @throws IllegalArgumentException {@literal cell is null.}
 */
public static BorderStyle getBorderBottom(final Cell cell) {

    ArgUtils.notNull(cell, "cell");

    final Sheet sheet = cell.getSheet();
    CellRangeAddress mergedRegion = getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());

    final Cell target;
    if(mergedRegion == null) {
        // 結合されていない場合
        target = cell;

    } else {
        if(mergedRegion.getLastRow() == cell.getRowIndex()) {
            // 引数のCellが下部のセルの場合
            target = cell;
        } else {
            target = getCell(sheet, cell.getColumnIndex(), mergedRegion.getLastRow());
        }

    }

    final CellStyle style = target.getCellStyle();
    if(style == null) {
        return BorderStyle.NONE;
    } else {
        return style.getBorderBottomEnum();
    }

}
 
Example 12
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * 結合を考慮してセルの罫線(左部)を取得する。
 *
 * @param cell セル
 * @return {@literal BorderStyle}
 * @throws IllegalArgumentException {@literal cell is null.}
 */
public static BorderStyle getBorderRight(final Cell cell) {

    ArgUtils.notNull(cell, "cell");

    final Sheet sheet = cell.getSheet();
    CellRangeAddress mergedRegion = getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());

    final Cell target;
    if(mergedRegion == null) {
        // 結合されていない場合
        target = cell;

    } else {
        if(mergedRegion.getLastColumn() == cell.getColumnIndex()) {
            // 引数のCellが右部のセルの場合
            target = cell;
        } else {
            target = getCell(sheet, mergedRegion.getLastColumn(), cell.getRowIndex());
        }

    }

    final CellStyle style = target.getCellStyle();
    if(style == null) {
        return BorderStyle.NONE;
    } else {
        return style.getBorderRightEnum();
    }

}
 
Example 13
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * 結合を考慮してセルの罫線(右部)を取得する。
 *
 * @param cell セル
 * @return {@literal BorderStyle}
 * @throws IllegalArgumentException {@literal cell is null.}
 */
public static BorderStyle getBorderLeft(final Cell cell) {

    ArgUtils.notNull(cell, "cell");

    final Sheet sheet = cell.getSheet();
    CellRangeAddress mergedRegion = getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());

    final Cell target;
    if(mergedRegion == null) {
        // 結合されていない場合
        target = cell;

    } else {
        if(mergedRegion.getFirstColumn() == cell.getColumnIndex()) {
            // 引数のCellが左部のセルの場合
            target = cell;
        } else {
            target = getCell(sheet, mergedRegion.getFirstColumn(), cell.getRowIndex());
        }

    }

    final CellStyle style = target.getCellStyle();
    if(style == null) {
        return BorderStyle.NONE;
    } else {
        return style.getBorderLeftEnum();
    }

}
 
Example 14
Source File: POIUtils.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * ハイパーリンクを取得する。
 * <p>結合されているセルの場合にも対応。
 * @param cell
 * @return 見つからない場合は、nullを返す。
 * @throws IllegalArgumentException {@literal cell is null.}
 */
public static Hyperlink getHyperlink(final Cell cell) {

    ArgUtils.notNull(cell, "cell");

    Hyperlink link = cell.getHyperlink();
    if(link != null) {
        return link;
    }

    final Sheet sheet = cell.getSheet();
    CellRangeAddress mergedRange = getMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
    if(mergedRange == null) {
        return null;
    }

    for(Hyperlink item : sheet.getHyperlinkList()) {
        if(item.getFirstRow() == mergedRange.getFirstRow()
                && item.getFirstColumn() == mergedRange.getFirstColumn()) {
            return item;
        }

    }

    return null;

}
 
Example 15
Source File: DefaultCellCommentHandler.java    From xlsmapper with Apache License 2.0 4 votes vote down vote up
@Override
public void handleSave(final Cell cell, final Optional<String> text, final Optional<XlsCommentOption> commentOption) {
    
    if(!text.isPresent()) {
        // コメントが空のとき
        commentOption.ifPresent(option -> {
            if(option.removeIfEmpty()) {
                // コメントが空のとき既存のコメントを削除する
                cell.removeCellComment();
            }
        });
        return;
    }
    
    final Sheet sheet = cell.getSheet();
    final CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    final Drawing<?> drawing = sheet.createDrawingPatriarch();
    
    final Comment comment;
    RichTextString richText = helper.createRichTextString(text.get());
    if(cell.getCellComment() == null) {
        ClientAnchor anchor = createAnchor(drawing, text.get(), cell, commentOption);
        comment = drawing.createCellComment(anchor);
        applyCommentFormat(richText, cell);
    } else {
        // 既存のコメントが存在する場合は、書式やサイズをコピーして使用する。
        comment = cell.getCellComment();
        RichTextString orgText = comment.getString();
        if(orgText.numFormattingRuns() > 0) {
            copyCommentFormat(richText, orgText);
        } else {
            applyCommentFormat(richText, cell);
        }
    }
    
    comment.setString(richText);
    
    // コメントの表示状態の更新
    commentOption.ifPresent(option -> comment.setVisible(option.visible()));
    
    cell.setCellComment(comment);
    
}
 
Example 16
Source File: CellFormulaHandler.java    From xlsmapper with Apache License 2.0 4 votes vote down vote up
/**
 * Excelの式を組み立てる。
 * @param config システム情報設定
 * @param cell セル情報
 * @param targetBean 処理対象のフィールドが定義されているクラスのインスタンス。
 * @return 組み立てた数式
 */
public String createFormulaValue(final Configuration config, final Cell cell, final Object targetBean) {
    
    if(formula.isPresent()) {
        final Map<String, Object> vars = new HashMap<>();
        vars.put("rowIndex", cell.getRowIndex());
        vars.put("columnIndex", cell.getColumnIndex());
        vars.put("rowNumber", cell.getRowIndex()+1);
        vars.put("columnNumber", cell.getColumnIndex()+1);
        vars.put("columnAlpha", CellReference.convertNumToColString(cell.getColumnIndex()));
        vars.put("address", CellPosition.of(cell).formatAsString());
        vars.put("targetBean", targetBean);
        vars.put("cell", cell);
        
        return config.getFormulaFormatter().interpolate(formula.get(), vars);
        
    } else if(method.isPresent()) {
        
        // メソッドの引数の組み立て
        final Class<?>[] paramTypes = method.get().getParameterTypes();
        final Object[] paramValues = new Object[paramTypes.length];
        
        for(int i=0; i < paramTypes.length; i++) {
            if(Cell.class.isAssignableFrom(paramTypes[i])) {
                paramValues[i] = cell;
                
            } else if(CellPosition.class.isAssignableFrom(paramTypes[i])) {
                paramValues[i] = CellPosition.of(cell);
                
            } else if(Point.class.isAssignableFrom(paramTypes[i])) {
                paramValues[i] = CellPosition.of(cell).toPoint();
                
            } else if(org.apache.poi.ss.util.CellAddress.class.isAssignableFrom(paramTypes[i])) {
                paramValues[i] = CellPosition.of(cell).toCellAddress();
                
            } else if(Sheet.class.isAssignableFrom(paramTypes[i])) {
                paramValues[i] = cell.getSheet();
                
            } else if(Configuration.class.isAssignableFrom(paramTypes[i])) {
                paramValues[i] = config;
                
            } else {
                paramValues[i] = null;
            }
        }
        
        try {
            return (String) method.get().invoke(targetBean, paramValues);
            
        } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
            final Class<?> targetClass = targetBean.getClass();
            final Throwable t = e.getCause() == null ? e : e.getCause();
            throw new XlsMapperException(
                    String.format("Fail execute method '%s#%s'.", targetClass.getName(), method.get().getName()),
                    t);
        }
        
    } else {
        // 数式や対応するメソッドがない場合
        throw new IllegalStateException("not found for formula or method.");
    }
    
}