Package Camelot :: Package camelot :: Package view :: Package export :: Module excel |
[frames] | no frames]

# Source Code for Module Camelot.camelot.view.export.excel

      1  #  ==================================================================================
      2  #
      3  #  Copyright (C) 2007-2008 Conceptive Engineering bvba. All rights reserved.
      4  #  www.conceptive.be / project-camelot@conceptive.be
      5  #
      6  #  This file is part of the Camelot Library.
      7  #
      8  #  This file may be used under the terms of the GNU General Public
      9  #  License version 2.0 as published by the Free Software Foundation
     10  #  and appearing in the file LICENSE.GPL included in the packaging of
     11  #  this file.  Please review the following information to ensure GNU
     12  #  General Public Licensing requirements will be met:
     13  #  http://www.trolltech.com/products/qt/opensource.html
     14  #
     15  #  If you are unsure which license is appropriate for your use, please
     16  #  review the following information:
     17  #  http://www.trolltech.com/products/qt/licensing.html or contact
     18  #  project-camelot@conceptive.be.
     19  #
     20  #  This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
     21  #  WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
     22  #
     23  #  For use of this library in commercial applications, please contact
     24  #  project-camelot@conceptive.be
     25  #
     26  #  ==================================================================================
     27
     28  import logging
     29  logger = logging.getLogger('camelot.view.export.excel')
     30
     31  from pyExcelerator import *
     32  import datetime
     33
     34  titleFont = Font()              # initializing titleFont Object
     35  headerFont = Font()             # initializing headerFont Object
     36  cellFont = Font()               # initializing cellFont Object
     37
     38  titleFont.name = 'Arial'        # Setting Fonts Name
     39  headerFont.name = 'Arial'
     40  cellFont.name = 'Arial'
     41
     42  titleFont.bold = True           # Setting title font to bold
     43  headerFont.bold = True          # Setting column header font to bold
     44  cellFont.bold = False           # Setting cell font to bold
     45
     46  titleFont.height = 240          # 12*20 = 240 Font Size
     47  headerFont.height = 220         # 10*20 = 240 Font Size
     48  cellFont.height = 220           # 10*20 = 240 Font Size
     49
     50  brdLeft = Borders()                # Defining border which is around header
     51  brdLeft.left = 0x01
     52
     53  brdRight = Borders()                # Defining border which is around header
     54  brdRight.right = 0x01
     55
     56  brdTop = Borders()                # Defining border which is around header
     57  brdTop.top = 0x01
     58
     59  brdBottom = Borders()                # Defining border which is around header
     60  brdBottom.bottom = 0x01
     61
     62  brdTopLeft = Borders()
     63  brdTopLeft.top = 0x01
     64  brdTopLeft.left = 0x01
     65
     66  brdBottomLeft = Borders()
     67  brdBottomLeft.bottom = 0x01
     68  brdBottomLeft.left = 0x01
     69
     70  brdBottomRight = Borders()
     71  brdBottomRight.bottom = 0x01
     72  brdBottomRight.right = 0x01
     73
     74  brdTopRight = Borders()
     75  brdTopRight.top = 0x01
     76  brdTopRight.right = 0x01
     77
     78  dateStyle = XFStyle()
     79
     80
     81  titleStyle = XFStyle()
     82  headerStyle = XFStyle()
     83  cellStyle = XFStyle()
     84  dateStyle = XFStyle()
     85
     86  leftCellStyle = XFStyle()
     87  rightCellStyle = XFStyle()
     88  bottomCellStyle = XFStyle()
     89  topleftCellStyle = XFStyle()
     90  bottomleftCellStyle = XFStyle()
     91  bottomrightCellStyle = XFStyle()
     92  toprightCellStyle = XFStyle()
     93
     94  titleStyle.font = titleFont
     95  headerStyle.font = headerFont
     96  headerStyle.borders = brdTop
     97  cellStyle.font = cellFont
     98
     99  topleftCellStyle.font = headerFont
    100  topleftCellStyle.borders = brdTopLeft
    101
    102  bottomleftCellStyle.font = cellFont
    103  bottomleftCellStyle.borders = brdBottomLeft
    104
    105  bottomrightCellStyle.font = cellFont
    106  bottomrightCellStyle.borders = brdBottomRight
    107
    108  toprightCellStyle.font = headerFont
    109  toprightCellStyle.borders = brdTopRight
    110
    111  leftCellStyle.borders = brdLeft
    112  leftCellStyle.font = cellFont
    113
    114  rightCellStyle.borders = brdRight
    115  rightCellStyle.font = cellFont
    116
    117  bottomCellStyle.borders = brdBottom
    118  bottomCellStyle.font = cellFont
    119
    120  pat1 = Pattern()
    121  pat1.pattern = Pattern.SOLID_PATTERN
    122  pat1.pattern_fore_colour = 0x16
    123  headerStyle.pattern = pat1
    124  topleftCellStyle.pattern = pat1
    125  toprightCellStyle.pattern = pat1
    126
    127 -def open_data_with_excel(title, headerList, dataList):
    128      import sys
    129      import tempfile
    130      _xls_fd, xls_fn = tempfile.mkstemp(suffix='.xls')
    131      write_data_to_excel(xls_fn, title, headerList, dataList)
    132      from PyQt4 import QtGui, QtCore
    133      if not 'win' in sys.platform:
    134          QtGui.QDesktopServices.openUrl(QtCore.QUrl('file://%s' % xls_fn))
    135      else:
    136          import pythoncom
    137          import win32com.client
    138          pythoncom.CoInitialize()
    139          excel_app = win32com.client.Dispatch("Excel.Application")
    140          excel_app.Visible = True
    141          excel_app.Workbooks.Open(xls_fn)
    142
    143 -def write_data_to_excel(filename, title, headerList, dataList):
    144      """
    145      @param filename: the file to which to save the exported excel
    146      @param title: title to put in the first row of the genarated excel file
    147      @param headerList: list of header definitions
    148      @param dataList: list or generator with the row data
    149      """
    150      w = Workbook()
    151      ws = w.add_sheet('Sheet1')
    152      ## Writing Title
    153      ws.write(0, 0, title, titleStyle)                   # Writing Title
    154      ws.col(0).width = len(title) * 400                  # Setting cell width
    155      ## Writing Header
    156      myDataTypeDict = {}            # dictionary of datatype, {columnnumber, Datatype}
    157      myPrecisionDict = {}        # dictionary of precision , {columnnumber, Precision}
    158      myLengthDict = {}           # dictionary of length , {columnnumber, length}
    159      myFormatDict = {}           # dictionary of dateformat , {columnnumber, format}
    160      for n,desc in enumerate(headerList):
    161          lst =  desc[1]
    162          if n==0:
    163              ws.write(2, n, unicode(lst['name']), topleftCellStyle)
    164          elif n==len(headerList)-1:
    165              ws.write(2, n, unicode(lst['name']), toprightCellStyle)
    166          else:
    167              ws.write(2, n, unicode(lst['name']), headerStyle)
    168          if len(unicode(lst['name'])) < 8:
    169              ws.col(n).width = 8 *  375
    170          else:
    171              ws.col(n).width = len(unicode(lst['name'])) *  375
    172          myDataTypeDict[ n ] = lst["python_type"]
    173          if lst["python_type"] == float:
    174              myPrecisionDict [ n ] = lst["precision"]    #Populating precision dictionary
    175          elif lst["python_type"] == datetime.date:
    176              myFormatDict [ n ] = lst["format"]          #Populating date Format dictionary
    177          elif lst["python_type"] == datetime.datetime:
    178              myFormatDict [ n ] = lst["format"]          #Populating date Format dictionary
    179          elif lst["python_type"] == str:
    180              if 'length' in lst:
    181                  myLengthDict [ n ] = lst["length"]          #Populating Column Length dictionary
    182      ## Writing Data
    183      row = 3
    184      column = 0
    185      valueAddedInSize = 0
    186      formatStr = '0'
    187      for dictCounter in dataList:                       # iterating the dataList, having dictionary
    188          column = 0
    189          cellStyle.num_format_str = '0'
    190          for i in range( 0 , len(dictCounter)): #for i in dictCounter:
    191              valueAddedInSize = 0
    192              val = dictCounter[i]
    193              if val != None:
    194                  if not isinstance(val,(str,unicode,int,float,datetime.datetime,datetime.time,datetime.date,
    195                                         ExcelFormula.Formula) ):
    196                      val = unicode(val)
    197                  if myDataTypeDict.has_key(column) == True:
    198                      if myLengthDict.get(column) != None:
    199                          if len(val) > myLengthDict[ column ]:
    200                              val = val[0:myLengthDict[ column ]]
    201                      elif myDataTypeDict[ column ] == str:
    202                          formatStr = '0'
    203                      elif myDataTypeDict[ column ] == int:
    204                          formatStr = '0'
    205                      elif myDataTypeDict[ column ] == float:
    206                          formatStr = '0.'
    207                          for _j in range( 0 , myPrecisionDict[ column ]):
    208                              formatStr += '0'
    209                          valueAddedInSize = len(formatStr) # To fit the cell width + 1 (of dot(.))
    210                      elif myDataTypeDict[ column ] == datetime.date:
    211                          formatStr = myFormatDict[column]
    212                          val = datetime.datetime( day = val.day, year = val.year, month = val.month)
    213                      elif myDataTypeDict[ column ] == datetime.datetime:
    214                          formatStr = myFormatDict[column]
    215                      elif myDataTypeDict[ column ] == bool:
    216                          formatStr = '0'
    217                      else:
    218                          formatStr = '0'
    219                  cellStyle.num_format_str = formatStr
    220                  bottomCellStyle.num_format_str = formatStr
    221                  rightCellStyle.num_format_str = formatStr
    222                  bottomrightCellStyle.num_format_str = formatStr
    223                  leftCellStyle.num_format_str = formatStr
    224                  bottomleftCellStyle.num_format_str = formatStr
    225              elif val == None:
    226                  val = ' '
    227              if row - 2  == len(dataList):
    228                  #we re at the bottom row
    229                  if i==0:
    230                      ws.write(row , column, val , bottomleftCellStyle)
    231                  elif i  == len(dictCounter)-1:
    232                      ws.write(row , column, val , bottomrightCellStyle)
    233                  else:
    234                      ws.write(row , column, val , bottomCellStyle)
    235              else:
    236                  if i==0:
    237                      ws.write(row , column, val , leftCellStyle)
    238                  elif  i == len(dictCounter)-1:
    239                      ws.write(row , column, val , rightCellStyle)
    240                  else:
    241                      ws.write(row , column, val , cellStyle)
    242              if ws.col(column).width < (len(unicode( val )) )* 300:
    243                  ws.col(column).width = (len(unicode( val )) + valueAddedInSize )* 300
    244              column = column + 1
    245          row = row + 1
    246      w.save(filename)
    247