| /******************************************************************************* | |
| * You may amend and distribute as you like, but don't remove this header! | |
| * | |
| * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets. | |
| * See http://www.codeplex.com/EPPlus for details. | |
| * | |
| * Copyright (C) 2011 Jan Källman | |
| * | |
| * This library is free software; you can redistribute it and/or | |
| * modify it under the terms of the GNU Lesser General Public | |
| * License as published by the Free Software Foundation; either | |
| * version 2.1 of the License, or (at your option) any later version. | |
| * This library is distributed in the hope that it will be useful, | |
| * but WITHOUT ANY WARRANTY; without even the implied warranty of | |
| * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | |
| * See the GNU Lesser General Public License for more details. | |
| * | |
| * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php | |
| * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html | |
| * | |
| * All code and executables are provided "as is" with no warranty either express or implied. | |
| * The author accepts no liability for any damage or loss of business that this product may cause. | |
| * | |
| * Code change notes: | |
| * | |
| * Author Change Date | |
| ******************************************************************************* | |
| * Jan Källman Initial Release 2009-10-01 | |
| * Jan Källman License changed GPL-->LGPL 2011-12-27 | |
| *******************************************************************************/ | |
| using System; | |
| using System.Collections.Generic; | |
| using System.Globalization; | |
| using System.Text; | |
| using OfficeOpenXml.Style; | |
| using System.Text.RegularExpressions; | |
| using OfficeOpenXml.FormulaParsing.LexicalAnalysis; | |
| using System.Linq; | |
| using OfficeOpenXml.FormulaParsing.Excel.Functions; | |
| using OfficeOpenXml.FormulaParsing; | |
| namespace OfficeOpenXml | |
| { | |
| /// <summary> | |
| /// Base class containing cell address manipulating methods. | |
| /// </summary> | |
| public abstract class ExcelCellBase | |
| { | |
| #region "public functions" | |
| /// <summary> | |
| /// Get the sheet, row and column from the CellID | |
| /// </summary> | |
| /// <param name="cellID"></param> | |
| /// <param name="sheet"></param> | |
| /// <param name="row"></param> | |
| /// <param name="col"></param> | |
| static internal void SplitCellID(ulong cellID, out int sheet, out int row, out int col) | |
| { | |
| sheet = (int)(cellID % 0x8000); | |
| col = ((int)(cellID >> 15) & 0x3FF); | |
| row = ((int)(cellID >> 29)); | |
| } | |
| /// <summary> | |
| /// Get the cellID for the cell. | |
| /// </summary> | |
| /// <param name="SheetID"></param> | |
| /// <param name="row"></param> | |
| /// <param name="col"></param> | |
| /// <returns></returns> | |
| internal static ulong GetCellID(int SheetID, int row, int col) | |
| { | |
| return ((ulong)SheetID) + (((ulong)col) << 15) + (((ulong)row) << 29); | |
| } | |
| #endregion | |
| #region "Formula Functions" | |
| private delegate string dlgTransl(string part, int row, int col, int rowIncr, int colIncr); | |
| #region R1C1 Functions" | |
| /// <summary> | |
| /// Translates a R1C1 to an absolut address/Formula | |
| /// </summary> | |
| /// <param name="value">Address</param> | |
| /// <param name="row">Current row</param> | |
| /// <param name="col">Current column</param> | |
| /// <returns>The RC address</returns> | |
| public static string TranslateFromR1C1(string value, int row, int col) | |
| { | |
| return Translate(value, ToAbs, row, col, -1, -1); | |
| } | |
| /// <summary> | |
| /// Translates a R1C1 to an absolut address/Formula: Version 1 | |
| /// </summary> | |
| /// <param name="value">Address</param> | |
| /// <param name="row">Current row</param> | |
| /// <param name="col">Current column</param> | |
| /// <returns>The RC address</returns> | |
| public static string TranslateFromR1C1_V1(string value, int row, int col) | |
| { | |
| return Translate_V1(value, ToAbs_V1, row, col, -1, -1); | |
| } | |
| /// <summary> | |
| /// Translates a absolut address to R1C1 Format | |
| /// </summary> | |
| /// <param name="value">R1C1 Address</param> | |
| /// <param name="row">Current row</param> | |
| /// <param name="col">Current column</param> | |
| /// <returns>The absolut address/Formula</returns> | |
| public static string TranslateToR1C1(string value, int row, int col) | |
| { | |
| return Translate(value, ToR1C1, row, col, -1, -1); | |
| } | |
| /// <summary> | |
| /// Translates a absolute address to R1C1 Format : Version 1 | |
| /// </summary> | |
| /// <param name="value">R1C1 Address</param> | |
| /// <param name="row">Current row</param> | |
| /// <param name="col">Current column</param> | |
| /// <returns>The absolut address/Formula</returns> | |
| public static string TranslateToR1C1_V1(string value, int row, int col) | |
| { | |
| return Translate_V1(value, ToR1C1_V1, row, col, -1, -1); | |
| } | |
| /// <summary> | |
| /// Translates betweein R1C1 or absolute addresses : Version 1 | |
| /// </summary> | |
| /// <param name="value">The addresss/function</param> | |
| /// <param name="addressTranslator">The translating function</param> | |
| /// <param name="row"></param> | |
| /// <param name="col"></param> | |
| /// <param name="rowIncr"></param> | |
| /// <param name="colIncr"></param> | |
| /// <returns></returns> | |
| private static string Translate(string value, dlgTransl addressTranslator, int row, int col, int rowIncr, int colIncr) | |
| { | |
| if (value == "") | |
| return ""; | |
| bool isText = false; | |
| string ret = ""; | |
| string part = ""; | |
| char prevTQ = (char)0; | |
| for (int pos = 0; pos < value.Length; pos++) | |
| { | |
| char c = value[pos]; | |
| if (c == '"' || c=='\'') | |
| { | |
| if (isText == true && prevTQ != c) | |
| { | |
| ret += c; | |
| continue; | |
| } | |
| if (isText == false && part != "" && prevTQ==c) | |
| { | |
| ret += addressTranslator(part, row, col, rowIncr, colIncr); | |
| part = ""; | |
| prevTQ = (char)0; | |
| } | |
| prevTQ = c; | |
| isText = !isText; | |
| ret += c; | |
| } | |
| else if (isText) | |
| { | |
| ret += c; | |
| } | |
| else | |
| { | |
| if ((c == '-' || c == '+' || c == '*' || c == '/' || | |
| c == '=' || c == '^' || c == ',' || c == ':' || | |
| c == '<' || c == '>' || c == '(' || c == ')' || c == '!' || | |
| c == ' ' || c == '&' || c == '%') && | |
| (pos == 0 || value[pos - 1] != '[')) //Last part to allow for R1C1 style [-x] | |
| { | |
| ret += addressTranslator(part, row, col, rowIncr, colIncr) + c; | |
| part = ""; | |
| } | |
| else | |
| { | |
| part += c; | |
| } | |
| } | |
| } | |
| if (part != "") | |
| { | |
| ret += addressTranslator(part, row, col, rowIncr, colIncr); | |
| } | |
| return ret; | |
| } | |
| private static string Translate_V1(string value, dlgTransl addressTranslator, int row, int col, int rowIncr, int colIncr) | |
| { | |
| if (value == "") | |
| return ""; | |
| bool isText = false; | |
| string ret = ""; | |
| string part = ""; | |
| char prevTQ = (char)0; | |
| value = value.Replace("\n", ""); // Eliminate new line characters in the formula | |
| for (int pos = 0; pos < value.Length; pos++) | |
| { | |
| char c = value[pos]; | |
| if (c == '"' || c == '\'') | |
| { | |
| if (isText == true && prevTQ != c) | |
| { | |
| ret += c; | |
| continue; | |
| } | |
| if (isText == false && part != "" && prevTQ == c) | |
| { | |
| ret += addressTranslator(part, row, col, rowIncr, colIncr); | |
| part = ""; | |
| prevTQ = (char)0; | |
| } | |
| prevTQ = c; | |
| isText = !isText; | |
| ret += c; | |
| } | |
| else if (isText) | |
| { | |
| ret += c; | |
| } | |
| else if (c == ':') // Keep Range expressions together | |
| { | |
| part += c; | |
| } | |
| else | |
| { | |
| if ((c == '-' || c == '+' || c == '*' || c == '/' || | |
| c == '=' || c == '^' || c == ',' || | |
| c == '<' || c == '>' || c == '(' || c == ')' || c == '!' || | |
| c == ' ' || c == '&' || c == '%') && | |
| (pos == 0 || value[pos - 1] != '[')) //Last part to allow for R1C1 style [-x] | |
| { | |
| ret += addressTranslator(part, row, col, rowIncr, colIncr) + c; | |
| part = ""; | |
| } | |
| else | |
| { | |
| part += c; | |
| } | |
| } | |
| } | |
| if (part != "") | |
| { | |
| ret += addressTranslator(part, row, col, rowIncr, colIncr); | |
| } | |
| return ret; | |
| } | |
| /// <summary> | |
| /// Translate to R1C1 | |
| /// </summary> | |
| /// <param name="part">the value to be translated</param> | |
| /// <param name="row"></param> | |
| /// <param name="col"></param> | |
| /// <param name="rowIncr"></param> | |
| /// <param name="colIncr"></param> | |
| /// <returns></returns> | |
| private static string ToR1C1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| int addrRow, addrCol; | |
| string Ret = "R"; | |
| if (GetRowCol(part, out addrRow, out addrCol, false)) | |
| { | |
| if (addrRow == 0 || addrCol == 0) | |
| { | |
| return part; | |
| } | |
| if (part.IndexOf('$', 1) > 0) | |
| { | |
| Ret += addrRow.ToString(); | |
| } | |
| else if (addrRow - row != 0) | |
| { | |
| Ret += string.Format("[{0}]", addrRow - row); | |
| } | |
| if (part.StartsWith("$")) | |
| { | |
| return Ret + "C" + addrCol; | |
| } | |
| else if (addrCol - col != 0) | |
| { | |
| return Ret + "C" + string.Format("[{0}]", addrCol - col); | |
| } | |
| else | |
| { | |
| return Ret + "C"; | |
| } | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| } | |
| private static string ToR1C1_V1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| int addrRow, addrCol; | |
| // Handle range expressions | |
| if ((part.Length > 1) && (part.IndexOf(':', 1) > 0)) | |
| { | |
| return RangeToR1C1_V1(part, row, col, rowIncr, colIncr); | |
| } | |
| string Ret = "R"; | |
| if (GetRowCol(part, out addrRow, out addrCol, false)) | |
| { | |
| if (addrRow == 0 || addrCol == 0) | |
| { | |
| return part; | |
| } | |
| if (part.IndexOf('$', 1) > 0) | |
| { | |
| Ret += addrRow.ToString(); | |
| } | |
| else if (addrRow - row != 0) | |
| { | |
| Ret += string.Format("[{0}]", addrRow - row); | |
| } | |
| if (part.StartsWith("$")) | |
| { | |
| return Ret + "C" + addrCol; | |
| } | |
| else if (addrCol - col != 0) | |
| { | |
| return Ret + "C" + string.Format("[{0}]", addrCol - col); | |
| } | |
| else | |
| { | |
| return Ret + "C"; | |
| } | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| } | |
| private static string RangeToR1C1_V1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| // Split range expression | |
| string[] cellValues = part.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries); | |
| // Convert range expressions | |
| string result = ""; | |
| result += RangeCellToR1C1_V1(cellValues[0], row, col, rowIncr, colIncr); | |
| result += ":"; | |
| result += (cellValues.Length > 1) ? RangeCellToR1C1_V1(cellValues[1], row, col, rowIncr, colIncr) : ""; | |
| // Return converted range expression | |
| return result; | |
| } | |
| private static string RangeCellToR1C1_V1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| int addrRow, addrCol; | |
| bool fixedRow, fixedCol; | |
| string result = ""; | |
| if (GetRowCol_V1(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol)) | |
| { | |
| if (addrRow > 0) | |
| { | |
| result += "R"; | |
| if (fixedRow) | |
| { | |
| // Absolute row | |
| result += addrRow.ToString(); | |
| } | |
| else if (addrRow - row != 0) | |
| { | |
| // Relative row | |
| result += string.Format("[{0}]", addrRow - row); | |
| } | |
| } | |
| if (addrCol > 0) | |
| { | |
| result += "C"; | |
| if (fixedCol) | |
| { | |
| // Absolute column | |
| result += addrCol; | |
| } | |
| else if (addrCol - col != 0) | |
| { | |
| // Relative column | |
| result += string.Format("[{0}]", addrCol - col); | |
| } | |
| } | |
| return result; | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| } | |
| /// <summary> | |
| /// Translates to absolute address | |
| /// </summary> | |
| /// <param name="part"></param> | |
| /// <param name="row"></param> | |
| /// <param name="col"></param> | |
| /// <param name="rowIncr"></param> | |
| /// <param name="colIncr"></param> | |
| /// <returns></returns> | |
| /// | |
| private static string ToAbs(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| string check = part.ToUpper(CultureInfo.InvariantCulture); | |
| int rStart = check.IndexOf("R"); | |
| if (rStart != 0) | |
| return part; | |
| if (part.Length == 1) //R | |
| { | |
| return GetAddress(row, col); | |
| } | |
| int cStart = check.IndexOf("C"); | |
| bool absoluteRow, absoluteCol; | |
| if (cStart == -1) | |
| { | |
| int RNum = GetRC(part, row, out absoluteRow); | |
| if (RNum > int.MinValue) | |
| { | |
| return GetAddress(RNum, absoluteRow, col, false); | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| } | |
| else | |
| { | |
| int RNum = GetRC(part.Substring(1, cStart - 1), row, out absoluteRow); | |
| int CNum = GetRC(part.Substring(cStart + 1, part.Length - cStart - 1), col, out absoluteCol); | |
| if (RNum > int.MinValue && CNum > int.MinValue) | |
| { | |
| return GetAddress(RNum, absoluteRow, CNum, absoluteCol); | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| } | |
| } | |
| private static string ToAbs_V1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| bool absoluteCol = false; | |
| bool absoluteRow = false; | |
| int colNum = -1; | |
| int rowNum = -1; | |
| int num; | |
| int numLength; | |
| int pos = 0; | |
| // Handle range expressions | |
| if ((part.Length > 1) && (part.IndexOf(':', 1) > 0)) | |
| { | |
| return RangeToA1_V1(part, row, col, rowIncr, colIncr); | |
| } | |
| // Ensure part is present | |
| if (string.IsNullOrWhiteSpace(part)) | |
| return ""; | |
| // Convert to upper case | |
| string check = part.ToUpper(CultureInfo.InvariantCulture); | |
| // Parse "R", if any | |
| if (pos < part.Length && check[pos] == 'R') | |
| { | |
| pos += 1; | |
| if (pos >= part.Length) | |
| { | |
| // Only "R" present | |
| absoluteRow = false; | |
| rowNum = row; | |
| } | |
| else if (pos < part.Length && check[pos] == 'C') | |
| { | |
| // "R" followed by "C" | |
| absoluteRow = false; | |
| rowNum = row; | |
| } | |
| else if (pos < part.Length && check[pos] == '[') | |
| { | |
| // "R" followed by relative row number | |
| pos += 1; | |
| num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); | |
| if (num == Int32.MinValue) | |
| return part; | |
| pos += numLength; | |
| if (pos < part.Length && check[pos] == ']') | |
| { | |
| pos += 1; | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| absoluteRow = false; | |
| rowNum = row + num; | |
| } | |
| else if (pos < part.Length) | |
| { | |
| // "R" followed by absolute row number | |
| num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); | |
| if (rowNum == Int32.MinValue) | |
| return part; | |
| pos += numLength; | |
| absoluteRow = true; | |
| rowNum = num; | |
| } | |
| } | |
| // Parse "C", if any | |
| if (pos < part.Length && check[pos] == 'C') | |
| { | |
| pos += 1; | |
| if (pos >= part.Length) | |
| { | |
| // Only "C" present | |
| absoluteCol = false; | |
| colNum = col; | |
| } | |
| else if (pos < part.Length && check[pos] == '[') | |
| { | |
| // "C" followed by relative column number | |
| pos += 1; | |
| num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); | |
| if (num == Int32.MinValue) | |
| return part; | |
| pos += numLength; | |
| if (pos < part.Length && check[pos] == ']') | |
| { | |
| pos += 1; | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| absoluteCol = false; | |
| colNum = col + num; | |
| } | |
| else if (pos < part.Length) | |
| { | |
| // "C" followed by absolute column number | |
| num = GetNumber_V1(check.Substring(pos, part.Length - pos), out numLength); | |
| if (num == Int32.MinValue) | |
| return part; | |
| pos += numLength; | |
| absoluteCol = true; | |
| colNum = num; | |
| } | |
| } | |
| // Ensure nothing remains unparsed | |
| if (pos < part.Length) | |
| return part; | |
| // Exit if neither row nor column is present | |
| if ((rowNum == Int32.MinValue) && (colNum == Int32.MinValue)) | |
| return part; | |
| // Append column | |
| string result = ""; | |
| if (colNum >= 0) | |
| { | |
| if (absoluteCol) | |
| result += "$"; | |
| result += GetColumnLetter(colNum); | |
| } | |
| // Append row | |
| if (rowNum >= 0) | |
| { | |
| if (absoluteRow) | |
| result += "$"; | |
| result += rowNum.ToString(); | |
| } | |
| // Return result | |
| return result; | |
| } | |
| private static int GetNumber_V1(string value, out int length) | |
| { | |
| // Get number length | |
| length = 0; | |
| // Ensure value is present | |
| if (string.IsNullOrWhiteSpace(value)) | |
| return Int32.MinValue; | |
| // Check for sign | |
| if ((length < value.Length) && ((value[length] == '-') || (value[length] == '+'))) | |
| { | |
| length += 1; | |
| } | |
| // Get number length | |
| while (length < value.Length && value[length] >= '0' && value[length] <= '9') | |
| { | |
| length += 1; | |
| } | |
| // No number found | |
| if (length == 0) | |
| return Int32.MinValue; | |
| // Return number value | |
| int result; | |
| return (int.TryParse(value.Substring(0, length), out result)) ? result : Int32.MinValue; | |
| } | |
| private static string RangeToA1_V1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| // Split range expression | |
| string[] cellValues = part.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries); | |
| // Convert range expressions | |
| string result = ""; | |
| result += ToAbs_V1(cellValues[0], row, col, rowIncr, colIncr); | |
| result += ":"; | |
| result += ToAbs_V1(cellValues[1], row, col, rowIncr, colIncr); | |
| // Return converted range expression | |
| return result; | |
| } | |
| private static string RangeCellToA1_V1(string part, int row, int col, int rowIncr, int colIncr) | |
| { | |
| int addrRow, addrCol; | |
| bool fixedRow, fixedCol; | |
| string result = ""; | |
| if (GetRowCol_V1(part, out addrRow, out addrCol, false, out fixedRow, out fixedCol)) | |
| { | |
| if (addrRow > 0) | |
| { | |
| result += "R"; | |
| if (fixedRow) | |
| { | |
| // Absolute row | |
| result += addrRow.ToString(); | |
| } | |
| else if (addrRow - row != 0) | |
| { | |
| // Relative row | |
| result += string.Format("[{0}]", addrRow - row); | |
| } | |
| } | |
| if (addrCol > 0) | |
| { | |
| result += "C"; | |
| if (fixedCol) | |
| { | |
| // Absolute column | |
| result += addrCol; | |
| } | |
| else if (addrCol - col != 0) | |
| { | |
| // Relative column | |
| result += string.Format("[{0}]", addrCol - col); | |
| } | |
| } | |
| return result; | |
| } | |
| else | |
| { | |
| return part; | |
| } | |
| } | |
| /// <summary> | |
| /// Adds or subtracts a row or column to an address | |
| /// </summary> | |
| /// <param name="Address"></param> | |
| /// <param name="row"></param> | |
| /// <param name="col"></param> | |
| /// <param name="rowIncr"></param> | |
| /// <param name="colIncr"></param> | |
| /// <returns></returns> | |
| private static string AddToRowColumnTranslator(string Address, int row, int col, int rowIncr, int colIncr) | |
| { | |
| int fromRow, fromCol; | |
| if (Address == "#REF!") | |
| { | |
| return Address; | |
| } | |
| if (GetRowCol(Address, out fromRow, out fromCol, false)) | |
| { | |
| if (fromRow == 0 || fromCol == 0) | |
| { | |
| return Address; | |
| } | |
| if (rowIncr != 0 && row != 0 && fromRow >= row && Address.IndexOf('$', 1) == -1) | |
| { | |
| if (fromRow < row - rowIncr) | |
| { | |
| return "#REF!"; | |
| } | |
| fromRow = fromRow + rowIncr; | |
| } | |
| if (colIncr != 0 && col != 0 && fromCol >= col && Address.StartsWith("$") == false) | |
| { | |
| if (fromCol < col - colIncr) | |
| { | |
| return "#REF!"; | |
| } | |
| fromCol = fromCol + colIncr; | |
| } | |
| Address = GetAddress(fromRow, Address.IndexOf('$', 1) > -1, fromCol, Address.StartsWith("$")); | |
| } | |
| return Address; | |
| } | |
| /// <summary> | |
| /// Returns with brackets if the value is negative | |
| /// </summary> | |
| /// <param name="v">The value</param> | |
| /// <returns></returns> | |
| private static string GetRCFmt(int v) | |
| { | |
| return (v < 0 ? string.Format("[{0}]", v) : v > 0 ? v.ToString() : ""); | |
| } | |
| /// <summary> | |
| /// Get the offset value for RC format | |
| /// </summary> | |
| /// <param name="value"></param> | |
| /// <param name="OffsetValue"></param> | |
| /// <param name="fixedAddr"></param> | |
| /// <returns></returns> | |
| /// | |
| private static int GetRC(string value, int OffsetValue, out bool fixedAddr) | |
| { | |
| if (value == "") | |
| { | |
| fixedAddr = false; | |
| return OffsetValue; | |
| } | |
| int num; | |
| if (value[0] == '[' && value[value.Length - 1] == ']') //Offset? | |
| { | |
| fixedAddr = false; | |
| if (int.TryParse(value.Substring(1, value.Length - 2), out num)) | |
| { | |
| return (OffsetValue + num); | |
| } | |
| else | |
| { | |
| return int.MinValue; | |
| } | |
| } | |
| else | |
| { | |
| // Absolute address | |
| fixedAddr = true; | |
| if (int.TryParse(value, out num)) | |
| { | |
| return num; | |
| } | |
| else | |
| { | |
| return int.MinValue; | |
| } | |
| } | |
| } | |
| private static int GetRC_V1(string value, int OffsetValue, out bool fixedAddr) | |
| { | |
| if ((value == "") || (value == "R") || (value == "C")) | |
| { | |
| // Relative address with no offset | |
| fixedAddr = false; | |
| return OffsetValue; | |
| } | |
| int num; | |
| if (value[1] == '[' && value[value.Length - 1] == ']') //Offset? | |
| { | |
| // Relative address | |
| fixedAddr = false; | |
| if (int.TryParse(value.Substring(2, value.Length - 3), out num)) | |
| { | |
| return (OffsetValue + num); | |
| } | |
| else | |
| { | |
| return int.MinValue; | |
| } | |
| } | |
| else | |
| { | |
| // Absolute address | |
| fixedAddr = true; | |
| if (int.TryParse(value.Substring(1, value.Length - 1), out num)) | |
| { | |
| return num; | |
| } | |
| else | |
| { | |
| return int.MinValue; | |
| } | |
| } | |
| } | |
| #endregion | |
| #region "Address Functions" | |
| #region GetColumnLetter | |
| /// <summary> | |
| /// Returns the character representation of the numbered column | |
| /// </summary> | |
| /// <param name="iColumnNumber">The number of the column</param> | |
| /// <returns>The letter representing the column</returns> | |
| protected internal static string GetColumnLetter(int iColumnNumber) | |
| { | |
| return GetColumnLetter(iColumnNumber, false); | |
| } | |
| protected internal static string GetColumnLetter(int iColumnNumber, bool fixedCol) | |
| { | |
| if (iColumnNumber < 1) | |
| { | |
| //throw new Exception("Column number is out of range"); | |
| return "#REF!"; | |
| } | |
| string sCol = ""; | |
| do | |
| { | |
| sCol = ((char)('A' + ((iColumnNumber - 1) % 26))) + sCol; | |
| iColumnNumber = (iColumnNumber - ((iColumnNumber - 1) % 26)) / 26; | |
| } | |
| while (iColumnNumber > 0); | |
| return fixedCol ? "$" + sCol : sCol; | |
| } | |
| #endregion | |
| internal static bool GetRowColFromAddress(string CellAddress, out int FromRow, out int FromColumn, out int ToRow, out int ToColumn) | |
| { | |
| bool fixedFromRow, fixedFromColumn, fixedToRow, fixedToColumn; | |
| return GetRowColFromAddress(CellAddress, out FromRow, out FromColumn, out ToRow, out ToColumn, out fixedFromRow, out fixedFromColumn, out fixedToRow, out fixedToColumn); | |
| } | |
| /// <summary> | |
| /// Get the row/columns for a Cell-address | |
| /// </summary> | |
| /// <param name="CellAddress">The address</param> | |
| /// <param name="FromRow">Returns the to column</param> | |
| /// <param name="FromColumn">Returns the from column</param> | |
| /// <param name="ToRow">Returns the to row</param> | |
| /// <param name="ToColumn">Returns the from row</param> | |
| /// <param name="fixedFromRow">Is the from row fixed?</param> | |
| /// <param name="fixedFromColumn">Is the from column fixed?</param> | |
| /// <param name="fixedToRow">Is the to row fixed?</param> | |
| /// <param name="fixedToColumn">Is the to column fixed?</param> | |
| /// <returns></returns> | |
| internal static bool GetRowColFromAddress(string CellAddress, out int FromRow, out int FromColumn, out int ToRow, out int ToColumn, out bool fixedFromRow, out bool fixedFromColumn, out bool fixedToRow, out bool fixedToColumn) | |
| { | |
| bool ret; | |
| if (CellAddress.IndexOf('[') > 0) //External reference or reference to Table or Pivottable. | |
| { | |
| FromRow = -1; | |
| FromColumn = -1; | |
| ToRow = -1; | |
| ToColumn = -1; | |
| fixedFromRow = false; | |
| fixedFromColumn = false; | |
| fixedToRow= false; | |
| fixedToColumn = false; | |
| return false; | |
| } | |
| CellAddress = CellAddress.ToUpper(CultureInfo.InvariantCulture); | |
| //This one can be removed when the worksheet Select format is fixed | |
| if (CellAddress.IndexOf(' ') > 0) | |
| { | |
| CellAddress = CellAddress.Substring(0, CellAddress.IndexOf(' ')); | |
| } | |
| if (CellAddress.IndexOf(':') < 0) | |
| { | |
| ret = GetRowColFromAddress(CellAddress, out FromRow, out FromColumn, out fixedFromRow, out fixedFromColumn); | |
| ToColumn = FromColumn; | |
| ToRow = FromRow; | |
| fixedToRow = fixedFromRow; | |
| fixedToColumn = fixedFromColumn; | |
| } | |
| else | |
| { | |
| string[] cells = CellAddress.Split(':'); | |
| ret = GetRowColFromAddress(cells[0], out FromRow, out FromColumn, out fixedFromRow, out fixedFromColumn); | |
| if (ret) | |
| ret = GetRowColFromAddress(cells[1], out ToRow, out ToColumn, out fixedToRow, out fixedToColumn); | |
| else | |
| { | |
| GetRowColFromAddress(cells[1], out ToRow, out ToColumn, out fixedToRow, out fixedToColumn); | |
| } | |
| if (FromColumn <= 0) | |
| FromColumn = 1; | |
| if (FromRow <= 0) | |
| FromRow = 1; | |
| if (ToColumn <= 0) | |
| ToColumn = ExcelPackage.MaxColumns; | |
| if (ToRow <= 0) | |
| ToRow = ExcelPackage.MaxRows; | |
| } | |
| return ret; | |
| } | |
| /// <summary> | |
| /// Get the row/column for n Cell-address | |
| /// </summary> | |
| /// <param name="CellAddress">The address</param> | |
| /// <param name="Row">Returns Tthe row</param> | |
| /// <param name="Column">Returns the column</param> | |
| /// <returns>true if valid</returns> | |
| internal static bool GetRowColFromAddress(string CellAddress, out int Row, out int Column) | |
| { | |
| return GetRowCol(CellAddress, out Row, out Column, true); | |
| } | |
| internal static bool GetRowColFromAddress(string CellAddress, out int row, out int col, out bool fixedRow, out bool fixedCol) | |
| { | |
| return GetRowCol(CellAddress, out row, out col, true, out fixedRow, out fixedCol); | |
| } | |
| /// <summary> | |
| /// Get the row/column for a Cell-address | |
| /// </summary> | |
| /// <param name="address">the address</param> | |
| /// <param name="row">returns the row</param> | |
| /// <param name="col">returns the column</param> | |
| /// <param name="throwException">throw exception if invalid, otherwise returns false</param> | |
| /// <returns></returns> | |
| internal static bool GetRowCol(string address, out int row, out int col, bool throwException) | |
| { | |
| bool fixedRow, fixedCol; | |
| return GetRowCol(address, out row, out col, throwException, out fixedRow, out fixedCol); | |
| } | |
| internal static bool GetRowCol(string address, out int row, out int col, bool throwException, out bool fixedRow, out bool fixedCol) | |
| { | |
| bool colPart = true; | |
| int colStartIx = 0; | |
| int colLength = 0; | |
| col = 0; | |
| row = 0; | |
| fixedRow = false; | |
| fixedCol = false; | |
| if (address.EndsWith("#REF!")) | |
| { | |
| row = 0; | |
| col = 0; | |
| return true; | |
| } | |
| int sheetMarkerIndex = address.IndexOf('!'); | |
| if (sheetMarkerIndex >= 0) | |
| { | |
| colStartIx = sheetMarkerIndex + 1; | |
| } | |
| address = address.ToUpper(CultureInfo.InvariantCulture); | |
| for (int i = colStartIx; i < address.Length; i++) | |
| { | |
| char c = address[i]; | |
| if (colPart && (c >= 'A' && c <= 'Z') && colLength <= 3) | |
| { | |
| col *= 26; | |
| col += ((int)c) - 64; | |
| colLength++; | |
| } | |
| else if (c >= '0' && c <= '9') | |
| { | |
| row *= 10; | |
| row += ((int)c) - 48; | |
| colPart = false; | |
| } | |
| else if (c == '$') | |
| { | |
| if (i == colStartIx) | |
| { | |
| colStartIx++; | |
| fixedCol = true; | |
| } | |
| else | |
| { | |
| colPart = false; | |
| fixedRow = true; | |
| } | |
| } | |
| else | |
| { | |
| row = 0; | |
| col = 0; | |
| if (throwException) | |
| { | |
| throw (new Exception(string.Format("Invalid Address format {0}", address))); | |
| } | |
| else | |
| { | |
| return false; | |
| } | |
| } | |
| } | |
| return row != 0 || col != 0; | |
| } | |
| internal static bool GetRowCol_V1(string address, out int row, out int col, bool throwException, out bool fixedRow, out bool fixedCol) | |
| { | |
| bool colPart = true; | |
| bool isFixed = false; | |
| int colStartIx = 0; | |
| int colLength = 0; | |
| col = 0; | |
| row = 0; | |
| fixedRow = false; | |
| fixedCol = false; | |
| if (address.EndsWith("#REF!")) | |
| { | |
| row = 0; | |
| col = 0; | |
| return true; | |
| } | |
| int sheetMarkerIndex = address.IndexOf('!'); | |
| if (sheetMarkerIndex >= 0) | |
| { | |
| colStartIx = sheetMarkerIndex + 1; | |
| } | |
| address = address.ToUpper(CultureInfo.InvariantCulture); | |
| for (int i = colStartIx; i < address.Length; i++) | |
| { | |
| char c = address[i]; | |
| if (c == '$') | |
| { | |
| // Absolute address | |
| isFixed = true; | |
| } | |
| else if (colPart && (c >= 'A' && c <= 'Z') && colLength <= 3) | |
| { | |
| // Column portion of address | |
| if (isFixed) | |
| { | |
| fixedCol = true; | |
| isFixed = false; | |
| } | |
| col *= 26; | |
| col += ((int)c) - 64; | |
| colLength++; | |
| } | |
| else if (c >= '0' && c <= '9') | |
| { | |
| // Row portion of address | |
| if (isFixed) | |
| { | |
| fixedRow = true; | |
| isFixed = false; | |
| } | |
| row *= 10; | |
| row += ((int)c) - 48; | |
| colPart = false; | |
| } | |
| else | |
| { | |
| row = 0; | |
| col = 0; | |
| if (throwException) | |
| { | |
| throw (new Exception(string.Format("Invalid Address format {0}", address))); | |
| } | |
| else | |
| { | |
| return false; | |
| } | |
| } | |
| } | |
| return row != 0 || col != 0; | |
| } | |
| private static int GetColumn(string sCol) | |
| { | |
| int col = 0; | |
| int len = sCol.Length - 1; | |
| for (int i = len; i >= 0; i--) | |
| { | |
| col += (((int)sCol[i]) - 64) * (int)(Math.Pow(26, len - i)); | |
| } | |
| return col; | |
| } | |
| #region GetAddress | |
| /// <summary> | |
| /// Returns the AlphaNumeric representation that Excel expects for a Cell Address | |
| /// </summary> | |
| /// <param name="Row">The number of the row</param> | |
| /// <param name="Column">The number of the column in the worksheet</param> | |
| /// <returns>The cell address in the format A1</returns> | |
| public static string GetAddress(int Row, int Column) | |
| { | |
| return GetAddress(Row, Column,false); | |
| } | |
| /// <summary> | |
| /// Returns the AlphaNumeric representation that Excel expects for a Cell Address | |
| /// </summary> | |
| /// <param name="Row">The number of the row</param> | |
| /// <param name="Column">The number of the column in the worksheet</param> | |
| /// <param name="AbsoluteRow">Absolute row</param> | |
| /// <param name="AbsoluteCol">Absolute column</param> | |
| /// <returns>The cell address in the format A1</returns> | |
| public static string GetAddress(int Row, bool AbsoluteRow, int Column, bool AbsoluteCol) | |
| { | |
| return ( AbsoluteCol ? "$" : "") + GetColumnLetter(Column) + ( AbsoluteRow ? "$" : "") + Row.ToString(); | |
| } | |
| /// <summary> | |
| /// Returns the AlphaNumeric representation that Excel expects for a Cell Address | |
| /// </summary> | |
| /// <param name="Row">The number of the row</param> | |
| /// <param name="Column">The number of the column in the worksheet</param> | |
| /// <param name="Absolute">Get an absolute address ($A$1)</param> | |
| /// <returns>The cell address in the format A1</returns> | |
| public static string GetAddress(int Row, int Column, bool Absolute) | |
| { | |
| if (Row == 0 || Column == 0) | |
| { | |
| return "#REF!"; | |
| } | |
| if (Absolute) | |
| { | |
| return ("$" + GetColumnLetter(Column) + "$" + Row.ToString()); | |
| } | |
| else | |
| { | |
| return (GetColumnLetter(Column) + Row.ToString()); | |
| } | |
| } | |
| /// <summary> | |
| /// Returns the AlphaNumeric representation that Excel expects for a Cell Address | |
| /// </summary> | |
| /// <param name="FromRow">From row number</param> | |
| /// <param name="FromColumn">From column number</param> | |
| /// <param name="ToRow">To row number</param> | |
| /// <param name="ToColumn">From column number</param> | |
| /// <returns>The cell address in the format A1</returns> | |
| public static string GetAddress(int FromRow, int FromColumn, int ToRow, int ToColumn) | |
| { | |
| return GetAddress(FromRow, FromColumn, ToRow, ToColumn, false); | |
| } | |
| /// <summary> | |
| /// Returns the AlphaNumeric representation that Excel expects for a Cell Address | |
| /// </summary> | |
| /// <param name="FromRow">From row number</param> | |
| /// <param name="FromColumn">From column number</param> | |
| /// <param name="ToRow">To row number</param> | |
| /// <param name="ToColumn">From column number</param> | |
| /// <param name="Absolute">if true address is absolute (like $A$1)</param> | |
| /// <returns>The cell address in the format A1</returns> | |
| public static string GetAddress(int FromRow, int FromColumn, int ToRow, int ToColumn, bool Absolute) | |
| { | |
| if (FromRow == ToRow && FromColumn == ToColumn) | |
| { | |
| return GetAddress(FromRow, FromColumn, Absolute); | |
| } | |
| else | |
| { | |
| if (FromRow == 1 && ToRow == ExcelPackage.MaxRows) | |
| { | |
| var absChar = Absolute ? "$" : ""; | |
| return absChar + GetColumnLetter(FromColumn) + ":" + absChar + GetColumnLetter(ToColumn); | |
| } | |
| else if(FromColumn==1 && ToColumn==ExcelPackage.MaxColumns) | |
| { | |
| var absChar = Absolute ? "$" : ""; | |
| return absChar + FromRow.ToString() + ":" + absChar + ToRow.ToString(); | |
| } | |
| else | |
| { | |
| return GetAddress(FromRow, FromColumn, Absolute) + ":" + GetAddress(ToRow, ToColumn, Absolute); | |
| } | |
| } | |
| } | |
| /// <summary> | |
| /// Returns the AlphaNumeric representation that Excel expects for a Cell Address | |
| /// </summary> | |
| /// <param name="FromRow">From row number</param> | |
| /// <param name="FromColumn">From column number</param> | |
| /// <param name="ToRow">To row number</param> | |
| /// <param name="ToColumn">From column number</param> | |
| /// <param name="FixedFromColumn"></param> | |
| /// <param name="FixedFromRow"></param> | |
| /// <param name="FixedToColumn"></param> | |
| /// <param name="FixedToRow"></param> | |
| /// <returns>The cell address in the format A1</returns> | |
| public static string GetAddress(int FromRow, int FromColumn, int ToRow, int ToColumn, bool FixedFromRow, bool FixedFromColumn, bool FixedToRow, bool FixedToColumn) | |
| { | |
| if (FromRow == ToRow && FromColumn == ToColumn) | |
| { | |
| return GetAddress(FromRow, FixedFromRow, FromColumn, FixedFromColumn); | |
| } | |
| else | |
| { | |
| if (FromRow == 1 && ToRow == ExcelPackage.MaxRows) | |
| { | |
| return GetColumnLetter(FromColumn, FixedFromColumn) + ":" + GetColumnLetter(ToColumn, FixedToColumn); | |
| } | |
| else if (FromColumn == 1 && ToColumn == ExcelPackage.MaxColumns) | |
| { | |
| return (FixedFromRow ? "$":"") + FromRow.ToString() + ":" + (FixedToRow ? "$":"") + ToRow.ToString(); | |
| } | |
| else | |
| { | |
| return GetAddress(FromRow, FixedFromRow, FromColumn, FixedFromColumn) + ":" + GetAddress(ToRow, FixedToRow, ToColumn, FixedToColumn); | |
| } | |
| } | |
| } | |
| /// <summary> | |
| /// Get the full address including the worksheet name | |
| /// </summary> | |
| /// <param name="worksheetName">The name of the worksheet</param> | |
| /// <param name="address">The address</param> | |
| /// <returns>The full address</returns> | |
| public static string GetFullAddress(string worksheetName, string address) | |
| { | |
| return GetFullAddress(worksheetName, address, true); | |
| } | |
| internal static string GetFullAddress(string worksheetName, string address, bool fullRowCol) | |
| { | |
| if (address.IndexOf("!") == -1 || address=="#REF!") | |
| { | |
| if (fullRowCol) | |
| { | |
| string[] cells = address.Split(':'); | |
| if (cells.Length > 0) | |
| { | |
| address = string.Format("'{0}'!{1}", worksheetName, cells[0]); | |
| if (cells.Length > 1) | |
| { | |
| address += string.Format(":{0}", cells[1]); | |
| } | |
| } | |
| } | |
| else | |
| { | |
| var a = new ExcelAddressBase(address); | |
| if ((a._fromRow == 1 && a._toRow == ExcelPackage.MaxRows) || (a._fromCol == 1 && a._toCol == ExcelPackage.MaxColumns)) | |
| { | |
| address = string.Format("'{0}'!{1}{2}:{3}{4}", worksheetName, ExcelAddress.GetColumnLetter(a._fromCol), a._fromRow, ExcelAddress.GetColumnLetter(a._toCol), a._toRow); | |
| } | |
| else | |
| { | |
| address=GetFullAddress(worksheetName, address, true); | |
| } | |
| } | |
| } | |
| return address; | |
| } | |
| #endregion | |
| #region IsValidCellAddress | |
| public static bool IsValidAddress(string address) | |
| { | |
| address = address.ToUpper(CultureInfo.InvariantCulture); | |
| string r1 = "", c1 = "", r2 = "", c2 = ""; | |
| bool isSecond = false; | |
| for (int i = 0; i < address.Length; i++) | |
| { | |
| if (address[i] >= 'A' && address[i] <= 'Z') | |
| { | |
| if (isSecond == false) | |
| { | |
| if (r1 != "") return false; | |
| c1 += address[i]; | |
| if (c1.Length > 3) return false; | |
| } | |
| else | |
| { | |
| if (r2 != "") return false; | |
| c2 += address[i]; | |
| if (c2.Length > 3) return false; | |
| } | |
| } | |
| else if (address[i] >= '0' && address[i] <= '9') | |
| { | |
| if (isSecond == false) | |
| { | |
| r1 += address[i]; | |
| if (r1.Length > 7) return false; | |
| } | |
| else | |
| { | |
| r2 += address[i]; | |
| if (r2.Length > 7) return false; | |
| } | |
| } | |
| else if (address[i] == ':') | |
| { | |
| isSecond=true; | |
| } | |
| else if (address[i] == '$') | |
| { | |
| if (i == address.Length - 1 || address[i + 1] == ':') | |
| { | |
| return false; | |
| } | |
| } | |
| else | |
| { | |
| return false; | |
| } | |
| } | |
| if (r1!="" && c1!="" && r2 == "" && c2 == "") //Single Cell | |
| { | |
| return (GetColumn(c1)<=ExcelPackage.MaxColumns && int.Parse(r1)<=ExcelPackage.MaxRows); | |
| } | |
| else if (r1 != "" && r2 != "" && c1 != "" && c2 != "") //Range | |
| { | |
| var iR2 = int.Parse(r2); | |
| var iC2 = GetColumn(c2); | |
| return GetColumn(c1) <= iC2 && int.Parse(r1) <= iR2 && | |
| iC2 <= ExcelPackage.MaxColumns && iR2 <= ExcelPackage.MaxRows; | |
| } | |
| else if (r1 == "" && r2 == "" && c1 != "" && c2 != "") //Full Column | |
| { | |
| var c2n=GetColumn(c2); | |
| return (GetColumn(c1) <= c2n && c2n <= ExcelPackage.MaxColumns); | |
| } | |
| else if (r1 != "" && r2 != "" && c1 == "" && c2 == "") | |
| { | |
| var iR2 = int.Parse(r2); | |
| return int.Parse(r1) <= iR2 && iR2 <= ExcelPackage.MaxRows; | |
| } | |
| else | |
| { | |
| return false; | |
| } | |
| } | |
| /// <summary> | |
| /// Checks that a cell address (e.g. A5) is valid. | |
| /// </summary> | |
| /// <param name="cellAddress">The alphanumeric cell address</param> | |
| /// <returns>True if the cell address is valid</returns> | |
| public static bool IsValidCellAddress(string cellAddress) | |
| { | |
| bool result = false; | |
| try | |
| { | |
| int row, col; | |
| if (GetRowColFromAddress(cellAddress, out row, out col)) | |
| { | |
| if (row > 0 && col > 0 && row <= ExcelPackage.MaxRows && col <= ExcelPackage.MaxColumns) | |
| result = true; | |
| else | |
| result = false; | |
| } | |
| } | |
| catch { } | |
| return result; | |
| } | |
| #endregion | |
| #region UpdateFormulaReferences | |
| /// <summary> | |
| /// Updates the Excel formula so that all the cellAddresses are incremented by the row and column increments | |
| /// if they fall after the afterRow and afterColumn. | |
| /// Supports inserting rows and columns into existing templates. | |
| /// </summary> | |
| /// <param name="Formula">The Excel formula</param> | |
| /// <param name="rowIncrement">The amount to increment the cell reference by</param> | |
| /// <param name="colIncrement">The amount to increment the cell reference by</param> | |
| /// <param name="afterRow">Only change rows after this row</param> | |
| /// <param name="afterColumn">Only change columns after this column</param> | |
| /// <returns></returns> | |
| internal static string UpdateFormulaReferences(string Formula, int rowIncrement, int colIncrement, int afterRow, int afterColumn, bool setFixed=false) | |
| { | |
| //return Translate(Formula, AddToRowColumnTranslator, afterRow, afterColumn, rowIncrement, colIncrement); | |
| var d=new Dictionary<string, object>(); | |
| try | |
| { | |
| var sct = new SourceCodeTokenizer(FunctionNameProvider.Empty, NameValueProvider.Empty); | |
| var tokens = sct.Tokenize(Formula); | |
| String f = ""; | |
| foreach (var t in tokens) | |
| { | |
| if (t.TokenType == TokenType.ExcelAddress) | |
| { | |
| var a = new ExcelAddressBase(t.Value); | |
| if (!String.IsNullOrEmpty(a._ws)) // Bug 15339 | |
| { | |
| // This is from a different worksheet, thus no update is required | |
| f += a.Address; | |
| continue; | |
| } | |
| if (rowIncrement > 0) | |
| { | |
| a = a.AddRow(afterRow, rowIncrement, setFixed); | |
| } | |
| else if (rowIncrement < 0) | |
| { | |
| a = a.DeleteRow(afterRow, -rowIncrement, setFixed); | |
| } | |
| if (colIncrement > 0) | |
| { | |
| a = a.AddColumn(afterColumn, colIncrement, setFixed); | |
| } | |
| else if (colIncrement < 0) | |
| { | |
| a = a.DeleteColumn(afterColumn, -colIncrement, setFixed); | |
| } | |
| if (a == null || !a.IsValidRowCol()) | |
| { | |
| f += "#REF!"; | |
| } | |
| else | |
| { | |
| f += a.Address; | |
| } | |
| } | |
| else | |
| { | |
| f += t.Value; | |
| } | |
| } | |
| return f; | |
| } | |
| catch //Invalid formula, skip updateing addresses | |
| { | |
| return Formula; | |
| } | |
| } | |
| #endregion | |
| #endregion | |
| #endregion | |
| } | |
| } |