org.apache.poi.ss.formula.SheetNameFormatter Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of poi Show documentation
Show all versions of poi Show documentation
Apache POI - Java API To Access Microsoft Format Files
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.ss.formula;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.SpreadsheetVersion;
/**
* Formats sheet names for use in formula expressions.
*/
public final class SheetNameFormatter {
private static final char DELIMITER = '\'';
/**
* Matches a single cell ref with no absolute ('$') markers
*/
private static final Pattern CELL_REF_PATTERN = Pattern.compile("([A-Za-z]+)([0-9]+)");
private SheetNameFormatter() {
// no instances of this class
}
/**
* Used to format sheet names as they would appear in cell formula expressions.
* @return the sheet name unchanged if there is no need for delimiting. Otherwise the sheet
* name is enclosed in single quotes ('). Any single quotes which were already present in the
* sheet name will be converted to double single quotes ('').
*/
public static String format(String rawSheetName) {
StringBuilder sb = new StringBuilder((rawSheetName == null ? 0 : rawSheetName.length()) + 2);
appendFormat(sb, rawSheetName);
return sb.toString();
}
/**
* Convenience method for ({@link #format(String)}) when a StringBuffer is already available.
*
* @param out - sheet name will be appended here possibly with delimiting quotes
* @param rawSheetName - sheet name
*/
public static void appendFormat(Appendable out, String rawSheetName) {
try {
boolean needsQuotes = needsDelimiting(rawSheetName);
if(needsQuotes) {
out.append(DELIMITER);
appendAndEscape(out, rawSheetName);
out.append(DELIMITER);
} else {
appendAndEscape(out, rawSheetName);
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* Convenience method for ({@link #format(String)}) when a StringBuffer is already available.
*
* @param out - sheet name will be appended here possibly with delimiting quotes
* @param workbookName - workbook name
* @param rawSheetName - sheet name
*/
public static void appendFormat(Appendable out, String workbookName, String rawSheetName) {
try {
boolean needsQuotes = needsDelimiting(workbookName) || needsDelimiting(rawSheetName);
if(needsQuotes) {
out.append(DELIMITER);
out.append('[');
appendAndEscape(out, workbookName.replace('[', '(').replace(']', ')'));
out.append(']');
appendAndEscape(out, rawSheetName);
out.append(DELIMITER);
} else {
out.append('[');
appendOrREF(out, workbookName);
out.append(']');
appendOrREF(out, rawSheetName);
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private static void appendOrREF(Appendable out, String name) throws IOException {
if(name == null) {
out.append("#REF");
} else {
out.append(name);
}
}
static void appendAndEscape(Appendable sb, String rawSheetName) {
try {
if (rawSheetName == null) {
sb.append("#REF");
return;
}
int len = rawSheetName.length();
for (int i = 0; i < len; i++) {
char ch = rawSheetName.charAt(i);
if (ch == DELIMITER) {
// single quotes (') are encoded as ('')
sb.append(DELIMITER);
}
sb.append(ch);
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* Tell if the given raw sheet name needs screening/delimiting.
* @param rawSheetName the sheet name.
* @return true if the given raw sheet name needs screening/delimiting, false otherwise or
* if the sheet name is null.
*/
static boolean needsDelimiting(String rawSheetName) {
if(rawSheetName == null) {
return false;
}
int len = rawSheetName.length();
if(len < 1) {
return false; // some cases we get missing external references, resulting in empty sheet names
}
if(Character.isDigit(rawSheetName.charAt(0))) {
// sheet name with digit in the first position always requires delimiting
return true;
}
for(int i=0; i
* This code is currently being used for translating formulas represented with {@code Ptg}
* tokens into human readable text form. In formula expressions, a sheet name always has a
* trailing '!' so there is little chance for ambiguity. It doesn't matter too much what this
* method returns but it is worth noting the likely consumers of these formula text strings:
*
* - POI's own formula parser
* - Visual reading by human
* - VBA automation entry into Excel cell contents e.g. ActiveCell.Formula = "=c64!A1"
* - Manual entry into Excel cell contents
* - Some third party formula parser
*
*
* At the time of writing, POI's formula parser tolerates cell-like sheet names in formulas
* with or without delimiters. The same goes for Excel(2007), both manual and automated entry.
*
* For better or worse this implementation attempts to replicate Excel's formula renderer.
* Excel uses range checking on the apparent 'row' and 'column' components. Note however that
* the maximum sheet size varies across versions.
* @see org.apache.poi.ss.util.CellReference
*/
/* package */ static boolean cellReferenceIsWithinRange(String lettersPrefix, String numbersSuffix) {
return CellReference.cellReferenceIsWithinRange(lettersPrefix, numbersSuffix, SpreadsheetVersion.EXCEL97);
}
/**
* Note - this method assumes the specified rawSheetName has only letters and digits. It
* cannot be used to match absolute or range references (using the dollar or colon char).
*
* Some notable cases:
*
* Notable cases
* Input Result Comments "A1" true "a111" true "AA" false "aa1" true "A1A" false "A1A1" false "A$1:$C$20" false Not a plain cell reference "SALES20080101" true
* Still needs delimiting even though well out of range
*
* @return {@code true} if there is any possible ambiguity that the specified rawSheetName
* could be interpreted as a valid cell name.
*/
/* package */ static boolean nameLooksLikePlainCellReference(String rawSheetName) {
Matcher matcher = CELL_REF_PATTERN.matcher(rawSheetName);
if(!matcher.matches()) {
return false;
}
// rawSheetName == "Sheet1" gets this far.
String lettersPrefix = matcher.group(1);
String numbersSuffix = matcher.group(2);
return cellReferenceIsWithinRange(lettersPrefix, numbersSuffix);
}
}