Use Python to manipulate Excel's xlsx file introduction

Some time ago to do a project, you have to use Python to generate Excel files directly, and then as the requirements change, but also to the existing Excel file to read. So think about the record, this article is mainly for you to introduce Python operation Excel's xlsx file related information, the need for friends can refer to.

Objective

The previous processing of Excel read and write with XLRD/XLWT, but the two libraries have a disadvantage is that only the XLS format processing is better, the format of the end of xlsx is not. Because now everyone is using the latest version of the Office,excel format is xlsx, so at this time to continue with XLRD/XLWT processing is not appropriate, fortunately for the xlsx file read and write, we can also use OPENPYXL to operate.

I am not familiar with Excel, usually do not use, so the processing of Excel is very simple, just simple read and write, here is also a simple read and write operation, the specific advanced features, you can refer to the link address after the text.

One: Write an Excel file as follows

From OPENPYXL import Workbook from openpyxl.utils import get_column_letter  # Creates a Workbook object in memory and creates at least one worksheet WB = Workbook ()  #获取当前活跃的worksheet, the default is the first worksheet ws = Wb.active  #设置单元格的值, A1 equals 6 (the test indicates that the row and column numbers of OPENPYXL are calculated from 1), B1 equals 7 Ws.cell (Row=1, column=1). Value = 6 Ws.cell ("B1"). Value = 7  #从第2行开始, writes 9 rows and 10 columns of data with the corresponding column ordinals A, B, C, D ... for row in rang E (2,11): for  col in Range (1,11):   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"])  #保存 wb.save (filename= "/users/budong/desktop/a.xlsx")

Two: Read the Excel content just written below

 from OPENPYXL import load_workbook #打开一个workbook wb = Load_workbook (filename= "/ Users/budong/desktop/a.xlsx ") #获取当前活跃的worksheet, the default is the first worksheet #ws = wb.active #当然也可以使用下面的方法 #获取所有表格 (worksheet) name Sheets = wb.get_sheet_names () #第一个表格的名称 Sheet_first = sheets[0] #获取特定的worksheet ws = Wb.get_sheet_by_name (Sheet_first) #获 Fetch all rows and columns of the table, both of which are iterated rows = ws.rows columns = ws.columns #迭代所有的行 for row in rows:line = [Col.value for col in row] Print Line #通过坐标读取值 print Ws.cell (' A1 '). Value # A represents the column, and 1 represents the row print Ws.cell (row=1, column=1). Value