python处理excel表格(二)

创建excel文件

from openpyxl import Workbook
wb = Workbook()
wb.save('./test.xlsx')

查看所有的sheet表格

from openpyxl import open
wb = open('./test.xlsx')
sheets = wb.sheetnames
print(sheets)

创建sheet表格

from openpyxl import open
wb = open('./test.xlsx')
wb.create_sheet('学生表')   # 还可以选择创建的sheet表格的顺序，只需要在括号里面加入数字参数，从0开始
wb.save('./test.xlsx')

修改表名称

from openpyxl import open
wb = open('./test.xlsx')
wb['Sheet'].title = "教师表"
wb.save('./test.xlsx')

删除sheet表格

from openpyxl import open
wb = open('./test.xlsx')
wb.remove(wb['教师表'])
wb.save('./test.xlsx')

给第一个单元格写入数据

from openpyxl import open
wb = open('./school.xlsx')
teacher_sheet = wb['教师表']
# 根据单元格名称来添加内容
teacher_sheet['A1'] = '张老师'
wb.save('./school.xlsx')
# 根据sheet坐标来添加内容
teacher_sheet.cell(2,1).value = "王老师"

查找excel里面的数据

from openpyxl import open
wb = open('./school.xlsx')
sheet = wb['学生表']
# 查看学生表第一行的所有数据
for data in sheet['1']:
  print(data.value,end="")
# 查看学生表第b列的所有数据
for data in sheet['B']:
  print(data.value,end="")

修改excel里面的数据

from openpyxl import open
wb = open('./school.xlsx')
sheet = wb['学生表']
sheet['A1'] = "哈哈哈"
wb.save('./school.xlsx')

删除excel里面的数据

# 删除第三行数据
from openpyxl import open
wb = open('./school.xlsx')
sheet = wb['学生表']
sheet.delete_rows(3,1)
# 删除第三列数据
sheet.delete_cols(3,1)
# 在第五行前插入一行单元格
sheet.insert_rows(5,1)
# 在第五列前插入一列单元格
sheet.insert_cols(5,1)