python读写excel文件

前言:

这里是为了美赛准备的快速入门教程:

这里使用python 主要是为了方便, 其余的一些小修小补的功能直接使用excel手工完成

绘图部分直接使用python 的其他绘图工具包如seaborn

各个工具的对比

OpenPyXL

2.1 新建表格和保存表格

上面的例子就是使用新建表格的方式。代码如下所示。

from openpyxl import Workbook 
# 实例化
wb = Workbook()
# 激活 worksheet
ws = wb.active
# 保存表格
wb.save("test.xlsx")

2.2 读取已有表格

针对系统中已经存在的excel表格，可以使用读取文件的方式。

from openpyxl import load_workbook

wb2 = load_workbook(path)

print(wb2.sheetnames)

2.3 创建工作簿

我们可以使用**create_sheet()**方法创建新的工作簿。

from openpyxl import Workbook
wb = Workbook()
ws = wb.active # 这里获得的ws都是第一个工作簿shell

# 新建的工作簿插到末尾
ws1 = wb.create_sheet("Myshee1") 
print(wb.sheetnames)

# 新建的工作簿插到首部
ws2 = wb.create_sheet("Mysheet2", 0) 
print(wb.sheetnames)

# 新建的工作簿插到倒数第二个位置
ws3 = wb.create_sheet("Mysheet3", -1) 
print(wb.sheetnames)

执行结果：

['Sheet', 'Myshee1']
['Mysheet2', 'Sheet', 'Myshee1']
['Mysheet2', 'Sheet', 'Mysheet3', 'Myshee1']

更改工作簿名称:

可以将现有的工作簿更改名字。系统默认的名称是“Sheet”。

from openpyxl import Workbook

wb = Workbook()

ws = wb.active
print(wb.sheetnames)

ws.title = "New Title"
print(wb.sheetnames)

查看执行结果：

['Sheet']
['New Title']

切换工作表:

if __name__ == "__main__":
    workbookName = "pacifier.xlsx"
    wb = load_workbook(workbookName)
    print(type(wb))

    ws1=wb.active
    print(type(ws1))
    ws2=wb["pacifier"]
    print(type(ws2))
    print("Success open {} ".format(workbookName))
    print("Sheet {}".format(wb.sheetnames))

<class 'openpyxl.workbook.workbook.Workbook'>
<class 'openpyxl.worksheet.worksheet.Worksheet'>
<class 'openpyxl.worksheet.worksheet.Worksheet'>
Success open pacifier.xlsx 
Sheet ['pacifier', 'Sheet1', 'Sheet2']

可以看到ws1和ws2是相同的类型

所以就是这样选择表的

删除工作表:

ws = wb["Sheet2"]
wb.remove(ws)

2.4 访问单元格

访问一个单元

可以使用两种方法来获取和修改一个单元格的值。

第一种是直接指定单元格，第二种是通过指定行和列的**cell()**方法。

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws['A4'] = 10
c=ws['A4'].value
print(c)

d=ws.cell(4,2,1000)
print(d.value)

执行结果：

10
1000

注意这里的下标从1开始, 使用range时要注意

访问多个单元

可以使用切片访问单元格范围，也可以使用行或列范围来访问多个单元，如示例所示。

from openpyxl import Workbook
from openpyxl import load_workbook
import datetime
import random

def create_workbook(filename):
    wb = Workbook()
    ws = wb.active

    ws['A1'] = 42
    ws.append([1, 2, 3])
    ws['A3'] = datetime.datetime.now()

    wb.save(filename)


if __name__ == '__main__':
    wb = Workbook()
    ws = wb.active

    for i in range(1,40):
        for j in range(1,60):
            ws.cell(i,j,random.randint(0, 60))

    # 使用切片访问
    range = ws['A1':'D10']
    print(type(range))
    print(type(range[0]))
    print(type(range[0][0]))
    for i in range:
        for j in i:
            print(j.value, end=" ")
        print("")

    # 使用列访问
    colC=ws['C']
    # print(colC)
    print(type(colC))
    print(type(colC[0]))
    for i in colC:
        print(i.value)

    col_range=ws['C:D']
    print(type(col_range))
    print(type(col_range[0]))
    
    # print(col_range)

    #使用行访问
    row10=ws[10]
    row_range=ws[5:10]

    wb.save("newExcel.xlsx")

执行结束后，查看变量情况

<class 'tuple'>
<class 'tuple'>
<class 'openpyxl.cell.cell.Cell'>
39 60 6 14
12 50 53 34 
47 46 0 51
11 18 38 30
26 38 28 51
33 46 5 24
4 9 8 15
59 55 23 3
54 6 7 56
34 58 32 5
<class 'tuple'>
<class 'openpyxl.cell.cell.Cell'>
6
53
0
38
28
5
8
23
7
32
43
14
29
32
2
55
39
37
0
37
35
10
16
28
47
0
47
7
22
23
29
39
42
46
22
21
46
25
37
<class 'tuple'>
<class 'tuple'>

• 行&列的都以tuple的形式储存

  如单独的行或列就是1维tuple, 切片返回的二维行列组就是以2维的tuple储存的

• 使用value获取数据

2.5 插入行和列

我们可以使用相关的工作簿插入行或列。

# openpyxl.worksheet.worksheet模块
insert_cols（idx，amount = 1 ）
# 在col == idx之前插入一列或多列

insert_rows（idx，amount = 1 ）
# 在row == idx之前插入一行或多行

默认值为1行或1列。例如，在第7行（在现有第7行之前）插入1行：

>>> ws.insert_rows(7)

例如，在第H列（在现有第H列之前）插入3列。

>>> ws.insert_cols(8,3)

    wb = Workbook()
    ws = wb.active


    for i in range(1,40):
        for j in range(1,60):
            ws.cell(i,j,random.randint(0, 60))

    ws.insert_cols(1)

ws.insert_cols(2, 3)

2.6 删除行和列

我们可以使用相关的工作簿删除行或列。

# openpyxl.worksheet.worksheet模块
delete_cols（idx，amount = 1 
# 从col == idx删除一列或多列

delete_rows（idx，amount = 1 ）
# 从row == idx删除一行或多行

默认值为1行或1列。例如，删除列F:H

>>> ws.delete_cols(6, 3)

删除与插入相似, 这里就不多陈述了

2.7 使用公式

我们可以使用excel中的所有数学公式，我们以SUM和AVERAGE为例进行说明。

from openpyxl import Workbook
import random

wb = Workbook()
ws = wb.active

for i in range(1,40):
    for j in range(1,60):
        ws.cell(i,j,random.randint(0, 60))

ws['F45']="=SUM(B1:F39)"
ws['F46']="=AVERAGE(B2:D30)"

wb.save("test.xlsx")

执行完毕后，打开“test.xlsx”，查看执行结果。