Hello everyone, let’s explore how to operate Excel files with Python today. Similar to the word file operation library python-docx, Python also has special libraries to provide support for Excel file operations. These libraries include xlrd, xlwt, xlutils, openpyxl, and xlsxwriter. Among them, my favorite is openpyxl, which is also The main content of this explanation. Excel files are familiar to everyone. They are used in daily work and study. Let's recall, what are the steps for you to operate an Excel file? The picture below shows an Excel file. Let’s compare it and think about it.
##OK, let’s walk through it together. First, we need to create or open a Excel file, Then select a worksheet, which is the sheet in the picture above, and finally read or set the value of cell. Correspondingly, in openpyxl, there are three concepts: Workbooks, Sheets, and Cells. Workbook is an open excel file, that is, an excel workbook; Sheet is a table in the workbook, that is, a worksheet; Cell is a simple cell. openpyxl revolves around these three concepts. Regardless of reading and writing, it is "three things": open the Workbook, locate the Sheet, and operate the Cell. OK, now that we understand the basic concepts, let’s see it in action!
First of all, openpyxl is not a pre-installed library of Python 3. We need to install it manually. It is very simple to open the command line window and enter pip install openpyxl. As shown in the picture below, mine has been installed, so the output information may be different from everyone else's.
After installing openpyxl, import it through the import statement, and then execute the help method to see To see what is included in the openpyxl library, you don’t need to know it, just have an impression.
Some words in it are still very familiar, such as cell, chart, styles, workbook, worksheet, In addition to using the help method, you can also use the dir method to view all members of a library. I have marked some that we may use later in red. You can focus on them during the learning process.
The following are the general steps for operating Excel files:
1. Open or create an Excel : You need to create a workbook object. The load_workbook method is used to open an Excel, and creating an Excel is done directly by instantiating the workbook class.
2. Get a worksheet: You need to create a workbook object first, and then use the method of the object to get a worksheet object.
3. If you want to get the data in the table, you need to get a worksheet object first, and then get the Cell object representing the cell from it.
OK, let’s take a look at the actual operation in Python. The object of the operation is the position list of civil servants entered in the Hainan Examination in 2018, as shown in the figure below.
Some basic operation examples are given below, you can follow them and write them down.
Let’s show the operation again. Read the cells in the specified row and column at once. The iter_rows method is used, which means iterating by rows within the specified range of the parameter. If you want to iterate by columns, you can use the iter_cols method.
The above code shows how to operate an existing Excel file. Let’s take a look at the example of creating a new Excel file.
The generated Excel file is as shown below:
OK, do you feel that operating Excel is very easy? That’s because you have made progress in learning Python during this period. Give yourself a thumbs up! Thank you for your attention and reading. There will be more delicious programming in the future, so please enjoy it.