Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Dealing with Excel

Reply
Thread Tools

Dealing with Excel

 
 
Robert Hicks
Guest
Posts: n/a
 
      10-18-2005
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

 
Reply With Quote
 
 
 
 
Chris Smith
Guest
Posts: n/a
 
      10-18-2005
>>>>> "Robert" == Robert Hicks <(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
 
McBooCzech
Guest
Posts: n/a
 
      10-18-2005
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

 
Reply With Quote
 
Robert Hicks
Guest
Posts: n/a
 
      10-18-2005
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

 
Reply With Quote
 
Thomas Bartkus
Guest
Posts: n/a
 
      10-18-2005
"Robert Hicks" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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).CopyFromRecordse t {recordset object}

and wham! - You have it in a table on a worksheet.

Thomas Bartkus




 
Reply With Quote
 
Carl Friedrich Bolz
Guest
Posts: n/a
 
      10-18-2005
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

 
Reply With Quote
 
McBooCzech
Guest
Posts: n/a
 
      10-18-2005
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(r w),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

 
Reply With Quote
 
Robert Hicks
Guest
Posts: n/a
 
      10-18-2005
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

 
Reply With Quote
 
Peter Hansen
Guest
Posts: n/a
 
      10-19-2005
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
 
Reply With Quote
 
Francois Lepoutre
Guest
Posts: n/a
 
      10-19-2005
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
Dealing with multiple excel sheets Rohan Python 2 08-07-2007 06:31 PM
OT: Tact When Dealing With Physical Deformitties Stoourt MCSE 12 08-06-2004 04:42 PM
Fustration in dealing with VUE MCSE 7 01-13-2004 07:29 AM
Re: Dealing with the BEAST "70-216" Jesse MCSE 8 07-25-2003 10:58 PM



Advertisments