Win32com and Excel

[Mike P]

Hi,

I currently have an excel table (1 table each time) that has differing
number of rows and differing number of columns each time, for another
program i use (SPSS) to import the data i need to know the cell range
of this data table.
I.e what the last row of data is and the last column that has data in
it.

Does anyone have any code that does something similar? My guess is i
have to do something like thefollowing to enable python to read xl?

import win32com.client
working_dir = '//c:/temp/'
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = 1 #open MS Excel
xl.Workbooks.Open('%s/working_output.xls' % (working_dir))

then code to find the cell ranges

Any help here is much appreciated

Mike

 

[Ryan Ginstrom]

On Behalf Of Mike P

Does anyone have any code that does something similar? My
guess is i have to do something like thefollowing to enable
python to read xl?

I think that what you want is UsedRange

for row in sheet.UsedRange.Value:
...

Regards,
Ryan Ginstrom

 

[John Machin]

Hi,

I currently have an excel table (1 table each time) that has differing
number of rows and differing number of columns each time, for another
program i use (SPSS) to import the data i need to know the cell range
of this data table.

SPSS is clever enough to be able to read an XLS file, but needs to be
told a precise range of cells, and can't be told "all the cells in the
worksheet"?

I.e what the last row of data is and the last column that has data in
it.

Does anyone have any code that does something similar? My guess is i
have to do something like thefollowing to enable python to read xl?

No, you don't have to something like that at all. See below.

import win32com.client
working_dir = '//c:/temp/'
xl = win32com.client.Dispatch("Excel.Application")
xl.Visible = 1 #open MS Excel
xl.Workbooks.Open('%s/working_output.xls' % (working_dir))

then code to find the cell ranges

Any help here is much appreciated

Assuming that you have only one worksheet in the workbook, the
following will do the trick. Otherwise you'll need book.nsheets plus a
peep at the documentation.
.... import xlrd
.... book = xlrd.open_workbook(xl_file_name)
.... sheet = book.sheet_by_index(sheet_index)
.... return sheet.nrows, sheet.ncols
....
http://pypi.python.org/pypi/xlrd

HTH,
John