Get Excel cell value

Ask general questions here.
kaancha
Posts: 17
Joined: Wed Oct 22, 2014 4:12 pm

Get Excel cell value

Post by kaancha » Thu Jan 28, 2016 9:27 pm

Hi,
I have an Excel cell value formatted to Currency so it displays as $1,000
But when Ranorex pulls the data , it pulls 1000.
I have used a variable in an object to pull the value from the spreadsheet, which is like this:
//rawtext[@rawtext=$Amount]

The Object returns
//rawtext[@rawtext='1000']

The correct should be :
//rawtext[@rawtext='$1,000']

Is there a way that I can get the Text ( $Amount = $1,000) from the repository level without having to write the user code?

Thanks for your help.

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 3930
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Get Excel cell value

Post by odklizec » Fri Jan 29, 2016 8:33 am

Hi,

Two questions...
What version of Excel are you using? And why are you using rawtexts?

I'm using Excel 2010 and Ranorex seems identify the content of Excel cells without the need of using GDI capture list. It even returns the content of 'currency' formatted cells as displayed in Excel.
rx_excel.png
rx_excel.png (57.58 KiB) Viewed 901 times
So there seems to be no need to use GDI capture (rawtexts). At least if using Excel 2010. So if you are using older Excel, the easiest way to solve your problem is to upgrade. If newer version, then downgrade ;)
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

kaancha
Posts: 17
Joined: Wed Oct 22, 2014 4:12 pm

Re: Get Excel cell value

Post by kaancha » Fri Jan 29, 2016 1:43 pm

Sorry I didn't make my question very clear. I have to use GDI capture objects to validate the MS Access Report.
When I captured it, it was like this :
//rawtext[@rawtext='$1,000']

Now , I am trying to make it data-driven with the Excel file.
The cell value in Excel is CURRENCY formatted and displayed as $1,000.
But when I run it , Ranorex returns the value = 1000

My question was , How can I make Ranorex read the cell value = $1,000 ( as it is being displayed , for example as Text or something like that?)

I hope it helps. Thanks.

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 3930
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Get Excel cell value

Post by odklizec » Fri Jan 29, 2016 1:54 pm

Ah, now I see. I'm afraid, there is probably nothing you can do about this. At least not with Ranorex Excel data connector, which always reads the content of excel cells as "string", regardless of cell format. The easiest solution of your problem would be converting Excel file to CSV file and then use CSV data connector instead.
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Get Excel cell value

Post by krstcs » Fri Jan 29, 2016 3:16 pm

I would agree that you should use CSV files instead of XLS. They can still be edited in Excel, but you don't need Excel installed everywhere.

In addition, I would suggest formatting your Excel column as a TEXT column instead of money. This way the '$' and ',' should be seen as literal text characters when Ranorex reads the data.

As Pavel said, the Ranorex Excel data connector always reads the RESULT of the Excel block as a string. Excel DISPLAYS a formatted string for MONEY, but it actually only stores the value (1000 in your case). Excel returns only the value portion of the text in a money type column, so you only get 1000. If you make the column TEXT and put the '$' & ',' in it, Excel shouldn't strip them out.
Shortcuts usually aren't...