python- excel 创建/写入/删sheet+花式遍历

前言

  如果您觉得有用的话，记得给博主点个赞，评论，收藏一键三连啊，写作不易啊^ _ ^。
  而且听说点赞的人每天的运气都不会太差，实在白嫖的话，那欢迎常来啊!!!

---

python- excel 创建/写入/删sheet+花式遍历

1. excel 创建

创建一个excel放到内存里
wb = Workbook()
获取当前活动的sheet
sheet = wb.active
修改sheet名称
sheet.title = ‘修改的名称’
保存到本地
wb.save(路径/xx.xlsx)

2. 写入excel

打开已有文件
wb = load_workbook(path)
获取当前活动的sheet
sheet = wb.active
写入方式一、数据可以直接分配到单元格(可以输入公式)
sheet[‘A1’] = ‘你好啊’
写入方法二、可以附加行，从第一列开始附加(从最下空白处，最左开始)(可以输入多行)
sheet.append([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])
sheet.append([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])

写入方式三、python 类型会自动转换
sheet[‘B1’] = datetime.datetime.now().strftime(‘%Y-%m-%d’)
创建一个新的sheet
newSheet = wb.create_sheet(“NewSheet”)
保存
wb.save(path)

3. 创建写入excel demo实战

import datetime
import os
import string
import random
from openpyxl import Workbook, load_workbook


# 返回指定目录下的所有文件和目录名
def listdir(file_path):
    paths = os.listdir(file_path)
    print(f'返回指定目录【{file_path}】下的所有文件和目录名:{os.listdir(file_path)}')
    return paths


def deleteFile(path):
    # 删除该目录下所有文件
    files = listdir(path)
    for file_name in files:
        # 函数会根据操作系统自动使用适当的分隔符,生成路径
        file_path_del = os.path.join(path, file_name)
        try:
            if os.path.isfile(file_path_del):  # 检查是否为文件
                os.remove(file_path_del)  # 删除文件
                print(f"Deleted {file_path_del}")
            else:
                print(f"Skipped {file_path_del} (not a file)")
        except OSError as error:
            print(f"Error while deleting {file_path_del}: {error}")


# 生成随机字符串
def createNum():
    v = "".join(random.sample(string.ascii_lowercase + string.digits, 10))
    return v


"""
 创建excel
 @:param sheetName Sheet名字
 @:param path 持久化路径
"""
def createExcel(sheetName, path):
    # 创建一个excel放到内存里
    wb = Workbook()
    # 获取当前活动的sheet
    sheet = wb.active
    print(f'打印sheet表名:{sheet.title}')
    # 修改sheet名称
    sheet.title = sheetName
    print(f'打印修改后的sheet表名:{sheet.title}')
    # 持久化到本地
    wb.save(path)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

if __name__ == '__main__':
    try:
        # 创建excel
        excelName = f'测试-{createNum()}.xlsx'
        sheetName = '测试1'
        file_path = 'D:\pythonExcel'
        # 清理 D:\pythonExcel下所有文件
        deleteFile(file_path)
        path = os.path.join(file_path, excelName)
        createExcel(sheetName, path)
        # 打开已有文件，并写入数据
        wb = load_workbook(path)
        # 获取当前活动的sheet
        sheet = wb.active
        # 方式一、数据可以直接分配到单元格(可以输入公式)
        sheet['A1'] = '你好啊'
        # 方法二、可以附加行，从第一列开始附加(从最下空白处，最左开始)(可以输入多行)
        for i in range(1, 10):
            sheet.append([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])
            sheet.append([6, 7, 8, 9, 10, 6, 7, 8, 9, 10])
        # 方式三、python 类型会自动转换
        sheet['B1'] = datetime.datetime.now().strftime('%Y-%m-%d')

        # 创建一个新的sheet
        newSheet = wb.create_sheet("NewSheet")
        # 设置新sheet为活动sheet
        wb.active = newSheet
        newSheet.append(['测试1', '测试2', '测试3', '测试4', '测试5', '测试6', '测试7', '测试8', '测试9', '测试10'])

        for i in range(1, 10):
            newSheet.append([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])
            newSheet.append([6, 7, 8, 9, 10, 6, 7, 8, 9, 10])
        wb.save(path)

    except Exception as e:
        print(f"Exception: {e}")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

执行测试:

4. 删除sheet

wb.remove(sheet)

demo实战:

import os
from openpyxl import load_workbook


def listdir(file_path):
    paths = os.listdir(file_path)
    print(f'返回指定目录【{file_path}】下的所有文件和目录名:{os.listdir(file_path)}')
    return paths


if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        print('删除sheet:测试1')
        sheet = wb['测试1']
        wb.remove(sheet)
        wb.save(file_path)
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')

    except Exception as e:
        print(f"Exception : {e}")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

5. excel 花式遍历 demo实战

import os
from openpyxl import load_workbook


def listdir(file_path):
    paths = os.listdir(file_path)
    print(f'返回指定目录【{file_path}】下的所有文件和目录名:{os.listdir(file_path)}')
    return paths

1
2
3
4
5
6
7
8
9

5.1. 获取 A1的值

if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        # 获取 A1的值
        print('======================获取A1的值')
        print(f'A1:{sheet["A1"].value}')
    except Exception as e:
        print(f"Exception : {e}")


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

5.2. 获取指定列的切片数据，获取 B1到B5的值

if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        # 获取指定列的切片数据，获取 B1到B5的值
        print('======================获取指定列的切片数据，获取 B1到B5的值')
        for cell in sheet["B1:B5"]:
            print(f'{cell[0].coordinate}:{cell[0].value}')
            # for row in cell:
            #     print(f'{row.coordinate}:{row.value}')
    except Exception as e:
        print(f"Exception : {e}")




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

5.3. 循环整个excel的这个sheet

if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        print('======================循环整个excel的这个sheet')
        for row in sheet:
            for cell in row:
                print(cell.value, end=',')
            print()
    except Exception as e:
        print(f"Exception : {e}")




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

5.4. 遍历指定行&列:从第2行开始至第5行，每行打印5列

if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        print('======================遍历指定行&列:从第2行开始至第5行，每行打印5列')
        for row in sheet.iter_rows(min_row=2, max_row=5,  max_col=5):
            for cell in row:
                print(cell.value, end=',')
            print()
    except Exception as e:
        print(f"Exception : {e}")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5.5. 遍历指定列:从第2列第4列

if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        print('======================遍历指定列:从第2列第4列')
        for row in sheet.iter_rows(min_col=2,  max_col=4):
            for cell in row:
                print(cell.value, end=',')
            print()
    except Exception as e:
        print(f"Exception : {e}")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5.5. 按列循环

if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        print('======================按列循环')
        for row in sheet.columns:
            for cell in row:
                print(cell.value, end=',')
            print()
    except Exception as e:
        print(f"Exception : {e}")




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

6. excel 花式遍历完整demo

import os
from openpyxl import load_workbook


def listdir(file_path):
    paths = os.listdir(file_path)
    print(f'返回指定目录【{file_path}】下的所有文件和目录名:{os.listdir(file_path)}')
    return paths


if __name__ == '__main__':
    path = 'D:\pythonExcel'
    try:
        fileName = listdir(path)
        file_path =os.path.join(path,  fileName[0])
        # 打开已有文件
        wb = load_workbook(file_path)
        # 获取所有 sheet
        print(f'获取所有sheet:{wb.sheetnames}')
        # 获取【NewSheet】 sheet页
        sheet = wb['NewSheet']
        # 获取 A1的值
        print('======================获取A1的值')
        print(f'A1:{sheet["A1"].value}')
        # 获取指定列的切片数据，获取 B1到B5的值
        print('======================获取指定列的切片数据，获取 B1到B5的值')
        for cell in sheet["B1:B5"]:
            print(f'{cell[0].coordinate}:{cell[0].value}')
            # for row in cell:
            #     print(f'{row.coordinate}:{row.value}')
        print('======================循环整个excel的这个sheet')
        for row in sheet:
            for cell in row:
                print(cell.value, end=',')
            print()
        print('======================遍历指定行&列:从第2行开始至第5行，每行打印5列')
        for row in sheet.iter_rows(min_row=2, max_row=5,  max_col=5):
            for cell in row:
                print(cell.value, end=',')
            print()
        print('======================遍历指定列:从第2列第4列')
        for row in sheet.iter_rows(min_col=2,  max_col=4):
            for cell in row:
                print(cell.value, end=',')
            print()
        print('======================按列循环')
        for row in sheet.columns:
            for cell in row:
                print(cell.value, end=',')
            print()
    except Exception as e:
        print(f"Exception : {e}")




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56