Place Ghantoos

python: pyExcelerator module very little howto

Tagged:

As a small reminder for myself, I post the thread about pyExcelerator.

pyExcelerator is a nice little python module that let’s you write Excel files (not openOffice spreadsheet, the other kind..).

For Ubuntu users, it is very easy to install: sudo apt-get install python-excelerator

Here basic script to write in ‘Hello World!’ excel file:

#!/usr/bin/env python    

import os
from pyExcelerator import *    

curpath = os.path.dirname(__file__)   

workbook = Workbook()
worksheet = workbook.add_sheet('My colour Sheet')
worksheet.write(0,0, 'Hello World!')
workbook.save(os.path.join(curpath, 'test.xls'))


Now to right in excel with some more fonts and colours, I did this little script that is, of course, not exhaustive. But it’s a start.
In the following script, I create a new font_style function used when writing in the excel file.

To set the column width , i used : (this part is not documented, I got it from pyExcelerator/Column.py)

worksheet.col(0).width = 0x24E1

Here is the script. It writes “Hello World!” in bolded red in the top left corner of test.xls file.

#!/usr/bin/env python   

import os
from pyExcelerator import *   

curpath = os.path.dirname(__file__)   

def font_style(position='left',bold=0, fontos=0) :   

	font = Font()
	font.name = 'Verdana'
	font.bold = bold
	center = Alignment()
	center.horz = Alignment.HORZ_CENTER
	center.vert = Alignment.VERT_CENTER
	left = Alignment()
	left.horz = Alignment.HORZ_LEFT
	left.vert = Alignment.VERT_CENTER
	orient = Alignment()
	orient.orie = Alignment.ORIENTATION_90_CC   

	style = XFStyle()
	if fontos == 'red' :
		font.colour_index = 2
		style.font = font
	else: style.font = font   

	if position == 'center' :
		style.alignment = center
	else : style.alignment = left   

	return style   

if __name__ == "__main__" :   

	workbook = Workbook()
	worksheet = workbook.add_sheet('My Test Sheet')   

	# set colmun width, default size set in pyExcelerator/Column.py
	worksheet.col(0).width = 0x24E1
	worksheet.col(1).width = 0x24E1   

	worksheet.write(0,0, 'Hello World!', font_style('left', 1, 'red'))   

	workbook.save(os.path.join(curpath, 'test.xls'))

One last thing. As you can see in the above script, the colour code for red is 2.
To get the list of colour codes, here is a script that draws a table of colours and their respective colour codes.

#!/usr/bin/env python 

import os
from pyExcelerator import * 

curpath = os.path.dirname(__file__) 

def font_style(position='left', colour='1') : 

	font = Font()
	font.name = 'Verdana'
	font.bold = 0
	style = XFStyle()
	font.colour_index = colour
	style.font = font 

	return style 

if __name__ == "__main__" : 

	workbook = Workbook()
	worksheet = workbook.add_sheet('My colour Sheet') 

	worksheet.write(0,1, 'Colour code')
	for i in range(2,65):
		worksheet.write(i-1,0, 'Hello World!', font_style('left', i))
		worksheet.write(i-1,1, i, font_style('left', i)) 

	workbook.save(os.path.join(curpath, 'colour.xls'))

Hope this was helpful,

cheers,

Ghantoos

Tagged:

5 Responses

  1. Alexander says:

    thanks a lot!

  2. Sanjeevoj says:

    This sounds good. But i feel there is some more to be added like i viewed this page to see alogic to write the vales in an existing Excel(appending).

    That is not in this page may be you can added it that will be useful to me and others

    Thanks
    Sanjeev

  3. @Sanjeev
    You can try combining the xlrd module and pyExcelerator.
    Use xlrd to open and parse your excel file, and reinject everything in a new excel file using pyExcelerator.
    This may be a start: http://code.activestate.com/recipes/483742/

    Cheers,
    ghantoos

  4. ros says:

    that is true… you can read the file using xlrd and write all the data back to a new file… but thats just a hack… is there no way to do it in an append mode or something?

  5. @ros
    I am not aware of anyway to append a file using pyExcelerator. Sorry.

    Cheers,
    Ignace M -ghantoos-

Leave a Reply