# -*- coding: utf-8 -*- """Google Sheet to XML.ipynb Automatically generated by Colaboratory. Original file is located at https://colab.research.google.com/drive/1T-b1N8Gq6wwbBurODzJIdhRQNeRbyBnz """ from googleapiclient.discovery import build from google.oauth2 import service_account import pygsheets import ast import re import pandas as pd from google.oauth2 import service_account from googleapiclient.discovery import build import pygsheets import re def authorizeLegend(): SCOPES = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/drive.metadata' ] credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES) spreadsheet_service = build('sheets', 'v4', credentials=credentials) drive_service = build('drive', 'v3', credentials=credentials) gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json') return spreadsheet_service,drive_service,gc spreadsheet_service,drive_service,gc=authorizeLegend() def create_new_sheet(path): spreadsheet_details = { 'properties': { 'title': path } } sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, fields='spreadsheetId').execute() spreadsheetId = sheet.get('spreadsheetId') permission1 = { 'type': 'anyone', 'role': 'writer', } drive_service.permissions().create(fileId=spreadsheetId, body=permission1, supportsAllDrives=True).execute() return spreadsheetId def update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath): ws = gc.open_by_key(spreadsheetId) worksheet = ws.worksheet(0) worksheet.title = 'Legend and data created' worksheet.clear() ws.create_developer_metadata('path', pdfpath) splittedpdfpath = re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,/<>?]', pdfpath) if splittedpdfpath[-2].startswith('2.2') or splittedpdfpath[-2].startswith('2.1'): worksheet.set_dataframe(start='A1', df=SimilarAreaDictionary) else: top_header_format = [ {'mergeCells': { # areas 'mergeType': 'MERGE_ROWS', 'range': { 'sheetId': '0', 'startRowIndex': 1, 'endRowIndex': 2, 'startColumnIndex': 3, 'endColumnIndex': 5 } }}, {'mergeCells': { # perimeters 'mergeType': 'MERGE_ROWS', 'range': { 'sheetId': '0', 'startRowIndex': 1, 'endRowIndex': 2, 'startColumnIndex': 5, 'endColumnIndex': 7 } }}, {'mergeCells': { # lengths 'mergeType': 'MERGE_ROWS', 'range': { 'sheetId': '0', 'startRowIndex': 1, 'endRowIndex': 2, 'startColumnIndex': 7, 'endColumnIndex': 9 } }}, {'mergeCells': { # legend and data created 'mergeType': 'MERGE_ROWS', 'range': { 'sheetId': '0', 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 11 } }} ] spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': top_header_format}).execute() worksheet.cell((1, 1)).value = 'Legend and Data Created' worksheet.cell((2, 1)).value = 'Guess' worksheet.cell((2, 2)).value = 'Color' worksheet.cell((2, 3)).value = 'Count' worksheet.cell((2, 4)).value = 'Areas' worksheet.cell((2, 6)).value = 'Perimeters' worksheet.cell((2, 8)).value = 'Lengths' worksheet.cell((2, 10)).value = 'Texts' worksheet.cell((2, 11)).value = 'Comments' second_row_data = ['Nr', 'm2', 'Total', 'm', 'Total', 'm', 'Total'] worksheet.update_row(3, second_row_data, col_offset=2) worksheet.update_col(1, list(SimilarAreaDictionary['Guess']), row_offset=3) worksheet.update_col(3, list(SimilarAreaDictionary['Occurences']), row_offset=3) worksheet.update_col(4, list(SimilarAreaDictionary['Area']), row_offset=3) worksheet.update_col(5, list(SimilarAreaDictionary['Total Area']), row_offset=3) worksheet.update_col(6, list(SimilarAreaDictionary['Perimeter']), row_offset=3) worksheet.update_col(7, list(SimilarAreaDictionary['Total Perimeter']), row_offset=3) worksheet.update_col(8, list(SimilarAreaDictionary['Length']), row_offset=3) worksheet.update_col(9, list(SimilarAreaDictionary['Total Length']), row_offset=3) worksheet.update_col(10, list(SimilarAreaDictionary['Texts']), row_offset=3) worksheet.update_col(11, list(SimilarAreaDictionary['Comments']), row_offset=3) if splittedpdfpath[-2].startswith('1.0'): colorsUsed = [ [SimilarAreaDictionary['R'].iloc[i], SimilarAreaDictionary['G'].iloc[i], SimilarAreaDictionary['B'].iloc[i]] for i in range(len(SimilarAreaDictionary)) ] elif splittedpdfpath[-2].startswith('3.2'): colorsUsed = list(SimilarAreaDictionary['Color']) rowsLen = len(SimilarAreaDictionary.values.tolist()) lastcell = worksheet.cell((rowsLen + 2, 1)) lastcellNotation = str(lastcell.address.label) columnsLen = len(SimilarAreaDictionary.columns.values.tolist()) lastUsedCol = columnsLen + 1 worksheet.adjust_column_width(start=2, end=3) worksheet.adjust_column_width(start=10, end=10) worksheet.adjust_column_width(start=4, end=9, pixel_size=60) startrow = 3 endColindex = 11 endrow = 3 sheetId = '0' batch_requests = [] for i in range(len(colorsUsed)): r, g, b = colorsUsed[i] batch_requests.append({ "updateCells": { "range": { "sheetId": sheetId, "startRowIndex": i + startrow, "startColumnIndex": 1, }, "rows": [ { "values": [ { "userEnteredFormat": { "backgroundColor": { "red": r / 255, "green": g / 255, "blue": b / 255, "alpha": 0.4, } } } ] } ], "fields": "userEnteredFormat.backgroundColor", } }) batch_requests.append({ "updateBorders": { "range": { "sheetId": sheetId, "startRowIndex": 0, "endRowIndex": len(SimilarAreaDictionary) + endrow, "startColumnIndex": 0, "endColumnIndex": endColindex }, "top": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green": 0.0, "blue": 0.0 } }, "bottom": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green": 0.0, "blue": 0.0 } }, "left": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green": 0.0, "blue": 0.0 } }, "right": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green": 0.0, "blue": 0.0 } }, "innerHorizontal": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green": 0.0, "blue": 0.0 } }, "innerVertical": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green": 0.0, "blue": 0.0 } } } }) spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': batch_requests}).execute() model_cell = worksheet.cell('A1') model_cell.set_text_format('bold', True) model_cell.set_horizontal_alignment(pygsheets.custom_types.HorizontalAlignment.CENTER) model_cell.color = (213 / 255, 219 / 255, 255 / 255) pygsheets.DataRange('A2', 'K2', worksheet=worksheet).apply_format(model_cell) def legendGoogleSheets(SimilarAreaDictionary, path, pdfpath, spreadsheetId=0): titles = gc.spreadsheet_titles() if path in titles: ws = gc.open(path) spreadsheetId = ws.id else: spreadsheetId = create_new_sheet(path) ws=gc.open_by_key(spreadsheetId) update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath) spreadsheet_url = f"https://docs.google.com/spreadsheets/d/{spreadsheetId}" drive_service.permissions().update(transferOwnership=True, fileId=spreadsheetId, permissionId='11OfoB4Z6wOVII8mYmbnCbbqTQs7rYA65') namepathArr = [path, spreadsheetId, ws.get_developer_metadata('path')[0].value] return gc, spreadsheet_service, spreadsheetId, spreadsheet_url, namepathArr ####################### def mapnametoLegend(McTName): sectionKey = McTName.pop() key=sectionKey[0] section=sectionKey[1] # spreadsheet_service,drive_service,gc=authorizeLegend() spreadsheet_key =str(key) # Please set the Spreadsheet ID. ws = gc.open_by_key(spreadsheet_key) # guessednamesfinal=getguessnames(gc,ws) sheetnames=[] unit='' # ws.add_worksheet("Summary") # Please set the new sheet name. for i in ws._sheet_list: print(i) sheetnames.append(i.title) print(i.index) if 'XML Export Summary' in sheetnames: worksheetS = ws.worksheet_by_title('XML Export Summary') else: ws.add_worksheet("XML Export Summary") # Please set the new sheet name. worksheetw = ws.worksheet(0) #legend worksheetS = ws.worksheet_by_title('XML Export Summary') summaryId= ws[1].id print('summaryyyID',summaryId) print('summaryyyID2',worksheetS.id) worksheetS.clear() countnames=0 row0=['MC_T Name','Qty','Unit'] worksheetS.update_row(1,row0) for i in range(len(McTName)): allgbnames='' item='' print(McTName[i][0]) # firstpart= re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,./<>?]', McTName[i][0]) print('kkk' ,McTName[i][2]) if McTName[i][2].startswith('Area'): if section.startswith('1.0') or section.startswith('3.2'): rowvalue=5# column 5 ar=0 unit='m2' if McTName[i][2].startswith('Perimeter'): if section.startswith('1.0') or section.startswith('3.2'): rowvalue=7# column 7 ar=0 unit='m' if McTName[i][2].startswith('Length'): if section.startswith('1.0') or section.startswith('3.2'): rowvalue=9# column 7 ar=0 unit='m' if McTName[i][2].startswith('Count'): if section.startswith('1.0') or section.startswith('3.2'): rowvalue=3# column 7 ar=0 unit='Nr' print('mcct',McTName[i][1]) if isinstance(McTName[i][1], list): guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) for m in McTName[i][1]: if m: if m.startswith('text1'): name=m.removeprefix('text1') allgbnames+= name +' +' indices = [o for o, x in enumerate(guessednames) if x == name] print(indices) for j in range(len(indices)): # print('kjjjj',roww[j]) ar+=float(worksheetw.cell((indices[j]+1 ,rowvalue)).value) else: item+=m + ' ,' print(item) n= McTName[i][0] + ' ( '+ allgbnames[:-2] +' , ' + item[:-1] + ' ) ' else: if McTName[i][1].startswith('text1'): name=McTName[i][1].removeprefix('text1') allgbnames+= name roww=worksheetw.find(name) print(roww) for j in range(len(roww)): print('kjjjj',roww[j]) ar+=float(worksheetw.cell((roww[j].row ,rowvalue)).value) n= McTName[i][0] + ' ( '+ allgbnames + ' ) ' rowi=[str(n),ar,unit] worksheetS.update_row(i+2,rowi) # worksheetS.adjust_column_width(start=1,end=4) worksheetS.adjust_column_width(start=1,end=1, pixel_size=350) worksheetS.adjust_column_width(start=2,end=2, pixel_size=100) worksheetS.adjust_column_width(start=3,end=3) xx=(worksheetS.cell( ( len(McTName) +1 ,3)) ).address.label model_cell1 =worksheetS.cell('A2') model_cell1.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.LEFT ) pygsheets.DataRange('A2', str(xx), worksheet=worksheetS).apply_format(model_cell1) model_cell =worksheetS.cell('A1') model_cell.set_text_format('bold', True) model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER ) pygsheets.DataRange('A1','C1', worksheet=worksheetS).apply_format(model_cell) body2={ "requests": [ { "updateBorders": { "range": { "sheetId": str(summaryId), "startRowIndex": 0, "endRowIndex": len(McTName) +1 , "startColumnIndex": 0, "endColumnIndex": 3 }, "top": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green":0.0, "blue":0.0 }, }, "bottom": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green":0.0, "blue":0.0 }, }, "left":{ "style": "SOLID", "width":2, "color": { "red": 0.0, "green":0.0, "blue":0.0 }, }, "right":{ "style": "SOLID", "width": 2, "color": { "red": 0.0, "green":0.0, "blue":0.0 }, }, "innerHorizontal":{ "style": "SOLID", "width":2, "color": { "red": 0.0, "green":0.0, "blue":0.0 }, }, "innerVertical": { "style": "SOLID", "width": 2, "color": { "red": 0.0, "green":0.0, "blue":0.0 }, }, } } ] } spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_key, body=body2).execute() return summaryId #,guessednamesfinal # print(x,xarea) def getguessnames(gc,ws): guessednamesfinal=[] worksheetw = ws.worksheet(0) guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) print(guessednames[2:]) for item in guessednames[2:]: if item not in guessednamesfinal: guessednamesfinal.append(item) print(guessednamesfinal) return guessednamesfinal ################################################################ def deletefromlegend(deletedrows,SimilarAreaDictionarycopy,section, areaPermArr=[]): items=[] print('dletefromlegend') idx=0 if section.startswith('1.0'): areaPermArr=ast.literal_eval(areaPermArr) myDict=eval(SimilarAreaDictionarycopy) SimilarAreaDictionarycopy=pd.DataFrame(myDict) # deletedrows=eval(deletedrows) strings=deletedrows['content'] areastodelete = [] perimstodelete=[] lengthstodelete=[] for item in strings: newitem=str(item).split('\n \n') input_str = " ".join(str(newitem).split()) # Search for the Area value matchA = re.search(r"Area=(\d+\.\d+)", input_str) matchL = re.search(r"Length=(\d+\.\d+)", input_str) matchP = re.search(r"Perimeter=(\d+\.\d+)", input_str) if matchA: areastodelete.append(float(matchA.group(1))) if matchP: perimstodelete.append(float(matchP.group(1))) if matchL: lengthstodelete.append(float(matchL.group(1))) print('Areas to delete:', areastodelete) print('Perimeters to delete:', perimstodelete) print('Lengths to delete:', lengthstodelete) for i in range(len(areastodelete)):#item in areastodelete: if section.startswith('1.0'): tol=0.3 elif section.startswith('3.2'): tol=1 areamin=round(areastodelete[i],1)- tol areamax=round(areastodelete[i],1)+ tol if section.startswith('1.0'): for p in range(len(areaPermArr)): if areastodelete[i] in areaPermArr[p]: width= areaPermArr[p][1] height= areaPermArr[p][2] break widthMin= width -10 widthMax= width +10 heightMin = height-10 heightMax=height+10 if len(areastodelete)>0: found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]] elif section.startswith('3.2'): found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )]] if len(found.index.values) >0: occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences'] if occ== 1: #drop row SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0]) else: #occ minus 1 , total area - areavalue , total perim - perimvalue print('occ>1') if section.startswith('1.0'): if len(areastodelete)>0: idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ] elif section.startswith('3.2'): idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )] if len(areastodelete)>0: comment = SimilarAreaDictionarycopy.loc[idx, 'Comments'] if pd.notna(comment.iloc[0]) and 'Area' in str(comment.iloc[0]): matches = re.findall(r'\b\d+\b', str(SimilarAreaDictionarycopy.loc[idx, 'Comments'])) area_occurrences = int(matches[1]) -1 perimeter_occurrences = int(matches[2]) print(area_occurrences, perimeter_occurrences) SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' if area_occurrences > perimeter_occurrences: SimilarAreaDictionarycopy.loc[idx,'Occurences'] = area_occurrences elif perimeter_occurrences> area_occurrences: SimilarAreaDictionarycopy.loc[idx,'Occurences'] = perimeter_occurrences elif int(area_occurrences)==int(perimeter_occurrences): SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1 if section.startswith('1.0'): SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i] else: print('not yet') area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1 perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] print(area_occurrences,perimeter_occurrences) SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' SimilarAreaDictionarycopy.loc[idx,'Total Area'] = SimilarAreaDictionarycopy.loc[idx,'Total Area'] - areastodelete[i] for i in range(len(perimstodelete)):#item in areastodelete: if section.startswith('1.0'): tol=0.3 elif section.startswith('3.2'): tol=10 if len(perimstodelete)>0: print(perimstodelete[i]) perimmin=round(perimstodelete[i],1)- 0.3 perimmax=round(perimstodelete[i],1)+ 0.3 print('perimmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm') if section.startswith('1.0'): for p in range(len(areaPermArr)): if areastodelete[i] in areaPermArr[p]: print('AAA',areaPermArr[p]) area= areaPermArr[p][0] width= areaPermArr[p][1] height= areaPermArr[p][2] break widthMin= width -10 widthMax= width +10 heightMin = height-10 heightMax=height+10 found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[( (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]] elif section.startswith('3.2'): found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[( (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) )]] if len(found.index.values) >0: occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences'] if occ== 1: #drop row print('occ=1') print(found) SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0]) else: #occ minus 1 , total area - areavalue , total perim - perimvalue print('occ>1') if section.startswith('1.0'): if len(perimstodelete)>0: idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ] elif section.startswith('3.2'): if len(perimstodelete)>0: perimmin=round(perimstodelete[i],1)- 1 perimmax=round(perimstodelete[i],1)+ 1 idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) )] if len(perimstodelete)>0: comment = SimilarAreaDictionarycopy.loc[idx, 'Comments'] if pd.notna(comment.iloc[0]) and 'Area' in str(comment.iloc[0]): matches = re.findall(r'\b\d+\b', str(SimilarAreaDictionarycopy.loc[idx, 'Comments'])) area_occurrences = int(matches[1]) perimeter_occurrences = int(matches[2])-1 print(area_occurrences, perimeter_occurrences) SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' if area_occurrences > perimeter_occurrences: SimilarAreaDictionarycopy.loc[idx,'Occurences'] = area_occurrences elif perimeter_occurrences> area_occurrences: SimilarAreaDictionarycopy.loc[idx,'Occurences'] = perimeter_occurrences elif int(area_occurrences)==int(perimeter_occurrences): SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1 if section.startswith('1.0'): SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i] else: print('not yet') area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1 SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1 perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] = SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] - perimstodelete[i] return SimilarAreaDictionarycopy ###################### ###################### # # -*- coding: utf-8 -*- # """Google Sheet to XML.ipynb # Automatically generated by Colaboratory. # Original file is located at # https://colab.research.google.com/drive/1T-b1N8Gq6wwbBurODzJIdhRQNeRbyBnz # """ # from googleapiclient.discovery import build # from google.oauth2 import service_account # import pygsheets # import ast # import re # import pandas as pd # def authorizeLegend(): # SCOPES = [ # 'https://www.googleapis.com/auth/spreadsheets', # 'https://www.googleapis.com/auth/drive', # 'https://www.googleapis.com/auth/drive.metadata' # ] # credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES) # spreadsheet_service = build('sheets', 'v4', credentials=credentials) # drive_service = build('drive', 'v3', credentials=credentials) # gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json') # return spreadsheet_service,drive_service,gc # def legendGoogleSheets(SimilarAreaDictionary,path ,pdfpath, spreadsheetId=0): # spreadsheet_service,drive_service,gc=authorizeLegend() # ######## # legendTitle= path # titles=gc.spreadsheet_titles() # if legendTitle in titles: # print('found sheet ', legendTitle) # ws=gc.open(str(legendTitle)) # spreadsheetId=ws.id # else: # # ####### create new sheet # print('creating new sheeet') # spreadsheet_details = { # 'properties': { # 'title': path # } # } # sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, # fields='spreadsheetId').execute() # spreadsheetId = sheet.get('spreadsheetId') # permission1 = { # 'type': 'anyone', # 'role': 'writer', # # 'emailAddress': 'marthe.adr@gmail.com' # } # # permission2 = { # # 'type': 'user', # # 'role': 'writer', # # 'emailAddress': 'marthe.adr@gmail.com', # # 'pendingOwner': True # # } # drive_service.permissions().create(fileId=spreadsheetId, body=permission1, supportsAllDrives=True ).execute() # ws=gc.open_by_key(spreadsheetId) # sheetId = '0' # Please set sheet ID. # worksheet = ws.worksheet(0) # worksheet.title='Legend and data created' # worksheet.clear() # print('PDFPATHHH',pdfpath) # ws.create_developer_metadata('path',pdfpath) # splittedpdfpath=re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,/<>?]', pdfpath) # namepathArr=[legendTitle , spreadsheetId,ws.get_developer_metadata('path')[0].value] # if splittedpdfpath[-2].startswith('2.2') or splittedpdfpath[-2].startswith('2.1') : # worksheet.set_dataframe(start='A1',df=SimilarAreaDictionary) # print(SimilarAreaDictionary) # else: # top_header_format = [ # {'mergeCells': { #areas # 'mergeType': 'MERGE_ROWS', # 'range': { # 'sheetId': '0', # 'startRowIndex': 1, # 'endRowIndex': 2, # 'startColumnIndex': 3, # 'endColumnIndex':5 # } # }}, # {'mergeCells': { #perimeters # 'mergeType': 'MERGE_ROWS', # 'range': { # 'sheetId': '0', # 'startRowIndex': 1, # 'endRowIndex': 2, # 'startColumnIndex': 5, # 'endColumnIndex':7 # } # }}, # {'mergeCells': { #lengths # 'mergeType': 'MERGE_ROWS', # 'range': { # 'sheetId': '0', # 'startRowIndex': 1, # 'endRowIndex': 2, # 'startColumnIndex': 7, # 'endColumnIndex':9 # } # }}, # {'mergeCells': { # legend and data created # 'mergeType': 'MERGE_ROWS', # 'range': { # 'sheetId': '0', # 'startRowIndex': 0, # 'endRowIndex': 1, # 'startColumnIndex': 0, # 'endColumnIndex':10 # } # }} # ] # spreadsheet_service.spreadsheets().batchUpdate( spreadsheetId=spreadsheetId , body={'requests': top_header_format} ).execute() # worksheet.cell((1,1)).value='Legend and Data Created' # worksheet.cell((2,1)).value='Guess' # worksheet.cell((2,2)).value='Color' # worksheet.cell((2,3)).value='Count' # worksheet.cell((2,4)).value='Areas' # worksheet.cell((2,6)).value='Perimeters' # worksheet.cell((2,8)).value='Lengths' # worksheet.cell((2,10)).value='Texts' # second_row_data=['Nr','m2','Total','m','Total','m','Total'] # if splittedpdfpath[-2].startswith('1.0') or splittedpdfpath[-2].startswith('3.2'): # worksheet.update_row(3,second_row_data,col_offset=2) # worksheet.update_col(1,list(SimilarAreaDictionary['Guess']),row_offset=3) # worksheet.update_col(3,list(SimilarAreaDictionary['Occurences']),row_offset=3) # worksheet.update_col(4,list(SimilarAreaDictionary['Area']),row_offset=3) # worksheet.update_col(5,list(SimilarAreaDictionary['Total Area']),row_offset=3) # worksheet.update_col(6,list(SimilarAreaDictionary['Perimeter']),row_offset=3) # worksheet.update_col(7,list(SimilarAreaDictionary['Total Perimeter']),row_offset=3) # worksheet.update_col(8,list(SimilarAreaDictionary['Length']),row_offset=3) # worksheet.update_col(9,list(SimilarAreaDictionary['Total Length']),row_offset=3) # worksheet.update_col(10,list(SimilarAreaDictionary['Texts']),row_offset=3) # if splittedpdfpath[-2].startswith('1.0'): # colorsUsed=[] # for i in range(len(SimilarAreaDictionary)): # colorsUsed.append([SimilarAreaDictionary['R'].iloc[i] ,SimilarAreaDictionary['G'].iloc[i] , SimilarAreaDictionary['B'].iloc[i]] ) # elif splittedpdfpath[-2].startswith('3.2'): # colorsUsed=list(SimilarAreaDictionary['Color']) # #legend specs here # rowsLen=len(SimilarAreaDictionary.values.tolist()) #kam row -- last row = rowsLen +1 # lastcell=worksheet.cell((rowsLen+2,1)) #row,col # lastcellNotation=str(lastcell.address.label) # # worksheet.set_data_validation('A3',lastcellNotation, condition_type='ONE_OF_LIST', condition_values=['Ground Beam','Pile Cap'], showCustomUi=True) # #get lengths of df # columnsLen=len(SimilarAreaDictionary.columns.values.tolist()) #kam column -- last col = columnsLen+1 3shan base0 # lastUsedCol=columnsLen+1 # worksheet.adjust_column_width(start=2,end=3) # worksheet.adjust_column_width(start=10,end=10) # # if splittedpdfpath[-2].startswith('1.0'): # worksheet.adjust_column_width(start=4,end=9,pixel_size=60) # startrow = 3 # # elif splittedpdfpath[-2].startswith('3.2'): # # startrow=2 # sheetId = '0' # Please set sheet ID. # for i in range(len(colorsUsed)): # print(colorsUsed[i]) # r,g,b=colorsUsed[i] # body = { # "requests": [ # { # "updateCells": { # "range": { # "sheetId": sheetId, # "startRowIndex": i+startrow, # # "endRowIndex":4 , # "startColumnIndex":1, # # "endColumnIndex": 0 # }, # "rows": [ # { # "values": [ # { # "userEnteredFormat": { # "backgroundColor": { # "red": r/255, # "green": g/255, # "blue": b/255, # "alpha": 0.4, # } # } # } # ] # } # ], # "fields": "userEnteredFormat.backgroundColor", # } # } # ] # } # res = spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body).execute() # # if splittedpdfpath[-2].startswith('1.0'): # endColindex=10 # endrow=3 # body2={ # "requests": [ # { # "updateBorders": { # "range": { # "sheetId": sheetId, # "startRowIndex": 0, # "endRowIndex": len(SimilarAreaDictionary)+endrow, # "startColumnIndex": 0, # "endColumnIndex": endColindex # }, # "top": { # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "bottom": { # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "left":{ # "style": "SOLID", # "width":2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "right":{ # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "innerHorizontal":{ # "style": "SOLID", # "width":2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "innerVertical": { # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # } # } # ] # } # spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body2).execute() # model_cell =worksheet.cell('A1') # model_cell.set_text_format('bold', True) # model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER ) # model_cell.color = (213/255, 219/255 ,255/255) # pygsheets.DataRange('A2','J2', worksheet=worksheet).apply_format(model_cell) # spreadsheet_url = "https://docs.google.com/spreadsheets/d/%s" % spreadsheetId # print(spreadsheet_url) # drive_service.permissions().update(transferOwnership=True , fileId=spreadsheetId,permissionId='11OfoB4Z6wOVII8mYmbnCbbqTQs7rYA65') # return gc,spreadsheet_service,spreadsheetId ,spreadsheet_url , namepathArr # ####################### # def mapnametoLegend(McTName): # sectionKey = McTName.pop() # key=sectionKey[0] # section=sectionKey[1] # spreadsheet_service,drive_service,gc=authorizeLegend() # spreadsheet_key =str(key) # Please set the Spreadsheet ID. # ws = gc.open_by_key(spreadsheet_key) # # guessednamesfinal=getguessnames(gc,ws) # sheetnames=[] # unit='' # # ws.add_worksheet("Summary") # Please set the new sheet name. # for i in ws._sheet_list: # print(i) # sheetnames.append(i.title) # print(i.index) # if 'XML Export Summary' in sheetnames: # worksheetS = ws.worksheet_by_title('XML Export Summary') # else: # ws.add_worksheet("XML Export Summary") # Please set the new sheet name. # worksheetw = ws.worksheet(0) #legend # worksheetS = ws.worksheet_by_title('XML Export Summary') # summaryId= ws[1].id # print('summaryyyID',summaryId) # print('summaryyyID2',worksheetS.id) # worksheetS.clear() # countnames=0 # row0=['MC_T Name','Qty','Unit'] # worksheetS.update_row(1,row0) # for i in range(len(McTName)): # allgbnames='' # item='' # print(McTName[i][0]) # # firstpart= re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,./<>?]', McTName[i][0]) # print('kkk' ,McTName[i][2]) # if McTName[i][2].startswith('Area'): # if section.startswith('1.0'): # rowvalue=5# column 5 # elif section.startswith('3.2'): # rowvalue=3 # ar=0 # unit='m2' # if McTName[i][2].startswith('Perimeter'): # if section.startswith('1.0'): # rowvalue=7# column 7 # elif section.startswith('3.2'): # rowvalue=3 # ar=0 # unit='m' # if McTName[i][2].startswith('Length'): # if section.startswith('1.0'): # rowvalue=9# column 7 # elif section.startswith('3.2'): # rowvalue=3 # ar=0 # unit='m' # if McTName[i][2].startswith('Count'): # if section.startswith('1.0'): # rowvalue=3# column 7 # elif section.startswith('3.2'): # rowvalue=3 # ar=0 # unit='Nr' # print('mcct',McTName[i][1]) # if isinstance(McTName[i][1], list): # guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) # for m in McTName[i][1]: # if m: # if m.startswith('text1'): # name=m.removeprefix('text1') # allgbnames+= name +' +' # indices = [o for o, x in enumerate(guessednames) if x == name] # print(indices) # for j in range(len(indices)): # # print('kjjjj',roww[j]) # ar+=float(worksheetw.cell((indices[j]+1 ,rowvalue)).value) # else: # item+=m + ' ,' # print(item) # n= McTName[i][0] + ' ( '+ allgbnames[:-2] +' , ' + item[:-1] + ' ) ' # else: # if McTName[i][1].startswith('text1'): # name=McTName[i][1].removeprefix('text1') # allgbnames+= name # roww=worksheetw.find(name) # print(roww) # for j in range(len(roww)): # print('kjjjj',roww[j]) # ar+=float(worksheetw.cell((roww[j].row ,rowvalue)).value) # n= McTName[i][0] + ' ( '+ allgbnames + ' ) ' # rowi=[str(n),ar,unit] # worksheetS.update_row(i+2,rowi) # # worksheetS.adjust_column_width(start=1,end=4) # worksheetS.adjust_column_width(start=1,end=1, pixel_size=350) # worksheetS.adjust_column_width(start=2,end=2, pixel_size=100) # worksheetS.adjust_column_width(start=3,end=3) # xx=(worksheetS.cell( ( len(McTName) +1 ,3)) ).address.label # model_cell1 =worksheetS.cell('A2') # model_cell1.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.LEFT ) # pygsheets.DataRange('A2', str(xx), worksheet=worksheetS).apply_format(model_cell1) # model_cell =worksheetS.cell('A1') # model_cell.set_text_format('bold', True) # model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER ) # pygsheets.DataRange('A1','C1', worksheet=worksheetS).apply_format(model_cell) # body2={ # "requests": [ # { # "updateBorders": { # "range": { # "sheetId": str(summaryId), # "startRowIndex": 0, # "endRowIndex": len(McTName) +1 , # "startColumnIndex": 0, # "endColumnIndex": 3 # }, # "top": { # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "bottom": { # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "left":{ # "style": "SOLID", # "width":2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "right":{ # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "innerHorizontal":{ # "style": "SOLID", # "width":2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # "innerVertical": { # "style": "SOLID", # "width": 2, # "color": { # "red": 0.0, # "green":0.0, # "blue":0.0 # }, # }, # } # } # ] # } # spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_key, body=body2).execute() # return summaryId #,guessednamesfinal # # print(x,xarea) # def getguessnames(gc,ws): # guessednamesfinal=[] # worksheetw = ws.worksheet(0) # guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) # print(guessednames[2:]) # for item in guessednames[2:]: # if item not in guessednamesfinal: # guessednamesfinal.append(item) # print(guessednamesfinal) # return guessednamesfinal # ################################################################ # def deletefromlegend(deletedrows,SimilarAreaDictionarycopy,section, areaPermArr=[]): # items=[] # idx=0 # if section.startswith('1.0'): # areaPermArr=ast.literal_eval(areaPermArr) # myDict=eval(SimilarAreaDictionarycopy) # SimilarAreaDictionarycopy=pd.DataFrame(myDict) # # deletedrows=eval(deletedrows) # strings=deletedrows['content'] # areastodelete = [] # perimstodelete=[] # lengthstodelete=[] # for item in strings: # items.append(str(item).split('\n \n')) # # print('itemsssssss',float(re.findall("\d+\.\d+", str(items[i][0]).split()[0])[0])) #take area and perim mn hna l sec 3.2 and +/- value margin # for i in range(len(items)): # print('ITEMSS',str(items[i]).split()) # items=ast.literal_eval(str(items[i])) # areastodelete.append(float(re.findall("\d+\.\d+", str(items[i][0]).split()[1])[0])) # perimstodelete.append(float(re.findall("\d+\.\d+", str(items[i][1]).split()[1])[0]) ) # lengthstodelete.append(float(re.findall("\d+\.\d+", str(items[i][2]).split()[1])[0]) ) # for i in range(len(areastodelete)):#item in areastodelete: # areamin=round(areastodelete[i],1)- 0.3 # areamax=round(areastodelete[i],1)+ 0.3 # perimmin=round(perimstodelete[i],1)- 0.3 # perimmax=round(perimstodelete[i],1)+ 0.3 # if section.startswith('1.0'): # for p in range(len(areaPermArr)): # if areastodelete[i] in areaPermArr[p]: # print('AAA',areaPermArr[p]) # area= areaPermArr[p][0] # width= areaPermArr[p][1] # height= areaPermArr[p][2] # # if section.startswith('1.0'): # widthMin= width -10 # widthMax= width +10 # heightMin = height-10 # heightMax=height+10 # found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) & (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]] # elif section.startswith('3.2'): # areamin=round(areastodelete[i],1)- 0.1 # areamax= round(areastodelete[i],1) + 0.1 # found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )]] # if len(found.index.values ) >0: # occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences'] # if occ== 1: #drop row # print('occ=1') # SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0]) # else: #occ minus 1 , total area - areavalue , total perim - perimvalue # print('occ>1') # if section.startswith('1.0'): # idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) & (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ] # elif section.startswith('3.2'): # perimmin=round(perimstodelete[i],1)- 50 # perimmax=round(perimstodelete[i],1)+ 50 # idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) & (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) )] # SimilarAreaDictionarycopy.loc[idx,'Total Area'] = SimilarAreaDictionarycopy.loc[idx,'Total Area'] - areastodelete[i] # SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] = SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] - perimstodelete[i] # SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i] # SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1 # return SimilarAreaDictionarycopy # #############################################################