Hello, can you please tell me what is the status of this?
I also have another issue (see the attached spreadsheet) that might be related.
This is the formula from the spreadsheet that doesn’t work in the Aspose calculation engine:
=SUMPRODUCT(Table1[Value],(–(Table1[Date]>=DATEVALUE(“2013-09-15”))))
This link describes how this type of formulas work: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
I think ultimately the Aspose calculation engine doesn’t support this type of expressions:
<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–>
(--(Table1[Date]>=DATEVALUE("2013-09-15"))
that get expanded into arrays.
<!–[if gte mso 10]>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman","serif";}
<![endif]–>
Any chance that you might support these formulas as well?
Code:
using System;
using System.Collections;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Aspose.Cells;
using Aspose.Cells.Charts;
using Aspose.Cells.Drawing;
using Aspose.Cells.Pivot;
using Aspose.Cells.Rendering;
using Aspose.Cells.Tables;
using TestAspose.NorthwindDataSetTableAdapters;
//using pk =DocumentFormat.OpenXml.Packaging;
//using ox=DocumentFormat.OpenXml;
//using oxs = DocumentFormat.OpenXml.Spreadsheet;
namespace TestAspose
{
class Program
{
static void Main(string[] args)
{
TestSumProduct();
Console.WriteLine(“Done!”);
Console.ReadKey();
}
public static void TestSumProduct()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test_sumproduct.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
workbook.CalculateFormula(true);
Console.WriteLine(worksheet.Cells[“D1”].Value); // <- this should print 360
Console.WriteLine(worksheet.Cells[“C16”].Value);
}
}
}