# -*- 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 #############################################################