2openpyxl-读取表格数据插入表格数据删除表格数据冻结单元格

一固定范围读取数据：

1读取单元格数据的两种方法：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path) #读取工作簿
worksheet = workbook['sheet1'] #指定工作表
cell1 = worksheet['C1'].value #查看A1单元格的值
cell2 = worksheet.cell(row=1,column=1).value
print(cell1)
print(cell2)

2一共6行数据，只显示第二列的奇数行：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path) #读取工作簿
worksheet = workbook['sheet1'] #指定工作表
for i in range(1,6,2):
    print(i,worksheet.cell(row=i,column=2).value)

3对整表进行操作通过切片的方式切出你想要的数据

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
a = list(worksheet.values)[0:3] #从整张表中切3行数据
print(a)

4获取指定区域内单元格的数据

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#从哪一行开始到哪一行结束，从哪一列开始，到哪一列结束
range = worksheet.iter_rows(min_row=0,max_row=5,min_col=3,max_col=5)
for line in range:
    for cell in line:
        print(cell.value)

5获取每一行和每一列的方法

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#for linw in worksheet.rows:
for lie in worksheet.columns:
    for cell in lie:
        print(cell.value)

6数字转字母，字母转数字

#数字转字母，字母转数字
import openpyxl as vb
数字转字母 = vb.utils.get_column_letter(26)
print(数字转字母)
字母转数字 = vb.utils.column_index_from_string('Z')
print(字母转数字)

二动态读取数据：

1)获取最大行和最大列：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
max_line = worksheet.max_row #最大行
max_col = worksheet.max_column #最大列
print('最大行是:',max_line)
print('最大列是:',max_col)
A1单元格所在的列 = worksheet['A1'].column
A1单元格所在的行 = worksheet['A1'].row
print(A1单元格所在的列)
print(A1单元格所在的行)

2）获取指定列的数据：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
list = []
for i in worksheet['C']: #遍历c列数据
    print(i.value)
    list.append(i.value)
print(list)
print(list[1:])

3)拿到第一行的数据：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
list = []
for i in worksheet[1]: #遍历c列数据
    list.append(i.value)
print(list)

4）拿到所有行的数据

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
list = []
for line in worksheet.rows:
    for cell in line:
        list.append(cell.value)
print(list)

三写入和插入数据：

1）在excel中追加一行数据：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
list = ['运单编号','重量','内物','成本中心名称','一级部门','二级部门']
worksheet.append(list)
workbook.save(path)

2）向指定区域内写批量内容：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#先把行遍历出来
for line in worksheet['A1:H6']:
    #再把单元格遍历出来
    for cell in line:
        #向单元格写内容
        cell.value = 520
workbook.save(path)

3）在第2列插入表格，一共插入5列：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#在第二列开始插入，共插入五列
worksheet.insert_cols(idx=2,amount=5)
workbook.save(path)

4）在第二行开始插入，一共插入5行：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#在第二行开始插入，共插入五行
worksheet.insert_rows(idx=2,amount=5)
workbook.save(path)

5）从第二行开始删，一共删5行：

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#从第二行开始删，删5行
worksheet.delete_rows(2,5)
workbook.save(path)

6）从第二列开始删，一共删除5列

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#从第二列开始删，一共删5列
worksheet.delete_cols(2,5)
workbook.save(path)

四移动单元格和冻结单元格：

1）把A1到C3的表数据向下移动10行，向右移动11行

import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#向下移动5行，向右移动8行
worksheet.move_range('A1:C3',rows=10,cols=11)
workbook.save(path)

2）冻结表头（A2单元格）：

#冻结单元格
import openpyxl as vb
path = r'C:/Users/66907/Desktop/test2021.xlsx'
workbook = vb.load_workbook(path)
worksheet = workbook['sheet1']
#冻结单元格
worksheet.freeze_panes = 'A2'
workbook.save(path)