Re: Dealing with Excel
Robert Hicks wrote: I need to pull data out of Oracle and stuff it into an Excel spreadsheet. What modules have you used to interface with Excel and would you recommend it? Robert http://sourceforge.net/projects/pyexcelerator/ http://sourceforge.net/projects/pyxlwriter/ We use the latter one in the past. As long as your output is plain enough. It's effective and MS-free. The former should be more powerful. Not tested here. Hope this helps Francois -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Using ODBC interface to Oracle Excel can do this without any external (Python) program. Just: 1) Create a ODBC DSN that interfaces with Oracle 2) In Excel do Data-Get External Data-New Database Query 3) Tell Excel what tables/columns/order/filtering you want You can even save the Database Query for later re-use. If you want, you can automate this process using Python COM+ interface to Excel. Larry Bates Robert Hicks wrote: I need to pull data out of Oracle and stuff it into an Excel spreadsheet. What modules have you used to interface with Excel and would you recommend it? Robert -- http://mail.python.org/mailman/listinfo/python-list
Dealing with Excel
I need to pull data out of Oracle and stuff it into an Excel spreadsheet. What modules have you used to interface with Excel and would you recommend it? Robert -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Robert Hicks wrote: I need to pull data out of Oracle and stuff it into an Excel spreadsheet. What modules have you used to interface with Excel and would you recommend it? It is possible to control Excel directly from the Python code (you do not need to write Excel macros within the Excel). It works flawlessly for me. My code goes for example: import win32api from win32com.client import Dispatch xlApp = Dispatch(Excel.Application) xlApp.Visible=0 xlApp.Workbooks.Add() . . === snip === It is helpful to find values of VBA (Visual Basic for Applications) constants on the Internet or in the Excel documentation and this constants values assign as Python constants with the same names as in VBA in the code. For example: xlToLeft = 1 xlToRight = 2 xlUp = 3 xlDown = 4 xlThick = 4 xlThin = 2 xlEdgeBottom=9 Than you can use exactly the same code as in your Excel macros (including formating etc.). === snip === xlApp.Range(xlApp.Selection, xlApp.Selection.End(xlToRight)).Select() xlApp.Range(xlApp.Selection, xlApp.Selection.End(xlDown)).Select() xlApp.Selection.NumberFormat = # ##0 xlApp.Selection.HorizontalAlignment = xlRight xlApp.Selection.IndentLevel = 1 === snip === HTH Petr Jakes -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
I just want to be and maybe I am not reading your response right. I am talking about reading in bunch of rows out of Oracle and writing them to an excel file, not using macros. Robert -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Robert Hicks [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I need to pull data out of Oracle and stuff it into an Excel
spreadsheet. What modules have you used to interface with Excel and
would you recommend it?
What does one use to bind Microsoft libraries to Python?
I think it would be win32com and I confess to not having used it.
Best bet would be to use Microsofts ADODB library together with Excels own
CopyFromRecordset function. Using ADODB, you can easily create a connection
to an Oracle server. You would use this to stuff an ADODB.Recordset object
with query results. Once you have your recordset stuffed with query results
you can pass it to the Excel CopyFromRecordset function:
Worksheets(Whatever).Cells(1,1).CopyFromRecordset {recordset object}
and wham! - You have it in a table on a worksheet.
Thomas Bartkus
--
http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Hi! Robert Hicks wrote: I need to pull data out of Oracle and stuff it into an Excel spreadsheet. What modules have you used to interface with Excel and would you recommend it? if it is enough to produce a file that excel can read (in contrast to a real .xls file), you could use the csv module: http://python.org/doc/2.4.2/lib/module-csv.html Cheers, Carl Friedrich Bolz -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Robert Sorry I was not more clear in my posting. I am solving similar problem as you are. 1) I am getting my data from the Firebird SQL database - directly, using SQL commands (kinterbasdb module), not using ODBC, or ADODB or what ever - some people here can suggest you how to connect directly to the Oracle. 2) In the Python code, I am processing data I have got from the Firebird (I have data stored in the two dimensional list usually) 3) I am setting up the Excel cell range according to the final size of data using Visual Basic for Applications commands for example: rng=xlApp.Range(xlApp.Cells(1,1),xlApp.Cells(len(rw),len(rw[0]))) 4) I am putting data from the Python to the Excel rng.Value=rw 5) I am formatting the data in the Excel worksheet using the VBA code from the Python code and finally I can save it, (it is possible get Excel under the full control from the Python). That's it! I am just a newbie in the Python, so I somebody here can show you different (better) way to go, but above mentioned works for me great. If you are looking for the way how to create (generate) the Excel file directly from the Python, I didn't find it. The only simple way I have found in this discussion group is to save your data separated by semicolons in the file with the .csv extension. Excel will recognize it as an Excel file and open it without problems. Petr Jakes -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
No, I have to format fields and everything sad to say. Another poster up the chain of this posting gave me the nudge in the direction I needed. Thanks all, Robert -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Robert == Robert Hicks [EMAIL PROTECTED] writes: Robert I need to pull data out of Oracle and stuff it into an Robert Excel spreadsheet. What modules have you used to interface Robert with Excel and would you recommend it? Robert Robert For simple enough tasks, I think you can make SQL*plus output HTML, which Excel could suck up directly. For a sripted approach, I would recommend getting an ADODB.Connection object to your Oracle database , opening an ADODB.Recordset against the connection , setting an Excel.Range object to Cell A1 , enumerating your recodset field names into the cells of row 1 , setting the Exel.Range to Cell A2 , using the ridiculously fast CopyFromRecordset method of the Excel.Range to dump the recordset to the sheet. HTH, Chris -- http://mail.python.org/mailman/listinfo/python-list
Re: Dealing with Excel
Robert Hicks wrote: No, I have to format fields and everything sad to say. Another poster up the chain of this posting gave me the nudge in the direction I needed. Doesn't Excel also support (in addition to binary .xls and simple text .csv files) an XML format, which allows full access to formatting and all other such features? I would assume it's reasonably well documented and you could just generate that output directly. -Peter -- http://mail.python.org/mailman/listinfo/python-list
