day8-代码操作.xls文件

一、发送学生成绩

# 1. 准备数据
import openpyxl

# 1)获取excel文件中两张表中的所有数据
wb = openpyxl.open('files/python数据分析.xlsx')
stu_sheet = wb['学生信息']
con_sheet = wb['学生联系方式']

# a.获取第一张表中的数据
stu_info = []
m_r1, m_c1 = stu_sheet.max_row, stu_sheet.max_column
for row in range(2, m_r1+1):
    stu = {}
    for col in range(1, m_c1+1):
        stu[stu_sheet.cell(1, col).value] = stu_sheet.cell(row, col).value
    stu_info.append(stu)
# print(stu_info)

# b.获取第二张表中的数据
contact_info = []
m_r2, m_c2 = con_sheet.max_row, con_sheet.max_column
"""
[
    {'email': '874616871@qq.com', 'msg':'李楠同学你好，以下是你本次期末考试各科成绩：\n英语: 78分\n办公软件操作:96分\n....'}
]
"""
all_con = []
for row in range(2, m_r2+1):
    con = {}
    name = con_sheet.cell(row, 1).value
    address = con_sheet.cell(row, 4).value
    con['email'] = address
    # 在第一张表对应的数据中获取学生对应的分数信息
    for stu in stu_info:
        if stu['姓名'] == name:
            # 根据学生信息对应的字典(stu)，去构建邮件中成绩的部分
            result = ''
            for key in stu:
                if key != '姓名':
                    result += '      %s: %s分\n' % (key, stu[key])
            con['msg'] = '%s同学你好，以下是你本次期末考试各科成绩：\n%s' % (name, result)
            # con['msg'] = '%s同学你好，以下是你本次期末考试各科成绩：\n%s' % (name, stu)
    all_con.append(con)
print(all_con)

# 2.发邮件
import smtplib
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.mime.text import MIMEText

connect = smtplib.SMTP_SSL('smtp.qq.com', 465)
connect.login('874616871@qq.com', 'lwxfnvtbnfpjbfbg')

# 遍历拿到每个联系信息，构建对应的邮件并发送
for con in all_con:
    to_address = con['email']
    email = MIMEMultipart()
    email['Subject'] = Header('考试成绩', 'utf-8').encode()
    email['From'] = 'ZHL <874616871@qq.com>'
    email['To'] = to_address

    msg = MIMEText(con['msg'], 'plain')
    email.attach(msg)
    connect.sendmail('874616871@qq.com', to_address, email.as_string())

connect.close()

字符串操作

# 练习：
# 将字典：{'姓名': '赵军力', '英语': 69, '办公软件操作': 69, '电子商务': 63, '计算机基础': 76}
# 构建成字符串："英语： 69分\n办公软件操作：69分\n电子商务：63分\n计算机基础：76分"

'英语: 69分\n办公软件操作: 69分\n...'

dict1 = {'姓名': '赵军力', '英语': 69, '办公软件操作': 69, '电子商务': 63, '计算机基础': 76}
result = ''
for key in dict1:
    if key != '姓名':
        result += '%s: %s分\n' % (key, dict1[key])
print(result)

二、XLS文件读操作

1.打开excel文件获取工作薄对象

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

2.获取所有的工作表的表名

names = wb.sheet_names()
print(names)

3. 获取工作表

# 工作簿对象.sheet_by_name(表名)  -  获取指定表名对应的工作表
# 工作簿对象.sheet_by_index(下标)  -   获取指定下标对应的工作表
sheet1 = wb.sheet_by_name('students')
print(sheet1)

sheet2 = wb.sheet_by_index(1)
print(sheet2)

4. 获取表的行数和列数(有数据部分的行和列)

print(sheet1.nrows)
print(sheet1.ncols)

5. 按行或者按列获取数据

# 工作表.row_values(行下标)       -    获取指定行所有的数据
# 工作表.col_values(列下标)       -    获取指定列所有的数据
# 工作表.row_values(行下标, 开始列下标, 结束列下标) -   获取指定行中指定范围的数据
# 工作表.col_values(列下标, 开始行下标, 结束行下标)  -  获取指定列中指定范围的数据
#注意：从0开始
result = sheet1.row_values(1)
print(result)

result = sheet1.col_values(3)
print(result)

result = sheet1.col_values(3, 1)
print(result)

result = sheet1.row_values(2, 0, 3)
print(result)

6. 获取指定单元格中的数据

# 工作表.cell_value(行下标, 列下标)
result = sheet1.cell_value(0, 0)
print(result)

三、XLS文件读操作练习

# 练习：读excel文件data1.xls中students表中的数据，得到一个大列表，列表中的每个元素是每一行内容对应的小列表
"""
[
    ['小明', '男', 20, 99],
    ...
]

[   
    {'姓名': '小明', '性别': 男, '年龄': 20, '分数': 99}
]
"""

import xlrd

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

# 获取行数量
num_row = stu_sheet.nrows

# =========情况1：每一行对应一个小列表=========
datas = []

for index in range(1, num_row):
    line = stu_sheet.row_values(index)
    datas.append(line)

print(datas)
# =========情况2：每一行对应一个小字典=========
datas = []
keys = stu_sheet.row_values(0)
# print(keys)

for index in range(1, num_row):
    line = stu_sheet.row_values(index)
    stu = {}
    for x in range(len(keys)):
        key = keys[x]
        value = line[x]
        stu[key] = value
    datas.append(stu)

    # datas.append({keys[index]: line[index] for index in range(len(keys))})

print(datas)

四、XLS文件写操作

import xlwt

# 1.新建工作簿
wb = xlwt.Workbook()

# 2. 新建工作表
stu_sheet = wb.add_sheet('students')

# 3. 写入数据
# 工作表.write(行下标, 列下标, 数据)
stu_sheet.write(0, 0, 'name')

# 保存文件
wb.save('files/data2.xls')

五、Xlwt修改单元格样式

import xlwt

wb = xlwt.Workbook()
sheet = wb.add_sheet('样式')

# 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.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

六、xls文件写操作练习

# 写入到data3.xls文件中
# 第一行的内容分别是：name、gender、age、score
# 1. 准备数据
data = [
    ['小明', '男', 20.0, 99.0],
    ['张三', '男', 25.0, 87.0],
    ['小花', '女', 22.0, 95.0],
    ['老王', '男', 30.0, 77.0]
]

line1 = ['name', 'gender', 'age', 'score']

data.insert(0, line1)

# 2. 准备工作表
import xlwt

wb = xlwt.Workbook()
stu_sheet = wb.add_sheet('students')
print(data)
# 3. 写入数据
# rowx = 0 1 2 3 4
for row_x in range(len(data)):
    row_data = data[row_x]
    for col_x in range(len(row_data)):
        stu_sheet.write(row_x, col_x, row_data[col_x])

# 4.保存数据
wb.save('files/data3.xls')

七、xls文件设置单元格样式

import xlwt

# 注意：xlwt设置样式的时候，如果需要颜色值(字体颜色、填充颜色、边框颜色)，
# 颜色值只能使用xlwt.Style.colour_map中提供的颜色值
print(xlwt.Style.colour_map)
wb = xlwt.Workbook()
sheet = wb.add_sheet('各种样式')
sheet.write(0, 0, '香蕉')

# 1. 创建样式对象
style1 = xlwt.XFStyle()

# 2. 创建字体对象
font1 = xlwt.Font()

# 3. 将字体对象关联到样式中
style1.font = font1

# 4. 设置字体相关属性
font1.name = '宋体'    # 设置字体名称
font1.height = 20*20        # 设置字体大小
font1.colour_index = 28     # 设置字体颜色
font1.bold = True           # 是否加粗
font1.italic = True         # 是否倾斜

# 5. 写入数据的时候添加样式
sheet.write(1, 0, '苹果', style1)

# 1. 创建样式对象
style2 = xlwt.XFStyle()

# 2. 创建填充对象
pattern1 = xlwt.Pattern()

# 3. 将填充对象关联到样式对象中
style2.pattern = pattern1

# 4. 设置填充属性
pattern1.pattern = xlwt.Pattern.SOLID_PATTERN       # 设置填充模式
pattern1.pattern_fore_colour = 52                   # 设置填充颜色

# 5.写入数据的时候添加样式
sheet.write(1, 1, '猕猴桃', style2)

# 1. 设置列宽度
sheet.col(0).width = 50 * 256

# 2. 设置行高
# 1) 允许设置某一行的高度
sheet.row(2).height_mismatch = True

# 2) 设置指定行的高度
sheet.row(2).height = 40*60

wb.save('files/data4.xls')

八、作业-颜色对照表

import xlwt

# 1. 准备数据
data = xlwt.Style.colour_map
print(data)

# 2. 准备工作表
wb = xlwt.Workbook()
sheet = wb.add_sheet('颜色对照表')

# 3. 将数据写入到工作表中
# 1）
line1 = ['颜色', '颜色单词', '颜色值']

style1 = xlwt.XFStyle()

# 设置字体样式
font1 = xlwt.Font()
style1.font = font1
font1.name = '黑体'
font1.bold = True
font1.height = 16 * 16

# 设置对齐方式
alignment1 = xlwt.Alignment()
style1.alignment = alignment1
alignment1.vert = xlwt.Alignment.VERT_CENTER
alignment1.horz = xlwt.Alignment.HORZ_CENTER

# 设置边框
border = xlwt.Borders()
style1.borders = border
border.left = border.right = border.top = border.bottom = 1
border.left_colour = border.right_colour = border.top_colour = border.bottom_colour = 8

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

for col in range(len(line1)):
    # 设置每一列的宽度
    sheet.col(col).width = 20 * 256
    # 写入数据
    sheet.write(0, col, line1[col], style1)


# 2)
# 准备样式
style2 = xlwt.XFStyle()

font2 = xlwt.Font()
style2.font = font2
font2.height = 14 * 14

style2.alignment = alignment1
style2.borders = border

# 写入数据
row = 1
for key in data:
    value = data[key]

    # 第一列单元格的样式
    style3 = xlwt.XFStyle()
    style3.borders = border

    pattern = xlwt.Pattern()
    style3.pattern = pattern
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = value

    # 写入数据
    sheet.write(row, 0, '', style3)
    sheet.write(row, 1, key, style2)
    sheet.write(row, 2, value, style2)

    # 设置行高
    sheet.row(row).height_mismatch = True
    sheet.row(row).height = 10 * 60

    row += 1

wb.save('files/颜色.xls')