org.apache.poi.ss.util.CellAddress Java Examples

private void readComments(ReadSheet readSheet) {
    if (!xlsxReadContext.readWorkbookHolder().getExtraReadSet().contains(CellExtraTypeEnum.COMMENT)) {
        return;
    }
    CommentsTable commentsTable = commentsTableMap.get(readSheet.getSheetNo());
    if (commentsTable == null) {
        return;
    }
    Map<CellAddress, XSSFComment> cellComments = commentsTable.getCellComments();
    for (XSSFComment xssfComment : cellComments.values()) {
        CellExtra cellExtra = new CellExtra(CellExtraTypeEnum.COMMENT, xssfComment.getString().toString(),
            xssfComment.getRow(), xssfComment.getColumn());
        xlsxReadContext.readSheetHolder().setCellExtra(cellExtra);
        xlsxReadContext.analysisEventProcessor().extra(xlsxReadContext);
    }
}
 

@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
    CellAddress cellAddress = new CellAddress(cellReference);
    int row = cellAddress.getRow();
    int headers = options.getHeaderStart();
    int column = cellAddress.getColumn();
    if (row <= headers) {
        columnIndexPerTitle.put(
                options.getCaseInsensitive() ? formattedValue.toLowerCase() : formattedValue,
                column
        );
        titlePerColumnIndex.put(column, getTitleNameForMap(formattedValue, column));
    }
    if (row + 1 <= options.skip()) {
        return;
    }
    if (limit != 0 && internalCount > limit) {
        return;
    }
    internalRow = row;
    setFieldValue(formattedValue, type, column);
}
 

@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
	// create empty column, if needed
	
	CellAddress currentCellAddress = new CellAddress(cellReference);
	for (int i=this.currentColumn;i<currentCellAddress.getColumn();i++) {
		this.spreadSheetCellDAOCurrentRow.add(null);
		this.currentColumn++;
	}
	// add column
	SpreadSheetCellDAO currentDAO = null;
	if (comment!=null) {
		currentDAO = new SpreadSheetCellDAO(formattedValue,comment.getString().getString(), "", cellReference,this.sheetName);
	} else {
		currentDAO = new SpreadSheetCellDAO(formattedValue,"", "", cellReference,this.sheetName);
	}
	this.currentColumn++;
	this.spreadSheetCellDAOCurrentRow.add(currentDAO);
}
 

/**
 * Finds all cell comments in this sheet and adds them to the specified locations map
 *
 * @param container a container that may contain HSSFComments
 * @param locations the map to store the HSSFComments in
 */
private void findCellCommentLocations(HSSFShapeContainer container, Map<CellAddress, HSSFComment> locations) {
    for (Object object : container.getChildren()) {
        HSSFShape shape = (HSSFShape) object;
        if (shape instanceof HSSFShapeGroup) {
            findCellCommentLocations((HSSFShapeGroup) shape, locations);
            continue;
        }
        if (shape instanceof HSSFComment) {
            HSSFComment comment = (HSSFComment) shape;
            if (comment.hasPosition()) {
                locations.put(new CellAddress(comment.getRow(), comment.getColumn()), comment);
            }
        }
    }
}
 

public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

@Override
public List<CellValue> getDataFromRange(String range)
{
    return StreamSupport.stream(CellRangeAddress.valueOf(range).spliterator(), false)
            .map(CellAddress::formatAsString)
            .map(addr -> new CellValue(getDataFromCell(addr), addr))
            .collect(Collectors.toList());
}
 

public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

public void cell(String cellReference, String formattedValue, XSSFComment comment) {
	if (firstCellOfRow) {
		firstCellOfRow = false;
	} else {
		_resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
	}

	// gracefully handle missing CellRef here in a similar way as
	// XSSFCell does
	if (cellReference == null) {
		cellReference = new CellAddress(currentRow, currentCol).formatAsString();
	}

	// Did we miss any cells?
	int thisCol = (new CellReference(cellReference)).getCol();
	int missedCols = thisCol - currentCol - 1;
	for (int i = 0; i < missedCols; i++) {
		_resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
	}
	currentCol = thisCol;

	// Number or string?
	try {
		Double.parseDouble(formattedValue);
		_resultRowTmp.append(formattedValue);
	} catch (NumberFormatException e) {
		_resultRowTmp.append(formattedValue);
	}
}
 

public void cell(String cellReference, String formattedValue, XSSFComment comment) {
    if (firstCellOfRow) {
        firstCellOfRow = false;
    } else {
        _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
    }

    // gracefully handle missing CellRef here in a similar way as
    // XSSFCell does
    if (cellReference == null) {
        cellReference = new CellAddress(currentRow, currentCol).formatAsString();
    }

    // Did we miss any cells?
    int thisCol = (new CellReference(cellReference)).getCol();
    int missedCols = thisCol - currentCol - 1;
    for (int i = 0; i < missedCols; i++) {
        _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
    }
    currentCol = thisCol;

    // Number or string?
    try {
        Double.parseDouble(formattedValue);
        _resultRowTmp.append(formattedValue);
    } catch (NumberFormatException e) {
        _resultRowTmp.append(formattedValue);
    }
}
 

@Override
public void cell(String cellReference, String formattedValue, XSSFComment xssfComment) {
    if (cellReference == null) {
        cellReference = new CellAddress(currentRow, currentCol).formatAsString();
    }
    int column = new CellReference(cellReference).getCol();
    currentCol = column;

    ColumnInfo currentColumnInfo = columns.get(column);
    if (Objects.isNull(currentColumnInfo)) {
        return;
    }

    if (isHeaderRow && !entityHandler.isSkipHeaderRow()) {
        if (!currentColumnInfo.getName().equalsIgnoreCase(formattedValue.trim())) {
            throw new ZeroCellException(String.format("Expected Column '%s' but found '%s'", currentColumnInfo.getName(), formattedValue));
        }
    }
    // Prevent from trying to write to a null instance
    if (Objects.isNull(cur)) return;
    if (entityHandler.isSkipEmptyRows()) {
        if (formattedValue == null || formattedValue.isEmpty()) {
            emptyColumnCounter.increment();
        }
    }
    writeColumnField(cur, formattedValue, currentColumnInfo, currentRow);
}
 

private void init(Comments commentsTable) {
    if (commentsTable != null) {
        commentCellRefs = new LinkedList<>();
        for (Iterator<CellAddress> iter = commentsTable.getCellAddresses(); iter.hasNext(); ) {
            commentCellRefs.add(iter.next());
        }
    }
}
 

/**
 * {@inheritDoc}
 */
@Override
public void setActiveCell(CellAddress address) {
    int row = address.getRow();
    short col = (short) address.getColumn();
    _sheet.setActiveCellRow(row);
    _sheet.setActiveCellCol(col);
}
 

/**
 * {@inheritDoc}
 */
@Override
public CellAddress getActiveCell() {
    int row = _sheet.getActiveCellRow();
    int col = _sheet.getActiveCellCol();
    return new CellAddress(row, col);
}
 

/**
 * Returns all cell comments on this sheet.
 * @return A map of each Comment in the sheet, keyed on the cell address where
 * the comment is located.
 */
@Override
public Map<CellAddress, HSSFComment> getCellComments() {
    HSSFPatriarch patriarch = getDrawingPatriarch();
    if (null == patriarch) {
        patriarch = createDrawingPatriarch();
    }
    
    Map<CellAddress, HSSFComment> locations = new TreeMap<CellAddress, HSSFComment>();
    findCellCommentLocations(patriarch, locations);
    return locations;
}
 

void addFormat(CellAddress cellAddress, short formatIndex, String formatString, String cellType, String cellStyleStr) {
    final InternalCellFormat poijiCellFormat = new InternalCellFormat();
    poijiCellFormat.setCellAddress(cellAddress);
    poijiCellFormat.setCellStypeStr(cellStyleStr);
    poijiCellFormat.setFormatIndex(formatIndex);
    poijiCellFormat.setFormatString(formatString);
    poijiCellFormat.setCellType(cellType);
    formats.add(poijiCellFormat);
}
 

@Override
public Comment getCellComment(CellAddress ref) {
    throw new UnsupportedOperationException();
}
 

@Override
public CellAddress getAddress() {
    return new CellAddress(getRow(), getColumn());
}
 

@Override
public Map<CellAddress, ? extends Comment> getCellComments() {
    throw new UnsupportedOperationException();
}
 

@Override
public void setActiveCell(CellAddress address) {
    throw new UnsupportedOperationException();
}
 

@Override
public Hyperlink getHyperlink(CellAddress addr) {
    throw new UnsupportedOperationException();
}
 

@Override
public CellAddress getActiveCell() {
    throw new UnsupportedOperationException();
}
 

@Override
public Comment getCellComment(CellAddress ref) {
    throw new UnsupportedOperationException();
}
 

@Override
public Map<CellAddress, ? extends Comment> getCellComments() {
    throw new UnsupportedOperationException();
}
 

@Override
public void setActiveCell(CellAddress address) {
    throw new UnsupportedOperationException();
}
 

@Override
public Hyperlink getHyperlink(CellAddress addr) {
    throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Comment getCellComment(CellAddress cellAddress) {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Map<CellAddress, ? extends Comment> getCellComments() {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Hyperlink getHyperlink(CellAddress cellAddress) {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public CellAddress getActiveCell() {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public void setActiveCell(CellAddress cellAddress) {
  throw new UnsupportedOperationException();
}