day8xlrd和xlwt操作excel练习

day8xlrd和xlwt操作excel练习

import xlrd,xlwt
from xlutils.copy import copy

1.以读的方式打开excel文件获取原始数据对应的表

wb = xlrd.open_workbook('files/副本作业数据.xls')
sheet1 =wb['原始数据']

2.在工作簿中新建’新数据’对应的表

wb1 = copy(wb)
if '新数据' not in wb.sheet_names():
    sheet2 = wb1.add_sheet('新数据')
else:
    exit()

3.从原始数据表中获取数据，添加到新数据对应的表中

font1 = xlwt.Font()
font1.bold = True
font1.height = 20*20

font2 = xlwt.Font()
font2.height = 18*18
font2.colour_index = 46

font3 = xlwt.Font()
font3.height = 18*18

font4 = xlwt.Font()
font4.colour_index = 10

a1 = xlwt.Alignment()
a1.vert = xlwt.Alignment.VERT_CENTER
a1.horz = xlwt.Alignment.HORZ_CENTER

b1 = xlwt.Borders()
b1.top = b1.bottom = b1.left = b1.right = 1
b1.top_colour = b1.bottom_colour = b1.left_colour = b1.right_colour = 8

p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = 43

p2 = xlwt.Pattern()
p2.pattern = xlwt.Pattern.SOLID_PATTERN
p2.pattern_fore_colour = 52

style1 = xlwt.XFStyle()
style1.borders = b1
style1.alignment = a1
style1.font = font1
style1.pattern = p1

style2 = xlwt.XFStyle()
style2.borders = b1
style2.alignment = a1
style2.font = font2

style3 = xlwt.XFStyle()
style3.borders = b1
style3.alignment = a1
style3.font = font3

style4 = xlwt.XFStyle()
style4.borders = b1
style4.alignment = a1
style4.font = font4

style5 = xlwt.XFStyle()
style5.borders = b1
style5.alignment = a1
style5.font = font3
style5.pattern = p2

2)添加数据

nc = sheet1.ncols
nr = sheet1.nrows
for r in range(nr):
    for c in range(nc):
        # 取出数据
        data = sheet1.cell(r,c).value
        # 将数据写入新的表中
        if r == 0:
            sheet2.write(r,c,data,style=style1)
        elif c == 0:
            sheet2.write(r,c,data,style=style2)
        else:
            if data == 0:
                sheet2.write(r,c,'补考',style=style4)
            elif data < 60:
                sheet2.write(r,c,data,style=style5)
            else:
                sheet2.write(r,c,data,style=style3)

设置宽度和高度

sheet2.row(0).height_mismatch = True
sheet2.row(0).height = 20*256

for x in range(nc):
    sheet2.col(x).width = 30 * 256

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

原始数据：

代码作用如下：

)

原始数据

[外链图片转存中...(img-Jr4NIYu7-1676536758031)]

代码作用如下：

[外链图片转存中...(img-lIRVsZWb-1676536758033)]