1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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()
35 headerFont = Font()
36 cellFont = Font()
37
38 titleFont.name = 'Arial'
39 headerFont.name = 'Arial'
40 cellFont.name = 'Arial'
41
42 titleFont.bold = True
43 headerFont.bold = True
44 cellFont.bold = False
45
46 titleFont.height = 240
47 headerFont.height = 220
48 cellFont.height = 220
49
50 brdLeft = Borders()
51 brdLeft.left = 0x01
52
53 brdRight = Borders()
54 brdRight.right = 0x01
55
56 brdTop = Borders()
57 brdTop.top = 0x01
58
59 brdBottom = Borders()
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
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
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
153 ws.write(0, 0, title, titleStyle)
154 ws.col(0).width = len(title) * 400
155
156 myDataTypeDict = {}
157 myPrecisionDict = {}
158 myLengthDict = {}
159 myFormatDict = {}
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"]
175 elif lst["python_type"] == datetime.date:
176 myFormatDict [ n ] = lst["format"]
177 elif lst["python_type"] == datetime.datetime:
178 myFormatDict [ n ] = lst["format"]
179 elif lst["python_type"] == str:
180 if 'length' in lst:
181 myLengthDict [ n ] = lst["length"]
182
183 row = 3
184 column = 0
185 valueAddedInSize = 0
186 formatStr = '0'
187 for dictCounter in dataList:
188 column = 0
189 cellStyle.num_format_str = '0'
190 for i in range( 0 , len(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)
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
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