Error with #N/A handling

Aspose.Cells Product Family
1

following code fails, but it should simply return me #N/A value ...

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";orders.Cells.CreateRange(

"A1", "A1").Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 120 here");hidden.Cells[

"F10"].Formula = "=ROUND(0.01*ROUNDUP(ROUND(MAX(OPENLEVEL/100,IF(IF(260.51260.51,IF(260.51<OPENLEVEL/100,OPENLEVEL/100,NA()),NA()))/0.01,9),0),9)*100";

orders.Cells["F10"].Formula = "=IF(OPENLEVEL>=hidden!$F$10,\"MOO\",hidden!$F$10)";orders.Cells[

"A1"].PutValue(25967);

//orders.Cells["A1"].PutValue(999999);

workbook.CalculateFormula();

object v = orders.Cells["F10"].Value;

workbook.Save("test8.xls");

This message was posted using Aspose.Live 2 Forum
2

Hi,

Yes we found the invalid casting error. We will figure out the issue soon.

Thank you.

3

Please try this attached version.

4

Does not work.

I am getting "MOO", but I should be getting "#N/A", check it in excel

A.

5
Hi,

No, I don't find the problem. With my testation of your formula (provided here in this thread.), Aspose.Cells calculates it as #N/A which is same as MS Excel.

Testing code:

Workbook workbook = new Workbook();
Worksheet orders = workbook.Worksheets[0];
Worksheet hidden = workbook.Worksheets.Add("hidden");
orders.Name = "orders";orders.Cells.CreateRange("A1", "A1").Name = "OPENLEVEL";
orders.Cells["A1"].PutValue(20);
hidden.Cells["F10"].Formula = "=ROUND(0.01*ROUNDUP(ROUND(MAX(OPENLEVEL/100,IF(IF(260.51260.51,IF(260.51<OPENLEVEL/100,OPENLEVEL/100,NA()),NA()))/0.01,9),0),9)*100";
workbook.CalculateFormula();
MessageBox.Show(hidden.Cells["F10"].Value.ToString()); //got #N/A

Could you provide more details with sample code to reproduce the issue, We will check it soon.

Thank you.

6

you need to check oders sheet (NOT hidden):

object v = orders.Cells["F10"].Value;

7

Hi,

Thanks, we found the problem, we will figure it out soon.

Thank you.

8

any chance you will fix the problem soon ?

9

Please try this attached fix.

10

generated sheed does not have worksheet tabs visible :(

and you removed columns from cells - that was annoying :(
headerRange.Worksheet.Cells.Columns[headerRange.FirstColumn + i].Style.Number = 3; //does not work any more

see attachment

A.

11

For sheet tab issue, we tested with your above sample code and don't find the problem. Could you give me some sample code to reproduce the problem?

We changed style setting routine to minimize memory usage. Please change your code to:

Style style = workbook.Styles[workbook.Styles.Add()];
style.Number = 3;

StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
headerRange.Worksheet.Cells.ApplyColumnStyle(headerRange.FirstColumn + i, style, flag);

12

here is the code:

I am attaching file as well.

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

orders.Cells.CreateRange("A1", "A1").Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 120 here");

//orders.Cells["A1"].SetStyle(

hidden.Cells["F10"].Formula = "=ROUND(0.01*ROUNDUP(ROUND(MAX(OPENLEVEL/100,IF(IF(260.51260.51,IF(260.51<OPENLEVEL/100,OPENLEVEL/100,NA()),NA()))/0.01,9),0),9)*100";

orders.Cells["F10"].Formula = "=IF(OPENLEVEL>=hidden!$F$10,\"MOO\",hidden!$F$10)";

orders.Cells["A1"].PutValue(25967);

//orders.Cells["A1"].PutValue(999999);

workbook.CalculateFormula();

object v1 = hidden.Cells["F10"].Value;

object v2 = orders.Cells["F10"].Value;

workbook.Save("test8.xls");

return;

13

I don’t find the problem with your code. Maybe it’s a problem in previous fix. Please try this attached version.

14

new version is ok - I can see tabs now.

15

you left Style in range - it compiles but has no effect:

my opinion new way of style is very unconfortable to use as it takes 2 times more code to do the same thing. - I don't care about memory usage these days - most pcs has 1-2 gig of ram anyway.

And now I need to change good part of my code - very annoying

License license = new License();

license.SetLicense("Aspose.Cells.lic");

Workbook workbook = new Workbook();

Worksheet orders = workbook.Worksheets[0];

Worksheet hidden = workbook.Worksheets.Add("hidden");

orders.Name = "orders";

Range range = orders.Cells.CreateRange("A1", "A1");

range.Name = "OPENLEVEL";

orders.Cells["A1"].PutValue("enter 120 here");

//compiles but has no effect any more

range.Style = workbook.Styles[workbook.Styles.Add()];

range.Style.Font.IsBold = true;

range.Style.Pattern = BackgroundType.Solid;

range.Style.ForegroundColor = Color.Yellow;

//new code

Style s = workbook.Styles[workbook.Styles.Add()];

s.Font.IsBold = true;

s.Pattern = BackgroundType.Solid;

s.ForegroundColor = Color.Yellow;

StyleFlag sf = new StyleFlag();

sf.CellShading = true;

sf.FontBold = true;

range.ApplyStyle(s,sf);

hidden.Cells["F10"].Formula = "=ROUND(0.01*ROUNDUP(ROUND(MAX(OPENLEVEL/100,IF(IF(260.51260.51,IF(260.51<OPENLEVEL/100,OPENLEVEL/100,NA()),NA()))/0.01,9),0),9)*100";

orders.Cells["F10"].Formula = "=IF(OPENLEVEL>=hidden!$F$10,\"MOO\",hidden!$F$10)";

orders.Cells["A1"].PutValue(25967);

//orders.Cells["A1"].PutValue(999999);

workbook.CalculateFormula();

object v1 = hidden.Cells["F10"].Value;

object v2 = orders.Cells["F10"].Value;

workbook.Save("test8.xls");

return;

16

Hi,

Thanks for pointing it out.

We will figure it out soon.

Thank you.

17

We don't hide Range.Style property in this fix but we will remove it in the future release. So you can remove this part of code:

range.Style = workbook.Styles[workbook.Styles.Add()];

range.Style.Font.IsBold = true;

range.Style.Pattern = BackgroundType.Solid;

range.Style.ForegroundColor = Color.Yellow;

I understand that the code change may be a little annoying however we cannot preserve the old style code.