Self filming tutorial 64 Python Testlink use case import split Excel tool

Case story: in the process of Testlink importing use cases in xml format, there will be a small problem:

How to make sure that the generated xml is less than 400kb as much as possible? Only excel can be cut and split,
The following case assumes a large test module of Excel, containing 1000 use cases,
We need to consider Python to achieve cutting, which is divided into five sub excel, each excel is about 200,
Of course, if the Excel of a test module only contains 200 or less use cases, there is no need to cut it.


Tip: in this case, input.xlsx and output_1 / 2 / 3 / 4 / 5.xlsx are no longer considered,
In the form of Python cut ﹣ excel.py 1000.xlsx 200, this kind of parameter input processing is adopted,
In fact, many command-line tools use the above multi parameter implementation processing, such as adb, ffmpeg command and so on.


Preparation stage
  1. Prepare an excel table with 1000 use cases, with unlimited names,
    For example, it's named: 1000.xlsx. We plan to cut it into 10 small Excel pieces, each of which is 100,
  2. Parameter input definitely involves the use of argv parameter of sys module. The following is an example of argv use:
command Parameter analysis
python cut_excel.py 1000.xlsx 200 sys.argv=["cut_excel.py","1000.xlsx","200"] is:
Parameter 0: "cut_excel.py"
Parameter 1: "1000.xlsx"
Parameter 2: "200"

Note: in fact, the compilation and packaging of cut_excel.py into cut_excel.exe is the same as the following adb.exe.
adb logcat -v time sys.argv=["adb","logcat","-v", "time"]
Namely:
Parameter 0: "adb"
Parameter 1: "logcat"
Argument 2: "-v"
Parameter 3: "time"

Python batch script form

Remember the essence of batch scripts: execute statements in batch order

# coding=utf-8

import os
import sys
import openpyxl

excel_file = sys.argv[1]  # excel files to be cut and split
limit = int(sys.argv[2])  # How much is a cutting unit limit, for example, 100 is a unit limit

excel = openpyxl.load_workbook(excel_file)
_, excel_name = os.path.split(excel_file)

sheet = excel.active
max_rows = sheet.max_row

# If the number of lines is too large, it is smaller or equal than the set unit limit, of course, it cannot be cut.
if max_rows <= limit:
    print("The cutting unit you set is too large to split, Must be less than excel Row number%s" % max_rows)
    sys.exit()  # Exit program directly

# Get all the rows and put them in a dictionary.
excel_dict = {}
row_num = 1
for row in sheet.iter_rows():
    temp_list = []
    for cell in row:
        temp_list.append(cell.value)
    excel_dict[row_num] = temp_list
    row_num = row_num + 1

# Cut (split) and fill in the new excel.
start_row = 1
for i in range(0, int(max_rows / limit) + 1):  # New multiple files
    part_excel = openpyxl.Workbook()  # Initialize an empty table
    part_sheet = part_excel.active
    if start_row != 1:  # If not the first line
        part_sheet.append(excel_dict[1])  # Write first row header row

    for j in range(start_row, (i + 1) * limit + 1):
        try:  # If it may not reach the multiple of unit num in the end, catch the exception and pass directly
            part_sheet.append(excel_dict[j])  # Write other lines
        except:
            pass

    save_file = "%s_%s_%s.xlsx" % (excel_name, start_row, (i + 1) * limit)
    part_excel.save(save_file)  # Save the form.
    print("Has been cut and saved to:%s" % save_file)

    start_row = start_row + limit  # Continue to the next unit multiple.

os.system("pause")

Python procedure oriented function form

Process function oriented programming thinking should be as follows:
How many functions do you need to do this.
It is better to encapsulate all functions as much as possible, only exposing some parameter interfaces.

# coding=utf-8

import os
import sys
import openpyxl


def _get_excel_dict(sheet):
    """Get all the rows and put them in a dictionary"""
    excel_dict = {}
    row_num = 1
    for row in sheet.iter_rows():
        temp_list = []
        for cell in row:
            temp_list.append(cell.value)
        excel_dict[row_num] = temp_list
        row_num = row_num + 1
    return excel_dict


def cut_excel(excel_file, limit):
    """Cut (split) and fill to new excel Go inside"""
    excel = openpyxl.load_workbook(excel_file)
    _, excel_name = os.path.split(excel_file)
    sheet = excel.active
    max_rows = sheet.max_row

    # If the number of lines is too small, smaller or equal than the set unit limit, of course, it cannot be cut.
    if max_rows <= limit:
        print("The cutting unit you set is too large to split, Must be less than excel Row number%s" % max_rows)
        sys.exit()  # Exit program directly

    excel_dict = _get_excel_dict(sheet)
    start_row = 1
    for i in range(0, int(max_rows / limit) + 1):  # New multiple files
        part_excel = openpyxl.Workbook()  # Initialize an empty table
        part_sheet = part_excel.active
        if start_row != 1:  # If not the first line
            part_sheet.append(excel_dict[1])  # Write first row header row

        for j in range(start_row, (i + 1) * limit + 1):
            try:  # If it may not reach the multiple of unit num in the end, catch the exception and pass directly
                part_sheet.append(excel_dict[j])  # Write other lines
            except:
                pass
        save_file = "%s_%s_%s.xlsx" % (excel_name, start_row, (i + 1) * limit)
        part_excel.save(save_file)  # Save the form.
        print("Has been cut and saved to:%s" % save_file)

        start_row = start_row + limit  # Continue to the next unit multiple.


excel_file = sys.argv[1]  # excel files to be cut and split
limit = int(sys.argv[2])  # How much is the limit per unit, for example, 100 is the limit per unit

cut_excel(excel_file, limit)
os.system("pause")

Python object oriented class form

The programming thinking of object-oriented class should be as follows:
If you are given a blank world, what kinds of things do you need in this world,
What are the common attributes and methods of these kinds of things,
What is the relationship between these kinds of things (objects) and other kinds of things (objects).
Try to encapsulate these classes and only expose the external attributes (variables) and methods (functions).

# coding=utf-8

import os
import sys
import openpyxl


class ExcelCutter(object):
    def __init__(self, excel_file):
        self.excel_file = excel_file

    def __get_excel_dict(self, sheet):
        """Get all the rows and put them in a dictionary"""
        excel_dict = {}
        row_num = 1
        for row in sheet.iter_rows():
            temp_list = []
            for cell in row:
                temp_list.append(cell.value)
            excel_dict[row_num] = temp_list
            row_num = row_num + 1
        return excel_dict

    def cut_excel(self, limit):
        """Cut (split) and fill to new excel Go inside"""

        excel = openpyxl.load_workbook(self.excel_file)
        _, excel_name = os.path.split(self.excel_file)
        sheet = excel.active
        max_rows = sheet.max_row

        # If the number of lines is too large, it is smaller or equal than the set unit limit, of course, it cannot be cut.
        if max_rows <= limit:
            print("The cutting unit you set is too large to split, Must be less than excel Row number%s" % max_rows)
            sys.exit()  # Exit program directly

        excel_dict = self.__get_excel_dict(sheet)
        start_row = 1
        for i in range(0, int(max_rows / limit) + 1):  # New multiple files
            part_excel = openpyxl.Workbook()  # Initialize an empty table
            part_sheet = part_excel.active
            if start_row != 1:  # If not the first line
                part_sheet.append(excel_dict[1])  # Write first row header row

            for j in range(start_row, (i + 1) * limit + 1):
                try:  # If it may not reach the multiple of unit num in the end, catch the exception and pass directly
                    part_sheet.append(excel_dict[j])  # Write other lines
                except:
                    pass
            save_file = "%s_%s_%s.xlsx" % (excel_name, start_row, (i + 1) * limit)
            part_excel.save(save_file)  # Save the form.
            print("Has been cut and saved to:%s" % save_file)

            start_row = start_row + limit  # Continue to the next unit multiple.


if __name__ == '__main__':
    excel_file = sys.argv[1]  # excel files to be cut and split
    limit = int(sys.argv[2])  # How much is the limit per unit, for example, 100 is the limit per unit

    e_obj = ExcelCutter(excel_file)
    e_obj.cut_excel(limit)

os.system("pause")

Download the case materials

Including: a test case to be cut, Python script
Jump to download the selfie tutorial on the official website

Operation mode and effect

The three implementation forms of the above code can be run directly, for example, save it as cut_excel.py and put it on the desktop,
cmd run: Python cut ﹣ excel.py D: \ tttt \ cut ﹣ excel \ vts.xlsx 100,
Run like this. Note that parameter 1: absolute path of Excel file,
Operation effect and split effect:


Code optimization direction

The robustness of parameter 1 and parameter 2 can be improved,
For example, parameter 2 must be a number. If it is not a number, you need to give the necessary prompt and exit the program.

For more and better original articles, please visit the official website: www.zipython.com
Selfie course (Python course of automatic test, compiled by Wu Sanren)
Original link: https://www.zipython.com/#/detail?id=46c29fcb25af42e9b7dde45109a0d0ac
You can also follow the wechat subscription number of "wusanren" and accept the article push at any time.

Tags: Python Excel less xml

Posted on Fri, 08 May 2020 11:08:43 -0400 by kurtis