How does the python deal with excel in our jobs? i think this is the common issue which asked by people in most. today i will state you what i did on this side. and hopefully this would really help you to accelerate your work effectivity.
Here I wrote serveral functions to deal with excel operations which we use more in daily routine. and the common operations include -
- Create an excel workbook
- Create excel worksheet
- Read excel data
- Write data into excel spreadsheet
- Format excel spreadsheet
and now, let’s see how I implement all of these Operations
import win32com
from win32com.client import Dispatch, constants, DispatchEx
import win32com.client, random
import os, sys, time, datetime
class VFQA_ATFW: # equal VFQA_ATFW() or VFQA_ATFW(object)
"""This is the automation test framework we defined for VFQA automation testing, in this framework we defined a sequence on how to develop a script, as well as a test report on how to generate automatically
please import this package before using this class
and recall the functions of this class sequentially
"""
def getBaseScriptParam_A(self, projectName):
"""this function will get the base parames which will offer the params for scripts to set up test report and so on
1. getRootPath - the path to save test report folder
2. getCurrentDate - get the time-stamp for time calculate
3. scriptName - get your current scripts name
4. projectName - your current project name
"""
reInitialTime = datetime.datetime.now()
os.system('taskkill /fi "imagename eq exce*" /f') # kill down all the active excel process
# 1. Create Test Report Saved Path - Note: this script should be in the same path of split folder 'AlanStudio_2020VF'
getRootPath = os.path.abspath(".").split('scripts')[0] # get current script absolute path
print("Debug only - check the scripts path >>> ", getRootPath)
getCurrentDate = time.strftime("%Y-%m-%d", time.localtime()) # get current system time
print("Debug only - check the Current Date >>> ", getCurrentDate)
# scriptName = os.path.basename(__file__).split('.')[0] # get script's Name without suffix
# print("Debug only - check the Script Name >>> ", scriptName)
# projectName = "CLM APAC"
return reInitialTime, getRootPath, getCurrentDate
# recall sample -
# projectName = "xxxxxx"
# reInitialTime, getRootPath, getCurrentDate, scriptName = getBaseScriptParam_A(projectName)
def creatReport_B(self, getRootPath, getCurrentDate, scriptName):
# 2. Judge and create a new report path
newPath = os.path.join(getRootPath, "Automation_Reports", getCurrentDate)
if os.path.exists(newPath):
print("The root of Test Report is existing already!")
else:
os.makedirs(newPath)
print(newPath)
# 3. Create an excel file and record checkpoints for current script as a report -
reportFile = os.path.join(newPath, scriptName + "_Reports" + ".xlsx")
return reportFile
# reportFile = creatReport_B(getRootPath, getCurrentDate)
def initializeReport_C(self, reportFile):
"""初始化测试报告，如果存在就打开，不存在就创建！"""
print("Check the test report and see if it's existing, if not just create it.")
xlApp = win32com.client.Dispatch("Excel.Application")
if os.path.exists(reportFile):
print("This report is already existing, just open it!")
xlOpenBook = xlApp.Workbooks.Open(reportFile)
sheetCount = xlOpenBook.Worksheets.Count
addedSheetName = "Runtime_" + str(sheetCount + 1)
xlOpenBook.Worksheets.Add().Name = addedSheetName
headerList = ['No', 'Checkpoints', 'Expected Result', 'Actual Result', 'Execuation Status', 'Screen Shot', 'Other/Remark']
for i in range(0, len(headerList)):
xlOpenBook.Worksheets(addedSheetName).Cells(1, i+1).value = headerList[i]
xlOpenBook.Save()
xlApp.Quit() # Quit excel object so that you can operate it no limited in read-only model
else:
xlBook = xlApp.Workbooks.Add() # means new create a excel workbook
xlBook.Worksheets[0].name = "Runtime_1"
headerList = ['No', 'Checkpoints', 'Expected Result', 'Actual Result', 'Execuation Status', 'Screen Shot',
'Other/Remark']
for i in range(0, len(headerList)):
xlBook.Worksheets[0].Cells(1, i+1).value = headerList[i]
xlBook.SaveAs(reportFile)
xlApp.Quit()
# initializeReport_C(reportFile=None)
def byPassed_D(self, reportFile, checkPointStatement):
xlApp = Dispatch("Excel.Application")
xlOpenBook = xlApp.Workbooks.Open(reportFile)
sheetCount = xlOpenBook.Worksheets.Count
objSheetName = "Runtime_" + str(sheetCount)
rowCount = xlOpenBook.Worksheets(objSheetName).UsedRange.Rows.Count # this is the same
objSheet = xlOpenBook.Worksheets(objSheetName)
objSheet.Cells(rowCount + 1, 1).Value = rowCount # the title occupied a line already. so here use rowCount not rowCount + 1
objSheet.Cells(rowCount + 1, 2).Value = checkPointStatement
index = random.randint(0,7)
getExpectedVal = ['It\'s supposed to be working as well as the checkpoint said', 'It works as the checkpoint state', 'It works as same as the Checkpoint statement', 'The response should be same as the checkpoint state', '正向执行结果和Checkpoint描述一致。', '和Checkpoint描述一样，能正常执行。', '如Checkpoint描述的一样，能正确获取期望的结果！', '能得到和Checkpoint一样所期待的结果！']
objSheet.Cells(rowCount + 1, 3).Value = getExpectedVal[index]
index = random.randint(0, 8)
getActualVal = ["Great...! It works as expected", "Bingo! it works as same as expected", "正确! 能看到期望的结果", "Excellent! The acutal result is just as same as the expectation", "返回的结果正如Checkpoint所期待的一样", "Great! It indeed got the right return", "正确！检查结果和Checkpoint期待的一致。", "Good! Get the right checkpoint exactly. ", "通过！检查的结果符合预期。"]
objSheet.Cells(rowCount + 1, 4).Value = getActualVal[index]
objSheet.Cells(rowCount + 1, 5).Value = "PASS"
objSheet.Cells(rowCount + 1, 6).Value = "None"
objSheet.Cells(rowCount + 1, 7).Value = "Nothing for now"
xlOpenBook.Save() # note the difference between Save and Save()
xlApp.Quit() # if use xlOpenBook.Quit() the hints will pops up for confirmation
# checkPointStatement = "Checkpoint01 - Checking the XXXX action and see if it can enter XXX page"
# byPassed_D(reportFile, checkPointStatement)
def byFailed_E(self, reportFile, checkPointStatement):
xlApp = Dispatch("Excel.Application")
xlOpenBook = xlApp.Workbooks.Open(reportFile)
sheetCount = xlOpenBook.Worksheets.Count
objSheetName = "Runtime_" + str(sheetCount)
rowCount = xlOpenBook.Worksheets(objSheetName).UsedRange.Rows.Count # this is the same
objSheet = xlOpenBook.Worksheets(objSheetName)
objSheet.Cells(rowCount + 1, 1).Value = rowCount # the title occupied a line already. so here use rowCount not rowCount + 1
objSheet.Cells(rowCount + 1, 2).Value = checkPointStatement
index = random.randint(0, 7)
getExpectedVal = ['It\'s supposed to be working as well as the checkpoint said', 'It works as the checkpoint state', 'It works as same as the Checkpoint statement', 'The response should be same as the checkpoint state', '正向执行结果和Checkpoint描述一致。', '和Checkpoint描述一样，能正常执行。', '如Checkpoint描述的一样，能正确获取期望的结果！', '能得到和Checkpoint一样所期待的结果！']
objSheet.Cells(rowCount + 1, 3).Value = getExpectedVal[index]
index = random.randint(0, 8)
getActualVal = ["Oh, my god...! It doesn't work as expected as statement", "Unfortunately! it doesn't work as the expected", "抱歉！没有得到期望的结果", "遗憾。。。没有获取期待的结果", "哦偶 :-(! 获取的结果并非Checkpoint所预期的那样。", "不好意思，没有看到预期的结果，请再次确认！", "Oops！What the return result didn't match the expectation. please double check it afterwards.", "Sorry! I didn't see what the checkpoint expected", "It's wrong. The actual result didn't match what you want."]
objSheet.Cells(rowCount + 1, 4).Value = getActualVal[index]
objSheet.Cells(rowCount + 1, 5).Value = "FAIL"
objSheet.Cells(rowCount + 1, 6).Value = "Check in manual again"
objSheet.Cells(rowCount + 1, 7).Value = "Nothing for now"
xlOpenBook.Save() # note the difference between Save and Save()
xlApp.Quit() # if use xlOpenBook.Quit() the hints will pops up for confirmation
# checkPointStatement = "Checkpoint01 - Checking the XXXX action and see if it can enter XXX page"
# byFailed_E(reportFile, checkPointStatement)
def formatReport_F(self, reportFile, scriptAuthor, reInitialTime, projectName, scriptName, testingPurpose):
if os.path.exists(reportFile):
# Step One - Create Excel.Application Object and Open an existing file and enter the specified sheet
xlApp = Dispatch("Excel.Application")
xlOpenWorkBook = xlApp.Workbooks.Open(reportFile)
sheetCount = xlOpenWorkBook.Worksheets.Count
objSheetName = "Runtime_" + str(sheetCount) # 获得sheet name 主要是为了从sheet名称上来打开指定的sheet，当然也可以用sheets[0]来表示，因为在脚本中创建的sheet都是位于第一个sheet的位置
xlOpenSheet = xlOpenWorkBook.Worksheets(objSheetName)
#Step Two - format the repor items - title
xlOpenSheet.UsedRange.Font.Name = "Gill Sans MT"
xlOpenSheet.UsedRange.Font.Size = 10
xlOpenSheet.UsedRange.Rows(1).Font.Size = 12 # Rows(1) 和 Row[1] 表示的意义不一样，前者代表第一行，后者表示第二行。这就是下标使用的作用
xlOpenSheet.UsedRange.Rows(1).Font.Bold = True # Notice this expression and it's equal the usage below in Bold and Italic
xlOpenSheet.UsedRange.Rows(1).Interior.ColorIndex = 42
# Add border and add insert a line spacing, >>> This is the second line#
xlOpenSheet.UsedRange.Borders.LineStyle = 1
# xlOpenSheet.UsedRange.Rows(1).Borders.LineStyle = -4119
xlOpenSheet.Rows[0].Insert() # insert a row at the first row place
xlOpenSheet.Rows[0].RowHeight = 5 # 设置行高; ColumnWidth 用于设置列宽
# >>>This is the first line#
xlOpenSheet.Rows[0].Insert()
xlOpenSheet.Rows[0].RowHeight = 38
xlOpenSheet.Range("A1:G1").Borders(4).LineStyle = 1 # solid line or dotted line
xlOpenSheet.Range("A1:G1").Borders(4).Weight = 3 # may be 4, 3, 1 not others
xlOpenSheet.Range("A1:G1").Merge() # merge cells for report title text
xlOpenSheet.Cells(1, 1).Value = "Execution Result for Scripts - " + scriptName # define the report title
# xlOpenSheet.Range("A1").Font.ColorIndex = 5 # or 41 for setting blue and light blue value
xlOpenSheet.Cells(1, 1).Font.Name = "Gill Sans MT"; xlOpenSheet.Cells(1, 1).Font.Size = 15; xlOpenSheet.Cells(1, 1).Font.Bold = True
# Format Result Color and make the failure record highlighted by Red font and then statistic the checkpoints result
rowsCount = xlOpenSheet.UsedRange.Rows.Count; passQty = 0; failQty = 0
for i in range(4, rowsCount + 1):
if "PASS" == xlOpenSheet.Cells(i, "E").value.upper(): # Notice here - .value is quite equal .Value
passQty += 1
xlOpenSheet.UsedRange.Cells(i, "E").Font.ColorIndex = 6 # set for font color param
xlOpenSheet.UsedRange.Cells(i, "E").Interior.ColorIndex = 10
else:
print(">>> A failure checkpoint found")
xlOpenSheet.UsedRange.Rows(i).Font.ColorIndex = 46 ; xlOpenSheet.UsedRange.Rows(i).Font.FontStyle = "Italic"; xlOpenSheet.UsedRange.Rows(i).Font.Bold = True # ColorIndex value can refer to the "https://zhidao.baidu.com/question/90240687.html".
xlOpenSheet.UsedRange.Cells(i, "E").Font.ColorIndex = 6 # set font to red
# xlOpenSheet.UsedRange.Rows(i).Interior.ColorIndex = 3 # set the selected block back graound color to red
xlOpenSheet.UsedRange.Cells(i, "E").Interior.ColorIndex = 3 # set the cell back ground color not for Font color
failQty += 1
# Add test Statistic key words, add statistic Title statement -
xlOpenSheet.Rows(3).Insert() # 在第三行向上插入一行，是以第二行的行高来定义的
xlOpenSheet.Rows(3).RowHeight = 23
xlOpenSheet.Cells(3, 1).Value = "Key Information which picked up based on this running -"
xlOpenSheet.Cells(3, 1).Font.Name = "Gill Sans MT"; xlOpenSheet.Cells(3, 1).Font.Size = 11; xlOpenSheet.Cells(3, 1).Font.Bold = True; xlOpenSheet.Cells(3, 1).Font.ColorIndex = 32; xlOpenSheet.Cells(3, 1).Font.FontStyle = "Bold Italic"
# xlOpenSheet.Range("A3:C3").Borders(4).LineStyle = 1 # solid line or dotted line xlLineStyleNone -4142 无线条。
# xlOpenSheet.Range("A1:G1").Borders(4).Weight = 3 # may be 4, 3, 1 not others
# Add test statistic key words, add the key words
xlOpenSheet.Rows(4).Insert() #在第四行插入一行，以第三行的行高来定义的
xlOpenSheet.Rows(4).RowHeight = 15 #设定第四行的行高
xlOpenSheet.Rows(5).Insert()
xlOpenSheet.Rows(6).Insert()
xlOpenSheet.Rows(7).Insert()
xlOpenSheet.Rows(8).Insert()
xlOpenSheet.Rows(9).Insert()
# the line spacing between keywords and checkpoints
xlOpenSheet.Rows(10).Insert()
xlOpenSheet.Rows(11).Insert()
xlOpenSheet.Rows(11).RowHeight = 7
# Calculate script execution duration
currentTime = datetime.datetime.now();
differ = (currentTime - reInitialTime).seconds;
h = differ // 3600; m = (differ % 3600) // 60; s = (differ % 3600) % 60 # the calculation difference between operate / and //, they would get the same value when the calculate under the all intgers, or difference
durationTime = str(h) + ' : ' + str(m) + ' : ' + str(s)
# xlOpenSheet.Range("A1:G1").Borders(4).Weight = 3 # may be 4, 3, 1 not others
xlOpenSheet.Range("A3:G3").Merge()
xlOpenSheet.Range("A4:C4").Merge()
xlOpenSheet.Range("A5:C5").Merge()
xlOpenSheet.Range("A6:C6").Merge()
xlOpenSheet.Range("A7:C7").Merge()
xlOpenSheet.Range("A8:C8").Merge()
xlOpenSheet.Range("A9:C9").Merge()
xlOpenSheet.Range("A10:D10").Merge()
xlOpenSheet.Range("E4:G10").Merge()
# xlOpenSheet.Range("A3:C3").Borders(4).LineStyle = 1
xlOpenSheet.Cells(4, "A").Value = " Project Name: " + projectName # 此处Cells（3，“A”）的写法，行以数字，列以字母标识
xlOpenSheet.Cells(5, "A").Value = " Script Name: " + scriptName
xlOpenSheet.Cells(6, "A").Value = " Duration (Hr:Min:Sec) - " + durationTime
xlOpenSheet.Cells(7, "A").Value = " Passed Checkpoints: " + str(passQty) + " ; Failed Checkpoints: " + str(failQty)
xlOpenSheet.Cells(8, "A").Value = " Automated Architecture Module: Automation Framework - VFQA"
xlOpenSheet.Cells(9, "A").Value = " Script Executor/Designer: " + scriptAuthor
xlOpenSheet.Cells(10, "A").Value = " Purpose of this script: " + testingPurpose
xlOpenSheet.UsedRange.Rows(4).Font.Name = "Gill Sans MT";
xlOpenSheet.UsedRange.Range("A4:G10").Font.Bold = False
xlOpenSheet.Range("A4:G10").Font.ColorIndex = 56
xlOpenSheet.Range("A4").GetCharacters(2, 14).Font.Bold = True # Notice - this sentense is aim to bold the part string by python method
xlOpenSheet.Range("A5").GetCharacters(2, 13).Font.Bold = True
xlOpenSheet.Range("A6").GetCharacters(2, 24).Font.Bold = True
xlOpenSheet.Range("A7").GetCharacters(2, 20).Font.Bold = True
xlOpenSheet.Range("A7").GetCharacters(27, 44).Font.Bold = True
xlOpenSheet.Range("A8").GetCharacters(2, 31).Font.Bold = True
xlOpenSheet.Range("A9").GetCharacters(2, 26).Font.Bold = True
xlOpenSheet.Range("A10").GetCharacters(2, 24).Font.Bold = True
xlOpenSheet.UsedRange.Range("A4:G10").Font.Size = 10; # xlOpenSheet.Range("B" + str(rowsCount + 4) + ":H" + str(rowsCount + 12)).Font.FontStyle = "Italic";
# How to implement AutoFit?
# xlOpenSheet.UsedRange.Columns.AutoFit()
xlOpenSheet.UsedRange.EntireColumn.AutoFit()
xlOpenSheet.UsedRange.HorizontalAlignment = 2 # maybe these value - Alignment, 1=auto | Alignment, 2=left | Alignment, 3=centre |Alignment, 4=right | |
xlOpenSheet.UsedRange.VerticalAlignment = 2 # may be these initialization - Alignment, 1=top | Alignment, 2=middle | Alignment, 3=bottom |
# Set report tileName alignment and make it align on bottom
xlOpenSheet.UsedRange.Rows(1).VerticalAlignment = 3
# Set report subTileName alignment and make it align on bottom
xlOpenSheet.UsedRange.Rows(3).VerticalAlignment = 3
# 增加最左边一列，以利排版美观
xlOpenSheet.Range("A:A").Insert() # Notice - this expression is actually equal with aove one on Insert()
xlOpenSheet.Range("A1:A" + str(rowsCount + 11)).Borders(2).LineStyle = 1; xlOpenSheet.Range("A1:A" + str(rowsCount + 12)).Borders(2).Weight = 4 # 格式化首列的最右边的实线边框
# xlOpenSheet.Cells(1, 1).Border(3).LineStyle = 1; xlOpenSheet.Cells(1, 1).Borders(3).Weight = 4; xlOpenSheet.Columns(1).ColumnWidth = 12 # Cells对象后面没有Borders属性，只能用Range来调用Border属性
xlOpenSheet.Range("A1").Borders(4).LineStyle = 1; xlOpenSheet.Range("A1:A1").Borders(4).Weight = 3; xlOpenSheet.Columns("A").ColumnWidth = 4 # 格式化单元格A1的宽度和下划线, 注意这里Range("A1")不能写成Cells("A1"), Cells参数为2，否则报错
# 去掉视图的网格线以及标题栏和标尺等属性
xlApp.ActiveWindow.DisplayGridlines = False
xlApp.ActiveWindow.DisplayHeadings = False
xlApp.ActiveWindow.DisplayFormulas = False
xlApp.ActiveWindow.DisplayWhitespace = False
xlApp.ActiveWindow.DisplayRuler = False
# rowCount = xlOpenWorkBook.Worksheets(objSheetName).UsedRange.Rows.Count
xlOpenWorkBook.Save()
print("So far, there are", xlOpenSheet.UsedRange.Rows.Count, "rows are available") # comma 连接的是任何数据类型，并且会自动加空格；plus连接的是同类型数据，不会自动加空格，这既是，和 + 连接符的区别
print("So far, there are", xlOpenSheet.UsedRange.Columns.Count, "Columns are available")
xlApp.Quit()
# formatReport_F(reportFile, scriptAuthor, reInitialTime, scriptName)
