Win32com and Excel

[John Machin]

On Jan 10, 8:21 pm, Mike P <michael.pearm... at tangozebra.com> wrote:
> 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.

>>> def get_sheet_size(xl_file_name, sheet_index=0):
...   import xlrd
...   book = xlrd.open_workbook(xl_file_name)
...   sheet = book.sheet_by_index(sheet_index)
...   return sheet.nrows, sheet.ncols
...
>>> get_sheet_size('sjm1.xls')
(8, 3)
>>>

http://pypi.python.org/pypi/xlrd

HTH,
John