Spaces:
Sleeping
Sleeping
| # -*- 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 xml.etree.ElementTree as ET | |
| import tsadropboxretrieval | |
| 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 getTimes(drive_service,spreadsheetId): | |
| summaryTimes=drive_service.files().get(fileId=spreadsheetId,fields="createdTime, modifiedTime").execute() | |
| createdTime=summaryTimes.get('createdTime') | |
| modifiedTime=summaryTimes.get('modifiedTime') | |
| return createdTime,modifiedTime | |
| def openSpreadsheet_DFs(legendTitle): | |
| spreadsheet_service,drive_service,gc=authorizeLegend() | |
| # legendTitle= '2123-HRW-01-BG-DR-S-110_P.03.pdf' | |
| print('found sheet ', legendTitle) | |
| ws=gc.open(str(legendTitle)) | |
| spreadsheetId=ws.id | |
| worksheet0 = ws.worksheet(0) | |
| worksheet1 = ws.worksheet_by_title('XML Export Summary') | |
| df0=worksheet0.get_as_df() | |
| df1=worksheet1.get_as_df() | |
| createdTime,modifiedTime=getTimes(drive_service,spreadsheetId) | |
| return df0,df1,createdTime,modifiedTime | |
| def create_xml(documentname , dbPath): | |
| df0,df1,createdTime,modifiedTime=openSpreadsheet_DFs(documentname) | |
| # createdTime,modifiedTime=getTimes() | |
| MarkupSummary=ET.Element('<MarkupSummary Version="1.0" Document="'+documentname+'">') | |
| # we make root element | |
| MarkupSummary = ET.SubElement(MarkupSummary, "MarkupSummary") | |
| MarkupSummary.attrib['Version'] = "1.0" | |
| MarkupSummary.attrib['Document'] = documentname | |
| # insert list element into sub elements | |
| for rowX,rowY in df1.iterrows(): | |
| print(rowX,rowY) | |
| Markup = ET.Element("Markup") | |
| Markup = ET.SubElement(MarkupSummary, "Markup") | |
| Subject = ET.SubElement(Markup, "Subject") | |
| Subject.text=str(rowY[0]) | |
| Date = ET.SubElement(Markup, "Date") | |
| Date.text=str(modifiedTime.split('T')[0].replace('-','/')+ ' '+ modifiedTime.split('T')[1].split('.')[0]) | |
| Colour = ET.SubElement(Markup, "Colour") | |
| Colour.text=str(' ') | |
| Count = ET.SubElement(Markup, "Count") | |
| Count.text=str(' ') | |
| Author = ET.SubElement(Markup, "Author") | |
| Author.text=str('ADR Team') | |
| if rowY[2]=='m': | |
| Length = ET.SubElement(Markup, "Length") | |
| Length.text=str(rowY[1]) | |
| else: | |
| Length = ET.SubElement(Markup, "Length") | |
| Length.text=str(0) | |
| Height = ET.SubElement(Markup, "Height") | |
| Height.text=str(0) | |
| if rowY[2]=='m2': | |
| Area = ET.SubElement(Markup, "Area") | |
| print('rowY',rowY) | |
| Area.text=str(rowY[1]) | |
| else: | |
| Area = ET.SubElement(Markup, "Area") | |
| Area.text=str(0) | |
| Label = ET.SubElement(Markup, "Label") | |
| Label.text=str(rowY[0]) | |
| Page_Label = ET.SubElement(Markup, "Page_Label") | |
| Page_Label.text=str(' ') | |
| Page_Index = ET.SubElement(Markup, "Page_Index") | |
| Page_Index.text=str(' ') | |
| Lock = ET.SubElement(Markup, "Lock") | |
| Lock.text=str('Unlocked') | |
| Tick_mark = ET.SubElement(Markup, "Tick_mark") | |
| Tick_mark.text=str(' ') | |
| Creation_Date = ET.SubElement(Markup, "Creation_Date") | |
| Creation_Date.text=str(createdTime.split('T')[0].replace('-','/')) | |
| x = ET.SubElement(Markup, "X") | |
| x.text=str(' ') | |
| y= ET.SubElement(Markup, "Y") | |
| y.text=str(' ') | |
| Document_Width= ET.SubElement(Markup, "Document_Width") | |
| Document_Width.text=str(' ') | |
| Document_Height= ET.SubElement(Markup, "Document_Height") | |
| Document_Height.text=str(' ') | |
| RiseDrop= ET.SubElement(Markup, "RiseDrop") | |
| RiseDrop.text=str(0) | |
| Unit= ET.SubElement(Markup, "Unit") | |
| Unit.text=str(rowY[2]) | |
| Wall_Area= ET.SubElement(Markup, "Wall_Area") | |
| Wall_Area.text=str(' ') | |
| Depth= ET.SubElement(Markup, "Depth") | |
| Depth.text=str(0.00) | |
| Volume= ET.SubElement(Markup, "Volume") | |
| Volume.text=str(0) | |
| Measurement= ET.SubElement(Markup, "Measurement") | |
| Measurement.text=str(rowY[1]) | |
| Layer= ET.SubElement(Markup, "Layer") | |
| Layer.text=str(' ') | |
| Capture= ET.SubElement(Markup, "Capture") | |
| Capture.text=str('No') | |
| File_Name= ET.SubElement(Markup, "File_Name") | |
| File_Name.text=str(documentname) | |
| tree = ET.ElementTree(Markup) | |
| tree = ET.ElementTree(MarkupSummary) | |
| # write the tree into an XML file | |
| # tree.write(str(documentname).split('.pdf')[0]+".xml", encoding ='UTF-8', xml_declaration = True ,method='xml') | |
| # dbPath='/TSA JOBS/ADR Test'+pdfpath+'XML/' | |
| # (pdfname,path) | |
| # path=path+pdfname | |
| xmllink= tsadropboxretrieval.uploadanyFile(doc=tree,pdfname=documentname,path=dbPath,flag='xml') | |
| return xmllink | |
| # create_xml(legendTitle) | |