How to use Python to operate Excel xlsx files

Some time ago, I had to use Python to directly generate an Excel file. later, as the demand changes, I had to read the existing Excel file. So I want to record it. This article mainly introduces you to the Python operation of the xlsx file in Excel. For more information, see. Some time ago, I had to use Python to directly generate an Excel file. later, as the demand changes, I had to read the existing Excel file. So I want to record it. This article mainly introduces you to the Python operation of the xlsx file in Excel. For more information, see.

Preface

Previously, xlrd/xlwt was used for reading and writing excel files. However, the two databases only process the xls format better and the format ending with xlsx won't work. Because all of you are using the latest version of office, and the excel format is xlsx, it is not appropriate to continue to use xlrd/xlwt to process the xlsx file. Fortunately, the xlsx file is read and written, we can also use openpyxl for operations.

I am not familiar with excel, and I am not very familiar with it at ordinary times. Therefore, the processing of excel is very simple, but simple reading and writing. Here we demonstrate simple reading and writing operations, specific advanced functions, you can refer to the link address after the document.

I. write an excel file as follows:

From openpyxl import Workbook from openpyxl. utils import get_column_letter # Create a workbook object in the memory, and at least one worksheet wb = Workbook () # obtain the currently active worksheet. by default, the first worksheet ws = wb. active # set the cell value. A1 is equal to 6 (the test shows that the row and column numbers of openpyxl are calculated from 1), and B1 is equal to 7 ws. cell (row = 1, column = 1 ). value = 6 ws. cell ("B1 "). value = 7 # write 10 columns of data in 9 rows starting from row 2nd. the values are column numbers A, B, C, and D... for row in range (): for col in range (): ws. cell (row = row, column = col ). value = get_column_letter (col) # you can use append to insert a row of data ws. append (["I", "you", "she"]) # save wb. save (filename = "/Users/budong/Desktop/a.xlsx ")

II. read the content of the newly written excel file as follows:

From openpyxl import load_workbook # Open a workbook wb = load_workbook (filename = "/Users/budong/Desktop/a.xlsx") # obtain the active worksheet, the first worksheet # ws = wb by default. active # of course, you can also use the following method # obtain the name of all tables (worksheet) sheets = wb. get_sheet_names () # Name of the first table sheet_first = sheets [0] # obtain a specific worksheet ws = wb. get_sheet_by_name (sheet_first) # obtain all rows and columns in the table. both of them are iteratable rows = ws. rows columns = ws. columns # iterate all rows for row in rows: line = [col. value for col in row] print line # read the value print ws through coordinates. cell ('A1 '). value # A indicates the column, and 1 indicates the row print ws. cell (row = 1, column = 1 ). value

The above is a detailed description of the xlsx file for Excel operations using Python. For more information, see other related articles in the first PHP community!