07-Python自动化结算工资和统计报表

# -*- coding: utf-8 -*-
# @Time    : 2022/7/15 21:48
# @Author  : AI悦创
# @FileName: demo2.py
# @Software: PyCharm
# @Blog    ：http://www.aiyc.top
# @公众号   ：AI悦创
import xlwt
import faker
import random
import datetime


def create_excel_file(filename, department):
    wb = xlwt.Workbook(filename)
    sheet = wb.add_sheet('sheet1')
    fake = faker.Faker("zh_CN")
    head_data = ['部门', '姓名', '工号', '薪资（元）', '迟到次数（次）', '奖金（元）', '实发工资']
    for head in head_data:
        sheet.write(0, head_data.index(head), head)
    for i in range(1, random.randint(5, 100)):
        sheet.write(i, 0, department)
        sheet.write(i, 1, fake.last_name() + fake.first_name())
        sheet.write(i, 2, "G{}".format(random.randint(1, 1000)))
        sheet.write(i, 3, random.randint(4000, 16000))
        sheet.write(i, 4, random.choice([0, 0, 0, 1, 2, 3, 4]))
        sheet.write(i, 5, random.choice([200, 300, 400, 500, 600, 700, 800, 900]))
    wb.save(filename)


department_name = ['技术部', '推广部', '客服部', '行政部', '财务部']
for dep in department_name:
    xls_name = "{}-{}.xls".format(datetime.datetime.now().strftime("%Y-%m"), dep)
    create_excel_file(xls_name, dep)
    print(xls_name, " 新建完成")

import datetime
import xlrd, xlwt
from xlutils.copy import copy

department = ['技术部', '推广部', '客服部', '行政部', '财务部']
template_name = "月结统计模板.xls"
today_datetime = datetime.datetime.now()
need_process_xls = []
for dep in department:
    xls_name = "{}-{}.xls".format(datetime.datetime.now().strftime("%Y-%m"), dep)
    need_process_xls.append(xls_name)
print(need_process_xls)
# 输出：['2022-12-技术部.xls', '2022-12-推广部.xls', '2022-12-客服部.xls', '2022-12-行政部.xls', '2022-12-财务部.xls']

def process_xls_return_data(xls_name):
    wb = xlrd.open_workbook(xls_name)
    wb_sheet = wb.sheet_by_index(0)
    xwb = copy(wb)
    xwb_sheet = xwb.get_sheet('sheet1')
    rows = wb_sheet.nrows
    for row in range(1, rows):
        bm = wb_sheet.cell(row, 0).value
        xm = wb_sheet.cell(row, 1).value
        gh = wb_sheet.cell(row, 2).value
        gz = wb_sheet.cell(row, 3).value
        cdcs = wb_sheet.cell(row, 4).value
        jj = wb_sheet.cell(row, 5).value
        sfgz = gz - (cdcs * 20) + jj  # 实发工资 = 工资  -  （迟到次数*20）  +  奖金
        print(bm, xm, gh, gz, cdcs, jj, sfgz)
        xwb_sheet.write(row, 6, sfgz)
        xwb.save(xls_name)


for cls in need_process_xls:
    process_xls_return_data(cls)

def process_xls_return_data(xls_name):
    staff_number = 0  # 总人数字段
    cd_number = 0  # 迟到人数字段
    jj_number = 0  # 拿奖金人数字段
    total_pay = 0  # 总应发工资字段
    wb = xlrd.open_workbook(xls_name, formatting_info=True)
    wb_sheet = wb.sheet_by_index(0)
    xwb = copy(wb)
    xwb_sheet = xwb.get_sheet('sheet1')
    rows = wb_sheet.nrows
    for row in range(1, rows):
        staff_number = staff_number + 1  # 每个数据都是一个员工，直接+1
        bm = wb_sheet.cell(row, 0).value  # 部门名称
        xm = wb_sheet.cell(row, 1).value
        gh = wb_sheet.cell(row, 2).value
        gz = wb_sheet.cell(row, 3).value
        cdcs = wb_sheet.cell(row, 4).value
        if cdcs > 0:  # 如果迟到次数大于0，则是迟到过的人，迟到人数+1
            cd_number = cd_number + 1
        jj = wb_sheet.cell(row, 5).value
        if jj > 0:  # 如果奖金大于0，则是获得了奖金的人，拿奖金人数+1
            jj_number = jj_number + 1
        sfgz = gz - (cdcs * 20) + jj  # 实发工资 = 工资  -  （迟到次数*20）  +  奖金
        total_pay = total_pay + sfgz  # 将所有的实发工资加到一起，就是总的实发工资
        print(bm, xm, gh, gz, cdcs, jj, sfgz)
        xwb_sheet.write(row, 6, sfgz)
    xwb.save(xls_name)
    print([bm, staff_number, cd_number, jj_number, total_pay])
    return [bm, staff_number, cd_number, jj_number, total_pay]  # 最后将部门 总人数  总迟到人数  总拿奖金人数  总实发工资做成列表，一并返回


all_info = []
for cls in need_process_xls:
    one_partment = process_xls_return_data(cls)
    all_info.append(one_partment)  # 将函数的返回值，放到列表中，就得到了所有部门的统计信息

wb = xlrd.open_workbook(template_name, formatting_info=True)
wb_sheet = wb.sheet_by_index(0)
xwb = copy(wb)
xwb_sheet = xwb.get_sheet('Sheet1')
current_row = wb_sheet.nrows
year_month = datetime.datetime.now().strftime("%Y-%m")
title = "{}-各部门员工数据总览"


def create_style():  # 定义字体格式，返回一个字体大小24，垂直居中 水平居中 宋体格式 的样式
    style = xlwt.XFStyle()
    fnt = xlwt.Font()  # 创建一个文本格式，包括字体、字号和颜色样式特性
    fnt.name = u'宋体'
    fnt.height = 20 * 24
    alignment = xlwt.Alignment()
    alignment.horz = 0x02  # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.vert = 0x01  # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
    style.font = fnt
    style.alignment = alignment
    return style


xwb_sheet.write(0, 0, title.format(year_month), create_style())  # 写入头部标题，内容是“xxxx-xx-各部门员工数据总览”，样式是 宋体 大小24 垂直水平居中

for info in all_info:  # 循环所有的部门信息，全部写入到文件中
    xwb_sheet.write(current_row, 0, info[0])
    xwb_sheet.write(current_row, 1, info[1])
    xwb_sheet.write(current_row, 2, info[2])
    xwb_sheet.write(current_row, 3, info[3])
    xwb_sheet.write(current_row, 4, info[4])
    current_row = current_row + 1
xwb.save(title.format(year_month) + '.xls')  # 最后保存，文件名是 xxxx-xx-各部门员工数据总览.xls