day8练习题

DAY8 xlrd .xlwt.xlutils的练习题

将原始数据制作成图二

原始数据

图二

![]()

"""
Author:Hook
Create Time:2023-02-15 20:10
身体健康，日富一日！！！
我与我周旋久 宁做我.
"""
import xlwt, xlrd
from xlutils.copy import copy
# 读入数据
# 1.使用xlrd打开已经存在的工作簿对象(得到一个只读的工作簿)
wb1 = xlrd.open_workbook('files/副本作业数据.xls')
# 2.将只读的工作簿转换成可写的工作簿
wb2 = copy(wb1)
source_data_sheet = wb1.sheet_by_index(0)
new_sheet = wb2.add_sheet("new_data")

m_r = source_data_sheet.nrows
m_c = source_data_sheet.ncols

# wb1.save('files/副本作业数据2.xls')
# 创建字体样式
# 第一行的标题样式
style1 = xlwt.XFStyle()
f1 = xlwt.Font()
f1.name = '宋体'
f1.height = 20* 20
f1.bold = True
f1.colour_index = 8
style1.font = f1

#第一列姓名的字体样式
style2 = xlwt.XFStyle()
f2 = xlwt.Font()
f2.name = '黑体'
f1.bold = True
f2.colour_index = 20
style2.font = f2

# 补考的字体样式
style3 = xlwt.XFStyle()
f3 = xlwt.Font()
f3.colour_index = 10
style3.font = f3

# 边框
style4 =xlwt.XFStyle()
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
style1.borders = style2.borders = style3.borders = style4.borders = b1
# 对齐样式
a1 = xlwt.Alignment()
a1.horz = xlwt.Alignment.HORZ_CENTER
a1.vert = xlwt.Alignment.VERT_CENTER
style1.alignment = style2.alignment = style3.alignment = style4.alignment = a1
# 填充样式
p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = 43
style1.pattern = p1

# 设置标题行
for x in range(m_c):
    new_sheet.write(0, x, source_data_sheet.cell(0, x).value, style = style1)

# 设置姓名
for index in range(1,m_r):
    new_sheet.write(index, 0, source_data_sheet.cell(index,0).value, style = style2)

for i in range(1,m_r):
    for j in range(1, m_c):
        if source_data_sheet.cell(i, j).value == 0:
            new_sheet.write(i, j, '补考', style = style3)
        else:
            new_sheet.write(i, j, source_data_sheet.cell(i, j).value, style = style4)

# 设置行高列宽
for x in range(m_c):
    new_sheet.col(x).width = 25*256
for x in range(m_r):
    new_sheet.row(x).height_mismatch = True
    new_sheet.row(x).height = 20*20

wb2.save('files/副本作业数据.xls')