Hello,
We are generating .xls worklist files using python - and we are unable to open the file in Venus before the Excel file has been opened and closed in Excel (even without saving). Does anyone know how to fix this issue?
Are you closing the Workbook/File in the python script after writing to the file?
Not really sure what to answer 
But we have made a generic code snippet of what the python script does (see below). The xls output file is not recognized/parsed, when using the Column Specification Helper in the “File Open - New” step:
But after opening and closing the output file in Excel, the file format is recognized/parsed:
Code snippet:
import pandas as pd
import xlwt
data1 = {
‘Name’: [‘Alice’, ‘Bob’, ‘Charlie’],
‘Age’: [25, 30, 35],
‘City’: [‘New York’, ‘Los Angeles’, ‘Chicago’]
}
df1 = pd.DataFrame(data1)
df1.to_excel(‘my_worklist.xls’, engine=‘xlwt’, index = False)
try exchanging xlwt with openpyxl. alternatively, push the data from pandas to csv and read that. both should work.
best
Dominik
Thanks for the suggestions.
Exchanging xlwt with openpyxl gave rise to the following error when opening the Excel file (see code snippet below):
We would like to keep using Excel sheets/files for our worklists - so the csv format is not our preferred option.
Code snippet:
import pandas as pd
import openpyxl
data1 = {
“Name”: [“Alice”, “Bob”, “Charlie”],
“Age”: [25, 30, 35],
“City”: [“New York”, “Los Angeles”, “Chicago”]
}
df1 = pd.DataFrame(data1)
df1.to_excel(“my_worklist2.xls”, engine=“openpyxl”, index = False)
Have you tried saving it as .xlsx and opening it anyways with VENUS? At least for my installation that works fine.
Can you share how you open an .xlsx file in VENUS? And were you able to reproduce my problem with the .xls file?
Uh sorry, I remembered it wrong. What I do is open xlsx via VENUS, do whatever modification is needed for that run, than transfor this to csv for ingesting it into VENUS. That way you have identical worklist in .xlsx and csv. Some overhead, but should work. Why does it have to be .xls?
As far as I know, xls in the only Excel file format that is compatible with VENUS (at least in ver4.5). That is why we are stuck with xls and not xlsx. How do you open xlsx files in VENUS?
Hi there,
you can run any program via shell (CMD) as long as it is registered in Path.
Then, after the modification and saving, you can check if the command sends exit code 1 and after verification run your python script that generates the csv.
Thanks - but this is not really the kind of solution I am looking for. I would like to be able to open and read the generated xls file directly in VENUS.
Anyone from the Hamilton team who is able to help with this isue? For example, @EricSindelar_Hamilton, @NickHealy_Hamilton?
Hello,
While I do not know the ins and outs of your python script, Venus should be able to open an excel file with ease. In fact, within the realm of Venus you should be able to open up an excel file, leave it open, and then be able to open it up again. My hunch is that somehow the file is still open with a “locked for editing” feature enabled when you go to access it in Venus.
So I wonder what would happen if in Venus you used the HSL extensions file library to make a copy of the file you need (to somewhere within the Hamilton folder perhaps) and then used file handling to read that copy of the file.
Matt
Hi Matthew. Thanks for the suggestion. I have not tried your suggestion - but ended up using the HSL_XLS_Converter library suggested by my colleague @benjaminwohl.
Our python script now generates xlsx files and the HSL_XLS_Converter library converts the xlsx file to xls - and then the xls file is being opened and read by VENUS. It is a bit slow (takes 5 seconds per worklist file) - but it works.
Long term solution: We plan to upgrade to VENUS 6 soon which is compatible with xlsx:
