Free Support Forum - aspose.com

Issue with formulas referencing other worksheets

Greetings,

I've finally been able to get back to working on the project with the Aspose.Grid in it. I tested out v1.7.1.10, and it appears to work as expected. The shift/mouse click was doing what I was expecting. It should make the client a little happier.

I've run into an issue with formulas that contain references to other worksheets. For example, my excel file contains two worksheets, "TestA" and "TestB". In TestA, the cell at A1 contains the value "This is a test.". In TestB, there is a cell that contains the formula ='TestA'!A1. In Excel, the value of the cell in TestB is "This is a test.", but when I load it in the Aspose.Grid, the cell remains blank.

I was looking through the Aspose.Grid Wiki (good job on the update by the way), and couldn't find anything about this. Does the Aspose.Grid currently support this kind of functionality, or are there any plans to add it?

Thanks,
Chris Powell

Hi Chris,

The feature of referencing to other worksheets is always supported. And I tested the feature by two ways:

gridDesktop1.Worksheets[0].Name = "TestA";
gridDesktop1.Worksheets.Add("TestB");
Aspose.Grid.Desktop.Worksheet sheet = gridDesktop1.Worksheets[0];
sheet.Cells["a1"].Value = "This is a test.";
gridDesktop1.Worksheets[1].Cells["a1"].Value = "='TestA'!A1";

And

gridDesktop1.ImportExcelFile(@"c:\book1.xls");

The book1.xls contains the contents of you said.

They work fine.

Could you please post your excel file to me?

Thanks

Greetings,

Apparently the issue is a little more complicated than the simple case I outlined.

I've attached an Excel file that I've been able to reproduce the issue with.

When I first tried this out, I just had the Test1 and Test2 worksheets. At that point, the formula in Test2!A6 worked fine. Then I added the '138' worksheet, and none of the formulas on Test2 worked correctly, when loaded into the Aspose.Grid control that is. The worksheet loads without any problem in Excel. When the excel file is loaded in the Aspose.Grid, the following messages are displayed in the Visual Studio output window. No exceptions are thrown however.

Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Divide by zero.
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Wrong number value.
Divide by zero.
: unexpected AST node: AJ101:AJ102
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Divide by zero.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Divide by zero.
Divide by zero.
Wrong number value.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
: unexpected AST node: AI90:AI91
Object reference not set to an instance of an object.
Wrong number value.
Wrong number value.
Wrong number value.
Wrong number value.
Wrong number value.
Wrong number value.
Wrong number value.
Function not found.
Function not found.
Wrong number value.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
: unexpected AST node: G43:G45
Object reference not set to an instance of an object.
Wrong number value.
Wrong number value.
Object reference not set to an instance of an object.
: unexpected AST node: F43:F45
Divide by zero.
Wrong number value.
Wrong number value.
Wrong number value.
Wrong number value.
Wrong number value.
Object reference not set to an instance of an object.
Wrong number value.
Object reference not set to an instance of an object.
Wrong number value.
Wrong number value.
Wrong number value.
Object reference not set to an instance of an object.
: unexpected AST node: AI80:AI81
: unexpected AST node: AI101:AI102
: unexpected AST node: J43:J45
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Object reference not set to an instance of an object.
Function not found.
Wrong number value.
Divide by zero.
Wrong number value.
Wrong number value.
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Wrong number value.
: unexpected AST node: AJ80:AJ81
: unexpected AST node: AD80:AD82
: unexpected AST node: AE80:AE82
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Wrong number value.
Wrong number value.
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Wrong number value.
: unexpected AST node: AJ90:AJ91
: unexpected AST node: AD90:AD92
: unexpected AST node: AE90:AE92
Divide by zero.
Wrong number value.
Divide by zero.
Wrong number value.
Wrong number value.
Wrong number value.
: unexpected AST node: AD101:AD103
: unexpected AST node: AE101:AE103
Function not found.
Function not found.
Function not found.
Function not found.
Function not found.
Function not found.

Hopefully this helps you track down the issue.

Thanks,

Chris Powell

Hi Chris,

I fixed the bug. Please try v1.7.2.1 in attachment.

And our formulas don't support referencing the other excel file. So some formulas' values could not be calculated.

Greetings,

Opps, that would be my fault. I thought I had gone through it and removed all those references. My apologies.

The last version you sent me seems to work a bit better. I'm getting more values returned. I believe that the problem that I'm facing now is that the client is using the VLOOKUP function, and it appears the Aspose.Grid control doesn't support this function.

Are there any plans to support this function anytime in the near future?

Thanks,
Chris Powell

Hi Chris,

I am going to support the "Extend no wrap text" and "Zoom" features. And then to support "vlookup" function.

Greetings,

Do you have a time frame on that? I'm going to need something to tell my project manager and the client. Because our application is basically useless without the vlookup function being implemented. There is no way the client is going to change 500+ Excel files to not use the vlookup function.

Thanks,

Chris Powell

Hi Chris,

I will implement the vlookup function in a week.

Hi Chris,

I implemented the vlookup function in v1.7.3. This version will be released today. Please download and try it.

Greetings,

I just tested out v1.7.3.0. The vlookup function appears to be working correctly. But there are still functions that are either not working as expected or not supported. For example, AVERAGE was returning a divide by zero error, and STDEV and AVERAGEA is not supported.

It would be very helpful if the "Function not supported" error message contained the name of the function that isn't supported. Something like "Function not supported: STDEV".

And the control is still running painfully slow in debug mode in VS2003, especially when it is loading a spreadsheet with functions that aren't supported. The last spreadsheet I tried to load took over 20 minutes to finally finish loading. This makes debugging the application rather difficult and extremely time consuming.

Thanks,

Chris Powell

Greetings,

I've done a little more testing with v1.7.3.0. I noticed an issue with the rotated text. In excel on the spreadsheet that I was working with, there were several cells where the text had been rotated 90 degrees to the left. ie instead of reading left to right, it read bottom to top. When I load the spreadsheet into the Aspose.Grid control, the text is rotated 90 degrees to the right, ie it reads top to bottom.

Its better than it was before and it keeps its formating much better, its just not rotated in the direction I was expecting.

Thanks,

Chris Powell

Hi Chris,

I implemented the rotation feature of 90 degrees and -90 degrees reversedly. I will fix it soon.

The AVERAGE will return a divide by zero error, when there is none value in the range of the parameters. The result is same as in Excel. Please check your AVERAGE parameters.

I will implement the STDEV and AVERAGEA functions after Extend no wrap text. I want to spend one or two weeks.

Thanks

Greetings,

Haven't heard anything for a while. Just wondering how the changes are coming along.

Thanks,

Chris Powell

Hi Chris,

I had fixed the rotation problem, and I am implementing Extend no wrap text feature. It is complex. I will release it in next week. And then I will implement STDEV and AVERAGEA functions.

Thanks

Hi Chris,

We will release v1.7.4 soon. And in this version:

1.Fixed the bug of text direction when rotating 90 or -90 degrees.
2.Extend no wrap text supported.
3.Stdev, averagea functions supported.

Please try it.

Thanks

Greetings,

I downloaded and installed v1.7.4 this morning. I haven't had a chance to fully test the new functions yet, but I have found a small issue with the Extend No Wrap text functionality.

I have attached a screenshot of the issue. I ran the grid first with formula's turned off. The grid appeared as I thought it should. Then I ran the grid with formula's turned on. The grid appeared as in the screenshot. Note the cells that I've outlined in Red. They should appear in the areas that I've outlined in blue. Its like the grid removed all the "blank" cells in between populated cells. Note that the blank cells that were removed were populated with formulas that returned "".

Interestingly, when I click on the cells that are appearing in the wrong spot, I don't get the value of the cell. But when I click on the cell where they should actually appear, the correct value is returned by the grid. So this appears to be just an appearance issue, rather than the actual cells moving around.

You'll notice that there are still formula errors in the spreadsheet. I'm trying to trace down what the issue is with those cells and what formulas they are using.

Thanks,

Chris Powell

Hi Chris,

Thank you for testing. I fixed the bug, please try the v1.7.4.1 in attachment.

Greetings,

I did some testing on v1.7.4.1, and the formatting issues that I was experiencing have gone away. Good job.

I think I found the formula that is causing me issues now. The cell that is causing the problems contains:

=INDEX($AQ$1:$AR$6,(CELL("CONTENTS",Y168))+1,2)

I don't know if its the INDEX function or the CELL function that is causing the issue yet. I couldn't see either of these functions listed in your list of supported functions though. Do you have any plans to support them in the future?

Thanks,
Chris Powell

Hi Chris,

I will implement the "index" and "cell" functions in Oct. Could you please check your excel files to find the other unsupported functions out.

Thanks

Greetings,



I’ve been trying to trace down as many unsupported
functions as I can. Its just that our client has several hundred
different types of spreadsheets, and there are probably over 10,000
instances of these spreadsheets. I’ve only been able to get my
hands on about 25 types of spreadsheet so far. What formulas are
used on the other spreadsheets is hard to say, as they are all
different, and all created by civil engineers, so lord knows what kind
of exotic functions they are going to find and use.



I’m hoping that there won’t be many more unsuppored
functions being used in their spreadsheets, but as the client has been
rather slow in getting us the different spreadsheets to test with, its
hard to guarantee that we won’t run into any more.



Thanks,

Chris Powell