[Python] [Excel] Operate an Excel sheet from Python using openpyxl (using a test sheet as an example)

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.

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. excel1.JPG

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,

excel2.JPG

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

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.

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

About license

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

that's all.

Recommended Posts

[Python] [Excel] Operate an Excel sheet from Python using openpyxl (using a test sheet as an example)
Run a python script from excel (using xlwings)
Operate Excel with Python openpyxl
Try to operate an Excel file using Python (Pandas / XlsxWriter) ①
Try to operate an Excel file using Python (Pandas / XlsxWriter) ②
Operate an I2C-connected display from Python
Operate Maya from an external Python interpreter using the rpyc module
Get the formula in an excel file as a string in Python
Try to operate Excel using Python (Xlwings)
Create a dataframe from excel using pandas
Operate the schedule app using python from iphone
Run a Python file from html using Django
Summary of Excel operations using OpenPyXL in Python
I tried to operate from Postman using Cisco Guest Shell as an API server
A little bit from Python using the Jenkins API
Operate Redmine using Python Redmine
Run python from excel
Operate neutron from Python!
Operate LXC from Python
Operate Excel with Python (1)
Operate Excel with Python (2)
How to create an instance of a particular class from dict using __new__ () in python
Paste the image into an excel file using Python's openpyxl
[Python] How to output a pandas table to an excel file
Shoot time-lapse from a PC camera using Python and OpenCV
Flatten using Python yield from
Understand the function of convolution using image processing as an example
Create an instance of a predefined class from a string in Python
Create an API that returns data from a model using turicreate
Format when passing a long string as an argument of python
Try to extract a character string from an image with Python3
Read an Excel sheet and loop it line by line Python VBA
Excel file column addition and row deletion processing using Python Openpyxl
How to get a job as an engineer from your 30s
[Note] Using 16x2-digit character LCD (1602A) from Python with Raspberry Pi
I tried to output the rpm list of SSH login destination to an Excel sheet with Python + openpyxl.