Copy Cells from Excel to Web Page table

Asked by B West on 2010-02-01

I am trying to use Sikuli to copy tabulated values from excel to a similar form on a web page. I believe it would require a loop, but I haven't been able to get Sikuli to recognize the row names to match values in the tables. Have there been any similar scripts completed at this time?

Question information

Language:
English Edit question
Status:
Answered
For:
Sikuli Edit question
Assignee:
No assignee Edit question
Last query:
2010-02-08
Last reply:
2010-02-08
Sergey Darovskih (darovskih) said : #1

If you know rows and columns of needed cells, I'd advice you tu use Key.LEFT, Key.DOWN etc to navigate through cells.

B West (batphreak) said : #2

That works to navigate through the cells in Excel. But I still need to copy each cell to the corresponding on in the explorer window. Here is a screen capture of and example of the 2 tables I'm working with.

http://batphreak.fileave.com/desktop tables.JPG

Sergey Darovskih (darovskih) said : #3

For example, if I need to copy and paste current Excel cell to a notepad, I do

click(<Excel Icon on a taskbar>)
type("c", KEY_CTRL)
openApp("notepad")
type("v", KEY_CTRL)

Can't you use the same approach?

B West (batphreak) said : #4

That works, as long as I click in the proper box on the web page before the "CTRL-V" command. I haven't found a way to get the Sikuli script for find the proper box on the web page to paste into. So far using the above the script automatically advances the active cell, copies it to the clipboard, then activates my explorer page and waits 3 seconds for me to click the proper input box.

RaiMan (raimund-hocke) said : #5

@BWest: the correct link to your screenshot: http://batphreak.fileave.com/desktop%20tables.JPG

if you have to do it from time to time, your solution should be sufficient.

If you have to do it every day, some effort may be justified.

The solution would be, to "calculate" the fieldposition in IE based on where you are in the XLS.
1. more general: each field has a pixelposition within the region that is occupied by the table. Regions in Sikuli are defined by the screen-absolute (x,y) of the upper-left corner an their w(idth) an h(eigth). So if you know the position and dimension of a clickable area (one of your fields in IE e.g) on the screen, you can use click(capture(x, y, w, h)). After this you have the focus and be able to type().
2. in your special case you could use findAll with the little icons on the right side of your fields in IE. With find.regions you have access to every match (= field). every match knows his position on the screen. the rest should work like in 1.
(look: https://answers.launchpad.net/sikuli/+question/98832)

With this approach, you could automate to copy/paste part of or whole table with one Sikuli-script.

Achallenge will be the fact, that not the whole table is on the screen (as it looks like on your screenshot)

B West (batphreak) said : #6

This is a task I need to do quite often, so coming up with a faster solution using a loop would help a lot. I have still been working on this off and on. My latest attempt, based on teh advice here I've been trying to use findAll and run through each instance. I have a screen capture of the icon on the right of each field, plus the empty field on the left so that it only finds empty fields and not ones that are already full. Right now I get this error, though:

TypeError: findAll(): 1st arg can't be coerced to String

I have been looking for information on how Sikuli or Python will store an array like this findAll. To run a loop like this and insure that it matches each cell in excel with the proper cell in the web page I should know how the found instances are stored. Does the script just store 0 - n with 0 on the top of the screen and n at the bottom? Or does it store them in order of Sikuli finding them? For the second case I would have to know what order they were found so that the proper excel cell could be matched.

for x in findAll( ):
   click( )
   sleep(1)
   type(Key.DOWN)
   sleep(1)
   type("c", KEY_CTRL)
   sleep(1)
   switchApp("Internet Explorer")
   click(x)
   type("v", KEY_CTRL)

RaiMan (raimund-hocke) said : #7

I guess, in the findAll() you have inserted a picture and then checked the search by clicking on that picture in the IDE. If you then use the similarity-slider, the picture is converted internally into a Pattern (the similarity value is reported in the upper right corner).

There is a bug already reported: https://bugs.launchpad.net/sikuli/+bug/517702 (findAll not accepting patterns)

Please go there and say you are affected (upper left).

So at the moment you have to use an image as such.

The search result concerning the sequence of the matches can be controlled with the search-check (click on the image) by starting with a "Number of Matches" of 1 and going up. The experience with your example (I used your screenshot) was, that sequence has nothing to do with what you see on the screen - its only about similarity.

try this:
p = ...image to find...
x = findAll(p)
for m in x

RaiMan (raimund-hocke) said : #8

sorry, pressed the wrong key .....

try this:
p = ...image to find...
x = findAll(p)
for m in x
    print m

and in the message area you can see the results with their position and simularity

RaiMan (raimund-hocke) said : #9

Back to your app:

if you have "for x in findAll()" every x is a Match object, that can tell you its position and dimension (see my comment above from 2010-02-03). With this knowledge about a cell, you can calculate what you need as a reference to the excel-field.

In the IE you need a reference point of your table and in the XLS you need a starting point from where you do your move/select by keyboard. OK, you have to calculate a little bit, but it should work.

B West (batphreak) said : #10

I think I see now, I should be able to use the Match object to find the first cell that I wan to fill on the web page, then in each iteration of the loop I can have an add that jumps the position a fixed distance that should allow a click event on the next cell. As long as my spreadsheet starts in the proper cell it should still be a simple arrow down for each iteration.

RaiMan (raimund-hocke) said : #11

On top you should work fullscreen. this would give you the chance to analyse the whole situation once and fix some pos and dim,
you need for calculations. You would be able to restrict the find() and findAll() (does not work in the moment - I report a bug) to subregions, what speeds up performance significantly und assures, that you only have matches you are really looking for.

e.g.
tab = Subregion(x, y, w, h) # has pos and dim of whole table, can be captured with the IDE (button third from top left)
col1 = Subregion(x,y,w,h) # first Column
... and so on

since you work fullscreen, pos and dim for these elements are always the same, when your app runs.

you get pos and dim by tab.x, tab.y, tab.w, tab.h
if you add your knowledge about cols and rows by saying
cols = 3
rows = 20
you should be able to make a nice little app.

Can you help with this problem?

Provide an answer of your own, or ask B West for more information if necessary.

To post a message you must log in.