XLSX Handling in Sikuli

Asked by Bharathi A

Hi -
I have a project where in:

Step 1: Fetch the value of a CELL from one Excel sheet
status: success
Step 2: Paste that value in another cell (say C30) in another Excel sheet
status: Success
Step 3: Save the updated sheet in xlsx format
status: Not Success

Need help for acheiving Step 3.

I am using xlrd, xlwt and xlutils but with xlrd - I see the file is getting saved as ".xls" format only. I see that if I use openPyXl then .xlsx format can be handled but I guess Sikuli is not supporting openPyXl yet.

Then for experimental purpose I tried the following:

 1a - Save the updated file as .xls
 2a - perfomr explicit renaming of the file using os.rename or shutil

dirPath = r'C:\blah\blah\Ericsson'
old_file = os.path.join(dirPath, "testnew3.xls")

new_file = os.path.join(dirPath, "testnew4.xlsx")
shutil.move(old_file, new_file )

though the renaming gets succeeded the file seems to get corrupted and not getting opened up.

Advice please. TiA !

Question information

Language:
English Edit question
Status:
Answered
For:
SikuliX Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
RaiMan (raimund-hocke) said :
#1

.xls cannot be changed to .xlsx simply by renaming (completely different file format).

If you do not find an appropriate function in xlwt/xlutils, than you should implement a workflow, that loads the xls in Excel and then saves it as xlsx.

Wether openPyXl works with SikuliX has to be tested (if it contains any access to native libs (C-based), then it will not work).

Revision history for this message
Bharathi A (rathisoft) said :
#2

Hi Raiman -
Thnx for the feedback.

I tested openPyXl and it is not not supported in sikuli as of now it seems.

I guess I should implement a workflow, that loads the xls in Excel and then saves it as xlsx.

Will keep the forum updated.

Revision history for this message
Mike (maestro+++) said :
#3

New versions of Excel have enhanced keyboard control. You press ALT and it reveals loads of opportunities.

Below is Office 365 on Windows 10.

#Create an xlsx copy of an xls spreadsheet

def xls2xlsx(xls):

   wdelay = 2
    switchApp("excel")
    wait(wdelay)
    type(Key.ALT) #activate alt keys
    wait(wdelay)
    type("f") # "File"
    wait(wdelay)
    type("o") # "Open"
    wait(wdelay)
    type("o") # "Browse"
    wait(wdelay)
    print "About to print " + xls
    type(xls)
    wait(wdelay)
    type(Key.ENTER) # Should bring up spreadsheet
    wait(wdelay)
    type(Key.ALT) #activate alt keys
    wait(wdelay)
    type("f") # "File"
    wait(wdelay)
    type("a") # "Save As"
    wait(wdelay)
    type("y4") # File Type
    wait(wdelay)
    type(Key.UP) #Select xlsx entry
    type(Key.UP)
    type(Key.UP)
    wait(wdelay)
    type(Key.ENTER)
    wait(wdelay)
    type(Key.ALT) #R Reactivate Alt keys
    wait(wdelay)
    type("a")
    wait(wdelay)
    type("y5") #press save button
    wait(wdelay)
    type(Key.ALT) # Now close sheet
    wait(wdelay)
    type("f")
    wait(wdelay)
    type("c")

xlsheet = "ExcelTest"
xls2xlsx(xlsheet)

(Note this is an example of using a visual interface but not needing any computer vision to do so)

Revision history for this message
Bharathi A (rathisoft) said :
#4

@Mike -
Thanks for the suggestion. I will try the workflow and keep the forum updated.

As a side note.. if Raiman & his core team look into integrating openPyXl or equivalent to that - it will of great addtion to sikuli.

Revision history for this message
Mike (maestro+++) said :
#5

It is an interesting point of discussion.

I would guess that openPyXL doesn't do anything that the Excel UI doesn't. As Sikulix can use all the power of the UI then openPyXL doesn't add functionality.

Sikulix is primarily for automating UIs and not APIs. You could argue that if you wanted to extend Sikulix it might make more sense to focus on UI capabilities - for example adding joystick features.

Revision history for this message
RaiMan (raimund-hocke) said :
#6

@rathisoft
openPyXL does not work with SikuliX, since it internally uses native libraries (accessing xml structures). But if you know, what you are doing, then have a valid Python installation with openPyXL, write your needed scripts for that environment and run them from SikuliX using e.g. subprocess.Popen(). Other tighter integrations are possible with a running Python interpreter as exec server for smaller Python scripts. But I leave this to other people.

@Mike
Thanks for your contribution and the kind words for SikuliX.
In my experience a tighter integration with the C-based Python is one of the most wanted things for SikuliX enhancements, due to the mass of available features and add-ons.
I made some promising experiments with Py4J (https://www.py4j.org/index.html). It principally works, but is not useable as a 1-to-1 replacement for the usage of Jython, since too much internally in SikuliX at the Java level is static (historical reasons) and Py4J can only access attributes/features of instantiated Java objects and nothing at the class level.
But I have it on the list.

Revision history for this message
Hiroyuki Terauchi (hirotochigi) said :
#7

I tried to use openpyxl library, and I found the way to do it. You can use openpyxl if you put openpyxl directory and the depended libraries (et_xmlfile directory and jdcal.py) in C:\Users\[username]\AppData\Roaming\Sikulix\Lib in Windows 10 with SikuliX 1.1.4. However, in my case, if I open the parsed Excel file, openpyxl library does not work.

Can you help with this problem?

Provide an answer of your own, or ask Bharathi A for more information if necessary.

To post a message you must log in.