Introduction to using Python to operate Excel xlsx files

When I was working on a project some time ago, I had to use Python to directly generate Excel files. Later, as the needs changed, I had to read the existing Excel files. So I wanted to record it. This article mainly introduces you to the relevant information on Python's operation of Excel's xlsx file. Friends in need can refer to it.

Preface

I used to use xlrd/xlwt when dealing with reading and writing excel, but these two libraries have the disadvantage that they only work with The format of xls is handled better, but the format ending with xlsx cannot be processed. Since everyone is using the latest version of office and the format of excel is xlsx, it is not appropriate to continue to use xlrd/xlwt for processing at this time. Fortunately, we can also use openpyxl to read and write xlsx files. operate.

I am not familiar with excel, and I don’t use it very often, so the processing of excel is very simple, just simple reading and writing. What is demonstrated here is also a simple reading and writing operation. For specific advanced functions, you can refer to The link address after the article.

One: Write an excel file as follows

 from openpyxl import Workbook
 from openpyxl.utils import get_column_letter
 
 # 在内存中创建一个workbook对象，而且会至少创建一个 worksheet
 wb = Workbook()
 
 #获取当前活跃的worksheet,默认就是第一个worksheet
 ws = wb.active
 
 #设置单元格的值，A1等于6(测试可知openpyxl的行和列编号从1开始计算)，B1等于7
 ws.cell(row=1, column=1).value = 6
 ws.cell("B1").value = 7
 
 #从第2行开始，写入9行10列数据，值为对应的列序号A、B、C、D...
 for row in range(2,11):
  for col in range (1,11):
   ws.cell(row=row, column=col).value = get_column_letter(col)
 
 #可以使用append插入一行数据
 ws.append(["我","你","她"])
 
 #保存
 wb.save(filename="/Users/budong/Desktop/a.xlsx")

Two: Read the excel content just written as follows

 from openpyxl import load_workbook
 
 #打开一个workbook
 wb = load_workbook(filename="/Users/budong/Desktop/a.xlsx")
 
 #获取当前活跃的worksheet,默认就是第一个worksheet
 #ws = wb.active
 
 #当然也可以使用下面的方法
 
 #获取所有表格(worksheet)的名字
 sheets = wb.get_sheet_names()
 #第一个表格的名称
 sheet_first = sheets[0]
 #获取特定的worksheet
 ws = wb.get_sheet_by_name(sheet_first)
 #获取表格所有行和列，两者都是可迭代的
 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表示列,1表示行
 print ws.cell(row=1, column=1).value

The above is the detailed content of Introduction to using Python to operate Excel xlsx files. For more information, please follow other related articles on the PHP Chinese website!