使用Python openpyxl模块读写Excel文件

---

Python 提供了 openpyxl 模块用于操作 Excel 文件。

如何创建 Excel 文件、如何写入、读取等都可以通过此模块实现。

要安装 openpyxl 模块，可以在命令提示符中输入此命令

pip install openpyxl

如果要指定工作表标题名称

示例代码

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet_title = my_sheet.title
print("My sheet title: " + my_sheet_title)

输出

My sheet title:Sheet

更改标题名称

示例代码

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet.title = "My New Sheet"
print("sheet name is : " + sheet.title)

输出

sheet name is : My New Sheet

插入数据或写入 Excel 工作表

示例代码

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
c1 = my_sheet.cell(row = 1, column = 1)
c1.value = "Aadrika"
c2 = my_sheet.cell(row= 1 , column = 2)
c2.value = "Adwaita"
c3 = my_sheet['A2']
c3.value = "Satyajit"
# B2 = column = 2 & row = 2.
c4 = my_sheet['B2']
c4.value = "Bivas"
my_wb.save("C:\Users\TP\Desktop\Book1.xlsx")

输出

在工作簿中添加工作表

示例代码

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_wb.create_sheet(index = 1 , title = "new sheet")
my_wb.save("C:\Users\TP\Desktop\Book1.xlsx")

输出

显示总行数。

示例代码

import openpyxl
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
print(my_sheet_obj.max_row)

输出

2

显示特定单元格的值

示例代码

import openpyxl
# Give the location of the file
My_path = "C:\Users\TP\Desktop\Book1.xlsx"
wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_cell_obj = my_sheet_obj.cell(row = 1, column = 1)
print(my_cell_obj.value)

输出

Aadrika

显示总列数

示例代码

import openpyxl
# Give the location of the file
My_path = "C:\Users\TP\Desktop\Book1.xlsx"
My_wb_obj = openpyxl.load_workbook(path)
my_sheet_obj = my_wb_obj.active
print(sheet_obj.max_column)

输出

2

显示所有列名

示例代码

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
# workbook object is created
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column
for i in range(1, my_max_col + 1):
   my_cell_obj = my_sheet_obj.cell(row = 1, column = i)
   print(my_cell_obj.value) 

输出

Aadrika Adwaita

显示第一列的值

示例代码

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_row = my_sheet_obj.max_row
for i in range(1, my_row + 1):
   cell_obj = my_sheet_obj.cell(row = i, column = 1)
   print(cell_obj.value)

输出

Aadrika
Satyajit

打印特定行值

示例代码

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column
for i in range(1, my_max_col + 1):
   cell_obj = my_sheet_obj.cell(row = 2, column = i)
   print(cell_obj.value, end = " ")

输出

Satyajit Bivas