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)
{
