day9Python操作excel

一、xlrd获取excel文件内容

openpyxl操作xlsx文件，xlrd操作xls文件

import xlrd

1.打开Excel文件获取工作簿对象(.xls)

wb = xlrd.open_workbook('files/data1.xls')

2.获取工作表

# 1）获取所有工作表的表名
print(wb.sheet_names())

# 2)获取工作表
# a.获取所有的工作表
all_sheet = wb.sheets()
print(all_sheet)     # [Sheet  0:<students>, Sheet  1:<teacher>]

# b.获取指定对应下标的工作表
# 工作簿对象.sheet_by_index(下标)
print(wb.sheet_by_index(0))   # Sheet  0:<students>
print(wb.sheet_by_index(1))   # Sheet  1:<teacher>

# c.获取指定名字对应的工作表
# 工作簿对象.sheet_by_name(表名)
students = wb.sheet_by_name('students')

3.获取表中的相关信息

# 1)获取表中数据部分的最大行数和最大列数
# 表对象.nrows - 最大行数
# 表对象.ncols - 最大列数
print(students.nrows, students.ncols)

# 2)按行或者按列获取数据
print(students.row_values(0))   # 获取第一行的数据
print(students.col_values(0))   # 获取第一列的数据  # ['姓名', '小明', '张三', '小花', '老王']
print(students.col_values(0, start_rowx=1))  # ['小明', '张三', '小花', '老王']
print(students.col_values(0, start_rowx=1, end_rowx=4))  # ['小明', '张三', '小花']
print(students.row_values(1, start_colx=1, end_colx=4))  # ['男', 20.0, 99.0]

练习：

# 练习1：一行一行的获取整个表中所有的数据
nr = students.nrows
nc = students.ncols
for x in range(0, nr):
    print(students.row_values(x))
   
# ['姓名', '性别', '年龄', '分数']
# ['小明', '男', 20.0, 99.0]
# ['张三', '男', 25.0, 87.0]
# ['小花', '女', 22.0, 95.0]
# ['老王', '男', 30.0, 77.0]

# 练习2：一列一列的获取整个表中所有的数据
for x in range(0, nc):
    print(students.col_values(x))
    
# ['姓名', '小明', '张三', '小花', '老王']
# ['性别', '男', '男', '女', '男']
# ['年龄', 20.0, 25.0, 22.0, 30.0]
# ['分数', 99.0, 87.0, 95.0, 77.0]

# 3)获取单元格对象
# a.工作表.cell(行下标, 列下标)   -  获取指定位置对应的单元格
# 单元格对象.value
print(students.cell(0, 0))        # text:'姓名'
print(students.cell(0, 0).value)  # 姓名

# b.工作表.row(行下标)  -  获取指定行中所有有数据的单元格对象
print(students.row(1))      # [text:'小明', text:'男', number:20.0, number:99.0]
print(students.row(1)[-1])  # number:99.0

# c.工作表.col(列下标)  -  获取指定列中所有数据的单元格对象
print(students.col(2))     # [text:'年龄', number:20.0, number:25.0, number:22.0, number:30.0]

二、xlwt对Excel进行写操作

注意：

1)xlwt操作单元格内容的时候，只能在空的单元格中添加数据，不能修改有数据的单元格内容

2)xlrd打开工作簿不能进行写操作，只能读操作；xlwt打开工作簿只能写操作，不能读操作

import xlwt, xlrd
import os

1.创建工作簿对象(默认不会创建工作表)

wb = xlwt.Workbook()

2.新建表

student = wb.add_sheet('学生表', cell_overwrite_ok=True)

3.写入数据

student.write(0, 0, '姓名')

如果在已经有数据的单元格中重新写入数据，创建表的时候参数cell_overwrite_ok必须是True

student.write(0, 0, '名称')

4.设置单元格样式

1）设置行的高度和列的宽度

# a.设置指定行的高度
# 允许设置高度
student.row(0).height_mismatch = True
# 设置高度值
student.row(0).height = 10*60

# b.设置指定列的宽度
student.col(1).width = 15 *256

2)设置字体

# a.创建样式对象
style1 = xlwt.XFStyle()
# b.创建字体对象
font = xlwt.Font()
# c.将字体对象绑定到样式对象中
style1.font = font

# d.添加各种字体属性
font.name = '黑体'
font.bold = True
font.italic = True
font.height = 15*15
font.colour_index = 10
font.underline = True
# font.escapement = xlwt.Font.ESCAPEMENT_SUBSCRIPT
font.struck_out = True

# e.将样式绑定到单元格中
student.write(0, 1, '年龄', style=style1)

3)设置边框的样式

style2 = xlwt.XFStyle()
# 创建边框对象
border = xlwt.Borders()
# 关联
style2.borders = border
# 设置边框样式
# 边框样式：细实线：1, 小粗实线：2, 细虚线：3, 中细虚线：4, 大粗实线：5, 双线：6, 细点虚线：7, 大粗虚线：8
border.bottom = 10
# 边框颜色
border.bottom_colour = 14
border.top = 2
border.top_colour = 53

student.write(1, 4, '班级', style=style2)

4）设置填充样式

style3 = xlwt.XFStyle()
pa = xlwt.Pattern()
style3.pattern = pa
# 设置填充样式
pa.pattern = xlwt.Pattern.SOLID_PATTERN
pa.pattern_fore_colour = 26
# pa.pattern_back_colour = 53

# 关联单元格
student.write(1, 5, '你好', style3)

5)设置对齐方式

style4 = xlwt.XFStyle()
ag = xlwt.Alignment()
style4.alignment = ag
ag.vert = xlwt.Alignment.VERT_CENTER
ag.horz = xlwt.Alignment.HORZ_CENTER
ag.rota = 45      # 旋转45度
ag.wrap = True    # 自动换行

student.write(0, 7, 'hi', style=style4)

5.合并单元格

# 工作表.write_merge(r1, r2, c1, c2, 数据, style=样式对象)
student.write_merge(5, 7, 1, 1, 'abc')

student.write_merge(8, 15, 3, 6, 2863)

练习：创建颜色对应表

import xlwt
# 练习1:新建一个Excel文件，创建一个颜色对应表
# result = xlwt.Style.colour_map
# print(result)
# for x in result:
#     print(x, result[x])

# 1.创建工作簿和工作表
wb = xlwt.Workbook()
map1 = wb.add_sheet('颜色表')

# 2.添加颜色信息
# 1）获取所有颜色值
color_map = xlwt.Style.colour_map

# 2)表头数据和基本样式
style1 = xlwt.XFStyle()
# 创建字体对象样式
font1 = xlwt.Font()
font1.height = 16*16
font1.bold = True
font1.name = '黑体'
style1.font = font1

# 创建对齐方式
ag = xlwt.Alignment()
ag.vert = xlwt.Alignment.VERT_CENTER
ag.horz = xlwt.Alignment.HORZ_CENTER
style1.alignment = ag

# 创建边框
border = xlwt.Borders()
border.top = 1
border.bottom = 1
border.left = 1
border.right = 1
style1.borders = border

# 写入第一行内容
map1.write(0, 0, '颜色', style=style1)
map1.write(0, 1, '颜色单词', style=style1)
map1.write(0, 2, '颜色值', style=style1)

# 设置第一行内容的高度
map1.row(0).height_mismatch = True
map1.row(0).height = 10*60

# 设置前三列的宽度
map1.col(0).width = 20*256
map1.col(1).width = 20*256
map1.col(2).width = 20*256


# 3)将颜色信息写入单元格中
row_index = 1
for x in color_map:
    # 拿到颜色值
    color_value = color_map[x]
    print(x, color_value)

    # 创建对应的填充对象
    style = xlwt.XFStyle()
    style.borders = border

    fill = xlwt.Pattern()
    fill.pattern = xlwt.Pattern.SOLID_PATTERN
    fill.pattern_fore_colour = color_value
    style.pattern = fill

    # 设置对应单元格样式
    map1.write(row_index, 0, '', style=style)
    # 设置行高
    map1.row(row_index).height_mismatch = True
    map1.row(row_index).height = 10 * 60

三、xlutils对Excel进行写操作

注意：

xlwt只能对新建的Excel文件进行写操作；xlutils可以对已经存在的Excel文件进行写操作

import xlrd
from xlutils.copy import copy

# 1.打开指定的Excel文件
r_wb = xlrd.open_workbook('files/data1.xls')

# 2.对打开的工作簿对象进行拷贝，得到一个可写的工作簿
w_wb = copy(r_wb)

# 3.在新的工作表中写数据
# sheet1 = w_wb.add_sheet('班级')
# sheet1.write(0, 0, '班级名称')

# 4.在已经存在的工作表中写数据(将students中'小明', 改成'XiaoMing')
sheet2_n = w_wb.add_sheet('students2', cell_overwrite_ok=True)
sheet2_o = r_wb.sheet_by_index(0)
for r in range(sheet2_o.nrows):
    r_data = sheet2_o.row_values(r)
    col = 0
    for x in r_data:
        sheet2_n.write(r, col, x)
        col += 1

sheet2_n.write(1, 0, 'XiaoMing')

w_wb.save('files/data1.xls')