Friday, October 12, 2007

Create Excel file with Python: My sort manual for pyExcelerator

For the needs of my work I had to write a python script that will create an spreadsheet document (Excel type) that would work in any platform. The library I have used is called pyExcelerator. I could find no manual for it, except the examples provided in the installation file, and a few examples from other programmers in the web, but nothing structured as a manual. So here is my attempt to help people start using it:

1. Initialisation phase

# Import the library *
import pyExcelerator

# Create a new workbook to work on *
workBookDocument = Workbook()

# The workbooks is empty, so you have to add a sheet *
docSheet1 = workBookDocument.add_sheet("sheet1")

* These commands are necessary for writing any python script

2. Font functions

# Create a font object *
myFont = Font()

# Change the font
myFont.name = 'Times New Roman'

# Make the font bold, underlined and italic
myFont.bold = True
myFont.underline = True
myFont.italic = True

# To strike through the text
myFont.struck_out = True

# To add green color to the font object
myFont.colour_index = 3
# Other colors: 1- White, 2- Red, 3- Green, 4- Blue, 5- Yellow, 6- Magenta, 7- Cyan

# To outline the font
myFont.outline = True

# the font should be transformed to style *
myFontStyle = XFStyle()
myFontStyle.font = myFont

# if you wish to apply a specific style to a specific row you can use the following command
docSheet1.row(0).set_style(myFontStyle)

* These commands are necessary for writing a python script when using a font object


3. Write Data

# Write a value “name” in the cell 0, 0 (default font Arial 10) on the sheet “docSheet1” *
docSheet1.write(0, 0, 'value')

# Write a value “name” in 0,0 cell with style “myFontStyle *
docSheet1.write(0, 0, 'name', myFontStyle)

# Save the file with any name *
workBookDocument.save('fileName.xls')

* All these commands are necessary for writing any python script

4. Cell Functions

4.1. Height

# To change the height or width you should use the
# the style to update a row after writing the data. Increase by at
# least 30 to see difference.
# If the cell has been written on the sheet with a specific style, you
# need to create a new font and style from the beginning to update
# it, because otherwise you change the size of the characters
# instead of the cell

myFont.height=120

# now you need to apply this style to the row you want to change height

4.2 Width

# Change the width (add a few hundreds to see difference in size)
docSheet1.col(i).width = 0x0d00 + 10

# Freeze 1 row as a pane
docSheet1.panes_frozen = True
docSheet1.horz_split_pos = 1

# Freeze 1 column as a pane – they can be combined as well
docSheet1.panes_frozen = True
docSheet1.vert_split_pos = 1

4.3 Borders

borders = Borders()
borders.left = i
borders.right = i
borders.top = i
borders.bottom = i
# where “i” an integer e.g. 6

# apply bordes to the Style
myFontStyle.borders = borders


4.4 Alignment

# Alignment Horizontally and Vertically Center
align1 = Alignment()
align1.horz = Alignment.HORZ_CENTER
align1.vert = Alignment.VERT_CENTER

# Alignment Horizontally Right and Vertically Bottom
align2 = Alignment()
align2.horz = Alignment.HORZ_RIGHT
align2.vert = Alignment.VERT_BOTTOM


# Alignment Horizontally Leftand Vertically Top
align3 = Alignment()
align3.horz = Alignment.HORZ_LEFT
align3.vert = Alignment.VERT_TOP

# And then we have to apply it on the style
myStyleFont.alignment = alignX

4.5 Merge cells

docSheet1.write_merge(lineStart, lineFinish, columnStart, columnFinish, 'text', style)

i.e. The command mySheet.write_merge(3, 3, 1, 5, 'test1', style)
merges 5 cells from column 1 to 5, on the 3rd
line of the sheet, with text test1

4.6 Format numbers

# To apply a specific format in a cell, you have to apply it on the Style
myStyleFont.num_format_str = 'M/D/YY'

# and then write the cell on the sheet with the usual way

Basic types of formats (I use the same input as the installation examples: -1278.9078):
(scroll down, don't be afraid)






















































































TypeExample
general-1278.91
0-1279
0.00-1278.91
#,##0-1,279
#,##0.00-1,278.91
0%-127891%
0.00%-127890.78%
0.00E+00-1.28E+03
# ?/?-1278 8/9
# ??/??-1278 69/76
M/D/YY29/06/1896
D-MMM-YY29-Jun-96
D-MMM29-Jun
MMM-YYJun-96
h:mm AM/PM2:12 AM
h:mm:ss AM/PM2:12:46 AM
h:mm02:12
h:mm:ss02:12:46
M/D/YY h:mm29/06/1896 02:12
mm:ss.012:46.1


I hope this post helps some people... Enjoy!

14 comments:

Anonymous said...

Hi, I'm reading from Italy and I'd thank you for your effort to explain a little this interesting module. Nice job. Bye.

Anonymous said...

Great start Mihalis...

Thanks.

Anonymous said...

Nice work, thank you!
But... I have another question ;-) There's a method to modify an existing xls file? I noticed the save method create a new file overwriting the old one.

Thank you a lot.

Mihalis Mavromatis said...

Hello Dario. I am afraid I am not aware of such a function. As far as I know, pyExcelerator has been created in order to create an excel file and not to edit one. All the best, Mihalis

Anonymous said...

So, currently, the only way for edit an xls consist in:

1) reading all cells with xlrd
2) edit them
3) save all cells with xlwt (pyexcelerator)

It's a tedious work, but for now that's it.

thanks again

Anonymous said...

With http://www.simplistix.co.uk/software/python/xlutils your should be able to copy your formatting over to a new file. Haven't tried it yet...

Nikunj said...

Hii,
I read your short manual for creating excel sheets using python. its very simple and understandable. Can you tell me of how to color a complete row or column using that??
thanks a lot for your useful contribution. :)

Alex Isayko said...

Thanks! Very usefull ;)

Alex said...

I deal with by excel documents quite often and every time something happens. Luckily for me several days ago I found out a good tool, which easily resolved some my issues. What is more this application could become the good solution for solving unlike issues too - excel xlsx viewer.

Mihalis Mavromatis said...

Hi Alex,
Thank you for your comment. I am not sure though how that relates to the current post, as the tool you mention doesn't create excel files with python.
Best,
Mihalis

Anonymous said...

Hi,
thanks for your great example.
but i didn't understand what is mydoc

Mihalis Mavromatis said...

Coding now corrected! :) Thanks for the comment!

Anonymous said...

Thank you for your post!
I just wanted to add that you can adjust font size as well: "font.size = 10" will make a 10pt font.

Sergey Kiselev said...

it's very good manual. THANK YOU FROM RUSSIA!!!!!