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
- 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, - Parameter input definitely involves the use of argv parameter of sys module. The following is an example of argv use:
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
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:
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.