DATA AHEAD toolkit support Discussions

cancel
Showing results for 
Search instead for 
Did you mean: 

XLR8 - reading raw string data issue

Hi,

when I try to read the sheet content as a raw string I get formulas instead of cell values (see attached test scenario).

It would useful to be able to read the whole excel sheet as a string values (without any formatting for the numbers, etc.), currently the primitive for reading the whole sheet is formatting the data so instead of having value "0", for the cell that is I32, I get "0,0".

Download All
0 Kudos
Message 1 of 5
(5,525 Views)

Hi,

your request is not practical for several reasons:

  • Excel does not distinguish between Integer and Float numbers. In fact all number are floating numbers. Only the cell format let you assume that there is a I32 number.
  • This is especially true for time stamps. Try to change the cell format of a time stamp from date/time to numeric and you will see that Excel internally handles timestamps as floating number.

At least for numbers you can get the desired result by using the VI Read Area (All Types) by the format string %#f for numerics:

image01.png

Regards, Jens

Kudos are welcome...
0 Kudos
Message 2 of 5
(3,969 Views)

I think you misunderstood my point. I do not want excel or the blocks to re-format the data. I want to get exactly the same value I see when I open the excel application.

The solution you propose DOES change the data.

I would like to have the same result as when using the excel automation.

If cell value is "1.0" the XLR8 blocks should return "1.0" and if the cell value is "1" they should return "1".

It works when reading cells as a string but then the cells containing formula instead of a value are not read properly (see attached example from the initial post).

0 Kudos
Message 3 of 5
(3,969 Views)

XLR8 does not intend to recreate every feature that you can achieve via Excel automation. If you want to read a number or the result of a formula from a cell I suggest that you use numeric implementations of the polymorphic VIs we provide for reading cells.

I'll put the idea to hand back the result of a formula instead of the formula itself for the implementation "Read strings" into our feature tracking system but I cannot tell you if we will tackle that idea.

We will definitely not tackle your request to read cells exactly in the same format as they are seen when opening the file in Excel. It would be a very time consuming and slow process to extract all cell formatting infos from the file itself and then apply all these possible formatting infos to each cell - if necessary.

That would completely undermine our goal to provide an easy to use and fast tool.


Regards, Jens

Kudos are welcome...
0 Kudos
Message 4 of 5
(3,969 Views)

Hey GPiotr,

what you are suggesting is a bit more complex than it seems. As Jens wrote, both Excel cell values (float) and their respective formatting have to be taken into consideration to render the Excel view.

This would require a new "read cells with formatting" function that goes through all potential Excel cell formats and translates those into LabVIEW formats. While we can certainly add this to our wish list, I am curious as to what the use-case is? What are you trying to achieve and why is the current behavior not acceptable?

Thanks

Peter

CLD | Alliance Partner | Web data storage and visualization | daq.io
0 Kudos
Message 5 of 5
(3,969 Views)