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 read_src_excel(sheetname): excel = Excel(settings.SRC_FILE_PATH) srccolnames = excel.get_column_names(sheetname) src_colnames = [] for src_col_item in srccolnames: if src_col_item is not None: src_colnames.append(src_col_item) return src_colnames
def read_tgt_excel(sheetname): excel = Excel(settings.TGT_FILE_PATH) tgtcolnames = excel.get_column_names(sheetname) tgt_colnames = [] for tgt_col_item in tgtcolnames: if tgt_col_item is not None: tgt_colnames.append(tgt_col_item) return tgt_colnames
def get_match_columns(sheetname, idx=None): _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) _matchcolumn = [] for _item in _srccolumn: if _item in _tgtcolumn and _item is not None: _matchcolumn.append(_item) if idx is not None: for i in _matchcolumn: if i in idx: _matchcolumn.remove(i) _sflogger.info('matched column: {}'.format(_matchcolumn)) return _matchcolumn
def get_add_columns(sheetname): _path = settings.TGT_FILE_PATH _tgtexcel = Excel(_path) _tgtcolumn = _tgtexcel.get_column_names(sheetname) _match_columns = get_match_columns(sheetname) for _item in _match_columns: if _item in _tgtcolumn and _item is not None: _tgtcolumn.remove(_item) _sflogger.info('added column: {}'.format(_tgtcolumn)) return _tgtcolumn
def get_del_columns(sheetname): _path = settings.SRC_FILE_PATH _srcexcel = Excel(_path) _srccolumn = _srcexcel.get_column_names(sheetname) _match_columns = get_match_columns(sheetname) for _item in _match_columns: if _item in _srccolumn and _item is not None: _srccolumn.remove(_item) _sflogger.info('deleted column: {}'.format(_srccolumn)) return _srccolumn
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Time : 3/23/18 4:32 PM # @Author : Miracle Young # @File : app.py from lib.logger import StreamFileLogger from lib.excel import Excel from utils import settings _sflogger = StreamFileLogger(settings.LOG_FILE, __file__).get_logger() _sflogger.info(1) _sflogger.debug(2) try: a except Exception as e: _sflogger.error('Failed', exc_info=True) excel_name = settings.SRC_DATA['a.xlsx'] excel = Excel(excel_name) excel.get_column_names('CAPS Industry KPIs New', 'A1', 'F28') # get all sheetname excel.read_excel_by_pos('CAPS Industry KPIs New', 'A1', 'F28') #