Compare Excel

from Config import configs
import pymssql
import xlrd
import os
import shutil
import pandas
import xlwings
import openpyxl
#import elementtree.ElementTree as ET

config = configs.get_config()

def query_from_db(sql):
server = config.ASAP_db[“server”]
db_name = config.ASAP_db[“db_name”]
user_name = config.ASAP_db[“user_name”]
password = config.ASAP_db[“password”]
#connect to db
with pymssql.connect(server,user_name,password,db_name) as conn:
#return data as a dictionary, default type is tuple
with conn.cursor(as_dict = True) as cursor:
print(sql)
cursor.execute(sql)
#fetch result and save into list
results = cursor.fetchall()
#print(results)
return results

def delete_from_db(sql):
server = config.ASAP_db[“server”]
db_name = config.ASAP_db[“db_name”]
user_name = config.ASAP_db[“user_name”]
password = config.ASAP_db[“password”]

#connect to db
with pymssql.connect(server,user_name,password,db_name) as conn:
    #return data as a dictionary, default type is tuple
    with conn.cursor(as_dict = True) as cursor:
        #print(sql)
        try:
            cursor.execute(sql)
            conn.commit()
        except:
            conn.rollback()

def get_testcase():
test_path = os.path.abspath(os.path.join(os.getcwd(),"…"))
config_path = os.path.join(test_path, “Config\case_list.xlsx”)
src_folder = os.path.join(test_path, “AllCases”)
des_folder = os.path.join(test_path, “TestCase”)
wk = xlrd.open_workbook(config_path)
ws_case = wk.sheet_by_name(“caselist”)
# column names:
# SourceFolder, CaseName, Status, DecFolder
# Status=on, means this case will be executed

for i in range(1,ws_case.nrows):
    if str.upper(ws_case.cell_value(i,2))=="ON":
        src_file = os.path.join(src_folder,ws_case.cell_value(i,1))
        #des_file = os.path.join(des_folder,ws_case.cell_value(i,2))
        shutil.copy(src_file,des_folder)

        pass

def compare_excel_values(gen_file_path, gen_sheet_name, exp_file_path,exp_sheet_name,result):
src_wk = xlrd.open_workbook(gen_file_path)
src_ws = src_wk.sheet_by_name(gen_sheet_name)
des_wk = xlrd.open_workbook(exp_file_path)
des_ws = des_wk.sheet_by_name(exp_sheet_name)
exp_result = []
act_result = []
#if source value mismatch with destination value, mark flag as 1
flag = 0
for i in range(1,des_ws.nrows):
for j in range(1,des_ws.ncols):
if des_ws.cell_value(i,j)==src_ws.cell_value(i,j):
continue
else:
flag = 1
exp_result.append(des_ws.cell_value(i,j))
act_result.append(src_ws.cell_value(i,j))
result[“exp_result”] = exp_result
result[“act_result”] = act_result

if flag==1:
    return False
else:
    return True

def convert_html_to_excel():
url = “file:///C:/Users/jzhang399/PycharmProjects/ASAP_API_unittest/TestReport/2019-05-13-17-59-37-bstest.html”
for i, df in enumerate(pandas.read_html(url)):
df.to_csv(‘APIResult%d.csv’ % i)

def execute_excel_macro(report_name):
wb = xlwings.Book(configs.excel_macro[‘excel_macro_path’])
app = wb.app
macro_WAL_vba = app.macro(configs.excel_macro[report_name])
macro_WAL_vba()

def clear_RE_summary_report():
wk = openpyxl.load_workbook(configs.excel_macro[‘summary_report_path’])
#report娴嬭瘯瀵规瘮缁撴灉淇濆瓨鍦⊿ummary sheet
ws = wk.get_sheet_by_name(‘Summary’)
#鑾峰彇Summary sheet宸茶浣跨敤鐨勮鏁�
row_used = ws.max_row
print(row_used)
#鍒犻櫎宸叉湁鐨勬祴璇曠粨鏋�淇濊瘉姣忔娴嬭瘯閲嶆柊鐢熸垚鏂扮殑娴嬭瘯缁撴灉,淇濈暀绗竴琛岃〃澶达紝浠庣浜岃寮�鍒犻櫎row_used -1 琛�
ws.delete_rows(2,row_used-1)
wk.save(configs.excel_macro[‘summary_report_path’])

def compare_xml_reports(baseline_report_path,new_report_path):

pass

if name==‘main’:
convert_html_to_excel()