Aspose worng behav.. Pls help

Aspose.Cells Product Family
1

If numeric formula has blank values, excel assumes that as 0 and calculate the cell value. But Aspose,

Workbook.CalculateFormula display the cell value as as error.

Also Aspose doesn't work the same way for VLOOKUP and Reference Cells. Please look into this and let me know. This is holding up our prod release.

Seems like all the above issues are related to Workbook.CalculateFormula

Thank you. Tomy

2

Hi Tomy,

Could you please attach your template excel file (containing formulas) here to show the issue, we will check it soon.

Thank you.

3

I did some more research into this issue, and looks like the issue is more related to VLOOKUP functions and Reference Cells. See my attached code and test files. I added more detail comments on the code.

Thank you

Tomy

4

Hi,

After an initial test, I have found the issue you have mentioned. We will soon look into your issue and get back to you.

Your issue has been logged into our issue tracking system with an issue id: CELLSNET-17309.

Thank you.

5

Hi Tomy,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Please try the attached latest version of Aspose.Cells. We have fixed the issue you mentioned.

Thank You & Best Regards,

6

Looks like the issue I mentioned in this post is fixed. But this update (V 4.9.1.8) broke several other formula which uses "VLOOKUP". See the attached test file and program to reproduce the error.

*** This works with V 4.9

Thank you

Tomy

7

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for the feedback.

We will further look into your issue and get back to you soon.

Thank You & Best Regards,

8

Hi,

Please ignore the v4.9.1.8 fix as we found the issue of VLOOKUP function again. Please check the document http://office.microsoft.com/en-us/excel/hp052093351033.aspx about VLOOKUP:

Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

* If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.

* If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.


The Range_Lookup is true or omitted in your template file, so please make sure that the data in the range is sorted properly. The Cell “AL98” formula is “=VLOOKUP(“D29”,Data!A:C,3)”in the worksheet “Insurance Summary”, MS Excel returns the value of “Data!C9” not “Data!C29” .

Which cells do you really want to get? By the way, we will try to work as MS Excel and figure it out soon.

Thanks for your patience.

9

Thanks for the reply. I understand how VLOOKUP function work in Microsoft Excel. Aspose is not working the same way. If you need more samples I am glad to get you some more.

See the code below for a quick test; Excel file attached.

static void Main(string[] args) {

Workbook book = new Workbook();

book.Open(@"C:\temp\500251190 InsSummaryUL June 30, 2010.xls", FileFormatType.Excel97To2003);

book.Settings.CalcMode = CalcModeType.Automatic;

book.Settings.ConvertNumericData = true;

Worksheet sheet2 = book.Worksheets["Per1000"];

string c1 = sheet2.Cells["C1"].StringValue; // Gettiing incorrect value

string c2 = sheet2.Cells["C2"].StringValue; // Gettiing incorrect value

string c3 = sheet2.Cells["C3"].StringValue; // Gettiing incorrect value

/// Try 1

string currdur = book.Worksheets.GetRangeByName("currdur")[0, 0].StringValue; // Getting incorrect value

book.CalculateFormula();

c1 = sheet2.Cells["C1"].StringValue; // Gettiing correct value

c2 = sheet2.Cells["C2"].StringValue; // Gettiing correct value

c3 = sheet2.Cells["C3"].StringValue;// Gettiing correct value

//// Try 2

currdur = book.Worksheets.GetRangeByName("currdur")[0, 0].StringValue; // Returning "#REF"

// Try 3

book.Worksheets.GetRangeByName("currdur")[0, 0].Formula = book.Worksheets.GetRangeByName("currdur")[0, 0].Formula;

//"=VLOOKUP(C1,IF(C3=1,B8:P23,IF(OR(C3=2,C3=3),B27:P41,B45:P53)),15,TRUE)";

book.CalculateFormula();

currdur = book.Worksheets.GetRangeByName("currdur")[0, 0].StringValue; // Returning incorrect value

// Try 4

// Remove the IF condition from the VLOOKUP

book.Worksheets.GetRangeByName("currdur")[0, 0].Formula = "=VLOOKUP(C1,B27:P41,15,TRUE)";

book.CalculateFormula();

currdur = book.Worksheets.GetRangeByName("currdur")[0, 0].StringValue; // Returning correct value

// Summary

// VLLOOKUP function does not work the way it works in EXCEL.

// Atleast in this case not supporting IF condition inside the VLOOKUP

book.Save(@"C:\temp\500251190 InsSummaryUL June 30, 2010.xls", FileFormatType.Excel97To2003);

}

10

Hi,

Thanks for sharing further details, sample code and template file

Please try the attached version/fix v4.9.1.9.

Let us know if everything is fine now.

Thank you.

11

It works perfectly on most cases. But found two issues.

Issue #1: CalculateFormula() This method looks like going in an infinite loop. CPU shoots upto 100%

See Sample Below.

static void Main(string[] args) {

Workbook book = new Workbook();

book.Open(@"C:\temp\500251260 AULDataPageCostDisclosure June 30, 2010.xls", FileFormatType.Excel97To2003);

book.Settings.CalcMode = CalcModeType.Automatic;

book.Settings.ConvertNumericData = true;

book.CalculateFormula(); // This method looks like going in an infinite loop. CPU shoots upto 100%

}

Issue #2: PutValue method is throwing the below error

at Aspose.Cells.Cell.set_x85274cd6fd4e259a(Byte xbcea506a33cf9111)
at Aspose.Cells.Cell.x0cf9b6f209b2f94c()
at Aspose.Cells.Cell.PutValue(String stringValue)
at Aspose.Cells.Cell.PutValue(Object objectValue)

My Code below.

static void Main(string[] args)

{

Workbook book = new Workbook();

book.Open(@"C:\temp\500251180 InsSummaryUL June 30, 2010.xls", FileFormatType.Excel97To2003);

book.Settings.CalcMode = CalcModeType.Automatic;

book.Settings.ConvertNumericData = true;

DataTable dt = ParseData(@"C:\temp\500251180 InsSummaryUL June 30, 2010.txt");

Worksheet dataSheet = book.Worksheets["Data"];

dataSheet.Cells.Clear();

dataSheet.Cells.ImportDataTable(dt, false, 0, 0, dt.Rows.Count, dt.Columns.Count, false, null, true);

book.CalculateFormula();

Worksheet sheet = book.Worksheets["Insurance Summary"];

Range r = sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1);

int maxRows = r.RowCount;

int maxCols = r.ColumnCount;

for (int i = 0; i < maxRows; i++)

{

for (int j = 0; j < maxCols; j++)

{

if (sheet.Cells[i, j].IsFormula)

{

sheet.Cells[i, j].PutValue(sheet.Cells[i, j].Value); // Throwing Error on Cell AM101

}

}

}

}

12

Hi,

Thanks for providing us the template files and sample codes.

I can find the issues you have mentioned. We will look into your mentioned issues and get back to you soon.

Thank you.

13

Hi,

Anything on my issue. This issue is blocking our prod release. Pease let me know.

Thank you, Tomy

14

Hi,

We will try to provide the fix before the end of this week.

Thanks for your understanding!

15

Hi,

Please try the attached version, we have fixed both your mentioned issues.

Kindly let us know if everything is fine now.

Thank you.

16

Thanks for the quick respose. I will run some tests and let you know.

Tomy

17

It worked perfectly. Thanks for the quick response and appreciate your efforts.

Thanks again, Tomy

18

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

While trying to keep the API as straightforward and clear as possible, we have decided to recognize and honor the common development practices of the platform; we have re-arranged API Structure/ Namespaces.

With this release, we have reorganized the API classes for Aspose.Cells component. This change has some major aspects that we follow. We have added new namespaces. The entire API (classes, interfaces, enumerations, structures etc.) were previously located in the Aspose.Cells namespace. Now, certain sets of API have been moved to their relative namespaces, which make the relationship of classes (with their members) and namespaces clear and simplified. It is to be noted here, we have not renamed existing API (classes, enumerations etc.) but, I am afraid you still need to make certain adjustments in your existing projects accordingly.

For complete reference, please see the product's API Reference.