day8 xlrd、xlwt、xlutils作业

day8 xlrd、xlwt、xlutils作业

"""
Author:TianGuoHui
Create Time:2023/2/15 18:29
"""
import xlrd, xlwt
from xlutils.copy import copy

workbook_1 = xlrd.open_workbook("files/data1.xls")

workbook_2 = copy(workbook_1)

source_sheet = workbook_1.sheet_by_index(0)

new_sheet = workbook_2.add_sheet("new_data1")

max_row = source_sheet.nrows
max_column = source_sheet.ncols

# 标题样式设置
title_style = xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = "黑体"
font1.bold = True
font1.height = 20*20
title_style.font = font1

# 姓名样式设计
name_style = xlwt.XFStyle()
font2 = xlwt.Font()
font2.name = "黑体"
font2.bold = True
font2.colour_index = 28
name_style.font = font2

# 补考字体样式设计
test_style = xlwt.XFStyle()
font3 = xlwt.Font()
font3.name = "宋体"
font3.colour_index = 10
test_style.font = font3

# 边框样式设计
align_style = xlwt.XFStyle()
boder = xlwt.Borders()
boder.bottom = 1
boder.right = 1
boder.left = 1
boder.top = 1
title_style.borders = boder
name_style.borders = boder
test_style.borders = boder
align_style.borders = boder

# 对齐样式设计
align = xlwt.Alignment()
align.vert = xlwt.Alignment.VERT_CENTER
align.horz = xlwt.Alignment.HORZ_CENTER
align_style.alignment = align
title_style.alignment = align
name_style.alignment = align
test_style.alignment = align

# 填充样式设计
padding = xlwt.Pattern()
padding.pattern = xlwt.Pattern.SOLID_PATTERN
padding.pattern_fore_colour = 51
title_style.pattern = padding

# 设置首行
for index in range(max_column):
    new_sheet.write(0, index, source_sheet.cell(0,index).value, style=title_style)

# 设置名字
for i in range(1, max_row):
    new_sheet.write(i, 0, source_sheet.cell(i, 0).value, style=name_style)

for i in range(1, max_row):
    for j in range(1, max_column):
        if source_sheet.cell(i, j).value == 0:
            new_sheet.write(i, j, "补考", style=test_style)
        else:
            new_sheet.write(i, j, source_sheet.cell(i, j).value, style=align_style)

# 设置宽高
for i in range(max_column):
    new_sheet.col(i).width = 25*256
for i in range(max_row):
    new_sheet.row(i).height_mismatch = True
    new_sheet.row(i).height = 20*20



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