xls文件的读写操作

xls文件的读写操作

1.关于发送学生成绩的课后练习（昨天的课后练习）

# 1. 准备数据
import openpyxl

# 1)获取excel文件中两张表中的所有数据
wb = openpyxl.open('D:\QF\daima\第一阶段\代码25\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': '726550822@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:
            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('726550822@qq.com', 'ilxqhvczadfwbegc')

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

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

connect.close()

2.关于xls文件的读操作

import xlrd
import xlwt

#1.打开excel文件获取工作簿对象
wb = xlrd.open_workbook('files/data1.xls')

#2.获取所有工作表的表名
names = wb.sheet_names()
print(names)

#3.获取工作表
#1)根据表名获取相应的工作表  工作簿对象.sheet_by_name(表名)
sheet1 = wb.sheet_by_name('students')
print(sheet1)

#2）获取指定下标对应的工作表  工作簿.sheet_by_index(下标)
sheet2 = wb.sheet_by_index(1)#小标都是从0开始
print(sheet2)

#4.获取表的最大行数和列数(有数据部分的行和列）
print(sheet1.nrows)
print(sheet1.ncols)

#5.按行或者按列获取数据
#工作表.row_values()  获取指定行所有的数据
result = sheet1.row_values(1)
print(result)
#工作表.col_values()  获取指定列所有的数据
result = sheet1.col_values(3)
print(result)

#start_rowx开始列下标    end_rowx结束列下标
#也可以sheet1.col_values(3,1)从第三列取，从第一行所有的行
result = sheet1.col_values(3,start_rowx=1,end_rowx=4)
print(result)
#取到第二行到第三列之前的数据   3表示取不到第3列
result1 = sheet1.row_values(2,0,3)
print(result1)
#6.获取指定单元格中的数据(行下标和列下标）
#工作表.cell_value(行下标，列下标)
result = sheet1.cell_value(0,0)
print(result)

2.1 xls文件的读操作练习

#练习：读excel文件data1.xls中student表中的数据，得到一个大列表，列表中的每个元素是每一行内容对应的小列表
import xlrd

# 1. 打开excel文件获取工作簿对象
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(列下标, 开始行下标, 结束行下标)  -  获取指定列中指定范围的数据
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)

3.关于xls文件的写操作

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

#2.新建工作表   创建完工作簿之后必须键表，否则打不开
stu_sheet = wb.add_sheet('students')



#3.写入数据     先给行下标，列下标，再写数据
#工作表.write(行下标，列下标，内容（数据）)
stu_sheet.write(0,0,'姓名')
stu_sheet.write(0,1,'性别')
stu_sheet.write(0,2,'年龄')


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

3.1 xls文件写操作的练习

1.暴力拆解法
#写入到data3.xls文件中
#第一行的内容分别是：name,gender,age,score
data = [
    ['小明', '男', 20.0, 99.0],
    ['张三', '男', 25.0, 87.0],
    ['小花', '女', 22.0, 95.0],
    ['老王', '男', 30.0, 77.0]
]
#导入数据包
import xlwt
#建立工作簿
wb = xlwt.Workbook()
#建立工作表
stu_sheet = wb.add_sheet('学生信息')
#写入表头
stu_sheet.write(0,0,'name')
stu_sheet.write(0,1,'gender')
stu_sheet.write(0,2,'age')
stu_sheet.write(0,3,'score')

#输入每个人的数据
stu_sheet.write(1,0,'小明')
stu_sheet.write(1,1,'男')
stu_sheet.write(1,2,'20.0')
stu_sheet.write(1,3,'99.0')

stu_sheet.write(2,0,'张三')
stu_sheet.write(2,1,'男')
stu_sheet.write(2,2,'25.0')
stu_sheet.write(2,3,'87.0')

stu_sheet.write(3,0,'小花')
stu_sheet.write(3,1,'女')
stu_sheet.write(3,2,'22.0')
stu_sheet.write(3,3,'95.0')

stu_sheet.write(4,0,'老王')
stu_sheet.write(4,1,'男')
stu_sheet.write(4,2,'30.0')
stu_sheet.write(4,3,'77.0')

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


2.使用循环语句(range函数，insert函数)
#写入到data3.xls文件中
#第一行的内容分别是：name,gender,age,score
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('student')
print(data)
print('-----------分割线-----------------')
#3.写入数据
#row_x = 0,1,2,3,4   因为插入了一行   大列表   循环！！！！
for row_x in range(len(data)):
    row_data = data[row_x]
    #print(row_x,row_data)
    for col_x in range(len(row_data)):
        stu_sheet.write(row_x,col_x,row_data[col_x])

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

4. 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 = 30*30  #设置字号的大小

font1.colour_index = 28 #设置字体颜色

font1.bold = True      #字体是否加粗

font1.italic = True   #是否倾斜

#5.写入数据的时候添加样式
sheet.write(1,0,'荔枝',style = 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   #设置填充颜色
#pattern1.pattern_back_colour = 46    #显示不出来

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




#----------------------设置单元格的行高和列宽----------------------------------
#1.设置行高
sheet.col(0).width = 105*15    #设置行高

#2.设置行高
#1）允许设置某一行的高度
sheet.row(2).height_mismatch = True
#2)设置指定行的高度
sheet.row(2).height = 40*60
#6.保存
wb.save('files/data6.xls')