1 | import string |
---|
2 | from fcc_utils2 import snlist |
---|
3 | import xlrd, string, shelve, os, re, sys |
---|
4 | import collections |
---|
5 | import xlutils, xlwt |
---|
6 | import xlutils.copy |
---|
7 | import xlutils.styles |
---|
8 | #### |
---|
9 | import dreq_cfg |
---|
10 | |
---|
11 | class wbcp(object): |
---|
12 | def __init__( self, inbook=dreq_cfg.rqcfg.tmpl ): |
---|
13 | self.book = xlrd.open_workbook(inbook,formatting_info=True) |
---|
14 | self.sns = self.book.sheet_names() |
---|
15 | self.wb = xlutils.copy.copy(self.book) |
---|
16 | ##self.book = xlrd.open_workbook(inbook,formatting_info=True) |
---|
17 | self.plain = xlwt.easyxf('') |
---|
18 | self.styles = xlutils.styles.Styles(self.book) |
---|
19 | |
---|
20 | def _getOutCell(self, rowIndex, colIndex): |
---|
21 | """ HACK: Extract the internal xlwt cell representation. """ |
---|
22 | row = self.currentSo._Worksheet__rows.get(rowIndex) |
---|
23 | if not row: return None |
---|
24 | |
---|
25 | cell = row._Row__cells.get(colIndex) |
---|
26 | return cell |
---|
27 | |
---|
28 | def putValue2(self, row, col, value): |
---|
29 | """ Change cell value without changing formatting. """ |
---|
30 | # HACK to retain cell style. |
---|
31 | previousCell = self._getOutCell( row, col) |
---|
32 | # END HACK, PART I |
---|
33 | |
---|
34 | self.currentSo.write(row, col, value) |
---|
35 | |
---|
36 | # HACK, PART II |
---|
37 | if previousCell: |
---|
38 | newCell = self._getOutCell( row, col) |
---|
39 | if newCell: |
---|
40 | newCell.xf_idx = previousCell.xf_idx |
---|
41 | # END HACK |
---|
42 | |
---|
43 | def focus( self, name ): |
---|
44 | self.currentIndex = self.sns.index(name) |
---|
45 | if self.currentIndex == -1: |
---|
46 | self.currentSi = None |
---|
47 | else: |
---|
48 | self.currentSi = self.book.sheet_by_name( name ) |
---|
49 | self.currentSo = self.wb.get_sheet( self.currentIndex ) |
---|
50 | |
---|
51 | def putValue(self,i,j,value): |
---|
52 | ##self.currentSi.write(i,j,value,self.plain) |
---|
53 | cell_style = self.styles[self.currentSi.cell(i,j)] |
---|
54 | self.currentSo.write(i,j,value,cell_style) |
---|
55 | |
---|
56 | def write(self,file='output.xls'): |
---|
57 | self.wb.save( file ) |
---|
58 | |
---|
59 | class tupsort: |
---|
60 | def __init__(self,k=0): |
---|
61 | self.k = k |
---|
62 | def cmp(self,x,y): |
---|
63 | return cmp( x[self.k], y[self.k] ) |
---|
64 | |
---|
65 | def uniquify( ll ): |
---|
66 | ll.sort() |
---|
67 | l0 = [ll[0],] |
---|
68 | for l in ll[1:]: |
---|
69 | if l != l0[-1]: |
---|
70 | l0.append(l) |
---|
71 | return l0 |
---|
72 | |
---|
73 | class workbook(object): |
---|
74 | def __init__(self,file): |
---|
75 | assert os.path.isfile(file), 'File %s not found' % file |
---|
76 | self.book = xlrd.open_workbook( file ) |
---|
77 | self.sns = self.book.sheet_names() |
---|
78 | |
---|
79 | clabs = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" |
---|
80 | def clab(n): |
---|
81 | i = n/26 |
---|
82 | assert i < 26, 'Not ready for row number greater than 26*26' |
---|
83 | if i == 0: |
---|
84 | return clabs[n] |
---|
85 | else: |
---|
86 | return clabs[i-1] + clabs[ n - i*26] |
---|
87 | |
---|
88 | def getRow(sht): |
---|
89 | ee = {} |
---|
90 | for i in range(sht.nrows): |
---|
91 | if sht.row(i)[0].ctype == 2 and str( sht.row(i)[0].value ) != '0.0': |
---|
92 | l = map( lambda x: str( x.value ), sht.row(i) ) |
---|
93 | ##k = string.replace( l[5], ' ','') |
---|
94 | k = l[5] |
---|
95 | try: |
---|
96 | ee[k] = l |
---|
97 | except: |
---|
98 | print l |
---|
99 | raise |
---|
100 | return ee |
---|
101 | |
---|
102 | snli = snlist( dir='../inputsOther/config/cf/' ) |
---|
103 | sn,sna = snli.gen_sn_list() |
---|
104 | esn = {} |
---|
105 | esna = {} |
---|
106 | for s in sn: |
---|
107 | esn[s] = s |
---|
108 | for s in sna: |
---|
109 | esna[s] = s |
---|
110 | |
---|
111 | vdict = collections.defaultdict( list ) |
---|
112 | for l in open( '../inputsOther/vlsc3.csv' ): |
---|
113 | v,l,s,c,m = string.split(string.strip(l),'\t')[:5] |
---|
114 | vdict[v].append(( l,s,c,m)) |
---|
115 | |
---|
116 | dir0 = '/home/martin/2014/wip/dreq/input/' |
---|
117 | file = 'PMIP/CMIP6DataRequestCompilationTemplate_20141218_PMIP_v150228_final.xls' |
---|
118 | file = 'C4MIP/CMIP6DataRequestCompilationTemplate_C4MIP_06March2015_submitted.xls' |
---|
119 | |
---|
120 | wk0 = wbcp( ) |
---|
121 | cfg = dreq_cfg.rqcfg() |
---|
122 | wk0.focus( u'New variables') |
---|
123 | |
---|
124 | keys = cfg.ee.keys() |
---|
125 | keys.sort() |
---|
126 | |
---|
127 | def outvSer( ov ): |
---|
128 | ll = [] |
---|
129 | for i in ov: |
---|
130 | ll.append( '%s|%s|%s' % tuple( map( str, i) ) ) |
---|
131 | return string.join(ll, '; ' ) |
---|
132 | |
---|
133 | |
---|
134 | class cpsh(object): |
---|
135 | |
---|
136 | def __init__(self,wk0,mip,path,kk=3,oo=None): |
---|
137 | self.oo = oo |
---|
138 | self.nn = 0 |
---|
139 | self.kk = kk |
---|
140 | self.nnew = 0 |
---|
141 | wk = wbcp( path ) |
---|
142 | wk0.focus( u'New variables') |
---|
143 | self.wk = wk |
---|
144 | self.mip = mip |
---|
145 | self.nvgs = [] |
---|
146 | for s in wk.sns: |
---|
147 | if (s not in ['ALL VARIABLES', 'Objectives','Experiments','Experiment Groups','Request scoping','New variables','__lists__']) and \ |
---|
148 | (s[:5] != 'CMIP5'): |
---|
149 | self.nvgs.append(s) |
---|
150 | self.outv = collections.defaultdict(list) |
---|
151 | for s in self.nvgs: |
---|
152 | thiss = wk.book.sheet_by_name( s ) |
---|
153 | for k in range(4,thiss.nrows): |
---|
154 | r = thiss.row(k) |
---|
155 | v = r[1].value |
---|
156 | t = r[2].value |
---|
157 | f = r[3].value |
---|
158 | s = r[5].value |
---|
159 | m = r[7].value |
---|
160 | noFilt = False |
---|
161 | if t[:3] == 'new' or noFilt: |
---|
162 | self.outv[v].append( (f,s,m) ) |
---|
163 | if u'New variables' not in wk.sns: |
---|
164 | print '############### no new variables, %s' % self.mip |
---|
165 | return |
---|
166 | this = wk.book.sheet_by_name(u'New variables') |
---|
167 | ee = collections.defaultdict( list ) |
---|
168 | for i in range(3,this.nrows): |
---|
169 | r = this.row(i) |
---|
170 | if r[0].value == "**end**": |
---|
171 | break |
---|
172 | v = r[0].value |
---|
173 | ee[v].append(i) |
---|
174 | self.nnew += 1 |
---|
175 | |
---|
176 | for i in range(3,this.nrows): |
---|
177 | r = this.row(i) |
---|
178 | if r[0].value == "**end**": |
---|
179 | break |
---|
180 | |
---|
181 | v = r[0].value |
---|
182 | l = r[4].value |
---|
183 | novar = v == '' and l == '' |
---|
184 | omitOld = True |
---|
185 | omitOld = False |
---|
186 | omit = False |
---|
187 | if vdict.has_key(v) and omitOld: |
---|
188 | omit = True |
---|
189 | s = r[1].value |
---|
190 | if s in ['','?']: |
---|
191 | chk = 0 |
---|
192 | elif esn.has_key(s): |
---|
193 | chk = 1 |
---|
194 | elif esna.has_key(s): |
---|
195 | chk = 2 |
---|
196 | else: |
---|
197 | chk = -1 |
---|
198 | omitWithSn = False |
---|
199 | if chk in [1,2] and omitWithSn: |
---|
200 | omit = True |
---|
201 | if novar: |
---|
202 | omit = True |
---|
203 | if v[0:17] == 'Insert additional': |
---|
204 | print 'Omitting comment line' |
---|
205 | omit = True |
---|
206 | |
---|
207 | if not omit: |
---|
208 | if not novar: |
---|
209 | wk0.putValue2( self.kk, 0, mip ) |
---|
210 | wk0.putValue2( self.kk, 1, chk ) |
---|
211 | self.nn += 1 |
---|
212 | htmlline = "<td>%s</td>" % mip |
---|
213 | else: |
---|
214 | chk = 0 |
---|
215 | htmlline = "<td></td>" |
---|
216 | |
---|
217 | j = 3 |
---|
218 | jo = 1 |
---|
219 | wk0.putValue2( self.kk, 3, outvSer( self.outv[v] ) ) |
---|
220 | for x in r: |
---|
221 | if j == 3: |
---|
222 | v = x.value |
---|
223 | v0 = x.value |
---|
224 | if str(v0) != "": |
---|
225 | if len(ee[v0]) != 1: |
---|
226 | v += '!' |
---|
227 | if vdict.has_key(v0): |
---|
228 | v += '**' |
---|
229 | wk0.putValue2( self.kk, j+jo, v ) |
---|
230 | else: |
---|
231 | wk0.putValue2( self.kk, j+jo, x.value ) |
---|
232 | if j in [3,7]: |
---|
233 | htmlline += "<td>%s</td>\n" % x.value |
---|
234 | elif j == 4: |
---|
235 | if chk == -1: |
---|
236 | htmlline += "<td>?%s?</td>\n" % x.value |
---|
237 | else: |
---|
238 | htmlline += "<td>%s</td>\n" % x.value |
---|
239 | elif j == 8: |
---|
240 | y = x.value |
---|
241 | elif j == 9: |
---|
242 | htmlline += '<td><span title="%s">%s</span></td>\n' % (x.value,y) |
---|
243 | j += 1 |
---|
244 | self.kk += 1 |
---|
245 | if not novar: |
---|
246 | htmlline = string.replace( htmlline, u'\u2013', '-' ) |
---|
247 | htmlline = string.replace( htmlline, u'\u2018', "'" ) |
---|
248 | htmlline = string.replace( htmlline, u'\u2019', "'" ) |
---|
249 | htmlline = string.replace( htmlline, u'\u2026', '...' ) |
---|
250 | htmlline = string.replace( htmlline, u'\u25e6', 'o' ) |
---|
251 | htmlline = string.replace( htmlline, u'\xb2', '2' ) |
---|
252 | htmlline = string.replace( htmlline, u'\xb3', '3' ) |
---|
253 | self.oo.write( "<tr>%s</tr>" % str(htmlline) + '\n' ) |
---|
254 | |
---|
255 | def parseRQ(self): |
---|
256 | if u'Request scoping' not in self.wk.sns: |
---|
257 | return |
---|
258 | this = self.wk.book.sheet_by_name(u'Request scoping') |
---|
259 | for i in range(6,this.nrows): |
---|
260 | r = this.row(i) |
---|
261 | mipt = r[0].value |
---|
262 | s = r[1].value |
---|
263 | if mipt[:4] in ['SPEC','CORD', 'CCMI'] and s != 'none': |
---|
264 | print self.mip,mipt |
---|
265 | |
---|
266 | kk=3 |
---|
267 | c1 = """<th>%s</th> |
---|
268 | <th>%s</th> |
---|
269 | <th>%s</th> |
---|
270 | <th>%s</th> |
---|
271 | <th>%s</th> |
---|
272 | <th>%s</th> |
---|
273 | <th>%s</th> |
---|
274 | <th>%s</th>""" % ("MIP","Variable","Standard name","SN status","grid","units","Long name","comments") |
---|
275 | c1 = """<th>%s</th> |
---|
276 | <th>%s</th> |
---|
277 | <th>%s</th> |
---|
278 | <th>%s</th> |
---|
279 | <th>%s</th>""" % ("MIP","Variable","Standard name","units","Long name") |
---|
280 | oo = open( 'table.htmllet', 'w' ) |
---|
281 | oo.write( """<table id="example" class="display" cellspacing="0" width="100%%"> |
---|
282 | <thead> |
---|
283 | <tr> |
---|
284 | %s |
---|
285 | </tr> |
---|
286 | </thead> |
---|
287 | <tfoot> |
---|
288 | <tr> |
---|
289 | %s |
---|
290 | </tr> |
---|
291 | </tfoot> |
---|
292 | <tbody> |
---|
293 | """ % (c1,c1) ) |
---|
294 | |
---|
295 | oo1 = open( 'newVarCount.txt', 'w' ) |
---|
296 | for k in keys: |
---|
297 | print k,cfg.ee[k] |
---|
298 | path = '%s%s/%s' % (dir0,k,cfg.ee[k]) |
---|
299 | c = cpsh(wk0,k,path,kk=kk,oo=oo) |
---|
300 | print c.nn |
---|
301 | oo1.write( '%s %s %s\n' % (k,c.nnew,c.nn) ) |
---|
302 | c.parseRQ() |
---|
303 | kk=c.kk |
---|
304 | oo1.close() |
---|
305 | oo.write( "</tbody>\n</table>\n" ) |
---|
306 | oo.close() |
---|
307 | |
---|
308 | wk0.write() |
---|