There are a lots of libraries to implement python excel operations (see the compared screenshot from net ), here I strongly recommend win32com as the primary options, This is not only because it has rich Windows Excel operation interface, but also can be seamlessly connected with the graphical interface, and then write a good interactive test scripts, here I wanna introduce you an interesting script that use python (win32com) pachage and combine with tkinter GUI to operation windows Excel
Okay, let’s go back to the road, and see how I implement win32 to check excell cells
1. The scenario statement
1. Specified a test excel file
2. Entry the tested title value(cell's value)
3. Validate the specified title and see if it's existing in the tested file
4. Statistic tested excel file used Range(rows, columns)
5. Consolidate test result and send the result to the specified recievers2. The implement code
# -*- coding: utf-8 -*-
# @Project Name: PyRepository
# @File: checkExcelTemplateDetails.py
# @Author: Alan.Yuan
# @Time: 2022/1/27 20:49
# Press Shift+F10 to execute it or replace it with your code, in my laptop you should use this combine keys to execute : "shift + fn + F10"
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
# --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# import the related packages -
import tkinter.messagebox
from tkinter import *
from tkinter.filedialog import (askdirectory, askopenfile, askopenfiles, asksaveasfile, askopenfilename, askopenfilenames, asksaveasfilename)
import win32com
from win32com.client import Dispatch
import sys, os
# Specify the tested excel file -
root = Tk()
root.wm_attributes('-topmost',1) # 将提示窗口置顶
root.withdraw() # 关闭原始窗口
# get the filename -
testedFile = askopenfilename() # get the testedFile like - C:/Users/ayuan2/Desktop/Alan_Studio_VF2022/home.html
objSheetName = testedFile.split("/")[-1] # get the testedFile like - home.htm
xlApp = win32com.client.Dispatch("Excel.Application")
xlOpenBook = xlApp.Workbooks.Open(testedFile)
xlOpenSheet = xlOpenBook.Worksheets(1) # from left to right, the sheet name can identified by 1, 2, 3 ...
# print(xlOpenSheet.Name)
# xlOpenSheet = xlOpenBook.Worksheets(2)
# print(xlOpenSheet.Name)
sheetData = xlOpenSheet.UsedRange.Value
print(list(sheetData[0])) # get the columns names
root.destroy() # if don't write this, the thinker GUI activex will not execute the next scripts which be behind of mainloop()
# gather test result rowColCount
rowColCount = []
rows = xlOpenSheet.UsedRange.Rows.Count
temp = "The totally used records in this file has: {} rows \n".format(rows)
rowColCount.append(temp)
cols = xlOpenSheet.UsedRange.Columns.Count
temp = "The totally used columns in this file has: {} cols \n".format(cols)
rowColCount.append(temp)
# for i in range(0, rows):
# for j in range(0, cols):
# print("{} ".format(sheetData[i][j]), end="")
# if j == cols-1:
# print()
getFullContent = []
for i in range(0, rows):
temp = "Display row#{} --- {} \n".format(i+1, sheetData[i])
getFullContent.append(temp)
# temp = xlOpenSheet.Range("A:B").AutoFilter
# Get the test fields which inputs from front dialog -
## define root GUI format
window = Tk()
window.wm_attributes('-topmost', 1) # make the dialog always being the top tier
window.title("Please Input Your Test Fields")
window.geometry("400x188+400+300") # note: use x not *, these four numbers means the length, width, and the distances from left and top
# window.attributes("-alpha", 0.6) #虚化，值越小虚化程度越高
window["bg"] = "#666666" # or write like this -- window.config(bg="grey")
## define text activex and boundle it into the window GUI
textExample = tkinter.Text(window, height=8, relief='raised', spacing1=2, selectborderwidth=5, bg="#CCCCCC", borderwidth=1) # set a text object from root GUI, relief - 其他可以选择的值是 "flat"，"raised"，"groove" 和 "ridge"
textExample.pack() # bundle text activex into the root object
# gather test result resultList
resultList = [] # this list could use for excel format in case it's necessary in future
def getTextInput():
result = textExample.get(1.0, tkinter.END).replace("\n", "")#or use - textExample.get("1.0", "end") instead, again, the get value will automatically add the \n as a end tag
# self.init_data_Text.get(1.0, END).strip().replace("\n", "").encode()
if (result.strip()) in sheetData[0]: #
getListItem = "Check the field - '{}' \n It's existing in the test file columns --- PASSED \n\n" .format(result)
resultList.append(getListItem)
else:
getListItem = "Check the field - '%s' \n It's NOT existing in the tested file columns --- FAILURE \n\n" %(result)
resultList.append(getListItem)
aTag = tkinter.messagebox.askyesno("Continue Test", "Continue to test field?")
if aTag == False: # don't put the quotation marks around this value
window.destroy()
else:
textExample.delete(1.0, "end") # clear the text dialog and make it is blank for next entry
textExample.focus_set() # make the mouse focus on the text activeX
def closeDialog():
window.destroy()
## define button in root GUI
btnRead=tkinter.Button(window, width=10, text="Execute", command=getTextInput, bg='green', activeforeground='green', borderwidth=5, foreground='white', anchor='center') #command绑定获取文本框内容的方法
btnExit = tkinter.Button(window, width=10, text="Exit", command=closeDialog, bg='red', borderwidth=5, anchor='center', foreground='yellow') #anchor must be n, ne, e, se, s, sw, w, nw, or center
## boundle button into the window GUI
btnRead.pack(side='left', padx=78, pady=2)
btnExit.pack(side='left')
## recall root GUI 进入消息循环
window.mainloop() # display the windows dialog
xlApp.Quit() # when executing mainloop xlApp will also participate running, so don't quit it before mainloop
# Send the mail ---------------------------------------------------------------------------------------------------------------------------
if resultList != []:
# recall outlook application
outlook = win32com.client.Dispatch('outlook.application')
# create a item for sending mail
mail = outlook.CreateItem(0) # 0: olMailItem
# reciever
mail.To = alan_yuan009@live.cn # ;alan_yuan@vfc.com
# CC - copied recipient
mail.CC = alan_yuan@vfc.com
# subject
mail.Subject = "Test Summary - an automatically generated mail, don't reply it just for your review"
print("see the resultList", resultList)
# body
content = 'Display Title Test Status ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n'
for each in resultList:
content += each
content = content + "\n\nDisplay Execle Baisc Row and Col Info ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n"
for each in rowColCount:
content += each
content = content + "\n\nDisplay All Content for Reference ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n"
for each in getFullContent:
content += each
mail.Body = "Dear All - \nSorry to disturb you! this is an automation email that generated by scripts, so don't try to reply it, if something wrong you think, please contact with \'Alan.Yuan\' to correct. Many thanks! \n\n" \
"This testing aims to test the specified excel file \"{}\" and the testing includes 'checking the specified column', 'total used range' and the 'content details', post the test summary as below, please reference - \n\n".format(objSheetName) + content + "\n\n-----------------------------------------------\nVDDA Automation Test Group"
mail.Send(
outlook.Quit()3. Some reference Code
# # Reference ---------------------------------------------------------------------------------------------------------------------------------
# # # result = getTextInput()
# # if btnRead in sheetData[0]:
# # print("Good! this column is existing this sheet!")
# # else:
# # print("Ooh! sorry it didn't find the column in this sheet ~~")
# #
#
# # # 预览单元格或者区域的值
# #print(xlOpenSheet.Cells(2,1).Value)
# # print(xlOpenSheet.Range('A1:K1').Value)
#
# # # 获取所有数据
# # xlSheet1_value = list(xlOpenSheet.Range(xlOpenSheet.Cells(1,1),xlOpenSheet.Cells(row,col)).Value)
#
# # #关闭Excel
# # xlOpenBook.Close(False) # True就是关闭该文件，并保存。不保存就是False
# # xlApp.Quit()
#
# # #获取行列范围
# # row = xlOpenSheet.UsedRange.Rows.Count
# # col = xlOpenSheet.UsedRange.Columns.Count
#
# # #获取某个Sheet页数据（页数从1开始）
# # sheet_data=xlOpenBook.Worksheets(1).UsedRange.Value
# # sheet_data[行][列]4. Another implement scenarios
This is an automated test reporting framework that can be used to generate relatively complete and professional test reports，the details code you can refer to this linkages -
Automated report framework
