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.

Environment etc.

Since I use Excel, I used Windows this time. I have confirmed the operation in the following environment.

OS is Windows 7 / Windows 10 (both 64bit)
Python is 2.7.13 (win32)
openpyxl-2.4.8

Basic operation of openpyxl

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).

Sample-I tried it with Excel like this

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.

I made an Excel operation (test_excel) class

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.

Supplement: When combined, it seems that the second and subsequent ones will be None. *

I'm wondering if this area can be used without much dependence on the sheet.

I wrote an Excel operation app

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.

First, get the upper and lower limits of Row corresponding to the API name with get_multi_row_data
Lick the Row that comes out next with for, and get the value by combining Colomn and Row of the given "test content"
If it matches with the test item name, return the Row

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.

sample_open (char * name), reopen result to "success"
sample_close (void), set normal result to "failure"

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).

About license

I used it below. Thank you for providing the wonderful software.

(I will write it for the time being ...) The above code is in the public domain. It's not enough code to claim copyright. However, of course, no one will undertake any damage when using it. Just be careful there.
Python itself is a PSF (Python Software Foundation) license.
For information on ↑, see [Python on Wikipedia](https://ja.wikipedia.org/wiki/Python#.E3.83.A9.E3.82.A4.E3.82.BB.E3.83.B3. E3.82.B9) is the source.
The license for openpyxl can be found in LICENCE.rst below. Sounds like MIT. https://bitbucket.org/openpyxl/openpyxl/src

that's all.