def replace_sheet(src, dst):
excel = client.Dispatch("Excel.Application")
excel.Visible = False # True if one wants to Excel app be opened
excel.DisplayAlerts = False # True if one wants to display excel alerts
wb1 = excel.Workbooks.Open(src)
ws1 = wb1.Worksheets(1)
if os.path.isfile(dst):
wb2 = excel.Workbooks.Open(dst)
wb2_sheet_list = [sheet.Name for sheet in wb2.Sheets]
if "06_1_VT" in wb2_sheet_list:
wb2.Worksheets(wb2_sheet_list.index("06_1_VT") + 1).Delete()
if "07_Cleanlines" in wb2_sheet_list:
ws1.Copy(
Before=wb2.Worksheets(wb2_sheet_list.index("07_Cleanlines")))
else:
ws1.Copy(Before=wb2.Worksheets(1))
else:
wb2 = Workbook()
wb2.save(dst)
wb2.close()
wb2 = excel.Workbooks.Open(dst)
ws1.Copy(Before=wb2.Worksheets(1))
wb2.Close(SaveChanges=True)
excel.Quit()
wb.save("aws-config-report-temp.xlsx")
print('Sheets all done')
PATH_TO_PDF = 'AWS_Config_Report.pdf'
excel = win32com.client.Dispatch("Excel.application")
try:
print('Starting convert to PDF')
wb = excel.Workbooks.Open(os.path.abspath('aws-config-report-temp.xlsx'))
time.sleep(1)
ws_list = ['Result by Rule', 'Evaluated Resources', 'Evaluation Summary']
wb.WorkSheets(ws_list).Select()
excel.ActiveSheet.ExportAsFixedFormat(0, os.path.abspath(PATH_TO_PDF))
except com_error as e:
print('converting failed.')
print(e)
else:
print('PDF file saved')
finally:
wb.Close(False)
excel.Quit()
os.remove(os.path.abspath("aws-config-report-temp.xlsx"))
###fortest os.remove(os.environ['USERPROFILE'] + r'\.aws\credentials')
###fortest os.remove(os.environ['USERPROFILE'] + r'\.aws\config')
###fortest os.rmdir(os.environ['USERPROFILE'] + r'\.aws'\config'))
print("Automatically closing...")
###########################################################################################
def output():
dcit_request = request.get_json()
username = dcit_request['username']
folderPath = dcit_request['folderPath']
imgsPath = dcit_request['imgsPath']
fileList = dcit_request['fileList']
result1 = dcit_request['result1']
result2 = dcit_request['result2']
imgsNum = dcit_request['imgsNum']
duplicateImgsNum = dcit_request['duplicateImgsNum']
nonDuplicateImgsData = dcit_request['nonDuplicateImgsData']
nonDuplicateImgsNum = len(nonDuplicateImgsData)
for file in fileList:
if str(file).split('.')[-1].lower() == 'docx':
shutil.copy(os.path.join(folderPath, file),
os.path.join(wordsPath, file))
elif str(file).split('.')[-1].lower() == 'xlsx':
shutil.copy(os.path.join(folderPath, file),
os.path.join(excelsPath, file))
elif str(file).split('.')[-1].lower() == 'pdf':
shutil.copy(os.path.join(folderPath, file),
os.path.join(pdfsPath, file))
for imgData in nonDuplicateImgsData:
shutil.copy(os.path.join(imgsPath, imgData['imgName']), os.path.join(
rawImgsPath, imgData['imgName']))
wb = Workbook()
wb.create_sheet("工作表1", 0)
sht = wb['工作表1']
sht.merge_cells('A1:E1')
sht['A1'] = '工程案件相片重複性辨識'
sht['A1'].font = Font(size=16, b=True, underline='single')
sht['A1'].alignment = Alignment(horizontal='center', vertical='center')
fileName = fileList[0].split('.')[0]
sht['A2'] = '工程檔案名稱：' + fileName
sht['E2'] = '日期：' + datetime.now().strftime("%Y/%m/%d")
sht['A2'].font = Font(size=14, b=True)
sht['E2'].font = Font(size=14, b=True)
sht.column_dimensions["A"].width = 40
sht.column_dimensions["B"].width = 25
sht.column_dimensions["C"].width = 40
sht.column_dimensions["D"].width = 25
sht.column_dimensions["E"].width = 25
sht.merge_cells('A3:E3')
sht['A3'].font = Font(size=14, b=True)
if duplicateImgsNum == 0:
sht['A3'] = '[系統比對結果]無重複相片(系統比對相片數量：' + str(imgsNum) + \
'張，寫入資料庫相片數量：' + str(imgsNum) + '張)'
elif imgsNum == duplicateImgsNum:
sht['A3'] = '[系統比對結果]全部相片重複(系統比對相片數量：' + str(imgsNum) + '張)'
else:
sht['A3'] = '[系統比對結果]部分相片重複，重複相片數量：' + \
str(duplicateImgsNum) + '張(系統比對相片數量：' + str(imgsNum) + '張)'
line = Side(style='thin', color='000000')
border = Border(top=line, bottom=line, left=line, right=line)
i = 4
if len(result1) != 0:
sht.merge_cells('A'+str(i)+':B'+str(i))
sht['A'+str(i)] = '上傳的相片'
sht['A'+str(i)].font = Font(b=True)
sht['A'+str(i)].border = border
sht['A'+str(i)].alignment = Alignment(horizontal='center',
vertical='center')
sht.merge_cells('C'+str(i)+':D'+str(i))
sht['C'+str(i)] = '上傳的相片'
sht['C'+str(i)].font = Font(b=True)
sht['C'+str(i)].border = border
sht['C'+str(i)].alignment = Alignment(horizontal='center',
vertical='center')
sht['E'+str(i)] = '說明/備註'
sht['E'+str(i)].font = Font(b=True)
sht['E'+str(i)].border = border
sht['E'+str(i)].alignment = Alignment(horizontal='center',
vertical='center')
i = i+1
for item in result1:
sht['A'+str(i)].border = border
sht['B'+str(i)].border = border
sht['C'+str(i)].border = border
sht['D'+str(i)].border = border
sht['E'+str(i)].border = border
sht.row_dimensions[i].height = 80
sht['A'+str(i)] = item['imgName1']
sht.add_image(Image_xl(os.path.join(
resizeImgsPath, item['imgName1'])), "B"+str(i))
sht['C'+str(i)] = item['imgName2']
sht.add_image(Image_xl(os.path.join(
resizeImgsPath, item['imgName2'])), "D"+str(i))
i = i + 1
if len(result2) != 0:
sht.merge_cells('A'+str(i)+':B'+str(i))
sht['A'+str(i)] = '上傳的相片'
sht['A'+str(i)].font = Font(b=True)
sht['A'+str(i)].border = border
sht['A'+str(i)].alignment = Alignment(horizontal='center',
vertical='center')
sht.merge_cells('C'+str(i)+':D'+str(i))
sht['C'+str(i)] = '資料庫的相片'
sht['C'+str(i)].font = Font(b=True)
sht['C'+str(i)].border = border
sht['C'+str(i)].alignment = Alignment(horizontal='center',
vertical='center')
sht['E'+str(i)] = '說明/備註'
sht['E'+str(i)].font = Font(b=True)
sht['E'+str(i)].border = border
sht['E'+str(i)].alignment = Alignment(horizontal='center',
vertical='center')
i = i+1
for item in result2:
sht['A'+str(i)].border = border
sht['B'+str(i)].border = border
sht['C'+str(i)].border = border
sht['D'+str(i)].border = border
sht['E'+str(i)].border = border
sht.row_dimensions[i].height = 80
sht['A'+str(i)] = item['imgName1']
sht.add_image(Image_xl(os.path.join(
resizeImgsPath, item['imgName1'])), "B"+str(i))
sht['C'+str(i)] = item['imgName2']
sht.add_image(Image_xl(os.path.join(
resizeImgsPath, item['imgName2'])), "D"+str(i))
i = i + 1
sht.page_setup.orientation = sht.ORIENTATION_LANDSCAPE
sht.page_setup.fitToHeight = False
sht.sheet_properties.pageSetUpPr.fitToPage = True
resultFileName = datetime.today().strftime(
"%Y%m%d%H%M") + '_' + fileName + '_比對結果.xlsx'
wb.save(os.path.join(excelResultsPath, resultFileName))
wb.close()
pythoncom.CoInitialize()
app = client.DispatchEx("Excel.Application")
pythoncom.CoInitialize()
wb = app.Workbooks.Open(os.path.join(excelResultsPath, resultFileName))
ws = wb.Worksheets[0]
ws.Visible = 1
resultFileName = datetime.today().strftime(
"%Y%m%d%H%M") + '_' + fileName + '_比對結果.pdf'
ws.ExportAsFixedFormat(0, os.path.join(resultsPath, resultFileName))
wb.Close()
app.Quit()
f = open(os.path.join(resultsPath, resultFileName), 'rb')
cu = cx.cursor()
for file in fileList:
cu.execute("insert into UploadRecord values(null, '" + datetime.now().strftime(
"%Y/%m/%d %H:%M") + "', '" + file + "', '" + username + "', '" + resultFileName + "')")
for imgData in nonDuplicateImgsData:
cu.execute("SELECT * FROM Image WHERE imageName = '" +
imgData['imgName'] + "'")
data = cu.fetchall()
if len(data) == 0:
cu.execute("insert into Image values(null, '" +
imgData['imgName'] + "', '" + imgData['pHash'] + "', '" + imgData['group'] + "', '" + username + "')")
else:
cu.execute("update Image SET pHash = '" + imgData['pHash'] + "', g = '" + imgData['group'] +
"', uploadUser = '******' WHERE imageName like '" + imgData['imgName'] + "'")
cu.close()
cx.commit()
return f.read()
app.Exit()
'''
name_files = [
'new_outfile0.xlsx', 'new_outfile1.xlsx', 'new_outfile2.xlsx',
'new_outfile3.xlsx', 'new_outfile4.xlsx', 'new_outfile5.xlsx',
'new_outfile6.xlsx', 'new_outfile7.xlsx', 'new_outfile8.xlsx',
'new_outfile9.xlsx', 'new_outfile10.xlsx', 'new_outfile11.xlsx',
'new_outfile12.xlsx', 'new_outfile13.xlsx', 'new_outfile14.xlsx',
'new_outfile15.xlsx', 'new_outfile16.xlsx', 'new_outfile17.xlsx',
'new_outfile18.xlsx', 'new_outfile19.xlsx'
]
for i in range(0, len(name_files)):
input_file = r'C:\Projects\ReportCard' + '\\new_outfile' + str(i) + '.xlsx'
#give your file name with valid path
output_file = r'C:\Projects\ReportCard\dummy_pdfs' + '\\dummy' + str(
i) + '.pdf'
#give valid output file name and path
app = client.DispatchEx("Excel.Application")
app.Interactive = False
app.Visible = False
Workbook = app.Workbooks.Open(input_file)
try:
Workbook.ActiveSheet.ExportAsFixedFormat(0, output_file)
except Exception as e:
print(
"Failed to convert in PDF format.Please confirm environment meets all the requirements and try again"
)
print(str(e))
finally:
Workbook.Close()
