ExportArray not returning the formula value

Aspose.Cells Product Family
1

Hi,

the Worksheet.Cells.ExportArry function doesnt return the proper value

Range rg = ws.Cells.CreateRange("l2", "P2");

System.Data.DataTable dt = ws.Cells.ExportDataTable(rg.FirstRow, rg.FirstColumn, rg.RowCount, rg.ColumnCount);

objArray = ws.Cells.ExportArray(rg.FirstRow, rg.FirstColumn, rg.RowCount, rg.ColumnCount);

it returns "#N/A"

2

can any on help on this then

3

Hi Saravanan,

Thanks for considering Aspose products.

Your query is more related to Aspose.Cells and I am moving your query to Aspose.Cells forum, so that you may be provided appropriate technical help.

Thanks and Regards,

4

Hi Saravanan,

Well, ExportArray works fine as I tested. For your information, Cells.ExportArray will not export formula string but the calculated or output values or data in the cells against formulas would be exported. Also, I think you should call Workbook.CalculateFormula() method just before using Cells.ExportArray() in your code as I think it might be possible you have changed the values in the cells against the formulas, so you need to call the method.

If you still find the issue, kindly post your template file here, we will check it soon.

Thank you.

5

Hi Amjad,

Thank you for your timely support, I found the problem, this is because of the formula calculation is taking so long to process, it is giving the value befor calculation has been happend. If I use workbook.caculateformula() method it is taking log time to process so can you suggest me any other solution to workaround.

Thank you,

Regards,

Saravanan V.

6

Hi,

Please post your sample template file here, we want to check why the Workbook.CalculateFormula is taking more time.

Thank you.

7

Hi Amjad,

I havt attaced the excel file for your reference.

Thank you,

8

Please ignore the previous file i have attached the value added file. I am trying to get the value form M2:P2 cell it is not given as it is in the excel.

Thank you,

Regards,

Saravanan V.

9

Hi,

Thanks for providing the template file.

We have found the issue after an initial test regarding the calculations for the formulas.

We will figure it out soon.

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

Thank you.

10

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

Please try the attached latest version of Aspose.Cells. We have fixed the calculating issues.<?xml:namespace prefix = u1 /> After checking the performance issue, there are many formulas which contains SUM(A:A) in the Column X,Y in the worksheet “Measure Units”. Please cache the summary of a column to cell.

For example:

The Cell BA1 formula =SUM(V:V), the Cell BB1 formula =SUM(W:W), the formula in the column X can simply be =IF(BA1>BB1,W2,V2), then SUM(V:V) will be done only once. It will improve the performance.


Thank You & Best Regards,

11

Thank you very much,

I got the license.