def get_compare_colNum(sheetname, idx):
_srcpath = settings.SRC_FILE_PATH
_tgtpath = settings.TGT_FILE_PATH
_srcexcel = Excel(_srcpath)
_tgtexcel = Excel(_tgtpath)
_srccolumn = _srcexcel.get_column_names(sheetname)
_tgtcolumn = _tgtexcel.get_column_names(sheetname)
#getcompare column position for both sides
_matchcolumn = []
for _item in _srccolumn:
if _item in _tgtcolumn and _item is not None:
_matchcolumn.append(_item)
for _item in _matchcolumn:
if _item in idx:
_matchcolumn.remove(_item)
_sheadnum = []
_theadnum = []
for _maccol in _matchcolumn:
_curshead = _srcexcel.convert_col2header(sheetname, _maccol)
_sheadnum.append(_curshead)
for _maccol in _matchcolumn:
_curthead = _tgtexcel.convert_col2header(sheetname, _maccol)
_theadnum.append(_curthead)
_compareColZips = zip(_sheadnum, _theadnum)
#getcompare row position for both sides
return _compareColZips
# get_compare_colNum('CAPS Industry KPIs New',['Name'])
def highlight_columns(sheetname):
dif_items = get_dif_columns(sheetname)
excel = Excel(settings.TGT_FILE_PATH)
wb = excel.get_wb()
ws = excel.get_sheet(sheetname)
max_row = excel.get_max_row(sheetname)
col_hdr = []
for dif_col in dif_items:
col_hdr_item = excel.convert_col2header(sheetname=sheetname,
column_name=dif_col)
col_hdr.append(col_hdr_item)
#print(col_hdr)
# for row_nbr in range(1,max_row-490):
# for hdr in col_hdr:
# cell_item = hdr+str(row_nbr)
# print(cell_item)
# cell_item.fill = openpyxl.styles.fills.Color.
# ws[cell_item].fill = colors.RED
#
# wb.save('new_compare_result.xlsx')
for item in get_dif_columns(sheetname):
cells = excel.get_column(sheetname, column_name=item)
print(cells)
for cell in cells:
cell.fill = PatternFill(patternType='solid', fgColor='00FF0000')
wb.save('new_compare_result.xlsx')
def get_data_message(path,sheetname,idx=None):
#initial index into list
_headername = []
#store message data
_alldata = []
_excel = Excel(path)
if idx is None:
_mactch_column_name = compareData.get_match_columns(sheetname)
for columnname in _mactch_column_name:
_curcells = _excel.convert_col2header(sheetname,columnname)
_headername.append(_curcells)
else:
_indexCols = idx.split(',')
_mactch_column_name = compareData.get_match_columns(sheetname, _indexCols)
for columnname in _indexCols:
_curcells = _excel.convert_col2header(sheetname, columnname)
_headername.append(_curcells)
_cursheet = _excel.get_sheet(sheetname)
for _row in range(2, _cursheet.max_row+1):
_rowdata = []
for _column in _headername:
_cellname = "{}{}".format(_column, _row)
#get current cell line number and line column
_cellvalue = _cursheet[_cellname].value
_cellvalue = str(_cellvalue).upper()
_rowdata.append(_cellvalue.strip())
#upper all values
_alldata.append(_rowdata)
for _item in _alldata[::-1]:
if _item in _alldata:
_getcount = _alldata.count(_item)
_item.append(_getcount)
return _alldata