pyExcelerator : Python exportar MySQL to Excel

Lets try to create Excel Files using python. There is a Addon for python named pyExcelerator. First you have to install it. Thats easy just go to http://sourceforge.net/projects/pyexcelerator
download pyExcelerator. Unpack it any where. Point folder from terminal as root. then
python setup.py build then type python setup.py install

Now here is a simple code to create an excel file of a table of MySQL database.
To connect with MySQL you need MySQL python its easy to install search google for installing MySQL Python.

My objective is to show you an easy code to create Excel file using python.

The code is given below:
__rev_id__ = """$Id: blanks.py,v 1.2 2005/07/22 08:22:26 rvk Exp $"""
#importing is done here
from pyExcelerator import *
import sys
import MySQLdb

#get data from mysql
table_name = "tareqfolder" #change the table name to the table name from which you want to create xls
sql_select="SELECT * FROM %s"%table_name
conn1 = MySQLdb.connect(host='host_name',user='mysql_user',passwd='secret_passwd',db='database_name') #connection information(change according to your mysql server)
cu_select=conn1.cursor(MySQLdb.cursors.DictCursor)

try:

    cu_select.execute(sql_select)

except MySQLdb.Error, e:

    errInsertSql = "Insert Sql ERROR!! sql is==>%s" %(sql_select)
    sys.exit(errInsertSql)

result_set = cu_select.fetchall()

#creation of excel file starts here
wb = Workbook()
ws0 = wb.add_sheet('0')

borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1


borders_cell = Borders()
borders_cell.right = 1

TestNoPat = Pattern()
TestNoPat.pattern = Pattern.SOLID_PATTERN
TestNoPat.pattern_fore_colour = 0x07

Alg = Alignment()
Alg.horz = Alignment.HORZ_CENTER
Alg.vert = Alignment.VERT_CENTER

row_number=1
for row in result_set:

    i=0
    for item in row:

val=str(row[item])

if row_number==1:

    style = XFStyle()
    style.borders = borders
    style.pattern = TestNoPat
    style.alignment = Alg
    ws0.write(0,i,'', style)
    ws0.write(0,i,item, style)
    style = XFStyle()
    style.borders = borders_cell
    ws0.write(row_number,i,val, style)
    i= i+1

else:

    style = XFStyle()
    style.borders = borders_cell
    ws0.write(row_number,i,val, style)
    i=i+1

row_number=row_number+1

wb.save('%s.xls'%table_name)
#xcel file created

print "SUCCESSFUL see in the directory a %s.xls is created "%table_name

Advertisements
About

Work is fun!

Tagged with: , , ,
Posted in ploneCMS, python
4 comments on “pyExcelerator : Python exportar MySQL to Excel
  1. […] FunBox ← pyExcelerator : Python exportar MySQL to Excel […]

  2. viral says:

    this is realy a helpful. i want to know how to create excel file from postgres from python.

    if u can help.

    Thank you

  3. Kshitiz Gupta says:

    I was able to successfully use the above code to create an excel file. But I get a repeated error that says No more new fonts may be applied to this workbook on opening the excel file. Any hints to solve this

  4. Kshitiz Gupta says:

    Never mind. I got it working. I was creating way too many styles. The trick is to move creation of styles outside of the loop.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Disclaimer
All content provided on this "tareqalam.com" blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner of [tareqalam.wordpress.com] will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
May 2008
M T W T F S S
« Apr   Jun »
 1234
567891011
12131415161718
19202122232425
262728293031  

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 23 other followers

%d bloggers like this: