xlrd | Python汇总各单位Excel档领料记录并加总每日领用次数

Python汇总各单位Excel档领料记录并加总每日领用次数

xlrd详解
代码：

import time
import xlrd
import datetime


# 读取xls文件中的数据
def 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)
# print(files)

# Get_data(files[0])


def 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


# print(Get_current_time())

# 汇总数据到主文件
from openpyxl import Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment  # 设置单元格格式

thin = Side(border_style="thin", color="000000")  # 定义边框粗细及颜色
title = ['部门', '部门编号', '时间', '业务类型', '品种', '数量', '单价', '金额', '额外值',
         '调整', '剩余', '库位', '操作员', '领取日期', '领取时间', '领取次数']

wb = Workbook()
ws = wb.active
ws.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")

记录：
工程部领料明细.xls
生产部领料明细.xls