Python自动化之Excel
转自datawhale
https://github.com/datawhalechina/team-learning-program/edit/master/OfficeAutomation/Task02%20Python%E4%B8%8EExcel.md
Python自动化之Excel
0.包的安装
方法一:应用pip执行命令
安装openpyxl模块pip install openpyxl
方法二:在Pycharm中:File->Setting->左侧Project Interpreter
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iIDEqodA-1624014756195)(./图片/pycharm1.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pP7BZpVR-1624014756198)(./图片/pycharm2.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gN9n43xO-1624014756200)(./图片/Excel.png)]
1.Excel读取
1.1读取对应表格
- 打开已经存在的Excel表格
from openpyxl import load_workbook
exl = load_workbook(filename = 'test.xlsx')
print(exl.sheetnames)
- 根据名称获取表格
from openpyxl import load_workbook
exl_1 = load_workbook(filename = 'test.xlsx')
print(exl_1.sheetnames)
sheet = exl_1['work']
'可改为如果表中只有一个sheet可以直接用active:'
sheet = exl_1.active
- 获取Excel 内容占据的大小
print(sheet.dimensions)
1.2读取单元格
- 获取某个单元格的具体内容
cell = sheet.cell(row=1,column=2) #指定行列数
print(cell.value)
cell_1 = sheet['A1'] #指定坐标
print(cell_1.value)
- 获取单元格对应的行、列和坐标
print(cell_1.row, cell_1.column, cell.coordinate)
1.3读取多个格子的值
- 指定坐标范围
cells = sheet['A1:C8'] #A1到C8区域的值
- 指定行的值
Row = sheet[1] #第1行的值
Rows = sheet[1:2] #第1到2行的值
- 指定列的值
Column = sheet['A'] #第A列
Columns = sheet['A:C'] #第A到C列
- 指定范围的值
# 行获取
for row in sheet.iter_rows(min_row = 1, max_row = 5,
min_col = 2, max_col = 6):
print(row)
# 一列由多个单元格组成,若需要获取每个单元格的值则循环获取即可
for cell in row:
print(cell.value)
# 列获取
for col in sheet.iter_cols(min_row = 1, max_row = 5,
min_col = 2, max_col = 6):
print(col)
for cell in col:
print(cell.value)
1.4练习题
找出test_1.xlsx中sheet1表中空着的格子,并输出这些格子的坐标
from openpyxl import load_workbook
exl = load_workbood('test_1.xlsx')
sheet = exl.active
for row in sheet.iter_rows(min_row = 1, max_row = 29972,
min_col = 1, max_col = 10):
#具体查看对应表格的行列数
for cell in row:
if not cell.value:
print(cell.coordinate)
2.Excel写入
2.1写入单元格并保存
from openpyxl import load_workbook
exl = load_workbook(filename = 'test.xlsx')
sheet = exl.active
sheet['A1'] = 'hello world'
#或者cell = sheet['A1']
#cell.value = 'hello world'
exl.save(filename = 'test.xlsx') #存入原Excel表中,若创建新文件则可命名为不同名称
2.2写入行数据并保存
- 写入一行数据并保存
import xlwt
workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个sheet
sheet = workbook.add_sheet('My Worksheet')
# 写入excel
# 参数对应 行, 列, 值
sheet.write(1,0,label = 'this is test')
# 保存
workbook.save('new_test.xls')
- 写入多行数据并保存
import xlwt
exl=xlwt.Workbook(encoding='utf-8')
worksheet=exl.add_sheet('My Worksheet')
data = [['hello',22,'hi'],
['hell',23,'h'],
['he',25,'him']]
for i in range(len(data)):
for j in range(len(data[i])):
worksheet.write(i,j,data[i][j])
exl.save(filename = 'test1.xlsx')
2.3将公式写入单元格保存
sheet[‘A2’] = '=SUM(A1:D1)'
exl.save(filename='test.xlsx')
2.4插入列数据
- 插入一列
sheet.insert_cols(idx=2) #idx=2第2列,第2列前插入一列
- 插入多列
#第2列前插入5列作为举例
sheet.insert_cols(idx=2, amount=5)
2.5插入行数据
第2行前上面插入一行(或多行)
#插入一行
sheet.insert_rows(idx=2)
#插入多行
sheet.insert_rows(idx=2, amount=5)
2.6删除
- 删除多列
sheet.delete_cols(idx=5, amount=2) #第5列前删除2列
- 删除多行
sheet.delete_rows(idx=2, amount=5)
2.7移动
当数字为正即向下或向右,为负即为向上或向左
sheet.move_range('C5:F10', rows=2, cols=-3)
2.8Sheet表操作
- 创建新的sheet
from openpyxl import Workbook
workbook=Workbook()
sheet=workbook.active
workbook.save(filename='new_test.xlsx')
exl.create_sheet('new_sheet')
- 复制已有的sheet
exl.copy_worksheet(sheet)
- 修改sheet表名
sheet = exl.active
sheet.title = 'newname'
2.9创建新的Excel表
from openpyxl import load_workbook
workbook = Workbook()
sheet = workbook.active
workbook.save(filename = 'new_test.xlsx')