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!
