Writing TO Excel file

[tzushky]

Hello, long time no see...

I've been getting along fine with Python lately and I just need to ask you a question:

Is it possible to just write to, NOT OVER , an Excel file, in a certain area, using a certain python module?. This way results from an application could be inserted in already existing files and the summum of all the results evaluated in the same worksheet (write them in column C row X of Sheet 1 and get automatic graph in Sheet2, etc...)

Can we do that?

Thanks in adavnce,

T

[jlm699]

Have you looked into pyExcelerator at all? I'd say it's worth a shot.
link

[tzushky]

From what I've been reading, pyExcelerator may allow you to CREATE and format Excel workbooks as you like, but I believe It doesn't allow to open an existing one in order to complete certain cells....Am I wrong?...

[jlm699]

I don't know; as I've never used it. If that does not suit your needs you'll possibly need to look into the Excel API; which can be access via win32com (you'll need to learn how to use COM objects).

[tzushky]

That's what I finally did, it's pretty fun actually, look:

from win32com.client import Dispatch
import os

#file path
file_name = 'D:\\dev\\PyWork\\ExcelWrite\\SensiTest.xls'

#The win32com function to open Excel
excel           = Dispatch('Excel.Application')
excel.Visible   = True  #If we want to see it change, it's fun

#Open the file we want in Excel
workbook        = excel.Workbooks.Open(file_name)

#Extract some of the file's components we may need
workBook    = excel.ActiveWorkbook
activeSheet = excel.ActiveSheet
sheets      = workBook.Sheets

#Add another sheet for example
sheets.Add(None, sheets('Sheet3')).Name = 'MySheetx'

#Activate the necessary sheet, assuming we know it exists, 
#didn't put in the test for that, just verify its exitance in 'sheets'...
sheet = sheets('The_Sheet_I_Need')
sheet.Activate()

#Write smth in  a Cell
line = 4
col  = 5
sheet.Cells(line,col).Value = 99

#Read something is just as easy
print sheet.Cells(line,col).Value

#The remove doesn't work for me, but I simply save as different file...
#I just put the lines for you to see what I had found...
#if os.path.exists(file_name):
    #os.remove(file_name)
workBook.SaveAs('D:\\dev\\PyWork\\ExcelWrite\\SensiTest_modif.xls')#change name

#The end...
workBook.Saved = 0 #p.248 Using VBA 5
workBook.Close(SaveChanges=0) #to avoid prompt
excel.Quit()
excel.Visible = 0 
#must make Visible=0 before del self.excelapp or EXCEL.EXE remains in memory.

del excel

It works like a charm, I don't even need difficult things anyway, formulas an stuff like that...I only needed this, to be able to pass some values I recover from some automation tests into an already existing fixed format file, in which I know the sheet, the lines and columns I need to fill...

win32com solves it then:)...

Thanks for your patience on this one too;)...

[Gribouillis]

Hello. The problem with your solution is that it works only on windows. I don't know if a portable solution exists. It should be possible to use the python interface of openoffice.org for such a task (but the ooo api is quite complicated). A starting point could be http://wiki.services.openoffice.org/wiki/Extensions_development_python :)

[jlm699]

Hello. The problem with your solution is that it works only on windows.

While that may be true you have to consider this: If you're using excel it's almost a guarantee that it's in a windows environment anyway.

I don't know why anybody on a Mac or *nix box (in their right mind) would use Excel...

[tzushky]

Thanks for your insights... I really wasn't designing it for portability, whatever tests w i do are for me...

[Gribouillis]

Hello again,
I found this link http://www.stuvel.eu/ooo-python which shows how you can open and modify a spreadsheet with ooo python interface. Note that the spreadsheet can be an ooo file or an excel file.