In our daily testing work, our test cases are generally saved in Excel files, of course, there are some companies will use Xmind to write test cases, then why we are here just to explain the reading and writing of Excel, because Excel it is a more standardized, more commonly used test case format for automation testing, if we save the use cases in Excel, then the remaining question is what kind of odd to use to parse the use case to get the test data, below we will introduce you to the specific use of openpyxl.
openpyxl is an open source project , openpyxl module is a read and write Excel 2010 documents Python library , if you want to deal with earlier formats of Excel documents , you need to use other libraries ( such as : xlrd, xlwt , etc.), this is openpyxl compared to the shortcomings of the other modules . openpyxl is a more comprehensive tool, not only can read and modify Excel documents at the same time, but also Excel files can be set up in detail, including cell styles and other content, and even support for chart insertion, print settings, etc., the use of openpyxl can read and write xltm, xltx, xlsm, xlsx, and other types of files, and can deal with a large amount of data in the Excel file, cross-platform processing of large amounts of data is not comparable to other modules. Therefore, openpyxl has become the preferred library function to deal with the complexity of Excel.
in the use of openpyxl to master the first three objects, namely: Workbook (Workbook, an Excel file containing a number of Sheet), Worksheet (Worksheet, a Workbook has more than one Worksheet, table name recognition, such as "Sheet1 "," Sheet2 "and so on), Cell (cell, store specific data objects) three objects.
1、Install openpyxl
2、openpyxl common module
(1) read excel in a cell, the model code is as follows:
***Summary of Cell Object Knowledge Points***
The Cell object is relatively simple, with the following common properties.
- row: the row where the cell is located
- column: the column in which the cell sits
- value: the value of the cell
- coordinate: the coordinates of the cell.
2) Get all the rows and store them in the list
After storing the data in the list, we will naturally think of traversing the way to take out the test data one by one, convenient for us to do the test, the specific demonstration code is as follows:
By running the above code, we take out the stored test data one by one, each data is stored in a tuple, all the tuples form a list, the result is as follows:
3) Write data
In the daily operation of the excel process, in addition to reading data, we may also be on the excel data to modify or write data to excel, the following we will show you how to openpyxl module to write data to excel, the model code is as follows:
In this way, we will be the second line of the table in the second column of the value of "login fail", then after the completion of the modification, we need to save the changes we have made and close the file, the modification will take effect, the model code is as follows:
4) Get Maximum Rows / Get Maximum Columns
This is the whole content of this article.