I think there are various software tests, but in my experience, the method of extracting logs from the terminal (network or serial for embedded systems) and looking at them to make a judgment was common. So, it feels like the test results are in Excel. It looks like Excel is safe ...
It seems that any scripting language is good at analyzing and judging logs. After that, I will fill in the result, but when I wonder what happened to this, there is a library called openpyxl in Python, and you can operate it with this. Also, in my case, it is not necessary to generate everything with openpyxl. I think I'll do the format and design with GUI, and have a script person write the necessary data.
This time, I lightly researched how to do such a thing and made a sample, so I will expose it including the meaning of memorandum. I hope it will be helpful to those who are trying to operate Excel.
Since I use Excel, I used Windows this time. I have confirmed the operation in the following environment.
For the basic operation, just looking at the following formula may be quite helpful. https://openpyxl.readthedocs.io/en/default/
Also, the following article of Qiita was very helpful. Thank you very much. http://qiita.com/tftf/items/07e4332293c2c59799d1
As a rough evaluation, I was able to operate it without any discomfort, as long as I selected the sheet, acquired the information of Row and Colomn, and wrote the character string on the contrary.
Installation can be done with the standard pip install openpyxl as long as you do a character string like this time. If you want to handle image files as well, the installation method seems to change, so I think you should refer to the official URL posted above.
Also, when dealing with character strings, it may be better for the user to use u "character string" and "char" properly (I think that this may differ depending on the situation, but this time we will proceed based on this I have).
This time, I tried using such a sheet as an example.
Based on this, I would like to analyze the log, judge the test result, and enter the result. The point is __ "Add the result to the appropriate place in ColumnG" __. I will expose the sample code later, but when I run the sample code,
(Although there is a tsukkomi that the normal system of close fails or is hidden, w)
Of course, if you specify Row and Column directly, it will end in two rows, but the code is (only) a little more ingenious.
This time, I tried to code the idea of deciding which result to include in "API and test contents". To do this, I wrote a code called test_excel class that provides the following API. It is less than.
#!/usr/bin/env python
# -*- coding: shift-jis -*-
#Excel related operation API
import sys
import openpyxl as px
#
#Please change these parameters appropriately according to the material.
#
#COLUMN How much to lick when licking
EXCEL_END_OF_COLUMN = "Z"
#How far do you look from the top when looking for TITLE?
EXCEL_END_OF_ROW_TITLE = 20
#How far do you look from the top when searching for items?
EXCEL_END_OF_ROW_ITEM = 30
class TestExcel:
#Specify the Excel file name when generating the class.
def __init__(self, filename):
self.filename = filename
self.workbook = px.load_workbook(filename)
self.sheet = None
def err_print(self, text):
print "\r\nERROR!:", text, "\r\n"
# select_Select the name sheet as the processing target.
def select_sheet(self, select_name):
sheetnames = self.workbook.get_sheet_names()
for name in sheetnames:
sheet_name = name
if select_name == sheet_name:
self.sheet = self.workbook[name]
print "%s selected." % select_name
return True
self.err_print("%s not found." % select_name)
return False
#Column with the name specified by name(A,B,...)Returns.
def get_column(self, name):
row_list = [int(i) for i in range(1, EXCEL_END_OF_ROW_TITLE)]
column_list = [chr(i) for i in range(ord('A'), ord(EXCEL_END_OF_COLUMN)+1)]
for row in row_list:
for column in column_list:
pos = column + str(row)
value = self.sheet[pos].value
if value == name:
return column
self.err_print( "%s not found." % name)
return None
#Multiple rows(1,2,3...)Gets the range of items created by combining.
#Return values are minimum and maximum+It is 1.
def get_multi_row_data(self, row_title, row_name):
hit = False
column = self.get_column(row_title)
if column == None:
self.err_print( "%s not found." % row_name)
return None, None
row_list = [int(i) for i in range(1, EXCEL_END_OF_ROW_ITEM)]
for row in row_list:
pos = column + str(row)
value = self.sheet[pos].value
if value != None:
if hit == True:
api_max = row
return api_min, api_max
if value == row_name and hit == False:
hit = True
api_min = row
if hit == True:
api_max = row
return api_min, api_max
else:
self.err_print( "%s not found." % row_name)
return None, None
#Specified row,Write value data to column.
def write(self, colomn, row, value):
self.sheet[colomn + str(row)] = value
#The original Excel file will be updated.
def save(self):
self.workbook.save(self.filename)
#EOF
The behavior of each API is as follows.
API | motion |
---|---|
err_print | Displaying error messages. Assuming internal use |
select_sheet | Select the sheet to be processed |
get_column | Column with the specified name (A,B,...) Is returned. This is conscious of getting the Column of the item name above the table. |
get_multi_row_data | If multiple Rows are combined, calculate the range. In the sample, the API applies to this |
write | Specified Row,Write a string to Column. |
save | Update the Excel file (otherwise the changes will not be reflected in Excel) |
The parameters have the following roles: Well, I'm sorry I'm doing it properly. I think that EXCEL_END_OF_ROW_ITEM is supposed to be done further down.
Parameters | role |
---|---|
EXCEL_END_OF_COLUMN | Column In other words, specify how far to track when licking laterally |
EXCEL_END_OF_ROW_TITLE | get_How far to search from the top when searching in column |
EXCEL_END_OF_ROW_ITEM | get_multi_row_How far to search from the top when searching by data |
Even if I'm not good at coding, it's a simple process, so if you compare the URL of the official or quoted article with the code, you can understand the process to some extent. Among them, get_multi_row_data is pretty terrible, but the point is that the place where you hit is the smallest, flag it with, and then turn it until it is not None, and the place where it is not None (or was None until the end) It feels like the maximum.
I'm wondering if this area can be used without much dependence on the sheet.
The code to operate the sample Excel using test_excel is as follows.
#!/usr/bin/env python
# -*- coding: shift-jis -*-
# test_excel.Excel result entry app using py
from datetime import datetime
from test_excel import TestExcel
#Table item name, API name to test, test name and Column(A,B,C,..)When you put
#Row of the corresponding item (1,2,3..) Is returned.
def get_test_row(excel, test_koumokumei, test_apiname, siken_naiyou_column, siken_naiyou_name):
api_min, api_max = excel.get_multi_row_data(test_koumokumei, test_apiname)
if api_min == None:
return None
for row in range(api_min, api_max):
pos = siken_naiyou_column + str(row)
value = excel.sheet[pos].value
if value == siken_naiyou_name:
return row
print("\r\nerror::%s found. but %s not found.\n\n" % (test_apiname, siken_naiyou_name))
return None
#
#Excel operation sample
#When accompanied by string manipulation"ascii"、u"test"It is assumed that the caller will use it properly.
#
if __name__ == "__main__":
filename = "API test sample.xlsx"
#excel initialization
excel = TestExcel(filename)
#Sheet selection
excel.select_sheet(u"API exam")
#Detects colimn corresponding to the item
naiyou_column = excel.get_column(u"contents of the test")
print "Test content column=", naiyou_column
kekka_column = excel.get_column(u"result")
print "Result column=", kekka_column
#Extract test information and describe the results(1)
test_koumokumei = "API"
test_api = "sample_open(char *name)"
test_name = u"Reopen"
test_row = get_test_row(excel, test_koumokumei, test_api, naiyou_column, test_name)
result = u"success"
excel.write(kekka_column, test_row, result)
print "API:", test_api, "contents of the test:", test_name, " =", test_row, "result:", result
#Extract test information and describe the results(2)
test_api = "sample_close(void)"
test_name = u"Normal system"
test_row = get_test_row(excel,test_koumokumei, test_api, naiyou_column, test_name)
result = u"Failure"
excel.write(kekka_column, test_row, result)
print "API:", test_api, "contents of the test:", test_name, " =", test_row, "result:", result
#update
excel.save()
print "complete."
#EOF
In this case, it is necessary to search for the relevant location from the API name and test content. Column can be brought in immediately with get_column of test_excel, but Row requires AND of API name and test name. I'm letting the __get_test_row function __ process it. The process is as follows.
Then, the idea is that you should specify Row and Column and write.
In the main, it is described to process the sheet "API test" of "API test sample.xlsx". It is assumed that the table shown in the figure above is written there. So, the following processing is done.
When you actually execute this python, the following message will be displayed and the result will be reflected in Excel.
API exam selected.
Test content column= D
Result column= G
API: sample_open(char *name)contents of the test:Reopen=4 Results:success
API: sample_close(void)contents of the test:Normal system=7 Results:Failure
complete.
So, if you specify the API name and test content, you can find the target location. I'm looking for it by licking it, so I feel that I can handle it even if I change the arrangement of Excel a little. Like other scripts, log judgment is a specialty of Python, so by combining it with the test_excel class this time, it will be able to fill in without permission (should).
I used it below. Thank you for providing the wonderful software.
that's all.
Recommended Posts