def compare_sheets(table, sht_old, sht_new, with_desc=False):
rslt = {
'tablename' : table.table_name,
'newdata' : [],
'moddata' : [],
}
cols = table.get_colname_list()
pks = insp.get_pk_constraint(table.table_name)['constrained_columns']
pks = [x[len(table.prefix):] for x in pks]
header, data_old = get_all_sheet_data(sht_old, pks, with_desc)
_, data_new = get_all_sheet_data(sht_new, pks, with_desc)
# new data
new_keys = [k for k in data_new if k not in data_old]
for key in new_keys:
row, data = data_new[key]
rslt['newdata'].append((key, dict((k + ' ' + attrs.get(table.prefix + k, ''), v) for (k, v) in data.iteritems())))
for colname in data:
col = header[colname]
cell = sht_new.cell(row=row, column=col)
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.YELLOW
# deleted data
# changed data
mod_keys = [k for k in data_new if k in data_old]
for key in mod_keys:
rold, dold = data_old[key]
rnew, dnew = data_new[key]
"""
print table.table_name
print 'added', set(dnew.keys()) - set(dold.keys())
print 'removed', set(dold.keys()) - set(dnew.keys())
"""
moddata = {}
for colname in (k for k in dnew if dnew.get(k) != dold.get(k)):
col = header[colname]
cell = sht_old.cell(row=rold, column=col)
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.YELLOW
cell = sht_new.cell(row=rnew, column=col)
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.YELLOW
coldesc = attrs.get(table.prefix + colname, '')
moddata[colname + ' ' + coldesc] = {
'old' : dold.get(colname),
'new' : dnew.get(colname),
}
if moddata:
rslt['moddata'].append((k, moddata))
return rslt
def write_data_sheet(sht, table, cond=None, with_col_desc=False):
result = table.select(cond).execute()
coltypes = dict(((c.name, c.type) for c in table.columns))
rowpos = with_col_desc and 2 or 0
if with_col_desc:
for c, colname in enumerate(result.keys()):
coldesc = attrs.get(colname, '')
cell = sht.cell(row=0, column=c)
cell.value = encode(coldesc)
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = 'EFEFEF'
coltype = coltypes[colname]
cell = sht.cell(row=1, column=c)
cell.value = encode(coltype).lower()
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = 'EFEFEF'
for c, colname in enumerate(result.keys()):
cell = sht.cell(row=rowpos, column=c)
cell.value = encode(colname if not table.prefix else colname[7:])
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = 'EFEFEF'
for r, row in enumerate(result.fetchall(), start=rowpos+1):
for c, (k, v) in enumerate(row.items()):
cell = sht.cell(row=r, column=c)
#cell.value = encode(v)
cell.set_value_explicit(value=encode(v))