Spaces:
Sleeping
Sleeping
File size: 5,332 Bytes
d9899b7 3259624 d9899b7 3259624 d9899b7 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 |
# -*- 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)
|