| 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() |
|---|
