def CreateSlumpCard(cardBook):
# modeName = "../Mode/6.xlsx"
modeName = "../../Mode/6.xlsx"
from Util.get_parm import get_parm
parm = get_parm()
modeBook = Util.FileUitl.openFileXlsx(modeName)
modeBook = Util.ExcelUtil.CopyFromModeExcel(modeBook, cardBook)
modeBook[modeBook.sheetnames[0]].title = "1-1"
cardSheets = cardBook.sheetnames
for sheetNum in range(len(cardSheets)):
"""
获得出厂合格证数据
"""
cardSheet = cardBook.worksheets[sheetNum]
ProjectName = cardSheet['E4'].value.strip('工程名称：')
ConstrUnit = cardSheet['E6'].value.strip('施工单位：')
ProjectManager = cardSheet['E7'].value.strip('工程负责人：')
ConcreteStrength = cardSheet['A7'].value.strip('强度等级：')
CuringDate = cardSheet['A4'].value.strip('出厂日期：')
ProjectSite = cardSheet['E5'].value.strip('工程部位：')
SlumpNum = cardSheet['C14'].value
"""
插入数据
"""
resultSheet = modeBook.worksheets[sheetNum]
resultSheet['A6'] = "工程名称：" + ProjectName
resultSheet['A7'] = "施工单位：" + ConstrUnit
resultSheet['E6'] = "浇筑部位：" + ProjectSite
resultSheet['A8'] = "工程负责人：" + ProjectManager
resultSheet['E7'] = "强度等级：" + ConcreteStrength
resultSheet['A5'] = "供应时间：" + CuringDate
resultSheet['E8'] = "工地要求坍落度：" + SlumpNum
resultSheet = iniexcel6(resultSheet)
return modeBook
def CreteConUseProve(UseBook):
modeName = "../../Mode/8.xlsx"
from Util.get_parm import get_parm
parm = get_parm()
modeBook = Util.FileUitl.openFileXlsx(modeName)
modeBook = Util.ExcelUtil.CopyFromModeExcel(modeBook, UseBook)
useSheets = UseBook.sheetnames
for sheetNum in range(len(useSheets)):
"""
获取使用记录数据
"""
DataList = []
sheet = UseBook.worksheets[sheetNum]
ProjectName = sheet['A2'].value.replace('工程名称：', '').strip()
BuildUnit = sheet['A3'].value.replace('建设单位：', '').strip()
ConstrUnit = sheet['A4'].value.replace('施工单位：', '').strip()
ContractId = sheet['A5'].value.replace('合同编号：', '').strip()
ProjectManager = sheet['A6'].value.replace('项目经理：', '').strip()
ProveAdress = sheet['A7'].value.replace('证明书地址：', '').strip()
ProveSite = sheet['A8'].value.replace('证明书工程部位：', '').strip()
# print(ProveSite)
CementSumNum = 0
rows = sheet.max_row
for rowNum in range(10, rows + 1):
CementSumNum = CementSumNum + sheet.cell(row=rowNum,
column=8).value
DataList.append(
sheet.cell(row=rowNum, column=6).value + timedelta(days=28))
DataList.sort(reverse=False)
"""
插入数据
"""
resultSheet = modeBook.worksheets[sheetNum]
iniexcel8(resultSheet) # 初始化表格
resultSheet['A3'] = "合 同 号 ：" + ContractId
resultSheet['A4'] = "建设单位：" + BuildUnit
resultSheet['A5'] = "施工单位：" + ConstrUnit
resultSheet['A6'] = "施工负责人：" + ProjectManager
resultSheet['A7'] = "位于：" + ProveAdress
resultSheet['A8'] = "工程名称：" + ProjectName
resultSheet['A9'] = "工程部位：" + ProveSite
# 3.28
if (ProveSite.find("主体") > 0):
resultSheet['A13'] = " 持 证 范 围：竣工验收"
CuringNumString = "实际使用我站混凝土为：{CementSumNum}㎥（{CementSumCN}立方米)"
resultSheet['A10'] = CuringNumString.format(
CementSumNum="{0:.1f}".format(CementSumNum),
CementSumCN=Util.ExcelUtil.to_currency(CementSumNum))
resultSheet['D21'] = DataList[-1].strftime('%Y$%#m/%#d').replace(
'$', '年').replace('/', '月') + '日'
# resultSheet['B15'] = parm[0].ConUseProveUtil
return modeBook
def ConStrengReport(useBook):
from Util.get_parm import get_parm
parm = get_parm()
MinC_Strength = float(parm.MinC_Strength)
MaxC_Strength = float(parm.MaxC_Strength)
useSheets = useBook.sheetnames
secModeName = "../../Mode/2.xlsx"
thrModeNmae = "../../Mode/3.xlsx"
fouModeName = "../../Mode/4.xlsx"
resultRepoBook = load_workbook(secModeName)
resultRepoSumBook = load_workbook(thrModeNmae)
resultRepoCompBook = load_workbook(fouModeName)
"""
复制第二份表模板
"""
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row
moreNum = useSheet.max_row
moreNum = moreNum - 9
for rowNum in range(10, rows + 1):
if useSheet.cell(row=rowNum, column=1).value == None:
break
CuringNum = useSheet.cell(row=rowNum, column=8).value
if (CuringNum >= 1000):
moreNum = moreNum + int(CuringNum / 200)
elif (CuringNum > 100):
moreNum = moreNum + int(CuringNum / 100)
# while (CuringNum > 100):
# moreNum = moreNum + 1
# CuringNum = CuringNum - 100
moreFixNum, rows = divmod(moreNum, 15)
if rows == 0:
moreFixNum = moreFixNum - 1
for copyNum in range(moreFixNum + 1):
modeSheet = resultRepoBook.worksheets[0]
iniexcel2(modeSheet)
modeSheet = resultRepoBook.copy_worksheet(modeSheet)
iniexcel2(modeSheet)
modeSheet.title = str(str(sheetNum + 1) + '-' + str(copyNum + 1))
resultRepoBook.remove(resultRepoBook.worksheets[0])
"""
复制第三份表模板
"""
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row - 9
moreFixNum, rows = divmod(rows, 20)
if rows == 0:
moreFixNum = moreFixNum - 1
for copyNum in range(moreFixNum + 1):
modeSheet = resultRepoSumBook.worksheets[0]
iniexcel3(modeSheet)
modeSheet = resultRepoSumBook.copy_worksheet(modeSheet)
iniexcel3(modeSheet)
modeSheet.title = str(str(sheetNum + 1) + '-' + str(copyNum + 1))
resultRepoSumBook.remove(resultRepoSumBook.worksheets[0])
# resultRepoSumBook.save('3.xlsx')
"""
获取表数据
"""
ConStrRepoHead = []
UseRecord = []
TestNameList = []
TestDict = {
'C15': 'A',
'C20': 'B',
'C25': 'C',
'C30': 'D',
'C35': 'E',
'C40': 'F',
'C45': 'G',
'C50': 'H',
'C55': 'I',
'C60': 'J',
'C65': 'K',
'C70': 'L',
'C75': 'M',
'C80': 'N'
}
RepoSum = []
RepoComp = []
for i in range(len(useSheets)):
sheet = useBook.worksheets[i]
rows = sheet.max_row
UseRecordList = []
RepoSumList = []
RepoCompList = []
ComRepoCompList = []
for k in range(10, rows + 1):
TypicalStrengthSum = 0
TypicalStrengthSumFlag = 0
if sheet.cell(row=k, column=1).value == None:
break
TypicalStrengthList = []
CuringNum = sheet.cell(row=k, column=8).value
if (CuringNum < 1000):
CementName = sheet['B' + str(k)].value.replace('砼', '')
ProjectSite = sheet.cell(row=k, column=1).value
ConcreteStrength = sheet.cell(row=k, column=3).value
ImperLevel = sheet.cell(row=k, column=4).value
SwellLevel = sheet.cell(row=k, column=5).value
CuringDate = sheet.cell(row=k, column=6).value
TestName = TestDict[ConcreteStrength] + CuringDate.strftime(
'%m%d')
TestNameList.append(TestName)
MoldingTime = (CuringDate +
timedelta(days=28)).strftime('%Y/%#m/%#d')
SortTime = datetime.strptime(MoldingTime, '%Y/%m/%d')
CuringDate = CuringDate.strftime('%Y/%#m/%#d')
Strength = float(ConcreteStrength.strip('C'))
LongStrength = Strength * uniform(MinC_Strength, MaxC_Strength)
WideStrength = Strength * uniform(MinC_Strength, MaxC_Strength)
HigthStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
AvgStrength = (LongStrength + WideStrength + HigthStrength) / 3
StrengthString = ConcreteStrength
if (ImperLevel == None):
StrengthString = StrengthString + CementName
else:
StrengthString = StrengthString + CementName + ImperLevel
if (SwellLevel != None):
StrengthString = StrengthString + '\n(' + str(
int(SwellLevel * 100)) + '%膨胀)'
LongStrength = '{:.1f}'.format(get_float(LongStrength, 1))
WideStrength = '{:.1f}'.format(get_float(WideStrength, 1))
HigthStrength = '{:.1f}'.format(get_float(HigthStrength, 1))
AvgStrength = '{:.1f}'.format(get_float(AvgStrength, 1))
ConcreteStrength = sheet.cell(row=k,
column=3).value + CementName
CompStrength = ConcreteStrength[1:3]
CompAvgStrength = AvgStrength
ComRepoComp = Bean.ExcelBean.ConRepoCompBean(
CompStrength=CompStrength, CompAvgStrength=CompAvgStrength)
ComRepoCompList.append(ComRepoComp)
ConUseRecord = Bean.ExcelBean.ConStrengUseBean(
TestName=TestName,
ProjectSite=ProjectSite,
ConcreteStrength=ConcreteStrength,
StrengthString=StrengthString,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
CuringDate=CuringDate,
MoldingTime=MoldingTime,
LongStrength=LongStrength,
WideStrength=WideStrength,
HigthStrength=HigthStrength,
AvgStrength=AvgStrength,
CuringNum=CuringNum,
SortTime=SortTime,
)
SumProjectSite = ProjectSite
# SumConcreteStrength = str(ConcreteStrength)
SumConcreteStrength = StrengthString
SumCuringDate = CuringDate
#
SumSortTime = datetime.strptime(SumCuringDate, '%Y/%m/%d')
TypicalStrengthList.append(AvgStrength)
TypicalStrengthSum = float(AvgStrength) + TypicalStrengthSum
TypicalStrengthSumFlag = TypicalStrengthSumFlag + 1
UseRecordList.append(ConUseRecord)
while (CuringNum - 100 > 0):
ProjectSite = sheet.cell(row=k, column=1).value
ConcreteStrength = sheet.cell(row=k, column=3).value
ImperLevel = sheet.cell(row=k, column=4).value
SwellLevel = sheet.cell(row=k, column=5).value
CementName = sheet['B' + str(k)].value.replace('砼', '')
CuringDate = sheet.cell(row=k, column=6).value
TestName = TestDict[
ConcreteStrength] + CuringDate.strftime('%m%d')
TestNameList.append(TestName)
MoldingTime = (CuringDate +
timedelta(days=28)).strftime('%Y/%#m/%#d')
#
SortTime = datetime.strptime(MoldingTime, '%Y/%m/%d')
CuringDate = CuringDate.strftime('%Y/%#m/%#d')
Strength = float(ConcreteStrength.strip('C'))
LongStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
WideStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
HigthStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
StrengthString = ConcreteStrength
if (ImperLevel == None):
StrengthString = StrengthString + CementName
else:
StrengthString = StrengthString + CementName + ImperLevel
if (SwellLevel != None):
StrengthString = StrengthString + '\n(' + str(
int(SwellLevel * 100)) + '%膨胀)'
AvgStrength = (LongStrength + WideStrength +
HigthStrength) / 3
LongStrength = '{:.1f}'.format(get_float(LongStrength, 1))
WideStrength = '{:.1f}'.format(get_float(WideStrength, 1))
HigthStrength = '{:.1f}'.format(get_float(
HigthStrength, 1))
AvgStrength = '{:.1f}'.format(get_float(AvgStrength, 1))
ConcreteStrength = sheet.cell(row=k,
column=3).value + CementName
ConUseRecord = Bean.ExcelBean.ConStrengUseBean(
TestName=TestName,
ProjectSite=ProjectSite,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
StrengthString=StrengthString,
CuringDate=CuringDate,
MoldingTime=MoldingTime,
LongStrength=LongStrength,
WideStrength=WideStrength,
HigthStrength=HigthStrength,
AvgStrength=AvgStrength,
CuringNum=CuringNum,
SortTime=SortTime,
)
UseRecordList.append(ConUseRecord)
TypicalStrengthSum = float(
AvgStrength) + TypicalStrengthSum
TypicalStrengthSumFlag = TypicalStrengthSumFlag + 1
TypicalStrengthList.append(AvgStrength)
CompAvgStrength = AvgStrength
ComRepoComp = Bean.ExcelBean.ConRepoCompBean(
CompStrength=CompStrength,
CompAvgStrength=CompAvgStrength)
ComRepoCompList.append(ComRepoComp)
CuringNum = CuringNum - 100
SumToStrength = (
TypicalStrengthSum / TypicalStrengthSumFlag) / float(
sheet.cell(row=k, column=3).value.strip('C'))
RepoSumRecord = Bean.ExcelBean.ConRepoSumBean(
ProjectSite=SumProjectSite,
ConcreteStrength=SumConcreteStrength,
CuringDate=SumCuringDate,
TypicalStrength=TypicalStrengthList,
ToStrength=SumToStrength,
SumSortTime=SumSortTime)
RepoSumList.append(RepoSumRecord)
elif (CuringNum >= 1000):
CementName = sheet['B' + str(k)].value.replace('砼', '')
ProjectSite = sheet.cell(row=k, column=1).value
ConcreteStrength = sheet.cell(row=k, column=3).value
ImperLevel = sheet.cell(row=k, column=4).value
SwellLevel = sheet.cell(row=k, column=5).value
CuringDate = sheet.cell(row=k, column=6).value
TestName = TestDict[ConcreteStrength] + CuringDate.strftime(
'%m%d')
TestNameList.append(TestName)
MoldingTime = (CuringDate +
timedelta(days=28)).strftime('%Y/%#m/%#d')
#
SortTime = datetime.strptime(MoldingTime, '%Y/%m/%d')
CuringDate = CuringDate.strftime('%Y/%#m/%#d')
Strength = float(ConcreteStrength.strip('C'))
LongStrength = Strength * uniform(MinC_Strength, MaxC_Strength)
WideStrength = Strength * uniform(MinC_Strength, MaxC_Strength)
HigthStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
AvgStrength = (LongStrength + WideStrength + HigthStrength) / 3
StrengthString = ConcreteStrength
if (ImperLevel == None):
StrengthString = StrengthString + CementName
else:
StrengthString = StrengthString + CementName + ImperLevel
if (SwellLevel != None):
StrengthString = StrengthString + '\n(' + str(
int(SwellLevel * 100)) + '%膨胀)'
LongStrength = '{:.1f}'.format(get_float(LongStrength, 1))
WideStrength = '{:.1f}'.format(get_float(WideStrength, 1))
HigthStrength = '{:.1f}'.format(get_float(HigthStrength, 1))
AvgStrength = '{:.1f}'.format(get_float(AvgStrength, 1))
ConcreteStrength = sheet.cell(row=k,
column=3).value + CementName
CompStrength = ConcreteStrength[1:3]
CompAvgStrength = AvgStrength
ComRepoComp = Bean.ExcelBean.ConRepoCompBean(
CompStrength=CompStrength, CompAvgStrength=CompAvgStrength)
ComRepoCompList.append(ComRepoComp)
ConUseRecord = Bean.ExcelBean.ConStrengUseBean(
TestName=TestName,
ProjectSite=ProjectSite,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
StrengthString=StrengthString,
CuringDate=CuringDate,
MoldingTime=MoldingTime,
LongStrength=LongStrength,
WideStrength=WideStrength,
HigthStrength=HigthStrength,
AvgStrength=AvgStrength,
CuringNum=CuringNum,
SortTime=SortTime,
)
SumProjectSite = ProjectSite
SumConcreteStrength = StrengthString
SumCuringDate = CuringDate
#
SumSortTime = datetime.strptime(SumCuringDate, '%Y/%m/%d')
TypicalStrengthList.append(AvgStrength)
TypicalStrengthSum = float(AvgStrength) + TypicalStrengthSum
TypicalStrengthSumFlag = TypicalStrengthSumFlag + 1
UseRecordList.append(ConUseRecord)
while (CuringNum - 200 > 0):
ProjectSite = sheet.cell(row=k, column=1).value
ConcreteStrength = sheet.cell(row=k, column=3).value
ImperLevel = sheet.cell(row=k, column=4).value
SwellLevel = sheet.cell(row=k, column=5).value
CementName = sheet['B' + str(k)].value.replace('砼', '')
CuringDate = sheet.cell(row=k, column=6).value
TestName = TestDict[
ConcreteStrength] + CuringDate.strftime('%m%d')
TestNameList.append(TestName)
MoldingTime = (CuringDate +
timedelta(days=28)).strftime('%Y/%#m/%#d')
#
SortTime = datetime.strptime(MoldingTime, '%Y/%m/%d')
CuringDate = CuringDate.strftime('%Y/%#m/%#d')
Strength = float(ConcreteStrength.strip('C'))
LongStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
WideStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
HigthStrength = Strength * uniform(MinC_Strength,
MaxC_Strength)
AvgStrength = (LongStrength + WideStrength +
HigthStrength) / 3
StrengthString = ConcreteStrength
if (ImperLevel == None):
StrengthString = StrengthString + CementName
else:
StrengthString = StrengthString + CementName + ImperLevel
if (SwellLevel != None):
StrengthString = StrengthString + '\n(' + str(
int(SwellLevel * 100)) + '%膨胀)'
LongStrength = '{:.1f}'.format(get_float(LongStrength, 1))
WideStrength = '{:.1f}'.format(get_float(WideStrength, 1))
HigthStrength = '{:.1f}'.format(get_float(
HigthStrength, 1))
AvgStrength = '{:.1f}'.format(get_float(AvgStrength, 1))
ConcreteStrength = sheet.cell(row=k,
column=3).value + CementName
ConUseRecord = Bean.ExcelBean.ConStrengUseBean(
TestName=TestName,
ProjectSite=ProjectSite,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
StrengthString=StrengthString,
CuringDate=CuringDate,
MoldingTime=MoldingTime,
LongStrength=LongStrength,
WideStrength=WideStrength,
HigthStrength=HigthStrength,
AvgStrength=AvgStrength,
CuringNum=CuringNum,
SortTime=SortTime,
)
UseRecordList.append(ConUseRecord)
TypicalStrengthSum = float(
AvgStrength) + TypicalStrengthSum
TypicalStrengthSumFlag = TypicalStrengthSumFlag + 1
TypicalStrengthList.append(AvgStrength)
CompAvgStrength = AvgStrength
ComRepoComp = Bean.ExcelBean.ConRepoCompBean(
CompStrength=CompStrength,
CompAvgStrength=CompAvgStrength)
ComRepoCompList.append(ComRepoComp)
CuringNum = CuringNum - 200
SumToStrength = (
TypicalStrengthSum / TypicalStrengthSumFlag) / float(
sheet.cell(row=k, column=3).value.strip('C'))
RepoSumRecord = Bean.ExcelBean.ConRepoSumBean(
ProjectSite=SumProjectSite,
ConcreteStrength=SumConcreteStrength,
CuringDate=SumCuringDate,
TypicalStrength=TypicalStrengthList,
ToStrength=SumToStrength,
SumSortTime=SumSortTime,
)
RepoSumList.append(RepoSumRecord)
RepoComp.append(ComRepoCompList)
# UseRecordList.sort(key=lambda x: x.SortTime, reverse=False)
UseRecord.append(UseRecordList)
# RepoSumList.sort(key=lambda x: x.SumSortTime, reverse=False)
RepoSum.append(RepoSumList)
# InspectionName = parm[0].ConStrengInspectionUtil
ProjectName = sheet['A2'].value.replace('工程名称：', '').strip()
BuildUnit = sheet['A3'].value.replace('建设单位：', '').strip()
# RepoTime = UseRecordList[-1].MoldingTime
RepoTimeList = []
for useRecord in UseRecordList:
repoTime = datetime.strptime(useRecord.MoldingTime, '%Y/%m/%d')
RepoTimeList.append(repoTime)
#
RepoTime = max(RepoTimeList)
RepoTime = RepoTime.strftime('%Y/%m/%d')
ConUseHeadRecord = Bean.ExcelBean.ConStrengRepoBean(
ProjectName=ProjectName,
BuildUnit=BuildUnit,
# InspectionName=InspectionName,
RepoTime=RepoTime)
ConStrRepoHead.append(ConUseHeadRecord)
TestNameSet = set(TestNameList)
TestNameDict = defaultdict(list)
for item in TestNameSet:
TestNameDict[item].append(TestNameList.count(item))
TestNameDict[item].append(0)
"""
获取第四份表数据
"""
repoCompBeanList = []
for repoComp in RepoComp:
repoCompStrengthList = []
# repoCompStrengthSet = []
repoCompDict = defaultdict(list)
for repoCompNum in repoComp:
repoCompStrengthList.append(repoCompNum)
# repoCompStrengthSet = list(set(repoCompStrengthList))
repoCompDict[repoCompNum.CompStrength].append(
repoCompNum.CompAvvgStrength)
repoCompBeanList.append(repoCompDict)
"""
复制第四份表模板
"""
for sheetNum in range(len(repoCompBeanList)):
rows = repoCompBeanList[sheetNum].__len__()
moreSixNum, rows = divmod(rows, 6)
if rows == 0:
moreSixNum = moreSixNum - 1
for copyNum in range(moreSixNum + 1):
modeSheet = resultRepoCompBook.worksheets[0]
iniexcel4(modeSheet)
modeSheet = resultRepoCompBook.copy_worksheet(modeSheet)
iniexcel4(modeSheet)
modeSheet.title = str(sheetNum + 1) + '-' + str(copyNum + 1)
resultRepoCompBook.remove(resultRepoCompBook.worksheets[0])
"""
插入第二份表数据
"""
curSheetNum = 0
for sheetNum in range(len(useSheets)):
useRecordNum = len(UseRecord[sheetNum])
moreFixNum, rows = divmod(useRecordNum, 15)
if rows == 0:
moreFixNum = moreFixNum - 1
for insertNum in range(moreFixNum + 1):
InsertFlag = 0
resultSheet = resultRepoBook.worksheets[curSheetNum]
resultSheet['I3'] = parm.Project2InspectCodeEdit
# resultSheet['I3'] = Project2InspectCodeEdit
resultSheet['B4'] = ConStrRepoHead[sheetNum].BuildUnit
# resultSheet['G4'] = ConStrRepoHead[sheetNum].InspectionName
resultSheet['B5'] = ConStrRepoHead[sheetNum].ProjectName
resultSheet[
'F5'] = "报告日期：" + ConStrRepoHead[sheetNum].RepoTime.replace(
'/', '.')
curSheetNum = curSheetNum + 1
for useNum in range(15):
if ((useNum + 15 * insertNum) == len(UseRecord[sheetNum])):
if ((useNum + 15 * insertNum) / 15 != 0):
resultSheet['D' + str(8 + InsertFlag)] = '以'
resultSheet['E' + str(9 + InsertFlag)] = '下'
resultSheet['F' + str(8 + InsertFlag)] = '空'
resultSheet['G' + str(8 + InsertFlag)] = '白'
break
TestName = UseRecord[sheetNum][useNum +
15 * insertNum].TestName
if (TestNameDict[UseRecord[sheetNum][
useNum + 15 * insertNum].TestName][0] != 1):
TestNameDict[UseRecord[sheetNum][
useNum +
15 * insertNum].TestName][1] = TestNameDict[UseRecord[
sheetNum][useNum + 15 * insertNum].TestName][1] + 1
TestName = UseRecord[sheetNum][
useNum + 15 * insertNum].TestName + '-' + str(
TestNameDict[UseRecord[sheetNum][
useNum + 15 * insertNum].TestName][1])
# if (TestNameDict[UseRecord[sheetNum][useNum].TestName][0] != 1):
# TestNameDict[UseRecord[sheetNum][useNum].TestName][1] = TestNameDict[UseRecord[sheetNum][useNum].TestName][1] + 1
# UseRecord[sheetNum][useNum].TestName = UseRecord[sheetNum][useNum].TestName + '-' + str(TestNameDict[UseRecord[sheetNum][useNum].TestName][1])
resultSheet['A' + str(8 + InsertFlag)] = TestName
resultSheet['B' + str(8 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].ProjectSite
resultSheet['D' + str(8 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].StrengthString
resultSheet['F' + str(8 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].CuringDate.replace('/', '-')
resultSheet['G' + str(8 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].MoldingTime.replace('/', '-')
resultSheet['H' + str(8 + InsertFlag)] = '28'
resultSheet['I' + str(8 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].LongStrength
resultSheet['E' + str(8 + InsertFlag)] = "长=150"
resultSheet['E' + str(9 + InsertFlag)] = "宽=150"
resultSheet['E' + str(10 + InsertFlag)] = "高=150"
resultSheet['I' + str(9 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].WideStrength
resultSheet['I' + str(10 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].HigthStrength
resultSheet['J' + str(8 + InsertFlag)] = UseRecord[sheetNum][
useNum + 15 * insertNum].AvgStrength
InsertFlag = InsertFlag + 3
from Util.InsetPicUtil import insertpic
from Util.get_parm import get_parm
parm = get_parm()
resultSheet = insertpic(resultSheet,
picname=parm.Project2Manager,
position='C54',
width=90,
heigh=30)
resultSheet = insertpic(resultSheet,
picname=parm.Project2Checker,
position='G54')
resultSheet = insertpic(resultSheet,
picname=parm.Project2Try,
position='J54')
"""
插入第三份表数据
"""
curSheetNum = 0
for sheetNum in range(len(useSheets)):
repoSumNum = len(RepoSum[sheetNum])
moreFixNum, rows = divmod(repoSumNum, 20)
if rows == 0:
moreFixNum = moreFixNum - 1
for insertNum in range(moreFixNum + 1):
InsertFlag = 0
resultSheet = resultRepoSumBook.worksheets[curSheetNum]
curSheetNum = curSheetNum + 1
resultSheet['A4'] = "工程名称：" + ConStrRepoHead[sheetNum].ProjectName
for sumNum in range(20):
resultSheet["F3"] = parm.Project3InspectCodeEdit
if ((sumNum + 20 * insertNum) == len(RepoSum[sheetNum])):
if (sumNum + 20 * insertNum != 20):
resultSheet['D' + str(7 + InsertFlag)] = '以'
resultSheet['E' + str(7 + InsertFlag)] = '下'
resultSheet['F' + str(7 + InsertFlag)] = '空'
resultSheet['G' + str(7 + InsertFlag)] = '白'
break
repoSum = RepoSum[sheetNum][sumNum + 20 * insertNum]
resultSheet['A' +
str(7 + InsertFlag)] = sumNum + 20 * insertNum + 1
resultSheet['B' + str(7 + InsertFlag)] = repoSum.ProjectSite
resultSheet['C' +
str(7 + InsertFlag)] = repoSum.ConcreteStrength
resultSheet['D' +
str(7 + InsertFlag)] = repoSum.CuringDate.replace(
'/', '-')
resultSheet['E' + str(7 + InsertFlag)] = 28
resultSheet['F' + str(7 + InsertFlag)] = "/".join(
repoSum.TypicalStrength)
resultSheet['G' + str(7 + InsertFlag)] = repoSum.ToStrength
InsertFlag = InsertFlag + 1
resultSheet = insertpic(resultSheet,
picname=parm.Project3MakeSheet,
position='F28',
width=90,
heigh=30)
"""
插入第四份表数据
"""
curSheetNum = 0
for sheetNum in range(len(useSheets)):
CompDictKeys = list(repoCompBeanList[sheetNum].keys())
CompDictKeys.sort(key=lambda x: x, reverse=False)
repoCompRows = repoCompBeanList[sheetNum].__len__()
moreSixNum, rows = divmod(repoCompRows, 6)
if rows == 0:
moreSixNum = moreSixNum - 1
for insertNum in range(moreSixNum + 1):
InsertFlag = 0
resultSheet = resultRepoCompBook.worksheets[curSheetNum]
curSheetNum = curSheetNum + 1
curNumFlag = 0
for curNum in range(6):
if (6 * insertNum + curNumFlag == repoCompRows):
break
resultSheet['M3'] = parm.Project4InspectCodeEdit
CompList = repoCompBeanList[sheetNum][CompDictKeys[curNumFlag +
6 *
insertNum]]
avgStreng = 0
minStreng = float(CompList[0])
for compList in CompList:
compList = float(compList)
avgStreng = avgStreng + compList
if (compList < minStreng):
minStreng = compList
avgStreng = avgStreng / len(CompList)
compNum = len(CompList)
compStreng = int(CompDictKeys[curNumFlag + 6 * insertNum])
if (compNum <= 9):
parm1 = 0.95
parm2 = 1.15
resultSheet['B' + str(8 + InsertFlag)] = 'C'
resultSheet['C' + str(8 + InsertFlag)] = compStreng
resultSheet['D' + str(8 + InsertFlag)] = compNum
resultSheet['G' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(avgStreng, 1))
resultSheet['H' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(minStreng, 1))
resultSheet['M' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float(parm1 * compStreng, 2))
resultSheet['N' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float(parm2 * compStreng, 2))
# QualifiedString = "C{compStreng}：mfcu = {avgStreng}＞λ\u2083·fcu,k ={Parm_3_Streng};fcu，min = {minStreng}≥λ\u2084·fcu，k = {Parm_4_Streng}，评定合格"
QualifiedString = "C{compStreng}：mfcu = {avgStreng}＞λ\u2083·fcu,k ={Parm_3_Streng};fcu，min = {minStreng}≥λ\u2084·fcu，k = {Parm_4_Streng}，评定合格"
resultSheet['C' +
str(14 + InsertFlag)] = QualifiedString.format(
compStreng='{0:.0f}'.format(compStreng),
avgStreng='{0:.1f}'.format(
get_float(avgStreng, 1)),
Parm_3_Streng='{0:.2f}'.format(
get_float((parm2 * compStreng), 2)),
minStreng=minStreng,
Parm_4_Streng='{0:.2f}'.format(
get_float((parm1 * compStreng), 2)))
elif (compNum > 9 and compNum <= 14):
parm1 = 1.15
parm2 = 0.9
standParm = 2.50
resultSheet['B' + str(8 + InsertFlag)] = 'C'
resultSheet['C' + str(8 + InsertFlag)] = compStreng
resultSheet['D' + str(8 + InsertFlag)] = compNum
resultSheet['E' + str(8 + InsertFlag)] = parm1
resultSheet['F' + str(8 + InsertFlag)] = parm2
resultSheet['G' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(avgStreng, 1))
resultSheet['H' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(minStreng, 1))
resultSheet['I' + str(8 + InsertFlag)] = standParm
resultSheet['J' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float((float(
resultSheet['G' + str(8 + InsertFlag)].value) -
(parm1 * standParm)), 2))
resultSheet['L' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float((parm2 * compStreng), 2))
QualifiedString = "C{compStreng}：mfcu = {avgStreng}＞fcu，k+λ\u2081·Sfcu ={Parm_1_Streng};fcu，min = {minStreng}≥λ\u2082·fcu，k = {Parm_2_Streng}，评定合格"
resultSheet['C' +
str(14 + InsertFlag)] = QualifiedString.format(
compStreng='{0:.0f}'.format(compStreng),
avgStreng='{0:.1f}'.format(
get_float(avgStreng, 1)),
Parm_1_Streng='{0:.2f}'.format(
get_float(
(parm1 * standParm + compStreng),
2)),
minStreng='{0:.1f}'.format(
get_float(minStreng, 1)),
Parm_2_Streng='{0:.2f}'.format(
get_float((parm2 * compStreng), 2)))
elif (compNum > 14 and compNum <= 19):
parm1 = 1.05
parm2 = 0.85
standParm = 2.50
resultSheet['B' + str(8 + InsertFlag)] = 'C'
resultSheet['C' + str(8 + InsertFlag)] = compStreng
resultSheet['D' + str(8 + InsertFlag)] = compNum
resultSheet['E' + str(8 + InsertFlag)] = parm1
resultSheet['F' + str(8 + InsertFlag)] = parm2
resultSheet['G' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(avgStreng, 1))
resultSheet['H' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(minStreng, 1))
resultSheet['I' + str(8 + InsertFlag)] = standParm
resultSheet['J' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float((float(
resultSheet['G' + str(8 + InsertFlag)].value) -
(parm1 * standParm)), 2))
resultSheet['K' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float((parm2 * compStreng), 2))
QualifiedString = "C{compStreng}：mfcu = {avgStreng}＞fcu，k+λ\u2081·Sfcu ={Parm_1_Streng};fcu，min = {minStreng}≥λ\u2082·fcu，k = {Parm_2_Streng}，评定合格"
resultSheet['C' +
str(14 + InsertFlag)] = QualifiedString.format(
compStreng='{0:.0f}'.format(compStreng),
avgStreng='{0:.1f}'.format(
get_float(avgStreng, 1)),
Parm_1_Streng='{0:.2f}'.format(
get_float(
(parm1 * standParm + compStreng),
2)),
minStreng='{0:.1f}'.format(
get_float(minStreng, 1)),
Parm_2_Streng='{0:.2f}'.format(
get_float((parm2 * compStreng), 2)))
elif (compNum >= 20):
parm1 = 0.95
parm2 = 0.85
standParm = 2.50
resultSheet['B' + str(8 + InsertFlag)] = 'C'
resultSheet['C' + str(8 + InsertFlag)] = compStreng
resultSheet['D' + str(8 + InsertFlag)] = compNum
resultSheet['E' + str(8 + InsertFlag)] = parm1
resultSheet['F' + str(8 + InsertFlag)] = parm2
resultSheet['G' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(avgStreng, 1))
resultSheet['H' + str(8 + InsertFlag)] = '{0:.1f}'.format(
get_float(minStreng, 1))
resultSheet['I' + str(8 + InsertFlag)] = standParm
resultSheet['J' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float(
float(resultSheet['G' + str(8 + InsertFlag)].value)
- (parm1 * standParm), 2))
resultSheet['K' + str(8 + InsertFlag)] = '{0:.2f}'.format(
get_float((parm2 * compStreng), 2))
QualifiedString = "C{compStreng}：mfcu = {avgStreng}＞fcu，k+λ\u2081·Sfcu ={Parm_1_Streng};fcu，min = {minStreng}≥λ\u2082·fcu，k = {Parm_2_Streng}，评定合格"
resultSheet['C' +
str(14 + InsertFlag)] = QualifiedString.format(
compStreng='{0:.0f}'.format(compStreng),
avgStreng='{0:.1f}'.format(
get_float(avgStreng, 1)),
Parm_1_Streng='{0:.2f}'.format(
get_float(
(parm1 * standParm + compStreng),
2)),
minStreng='{0:.1f}'.format(
get_float(minStreng, 1)),
Parm_2_Streng='{0:.2f}'.format(
get_float((parm2 * compStreng), 2)))
resultSheet[
'A4'] = "工程名称：" + ConStrRepoHead[sheetNum].ProjectName
resultSheet[
'A5'] = "施工单位: " + ConStrRepoHead[sheetNum].BuildUnit
InsertFlag = InsertFlag + 1
# avgStreng = mean(compList)
# minStreng = min(compList)
curNumFlag = curNumFlag + 1
from Util.InsetPicUtil import insertpic
from Util.get_parm import get_parm
parm = get_parm()
resultSheet = insertpic(resultSheet,
picname=parm.Project4Manager,
position='D21',
width=90,
heigh=30)
resultSheet = insertpic(resultSheet,
picname=parm.Project4Checker,
position='G21')
resultSheet = insertpic(resultSheet,
picname=parm.Project4Calculate,
position='K21',
width=90,
heigh=30)
return resultRepoBook, resultRepoSumBook, resultRepoCompBook
def PermeabilityTestReport(useBook, book2):
modeName = "../../Mode/10.xlsx"
modebook = load_workbook(modeName)
useSheets = useBook.sheetnames
UseRecordListArr = []
"""
获取数据
"""
ProjectNames = []
for sheetNum in range(len(useSheets)):
sheet = useBook.worksheets[sheetNum]
ProjectNames.append(sheet.cell(row=2, column=1).value)
UseRecordList = []
rows = sheet.max_row
for rowNum in range(10, rows + 1):
if sheet.cell(row=rowNum, column=1).value is None:
break
ProjectSite = sheet.cell(row=rowNum, column=1).value
ConcreteName = sheet.cell(row=rowNum, column=2).value
ConcreteStrength = sheet.cell(row=rowNum, column=3).value
ImperLevel = sheet.cell(row=rowNum, column=4).value
SwellLevel = sheet.cell(row=rowNum, column=5).value
CuringDate = sheet.cell(row=rowNum, column=6).value
CuringTime = sheet.cell(row=rowNum, column=7).value
CuringNum = sheet.cell(row=rowNum, column=8).value
ConUseRecord = ConcreteUseRecord(
ProjectSite=ProjectSite,
ConcreteName=ConcreteName,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
CuringDate=CuringDate,
CuringTime=CuringTime,
CuringNum=CuringNum)
UseRecordList.append(ConUseRecord)
UseRecordListArr.append(UseRecordList)
'''
填数据
'''
parm = get_parm()
Min = float(parm.Project10MinCreepEdit)
Max = float(parm.Project10MaxCreepEdit)
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row
ProjectName = ProjectNames[sheetNum]
count = 0
num = 0
for rowNum in range(10, rows + 1):
useMessage = UseRecordListArr[sheetNum][count]
count += 1
if useMessage.ImperLevel is not None:
num += 1
curSheet = modebook.copy_worksheet(modebook.worksheets[0])
curSheet.title = (str(sheetNum + 1) + '#' + str(num))
iniexcel(curSheet, 10)
ConcreteName = str(useMessage.ConcreteName)
ConcreteStrength = str(useMessage.ConcreteStrength).replace('C', '')
if useMessage.ImperLevel != None:
ImperLevelx = str(useMessage.ImperLevel)
else:
ImperLevelx = None
if useMessage.SwellLevel != None:
SwellLevelx = str(useMessage.SwellLevel)
else:
SwellLevelx = None
UseMix = query_mix(ConcreteName,ConcreteStrength,ImperLevelx,SwellLevelx)
# UseMix = session.query(
# Bean.DbBean.ConcreteMix).filter(
# Bean.DbBean.ConcreteMix.ConcreteName == useMessage.ConcreteName,
# Bean.DbBean.ConcreteMix.StrengthLevel == useMessage.ConcreteStrength.replace(
# 'C',
# ''),
# Bean.DbBean.ConcreteMix.ImperLevel == useMessage.ImperLevel,
# Bean.DbBean.ConcreteMix.SwellLevel == useMessage.SwellLevel).first()
# print(useMessage.ConcreteName,useMessage.ConcreteStrength,useMessage.ImperLevel,useMessage.SwellLevel)
# print(UseMix)
fill = Sheet()
fill.E3 = parm.Project10InspectCodeEdit
fill.A4 = ProjectName
fill.A5 = '工程部位：' + useMessage.ProjectSite
fill.A6 = '检验日期：' + \
str((useMessage.CuringDate + timedelta(days=+28)).strftime('%Y-%#m-%#d'))
fill.D6 = '报告日期：' + \
str((useMessage.CuringDate + timedelta(days=+31)).strftime('%Y-%#m-%#d'))
fill.F6 = '检验依据：' + \
str(parm.Project10ConTestReportTestBasisEdit)
fill.D9 = 'SJY' + \
str(useMessage.CuringDate).replace('00:00:00', '').replace('-', '')
fill.D7 = useMessage.ConcreteStrength + \
useMessage.ConcreteName.replace('砼', '')
# print(useMessage.ConcreteStrength,' ', useMessage.ConcreteName)
# print(useMessage.ConcreteStrength + useMessage.ConcreteName.replace('砼', ''))
fill.D8 = useMessage.ImperLevel
fill.D10 = str(useMessage.CuringDate.strftime('%Y-%#m-%#d'))
if useMessage.ImperLevel == 'P6':
fill.D11 = str((useMessage.CuringDate + timedelta(days=+28)).strftime('%Y-%#m-%#d')) + ' ' + str(parm.Project10TimeEdit) + ' - '\
+ str((useMessage.CuringDate + timedelta(days=+30)).strftime('%Y-%#m-%#d')) + ' ' + str(parm.Project10TimeEdit)
if useMessage.ImperLevel == 'P8':
s = int(parm.Project10TimeEdit.split(':')[0])
m = parm.Project10TimeEdit.split(':')[1]
if s >= 8:
fill.D11 = str(useMessage.CuringDate + timedelta(days=+28)).replace('00:00:00', '') + str(parm.Project10TimeEdit) + \
' - ' + str(useMessage.CuringDate + timedelta(days=+31)).replace('00:00:00', '') + str(s - 8) + ':' + str(m)
else:
fill.D11 = str(useMessage.CuringDate + timedelta(days=+28)).replace('00:00:00', '') + str(parm.Project10TimeEdit) + ' - '\
+ str(useMessage.CuringDate + timedelta(days=+31)).replace('00:00:00', '') + str(s + 16) + ':' + str(m)
fill.D13 = findWaterAsh(
sheetNum + 1,
useMessage.ConcreteName,
useMessage.ConcreteStrength,
useMessage.ImperLevel,
useMessage.SwellLevel,
book2)[0]
# print(fill.D13)
# fill.D18 = fill.D13 = findWaterAsh(sheetNum+1, useMessage.ConcreteName, useMessage.ConcreteStrength,
# useMessage.ImperLevel, useMessage.SwellLevel, book2)[1]
# fill.D13 = '0.28'
fill.D18 = '2.5'
fill.D14 = UseMix.MixRatioName
fill.D25 = UseMix.AdmixtureAmount
fill.D27 = '以下是本组（六块）的最高渗水值(mm)：' + '\n1#' +\
str(randint(Min, Max)) + '；2#' +\
str(randint(Min, Max)) + '；3#' +\
str(randint(Min, Max)) + '；4#' +\
str(randint(Min, Max)) + '；5#' +\
str(randint(Min, Max)) + '；6#' +\
str(randint(Min, Max)) + '。'
curSheet['E3'] = fill.E3
curSheet['A4'] = fill.A4
curSheet['A5'] = fill.A5
curSheet['A6'] = fill.A6
curSheet['D6'] = fill.D6
curSheet['F6'] = fill.F6
curSheet['D7'] = fill.D7
curSheet['D8'] = fill.D8
curSheet['D9'] = fill.D9
curSheet['D10'] = fill.D10
curSheet['D11'] = fill.D11
curSheet['D13'] = fill.D13
curSheet['D14'] = fill.D14
curSheet['D18'] = fill.D18
curSheet['D25'] = fill.D25
curSheet['D27'] = fill.D27
curSheet = insertpic(curSheet,parm.Project10Manager,'C29',width=80, heigh=30)
curSheet = insertpic(curSheet, parm.Project10Examine, 'D29')
curSheet = insertpic(curSheet, parm.Project10Checker, 'F29')
Idlist = []
for i in range(len(modebook.worksheets)):
Idlist.append(modebook.worksheets[i].cell(row=9, column=4).value)
# print(Idlist)
Idlist = fun(Idlist)
for i in range(len(modebook.worksheets)):
modebook.worksheets[i]['D9'] = Idlist[i]
if len(modebook.sheetnames) > 1:
modebook.remove(modebook.worksheets[0])
else:
iniexcel(modebook.worksheets[0], 10)
return modebook
def function(self):
self.OutExcelButton.clicked.connect(self.OutExcel) # 导出表格按钮
self.InSQLButton.clicked.connect(self.InSQL) # Excel导入信息按钮
self.OutPutSQLButton.clicked.connect(self.OutSQL) # 基本信息导出按钮
self.CementDateButton.clicked.connect(self.CementDate) # 水泥一览表信息按钮
self.MixpushButton.clicked.connect(self.MixDate) # 配合比表信息按钮
self.ChangePrarmerButton.clicked.connect(
self.ChangePrarmer) # 确认修改参数按钮
self.CementrefreshButton.clicked.connect(self.cement_refresh) # 刷新
self.CementsubmitButton.clicked.connect(self.cement_submit) # 提交
self.CementdelButton.clicked.connect(self.cement_del) # 删除
self.CementQueryButton.clicked.connect(self.query_cement) # 查询
self.mixrefreshButton.clicked.connect(self.mix_refresh) # 刷新
self.mixsubmitButton.clicked.connect(self.mix_submit) # 提交
self.MixDelButton.clicked.connect(self.mix_del) # 删除
self.MixqueryButton.clicked.connect(self.query_mix) # 查询
self.deletArtButton.clicked.connect(self.empty_Art) # 水泥一览表数据清空
self.deleMIXButton.clicked.connect(self.empty_Mix) # 配合比表数据清空
self.CementAddButton.clicked.connect(self.addArt) # 添加水泥按钮
self.mixAddButton.clicked.connect(self.addMix) # 添加配合比按钮
self.CementInfoButton.clicked.connect(self.CementInfo) # 选择水泥一览表记录
self.MixPoportionButton.clicked.connect(self.ConMixInsert) # 选择配合比表记录
self.ChoicConcreteUsageRecordButton.clicked.connect(
self.ChoicConcreteUsageRecord) # 选择使用记录表
self.OutSQLButton_2.clicked.connect(self.ChoicSQLPath) # 选择OutPutSQL路径
self.OutSQLButton_3.clicked.connect(self.OutPutSQL) # 导出SQL
self.OutPutButton.clicked.connect(self.ouputexcel) # 确认导出按钮
self.OutPutVoidButton.clicked.connect(self.outputvoidexcel) # 确认导出按钮
# 查看数据库是否存在
try:
parm = get_parm()
print('已获取SQL')
except BaseException:
print('创建SQL')
from DateBase.creat import creat_table
from DateBase.insert_value import parm_init
creat_table()
parm_init()
# 填充信息
parm = get_parm()
self.MinC_StrengthEdit.setText(str(parm.MinC_Strength))
self.MaxC_StrengthEdit.setText(str(parm.MaxC_Strength))
self.MinS_FinenessDensityEdit.setText(str(parm.MinS_FinenessDensity))
self.MaxS_FinenessDensityEdit.setText(str(parm.MaxS_FinenessDensity))
self.MinS_SurfaceDensityEdit.setText(str(parm.MinS_SurfaceDensity))
self.MaxS_SurfaceDensityEdit.setText(str(parm.MaxS_SurfaceDensity))
self.MinS_DensityEdit.setText(str(parm.MinS_Density))
self.MaxS_DensityEdit.setText(str(parm.MaxS_Density))
self.MinS_SlitContentEdit.setText(str(parm.MinS_SlitContent))
self.MaxS_SlitContentEdit.setText(str(parm.MaxS_SlitContent))
self.MinS_WaterContentEdit.setText(str(parm.MinS_WaterContent))
self.MaxS_WaterContentEdit.setText(str(parm.MaxS_WaterContent))
self.MinG_GrainContentEdit.setText(str(parm.MinG_GrainContent))
self.MaxG_GrainContentEdit.setText(str(parm.MaxG_GrainContent))
self.MinG_CrushLevelEdit.setText(str(parm.MinG_CrushLevel))
self.MaxG_CrushLevelEdit.setText(str(parm.MaxG_CrushLevel))
self.MinG_DensityEdit.setText(str(parm.MinG_Density))
self.MaxG_DensityEdit.setText(str(parm.MaxG_Density))
self.MinG_SlitContentEdit.setText(str(parm.MinG_SlitContent))
self.MaxG_SlitContentEdit.setText(str(parm.MaxG_SlitContent))
self.MinG_WaterContentEdit.setText(str(parm.MinG_WaterContent))
self.MaxG_WaterContentEdit.setText(str(parm.MaxG_WaterContent))
self.MinA_DensityEdit.setText(str(parm.MinA_Density))
self.MaxA_DensityEdit.setText(str(parm.MaxA_Density))
self.MinR7_CompressionEdit.setText(str(parm.MinR7_Compression))
self.MaxR7_CompressionEdit.setText(str(parm.MaxR7_Compression))
self.MinR28_CompressionEdit.setText(str(parm.MinR28_Compression))
self.MaxR28_CompressionEdit.setText(str(parm.MaxR28_Compression))
_translate = QtCore.QCoreApplication.translate
PicName = getpiname()
for i in range(len(PicName)):
self.Project1Manager.addItem("")
self.Project1Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project1FillSheeter.addItem("")
self.Project1FillSheeter.setItemText(
i, _translate("Form", PicName[i]))
self.Project2Manager.addItem("")
self.Project2Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project2Checker.addItem("")
self.Project2Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project2Try.addItem("")
self.Project2Try.setItemText(i, _translate("Form", PicName[i]))
self.Project3MakeSheet.addItem("")
self.Project3MakeSheet.setItemText(i,
_translate("Form", PicName[i]))
self.Project4Manager.addItem("")
self.Project4Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project4Checker.addItem("")
self.Project4Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project4Calculate.addItem("")
self.Project4Calculate.setItemText(i,
_translate("Form", PicName[i]))
self.Project5Manager.addItem("")
self.Project5Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project5Filler.addItem("")
self.Project5Filler.setItemText(i, _translate("Form", PicName[i]))
self.Project7Manager.addItem("")
self.Project7Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project7Checker.addItem("")
self.Project7Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project7try.addItem("")
self.Project7try.setItemText(i, _translate("Form", PicName[i]))
self.Project9Manager.addItem("")
self.Project9Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project9Checker.addItem("")
self.Project9Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project9Record.addItem("")
self.Project9Record.setItemText(i, _translate("Form", PicName[i]))
self.Project10Manager.addItem("")
self.Project10Manager.setItemText(i,
_translate("Form", PicName[i]))
self.Project10Examine.addItem("")
self.Project10Examine.setItemText(i,
_translate("Form", PicName[i]))
self.Project10Checker.addItem("")
self.Project10Checker.setItemText(i,
_translate("Form", PicName[i]))
self.Project11Manager.addItem("")
self.Project11Manager.setItemText(i,
_translate("Form", PicName[i]))
self.Project11Checker.addItem("")
self.Project11Checker.setItemText(i,
_translate("Form", PicName[i]))
self.Project1Manager.setCurrentText(str(parm.Project1Manager))
self.Project1FillSheeter.setCurrentText(str(parm.Project1FillSheeter))
self.Project2InspectCodeEdit.setText(str(parm.Project2InspectCodeEdit))
self.Project2Manager.setCurrentText(str(parm.Project2Manager))
self.Project2Checker.setCurrentText(str(parm.Project2Checker))
self.Project2Try.setCurrentText(str(parm.Project2Try))
self.Project3MakeSheet.setCurrentText(str(parm.Project3MakeSheet))
self.Project3InspectCodeEdit.setText(str(parm.Project3InspectCodeEdit))
self.Project4Manager.setCurrentText(str(parm.Project4Manager))
self.Project4Checker.setCurrentText(str(parm.Project4Checker))
self.Project4Calculate.setCurrentText(str(parm.Project4Calculate))
self.Project4InspectCodeEdit.setText(str(parm.Project4InspectCodeEdit))
self.Project5Manager.setCurrentText(str(parm.Project5Manager))
self.Project5Filler.setCurrentText(str(parm.Project5Filler))
self.Project7ConDesignSpeciEdit.setText(
str(parm.Project7ConDesignSpeciEdit))
self.Project7CodeEdit.setText(str(parm.Project7CodeEdit))
self.Project7Manager.setCurrentText(str(parm.Project7Manager))
self.Project7Checker.setCurrentText(str(parm.Project7Checker))
self.Project7try.setCurrentText(str(parm.Project7try))
self.Project9Manager.setCurrentText(str(parm.Project9Manager))
self.Project9Checker.setCurrentText(str(parm.Project9Checker))
self.Project9Record.setCurrentText(str(parm.Project9Record))
self.Project10ConTestReportTestBasisEdit.setText(
str(parm.Project10ConTestReportTestBasisEdit))
self.Project10InspectCodeEdit.setText(
str(parm.Project10InspectCodeEdit))
from PyQt5.QtCore import QTime
time = str(parm.Project10TimeEdit).split(':')
time = QTime(int(time[0]), int(time[1]))
self.Project10TimeEdit.setTime(time)
self.Project10MaxCreepEdit.setText(str(parm.Project10MaxCreepEdit))
self.Project10MinCreepEdit.setText(str(parm.Project10MinCreepEdit))
self.Project10Manager.setCurrentText(str(parm.Project10Manager))
self.Project10Examine.setCurrentText(str(parm.Project10Examine))
self.Project10Checker.setCurrentText(str(parm.Project10Checker))
self.Project11Manager.setCurrentText(str(parm.Project11Manager))
self.Project11Checker.setCurrentText(str(parm.Project11Checker))
# 插入水泥一览表
self.con2 = QSqlDatabase.addDatabase('QSQLITE')
self.con2.setDatabaseName(db_path)
# con2.exec_("PRAGMA foreign_keys = ON;")
self.CementMode = QSqlTableModel()
self.CementMode.setTable("cement_attribute_data")
self.CementMode.setSort(0, Qt.AscendingOrder)
self.CementMode.setEditStrategy(self.CementMode.OnManualSubmit)
self.CementMode.setHeaderData(0, Qt.Horizontal, "id")
self.CementMode.setHeaderData(1, Qt.Horizontal, "进场日期")
self.CementMode.setHeaderData(2, Qt.Horizontal, "水泥品种")
self.CementMode.setHeaderData(3, Qt.Horizontal, "生产厂家")
self.CementMode.setHeaderData(4, Qt.Horizontal, "生产日期")
self.CementMode.setHeaderData(5, Qt.Horizontal, "编号")
self.CementMode.setHeaderData(6, Qt.Horizontal, "数量(T)")
self.CementMode.setHeaderData(7, Qt.Horizontal, "安定性")
self.CementMode.setHeaderData(8, Qt.Horizontal, "初凝")
self.CementMode.setHeaderData(9, Qt.Horizontal, "终凝")
self.CementMode.setHeaderData(10, Qt.Horizontal, "R3抗压")
self.CementMode.setHeaderData(11, Qt.Horizontal, "R28抗压")
self.CementMode.setHeaderData(12, Qt.Horizontal, "R3抗折")
self.CementMode.setHeaderData(13, Qt.Horizontal, "R28抗折")
self.CementMode.setHeaderData(14, Qt.Horizontal, "是否优先")
self.CementMode.select()
self.CementtableView.setModel(self.CementMode)
self.CementtableView.setSelectionMode(QTableView.SingleSelection)
self.CementtableView.setSelectionBehavior(QTableView.SelectRows)
self.CementtableView.resizeColumnsToContents()
# 配合比表
self.MixMode = QSqlTableModel()
self.MixMode.setTable("concrete_mix")
self.MixMode.setSort(0, Qt.AscendingOrder)
self.MixMode.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.MixMode.setHeaderData(1 - 1, Qt.Horizontal, "名称")
self.MixMode.setHeaderData(2 - 1, Qt.Horizontal, "配合比编号")
self.MixMode.setHeaderData(3 - 1, Qt.Horizontal, "强度等级 ")
self.MixMode.setHeaderData(4 - 1, Qt.Horizontal, "抗渗等级")
self.MixMode.setHeaderData(5 - 1, Qt.Horizontal, "膨胀")
self.MixMode.setHeaderData(6 - 1, Qt.Horizontal, "配合比编号2")
self.MixMode.setHeaderData(7 - 1, Qt.Horizontal, "坍落度")
self.MixMode.setHeaderData(8 - 1, Qt.Horizontal, "标准差(MPa)")
self.MixMode.setHeaderData(9 - 1, Qt.Horizontal, "配制强度(MPa)")
self.MixMode.setHeaderData(10 - 1, Qt.Horizontal, "水W")
self.MixMode.setHeaderData(11 - 1, Qt.Horizontal, "水泥C")
self.MixMode.setHeaderData(12 - 1, Qt.Horizontal, "粉煤灰F")
self.MixMode.setHeaderData(13 - 1, Qt.Horizontal, "砂S")
self.MixMode.setHeaderData(14 - 1, Qt.Horizontal, "石G")
self.MixMode.setHeaderData(15 - 1, Qt.Horizontal, "水胶比A/P")
self.MixMode.setHeaderData(16 - 1, Qt.Horizontal, "砂率 BS")
self.MixMode.setHeaderData(17 - 1, Qt.Horizontal, "外加剂掺量A%")
self.MixMode.setHeaderData(18 - 1, Qt.Horizontal, "外加剂用量LS-JS(B)")
self.MixMode.setHeaderData(19 - 1, Qt.Horizontal, "膨胀剂用量")
self.MixMode.setHeaderData(20 - 1, Qt.Horizontal, "质量密度 (容重）Mcp")
self.MixMode.setHeaderData(21 - 1, Qt.Horizontal, "初凝时间")
self.MixMode.setHeaderData(22 - 1, Qt.Horizontal, "终凝时间")
self.MixMode.select()
self.MixtableView.setModel(self.MixMode)
self.MixtableView.setSelectionMode(QTableView.SingleSelection)
self.MixtableView.setSelectionBehavior(QTableView.SelectRows)
self.MixtableView.resizeColumnsToContents()
if self.con2.isOpen():
self.con2.close()
def PermeabilityTestReportMode(useBook):
modeName = "../../Mode/10.xlsx"
modebook = load_workbook(modeName)
useSheets = useBook.sheetnames
UseRecordListArr = []
"""
获取数据
"""
ProjectNames = []
for sheetNum in range(len(useSheets)):
sheet = useBook.worksheets[sheetNum]
ProjectNames.append(sheet.cell(row=2, column=1).value)
UseRecordList = []
rows = sheet.max_row
for rowNum in range(10, rows + 1):
if sheet.cell(row=rowNum, column=1).value is None:
break
ProjectSite = sheet.cell(row=rowNum, column=1).value
ConcreteName = sheet.cell(row=rowNum, column=2).value
ConcreteStrength = sheet.cell(row=rowNum, column=3).value
ImperLevel = sheet.cell(row=rowNum, column=4).value
SwellLevel = sheet.cell(row=rowNum, column=5).value
CuringDate = sheet.cell(row=rowNum, column=6).value
CuringTime = sheet.cell(row=rowNum, column=7).value
CuringNum = sheet.cell(row=rowNum, column=8).value
ConUseRecord = ConcreteUseRecord(
ProjectSite=ProjectSite,
ConcreteName=ConcreteName,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
CuringDate=CuringDate,
CuringTime=CuringTime,
CuringNum=CuringNum)
UseRecordList.append(ConUseRecord)
UseRecordListArr.append(UseRecordList)
'''
填数据
'''
parm = get_parm()
Min = float(parm.Project10MinCreepEdit)
Max = float(parm.Project10MaxCreepEdit)
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row
ProjectName = ProjectNames[sheetNum]
count = 0
num = 0
for rowNum in range(10, rows + 1):
useMessage = UseRecordListArr[sheetNum][count]
count += 1
if useMessage.ImperLevel is not None:
num += 1
curSheet = modebook.copy_worksheet(modebook.worksheets[0])
curSheet.title = (str(sheetNum + 1) + '#' + str(num))
iniexcel(curSheet, 10)
Idlist = []
for i in range(len(modebook.worksheets)):
Idlist.append(modebook.worksheets[i].cell(row=9, column=4).value)
# print(Idlist)
for i in range(len(modebook.worksheets)):
modebook.worksheets[i]['D9'] = Idlist[i]
if len(modebook.sheetnames) > 1:
modebook.remove(modebook.worksheets[0])
else:
iniexcel(modebook.worksheets[0], 10)
return modebook
def ConQualityRecord(useBook, book1, book2):
modeName = "../../Mode/9.xlsx"
modebook = load_workbook(modeName)
useSheets = useBook.sheetnames
UseRecordListArr = []
"""
获取数据
"""
ProjectNames = []
ProjectUnits = []
for sheetNum in range(len(useSheets)):
sheet = useBook.worksheets[sheetNum]
ProjectNames.append(sheet.cell(row=2, column=1).value)
ProjectUnits.append(sheet.cell(row=4, column=1).value)
UseRecordList = []
rows = sheet.max_row
for rowNum in range(10, rows + 1):
if sheet.cell(row=rowNum, column=1).value == None:
break
ProjectSite = sheet.cell(row=rowNum, column=1).value
ConcreteName = sheet.cell(row=rowNum, column=2).value
ConcreteStrength = sheet.cell(row=rowNum, column=3).value
ImperLevel = sheet.cell(row=rowNum, column=4).value
SwellLevel = sheet.cell(row=rowNum, column=5).value
CuringDate = sheet.cell(row=rowNum, column=6).value
CuringTime = sheet.cell(row=rowNum, column=7).value
CuringNum = sheet.cell(row=rowNum, column=8).value
ConUseRecord = ConcreteUseRecord(ProjectSite=ProjectSite,
ConcreteName=ConcreteName,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
CuringDate=CuringDate,
CuringTime=CuringTime,
CuringNum=CuringNum)
UseRecordList.append(ConUseRecord)
UseRecordListArr.append(UseRecordList)
'''
填数据
'''
curSheetNum = 0
parm = get_parm()
MinS_SlitContent = float(parm.MinS_SlitContent)
MaxS_SlitContent = float(parm.MaxS_SlitContent)
MinG_SlitContent = float(parm.MinG_SlitContent)
MaxG_SlitContent = float(parm.MaxG_SlitContent)
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row
ProjectName = ProjectNames[sheetNum].replace('工程名称：', '')
ProjectUnit = ProjectUnits[sheetNum].replace('施工单位：', '')
count = 0
for rowNum in range(10, rows + 1):
useMessage = UseRecordListArr[sheetNum][count]
count += 1
R28 = findR28(sheetNum + 1, rowNum - 9, book1)
R28len = len(R28)
# print(R28, sheetNum+1, rowNum - 9)
for iii in range((len(R28) + 11) // 12):
curSheet = modebook.copy_worksheet(modebook.worksheets[0])
iniexcel(curSheet, 9)
curSheet.title = str(sheetNum + 1) + '#' + str(rowNum - 9)
fill = Sheet()
fill.B4 = ProjectName
fill.G4 = '施工单位：' + ProjectUnit
fill.B6 = useMessage.ProjectSite
date = str(
useMessage.CuringDate.strftime('%Y/%#m/%#d')).split("/")
t = str(
useMessage.CuringTime.replace('~', '-').replace('_', ''))
t = t.split('-')
# print(t)
if len(t[0]) == 4:
t[0] = '0' + t[0]
if len(t[1]) == 4:
t[1] = '0' + t[1]
fill.B7 = date[0] + "年" + date[1] + "月" + date[2] + "日" + t[
0] + '-' + t[1]
# fill.C7 = useMessage.CuringTime
# print(sheetNum + 1,useMessage.ConcreteName,useMessage.ConcreteStrength,useMessage.ImperLevel,useMessage.SwellLevel)
# aa = findmix(i=sheetNum + 1, ConcreteName=useMessage.ConcreteName,
# ConcreteStrength=useMessage.ConcreteStrength,
# ImperLevel=useMessage.ImperLevel, SwellLevel=useMessage.SwellLevel, book2=book2)
# print(aa)
fill.G7, fill.H7, fill.I7, fill.J7, fill.K7, fill.M7, fill.C28, fill.L7 = \
findmix(i=sheetNum + 1, ConcreteName=useMessage.ConcreteName,
ConcreteStrength=useMessage.ConcreteStrength,
ImperLevel=useMessage.ImperLevel, SwellLevel=useMessage.SwellLevel, book2=book2)
for i in range(1, 40):
time = str((useMessage.CuringDate +
timedelta(days=-i)).strftime('%#d'))
if time == "1" or time == "10" or time == "20" or time == "30":
fill.G8 = str(
(useMessage.CuringDate + timedelta(days=-i)
).strftime('%Y/%#m/%#d')).replace('/', '-')
# print("G8: ",fill.G8)
break
# fill.G8 = str((useMessage.CuringDate + timedelta(days=-2)).strftime('%Y/%#m/%#d')).replace('/', '-')
ImperLevel = ''
SwellLevel = ''
if useMessage.ImperLevel == None:
ImperLevel = ''
else:
ImperLevel = useMessage.ImperLevel
if useMessage.SwellLevel == None:
SwellLevel = ''
else:
SwellLevel = useMessage.SwellLevel * 100
SwellLevel = '\n（' + str(int(SwellLevel)) + "%膨胀）"
fill.K8 = str(useMessage.ConcreteStrength) + str(
useMessage.ConcreteName.replace(
'砼', '')) + str(ImperLevel) + str(SwellLevel)
fill.K9 = str(
float('%.1f' % uniform(float(MinS_SlitContent),
float(MaxS_SlitContent)))) + '%'
fill.K10 = str(
float('%.1f' % uniform(float(MinG_SlitContent),
float(MaxG_SlitContent)))) + '%'
# print(R28len,iii)
if R28len >= 12:
ccc = 0
else:
ccc = R28len % 12
if ccc == 1:
fill.L28 = R28[iii * 12 + 0]
elif ccc == 2:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
elif ccc == 3:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
elif ccc == 4:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
elif ccc == 5:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
elif ccc == 6:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
elif ccc == 7:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
fill.J27 = 'R28'
fill.J28 = R28[iii * 12 + 6]
elif ccc == 8:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
fill.J27 = 'R28'
fill.J28 = R28[iii * 12 + 6]
fill.J29 = R28[iii * 12 + 7]
elif ccc == 9:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
fill.J27 = 'R28'
fill.J28 = R28[iii * 12 + 6]
fill.J29 = R28[iii * 12 + 7]
fill.J30 = R28[iii * 12 + 8]
elif ccc == 10:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
fill.J27 = 'R28'
fill.J28 = R28[iii * 12 + 6]
fill.J29 = R28[iii * 12 + 7]
fill.J30 = R28[iii * 12 + 8]
fill.I27 = 'R28'
fill.I28 = R28[iii * 12 + 9]
elif ccc == 11:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
fill.J27 = 'R28'
fill.J28 = R28[iii * 12 + 6]
fill.J29 = R28[iii * 12 + 7]
fill.J30 = R28[iii * 12 + 8]
fill.I27 = 'R28'
fill.I28 = R28[iii * 12 + 9]
fill.I29 = R28[iii * 12 + 10]
elif ccc == 0:
fill.L28 = R28[iii * 12 + 0]
fill.L29 = R28[iii * 12 + 1]
fill.L30 = R28[iii * 12 + 2]
fill.K27 = 'R28'
fill.K28 = R28[iii * 12 + 3]
fill.K29 = R28[iii * 12 + 4]
fill.K30 = R28[iii * 12 + 5]
fill.J27 = 'R28'
fill.J28 = R28[iii * 12 + 6]
fill.J29 = R28[iii * 12 + 7]
fill.J30 = R28[iii * 12 + 8]
fill.I27 = 'R28'
fill.I28 = R28[iii * 12 + 9]
fill.I29 = R28[iii * 12 + 10]
fill.I30 = R28[iii * 12 + 11]
R28len = R28len - 12
if fill.L7 != -1:
curSheet.unmerge_cells(start_row=6,
end_row=6,
start_column=12,
end_column=13)
curSheet.unmerge_cells(start_row=7,
end_row=7,
start_column=12,
end_column=13)
# 设置字体格式
from openpyxl.styles import Color, Font, Alignment
font = Font(u'宋体', size=12, color='000000')
curSheet['L6'].font = font
curSheet['L7'].font = font
curSheet['L6'].alignment = Alignment(horizontal='center',
vertical='center')
curSheet['L7'].alignment = Alignment(horizontal='center',
vertical='center')
curSheet['M6'].font = font
curSheet['M7'].font = font
curSheet['M6'].alignment = Alignment(horizontal='center',
vertical='center')
curSheet['M7'].alignment = Alignment(horizontal='center',
vertical='center')
curSheet['L6'] = '膨胀剂'
curSheet['L7'] = fill.L7
curSheet['M6'] = '外加剂'
curSheet['M7'] = fill.M7
else:
curSheet['L7'] = fill.M7
curSheet['B4'] = fill.B4
curSheet['G4'] = fill.G4
curSheet['B6'] = fill.B6
curSheet['B7'] = fill.B7
# curSheet['C7'] = fill.C7
curSheet['G7'] = fill.G7
curSheet['H7'] = fill.H7
curSheet['I7'] = fill.I7
curSheet['J7'] = fill.J7
curSheet['K7'] = fill.K7
curSheet['G8'] = fill.G8
curSheet['K8'] = fill.K8
curSheet['K9'] = fill.K9
curSheet['K10'] = fill.K10
curSheet['C28'] = fill.C28
curSheet['I27'] = fill.I27
curSheet['I28'] = fill.I28
curSheet['I29'] = fill.I29
curSheet['I30'] = fill.I30
curSheet['J27'] = fill.J27
curSheet['J28'] = fill.J28
curSheet['J29'] = fill.J29
curSheet['J30'] = fill.J30
curSheet['K27'] = fill.K27
curSheet['K28'] = fill.K28
curSheet['K29'] = fill.K29
curSheet['K30'] = fill.K30
curSheet['L28'] = fill.L28
curSheet['L29'] = fill.L29
curSheet['L30'] = fill.L30
Project9Manager = parm.Project9Manager
Project9Checker = parm.Project9Checker
Project9Record = parm.Project9Record
curSheet = insertpic(curSheet, Project9Manager, 'B32')
curSheet = insertpic(curSheet, Project9Checker, 'D32')
curSheet = insertpic(curSheet, Project9Record, 'G32', width=60)
modebook.remove(modebook.worksheets[0])
return modebook
def ConUseBuyRecord(useBook):
useSheets = useBook.sheetnames
modeName = "../../Mode/1.xlsx"
modeBook = load_workbook(modeName)
"""
第一份表操作
"""
"""
复制模板
"""
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row - 9
moreFixNum, rows = divmod(rows, 15)
if rows == 0:
moreFixNum = moreFixNum - 1
for copyNum in range(moreFixNum + 1):
modeSheet = modeBook.worksheets[0]
iniexcel1(modeSheet)
modeSheet = modeBook.copy_worksheet(modeSheet)
iniexcel1(modeSheet)
modeSheet.title = str(str(sheetNum + 1) + '-' + str(copyNum + 1))
modeBook.remove(modeBook.worksheets[0])
UseRecordListArr = []
"""
获取数据
"""
for sheetNum in range(len(useSheets)):
sheet = useBook.worksheets[sheetNum]
UseRecordList = []
rows = sheet.max_row
for rowNum in range(10, rows + 1):
if sheet.cell(row=rowNum, column=1).value == None:
break
ProjectSite = sheet.cell(row=rowNum, column=1).value
ConcreteName = sheet.cell(row=rowNum, column=2).value
ConcreteStrength = sheet.cell(row=rowNum, column=3).value
ImperLevel = sheet.cell(row=rowNum, column=4).value
SwellLevel = sheet.cell(row=rowNum, column=5).value
CuringDate = sheet.cell(row=rowNum, column=6).value
CuringTime = sheet.cell(row=rowNum, column=7).value
CuringNum = sheet.cell(row=rowNum, column=8).value
ConUseRecord = Bean.DbBean.ConcreteUseRecord(
ProjectSite=ProjectSite,
ConcreteName=ConcreteName,
ConcreteStrength=ConcreteStrength,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
CuringDate=CuringDate,
CuringTime=CuringTime,
CuringNum=CuringNum)
UseRecordList.append(ConUseRecord)
# UseRecordList.sort(key=lambda x: x.CuringDate, reverse=False)
UseRecordListArr.append(UseRecordList)
"""
获取使用记录表报告时间
"""
repoTimeList = []
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
timeList = []
rows = useSheet.max_row
for useSheetRowNum in range(10, rows + 1):
CuringDate = useSheet.cell(row=useSheetRowNum, column=6).value
timeList.append(CuringDate)
timeList.sort()
repoTimeList.append(timeList[0])
"""
插入数据
"""
curSheetNum = 0
CementAtrList = []
repoTimeIndex = 0
for sheetNum in range(len(useSheets)):
sheet = useBook.worksheets[sheetNum]
useRecordNum = len(UseRecordListArr[sheetNum])
moreFixNum, rows = divmod(useRecordNum, 15)
if rows == 0:
moreFixNum = moreFixNum - 1
CementAtrDataList = []
for insertNum in range(moreFixNum + 1):
InsertFlag = 0
resultSheet = modeBook.worksheets[curSheetNum]
ProjectName = sheet['A2'].value.replace('工程名称：', '').strip()
ConstrUnit = sheet['A4'].value.replace('施工单位：', '').strip()
resultSheet['A4'] = '工程名称：' + ProjectName
resultSheet['N4'] = ConstrUnit
curSheetNum = curSheetNum + 1
for useNum in range(15):
if ((useNum + 15 * insertNum) == len(
UseRecordListArr[sheetNum])):
if ((useNum + 15 * insertNum) / 15 != 0):
resultSheet['M' + str(7 + InsertFlag)] = '以'
resultSheet['N' + str(7 + InsertFlag)] = '下'
resultSheet['O' + str(7 + InsertFlag)] = '空'
resultSheet['P' + str(7 + InsertFlag)] = '白'
break
CementName = sheet['B' + str(10 + InsertFlag)].value.replace(
'砼', '')
UseRecord = UseRecordListArr[sheetNum][useNum + 15 * insertNum]
query = session.query(CementAttributeDatum).order_by(
CementAttributeDatum.ArrivalTime.desc())
result = query.filter(
CementAttributeDatum.PriorityLevel == 1,
CementAttributeDatum.ArrivalTime <
UseRecord.CuringDate - timedelta(days=1)).all()
CementAtr = result[0]
resultSheet['A' +
str(7 + InsertFlag)] = useNum + 15 * insertNum + 1
resultSheet['B' +
str(7 +
InsertFlag)] = CementAtr.ArrivalTime.strftime(
'%Y-%#m-%#d')
resultSheet['C' +
str(7 + InsertFlag)] = CementAtr.CementVariety
resultSheet['D' + str(7 + InsertFlag)] = CementAtr.Manufacturer
resultSheet[
'E' +
str(7 + InsertFlag)] = CementAtr.ProductionDate.strftime(
'%#m-%#d').replace('-', '月') + '日'
resultSheet['F' + str(7 + InsertFlag)] = CementAtr.CementId
resultSheet['G' + str(7 + InsertFlag)] = CementAtr.CementNumber
resultSheet[
'H' +
str(7 + InsertFlag)] = Util.ExcelUtil.CemAtrIsStablility(
CementAtr.IsStability)
if (CementAtr.InitialTime[0:1] == '0'):
CementAtr.InitialTime = CementAtr.InitialTime[1:]
if (CementAtr.FinalTime[0:1] == '0'):
CementAtr.FinalTime = CementAtr.FinalTime[1:]
resultSheet['I' + str(7 + InsertFlag)] = CementAtr.InitialTime
resultSheet['J' + str(7 + InsertFlag)] = CementAtr.FinalTime
resultSheet['K' +
str(7 + InsertFlag)] = CementAtr.R3_Compression
resultSheet['L' +
str(7 + InsertFlag)] = CementAtr.R28_Compression
resultSheet['M' + str(7 + InsertFlag)] = UseRecord.ProjectSite
# resultSheet['M' + str(7 + InsertFlag)] = Util.ExcelUtil.StringInLine(UseRecord.ProjectSite,11)
StrengthString = UseRecord.ConcreteStrength
if (UseRecord.ImperLevel == None):
StrengthString = StrengthString + CementName
else:
StrengthString = StrengthString + CementName + UseRecord.ImperLevel
if (UseRecord.SwellLevel != None):
StrengthString = StrengthString + '\n(' + str(
int(UseRecord.SwellLevel * 100)) + '%膨胀)'
resultSheet['N' + str(7 + InsertFlag)] = StrengthString
resultSheet['O' +
str(7 +
InsertFlag)] = UseRecord.CuringDate.strftime(
'%Y-%#m-%#d')
resultSheet['P' + str(7 + InsertFlag)] = UseRecord.CuringNum
from Util.InsetPicUtil import insertpic
parm = get_parm()
resultSheet = insertpic(resultSheet,
picname=parm.Project1Manager,
position='C23')
resultSheet = insertpic(resultSheet,
picname=parm.Project1FillSheeter,
position='G23',
width=90,
heigh=30)
# 按照报告时间查询水泥
QueryDate = repoTimeList[repoTimeIndex]
query = session.query(CementAttributeDatum).order_by(
CementAttributeDatum.ArrivalTime.desc())
result = query.filter(
CementAttributeDatum.PriorityLevel == 1,
CementAttributeDatum.ArrivalTime <
QueryDate - timedelta(days=29)).all()
CementAtr = result[0]
conMixCementAtr = Bean.ExcelBean.ConMixCementAtr(
ArrivalTime=CementAtr.ArrivalTime,
CementId=CementAtr.CementId,
CementName=UseRecord.ConcreteName,
ConcreteStrength=UseRecord.ConcreteStrength,
ImperLevel=Util.ExcelUtil.IsLevelNone(
UseRecord.ImperLevel),
SwellLevel=Util.ExcelUtil.IsLevelNone(
UseRecord.SwellLevel),
R3_Bending=CementAtr.R3_Bending,
R28_Bending=CementAtr.R28_Bending,
R3_Compression=CementAtr.R3_Compression,
R28_Compression=CementAtr.R28_Compression)
CementAtrDataList.append(conMixCementAtr)
InsertFlag = InsertFlag + 1
repoTimeIndex = repoTimeIndex + 1
CementAtrList.append(CementAtrDataList)
"""
第七份表操作
"""
cementAtrBeanList = []
for cementAtrBean in CementAtrList:
cementAtrDataDict = defaultdict(list)
for cementAtr in cementAtrBean:
nameString = str(cementAtr.ConcreteStrength) + '$' + str(
cementAtr.CementName) + '$' + str(
cementAtr.ImperLevel) + '$' + str(cementAtr.SwellLevel)
cementAtrDataDict[nameString].append(cementAtr)
cementAtrBeanList.append(cementAtrDataDict)
modeName = "../../Mode/7.xlsx"
CementDesignBook = load_workbook(modeName)
useSheets = useBook.sheetnames
UseRecordHeadList = []
parm = get_parm()
repoTimeIndex = 0
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
UseRecordList = []
rows = useSheet.max_row
"""
工地混凝土使用记录数据
"""
ProjectName = useSheet['A2'].value.replace('工程名称：', '').strip()
BuildUnit = useSheet['A3'].value.replace('建设单位：', '').strip()
for useSheetRowNum in range(10, rows + 1):
# CuringDate = useSheet.cell(row=useSheetRowNum, column=6).value
CuringDate = repoTimeList[repoTimeIndex]
for i in range(1, 40):
time = str((CuringDate + timedelta(days=-i)).strftime('%#d'))
if time == "1" or time == "10" or time == "20" or time == "30":
CuringDate = str(
(CuringDate + timedelta(days=-i)).strftime('%Y.%m.%d'))
break
ConcreteName = useSheet.cell(row=useSheetRowNum, column=2).value
ConcreteStrength = useSheet.cell(row=useSheetRowNum,
column=3).value
ImperLevel = useSheet.cell(row=useSheetRowNum, column=4).value
SwellLevel = useSheet.cell(row=useSheetRowNum, column=5).value
ConUseRecord = Bean.ExcelBean.ConMixUseBean(
ProjectName=ProjectName,
BuildUnit=BuildUnit,
ConcreteName=ConcreteName,
ConcreteStrength=ConcreteStrength,
ImperLevel=Util.ExcelUtil.IsLevelNone(ImperLevel),
SwellLevel=Util.ExcelUtil.IsLevelNone(SwellLevel),
CuringDate=CuringDate,
)
UseRecordList.append(ConUseRecord)
repoTimeIndex = repoTimeIndex + 1
UseRecordHeadList.append(UseRecordList)
mixDesignBeanList = []
for useRecordHeadList in UseRecordHeadList:
mixDesignDataDict = defaultdict(list)
for useRecord in useRecordHeadList:
nameString = str(useRecord.ConcreteStrength) + '$' + str(
useRecord.ConcreteName) + '$' + str(
useRecord.ImperLevel) + '$' + str(useRecord.SwellLevel)
mixDesignDataDict[nameString].append(useRecord)
mixDesignBeanList.append(mixDesignDataDict)
"""
复制模板
"""
for sheetNum in range(len(useSheets)):
rows = mixDesignBeanList[sheetNum].__len__()
for insertNum in range(rows):
modeSheet = CementDesignBook.worksheets[0]
iniexcel7(modeSheet)
modeSheet = CementDesignBook.copy_worksheet(modeSheet)
iniexcel7(modeSheet)
modeSheet.title = str(sheetNum + 1) + '-' + str(insertNum + 1)
CementDesignBook.remove(CementDesignBook.worksheets[0])
"""
插入数据
"""
curSheetNum = 0
for sheetNum in range(len(useSheets)):
mixDesignDictKeys = list(mixDesignBeanList[sheetNum].keys())
cenAtrDictKeys = list(cementAtrBeanList[sheetNum].keys())
rows = mixDesignBeanList[sheetNum].__len__()
for insertNum in range(rows):
resultSheet = CementDesignBook.worksheets[curSheetNum]
curSheetNum = curSheetNum + 1
mixDesign = mixDesignBeanList[sheetNum][
mixDesignDictKeys[insertNum]][0]
cenAtr = cementAtrBeanList[sheetNum][
mixDesignDictKeys[insertNum]][0]
mixDesignNameList = mixDesignDictKeys[insertNum].split('$')
ConcreteStrength = mixDesignNameList[0]
ConcreteName = mixDesignNameList[1]
ImperLevel = mixDesignNameList[2]
SwellLevel = mixDesignNameList[3]
if (SwellLevel != '/'):
"""
获取混合比数据
"""
queryStreng = float(ConcreteStrength.strip('C'))
queryName = ConcreteName
if (ImperLevel == '/'):
ImperLevel = None
queryImperLevel = ImperLevel
querySwellLevel = float(float(SwellLevel.strip('%')))
queryStreng = int(queryStreng)
UseMix = query_mix(ConcreteName=queryName,
StrengthLevel=queryStreng,
ImperLevel=queryImperLevel,
SwellLevel=querySwellLevel)
# resultSheet['O3'] = "设计单位:" + parm[0].ConDesignUtil
resultSheet['P4'] = "试验规格:" + parm.Project7ConDesignSpeciEdit
"""
膨胀表修改
"""
resultSheet['M8'] = "膨胀"
resultSheet['M9'] = '{0:.0f}'.format(
querySwellLevel * 100) + '%'
resultSheet['C17'] = "型号"
resultSheet['H6'] = "粉煤灰"
resultSheet['L16'] = "高效扛裂膨胀剂"
resultSheet['L17'] = "HEA"
resultSheet['P9'] = "泵送"
# 接触掺合料合并
resultSheet.unmerge_cells(start_row=19,
end_row=19,
start_column=13,
end_column=14)
resultSheet.unmerge_cells(start_row=20,
end_row=20,
start_column=13,
end_column=14)
resultSheet.unmerge_cells(start_row=24,
end_row=24,
start_column=13,
end_column=14)
resultSheet.unmerge_cells(start_row=25,
end_row=25,
start_column=13,
end_column=14)
# 设置字体格式
from openpyxl.styles import Color, Font, Alignment
font = Font(u'宋体', size=12, color='000000')
resultSheet['N19'].font = font
resultSheet['N24'].font = font
resultSheet['N19'].alignment = Alignment(horizontal='center',
vertical='center')
resultSheet['N24'].alignment = Alignment(horizontal='center',
vertical='center')
resultSheet['N20'].font = font
resultSheet['N25'].font = font
resultSheet['N20'].alignment = Alignment(horizontal='center',
vertical='center')
resultSheet['N25'].alignment = Alignment(horizontal='center',
vertical='center')
resultSheet['N19'] = "膨胀剂"
resultSheet['N24'] = "膨胀剂"
"""
插入工地混凝土使用记录
"""
resultSheet['A3'] = "建设单位：" + mixDesign.BuildUnit
resultSheet['A4'] = "工程名称：" + mixDesign.ProjectName
resultSheet['A5'] = "发报告日期：" + mixDesign.CuringDate
resultSheet['B9'] = mixDesign.ConcreteName
resultSheet['H9'] = mixDesign.ConcreteStrength
resultSheet['L9'] = mixDesign.ImperLevel
"""
插入配合比选用汇总表
"""
if (UseMix.MixRatioName == None):
resultSheet['P5'] = "编号:"
else:
resultSheet['P5'] = "编号:" + UseMix.MixRatioName
resultSheet['O9'] = str(UseMix.SlumpNum).replace('', '')
resultSheet['Q9'] = '{0:.1f}'.format(
get_float(float(UseMix.StandardDeviation), 1))
resultSheet['R9'] = '{0:.1f}'.format(
get_float(float(UseMix.ConcreteStrengh), 1))
resultSheet['P17'] = '{0:.1f}'.format(
get_float(float(UseMix.AdmixtureAmount), 1))
# resultSheet['B20'] = UseMix.CementRatio
resultSheet['L20'] = UseMix.CementNum
resultSheet['M20'] = UseMix.FlyashNum
resultSheet['N20'] = int(UseMix.SwellingNum)
resultSheet['O20'] = UseMix.SandNum
resultSheet['P20'] = UseMix.GravelNum
resultSheet['Q20'] = UseMix.WaterNum
resultSheet['R20'] = '{0:.1f}'.format(
get_float(float(UseMix.AdmixtureNum), 1))
resultSheet['S20'] = Util.ExcelUtil.IsSwellingLevelNone(
UseMix.SwellingNum)
# resultSheet['H20'] = UseMix.SwellingNum
resultSheet['B22'] = '{0:.1f}'.format(
get_float((float(UseMix.SandRatio) * 100), 1)) + "%"
resultSheet['C22'] = str(UseMix.SlumpNum).replace(' ', '')
resultSheet['C27'] = str(UseMix.SlumpNum).replace(' ', '')
"""
插入水泥购进，使用情况一览表
"""
resultSheet['M11'] = cenAtr.CementId
resultSheet['O11'] = cenAtr.R3_Bending
resultSheet['P11'] = cenAtr.R28_Bending
resultSheet['Q11'] = cenAtr.R3_Compression
resultSheet['R11'] = cenAtr.R28_Compression
"""
参数表调节数据插入
"""
compStrength = float(mixDesign.ConcreteStrength.strip('C'))
resultSheet['L13'] = '{0:.1f}'.format(
uniform(float(parm.MinS_FinenessDensity),
float(parm.MaxS_FinenessDensity)))
resultSheet['M13'] = '{0:.0f}'.format(
uniform(float(parm.MinS_SurfaceDensity),
float(parm.MaxS_SurfaceDensity)))
resultSheet['O13'] = '{0:.0f}'.format(
uniform(float(parm.MinS_Density),
float(parm.MaxS_Density)))
resultSheet['Q13'] = uniform(float(parm.MinS_SlitContent),
float(parm.MaxS_SlitContent))
resultSheet['R13'] = '{0:.0f}'.format(
uniform(float(parm.MinS_WaterContent),
float(parm.MaxS_WaterContent)))
resultSheet['R13'] = uniform(float(parm.MinS_WaterContent),
float(parm.MaxS_WaterContent))
resultSheet['M15'] = '{0:.1f}'.format(
uniform(float(parm.MinG_GrainContent),
float(parm.MaxG_GrainContent)))
resultSheet['O15'] = uniform(float(parm.MinG_CrushLevel),
float(parm.MaxG_CrushLevel))
resultSheet['P15'] = '{0:.0f}'.format(
uniform(float(parm.MinG_Density),
float(parm.MaxG_Density)))
resultSheet['Q15'] = '{0:.1f}'.format(
uniform(float(parm.MinG_SlitContent),
float(parm.MaxG_SlitContent)))
# to do
resultSheet['R15'] = '{0:.1f}'.format(
uniform(float(parm.MinG_WaterContent),
float(parm.MaxG_WaterContent)))
resultSheet['Q17'] = '{0:.1f}'.format(
uniform(float(parm.MinA_Density),
float(parm.MaxA_Density)))
resultSheet['M22'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR7_Compression),
float(parm.MaxR7_Compression)))
resultSheet['O22'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR28_Compression),
float(parm.MaxR28_Compression)))
resultSheet['M27'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR7_Compression),
float(parm.MaxR7_Compression)))
resultSheet['O27'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR28_Compression),
float(parm.MaxR28_Compression)))
"""
质量比公式数据插入
"""
# 干料
resultSheet['C20'] = 1
# resultSheet['E20'] = float(resultSheet['M20'].value) / float(resultSheet['L20'].value)
# resultSheet['G20'] = float(resultSheet['N20'].value) / float(resultSheet['L20'].value)
# resultSheet['I20'] = float(resultSheet['O20'].value) / float(resultSheet['L20'].value)
# resultSheet['K20'] = float(resultSheet['P20'].value) / float(resultSheet['L20'].value)
# resultSheet['K20'] = resultSheet['Q20'].value / resultSheet['L20'].value
admixtureNum = float(resultSheet['M20'].value) / float(
resultSheet['L20'].value)
imperviousNum = float(resultSheet['N20'].value) / float(
resultSheet['L20'].value)
sandNum = float(resultSheet['O20'].value) / float(
resultSheet['L20'].value)
stoneNum = float(resultSheet['P20'].value) / float(
resultSheet['L20'].value)
waterNum = float(resultSheet['Q20'].value) / float(
resultSheet['L20'].value)
QualifiedString = " 1：{admixtureNum} ：{imperviousNum} ：{sandNum} ：{stoneNum} ：{waterNum}"
resultSheet["C20"] = QualifiedString.format(
admixtureNum='{0:.2f}'.format(admixtureNum),
imperviousNum='{0:.2f}'.format(imperviousNum),
sandNum='{0:.2f}'.format(sandNum),
stoneNum='{0:.2f}'.format(stoneNum),
waterNum='{0:.2f}'.format(waterNum),
)
# 施工
resultSheet['L25'] = resultSheet['L20'].value
resultSheet['M25'] = resultSheet['M20'].value
# 3.26
resultSheet['N25'] = resultSheet['N20'].value
resultSheet['O25'] = '{0:.0f}'.format(
float(resultSheet['O20'].value) +
float(resultSheet['O20'].value) * resultSheet['R13'].value)
resultSheet['P25'] = '{0:.0f}'.format(
float(resultSheet['P20'].value) +
float(resultSheet['P20'].value) *
(float(resultSheet['R15'].value) / 100))
# resultSheet['Q25'] = '{0:.0f}'.format(
# float(resultSheet['Q20'].value) - float(resultSheet['O20'].value) * resultSheet[
# 'R13'].value - float(resultSheet['P20'].value) * (float(resultSheet['R15'].value) / 100))
resultSheet['Q25'] = '{0:.0f}'.format(
float(resultSheet['Q20'].value) -
(float(resultSheet['O25'].value) +
float(resultSheet["P25"].value) -
float(resultSheet['O20'].value) -
float(resultSheet['P20'].value)))
resultSheet['R25'] = resultSheet['R20'].value
resultSheet['S25'] = resultSheet['S20'].value
# resultSheet['C25'] = 1
# resultSheet['E25'] = float(resultSheet['M25'].value) / float(resultSheet['L25'].value)
# resultSheet['G25'] = '{0:.2f}'.format(float(resultSheet['N25'].value) / float(resultSheet['L25'].value))
# resultSheet['I25'] = '{0:.2f}'.format(float(resultSheet['O25'].value) / float(resultSheet['L25'].value))
# resultSheet['K25'] = '{0:.2f}'.format(float(resultSheet['P25'].value) / float(resultSheet['L25'].value))
# resultSheet['K25'] = '{0:.2f}'.format(float(resultSheet['Q25'].value) / float(resultSheet['L25'].value))
admixtureNum = float(resultSheet['M25'].value) / float(
resultSheet['L25'].value)
imperviousNum = float(resultSheet['N25'].value) / float(
resultSheet['L25'].value)
sandNum = float(resultSheet['O25'].value) / float(
resultSheet['L25'].value)
stoneNum = float(resultSheet['P25'].value) / float(
resultSheet['L25'].value)
waterNum = float(resultSheet['Q25'].value) / float(
resultSheet['L25'].value)
QualifiedString = " 1：{admixtureNum} ：{imperviousNum} ：{sandNum} ：{stoneNum} ：{waterNum}"
resultSheet["C25"] = QualifiedString.format(
admixtureNum='{0:.2f}'.format(
get_float(float(admixtureNum), 2)),
imperviousNum='{0:.2f}'.format(
get_float(float(imperviousNum), 2)),
sandNum='{0:.2f}'.format(get_float(float(sandNum), 2)),
stoneNum='{0:.2f}'.format(get_float(float(stoneNum), 2)),
waterNum='{0:.2f}'.format(get_float(float(waterNum), 2)),
)
resultSheet['B20'] = '{0:.2f}'.format(
float(resultSheet['Q20'].value) /
(float(resultSheet['L20'].value) +
float(resultSheet['M20'].value) +
float(resultSheet['N20'].value)))
resultSheet['B25'] = '{0:.2f}'.format(
float(resultSheet['Q25'].value) /
(float(resultSheet['L25'].value) +
float(resultSheet['M25'].value) +
float(resultSheet['N25'].value)))
resultSheet['B27'] = '{0:.1f}'.format(
float(resultSheet['O25'].value) /
(float(resultSheet['O25'].value) +
float(resultSheet['P25'].value)) * 100) + '%'
resultSheet['R13'] = '{0:.1f}'.format(
resultSheet['R13'].value * 100)
resultSheet['H22'] = '{0:.0f}'.format(
get_float(
float(resultSheet["L20"].value) +
float(resultSheet["M20"].value) +
float(resultSheet["N20"].value) +
float(resultSheet["O20"].value) +
float(resultSheet["P20"].value) +
float(resultSheet["Q20"].value) +
float(resultSheet["R20"].value), 0))
resultSheet['H27'] = '{0:.0f}'.format(
get_float(
float(resultSheet["L25"].value) +
float(resultSheet["M25"].value) +
float(resultSheet["N25"].value) +
float(resultSheet["O25"].value) +
float(resultSheet["P25"].value) +
float(resultSheet["Q25"].value) +
float(resultSheet["R25"].value), 0))
else:
"""
获取混合比数据
"""
queryStreng = float(ConcreteStrength.strip('C'))
queryName = ConcreteName
if (queryName == "泵送砼"):
resultSheet['O9'] = "泵送"
if (ImperLevel == '/'):
ImperLevel = None
if (SwellLevel == '/'):
SwellLevel = None
queryImperLevel = ImperLevel
querySwellLevel = SwellLevel
queryStreng = int(queryStreng)
UseMix = query_mix(ConcreteName=queryName,
StrengthLevel=queryStreng,
ImperLevel=queryImperLevel,
SwellLevel=querySwellLevel)
# resultSheet['O3'] = "设计单位:" + parm[0].ConDesignUtil
resultSheet['P4'] = "试验规格:" + parm.Project7ConDesignSpeciEdit
"""
插入工地混凝土使用记录
"""
resultSheet['A3'] = "建设单位：" + mixDesign.BuildUnit
resultSheet['A4'] = "工程名称：" + mixDesign.ProjectName
resultSheet['A5'] = "发报告日期：" + mixDesign.CuringDate
resultSheet['B9'] = mixDesign.ConcreteName
resultSheet['H9'] = mixDesign.ConcreteStrength
resultSheet['L9'] = mixDesign.ImperLevel
"""
插入配合比选用汇总表
"""
if (UseMix.MixRatioName == None):
resultSheet['P5'] = "编号:"
else:
resultSheet['P5'] = "编号:" + UseMix.MixRatioName
resultSheet['O9'] = UseMix.SlumpNum
resultSheet['Q9'] = '{0:.1f}'.format(
get_float(float(UseMix.StandardDeviation), 1))
resultSheet['R9'] = '{0:.1f}'.format(
get_float(float(UseMix.ConcreteStrengh), 1))
resultSheet['P17'] = '{0:.1f}'.format(
get_float(float(UseMix.AdmixtureAmount), 1))
# resultSheet['B20'] = UseMix.CementRatio
resultSheet['L20'] = UseMix.CementNum
resultSheet['M20'] = UseMix.FlyashNum
resultSheet['O20'] = UseMix.SandNum
resultSheet['P20'] = UseMix.GravelNum
resultSheet['Q20'] = UseMix.WaterNum
resultSheet['R20'] = '{0:.1f}'.format(
get_float(float(UseMix.AdmixtureNum), 1))
resultSheet['S20'] = Util.ExcelUtil.IsSwellingLevelNone(
UseMix.SwellingNum)
# resultSheet['H20'] = UseMix.SwellingNum
resultSheet['B22'] = '{0:.1f}'.format(
get_float((float(UseMix.SandRatio) * 100), 1)) + "%"
resultSheet['C22'] = UseMix.SlumpNum
resultSheet['C27'] = UseMix.SlumpNum
# resultSheet['H22'] = UseMix.MassDensity
# resultSheet['H27'] = UseMix.MassDensity
"""
插入水泥购进，使用情况一览表
"""
resultSheet['M11'] = cenAtr.CementId
resultSheet['O11'] = cenAtr.R3_Bending
resultSheet['P11'] = cenAtr.R28_Bending
resultSheet['Q11'] = cenAtr.R3_Compression
resultSheet['R11'] = cenAtr.R28_Compression
"""
参数表调节数据插入
"""
compStrength = float(mixDesign.ConcreteStrength.strip('C'))
resultSheet['L13'] = '{0:.1f}'.format(
uniform(float(parm.MinS_FinenessDensity),
float(parm.MaxS_FinenessDensity)))
resultSheet['M13'] = '{0:.0f}'.format(
uniform(float(parm.MinS_SurfaceDensity),
float(parm.MaxS_SurfaceDensity)))
resultSheet['O13'] = '{0:.0f}'.format(
uniform(float(parm.MinS_Density),
float(parm.MaxS_Density)))
resultSheet['Q13'] = uniform(float(parm.MinS_SlitContent),
float(parm.MaxS_SlitContent))
resultSheet['P13'] = '{0:.1f}'.format(
uniform(float(parm.MinS_WaterContent),
float(parm.MaxS_WaterContent)))
resultSheet['R13'] = uniform(float(parm.MinS_WaterContent),
float(parm.MaxS_WaterContent))
resultSheet['M15'] = '{0:.1f}'.format(
uniform(float(parm.MinG_GrainContent),
float(parm.MaxG_GrainContent)))
resultSheet['O15'] = uniform(float(parm.MinG_CrushLevel),
float(parm.MaxG_CrushLevel))
resultSheet['P15'] = '{0:.0f}'.format(
uniform(float(parm.MinG_Density),
float(parm.MaxG_Density)))
resultSheet['Q15'] = '{0:.1f}'.format(
uniform(float(parm.MinG_SlitContent),
float(parm.MaxG_SlitContent)))
# to do
resultSheet['R15'] = '{0:.1f}'.format(
uniform(float(parm.MinG_WaterContent),
float(parm.MaxG_WaterContent)))
resultSheet['Q17'] = '{0:.1f}'.format(
uniform(float(parm.MinA_Density),
float(parm.MaxA_Density)))
resultSheet['M22'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR7_Compression),
float(parm.MaxR7_Compression)))
resultSheet['O22'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR28_Compression),
float(parm.MaxR28_Compression)))
resultSheet['M27'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR7_Compression),
float(parm.MaxR7_Compression)))
resultSheet['O27'] = '{0:.1f}'.format(
compStrength * uniform(float(parm.MinR28_Compression),
float(parm.MaxR28_Compression)))
"""
质量比公式数据插入
"""
# 干料
# resultSheet['C20'] = 1
# resultSheet['E20'] = float(resultSheet['M20'].value) / float(resultSheet['L20'].value)
# resultSheet['G20'] = float(resultSheet['O20'].value) / float(resultSheet['L20'].value)
# resultSheet['I20'] = float(resultSheet['P20'].value) / float(resultSheet['L20'].value)
# resultSheet['K20'] = float(resultSheet['Q20'].value) / float(resultSheet['L20'].value)
admixtureNum = float(resultSheet['M20'].value) / float(
resultSheet['L20'].value)
# imperviousNum = float(resultSheet['N20'].value) / float(resultSheet['L20'].value)
sandNum = float(resultSheet['O20'].value) / float(
resultSheet['L20'].value)
stoneNum = float(resultSheet['P20'].value) / float(
resultSheet['L20'].value)
waterNum = float(resultSheet['Q20'].value) / float(
resultSheet['L20'].value)
QualifiedString = " 1 ：{admixtureNum} ：{sandNum} ：{stoneNum} ：{waterNum} "
resultSheet["C20"] = QualifiedString.format(
admixtureNum='{0:.2f}'.format(
get_float(float(admixtureNum), 2)),
sandNum='{0:.2f}'.format(get_float(float(sandNum), 2)),
stoneNum='{0:.2f}'.format(get_float(float(stoneNum), 2)),
waterNum='{0:.2f}'.format(get_float(float(waterNum), 2)),
)
# 施工
resultSheet['L25'] = resultSheet['L20'].value
resultSheet['M25'] = resultSheet['M20'].value
resultSheet['O25'] = '{0:.0f}'.format(
float(resultSheet['O20'].value) +
float(resultSheet['O20'].value) * resultSheet['R13'].value)
resultSheet['P25'] = '{0:.0f}'.format(
float(resultSheet['P20'].value) +
float(resultSheet['P20'].value) *
(float(resultSheet['R15'].value) / 100))
# resultSheet['Q25'] = '{0:.0f}'.format(
# float(resultSheet['Q20'].value) - float(resultSheet['O20'].value) * resultSheet[
# 'R13'].value - float(resultSheet['P20'].value) * (float(resultSheet['R15'].value) / 100))
resultSheet['Q25'] = '{0:.0f}'.format(
float(resultSheet['Q20'].value) -
(float(resultSheet['O25'].value) +
float(resultSheet["P25"].value) -
float(resultSheet['O20'].value) -
float(resultSheet['P20'].value)))
resultSheet['R25'] = resultSheet['R20'].value
resultSheet['S25'] = resultSheet['S20'].value
# resultSheet['C25'] = 1
# resultSheet['E25'] = float(resultSheet['M25'].value) / float(resultSheet['L25'].value)
# resultSheet['G25'] = '{0:.2f}'.format(float(resultSheet['O25'].value) / float(resultSheet['L25'].value))
# resultSheet['I25'] = '{0:.2f}'.format(float(resultSheet['P25'].value) / float(resultSheet['L25'].value))
# resultSheet['K25'] = '{0:.2f}'.format(float(resultSheet['Q25'].value) / float(resultSheet['L25'].value))
admixtureNum = float(resultSheet['M25'].value) / float(
resultSheet['L25'].value)
# imperviousNum = float(resultSheet['N25'].value) / float(resultSheet['L25'].value)
sandNum = float(resultSheet['O25'].value) / float(
resultSheet['L25'].value)
stoneNum = float(resultSheet['P25'].value) / float(
resultSheet['L25'].value)
waterNum = float(resultSheet['Q25'].value) / float(
resultSheet['L25'].value)
QualifiedString = " 1 ：{admixtureNum} ：{sandNum} ：{stoneNum} ：{waterNum} "
resultSheet["C25"] = QualifiedString.format(
admixtureNum='{0:.2f}'.format(
get_float(float(admixtureNum), 2)),
# imperviousNum='{0:.2f}'.format(imperviousNum),
sandNum='{0:.2f}'.format(get_float(float(sandNum), 2)),
stoneNum='{0:.2f}'.format(get_float(float(stoneNum), 2)),
waterNum='{0:.2f}'.format(get_float(float(waterNum), 2)),
)
resultSheet['B20'] = '{0:.2f}'.format(
float(resultSheet['Q20'].value) /
(float(resultSheet['L20'].value) +
float(resultSheet['M20'].value)))
resultSheet['B25'] = '{0:.2f}'.format(
float(resultSheet['Q25'].value) /
(float(resultSheet['L25'].value) +
float(resultSheet['M25'].value)))
resultSheet['B27'] = '{0:.1f}'.format(
(float(resultSheet['O25'].value) /
(float(resultSheet['O25'].value) +
float(resultSheet['P25'].value)) * 100)) + '%'
resultSheet['R13'] = '{0:.1f}'.format(
resultSheet['R13'].value * 100)
resultSheet['H22'] = '{0:.0f}'.format(
get_float((float(resultSheet["L20"].value) +
float(resultSheet["M20"].value) +
float(resultSheet["O20"].value) +
float(resultSheet["P20"].value) +
float(resultSheet["Q20"].value) +
float(resultSheet["R20"].value)), 0))
resultSheet['H27'] = '{0:.0f}'.format(
get_float((float(resultSheet["L25"].value) +
float(resultSheet["M25"].value) +
float(resultSheet["O25"].value) +
float(resultSheet["P25"].value) +
float(resultSheet["Q25"].value) +
float(resultSheet["R25"].value)), 0))
from Util.InsetPicUtil import insertpic
resultSheet = insertpic(resultSheet,
picname=parm.Project7Manager,
position='B29',
width=90,
heigh=30)
resultSheet = insertpic(resultSheet,
picname=parm.Project7Checker,
position='M29')
resultSheet = insertpic(resultSheet,
picname=parm.Project7try,
position='Q29',
width=80,
heigh=35)
# 删除第七份表重复的
useSheets = CementDesignBook.sheetnames
num = [] # 记录要删除的号码
for i in range(len(useSheets)):
useSheet1 = CementDesignBook.worksheets[i]
name1 = useSheet1['B9'].value
strength1 = useSheet1['H9'].value
permeability1 = useSheet1['L9'].value
swell1 = useSheet1['M9'].value
projectName1 = useSheet1['A4'].value
for j in range(i):
useSheet2 = CementDesignBook.worksheets[j]
name2 = useSheet2['B9'].value
strength2 = useSheet2['H9'].value
permeability2 = useSheet2['L9'].value
swell2 = useSheet2['M9'].value
projectName2 = useSheet2['A4'].value
if (projectName1 == projectName2):
if (name1 == name2 and strength1 == strength2
and permeability1 == permeability2
and swell1 == swell2):
num.append(i)
break
for i in num:
CementDesignBook.remove(CementDesignBook.worksheets[i])
for j in range(len(num)):
num[j] = num[j] - 1
return modeBook, CementDesignBook
def CreateConFacyoryCard(useBook, buyBook):
"""
获取使用表数据
"""
useSheets = useBook.sheetnames
modeName = "../../Mode/5.xlsx"
modeBook = load_workbook(modeName)
conUseRecordList = []
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row
ProjectName = useSheet['A2'].value.replace('工程名称：', '').strip()
BuildUnit = useSheet['A3'].value.replace('建设单位：', '').strip()
ConstrUnit = useSheet['A4'].value.replace('施工单位：', '').strip()
ContractId = useSheet['A5'].value.replace('合同编号：', '').strip()
ProjectManager = useSheet['A6'].value.replace('项目经理：', '').strip()
conUseDataList = []
for rowNum in range(10, rows + 1):
ProjectSite = useSheet.cell(row=rowNum, column=1).value
ConcreteName = useSheet.cell(row=rowNum, column=2).value
ConcreteStrength = useSheet.cell(row=rowNum, column=3).value
ImperLevel = useSheet.cell(row=rowNum, column=4).value
SwellLevel = useSheet.cell(row=rowNum, column=5).value
CuringDate = useSheet.cell(row=rowNum, column=6).value
StrengthString = ConcreteStrength
if (ImperLevel == None):
StrengthString = StrengthString + ConcreteName.replace('砼', '')
else:
StrengthString = StrengthString + ConcreteName.replace(
'砼', '') + ImperLevel
if (SwellLevel != None):
StrengthString = StrengthString + '\n(' + str(
int(SwellLevel * 100)) + '%膨胀)'
conUseRecord = Bean.ExcelBean.FactoryConUseBean(
ProjectName=ProjectName,
BuildUnit=BuildUnit,
ConstrUnit=ConstrUnit,
ContractId=ContractId,
ProjectManager=ProjectManager,
ProjectSite=ProjectSite,
ConcreteName=ConcreteName,
ImperLevel=ImperLevel,
SwellLevel=SwellLevel,
ConcreteStrength=ConcreteStrength,
CuringDate=CuringDate,
StrengthString=StrengthString)
conUseDataList.append(conUseRecord)
"""
复制模板
"""
modeSheet = modeBook.worksheets[0]
# iniexcel5(modeSheet)
modeSheet = modeBook.copy_worksheet(modeSheet)
# print(ProjectSite, rowNum)
iniexcel5(modeSheet)
modeSheet.title = str(str(sheetNum + 1) + '-' + str(rowNum - 9))
# conUseDataList.sort(key=lambda x: x.CuringDate, reverse=False)
conUseRecordList.append(conUseDataList)
modeBook.remove(modeBook.worksheets[0])
"""
获取水泥编号
"""
curNum = 0
cenBuyList = []
for sheetNum in range(len(useSheets)):
sheet = useBook.worksheets[sheetNum]
useRecordNum = len(conUseRecordList[sheetNum])
moreFixNum, rows = divmod(useRecordNum, 15)
if (rows == 0):
moreFixNum = moreFixNum - 1
cenDataList = []
for insertNum in range(moreFixNum + 1):
buySheet = buyBook.worksheets[curNum]
InsertFlag = 0
curNum = curNum + 1
for buyNum in range(15):
# print(len(conUseRecordList[sheetNum]), buyNum + 15 * insertNum)
if ((buyNum + 15 * insertNum) == len(
conUseRecordList[sheetNum])):
break
CementId = buySheet['F' + str(7 + InsertFlag)].value
cenDataList.append(CementId)
InsertFlag = InsertFlag + 1
cenBuyList.append(cenDataList)
"""
获取配合比数据
"""
conMixList = []
for conUseRecord in conUseRecordList:
conMixDataList = []
for conUseData in conUseRecord:
queryStreng = int(conUseData.ConcreteStrength.strip('C'))
queryName = conUseData.ConcreteName
queryImperLevel = conUseData.ImperLevel
querySwellLevel = conUseData.SwellLevel
UseMix = query_mix(ConcreteName=queryName,
StrengthLevel=queryStreng,
ImperLevel=queryImperLevel,
SwellLevel=querySwellLevel)
conMixDataList.append(UseMix)
conMixList.append(conMixDataList)
"""
插入数据
"""
curSheetNum = 0
for sheetNum in range(len(useSheets)):
useSheet = useBook.worksheets[sheetNum]
rows = useSheet.max_row
for rowNum in range(10, rows + 1):
resultSheet = modeBook.worksheets[curSheetNum]
curSheetNum = curSheetNum + 1
rowIndex = rowNum - 10
resultSheet['A4'] = "出厂日期：" + conUseRecordList[sheetNum][
rowIndex].CuringDate.strftime('%Y.%m.%d')
resultSheet['e4'] = "工程名称：" + conUseRecordList[sheetNum][
rowIndex].ProjectName
resultSheet['A5'] = "合同编号：" + conUseRecordList[sheetNum][
rowIndex].ContractId
resultSheet['E5'] = "工程部位：" + conUseRecordList[sheetNum][
rowIndex].ProjectSite
resultSheet['A6'] = "建设单位：" + conUseRecordList[sheetNum][
rowIndex].BuildUnit
resultSheet['E6'] = "施工单位：" + conUseRecordList[sheetNum][
rowIndex].ConstrUnit
resultSheet['A7'] = "强度等级：" + conUseRecordList[sheetNum][
rowIndex].StrengthString
resultSheet['E7'] = "工程负责人：" + conUseRecordList[sheetNum][
rowIndex].ProjectManager
resultSheet['C10'] = str(cenBuyList[sheetNum][rowIndex])
resultSheet['A14'] = '{0:.0f}'.format(
float(conMixList[sheetNum]
[rowIndex].MassDensity)) + "±40" + "kg/m³"
resultSheet['C14'] = conMixList[sheetNum][rowIndex].SlumpNum
resultSheet['D14'] = conMixList[sheetNum][rowIndex].InitialTime
resultSheet['F14'] = conMixList[sheetNum][rowIndex].FinalTime
# resultSheet['E18'] = "三水区建友混凝土有限公司试验室"
resultSheet['F20'] = conUseRecordList[
sheetNum][rowIndex].CuringDate.strftime('%Y$%#m-%#d').replace(
'$', '年').replace('-', '月') + '日'
# 3.28
if (conUseRecordList[sheetNum][rowIndex].SwellLevel != None):
resultSheet.unmerge_cells(start_row=8,
end_row=8,
start_column=7,
end_column=8)
resultSheet.unmerge_cells(start_row=9,
end_row=10,
start_column=7,
end_column=8)
resultSheet.merge_cells(start_row=9,
end_row=10,
start_column=7,
end_column=7)
resultSheet.merge_cells(start_row=9,
end_row=10,
start_column=8,
end_column=8)
resultSheet.unmerge_cells(start_row=11,
end_row=11,
start_column=7,
end_column=8)
# 设置字体格式
from openpyxl.styles import Color, Font, Alignment, Border
font = Font(u'宋体', size=12, color='000000')
resultSheet['H8'].font = font
resultSheet['H9'].font = font
resultSheet['G9'].font = font
resultSheet['H8'].alignment = Alignment(horizontal='center',
vertical='center')
resultSheet['H9'].alignment = Alignment(horizontal='center',
vertical='center',
wrap_text='True')
resultSheet['G9'].alignment = Alignment(horizontal='center',
vertical='center',
wrap_text='True')
resultSheet['H11'].font = font
resultSheet['H11'].alignment = Alignment(horizontal='center',
vertical='center')
resultSheet['G9'] = "HEA型高效抗裂膨胀剂"
resultSheet['G8'] = "膨胀剂"
resultSheet['H8'] = "外加剂"
resultSheet['H9'] = "LS-JS(B)高效减水剂"
resultSheet['H11'] = "合格"
from Util.InsetPicUtil import insertpic
from Util.get_parm import get_parm
parm = get_parm()
resultSheet = insertpic(resultSheet,
picname=parm.Project5Manager,
position='B17',
width=90,
heigh=30)
resultSheet = insertpic(resultSheet,
picname=parm.Project5Filler,
position='F17',
width=90,
heigh=30)
resultSheet = iniexcel5(resultSheet)
# """
# 水下砼合并
# """
# length = len(modeBook.sheetnames)
# for i in range(length-1, -1, -1):
# if str(modeBook.worksheets[i]['A7'].value)[-2:] == '水下':
# for j in range(i - 1, -1, -1):
# if str(modeBook.worksheets[i]['A4'].value) == str(modeBook.worksheets[j]['A4'].value):
# modeBook.worksheets[j]['E5'] = str(modeBook.worksheets[j]['E5'].value) + '、' +\
# str(modeBook.worksheets[i]['E5'].value).split('：')[-1]
# modeBook.remove(modeBook.worksheets[i])
# break
return modeBook