[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.python

Win32com and Excel

michael.pearmain

1/10/2008 9:21:00 AM

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
2 Answers

Ryan Ginstrom

1/10/2008 9:55:00 AM

0

> 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

1/10/2008 10:04:00 AM

0

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

HTH,
John