python 操作execl 格式设置

最新推荐文章于 2023-10-12 19:05:21 发布

ciyuacca

最新推荐文章于 2023-10-12 19:05:21 发布

阅读量168

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/weixin_38098312/article/details/115659164

版权

python 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

该博客介绍了一个Python脚本，用于创建Excel工作簿并填充数据备份记录。脚本使用xlwt库，自定义样式，包括字体、对齐方式和边框，并按日期循环填充数据。内容涉及日期比较、单元格格式设置以及工作表的写入操作。

摘要由CSDN通过智能技术生成

import xlwt;
from datetime import datetime,timedelta;

def date_compare(date1, date2, fmt='%Y-%m-%d') -> bool:
    """
    比较两个真实日期之间的大小，date1 > date2 则返回True
    :param date1:
    :param date2:
    :param fmt:
    :return:
    """

    zero = datetime.fromtimestamp(0)

    try:
        d1 = datetime.strptime(str(date1), fmt)
    except:
        d1 = zero

    try:
        d2 = datetime.strptime(str(date2), fmt)
    except:
        d2 = zero
    return d1 > d2



style0 = xlwt.XFStyle()#格式信息
style1 = xlwt.XFStyle()#格式信息
font = xlwt.Font()#字体基本设置
font.name = u'宋体'
font.color = 'black'
font.height= 320 #字体大小，220就是11号字体，大概就是11*20得来的吧

font1 = xlwt.Font()#字体基本设置
font1.name = u'宋体'
font1.color = 'black'
font1.height= 240 #字体大小，220就是11号字体，大概就是11*20得来的吧
style0.font = font
style1.font = font1

alignment = xlwt.Alignment() # 设置字体在单元格的位置
alignment.horz = xlwt.Alignment.HORZ_CENTER #水平方向
alignment.vert = xlwt.Alignment.VERT_CENTER #竖直方向
style0.alignment = alignment
style1.alignment = alignment

border = xlwt.Borders()  #给单元格加框线
border.left = xlwt.Borders.THIN  #左
border.top=xlwt.Borders.THIN     #上
border.right=xlwt.Borders.THIN   #右
border.bottom=xlwt.Borders.THIN  #下
border.left_colour = 0x40  #设置框线颜色，0x40是黑色，颜色真的巨多，都晕了
border.right_colour = 0x40
border.top_colour = 0x40
border.bottom_colour = 0x40
#style0.borders = border
style1.borders = border



wb = xlwt.Workbook();
ws = wb.add_sheet('A Test Sheet');

ws.write_merge(0,0,0,9,"数据备份记录表（按天备份)",style0)
ws.write_merge(1,2,0,0,"序号 ",style1)
ws.write_merge(1,2,1,1,"备份日期",style1)
ws.write_merge(1,2,2,2,"备份时间",style1)
ws.write_merge(1,1,3,4,"备份内容",style1)
ws.write(2,3,"完整",style1)
ws.write(2,4,"日志",style1)
ws.write_merge(1,2,5,5,"备份原因",style1)
ws.write_merge(1,2,6,6,"备份介质",style1)
ws.write_merge(1,1,7,8,"备份方法",style1)
ws.write(2,7,"手动",style1)
ws.write(2,8,"自动",style1)
ws.write_merge(1,2,9,9,"备份操作员",style1)

date1 = '2021-04-12'
#while  date2<date1:
date2 = '2016-01-02'
dt = datetime.strptime(date2, "%Y-%m-%d")
out_date = (dt + timedelta(days=0)).strftime("%Y-%m-%d")
y = 50
x = 1
while date_compare(date1,out_date):
    z=0
    for i in range(x,y):
        z+=1
        out_date = (dt + timedelta(days=i)).strftime("%Y-%m-%d")
        ws.write(i+3,0,z,style1)
        ws.write(i+3,1,out_date,style1)
        ws.write(i+3,2,"1:01",style1)
        ws.write(i+3,3,"√",style1)
        ws.write(i+3,4,"√",style1)
        ws.write(i+3,5,"全库备份",style1)
        ws.write(i+3,6,"存储",style1)
        ws.write(i+3,7,"",style1)
        ws.write(i+3,8,"√",style1)
        ws.write(i+3,9,"",style1)
    x = i + 1
    y = x + 49
    print(out_date,x,y)

wb.save('example.xls');