openpyxl

四大要素 : sheet , row, cell, col

load_workbook

打开excel表格

注意: 只能打开已经存在的表壳,不能用该方法创建表格

import openpyxl
import os

PATH = os.path.dirname(__file__)
excel_list = os.listdir(PATH)
print(excel_list[1])  # excel_1.xlsx

wk1 = openpyxl.load_workbook(excel_list[1])
print(wk1.sheetnames)  # ['Sheet1', 'Sheet2', 'Sheet3']

wk2 = openpyxl.load_workbook(filename='小明.xlsx')  # 相对路径 只需要excel名就可以
print(wk2.sheetnames)  # ['Sheet1', 'Sheet2', 'Sheet3']

wk3 = openpyxl.load_workbook(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx') # 可以是绝对路径
print(wk3.sheetnames)  # ['Sheet1', 'Sheet2', 'Sheet3']

通过sheet 名字获取表格

sheet = wk3['Sheet1']
print(sheet)  # <Worksheet "Sheet1">

创建sheet

wk3.create_sheet('Sheet4')
wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')

修改sheet名称

sheet = wk3['Sheet4']
sheet.title = '表格4'

获取表格尺寸大小

size = sheet.dimensions
print(size)  # A1:D2  可以看出有多少行 即2行

获取表格内某个各自的数据

sheet = wk3.active  # 打开活跃的/唯一的表格  (属于简写)
cell = sheet['A1']  # 获取A1格子
print(cell.value)  # 获取格子中的数据

获取某个格子的行数.列数,坐标

row = cell.row
col = cell.column
coo = cell.coordinate
print(row, col, coo)  # 1 1 A1

用行数,列数定位格子

cell = sheet.cell(row = 1, column= 3)
print(cell.value)  # c2

查看格子

1. 查看 A1 到 A4 的格子
cells = sheet["A1":"A4"]
print(cells)  # 元组套元组, 每个元组就是一个格子, 可以for循环value
# ((<Cell 'Sheet1'.A1>,), (<Cell 'Sheet1'.A2>,), (<Cell 'Sheet1'.A3>,), (<Cell 'Sheet1'.A4>,))

2. 查看 A 列的所有数据
cells = sheet['A']
print(cells)
# (<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.A2>, <Cell 'Sheet1'.A3>, <Cell 'Sheet1'.A4>)

3. 查看第 5 行
cells = sheet[5]
print(cells)
# (<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>)

4. 查看第 5 行到第 6 行
cells = sheet[5:6]
print(cells)
# ((<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>, <Cell 'Sheet1'.D5>), (<Cell 'Sheet1'.A6>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.C6>, <Cell 'Sheet1'.D6>))

5. 第一行第一列到第三行第四列
for row in sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=4):
    print(row)
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>)
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>)

6. 查看整个表格的所有行   列同理
for row in sheet.rows:
    print(row)

向某个表格内输入内容

wk3 = openpyxl.load_workbook(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx') 
sheet = wk3.active
sheet['A3'] = '哈哈'
wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')

将列表中的数据追加到表格中

sheet = wk3.active
data = [
            ['id', 'resolutionStatus(知识点解决状态)', 'color(颜色)', '2018年1月', '小东'],
            [1.0, '暂不处理', '紫色'],
            [2.0, '未解决待处理', '红色'],
            [3.0, '已验证', '绿色'],
            [4.0, '临时解决方案', '橙色']
        ]
for row in data:
    sheet.append(row)
wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')

插入删除行或列

sheet.insert_cols(idx=2, amount=2)  # 默认在第二列的左边插入空白列, amount用来指定插入几列
sheet.insert_rows(idx=2, amount=3)  # 默认在第二行的上边插入空白列, amount用来指定插入几列
sheet.delete_cols(idx=2, amount=3)  # 默认删掉第二列, amount用来指定删除几列
sheet.delete_rows(idx=2, amount=3)  # 默认删掉第二行, amount用来指定删除几行
wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')

移动表格到指定位置

将面积为 c1到D4 的表格 向下移动2行, 向左移动2列
sheet.move_range('c1 : D4', row=2, cols=-2)
wk3.save(filename='D:\study\学习代码\openpyxl_t\excel_1.xlsx')

创建新的excel

wk4 = openpyxl.Workbook()  # 该方法用来创建新的excel
sheet = wk4.active
sheet.title = '表格1'
wk4.save(filename='新的表格名字.xlsx')