Create Excel file with Python: My sort manual for pyExcelerator

2007-10-12T15:46:00.001+01:00

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)

Type	Example
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/YY	29/06/1896
D-MMM-YY	29-Jun-96
D-MMM	29-Jun
MMM-YY	Jun-96
h:mm AM/PM	2:12 AM
h:mm:ss AM/PM	2:12:46 AM
h:mm	02:12
h:mm:ss	02:12:46
M/D/YY h:mm	29/06/1896 02:12
mm:ss.0	12:46.1

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

Developing or being developed?

Create Excel file with Python: My sort manual for pyExcelerator