Cell_ReadByIndex reads blank

Cell_ReadByIndex reads blank

  

I have an excel file that has formulas for computing values with given inputs. After writing the new value in the excel file via code, when i re-open again the binary file, the cell_readbyindex reads blank. but when the file is generated, it contains the computed value. any reason why the cell_readByIndex reads blank value? The excel file comes from the one pointed from the arrow. After modifying some cells, I get the binary data using Workbook_GetBinaryData which is circled below. then the one where is highlighted by rectangle shape, is where I try to read the new value.


Lawrence Arielle . wrote:

I have an excel file that has formulas for computing values with given inputs. After writing the new value in the excel file via code, when i re-open again the binary file, the cell_readbyindex reads blank. but when the file is generated, it contains the computed value. any reason why the cell_readByIndex reads blank value? The excel file comes from the one pointed from the arrow. After modifying some cells, I get the binary data using Workbook_GetBinaryData which is circled below. then the one where is highlighted by rectangle shape, is where I try to read the new value.



Hello Lawrence,

have you checked that the source of Workbook_Open_BinaryData2 should be Workbook_GetBinaryData?

Or can you attach the OML file so I may help to check?

Hi,

thanks for your response. Yes I checked the source of the Workbook_Open_BinaryData2.

unfortunately i cannot attach OML, company rules. However here's my screenshot.



Hi Lawrence,

If you can't attach your working module (perfectly understandable), you can always create a small example to make it available here, that reproduces your error.

It is much easier to work over it. :)

Also, which component from Forge are you using? There are many there. Knowing which one also helps to understand the problem.

(I don't know if all the components have the hability of actually EXECUTE the formulae when reading a cell)

Cheers,
Eduardo Jauch

Lawrence Arielle . wrote:

Hi,

thanks for your response. Yes I checked the source of the Workbook_Open_BinaryData2.

unfortunately i cannot attach OML, company rules. However here's my screenshot.




Hi Lawrence,

attached an OML file for you reference.
I dont know how complex your computation is, so I make a simplest one. In this OML file I'm success to read a cell value, edit it, generate binary data, read the value again, and show it as an expression.

Hi Lady,

I think Lawrence's problem is that the cell it is reading contains a formulae...
And it is not being "executed", so, the value is not being retrieve correctly.

At least was what I understood.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hi Lady,

I think Lawrence's problem is that the cell it is reading contains a formulae...
And it is not being "executed", so, the value is not being retrieve correctly.

At least was what I understood.

Cheers,
Eduardo Jauch


Hi Eduardo,
Thank you for your explanation.

Hi Lawrence,
if it was the case, I'm afraid it is not possible. I think the formula inside the template will only be excecuted when the excel file is generated.

i.e you put in A1 and A2=A1^2.

Then in your code you change A1 value to 5.
You will not get 25 as A2 value unless you generate the excel file.

This is because your formula is stored inside the template, not inside the system.

Lady wrote:

Eduardo Jauch wrote:

Hi Lady,

I think Lawrence's problem is that the cell it is reading contains a formulae...
And it is not being "executed", so, the value is not being retrieve correctly.

At least was what I understood.

Cheers,
Eduardo Jauch


Hi Eduardo,
Thank you for your explanation.

Hi Lawrence,
if it was the case, I'm afraid it is not possible. I think the formula inside the template will only be excecuted when the excel file is generated.

i.e you put in A1 and A2=A1^2.

Then in your code you change A1 value to 5.
You will not get 25 as A2 value unless you generate the excel file.

This is because your formula is stored inside the template, not inside the system.

Hi Lady,

The library used by the Excel Package extension can calculate formulae.
But this is not implemented in the extension, as far as I know.

Cheers,
Eduardo Jauch

EDIT:
And I'm not sure you will get any result even if you generate the excel, as the cell will not have a value, but still the formulae, unless you open the file so that Microsof Excel can evaluate the formulae. Even than, I'm not entirely sure the value is still stored, as this will not be a link to another workbook, but a internal formulae.

Hi All,


 thank you for your replies. After writing some change to the current excel object, I pass the current workbook to the Workbook_GetBinaryData function. I have no issues about that. Also, The value was able to recompute because when I get the edited excel file from the workbook_getbinarydata function and download it as a file from the system, the cell has recomputed the value. 

I am using Excel_Package extension.

Hi Lawrence.

When you download the excel file and open it in Microsoft Excel, the formulae will be evaluated.
So, if this is what you want, there is no problem.

But to get the result of the formulae in OutSystems, the extension must be able to "compute" the formulae, what, I think, this extension is not prepared to do.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hi Lawrence.

When you download the excel file and open it in Microsoft Excel, the formulae will be evaluated.
So, if this is what you want, there is no problem.

But to get the result of the formulae in OutSystems, the extension must be able to "compute" the formulae, what, I think, this extension is not prepared to do.

Cheers,
Eduardo Jauch

Hi Eduardo, 


Actually my main goal is this. My task requires to use an excel file that is computing for some values, I input values on specific cells on the excel file, then retrieve the computed value from a certain cell with computation.


Do you know of any other extensions that can get the result of the formulae in outsystems?


Hi Lawrence.

Sorry, don't know. You can check the other extensions that allow to manipulate excel file to see if one of them can do this.

Cheers,
Eduardo Jauch