org.apache.poi.ss.usermodel.SheetConditionalFormatting#org.apache.poi.ss.util.CellAddress源码实例Demo

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);
}
 

/**
 * 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);
            }
        }
    }
}
 

@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);
}
 

@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());
}
 

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);
}
 

/**
 * 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;
}
 

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

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

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

@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);
}
 

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);
	}
}
 

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 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 void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {

    super.startElement(uri, localName, qName, attributes);

    if (this.cellFormat == null) {
        return;
    }
    if (uri != null && !uri.equals(NS_SPREADSHEETML)) {
        return;
    }

    if ("c".equals(localName)) {
        // Set up defaults.
        String cellRef = attributes.getValue("r");
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        CellAddress cellAddress = new CellAddress(cellRef);
        if ("b".equals(cellType) || "e".equals(cellType) || "inlineStr".equals(cellType)) {
            this.cellFormat.addFormat(cellAddress, (short) 0, null, cellType, cellStyleStr);
            return;
        }
        if ("s".equals(cellType) || "str".equals(cellType)) {
            this.cellFormat.addFormat(cellAddress, (short) 0, null, cellType, cellStyleStr);
            return;
        }
        // Number, but almost certainly with a special style or format
        XSSFCellStyle style = null;
        if (stylesTable != null) {
            if (cellStyleStr != null) {
                int styleIndex = Integer.parseInt(cellStyleStr);
                style = stylesTable.getStyleAt(styleIndex);
            } else if (stylesTable.getNumCellStyles() > 0) {
                style = stylesTable.getStyleAt(0);
            }
        }
        if (style != null) {
            short formatIndex = style.getDataFormat();
            String formatString = style.getDataFormatString();
            if (formatString == null)
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);

            this.cellFormat.addFormat(cellAddress, formatIndex, formatString, cellType, cellStyleStr);
        }

    }
}
 

public CellAddress getCellAddress() {
    return cellAddress;
}
 

void setCellAddress(CellAddress cellAddress) {
    this.cellAddress = cellAddress;
}
 

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

@Override
public void setAddress(CellAddress address) {
    setRow(address.getRow());
    setColumn(address.getColumn());
}
 

/**
 * {@inheritDoc}
 */
@Override
public CellAddress getAddress() {
    return new CellAddress(this);
}
 

/**
 * Output an empty-cell comment.
 */
private void outputEmptyCellComment(CellAddress cellRef) {
    XSSFComment comment = comments.findCellComment(cellRef);
    output.cell(cellRef.formatAsString(), null, comment);
}
 

@Override
public void cell(String cellReference, final String formattedValue,
    final XSSFComment xssfComment) {
  if (java.util.Objects.isNull(cur)) return;
  // Gracefully handle missing CellRef here in a similar way as XSSFCell does
  if(cellReference == null) {
    cellReference = new CellAddress(currentRow, currentCol).formatAsString();
  }
  int column = new CellReference(cellReference).getCol();
  currentCol = column;
  if (COL_0 == column) {
    assertColumnName("ID", formattedValue);
    //Handle any exceptions thrown by the converter - this stops execution of the whole process;
    try {
      cur.setId(new com.creditdatamw.zerocell.example.IdPrefixingConverter().convert(formattedValue, "ID", currentRow));
    } catch(Exception e) {
      throw new ZeroCellException("com.creditdatamw.zerocell.example.IdPrefixingConverter threw an exception while trying to convert value " + formattedValue, e);
    }
    return;
  }
  if (COL_1 == column) {
    assertColumnName("FIRST_NAME", formattedValue);
    cur.setFirstName(noop.convert(formattedValue, "FIRST_NAME", currentRow));
    return;
  }
  if (COL_2 == column) {
    assertColumnName("MIDDLE_NAME", formattedValue);
    cur.setMiddleName(noop.convert(formattedValue, "MIDDLE_NAME", currentRow));
    return;
  }
  if (COL_3 == column) {
    assertColumnName("LAST_NAME", formattedValue);
    cur.setLastName(noop.convert(formattedValue, "LAST_NAME", currentRow));
    return;
  }
  if (COL_4 == column) {
    assertColumnName("DATE_OF_BIRTH", formattedValue);
    cur.setDateOfBirth(toLocalDate.convert(formattedValue, "DATE_OF_BIRTH", currentRow));
    return;
  }
  if (COL_6 == column) {
    assertColumnName("DATE_REGISTERED", formattedValue);
    cur.setDateOfRegistration(toLocalDate.convert(formattedValue, "DATE_REGISTERED", currentRow));
    return;
  }
  if (COL_5 == column) {
    assertColumnName("FAV_NUMBER", formattedValue);
    cur.setFavouriteNumber(toInteger.convert(formattedValue, "FAV_NUMBER", currentRow));
    return;
  }
}
 

@Documentation(
    value = "Insert a table from an Excel .xlsx file.",
    params = {
        @Param(name = "uri", value = "The Excel .xlsx file uri, it can be relative to the template"),
        @Param(name = "sheetName", value = "The sheet name"),
        @Param(name = "topLeftCellAdress", value = "The top left cell address"),
        @Param(name = "bottomRightCellAdress", value = "The bottom right cell address"),
        @Param(name = "languageTag", value = "The language tag for the locale"),
    },
    result = "insert the table",
    examples = {
        @Example(expression = "'excel.xlsx'.asTable('Feuil1', 'C3', 'F7', 'fr-FR')", result = "insert the table from 'excel.xlsx'"),
    }
)
// @formatter:on
public MTable asTable(String uriStr, String sheetName, String topLeftCellAdress, String bottomRightCellAdress,
        String languageTag) throws IOException {
    final MTable res = new MTableImpl();

    final URI xlsxURI = URI.createURI(uriStr, false);
    final URI uri = xlsxURI.resolve(templateURI);

    try (XSSFWorkbook workbook = new XSSFWorkbook(uriConverter.createInputStream(uri));) {
        final FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
        final XSSFSheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            throw new IllegalArgumentException(String.format("The sheet %s doesn't exist in %s.", sheetName, uri));
        } else {
            final Locale locale;
            if (languageTag != null) {
                locale = Locale.forLanguageTag(languageTag);
            } else {
                locale = Locale.getDefault();
            }
            final DataFormatter dataFormatter = new DataFormatter(locale);
            final CellAddress start = new CellAddress(topLeftCellAdress);
            final CellAddress end = new CellAddress(bottomRightCellAdress);
            int rowIndex = start.getRow();
            while (rowIndex <= end.getRow()) {
                final XSSFRow row = sheet.getRow(rowIndex++);
                if (row != null) {
                    final MRow mRow = new MRowImpl();
                    int cellIndex = start.getColumn();
                    while (cellIndex <= end.getColumn()) {
                        final XSSFCell cell = row.getCell(cellIndex++);
                        if (cell != null) {
                            final MStyle style = getStyle(cell);
                            final MElement text = new MTextImpl(dataFormatter.formatCellValue(cell, evaluator),
                                    style);
                            final Color background = getColor(cell.getCellStyle().getFillForegroundColorColor());
                            final MCell mCell = new MCellImpl(text, background);
                            mRow.getCells().add(mCell);
                        } else {
                            mRow.getCells().add(createEmptyCell());
                        }
                    }
                    res.getRows().add(mRow);
                } else {
                    final int length = end.getColumn() - start.getColumn() + 1;
                    res.getRows().add(createEmptyRow(length));
                }
            }

        }
    }

    return res;
}
 

@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();
}
 

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