//private static bool IsValid(int firstRow, int lastRow, int firstColumn, int lastColumn)
//{
// if (lastRow < 0 || lastRow > LAST_ROW_INDEX)
// {
// return false;
// }
// if (firstRow < 0 || firstRow > LAST_ROW_INDEX)
// {
// return false;
// }
// if (lastColumn < 0 || lastColumn > LAST_COLUMN_INDEX)
// {
// return false;
// }
// if (firstColumn < 0 || firstColumn > LAST_COLUMN_INDEX)
// {
// return false;
// }
// return true;
//}
/**
* Validate the range limits against the supplied version of Excel
*
* @param ssVersion the version of Excel to validate against
* @throws IllegalArgumentException if the range limits are outside of the allowed range
*/
public void Validate(SpreadsheetVersion ssVersion)
{
ValidateRow(_firstRow, ssVersion);
ValidateRow(_lastRow, ssVersion);
ValidateColumn(_firstCol, ssVersion);
ValidateColumn(_lastCol, ssVersion);
}
public static bool IsColumnWithnRange(String colStr, SpreadsheetVersion ssVersion)
{
String lastCol = ssVersion.LastColumnName;
int lastColLength = lastCol.Length;
int numberOfLetters = colStr.Length;
if (numberOfLetters > lastColLength)
{
// "Sheet1" case etc
return(false); // that was easy
}
if (numberOfLetters == lastColLength)
{
//if (colStr.ToUpper().CompareTo(lastCol) > 0)
if (string.Compare(colStr.ToUpper(), lastCol, StringComparison.Ordinal) > 0)
{
return(false);
}
}
else
{
// apparent column name has less chars than max
// no need to check range
}
return(true);
}
protected void verifySpreadsheetVersion(SpreadsheetVersion expected)
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
Assert.AreEqual(expected, wb.SpreadsheetVersion);
wb.Close();
}
//private static bool IsValid(int firstRow, int lastRow, int firstColumn, int lastColumn)
//{
// if (lastRow < 0 || lastRow > LAST_ROW_INDEX)
// {
// return false;
// }
// if (firstRow < 0 || firstRow > LAST_ROW_INDEX)
// {
// return false;
// }
// if (lastColumn < 0 || lastColumn > LAST_COLUMN_INDEX)
// {
// return false;
// }
// if (firstColumn < 0 || firstColumn > LAST_COLUMN_INDEX)
// {
// return false;
// }
// return true;
//}
/**
* Validate the range limits against the supplied version of Excel
*
* @param ssVersion the version of Excel to validate against
* @throws IllegalArgumentException if the range limits are outside of the allowed range
*/
public void Validate(SpreadsheetVersion ssVersion)
{
ValidateRow(_firstRow, ssVersion);
ValidateRow(_lastRow, ssVersion);
ValidateColumn(_firstCol, ssVersion);
ValidateColumn(_lastCol, ssVersion);
}
private static NameType ValidateNamedRangeName(String str, SpreadsheetVersion ssVersion)
{
Regex colMatcher = new Regex(COLUMN_REF_PATTERN);
if (colMatcher.IsMatch(str))
{
Group colStr = colMatcher.Matches(str)[0].Groups[1];
if (IsColumnWithnRange(colStr.Value, ssVersion))
{
return(NameType.Column);
}
}
Regex rowMatcher = new Regex(ROW_REF_PATTERN);
if (rowMatcher.IsMatch(str))
{
Group rowStr = rowMatcher.Matches(str)[0].Groups[1];
if (IsRowWithnRange(rowStr.Value, ssVersion))
{
return(NameType.Row);
}
}
if (!Regex.IsMatch(str, NAMED_RANGE_NAME_PATTERN))
{
return(NameType.BadCellOrNamedRange);
}
return(NameType.NamedRange);
}
public void TestMaxFunctionArguments_bug46729()
{
String[] func = { "COUNT", "AVERAGE", "MAX", "MIN", "OR", "SUBTOTAL", "SKEW" };
SpreadsheetVersion ssVersion = _testDataProvider.GetSpreadsheetVersion();
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
String fmla;
foreach (String name in func)
{
fmla = CreateFunction(name, 5);
cell.CellFormula = (/*setter*/ fmla);
fmla = CreateFunction(name, ssVersion.MaxFunctionArgs);
cell.CellFormula = (/*setter*/ fmla);
try
{
fmla = CreateFunction(name, ssVersion.MaxFunctionArgs + 1);
cell.CellFormula = (/*setter*/ fmla);
Assert.Fail("Expected FormulaParseException");
}
catch (NPOI.SS.Formula.FormulaParseException e)
{
Assert.IsTrue(e.Message.StartsWith("Too many arguments to function '" + name + "'"));
}
}
}
public static AreaReference GetWholeColumn(SpreadsheetVersion version, String start, String end)
{
if (null == version)
{
version = DEFAULT_SPREADSHEET_VERSION;
}
return(new AreaReference(start + "$1:" + end + "$" + version.MaxRows, version));
}
public static AreaReference GetWholeRow(SpreadsheetVersion version, String start, String end)
{
if (null == version)
{
version = DEFAULT_SPREADSHEET_VERSION;
}
return(new AreaReference("$A" + start + ":$" + version.LastColumnName + end, version));
}
/**
* Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be
* interpreted as a cell reference. Names of that form can be also used for sheets and/or
* named ranges, and in those circumstances, the question of whether the potential cell
* reference is valid (in range) becomes important.
* <p/>
* Note - that the maximum sheet size varies across Excel versions:
* <p/>
* <blockquote><table border="0" cellpadding="1" cellspacing="0"
* summary="Notable cases.">
* <tr><th>Version </th><th>File Format </th>
* <th>Last Column </th><th>Last Row</th></tr>
* <tr><td>97-2003</td><td>BIFF8</td><td>"IV" (2^8)</td><td>65536 (2^14)</td></tr>
* <tr><td>2007</td><td>BIFF12</td><td>"XFD" (2^14)</td><td>1048576 (2^20)</td></tr>
* </table></blockquote>
* POI currently targets BIFF8 (Excel 97-2003), so the following behaviour can be observed for
* this method:
* <blockquote><table border="0" cellpadding="1" cellspacing="0"
* summary="Notable cases.">
* <tr><th>Input </th>
* <th>Result </th></tr>
* <tr><td>"A", "1"</td><td>true</td></tr>
* <tr><td>"a", "111"</td><td>true</td></tr>
* <tr><td>"A", "65536"</td><td>true</td></tr>
* <tr><td>"A", "65537"</td><td>false</td></tr>
* <tr><td>"iv", "1"</td><td>true</td></tr>
* <tr><td>"IW", "1"</td><td>false</td></tr>
* <tr><td>"AAA", "1"</td><td>false</td></tr>
* <tr><td>"a", "111"</td><td>true</td></tr>
* <tr><td>"Sheet", "1"</td><td>false</td></tr>
* </table></blockquote>
*
* @param colStr a string of only letter characters
* @param rowStr a string of only digit characters
* @return <c>true</c> if the row and col parameters are within range of a BIFF8 spreadsheet.
*/
public static bool CellReferenceIsWithinRange(String colStr, String rowStr, SpreadsheetVersion ssVersion)
{
if (!IsColumnWithnRange(colStr, ssVersion))
{
return(false);
}
return(IsRowWithnRange(rowStr, ssVersion));
}
/**
* Create an area ref from a string representation. Sheet names containing special Chars 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, SpreadsheetVersion version)
{
_version = (null != version) ? version : DEFAULT_SPREADSHEET_VERSION;
if (!IsContiguous(reference))
{
throw new ArgumentException(
"References passed to the AreaReference must be contiguous, " +
"use generateContiguous(ref) if you have non-contiguous references");
}
String[] parts = SeparateAreaRefs(reference);
String part0 = parts[0];
if (parts.Length == 1)
{
// TODO - probably shouldn't initialize area ref when text is really a cell ref
// Need to fix some named range stuff to get rid of this
_firstCell = new CellReference(part0);
_lastCell = _firstCell;
_isSingleCell = true;
return;
}
if (parts.Length != 2)
{
throw new ArgumentException("Bad area ref '" + reference + "'");
}
String part1 = parts[1];
if (IsPlainColumn(part0))
{
if (!IsPlainColumn(part1))
{
throw new Exception("Bad area ref '" + reference + "'");
}
// Special handling for whole-column references
// Represented internally as x$1 to x$65536
// which is the maximum range of rows
bool firstIsAbs = CellReference.IsPartAbsolute(part0);
bool lastIsAbs = CellReference.IsPartAbsolute(part1);
int col0 = CellReference.ConvertColStringToIndex(part0);
int col1 = CellReference.ConvertColStringToIndex(part1);
_firstCell = new CellReference(0, col0, true, firstIsAbs);
_lastCell = new CellReference(0xFFFF, col1, true, lastIsAbs);
_isSingleCell = false;
// TODO - whole row refs
}
else
{
_firstCell = new CellReference(part0);
_lastCell = new CellReference(part1);
_isSingleCell = part0.Equals(part1);
}
}
public void IsRowWithinRange()
{
SpreadsheetVersion ss = SpreadsheetVersion.EXCEL2007;
Assert.IsFalse(CellReference.IsRowWithinRange("0", ss), "1 before first row");
Assert.IsTrue(CellReference.IsRowWithinRange("1", ss), "first row");
Assert.IsTrue(CellReference.IsRowWithinRange("1048576", ss), "last row");
Assert.IsFalse(CellReference.IsRowWithinRange("1048577", ss), "1 beyond last row");
}
public static NameType ClassifyCellReference(String str, SpreadsheetVersion ssVersion)
{
int len = str.Length;
if (len < 1)
{
throw new ArgumentException("Empty string not allowed");
}
char firstChar = str[0];
switch (firstChar)
{
case ABSOLUTE_REFERENCE_MARKER:
case '.':
case '_':
break;
default:
if (!Char.IsLetter(firstChar) && !Char.IsDigit(firstChar))
{
throw new ArgumentException("Invalid first char (" + firstChar
+ ") of cell reference or named range. Letter expected");
}
break;
}
if (!Char.IsDigit(str[len - 1]))
{
// no digits at end of str
return(ValidateNamedRangeName(str, ssVersion));
}
Regex cellRefPatternMatcher = new Regex(CELL_REF_PATTERN);
if (!cellRefPatternMatcher.IsMatch(str))
{
return(ValidateNamedRangeName(str, ssVersion));
}
MatchCollection matches = cellRefPatternMatcher.Matches(str);
string lettersGroup = matches[0].Groups[1].Value;
string digitsGroup = matches[0].Groups[2].Value;
if (CellReferenceIsWithinRange(lettersGroup, digitsGroup, ssVersion))
{
// valid cell reference
return(NameType.Cell);
}
// If str looks like a cell reference, but is out of (row/col) range, it is a valid
// named range name
// This behaviour is a little weird. For example, "IW123" is a valid named range name
// because the column "IW" is beyond the maximum "IV". Note - this behaviour is version
// dependent. In BIFF12, "IW123" is not a valid named range name, but in BIFF8 it is.
if (str.IndexOf(ABSOLUTE_REFERENCE_MARKER) >= 0)
{
// Of course, named range names cannot have '$'
return(NameType.BadCellOrNamedRange);
}
return(NameType.NamedRange);
}
public void IsColWithinRange()
{
SpreadsheetVersion ss = SpreadsheetVersion.EXCEL2007;
Assert.IsTrue(CellReference.IsColumnWithinRange("", ss), "(empty)");
Assert.IsTrue(CellReference.IsColumnWithinRange("A", ss), "first column (A)");
Assert.IsTrue(CellReference.IsColumnWithinRange("XFD", ss), "last column (XFD)");
Assert.IsFalse(CellReference.IsColumnWithinRange("XFE", ss), "1 beyond last column (XFE)");
}
private static NameType ClassifyCellReference(String str, SpreadsheetVersion ssVersion)
{
int len = str.Length;
if (len < 1)
{
return(NameType.BAD_CELL_OR_NAMED_RANGE);
}
return(CellReference.ClassifyCellReference(str, ssVersion));
}
/**
* Create an instance for shifting sheets.
*
* For example, this will be called on {@link org.apache.poi.hssf.usermodel.HSSFWorkbook#setSheetOrder(String, int)}
*/
private FormulaShifter(int srcSheetIndex, int dstSheetIndex)
{
_externSheetIndex = _firstMovedIndex = _lastMovedIndex = _amountToMove = -1;
_sheetName = null;
_version = null;
_srcSheetIndex = srcSheetIndex;
_dstSheetIndex = dstSheetIndex;
_mode = ShiftMode.SheetMove;
}
private static void CheckBounds(int cellIndex)
{
SpreadsheetVersion exceL2007 = SpreadsheetVersion.EXCEL2007;
int lastColumnIndex = SpreadsheetVersion.EXCEL2007.LastColumnIndex;
if (cellIndex < 0 || cellIndex > lastColumnIndex)
{
throw new ArgumentException("Invalid column index (" + (object)cellIndex + "). Allowable column range for " + exceL2007.ToString() + " is (0.." + (object)lastColumnIndex + ") or ('A'..'" + exceL2007.LastColumnName + "')");
}
}
private static NameType ClassifyCellReference(string str, SpreadsheetVersion ssVersion)
{
int len = str.Length;
if (len < 1)
{
return(NameType.BadCellOrNamedRange);
}
return(CellReference.ClassifyCellReference(str, ssVersion));
}
private static void ConfirmCrInRange(bool expResult, String colStr, String rowStr,
SpreadsheetVersion sv)
{
if (expResult == CellReference.CellReferenceIsWithinRange(colStr, rowStr, sv))
{
return;
}
throw new AssertionException("expected (c='" + colStr + "', r='" + rowStr + "' to be "
+ (expResult ? "within" : "out of") + " bounds for version " + sv.ToString());
}
/**
* Creates an area ref from a pair of Cell References.
*/
public AreaReference(CellReference topLeft, CellReference botRight)
{
_version = DEFAULT_SPREADSHEET_VERSION;
bool swapRows = topLeft.Row > botRight.Row;
bool swapCols = topLeft.Col > botRight.Col;
if (swapRows || swapCols)
{
int firstRow;
int lastRow;
int firstColumn;
int lastColumn;
bool firstRowAbs;
bool lastRowAbs;
bool firstColAbs;
bool lastColAbs;
if (swapRows)
{
firstRow = botRight.Row;
firstRowAbs = botRight.IsRowAbsolute;
lastRow = topLeft.Row;
lastRowAbs = topLeft.IsRowAbsolute;
}
else
{
firstRow = topLeft.Row;
firstRowAbs = topLeft.IsRowAbsolute;
lastRow = botRight.Row;
lastRowAbs = botRight.IsRowAbsolute;
}
if (swapCols)
{
firstColumn = botRight.Col;
firstColAbs = botRight.IsColAbsolute;
lastColumn = topLeft.Col;
lastColAbs = topLeft.IsColAbsolute;
}
else
{
firstColumn = topLeft.Col;
firstColAbs = topLeft.IsColAbsolute;
lastColumn = botRight.Col;
lastColAbs = botRight.IsColAbsolute;
}
_firstCell = new CellReference(firstRow, firstColumn, firstRowAbs, firstColAbs);
_lastCell = new CellReference(lastRow, lastColumn, lastRowAbs, lastColAbs);
}
else
{
_firstCell = topLeft;
_lastCell = botRight;
}
_isSingleCell = false;
}
/// <summary>
/// throws RuntimeException if the bounds are exceeded.
/// </summary>
/// <param name="cellIndex"></param>
private static void CheckBounds(int cellIndex)
{
SpreadsheetVersion v = SpreadsheetVersion.EXCEL2007;
int maxcol = SpreadsheetVersion.EXCEL2007.LastColumnIndex;
if (cellIndex < 0 || cellIndex > maxcol)
{
throw new ArgumentException("Invalid column index (" + cellIndex
+ "). Allowable column range for " + v.DefaultExtension + " is (0.."
+ maxcol + ") or ('A'..'" + v.LastColumnName + "')");
}
}
public void TestExcel2007()
{
SpreadsheetVersion v = SpreadsheetVersion.EXCEL2007;
Assert.AreEqual(1 << 14, v.MaxColumns);
Assert.AreEqual(v.MaxColumns - 1, v.LastColumnIndex);
Assert.AreEqual(1 << 20, v.MaxRows);
Assert.AreEqual(v.MaxRows - 1, v.LastRowIndex);
Assert.AreEqual(255, v.MaxFunctionArgs);
Assert.AreEqual(Int32.MaxValue, v.MaxConditionalFormats);
Assert.AreEqual("XFD", v.LastColumnName);
}
public void TestExcel97()
{
SpreadsheetVersion v = SpreadsheetVersion.EXCEL97;
Assert.AreEqual(1 << 8, v.MaxColumns);
Assert.AreEqual(v.MaxColumns - 1, v.LastColumnIndex);
Assert.AreEqual(1 << 16, v.MaxRows);
Assert.AreEqual(v.MaxRows - 1, v.LastRowIndex);
Assert.AreEqual(30, v.MaxFunctionArgs);
Assert.AreEqual(3, v.MaxConditionalFormats);
Assert.AreEqual("IV", v.LastColumnName);
}
/**
* Runs a bounds check for row numbers
* @param row
*/
private static void ValidateRow(int row, SpreadsheetVersion ssVersion)
{
int maxrow = ssVersion.LastRowIndex;
if (row > maxrow)
{
throw new ArgumentException("Maximum row number is " + maxrow);
}
if (row < 0)
{
throw new ArgumentException("Minumum row number is 0");
}
}
/**
* Runs a bounds check for column numbers
* @param column
*/
private static void ValidateColumn(int column, SpreadsheetVersion ssVersion)
{
int maxcol = ssVersion.LastColumnIndex;
if (column > maxcol)
{
throw new ArgumentException("Maximum column number is " + maxcol);
}
if (column < 0)
{
throw new ArgumentException("Minimum column number is 0");
}
}
public static bool IsRowWithnRange(String rowStr, SpreadsheetVersion ssVersion)
{
int rowNum = Int32.Parse(rowStr, CultureInfo.InvariantCulture);
if (rowNum < 0)
{
throw new InvalidOperationException("Invalid rowStr '" + rowStr + "'.");
}
if (rowNum == 0)
{
// execution Gets here because caller does first pass of discriminating
// potential cell references using a simplistic regex pattern.
return(false);
}
return(rowNum <= ssVersion.MaxRows);
}
/**
* is the reference for a whole-column reference,
* such as C:C or D:G ?
*/
public static bool IsWholeColumnReference(SpreadsheetVersion version, CellReference topLeft, CellReference botRight)
{
if (null == version)
{
version = SpreadsheetVersion.EXCEL97; // how the code used to behave.
}
// These are represented as something like
// C$1:C$65535 or D$1:F$0
// i.e. absolute from 1st row to 0th one
if (topLeft.Row == 0 && topLeft.IsRowAbsolute &&
(botRight.Row == version.LastRowIndex) && botRight.IsRowAbsolute)
{
return(true);
}
return(false);
}
protected bool AdjustRegionRelativeReference(Ptg[] ptgs, CellReference target, CellRangeAddressBase region)
{
if (!region.IsInRange(target))
{
throw new ArgumentException(target + " is not within " + region);
}
//return adjustRegionRelativeReference(ptgs, target.getRow() - region.getFirstRow(), target.getCol() - region.getFirstColumn());
int deltaRow = target.Row;
int deltaColumn = target.Col;
bool shifted = false;
foreach (Ptg ptg in ptgs)
{
// base class for cell reference "things"
if (ptg is RefPtgBase)
{
RefPtgBase reference = (RefPtgBase)ptg;
// re-calculate cell references
SpreadsheetVersion version = _workbook.GetSpreadsheetVersion();
if (reference.IsRowRelative)
{
int rowIndex = reference.Row + deltaRow;
if (rowIndex > version.MaxRows)
{
throw new IndexOutOfRangeException(version.Name + " files can only have " + version.MaxRows + " rows, but row " + rowIndex + " was requested.");
}
reference.Row = rowIndex;
shifted = true;
}
if (reference.IsColRelative)
{
int colIndex = reference.Column + deltaColumn;
if (colIndex > version.MaxColumns)
{
throw new IndexOutOfRangeException(version.Name + " files can only have " + version.MaxColumns + " columns, but column " + colIndex + " was requested.");
}
reference.Column = colIndex;
shifted = true;
}
}
}
return(shifted);
}
public void TestAddMerged()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
Assert.AreEqual(0, sheet.NumMergedRegions);
SpreadsheetVersion ssVersion = _testDataProvider.GetSpreadsheetVersion();
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
sheet.AddMergedRegion(region);
Assert.AreEqual(1, sheet.NumMergedRegions);
try
{
region = new CellRangeAddress(-1, -1, -1, -1);
sheet.AddMergedRegion(region);
Assert.Fail("Expected exception");
}
catch (ArgumentException)
{
// TODO Assert.AreEqual("Minimum row number is 0.", e.Message);
}
try
{
region = new CellRangeAddress(0, 0, 0, ssVersion.LastColumnIndex + 1);
sheet.AddMergedRegion(region);
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Maximum column number is " + ssVersion.LastColumnIndex, e.Message);
}
try
{
region = new CellRangeAddress(0, ssVersion.LastRowIndex + 1, 0, 1);
sheet.AddMergedRegion(region);
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Maximum row number is " + ssVersion.LastRowIndex, e.Message);
}
Assert.AreEqual(1, sheet.NumMergedRegions);
}
public void TestAddMerged()
{
Workbook wb = GetTestDataProvider().CreateWorkbook();
Sheet sheet = wb.CreateSheet();
Assert.AreEqual(0, sheet.NumMergedRegions);
SpreadsheetVersion ssVersion = GetTestDataProvider().GetSpreadsheetVersion();
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
sheet.AddMergedRegion(region);
Assert.AreEqual(1, sheet.NumMergedRegions);
try
{
region = new CellRangeAddress(-1, -1, -1, -1);
sheet.AddMergedRegion(region);
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
}
try
{
region = new CellRangeAddress(0, 0, 0, ssVersion.LastColumnIndex + 1);
sheet.AddMergedRegion(region);
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
}
try
{
region = new CellRangeAddress(0, ssVersion.LastRowIndex + 1, 0, 1);
sheet.AddMergedRegion(region);
Assert.Fail("Expected exception");
}
catch (ArgumentException e)
{
}
Assert.AreEqual(1, sheet.NumMergedRegions);
}
/**
* Create an instance for Shifting row.
*
* For example, this will be called on {@link NPOI.HSSF.UserModel.HSSFSheet#ShiftRows(int, int, int)} }
*/
private FormulaShifter(int externSheetIndex, String sheetName, int firstMovedIndex, int lastMovedIndex, int amountToMove,
ShiftMode mode, SpreadsheetVersion version)
{
if (amountToMove == 0)
{
throw new ArgumentException("amountToMove must not be zero");
}
if (firstMovedIndex > lastMovedIndex)
{
throw new ArgumentException("firstMovedIndex, lastMovedIndex out of order");
}
_externSheetIndex = externSheetIndex;
_sheetName = sheetName;
_firstMovedIndex = firstMovedIndex;
_lastMovedIndex = lastMovedIndex;
_amountToMove = amountToMove;
_mode = mode;
_version = version;
_srcSheetIndex = _dstSheetIndex = -1;
}
public void TestBadRowNumber()
{
SpreadsheetVersion v97 = SpreadsheetVersion.EXCEL97;
SpreadsheetVersion v2007 = SpreadsheetVersion.EXCEL2007;
ConfirmCrInRange(true, "A", "1", v97);
ConfirmCrInRange(true, "IV", "65536", v97);
ConfirmCrInRange(false, "IV", "65537", v97);
ConfirmCrInRange(false, "IW", "65536", v97);
ConfirmCrInRange(true, "A", "1", v2007);
ConfirmCrInRange(true, "XFD", "1048576", v2007);
ConfirmCrInRange(false, "XFD", "1048577", v2007);
ConfirmCrInRange(false, "XFE", "1048576", v2007);
if (CellReference.CellReferenceIsWithinRange("B", "0", v97))
{
throw new AssertionException("Identified bug 47312a");
}
ConfirmCrInRange(false, "A", "0", v97);
ConfirmCrInRange(false, "A", "0", v2007);
}
private static NameType ClassifyCellReference(String str, SpreadsheetVersion ssVersion)
{
int len = str.Length;
if (len < 1)
{
return NameType.BadCellOrNamedRange;
}
return CellReference.ClassifyCellReference(str, ssVersion);
}
public SharedFormula(SpreadsheetVersion ssVersion)
{
_columnWrappingMask = ssVersion.LastColumnIndex; //"IV" for .xls and "XFD" for .xlsx
_rowWrappingMask = ssVersion.LastRowIndex;
}
/**
* 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 retrive the
* Parse results.
* This class is recommended only for single threaded use.
*
* If you only have a usermodel.HSSFWorkbook, and not a
* model.Workbook, then use the convenience method on
* usermodel.HSSFFormulaEvaluator
*/
public FormulaParser(String formula, IFormulaParsingWorkbook book, int sheetIndex)
{
formulaString = formula;
pointer = 0;
this._book = book;
_ssVersion = book == null ? SpreadsheetVersion.EXCEL97 : book.GetSpreadsheetVersion();
formulaLength = formulaString.Length;
_sheetIndex = sheetIndex;
}
private static NameType ClassifyCellReference(String str, SpreadsheetVersion ssVersion)
{
int len = str.Length;
if (len < 1)
{
return NameType.BAD_CELL_OR_NAMED_RANGE;
}
return CellReference.ClassifyCellReference(str, ssVersion);
}
/**
* Runs a bounds check for row numbers
* @param row
*/
private static void ValidateRow(int row, SpreadsheetVersion ssVersion)
{
int maxrow = ssVersion.LastRowIndex;
if (row > maxrow) throw new ArgumentException("Maximum row number is " + maxrow);
if (row < 0) throw new ArgumentException("Minumum row number is 0");
}
/**
* Runs a bounds check for column numbers
* @param column
*/
private static void ValidateColumn(int column, SpreadsheetVersion ssVersion)
{
int maxcol = ssVersion.LastColumnIndex;
if (column > maxcol) throw new ArgumentException("Maximum column number is " + maxcol);
if (column < 0) throw new ArgumentException("Minimum column number is 0");
}
public static NameType ClassifyCellReference(String str, SpreadsheetVersion ssVersion)
{
int len = str.Length;
if (len < 1)
{
throw new ArgumentException("Empty string not allowed");
}
char firstChar = str[0];
switch (firstChar)
{
case ABSOLUTE_REFERENCE_MARKER:
case '.':
case '_':
break;
default:
if (!Char.IsLetter(firstChar) && !Char.IsDigit(firstChar))
{
throw new ArgumentException("Invalid first char (" + firstChar
+ ") of cell reference or named range. Letter expected");
}
break;
}
if (!Char.IsDigit(str[len - 1]))
{
// no digits at end of str
return ValidateNamedRangeName(str, ssVersion);
}
Regex cellRefPatternMatcher = new Regex(CELL_REF_PATTERN);
if (!cellRefPatternMatcher.IsMatch(str))
{
return ValidateNamedRangeName(str, ssVersion);
}
MatchCollection matches = cellRefPatternMatcher.Matches(str);
string lettersGroup = matches[0].Groups[1].Value;
string digitsGroup = matches[0].Groups[2].Value;
if (CellReferenceIsWithinRange(lettersGroup, digitsGroup, ssVersion))
{
// valid cell reference
return NameType.Cell;
}
// If str looks like a cell reference, but is out of (row/col) range, it is a valid
// named range name
// This behaviour is a little weird. For example, "IW123" is a valid named range name
// because the column "IW" is beyond the maximum "IV". Note - this behaviour is version
// dependent. In BIFF12, "IW123" is not a valid named range name, but in BIFF8 it is.
if (str.IndexOf(ABSOLUTE_REFERENCE_MARKER) >= 0)
{
// Of course, named range names cannot have '$'
return NameType.BadCellOrNamedRange;
}
return NameType.NamedRange;
}
private static NameType ValidateNamedRangeName(String str, SpreadsheetVersion ssVersion)
{
Regex colMatcher = new Regex(COLUMN_REF_PATTERN);
if (colMatcher.IsMatch(str))
{
Group colStr = colMatcher.Matches(str)[0].Groups[1];
if (IsColumnWithnRange(colStr.Value, ssVersion))
{
return NameType.Column;
}
}
Regex rowMatcher = new Regex(ROW_REF_PATTERN);
if (rowMatcher.IsMatch(str))
{
Group rowStr = rowMatcher.Matches(str)[0].Groups[1];
if (IsRowWithnRange(rowStr.Value, ssVersion))
{
return NameType.Row;
}
}
if (!Regex.IsMatch(str, NAMED_RANGE_NAME_PATTERN))
{
return NameType.BadCellOrNamedRange;
}
return NameType.NamedRange;
}
public static bool IsColumnWithnRange(String colStr, SpreadsheetVersion ssVersion)
{
String lastCol = ssVersion.LastColumnName;
int lastColLength = lastCol.Length;
int numberOfLetters = colStr.Length;
if (numberOfLetters > lastColLength)
{
// "Sheet1" case etc
return false; // that was easy
}
if (numberOfLetters == lastColLength)
{
//if (colStr.ToUpper().CompareTo(lastCol) > 0)
if (string.Compare(colStr.ToUpper(), lastCol, StringComparison.Ordinal) > 0)
{
return false;
}
}
else
{
// apparent column name has less chars than max
// no need to check range
}
return true;
}
public static bool IsRowWithnRange(String rowStr, SpreadsheetVersion ssVersion)
{
int rowNum = Int32.Parse(rowStr, CultureInfo.InvariantCulture);
if (rowNum < 0)
{
throw new InvalidOperationException("Invalid rowStr '" + rowStr + "'.");
}
if (rowNum == 0)
{
// execution Gets here because caller does first pass of discriminating
// potential cell references using a simplistic regex pattern.
return false;
}
return rowNum <= ssVersion.MaxRows;
}
/**
* Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be
* interpreted as a cell reference. Names of that form can be also used for sheets and/or
* named ranges, and in those circumstances, the question of whether the potential cell
* reference is valid (in range) becomes important.
* <p/>
* Note - that the maximum sheet size varies across Excel versions:
* <p/>
* <blockquote><table border="0" cellpadding="1" cellspacing="0"
* summary="Notable cases.">
* <tr><th>Version </th><th>File Format </th>
* <th>Last Column </th><th>Last Row</th></tr>
* <tr><td>97-2003</td><td>BIFF8</td><td>"IV" (2^8)</td><td>65536 (2^14)</td></tr>
* <tr><td>2007</td><td>BIFF12</td><td>"XFD" (2^14)</td><td>1048576 (2^20)</td></tr>
* </table></blockquote>
* POI currently targets BIFF8 (Excel 97-2003), so the following behaviour can be observed for
* this method:
* <blockquote><table border="0" cellpadding="1" cellspacing="0"
* summary="Notable cases.">
* <tr><th>Input </th>
* <th>Result </th></tr>
* <tr><td>"A", "1"</td><td>true</td></tr>
* <tr><td>"a", "111"</td><td>true</td></tr>
* <tr><td>"A", "65536"</td><td>true</td></tr>
* <tr><td>"A", "65537"</td><td>false</td></tr>
* <tr><td>"iv", "1"</td><td>true</td></tr>
* <tr><td>"IW", "1"</td><td>false</td></tr>
* <tr><td>"AAA", "1"</td><td>false</td></tr>
* <tr><td>"a", "111"</td><td>true</td></tr>
* <tr><td>"Sheet", "1"</td><td>false</td></tr>
* </table></blockquote>
*
* @param colStr a string of only letter characters
* @param rowStr a string of only digit characters
* @return <c>true</c> if the row and col parameters are within range of a BIFF8 spreadsheet.
*/
public static bool CellReferenceIsWithinRange(String colStr, String rowStr, SpreadsheetVersion ssVersion)
{
if (!IsColumnWithnRange(colStr, ssVersion))
{
return false;
}
return IsRowWithnRange(rowStr, ssVersion);
}
