Python对Excel的常用操作

Y国惠 发布于 1 月 18 日

本文是Python对Excel常用操作的演示

测试环境：

Python 3.7
openpyxl 3.0.5
Windows 10 专业版（CPU:i5-8500 RAM:16GB）

使用到的Python包：

1.OS(内置,无需安装)
2.random(内置,无需安装)
3.openpyxl 安装：

pip3 install openpyxl==3.0.5

运行代码

# 1.解压'excel_oparetion.rar'后
# 2.
python3 excel_base_op.py

Excel在openpyxl中对应的的对象

工作簿(workbook)： 一个 Excel 电子表格文档;
工作表(sheet)： 每个工作簿可以包含多个表, 如： sheet1， sheet2等;
活动表(active sheet)： 用户当前查看的表;
列(column)： 列地址是从 A 开始的;
行(row)： 行地址是从 1 开始的;
单元格(cell)： 特定行和列的方格;

openpyxl的基础操作（建议一步一步跟着操作哦，虽然后边有有完整代码）

导包

import random
import openpyxl

读取Excel:
1.打开现有工作表,获取workbook对象

file_name = "练习.xlsx"
wb = openpyxl.load_workbook(file_name)

2.workbook的基本属性

# 获取表格中所有sheet的名字，返回一个列表
sheet_name_lis = wb.sheetnames
print("文件 %s, 拥有sheet的名字列表为:%s" % (file_name, sheet_name_lis))
# 文件 练习.xlsx, 拥有sheet的名字列表为:['Sheet1', 'Sheet2']

3.获取sheet对象

sheet_name_1 = wb.sheetnames[0]
sheet_1 = wb[sheet_name_1]

4.sheet的基本属性

print("%s最大列数：%s, 最大行数：%s" % (sheet_1.title,sheet_1.max_column, sheet_1.max_row))
# Sheet1最大列数：5, 最大行数：10

5.获取cell对象

cell_1_2 = sheet_1.cell(1, 2)

6.cell的基本属性

print("单元格%s, 值：%s, 横坐标：%s，纵坐标%s" % (
            cell_1_2.coordinate, cell_1_2.value, cell_1_2.row, cell_1_2.column
        ))
# 单元格B1, 值：我是B1, 横坐标：1，纵坐标2

7.多种方式访问cell

# 1.使用列访问单元格
# 单列
col_b = sheet_1['B']
print("表 %s 列B的值：%s" % (sheet_1.title, [cel.value for cel in col_b]))
# 表 Sheet1 列B的值：['我是B1', '我是B2', '我是B3', '我是B4', '我是B5', '我是B6', '我是B7', '我是B8', '我是B9', '我是B10']

# 多列
# col_range = sheet_1['C:D']

# 2.使用行访问单元格
# 单行
row_3 = sheet_1[3]
print("表 %s 行3的值：%s" % (sheet_1.title, [r.value for r in row_3]))
# 表 Sheet1 行3的值：['我是A3', '我是B3', '我是C3', '我是D3', '我是E3']

# 多行
# row_range = sheet_1[2:5]

二·写入Excel:

1.创建新的excel文件，当然你也可以在当前文件修改

# 注意Workbook首字母是大写
new_wb = openpyxl.Workbook()

2.创建新的sheet

# 获取当前sheet，默认第一个
new_sheet_1 = new_wb.active
# 可以重命名sheet, 默认“sheet”
new_sheet_1.title = "我是新sheet"
# 或者新创建一个sheet
# new_sheet_1 = new_wb.create_sheet("我是title", 0)

3.插入数据

# 1.设定单元格的值，三种方式
new_sheet_1['A2'] = "第一种"
new_sheet_1.cell(row=2, column=2).value = "第二种"
new_sheet_1.cell(row=2, column=3, value="第三种")
# 2.整行写
# 添加一行到当前sheet的最底部（即逐行追加从第一行开始） 必须是list,tuple,dict,range,generator类型的。
# 1,如果是list,将list从头到尾顺序添加。
# 2,如果是dict,按照相应的键添加相应的键值。
new_sheet_1.append([0, 1, 2, 3])
new_sheet_1.append({"A": "This is A", "C": "This is C"})
new_sheet_1.append({1: "This is A", 3: "This is C"})

# 使用列写入单元格
col_c = new_sheet_1['C']
for cel in col_c:
    cel.value = random.randint(1, 50)

col_range = new_sheet_1['C:D']
for _cel in col_range:
    for c in _cel:
        c.value = random.randint(1, 50)

# 使用行写入单元格
# row_10 = new_sheet_1[10]
# row_range = new_sheet_1[2:9]

4.保存(必须要保存！！)

new_wb.save("我是新表.xlsx")
# 如果报这个错，大概率是你还开着这个表，关掉即可
# PermissionError: [Errno 13] Permission denied: '我是新表.xlsx'

三·其他
1.创建sheet的副本

copy_sheet = new_wb.copy_worksheet()

2.设置cell的背景色(RRGGBB)

from openpyxl.styles import PatternFill
# 生成样式
green_fill = PatternFill(fgColor="AACF91", fill_type="solid")
orange_fill = PatternFill(fgColor="FFC125", fill_type="solid")
# 填充样式
new_sheet_1.cell(2, 2).fill = green_fill
new_sheet_1.cell(2, 3).fill = orange_fill
new_wb.save("我是新表.xlsx")

四.练习
根据 '练习.xlsx' 文件sheet2中的H列分组，将其中值为1的行填充在 该文件 新建的sheet，将值为2的行填充在 新文件。

五.演示的完整

import random
import openpyxl
from openpyxl.styles import PatternFill

file_name = "练习.xlsx"
wb = openpyxl.load_workbook(file_name)

# 获取表格中所有sheet的名字，返回一个列表
sheet_name_lis = wb.sheetnames
print("文件 %s, 拥有sheet的名字列表为:%s" % (file_name, sheet_name_lis))
# 文件 练习.xlsx, 拥有sheet的名字列表为:['Sheet1', 'Sheet2']

sheet_name_1 = wb.sheetnames[0]
sheet_1 = wb[sheet_name_1]

print("%s最大列数：%s, 最大行数：%s" % (sheet_1.title, sheet_1.max_column, sheet_1.max_row))
# Sheet1最大列数：5, 最大行数：10

cell_1_2 = sheet_1.cell(1, 2)
print("单元格%s, 值：%s, 横坐标：%s，纵坐标%s" % (
            cell_1_2.coordinate, cell_1_2.value, cell_1_2.row, cell_1_2.column
        ))
# 单元格B1, 值：我是B1, 横坐标：1，纵坐标2

# 1.使用列访问单元格
# 单列
col_b = sheet_1['B']
print("表 %s 列B的值：%s" % (sheet_1.title, [cel.value for cel in col_b]))
# 表 Sheet1 列B的值：['我是B1', '我是B2', '我是B3', '我是B4', '我是B5', '我是B6', '我是B7', '我是B8', '我是B9', '我是B10']

# 多列
# col_range = sheet_1['C:D']

# 2.使用行访问单元格
# 单行
row_3 = sheet_1[3]
print("表 %s 行3的值：%s" % (sheet_1.title, [r.value for r in row_3]))
# 表 Sheet1 行3的值：['我是A3', '我是B3', '我是C3', '我是D3', '我是E3']

# 多行
# row_range = sheet_1[2:5]


# 注意Workbook首字母是大写
new_wb = openpyxl.Workbook()

# 获取当前sheet，默认第一个
new_sheet_1 = new_wb.active
# 可以重命名sheet, 默认“sheet”
new_sheet_1.title = "我是新sheet"
# 或者新创建一个sheet
# new_sheet_1 = new_wb.create_sheet("我是title", 0)

# 1.设定单元格的值，三种方式
new_sheet_1['A2'] = "第一种"
new_sheet_1.cell(row=2, column=2).value = "第二种"
new_sheet_1.cell(row=2, column=3, value="第三种")
# 2.整行写
# 添加一行到当前sheet的最底部（即逐行追加从第一行开始） 必须是list,tuple,dict,range,generator类型的。
# 1,如果是list,将list从头到尾顺序添加。
# 2,如果是dict,按照相应的键添加相应的键值。
new_sheet_1.append([0, 1, 2, 3])
new_sheet_1.append({"A": "This is A", "C": "This is C"})
new_sheet_1.append({1: "This is A", 3: "This is C"})

# 使用列写入单元格
col_c = new_sheet_1['C']
for cel in col_c:
    cel.value = random.randint(1, 50)

col_range = new_sheet_1['C:D']
for _cel in col_range:
    for c in _cel:
        c.value = random.randint(1, 50)

# 使用行写入单元格
# 单行
# row_10 = new_sheet_1[10]
# 多行
# row_range = new_sheet_1[2:9]

new_wb.save("我是新表.xlsx")
# 如果报这个错，大概率是你还开着这个表，关掉即可
# PermissionError: [Errno 13] Permission denied: '我是新表.xlsx'

# 生成样式
green_fill = PatternFill(fgColor="AACF91", fill_type="solid")
orange_fill = PatternFill(fgColor="FFC125", fill_type="solid")
# 填充样式
new_sheet_1.cell(2, 2).fill = green_fill
new_sheet_1.cell(2, 3).fill = orange_fill
new_wb.save("我是新表.xlsx")