countexcel/main.py

import pandas as pd
from scipy.optimize import fsolve
import glob
import os
import math

class countExcel:
    # init 
    def __init__(self,FilePath='C:\\Users\\caner\\Desktop\\countExcelC:\\Users\\caner\\Desktop\\countExcel') -> None:
        print('初始化传参',FilePath)
        pass
    # RCE方程函数
    def RCE(self,rce,v):
        try:
            RC = float(rce[0])
            E = float(rce[1])
            K = float(v[0])
            K1 = float(v[1])
            return [
                2*K*E-RC*RC,
                2*K1*E-RC*RC
            ]
        except:
            return ['null', 'null']

    # R123方程函数
    def R123(self,r,v):
        try:
            R1 = float(r[0])
            R2 = float(r[1])
            R3 = float(r[2])
            Er = float(r[3])
            V = float(r[4])
            K = float(v[0])
            K1 = float(v[1])
            K2 = float(v[2])
            K3 = float(v[3])          
            K4 = float(v[4])               
            return [
                # 2*弹性模量（E）*机械比能-R1*R1-R2*R2-R3*R3+2*v*(R1*R2+R1*R3+R2*R3)
                2*Er*K-R1*R1-R2*R2-R3*R3+2*V*(R1*R2+R1*R3+R2*R3),
                2*Er*K1-R1*R1-R2*R2-R3*R3+2*V*(R1*R2+R1*R3+R2*R3),
                2*Er*K2-R1*R1-R2*R2-R3*R3+2*V*(R1*R2+R1*R3+R2*R3),
                2*Er*K3-R1*R1-R2*R2-R3*R3+2*V*(R1*R2+R1*R3+R2*R3),
                2*Er*K4-R1*R1-R2*R2-R3*R3+2*V*(R1*R2+R1*R3+R2*R3)
            ]
        except:
            return ['null', 'null', 'null', 'null', 'null']

    # 对数据为null 的进行平均值填补
    def nullData(self,arr):
        num = 0
        lens = len(arr)
        for j in arr:
            if j != 'null':
                num += j
        ag = num/lens
        for z in range(lens):
            el = arr[z]
            if el == 'null' or el == 0:
                arr[z] = ag

    def main(self):
        colTitle = '机械比能'
        try:
            files = glob.glob(os.path.join(self.FilePath, "*.xls"))
            RC,E ,RK1,RK2,RK3,GRA #注意返回的变量是当前域全局变量
            for file in files:
                array = pd.read_excel(file)  # 读取excel数据
                try:
                    # 删除一次old
                    array.drop('单轴抗压强度', axis=1, inplace=True)
                    array.drop('弹性模量', axis=1, inplace=True)
                    array.drop('第一主应力', axis=1, inplace=True)
                    array.drop('第二主应力', axis=1, inplace=True)
                    array.drop('第三主应力', axis=1, inplace=True)
                    array.drop('岩石强度应力比', axis=1, inplace=True)
                except:
                    print('无旧数据')

                # 获取数据
                col = array[colTitle]  # 某列
                colLen = len(col)  # 一列的长度
                rowLen = len(array.iloc[1])  # 一行的长度
                RC = [0]*(colLen)  # RC初始化
                E = [0]*(colLen)  # E初始化
                RK1 = [0]*(colLen)  # R1初始化
                RK2 = [0]*(colLen)  # R2初始化
                RK3 = [0]*(colLen)  # R3初始化
                GRA = [0]*(colLen)  # 强度初始化

                # 两行计算RCE
                for i in range(colLen):
                    if (i+1) < len(col):
                        K = col[i]
                        K1 = col[i+1]
                        res = fsolve(self.RCE, [1, 1000],[K,K1])  # 其它参数直接用
                        rc = self.RCE(res)[0]
                        e = self.RCE(res)[1]
                        RC[i] = math.fabs(rc)  # 绝对值
                        RC[i+1] = math.fabs(rc)  # 列最后一个值使用上一个的值
                        E[i] = math.fabs(e)
                        E[i+1] = math.fabs(e)

                    # 单独对数据为null的进行平均值计算
                self.nullData(RC)
                self.nullData(E)

                # 三行计算R123
                for j in range(colLen):
                    if (j+4) < len(col):
                        K = col[j]
                        K1 = col[j+1]
                        K2 = col[j+2]
                        K3 = col[j+3]
                        K4 = col[j+4]
                        rea = fsolve(self.R123, [1, 1, 1, 100, 1],[K,K1,K2,K3,K4])  # 其它参数直接用
                        rea = self.R123(rea)
                        arr = sorted(rea, reverse=True)  # 倒叙
                        print(arr)
                        RK1[j] = math.fabs(arr[0])
                        RK1[j+1] = math.fabs(arr[0])
                        RK1[j+2] = math.fabs(arr[0])
                        RK1[j+3] = math.fabs(arr[0])
                        RK1[j+4] = math.fabs(arr[0])
                        RK2[j] = math.fabs(arr[1])
                        RK2[j+1] = math.fabs(arr[1])
                        RK2[j+2] = math.fabs(arr[1])
                        RK2[j+3] = math.fabs(arr[1])
                        RK2[j+4] = math.fabs(arr[1])
                        RK3[j] = math.fabs(arr[2])
                        RK3[j+1] = math.fabs(arr[2])
                        RK3[j+2] = math.fabs(arr[2])
                        RK3[j+3] = math.fabs(arr[2])
                        RK3[j+4] = math.fabs(arr[2])

                # 单独对数据为null的进行平均值计算
                self.nullData(RK1)
                self.nullData(RK2)
                self.nullData(RK3)

                # 对整体数据进行倍数计算↓
                for G in range(colLen):
                    RC[G] = RC[G]/100
                    E[G] = E[G]
                    RK1[G] = RK1[G]/100
                    RK2[G] = RK2[G]/100
                    RK3[G] = RK3[G]/100

                # 计算强度
                for z in range(colLen):
                    try:
                        GRA[z] = RC[z] / RK1[z]
                    except:
                        GRA[z] = 'null'

                # 单独对数据为null的进行平均值计算
                self.nullData(GRA)

                # # 保存excel
                # array.insert(rowLen, '单轴抗压强度', RC)
                # array.insert(rowLen+1, '弹性模量', E)
                # array.insert(rowLen+2, '第一主应力', RK1)
                # array.insert(rowLen+3, '第二主应力', RK2)
                # array.insert(rowLen+4, '第三主应力', RK3)
                # array.insert(rowLen+5, '岩石强度应力比', GRA)
                # pd.DataFrame(array).to_excel(
                #     file, sheet_name='Sheet1', index=False, header=True)
                # print('保存完毕')
            return {RC,E ,RK1,RK2,RK3,GRA}
        except ZeroDivisionError as err:
            print('计算错误', err)    
# 调用
# from countExcel import countExcel
# a = countExcel()
# b = a.main()


# if __name__ == "__main__":
#     path = 'C:\\Users\\caner\\Desktop\\countExcel'
#     colTitle = '机械比能'
#     try:
#         files = glob.glob(os.path.join(path, "*.xls"))
#         for file in files:
#             array = pd.read_excel(file)  # 读取excel数据
#             try:
#                 # 删除一次old
#                 array.drop('单轴抗压强度', axis=1, inplace=True)
#                 array.drop('弹性模量', axis=1, inplace=True)
#                 array.drop('第一主应力', axis=1, inplace=True)
#                 array.drop('第二主应力', axis=1, inplace=True)
#                 array.drop('第三主应力', axis=1, inplace=True)
#                 array.drop('岩石强度应力比', axis=1, inplace=True)
#             except:
#                 print('无旧数据')

#             # 获取数据
#             col = array[colTitle]  # 某列
#             colLen = len(col)  # 一列的长度
#             rowLen = len(array.iloc[1])  # 一行的长度
#             RC = [0]*(colLen)  # RC初始化
#             E = [0]*(colLen)  # E初始化
#             RK1 = [0]*(colLen)  # R1初始化
#             RK2 = [0]*(colLen)  # R2初始化
#             RK3 = [0]*(colLen)  # R3初始化
#             GRA = [0]*(colLen)  # 强度初始化

#             # 两行计算RCE
#             for i in range(colLen):
#                 if (i+1) < len(col):
#                     K = col[i]
#                     K1 = col[i+1]
#                     res = fsolve(RCE, [1, 1000])  # 其它参数直接用
#                     rc = RCE(res)[0]
#                     e = RCE(res)[1]
#                     RC[i] = math.fabs(rc)  # 绝对值
#                     RC[i+1] = math.fabs(rc)  # 列最后一个值使用上一个的值
#                     E[i] = math.fabs(e)
#                     E[i+1] = math.fabs(e)

#                 # 单独对数据为null的进行平均值计算
#             nullData(RC)
#             nullData(E)

#             # 三行计算R123
#             for j in range(colLen):
#                 if (j+4) < len(col):
#                     K = col[j]
#                     K1 = col[j+1]
#                     K2 = col[j+2]
#                     K3 = col[j+3]
#                     K4 = col[j+4]
#                     rea = fsolve(R123, [1, 1, 1, 100, 1])  # 其它参数直接用
#                     rea = R123(rea)
#                     arr = sorted(rea, reverse=True)  # 倒叙
#                     print(arr)
#                     RK1[j] = math.fabs(arr[0])
#                     RK1[j+1] = math.fabs(arr[0])
#                     RK1[j+2] = math.fabs(arr[0])
#                     RK1[j+3] = math.fabs(arr[0])
#                     RK1[j+4] = math.fabs(arr[0])
#                     RK2[j] = math.fabs(arr[1])
#                     RK2[j+1] = math.fabs(arr[1])
#                     RK2[j+2] = math.fabs(arr[1])
#                     RK2[j+3] = math.fabs(arr[1])
#                     RK2[j+4] = math.fabs(arr[1])
#                     RK3[j] = math.fabs(arr[2])
#                     RK3[j+1] = math.fabs(arr[2])
#                     RK3[j+2] = math.fabs(arr[2])
#                     RK3[j+3] = math.fabs(arr[2])
#                     RK3[j+4] = math.fabs(arr[2])

#             # 单独对数据为null的进行平均值计算
#             nullData(RK1)
#             nullData(RK2)
#             nullData(RK3)

#             # 对整体数据进行倍数计算↓
#             for G in range(colLen):
#                 RC[G] = RC[G]/100
#                 E[G] = E[G]
#                 RK1[G] = RK1[G]/100
#                 RK2[G] = RK2[G]/100
#                 RK3[G] = RK3[G]/100

#             # 计算强度
#             for z in range(colLen):
#                 try:
#                     GRA[z] = RC[z] / RK1[z]
#                 except:
#                     GRA[z] = 'null'

#             # 单独对数据为null的进行平均值计算
#             nullData(GRA)

#             # 保存excel
#             array.insert(rowLen, '单轴抗压强度', RC)
#             array.insert(rowLen+1, '弹性模量', E)
#             array.insert(rowLen+2, '第一主应力', RK1)
#             array.insert(rowLen+3, '第二主应力', RK2)
#             array.insert(rowLen+4, '第三主应力', RK3)
#             array.insert(rowLen+5, '岩石强度应力比', GRA)
#             pd.DataFrame(array).to_excel(
#                 file, sheet_name='Sheet1', index=False, header=True)
#             print('保存完毕')

#     except ZeroDivisionError as err:
#         print('计算错误', err)