Java Code Examples for org.apache.poi.ss.SpreadsheetVersion#EXCEL97

/**
 * @return the equivalent {@link Ptg} array that the formula would have, were it not shared.
 */
public Ptg[] getFormulaTokens(FormulaRecord formula) {
    int formulaRow = formula.getRow();
    int formulaColumn = formula.getColumn();
    //Sanity checks
    if (!isInRange(formulaRow, formulaColumn)) {
        throw new RuntimeException("Shared Formula Conversion: Coding Error");
    }

    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
    return sf.convertSharedFormulas(field_7_parsed_expr.getTokens(), formulaRow, formulaColumn);
}
 

/**
 * シートの種類を判定する。
 *
 * @since 2.0
 * @param sheet 判定対象のオブジェクト
 * @return シートの種類。不明な場合はnullを返す。
 * @throws IllegalArgumentException {@literal sheet == null}
 */
public static SpreadsheetVersion getVersion(final Sheet sheet) {
    ArgUtils.notNull(sheet, "sheet");

    if(sheet instanceof HSSFSheet) {
        return SpreadsheetVersion.EXCEL97;

    } else if(sheet instanceof XSSFSheet) {
        return SpreadsheetVersion.EXCEL2007;
    }

    return null;
}
 

public static AreaReference getAreaForRange(HSSFWorkbook workbook, String rangeName) {
    int rangeNameIdx = workbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1) return null;

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    return new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
}
 

/**
    * @deprecated use {@link #AreaPtg(AreaReference)} instead
 */
@Deprecated()
@Removal(version="3.19")
   public AreaPtg(String arearef) {
       this(new AreaReference(arearef, SpreadsheetVersion.EXCEL97));
   }
 

public Area3DPtg(String arearef, int externIdx) {
	super(new AreaReference(arearef, SpreadsheetVersion.EXCEL97));
	setExternSheetIndex(externIdx);
}
 

@Override
public SpreadsheetVersion getSpreadsheetVersion(){
    return SpreadsheetVersion.EXCEL97;
}
 

public AreaReference toAreaReference() {
    return new AreaReference(topLeft.toCellReference(), bottomRight.toCellReference(), SpreadsheetVersion.EXCEL97);
}
 

/**
 * Method creates mapping [rangeName : List<CellRangeAddress>].
 * List contains all merge regions for this named range.
 * Attention: if merged regions writes wrong - look on methods isMergeRegionInsideNamedRange or isNamedRangeInsideMergeRegion
 * todo: how to recognize if merge region must be copied with named range
 *
 * @param currentSheet Sheet which contains merge regions
 */
protected void initMergeRegions(HSSFSheet currentSheet) {
    int rangeNumber = templateWorkbook.getNumberOfNames();
    for (int i = 0; i < rangeNumber; i++) {
        HSSFName aNamedRange = templateWorkbook.getNameAt(i);

        String refersToFormula = aNamedRange.getRefersToFormula();
        if (!AreaReference.isContiguous(refersToFormula)) {
            continue;
        }

        AreaReference aref = new AreaReference(refersToFormula, SpreadsheetVersion.EXCEL97);
        Integer rangeFirstRow = aref.getFirstCell().getRow();
        Integer rangeFirstColumn = (int) aref.getFirstCell().getCol();
        Integer rangeLastRow = aref.getLastCell().getRow();
        Integer rangeLastColumn = (int) aref.getLastCell().getCol();

        for (int j = 0; j < currentSheet.getNumMergedRegions(); j++) {
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(j);
            if (mergedRegion != null) {
                Integer regionFirstRow = mergedRegion.getFirstRow();
                Integer regionFirstColumn = mergedRegion.getFirstColumn();
                Integer regionLastRow = mergedRegion.getLastRow();
                Integer regionLastColumn = mergedRegion.getLastColumn();

                boolean mergedInsideNamed = isMergeRegionInsideNamedRange(
                        rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn,
                        regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn);

                boolean namedInsideMerged = isNamedRangeInsideMergeRegion(
                        rangeFirstRow, rangeFirstColumn, rangeLastRow, rangeLastColumn,
                        regionFirstRow, regionFirstColumn, regionLastRow, regionLastColumn);

                if (mergedInsideNamed || namedInsideMerged) {
                    String name = aNamedRange.getNameName();
                    SheetRange sheetRange = new SheetRange(mergedRegion, currentSheet.getSheetName());
                    if (mergeRegionsForRangeNames.get(name) == null) {
                        ArrayList<SheetRange> list = new ArrayList<>();
                        list.add(sheetRange);
                        mergeRegionsForRangeNames.put(name, list);
                    } else {
                        mergeRegionsForRangeNames.get(name).add(sheetRange);
                    }
                }
            }
        }
    }
}
 

/**
 * Create new merge regions in result sheet identically to range's merge regions from template.
 * Not support copy of frames and rules
 *
 * @param resultSheet            - result sheet
 * @param rangeName              - range name
 * @param firstTargetRangeRow    - first column of target range
 * @param firstTargetRangeColumn - first column of target range
 */
protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName,
                                int firstTargetRangeRow, int firstTargetRangeColumn) {
    int rangeNameIdx = templateWorkbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1) return;

    HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx);
    AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
    int column = aref.getFirstCell().getCol();
    int row = aref.getFirstCell().getRow();

    List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName);
    if (regionsList != null)
        for (SheetRange sheetRange : regionsList) {
            if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) {
                CellRangeAddress cra = sheetRange.getCellRangeAddress();
                if (cra != null) {
                    int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1;
                    int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1;

                    int regionVOffset = cra.getFirstRow() - row;
                    int regionHOffset = cra.getFirstColumn() - column;

                    CellRangeAddress newRegion = cra.copy();
                    newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn);
                    newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn);

                    newRegion.setFirstRow(regionVOffset + firstTargetRangeRow);
                    newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow);

                    boolean skipRegion = false;

                    for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) {
                        CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex);

                        if (!intersects(newRegion, mergedRegion)) {
                            continue;
                        }

                        skipRegion = true;
                    }

                    if (!skipRegion) {
                        resultSheet.addMergedRegion(newRegion);
                    }
                }
            }
        }
}
 

/**
 * Create the formula parser, with the string that is to be
 *  parsed against the supplied workbook.
 * A later call the parse() method to return ptg list in
 *  rpn order, then call the getRPNPtg() to retrieve the
 *  parse results.
 * This class is recommended only for single threaded use.
 *
 * If you have a {@link org.apache.poi.hssf.usermodel.HSSFWorkbook}, and not a
 *  {@link org.apache.poi.ss.usermodel.Workbook}, then use the convenience method on
 *  {@link org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator}
 */
private FormulaParser(String formula, FormulaParsingWorkbook book, int sheetIndex, int rowIndex) {
    _formulaString = formula;
    _pointer=0;
    _book = book;
    _ssVersion = book == null ? SpreadsheetVersion.EXCEL97 : book.getSpreadsheetVersion();
    _formulaLength = _formulaString.length();
    _sheetIndex = sheetIndex;
    _rowIndex = rowIndex;
}
 

/**
 * Returns the spreadsheet version (EXCLE97) of this workbook
 * 
 * @return EXCEL97 SpreadsheetVersion enum
 * @since 3.14 beta 2
 */
@Override
public SpreadsheetVersion getSpreadsheetVersion() {
    return SpreadsheetVersion.EXCEL97;
}
 

/**
   * Create an area ref from a string representation.  Sheet names containing special characters should be
   * delimited and escaped as per normal syntax rules for formulas.<br> 
   * The area reference must be contiguous (i.e. represent a single rectangle, not a union of rectangles)
   */
  public AreaReference(String reference) {
super(reference, SpreadsheetVersion.EXCEL97);
  }