Bugfix ArrayFormula
diff --git a/EPPlus/ExcelRangeBase.cs b/EPPlus/ExcelRangeBase.cs index f1f206c..288ad58 100644 --- a/EPPlus/ExcelRangeBase.cs +++ b/EPPlus/ExcelRangeBase.cs
@@ -128,7 +128,7 @@ } ~ExcelRangeBase() { - this.AddressChange -= new EventHandler(ExcelRangeBase_AddressChange); + //this.AddressChange -= new EventHandler(ExcelRangeBase_AddressChange); } #endregion #region Set Value Delegates @@ -328,7 +328,11 @@ _worksheet._values.SetValue(row, col, (Uri)null); } } - private void Set_IsRichText(object value, int row, int col) + private void Set_IsArrayFormula(object value, int row, int col) + { + _worksheet._flags.SetFlagValue(row, col, (bool)value, CellFlags.ArrayFormula); + } + private void Set_IsRichText(object value, int row, int col) { //_worksheet.Cell(row, col).IsRichText = (bool)value; _worksheet._flags.SetFlagValue(row, col, (bool)value, CellFlags.RichText); @@ -1162,10 +1166,18 @@ } } } - /// <summary> - /// Set the hyperlink property for a range of cells - /// </summary> - public Uri Hyperlink + public string ArrayFormulaAddress + { + get + { + IsRangeValid("FormulaR1C1"); + return _worksheet.GetArrayFormulaAddress(_fromRow, _fromCol); + } + } + /// <summary> + /// Set the hyperlink property for a range of cells + /// </summary> + public Uri Hyperlink { get { @@ -1321,7 +1333,11 @@ IsRangeValid("arrayformulas"); return _worksheet._flags.GetFlagValue(_fromRow, _fromCol, CellFlags.ArrayFormula); } - } + set + { + _changePropMethod(Set_IsArrayFormula, value); + } + } ExcelRichTextCollection _rtc = null; /// <summary> /// Cell value is richtext formatted.
diff --git a/EPPlus/ExcelWorksheet.cs b/EPPlus/ExcelWorksheet.cs index e79d163..995febe 100644 --- a/EPPlus/ExcelWorksheet.cs +++ b/EPPlus/ExcelWorksheet.cs
@@ -120,7 +120,37 @@ _tokenizer = tokenizer; } - private ISourceCodeTokenizer _tokenizer; + public static string RemoveDummyFunction(string formula) + { + const string DummyFunctionPrefix = "IFERROR(__xludf.DUMMYFUNCTION(\""; + const string DummyFunctionSuffix = "\"),\""; + + if (string.IsNullOrEmpty(formula)) + return formula; + + // Look for Prefix + if (!formula.StartsWith(DummyFunctionPrefix)) + return formula; + + // Look for Suffix + int index = formula.LastIndexOf(DummyFunctionSuffix); + if (index < 0) + return formula; + + // Trim Suffix + formula = formula.Substring(0, index); + + // Trim Prefix + formula = formula.Replace(DummyFunctionPrefix, ""); + + // Replace doubled quotes with single quote + formula = formula.Replace("\"\"", "\""); + + // Return formula + return formula; + } + + private ISourceCodeTokenizer _tokenizer; internal int Index { get; set; } internal string Address { get; set; } internal bool IsArray { get; set; } @@ -129,17 +159,18 @@ public int StartCol { get; set; } private IEnumerable<Token> Tokens {get; set;} + internal string GetFormula(int row, int column, string worksheet) { - if (StartRow == row && StartCol == column) + if ((StartRow == row && StartCol == column) || IsArray) { - return Formula; + return RemoveDummyFunction(Formula); } if (Tokens == null) { - Tokens = _tokenizer.Tokenize(Formula, worksheet); + Tokens = _tokenizer.Tokenize(RemoveDummyFunction(Formula), worksheet); } string f = ""; @@ -1255,11 +1286,37 @@ else if (t == "array") //TODO: Array functions are not support yet. Read the formula for the start cell only. { string aAddress = xr.GetAttribute("ref"); + ExcelRange addressRange = new ExcelRange(this, aAddress); string formula = xr.ReadElementContentAsString(); - var afIndex = GetMaxShareFunctionIndex(true); - _formulas.SetValue(address._fromRow, address._fromCol, afIndex); - _values.SetValue(address._fromRow, address._fromCol, null); - _sharedFormulas.Add(afIndex, new Formulas(SourceCodeTokenizer.Default) { Index = afIndex, Formula = formula, Address = aAddress, StartRow = address._fromRow, StartCol = address._fromCol, IsArray = true }); + bool isIndexMatchFormula = Regex.IsMatch(formula, @"INDEX\(", RegexOptions.IgnoreCase) && Regex.IsMatch(formula, @"MATCH\(", RegexOptions.IgnoreCase) && !aAddress.Contains(":"); + if (isIndexMatchFormula) + { + addressRange.IsArrayFormula = false; + for (int colIndex = addressRange.Start.Column; colIndex <= addressRange.End.Column; colIndex++) + { + for (int rowIndex = addressRange.Start.Row; rowIndex <= addressRange.End.Row; rowIndex++) + { + var afIndex = GetMaxShareFunctionIndex(true); + _formulas.SetValue(rowIndex, colIndex, afIndex); + _values.SetValue(rowIndex, colIndex, null); + _sharedFormulas.Add(afIndex, new Formulas(SourceCodeTokenizer.Default) { Index = afIndex, Formula = formula, Address = aAddress, StartRow = address._fromRow, StartCol = address._fromCol, IsArray = false }); + } + } + } + else + { + addressRange.IsArrayFormula = true; + var afIndex = GetMaxShareFunctionIndex(true); + for (int colIndex = addressRange.Start.Column; colIndex <= addressRange.End.Column; colIndex++) + { + for (int rowIndex = addressRange.Start.Row; rowIndex <= addressRange.End.Row; rowIndex++) + { + _formulas.SetValue(rowIndex, colIndex, afIndex); + _values.SetValue(rowIndex, colIndex, null); + } + } + _sharedFormulas.Add(afIndex, new Formulas(SourceCodeTokenizer.Default) { Index = afIndex, Formula = formula, Address = aAddress, StartRow = address._fromRow, StartCol = address._fromCol, IsArray = true }); + } } else // ??? some other type { @@ -1365,6 +1422,18 @@ }; } + private DateTime DurationFromOADate(double value) + { + // Convert the Duration value into the corresponding DateTime value. + // The Duration value is passed in as a fractional Office Automation double values where the + // whole portion is the number of days and the fractional portion of the number is the hours. + // For example, the Duration value -1.5 represents a negative duration of one day and 12 hours. + // We use this technique rather than calling the 'DateTime.FromOADate()' method, because + // this technique works for both positive and negative duration values. unlike 'DateTime.FromOADate()' + // which yields the wrong result for negative Duration values. + return new DateTime(1899, 12, 30) + TimeSpan.FromDays(value); + } + private void SetValueFromXml(XmlTextReader xr, string type, int styleID, int row, int col) { //XmlNode vnode = colNode.SelectSingleNode("d:v", NameSpaceManager); @@ -1372,6 +1441,17 @@ if (type == "s") { int ix = xr.ReadElementContentAsInt(); + + // Temporary debugging code to locate intermittent 'Index was out of range' exception. + if (ix < 0) + { + throw new Exception(string.Format("ReadElementContentAsInt returned value '{0}' which is less than zero.", ix)); + } + if (ix >= _package.Workbook._sharedStringsList.Count) + { + throw new Exception(string.Format("ReadElementContentAsInt returned index value '{0}' which is greater than _sharedStringsList count of {1}.", ix, _package.Workbook._sharedStringsList.Count)); + } + _values.SetValue(row, col, _package.Workbook._sharedStringsList[ix].Text); if (_package.Workbook._sharedStringsList[ix].isRichText) { @@ -1394,7 +1474,31 @@ { string v = xr.ReadElementContentAsString(); var nf = Workbook.Styles.CellXfs[styleID].NumberFormatId; - if ((nf >= 14 && nf <= 22) || (nf >= 45 && nf <= 47)) + if ((nf >= 20 && nf <= 21) || (nf >= 45 && nf <= 47)) // Duration + { + double res; + if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out res)) + { + if (Workbook.Date1904) + { + res += ExcelWorkbook.date1904Offset; + } + if (res >= -657435.0 && res < 2958465.9999999) + { + // Get the Duration value expressed as a DateTime. + _values.SetValue(row, col, DurationFromOADate(res)); + } + else + { + _values.SetValue(row, col, ""); + } + } + else + { + _values.SetValue(row, col, ""); + } + } + else if ((nf >= 14 && nf <= 19) || (nf == 22)) // DateTime { double res; if (double.TryParse(v, NumberStyles.Any, CultureInfo.InvariantCulture, out res)) @@ -1410,7 +1514,7 @@ else { _values.SetValue(row, col, ""); - } + } } else { @@ -4095,7 +4199,7 @@ if (v is int) { var sf = _sharedFormulas[(int)v]; - return ExcelCellBase.TranslateToR1C1(sf.Formula, sf.StartRow, sf.StartCol); + return ExcelCellBase.TranslateToR1C1(Formulas.RemoveDummyFunction(sf.Formula), sf.StartRow, sf.StartCol); } else if (v != null) { @@ -4106,6 +4210,18 @@ return ""; } } + internal string GetArrayFormulaAddress(int row, int col) + { + var v = _formulas.GetValue(row, col); + if ((v is int) && (_sharedFormulas[(int)v].IsArray)) + { + return _sharedFormulas[(int)v].Address; + } + else + { + return ""; + } + } private void DisposeInternal(IDisposable candidateDisposable) {