Dealing with Excel

Discussion in 'Python' started by Robert Hicks, Oct 18, 2005.

  1. Robert Hicks

    Robert Hicks Guest

    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
     
    Robert Hicks, Oct 18, 2005
    #1
    1. Advertising

  2. Robert Hicks

    Chris Smith Guest

    >>>>> "Robert" == Robert Hicks <> 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
     
    Chris Smith, Oct 18, 2005
    #2
    1. Advertising

  3. Robert Hicks

    McBooCzech Guest

    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
     
    McBooCzech, Oct 18, 2005
    #3
  4. Robert Hicks

    Robert Hicks Guest

    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
     
    Robert Hicks, Oct 18, 2005
    #4
  5. "Robert Hicks" <> wrote in message
    news:...
    > 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
     
    Thomas Bartkus, Oct 18, 2005
    #5
  6. 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
     
    Carl Friedrich Bolz, Oct 18, 2005
    #6
  7. Robert Hicks

    McBooCzech Guest

    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
     
    McBooCzech, Oct 18, 2005
    #7
  8. Robert Hicks

    Robert Hicks Guest

    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
     
    Robert Hicks, Oct 19, 2005
    #8
  9. Robert Hicks

    Peter Hansen Guest

    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
     
    Peter Hansen, Oct 19, 2005
    #9
  10. 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
     
    Francois Lepoutre, Oct 19, 2005
    #10
  11. Robert Hicks

    Larry Bates Guest

    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
    >
     
    Larry Bates, Oct 19, 2005
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jesse
    Replies:
    2
    Views:
    332
  2. Justin Dutoit

    Dealing with Session State timeouts

    Justin Dutoit, Aug 5, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    368
    Steve C. Orr, MCSD
    Aug 5, 2003
  3. SStory
    Replies:
    1
    Views:
    996
    Steve C. Orr [MVP, MCSD]
    Nov 4, 2003
  4. Rohan
    Replies:
    2
    Views:
    330
    Neil Cerutti
    Aug 7, 2007
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,548
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page