Excel syntax not supported by Aspose

Aspose.Cells Product Family
1

The following syntax is not supported in Aspose.Cells:

=LOOKUP(2,1/(B$2:B2<>0),B$2:B2)


<!–[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>
<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]–><!–[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:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–>
The expression: 1/(B$2:B2<>0) looks strange.

Do you have any plans to support it? I just came across it from a post. Do you know what it actually does (having to spend so much time in this space I assume that you might have come across it)?

Thanks

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your source excel file having these formulas and sample code replicating this issue with the latest version. We will look into it and update you asap.

Please also download and try the latest version: Aspose.Cells
for .NET v7.5.1.2 and see if it fixes your issue.

3

The following little function displays #N/A when it runs.

public static void TestArrayFormula()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\test03.xlsx”);
Worksheet sheet1 = workbook.Worksheets[0];

workbook.CalculateFormula(false);
Console.WriteLine("Value: " + sheet1.Cells[“E1”].Value);


}

I guess the general question is whether aspose supports array formulas. I thought it did. There are some edge cases where you have operations such as 1 / which becomes an array <1/a1, 1/a2, …> where a1, a2 are the elements of the array.

If you want to test your product I came across this site: http://people.highline.edu/mgirvin/ExcelIsFun.htm

They have a lot of different spreadsheets. They also have videos.


With regards to the second spreadsheet, if I run this function:

public static void TestArrayFormula2()
{
Workbook workbook = new Workbook(@“C:\temp\spreadsheets\EMT984.xlsx”);
Worksheet sheet1 = workbook.Worksheets[0];
Worksheet sheet2 = workbook.Worksheets[1];

workbook.CalculateFormula(false);
Console.WriteLine("Value: " + sheet2.Cells[“G2”].Value);


}

it displays:
Value: #NAME?

It should display Value: happy.

Could you please fix it? There is also a video associated with the spreadsheet: https://www.youtube.com/watch?v=s97SkREIqZY

thanks




4
Hi,

Thanks for the template files and sharing the sample code.
After an initial test, I noticed the issue i.e.., "#N/A" error for the formulas as you mentioned by using your template file "test03.xlsx" and sample code.

Sample code:
Workbook workbook = new Workbook(@"C:\temp\spreadsheets\test03.xlsx");
Worksheet sheet1 = workbook.Worksheets[0];

workbook.CalculateFormula(false);
Console.WriteLine("Value: " + sheet1.Cells["E1"].Value);

I have logged a ticket with an id "CELLSNET-41874" for your issue. We will look into your issue to figure it out soon.

For the second file, I don't find any exception when using the CalculateFormula method.

I am using latest version/fix i.e. v7.5.1.2.

Thank you.

Update: With regards to the second spreadsheet, if I run your code with your second file "EMT984.xlsx".


Workbook workbook = new Workbook(@"C:\temp\spreadsheets\EMT984.xlsx");
Worksheet sheet1 = workbook.Worksheets[0];
Worksheet sheet2 = workbook.Worksheets[1];

workbook.CalculateFormula(false);
Console.WriteLine("Value: " + sheet2.Cells["G2"].Value);

Yes, it does displays:
Value: #NAME?
I have re-calculated the formula in G2 in the second sheet in MS Excel manually and it does show the same #NAME? error too.

Thank you.
5

Yes, the exception was thrown when I used 7.5.1.0 . I posted the message, then I upgraded to 7.5.1.2, tested again, no exception was thrown so I updated the message.

What version of Excel did you use? I used Excel 2010 and it works fine. I clicked on the Calculate Now button, I pressed F9 and it is fine.

I am attaching it again just in case there was something wrong.

6

Hi,


Thanks for providing further details and sample file.

Earlier I was using MS Excel 2007. Now I use MS Excel 2010 to evaluate the formula on G2 cell in the second sheet for your second file, it actually gives “happy” for its calculated value. I have also logged the problem to attach with your existing issue “CELLSNET-41874”, we will look into it as well.

Thank you.
7

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: Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial

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);
}
}
}

8

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are afraid, your issue logged as CELLSNET-41874 is not resolved yet. Please spare us some time. Once, it is fixed or we have some other update for you, we will let you know asap.

We were also able to observe this issue with your sample code and source file. The calculated value is 360 for cell D1 but it prints 0 instead. We have logged this issue in our database. We will look into it and fix this issue if possible. Once, the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42038.

9

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issue.

Please download and try this fix: Aspose.Cells for .NET v7.5.3.4 and let us know your feedback.

10

Did you fix both issues or only CELLSNET-42038?

Thanks

11

Hi Costa,

Only CELLSNET-42038 has got resolved with the latest fix version. I am afraid, your other reported issue CELLSNET-41874 is still unresolved with our development team currently working on it. As soon as we receive any updates in this regard, we will post here for your reference.

12

The issues you have found earlier (filed as CELLSNET-42038) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
13

The issues you have found earlier (filed as CELLSNET-41874) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.