Thursday, April 21, 2011

Cold Shooting Through Foot

Generate Excel files with Python


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
  • pyExcelerator project

0 comments:

Post a Comment