pyExcelerator is a Python module created by Roman V. Kiselyov to read and write data in Excel format. You need a superior to the 2.4 version of python. The advantage (say) to use this module to other as CSV is that it allows unicode formats, reads / writes well in both OpenOffice and Microsoft Office and has a very simple API.
Install module Ubuntu
| $ sudo apt-get install python-Excelerator |
Basics
Once installed, go back to basics. Begin to write something. Saying that if we unload the module http://sourceforge.net/projects/pyexcelerator/ in it there are many examples that can guide us to what we will do. ____________________________________________
import from pyExcelerator
* # Instance objects
the workbook = Workbook () worksheet = workbook.add_sheet
( "my first sheet" )
# Write a row
worksheet.write ( 1 , 1 , "Example" )
# Save the content
workbook.save ( 'file.xls' ) ______________________________________
What you do is instantiate a workbook and a sheet. We can create as many worksheets as you want. Then write in the position (1.1) Example word and save the name file.xls.
Format
can also write in different fonts, colors, italics , etc. This can be done by the Font class module. With the font, we show you all properties necesarias.
______________________________________
from pyExcelerator import *
# Instance the objects
workbook = Workbook()
worksheet = workbook.add_sheet( "my first sheet" )
# Style
font = Font()
font.name = "Arial"
font.colour_index = 1
font.bold = True
style = XFStyle()
style.font = font
# Write
worksheet.write ( 1 , 1 , "Example" , style)
# Save the content
workbook.save ( 'file.xls' )
______________________________________
This can also be done on the style object, as used internally Font.
Reading CSV
able to read and process the data, we parse_xls function, which we pass a file and coding, which can be utf-8. This function returns all the leaves worksheet and values, so we have to go and for example, inserted into an array. Listed below are a complete example of how to do this. The following code is an adaptation of the author's code examples directory.
Complete Example ______________________________________
# -*- coding: utf-8 -*-
# This example adapted from WAS Roman V. Kiselyov
# in examples section project
from pyExcelerator import *
class Excel ( object ):
def __init__ (self, name_space= '0' ):
self.column = 0
self.row = 0
self.workbook = Workbook()
self.worksheet = self.workbook.add_sheet(name_space)
def write_row (self, content):
self.column = 0
for i in content:
self.worksheet.write(self.row, self.column, i)
self.column += 1
self.row += 1
def write_with_format (self, content, bold= False , name= 'Arial' , colour_index= 4 ):
font = Font()
font.name = name
font.colour_index = colour_index
font.bold = bold
style = XFStyle()
style.font = font
self.column = 0
for i in content:
self.worksheet.write(self.row, self.column, i, style)
self.column += 1
self.row += 1
def save (self, filename= 'file.xls' ):
self.workbook.save(filename)
@ classmethod
def read (self, filename, coding= 'utf-8' , verbose= False ):
for sheet_name, values in parse_xls(filename, coding):
matrix = [[]]
if verbose:
print 'Sheet = "%s"' % sheet_name.encode(coding, 'backslashreplace' )
print '----------------'
for row_idx, col_idx in sorted (values.keys()):
v = values[(row_idx, col_idx)]
if isinstance (v, unicode ):
v = v.encode(coding, 'backslashreplace' )
else :
v = `v`
v = '%s' % v.strip()
last_row, last_col = len (matrix), len (matrix[- 1 ])
while last_row <= row_idx:
matrix.extend([[]])
last_row = len (matrix)
while last_col < col_idx:
matrix[- 1 ].extend([ '' ])
last_col = len (matrix[- 1 ])
matrix[- 1 ].extend([v]
if verbose:
for row in matrix:
csv_row = ', ' .join(row)
print csv_row
return matrix
if __name__ == '__main__' :
print 'Generating xls ........... ' ,
header = [ ' Name ' , ' Surname ' , ' ID ' ]
excel = Excel ()
excel.write_with_format (header, bold = True )
data = [ u'Tutú ' , u'Tiñi ' , '1239812H ' ]
excel.write_row (data) data =
[ u'Kaká' , u 'Asd' , '8712382H ' ]
excel.write_row (data)
excel.save ()
print 'ok'
print 'Reading data ........... '
Excel.read ( ' file.xls' , verbose = True )
print '................... ok '
______________________________________
This module contains many more things. Formula writing, images, styles, conversion to html, etc. In the examples section of the project can be seen in detail all this.
References