Python自动化办公

import xlrdimport datetimedef Get_data(file):    wb = xlrd.open_workbook(file) #读取工作簿    ws = wb.sheets()[0] #选第一个工作表    data = {}
    for row in range(7, ws.nrows-2):        dept = ws.cell(2, 16).value #部门        dept_id = ws.cell(3, 16).value #部门编号        dt = ws.cell(row, 0).value #时间        if type(dt) is float:            date_time = xlrd.xldate.xldate_as_datetime(dt, 0)        else:            date_time = datetime.datetime.strptime(dt,'%Y-%m-%d %H:%M:%S')        business = ws.cell(row, 2).value #业务类型        model = ws.cell(row, 3).value #品种        qty = ws.cell(row, 4).value #数量        unit_price = ws.cell(row, 6).value #单价        price = ws.cell(row, 8).value #总价        reward = ws.cell(row, 9).value #额外值        discount = ws.cell(row, 11).value #调整        balance = ws.cell(row, 13).value #剩余        location = str(ws.cell(row, 15).value).strip() #库位        operator = ws.cell(row, 17).value #操作员        date = date_time.date() #日期        time = date_time.time() #时间        info_list=[dept,dept_id,date_time,business,model,qty,unit_price,price,reward,discount,                   balance,location,operator,date,time]        data.setdefault(date,[]) #以日期为键        if info_list[3] != "备注": #不要业务类型为“备注”的数据            data[date].append(info_list)
    #增加当日领取次数            for key in data.keys():        for i in data[key]:            i.append(len(data[key]))
    return data

import os #用于获取目标文件所在路径path=os.getcwd()+"\\记录\\" # 文件夹绝对路径files=[]for file in os.listdir(path):    if file.endswith(".xls"): #只获取".xls"后缀的文件        files.append(path+file)

import timedef Get_current_time():    time_stamp = time.time()  # 当前时间的时间戳    local_time = time.localtime(time_stamp)  #    str_time = time.strftime('%Y-%m-%d %H.%M.%S', local_time)    return str_time

from openpyxl import Workbookfrom openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment #设置单元格格式thin = Side(border_style="thin", color="000000")#定义边框粗细及颜色title = ['部门', '部门编号', '时间', '业务类型', '品种', '数量', '单价', '金额', '额外值',         '调整', '剩余', '库位', '操作员', '领取日期', '领取时间', '领取次数']
wb = Workbook() ws = wb.activews.merge_cells("A1:P1") #合并首行单元格ws.cell(1,1).value = "领料明细汇总表"ws.cell(1,1).font = Font(name=u'黑体',bold=True,size=18)ws.row_dimensions[1].height  = 22.2 #设置首行行高ws.cell(1,1).alignment = Alignment(horizontal="center", vertical="center") #设置对齐ws.append(title) #写入字段行
#写入各部门领料的数据for file in files:    data = Get_data(file)    for key in data.keys():        for i in data[key]:            ws.append(i)
#设置字号，对齐，缩小字体填充，加边框#Font(bold=True)可加粗字体for row_number in range(2, ws.max_row+1):    for col_number in range(1,17):        c = ws.cell(row=row_number,column=col_number)        c.font = Font(size=9)        c.border = Border(top=thin, left=thin, right=thin, bottom=thin)        c.alignment = Alignment(horizontal="left", vertical="center")
#设置列宽        col_name= list("ABCDEFGHIJKLMNOP")col_width = [8, 8, 16, 8, 16, 8, 8, 9.8, 8, 8, 8, 11, 8.3, 9, 8, 8]for i in range(len(col_name)):    ws.column_dimensions[col_name[i]].width = col_width[i]
#分组隐藏列ws.column_dimensions.group('I','K',hidden=True)ws.column_dimensions.group('N','O',hidden=True)
wb.save(f"领料明细汇总表{Get_current_time()}.xlsx")