Using Python to operate xlsx files in Excel,
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
Summary
The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.