EXCEL文件处理程序（秋总）

EXCEL文件处理程序

编程语言：python；

涉及内容

	#### 表格统计
	#### 文件复制
	#### 文件改名
	#### 文件读取
	#### 文件修改
	#### 内容汇总求和
	#### 缺失项处理

下面是代码

// An highlighted block
import numpy as np
import pandas as pd
from os import walk
import openpyxl
import os
import shutil
pd.set_option('display.max_columns', None)   #显示完整的列
pd.set_option('display.max_rows', None)  #显示完整的行



class Score_tongji:
    def __init__(self,month):
        self.month = month
    def rename_file(self, folder,file,sumsum):
        os.rename(folder+file,folder+'【{sumsum}】'.format(sumsum=sumsum)+file)
    def copy_file(self, region, xiangmu):
        folder = os.getcwd() + '\\{month}月\\{month}月汇总无折叠\\【{region}片区】2021年4月\\'.format(month=self.month,\
                                    region=region)
        # 获取此py文件路径，在此路径选创建在new_folder文件夹中的test文件夹)
        if not os.path.exists(folder):
            os.makedirs(folder)
        shutil.copy('项目成本管理检查评分表V1.0.xlsx', folder+'【{xiangmu}】附件：项目成本管理检查评分表V1.0.xlsx'.format(xiangmu=xiangmu))

    def Save_Excel(self,Score_result,region,xiangmu):    #输入：项目得分 项目文件地址  输出：项目得分保存至无得分文件
        self.copy_file(region,xiangmu)
        folder='{month}月/{month}月汇总无折叠/【{region}片区】2021年{month}月/【{xiangmu}】附件：项目成本管理检查评分表V1.0.xlsx'\
                                  .format(month=self.month,xiangmu=xiangmu,region=region)
        workbook = openpyxl.load_workbook(folder)
        sheet = workbook.worksheets[0]  # 第一个页签
        for i in range(len(Score_result)):
            sheet.cell(row=8+i, column=9, value=Score_result[i][0])
        workbook.save(folder)
        self.rename_file('{month}月/{month}月汇总无折叠/【{region}片区】2021年{month}月/'.format(month=self.month,xiangmu=xiangmu,region=region),\
                         '【{xiangmu}】附件：项目成本管理检查评分表V1.0.xlsx'.format(xiangmu=xiangmu),result[len(result)-1][0])
    def Sum_project(self,department,region,project): #输入：部门列表 单个项目  输出：项目得分
        data = np.zeros((39, 1))
        for list in department:
            data = np.append(data ,self.Read_data(list,region,project),axis=1)  #初始打分数据
        Summation=data.sum(axis=1).reshape(39,1)
        sumsum=np.array(Summation.sum()).reshape(1,1)
        result=np.append(Summation, sumsum, axis=0)
        return result
    def Read_data(self,bumen,region,xiangmu):   #输入：部门单个 片区单个 项目单个  输出：单部门得分
        # 读取分数位置
        try:
            data1 = pd.read_excel('{month}月/各条线上报/1212考核-分片区{month}月{bumen}/{region}片区{month}月1212考核/【{xiangmu}】附件：1212管理法之项目成本管理检查评分表V1.0.xlsx'\
                                      .format(month=self.month,bumen=bumen,xiangmu=xiangmu,region=region),\
                                      sheet_name = '1212管理法之项目成本管理检查评分表',\
                                  usecols=[7,8],\
                                  skiprows=[0,1,2,3,4,5,6,46,47], \
                                  header=None)
        except:
            print ('{region}-{project}-{bumen}  出现错误！！！'.format(region=region, project=xiangmu,bumen=bumen))
        # finally:
        #     workbook = openpyxl.load_workbook(
        #         '各条线上报{month}月/1212考核-分片区{month}月{bumen}/{region}片区{month}月1212考核/problm【{xiangmu}】附件：项目成本管理检查评分表V1.0.xlsx' \
        #         .format(month=self.month, bumen=bumen, xiangmu=xiangmu, region=region))
        #     sheet = workbook.worksheets[0]
        #     va=sheet.cell(5,8).value
        #     va1 = sheet.cell(5, 9).value
        #     print (help(va))
        #     print ('zhi:',va,va1)
        #
        #     # print (help(sheet))
        #     data1 = pd.read_excel('{month}月/各条线上报/1212考核-分片区{month}月{bumen}/{region}片区{month}月1212考核/【{xiangmu}】附件：1212管理法之项目成本管理检查评分表V1.0.xlsx' \
        #         .format(month=self.month, bumen=bumen, xiangmu=xiangmu, region=region), \
        #         sheet_name='1212管理法之项目成本管理检查评分表', \
        #         usecols=[7, 8], \
        #         skiprows=[0, 1, 2, 3, 4, 5, 6, 46, 47], \
        #         header=None)
        # data1 = pd.read_excel('各条线上报{month}月/1212考核-分片区{month}月{bumen}/{region}片区{month}月1212考核/【{xiangmu}】附件：1212管理法之项目成本管理检查评分表V1.0.xlsx'\
        #                           .format(month=self.month,bumen=bumen,xiangmu=xiangmu,region=region))
        # print (data1)
        data1=data1.where(data1.notnull(), 0)

        Score = np.array(data1)
        # print (Score[1][0][0:2])
        # print (Score)
        # 纠正填写错误的项
        if bumen == '成控':
            for i in range(len(Score)):
                if Score[i][0][0:3] != '全成本':
                    Score[i][1]=0
        elif bumen == '项目成本':
            for i in range(len(Score)):
                if Score[i][0][0:2] != '项目' and Score[i][0][0:2] != '造价':
                    Score[i][1]=0
        else:
            for i in range(len(Score)):
                if Score[i][0][0:2] !=bumen:
                    Score[i][1] = 0

        #非0项转0
        if bumen=='采购':
            for i in range(len(Score)):
                if type(Score[i][1]) != int:
                    Score[i][1]=0
            # print (self.Score)
            return Score[:,1].reshape(39,1)
        else:
            for i in range(len(Score)):
                if type(Score[i][1]) == str:
                    Score[i][1]=0
                # print (type(Score[i][1]))
            # print (Score)
            return Score[:,1].astype(np.uint8).reshape(39,1)


if __name__ == "__main__":
    departments = ('采购', '成控', '市政', '项目成本', '招标')
    regions=('滨州', '德州', '济南')
    projects={}
    projects['滨州'] = ('', '', '','', '','','') #项目名称
    projects['德州'] = ('', '', '','', '','','') #项目名称
    projects['济南'] = ('', '', '','', '','','') #项目名称
    month=4
    order=0  #错误次数
    Score_tongji=Score_tongji(month)
    # Score_tongji.copy_file( '滨州', '')
    # 全统计
    for region in regions:
        for project in projects[region]:
            try:
                result = Score_tongji.Sum_project(departments,region, project)
                Score_tongji.Save_Excel(result, region, project)
                print ('{region}-{project}  完成'.format(region=region,project=project))
            except:
                order=order+1
                print ('{region}-{project}  出现错误！！！'.format(region=region,project=project))
    print ('\n')
    print ('---出现错误的项目数量{order}---'.format(order=order))
    # 单项目测试
    # result=Score_tongji.Sum_project(departments,'滨州','')
    # Score_tongji.Save_Excel(result,'滨州','')

    # 单部门循环测试
    # for department in departments:
    #     jieguo=Score_tongji.Read_data(department,'滨州','')
    #     print (type(jieguo))
    #     print (jieguo)
    #     print ('{department}没有问题'.format(department=department))

    # 单部门测试
    # jieguo=Score_tongji.Read_data('项目成本','滨州','')
    # print (type(jieguo))
    # print (jieguo.reshape(1,39))