-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSparesOEMWriter.py
133 lines (106 loc) · 6.43 KB
/
SparesOEMWriter.py
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
import os, PyPDF2, openpyxl, shutil
from pathlib import Path
# Need to figure out how to navigate to OEM directory to do the keyword writing.
def OEMToSpareParts(sparesFile):
wb = openpyxl.load_workbook(sparesFile)
print ('Sheetnames ', wb.sheetnames)
sparesSheet = wb['spare parts']
maintSheet = wb['maintenance']
print ('Spares sheet Cell A1 ', sparesSheet['A1'].value)
for sparesRow in range(1, sparesSheet.max_row + 1):
sparesCell = sparesSheet.cell(row=sparesRow, column=2)
for maintRow in range(1, maintSheet.max_row +1):
maintCell = maintSheet.cell(row=maintRow, column=3)
sparesNumber = sparesSheet.cell(row=sparesRow, column=1).value
#print('sparesNumber ', sparesNumber)
print(sparesCell.value, ' ', maintCell.value)
if maintCell.value is not None:
if (maintCell.value in sparesCell.value) and (sparesNumber.startswith('20')):
manufacturer = maintSheet.cell(maintRow, 2)
OEM = maintSheet.cell(maintRow, 4)
print('Match OEM ', OEM.value)
sparesSheet.cell(row=sparesRow, column=6).value = manufacturer.value
sparesSheet.cell(row=sparesRow, column=7).value = OEM.value
# write OEM into the row on maintSheet
#write out to new file
outFile = '_'.join(['Out', sparesFile])
wb.save(outFile)
#OEMToSpareParts('DrySF_31621699.xlsx')
def sparesFileWalk(projectDir):
for folderName, subfolders, filenames in os.walk(projectDir):
print('Current folder ', folderName)
for filename in filenames:
#print('File inside ', folderName, ': ', filename)
#Check older file suffix is correct
if (Path(filename).suffix) == ('.xlsx' or '.xsl'):
print('XL file ',Path(filename).suffix)
wb = openpyxl.load_workbook(filename)
print(wb.sheetnames)
if ('spare parts' in wb.sheetnames and 'maintenance' in wb.sheetnames):
print('Hello Spare Parts List')
OEMToSpareParts(filename)
# Next - Identify spares files
# sparesFileWalk('.\\')
# ToDO - currently writes E2932 as keyword everytime and saving Out files in directory this is
# run from. Needs to save back in file it came from.
# Appends keyword to current list of keywords in pdfFile and writes the file
# to Out_pdffile
def writeKeyword(pdfFile, keyword):
pdfFileObj = open(pdfFile, 'rb')
pdfReader = PyPDF2.PdfFileReader(pdfFileObj)
pdfWriter = PyPDF2.PdfFileWriter()
pdfInfo = pdfReader.getDocumentInfo()
# print('PDFInfo ', str(pdfInfo))
if '/Keywords' in pdfInfo:
keywordIn = pdfInfo['/Keywords']
# print('keywordIn ', keywordIn)
# print ('Keyword ', keyword)
keyword = ', '.join([keywordIn, keyword])
# print ('Keyword ', keyword)
for pageNum in range(pdfReader.numPages):
pageObj = pdfReader.getPage(pageNum)
pdfWriter.addPage(pageObj)
pdfWriter.addMetadata({'/Author': 'madeline', '/Keywords': keyword})
pdfFileBasename = os.path.basename(pdfFile)
pdfOutfile = '_'.join(['Out', pdfFileBasename])
if (os.path.exists(pdfOutfile)):
os.remove(pdfOutfile)
pdfOutputFile = open(pdfOutfile, 'wb')
pdfWriter.write(pdfOutputFile)
pdfOutputFile.close()
pdfFileObj.close()
#writeKeyword('test.pdf', '7Mar')
# Loops through spare parts worksheetand calls writeKeyword for each spare part with an
# OEM file listed and writes the 20,000,000 and Manufacturer to the Keywords metadata
def sparesToPDFKeyword(sparesFile):
wb = openpyxl.load_workbook(sparesFile)
ws = wb['spare parts']
oemFiles = []
for rowNum in range(1, ws.max_row + 1):
partMan = ws.cell(row=rowNum, column=6).value
oem = ws.cell(row=rowNum, column= 7).value
partNum = ws.cell(row=rowNum, column= 1).value
if (oem is not None) and not ('www.' in oem):
#create OEM list
# if len(oem) > 5:
# print('OEM length > 5')
oem = oem.replace(" ", "")
oemList = oem.split(',')
print("oemList ",oemList)
for oemFile in oemList:
#print('Find oem file ', oemFile)
for folderName, subfolders, filenames in os.walk('C:\\Users\\Mick\\Documents\\OEM'):
for filename in filenames:
# When OEM matches filename, call writeKeyword
# Need to split strings with a comma and loop
oemFilePath = os.path.join(folderName, filename)
# print("oemFile ", oemFile)
if oemFile in filename:
print("Match oemFile filename", oemFile, filename)
print('Path and Filename ', oemFilePath)
# overwrites when call twice. Look at write keyword
writeKeyword(oemFilePath, partNum)
# writeKeyword(oemFilePath, partMan)
# if not none. Test for OEM file. Then filewalk in OEM directory. Change to right place.
# Call writeKeyword
sparesToPDFKeyword('TestFiles/Out_DrySF_31621699.xlsx')