day8-xlrd和xlwt操作Excel文件
一、xlrd获取文件内容
1.打开Excel文件
wb = xlrd.open_workbook('files/data1.xls')
2.获取所有工作表的表名
工作簿.sheet_names()
names = wb.sheet_names()
print(names)
3.获取工作表
1)获取所有的工作表: 工作簿.sheets()
all_sheet = wb.sheets()
print(all_sheet)
2)获取一个工作表:
工作簿.sheet_by_name(表名)
工作簿.sheet_by_index(下标)
stu_sheet = wb.sheet_by_name('students')
print(stu_sheet)
tea_sheet = wb.sheet_by_index(1)
print(tea_sheet)
4.获取最大行数和最大列数
工作表.nrows
工作表.ncols
m_r = stu_sheet.nrows
m_c = stu_sheet.ncols
print(m_r, m_c)
5.按行或者按列获取数据
1)按行获取数据
工作表.row_values(行下标) - 获取指定行对应的所有数据(行下标是从0开始的)
工作表.row_values(行下标, 起始列下标) - 获取指定行中指定列开始所有的数据
工作表.row_values(行下标, 起始列下标, 终止列下标) - 获取指定行中指定范围的所有数据(终止列下标对应的数据取不到)
result = stu_sheet.row_values(3)
print(result) # ['小花', '女', 22.0, 95.0]
result = stu_sheet.row_values(2, 1, 3)
print(result)
2)按列获取数据
工作表.col_values(列下标)
工作表.col_values(列下标, 起始行下标)
工作表.col_values(列下标, 起始行下标, 终止行下标)
result = stu_sheet.col_values(0)
print(result)
result = stu_sheet.col_values(3, 1)
print(result)
练习1:一行一行的获取整个学生表中所有的数据
for x in range(m_r):
print(stu_sheet.row_values(x))
练习2:一列一列的获取所有学生的信息
for x in range(m_c):
print(stu_sheet.col_values(x, 1))
6.获取单元格
1)工作表.cell(行下标, 列下标) - 获取指定位置对应的单元格对象
2)单元格.value - 获取单元格中的数据
print(stu_sheet.cell(1, 0))
print(stu_sheet.cell(1, 2).value)
3)工作表.row(行下标) - 获取指定行中所有的单元格
result = stu_sheet.row(0)
print(result)
4)工作表.col(列下标) - 获取指定列中所有的单元格
result = stu_sheet.col(0)
print(result)
二、xlwt对文件进行写操作
1. 创建Excel文件(创建工作簿)
注意:创建工作簿的时候不会自动创建工作表
wb = xlwt.Workbook()
2.添加工作表
1)工作簿.add_sheet(表名) - 在工作簿中添加指定名字对应的工作表,这个表中的单元格不能重写
2)工作簿.add_sheet(表名, cell_overwrite_ok=True) - 在工作簿中添加指定名字对应的工作表,这个表中的单元格可以重写
sheet1 = wb.add_sheet('数据1')
sheet2 = wb.add_sheet('数据2', cell_overwrite_ok=True)
3.写入数据
工作表.write(行下标, 列下标, 数据) - 将数据写入到指定工作表中指定位置
sheet1.write(0, 0, '名字')
sheet1.write(1, 2, 'hello')
#最后记得保存数据
wb.save('files/example1.xls')
三、修改单元格样式
1.创建样式对象
style1 = xlwt.XFStyle()
2.添加字体样式
font1 = xlwt.Font()
font1.name = '黑体'
font1.bold = True #加粗
font1.height = 20*20 #字体大小
font1.colour_index = 12
style1.font = font1
3.添加边框样式
b1 = xlwt.Borders()
b1.bottom = 10
b1.right = 10
b1.left = 10
b1.bottom_colour = 16
style1.borders = b1
4.添加对齐样式
style2 = xlwt.XFStyle()
al1 = xlwt.Alignment()
al1.vert = xlwt.Alignment.VERT_CENTER
al1.horz = xlwt.Alignment.HORZ_CENTER
al1.rota = 90 #字体旋转
style2.alignment = al1
5.添加填充样式
p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = 13
style1.pattern = p1
sheet.write(0, 0, '名字', style=style1)
sheet.write(2, 3, '对齐格式', style=style2)
6.合并单元格
工作表.write_merge(行下标1, 行下标2, 列下标1,列下标2, 数据, 样式)
sheet.write_merge(8, 8, 2, 6, 'hello', style1)
sheet.write_merge(12, 22, 2, 7, 'hello', style2)
7.设置列的宽度和行的高度
1)设置列的宽度
sheet.col(0).width = 30*256
2)设置行的高度
运行指定的行可以设置高度
sheet.row(0).height_mismatch = True
设置行的高度
sheet.row(0).height = 30*60
四、颜色对照表
import xlwt
wb = xlwt.Workbook()
sheet = wb.add_sheet('颜色对照表')
# 1. 准备相关的样式
# 边框
b1 = xlwt.Borders()
b1.bottom = b1.top = b1.left = b1.right = 1
b1.bottom_colour = b1.top_colour = b1.left_colour = b1.right_colour = 8
# 字体
f1 = xlwt.Font()
f1.name = '黑体'
f1.bold = True
f1.height = 18 * 18
# 对齐
a1 = xlwt.Alignment()
a1.horz = xlwt.Alignment.HORZ_CENTER
a1.vert = xlwt.Alignment.VERT_CENTER
style1 = xlwt.XFStyle()
style1.font = f1
style1.borders = b1
style1.alignment = a1
style2 = xlwt.XFStyle()
style2.borders = b1
style2.alignment = a1
# 2. 写入对应的数据
sheet.write(0, 0, '颜色', style=style1)
sheet.write(0, 1, '颜色单词', style=style1)
sheet.write(0, 2, '颜色', style=style1)
row = 1
colors = xlwt.Style.colour_map
style3 = xlwt.XFStyle()
style3.borders = b1
for key in colors:
p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = colors[key]
style3.pattern = p1
# 颜色显示的列
sheet.write(row, 0, None, style=style3)
# 单词
sheet.write(row, 1, key, style=style2)
# 颜色数值
sheet.write(row, 2, colors[key], style=style2)
row += 1
# 设置每一列的宽度
for x in range(3):
sheet.col(x).width = 20 * 256
# 设置每一行的高度
for x in range(row):
sheet.row(x).height_mismatch = True
sheet.row(x).height = 15 * 60
wb.save('files/颜色.xls')
结果图:
五、xlutils的用法
xlrd只能对Excel文件进行读操作,而且只能用通过xlrd打开的Excel文件(xlrd无法直接操作xlwt创建的工作簿)
xlwt只能创建新的Excel,无法获取已经存在的Excel文件
结论:xlutils只能让已经存在的Excel可以添加新的表的写操作,不能直接修改表中的数据。
import xlwt, xlrd
from xlutils.copy import copy
1.使用xlrd打开已经存在的工作簿对象
(得到一个只读的工作簿)
wb = xlrd.open_workbook('files/data1.xls')
sheet0 = wb.sheet_by_index(0)
2.将只读的工作簿转换成可写的工作簿
wb1 = copy(wb)
sheet1 = wb1.add_sheet('data1') #创建新的工作表
sheet1.write(3, 3, 'good') #在新工作表3行3列的位置加'good'数据
wb1.save('files/data1.xls')
六、作业
更改下面Excel表样式
import xlutils, xlwt, xlrd
from xlutils.copy import copy
wb = xlrd.open_workbook('files/作业数据.xls')
wb1 = copy(wb)
al1 = xlwt.Alignment()
al1.horz = xlwt.Alignment.HORZ_CENTER
al1.vert = xlwt.Alignment.VERT_CENTER
b1 = xlwt.Borders()
b1.left = 1
b1.top = 1
b1.right = 1
b1.bottom = 1
style0 = xlwt.XFStyle()
font0 = xlwt.Font()
font0.name = '宋体'
style0.font = font0
style0.alignment = al1
style0.borders = b1
style0.alignment = al1
style1 = xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = '宋体'
font1.colour_index = 10
style1.font = font1
style1.borders = b1
style1.alignment = al1
style2 = xlwt.XFStyle()
font2 = xlwt.Font()
font2.name = '宋体'
font2.bold = True
font2.colour_index = 28
style2.font = font2
style2.borders = b1
style2.alignment = al1
style3 = xlwt.XFStyle()
font3 = xlwt.Font()
font3.name = '宋体'
font3.height = 15*15
font3.bold = True
style3.font = font3
p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = 51
style3.pattern = p1
style3.borders = b1
style3.alignment = al1
style4 = xlwt.XFStyle()
font4 = xlwt.Font()
font4.name = '宋体'
style4.font = font4
p2 = xlwt.Pattern()
p2.pattern = xlwt.Pattern.SOLID_PATTERN
p2.pattern_fore_colour = 47
style4.pattern = p2
style4.borders = b1
style4.alignment = al1
sheet0 = wb.sheet_by_index(0)
sheet1 = wb1.add_sheet('原始数据1', cell_overwrite_ok=True)
row = sheet0.nrows
col = sheet0.ncols
for r in range(0, row):
sheet1.write(r, 0, sheet0.row(r)[0].value, style = style2)
for c in range(0, col):
sheet1.write(0, c, sheet0.row(0)[c].value, style = style3)
sheet1.col(c).width = 20 * 256
sheet1.row(0).height_mismatch = True
sheet1.row(0).height = 10 * 50
for x in range(1, row):
for y in range(1, col):
if sheet0.row(x)[y].value == 0:
sheet1.write(x, y, '补考', style = style1)
elif sheet0.row(x)[y].value < 60:
sheet1.write(x, y, sheet0.row(x)[y].value, style = style4)
else:
sheet1.write(x, y, sheet0.row(x)[y].value, style = style0)
wb1.save('files/作业数据.xls')
结果图: