Friday, May 29, 2009

Using Excel through Python for ArcGIS Desktop

It makes sense. Seamless integration between different pieces of software. Excel is a widely accepted spreadsheet format, and even those horrible Mac user even use.

To use the python module, first you need to create a python script, call it excel.py.

Import the win32com.client library, so you can consume the Excel.Application library.

import win32com.client
xlApp = win32com.client.Dispatch("Excel.Application")

You now have your meat hooks in excel, and you can perform almost any command. If you need API references, go to the msdn website and browse the excel libraries.

To make you excel spreadsheet visible, set the Visible property to 1, which means true. When you are finished testing, I highly recommend that you set the value to 0, which is false. The users really don't want to see the data populate.

xlApp.Visible=1

Next you need to create a worksheet, so you can store information.

xlApp.Workbooks.Add()

Reference to your sheet by name, you can find the name of the sheet by referring to the bottom tabs on the xlsx file. By default the first sheet is called 'Sheet1'.

cell = xlApp.Worksheets("Sheet1")

Now we can put information into our spreadsheet. Just reference the cell number using Range, and the insert the value using the Value method

cell.Range("C3").Value = "groovy"

This will but the word 'groovy' into cell C3 in sheet 1.
Exiting excel can be sightly annoying. You simply just can't quit because your python script will hang if the visibility is turned off. So to counter this problem you must use the SaveAs command along with saying upon exiting, do not save the changes I already did it.

filename = r'C:\mySpreadSheet.xlsx'
xlApp.ActiveWorkbook.SaveAs(filename)
xlApp.ActiveWorkbook.Close(SaveChanges=0)
xlApp.Quit()


Wham-o you now have a python made spreadsheet that can be viewed in ArcGIS Desktop.

1 comment:

Michalis Avraam said...

Good and informative article.

I have a post on my blog on how to read Excel files using Python, without the use of the Excel APIs (meaning, reading the files using pure Python code). I do want to write an entry about writing them too, without Excel (one day soon hopefully).

Michalis

PS: To read the files: http://www.michalisavraam.org/blog/38-blog-entries/57-manipulating-excel-files-in-python-no-excel-needed.html