| 1 |
"""
|
| 2 |
|
| 3 |
EvidoxJSParser
|
| 4 |
|
| 5 |
Created by
|
| 6 |
Emanuel Borges
|
| 7 |
03.20.2018
|
| 8 |
|
| 9 |
A program or just set of methods that will parse and create a new report from the JS.
|
| 10 |
|
| 11 |
|
| 12 |
"""
|
| 13 |
|
| 14 |
from win32com.client import Dispatch
|
| 15 |
|
| 16 |
def GatherColumnTableEnd(sheet, rowStart, colStart):
|
| 17 |
"""Takes a JS sheet and returns the start and end range of what has data in it."""
|
| 18 |
right = colStart
|
| 19 |
while sheet.Cells(rowStart, right + 1).Value not in [None,""]:
|
| 20 |
right = right +1
|
| 21 |
|
| 22 |
colEnd = right
|
| 23 |
|
| 24 |
return colStart, colEnd
|
| 25 |
|
| 26 |
def GatherRowTableEnd(sheet, rowStart, col):
|
| 27 |
"""using only the col field, will test all rows, starting at rowstart and returns the end row"""
|
| 28 |
bottom = rowStart
|
| 29 |
while sheet.Cells(bottom + 1, col).Value not in [None, '']:
|
| 30 |
bottom = bottom +1
|
| 31 |
|
| 32 |
rowEnd = bottom
|
| 33 |
return rowEnd
|
| 34 |
|
| 35 |
|
| 36 |
if __name__ == '__main__':
|
| 37 |
xlApp = Dispatch('Excel.Application')
|
| 38 |
## Turn off any messages about saving over existing target files.
|
| 39 |
xlApp.DisplayAlerts = False
|
| 40 |
|
| 41 |
## matrix of casename, (sourceJS, targetJS)
|
| 42 |
#reportsToCreate = {'Bielins - Bennardo':
|
| 43 |
# (r"C:\Test-PY\liveTest\Bielins-Bennardo-Job Summary.xlsx",r"C:\Test-PY\liveTest\Bielins - BennardoTESTJSLiveReport.xlsx"),
|
| 44 |
# 'SharkNinja - Keurig':
|
| 45 |
# (r"C:\Test-PY\liveTest\SharkNinja-Keurig-Job Summary.xlsx",r"C:\Test-PY\liveTest\SharkNinja - KeurigTESTJSLiveReport.xlsx")}
|
| 46 |
|
| 47 |
reportsToCreate = {'I Grace Productions':
|
| 48 |
(r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\I Grace Productions-JS-LiveReport.xlsx"),
|
| 49 |
'McLaughlin Northrup - McDonald Fracassa':
|
| 50 |
(r"\\Bluearc01\jobs\Murphy King\McLaughlin Northup-McDonald Fracassa\McLaughlin Northrup-McDonald Fracassa-Job Summary.xlsx",r"L:\__People\Emanuel\MCP3_Temp\McLaughlin Northrup - McDonald Fracassa-JS-LiveReport.xlsx")}
|
| 51 |
|
| 52 |
for reportCaseItem in reportsToCreate.keys():
|
| 53 |
caseName = reportCaseItem
|
| 54 |
sourceSpreadsheetPath,targetSpreadsheet = reportsToCreate[reportCaseItem]
|
| 55 |
|
| 56 |
print "Now processing %s..."% caseName
|
| 57 |
xlBook = xlApp.Workbooks.Open(sourceSpreadsheetPath)
|
| 58 |
#xlBook = xlApp.Workbooks.Open(r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx")
|
| 59 |
#targetSpreadsheet = r"C:\Test-PY\liveTest\OutputJSReport.xlsx"
|
| 60 |
sht = xlBook.Worksheets(1)
|
| 61 |
acceptedFieldsList = [u'Evidox Job Number', u'Evidox Media ID', u'Media Type', u'Disk Label - Folder / File name', u'Total File Count (# of Files Received)', u'Extracted Filesize (GB)', u'Serial Number', u'Notes', u'Custodian(s)\nREQUIRED', u'Media Received', u'Media Returned']
|
| 62 |
acceptedHeadderColor = 2
|
| 63 |
|
| 64 |
mediaLogHeadderRow = 15
|
| 65 |
mediaLogJobNumberColumn = None
|
| 66 |
mediaLogColSizeMatrix = {}
|
| 67 |
|
| 68 |
## Unhide all hidden rows. Set to 10,000 rows for now.
|
| 69 |
sht.Rows('1:10000').Hidden = False
|
| 70 |
sht.Rows.AutoFit()
|
| 71 |
|
| 72 |
## Get a list of the colums that you should grab based on the testing
|
| 73 |
sheetFieldsToGrab = []
|
| 74 |
colStart,colEnd = GatherColumnTableEnd(sht,mediaLogHeadderRow,1)
|
| 75 |
for i in range(colEnd):
|
| 76 |
i = i + 1
|
| 77 |
#print "Testing %s"%i
|
| 78 |
#print "color is %d"% sht.Cells(mediaLogHeadderRow,i).Interior.ColorIndex
|
| 79 |
## First test the color
|
| 80 |
if sht.Cells(mediaLogHeadderRow,i).Interior.ColorIndex == acceptedHeadderColor:
|
| 81 |
## Then test that the test is in the approved list
|
| 82 |
cellText = sht.Cells(mediaLogHeadderRow,i).Value
|
| 83 |
if cellText in acceptedFieldsList:
|
| 84 |
sheetFieldsToGrab.append(i)
|
| 85 |
mediaLogColSizeMatrix[i] = sht.Cells(mediaLogHeadderRow,i).ColumnWidth
|
| 86 |
## Try to find the Job Number field too
|
| 87 |
if cellText == acceptedFieldsList[0]:
|
| 88 |
mediaLogJobNumberColumn = i
|
| 89 |
print sheetFieldsToGrab
|
| 90 |
|
| 91 |
## Using the jobNumber column, gather the highest row number
|
| 92 |
if mediaLogJobNumberColumn:
|
| 93 |
rowEnd = GatherRowTableEnd(sht, mediaLogHeadderRow, mediaLogJobNumberColumn)
|
| 94 |
print "End row is %d"% rowEnd
|
| 95 |
targetxlBook = xlApp.Workbooks.Add()
|
| 96 |
targetSht = targetxlBook.Worksheets(1)
|
| 97 |
|
| 98 |
## First let's set a starting column on the target
|
| 99 |
targetColumnStart = 2
|
| 100 |
|
| 101 |
for fieldNumber in sheetFieldsToGrab:
|
| 102 |
targetMediaLogHeadderRow = mediaLogHeadderRow-mediaLogHeadderRow +3
|
| 103 |
targetRowEnd = rowEnd-mediaLogHeadderRow + 1
|
| 104 |
sht.Range(sht.Cells(mediaLogHeadderRow,fieldNumber),sht.Cells(rowEnd,fieldNumber)).Copy(targetSht.Range(targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart),targetSht.Cells(targetRowEnd,targetColumnStart)))
|
| 105 |
#targetSht.Columns.AutoFit()
|
| 106 |
targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart).ColumnWidth = mediaLogColSizeMatrix[fieldNumber]
|
| 107 |
|
| 108 |
targetColumnStart = targetColumnStart + 1
|
| 109 |
|
| 110 |
#targetSht.Range(targetSht.Cells(2,targetMediaLogHeadderRow),targetSht.Cells(targetColumnStart,targetMediaLogHeadderRow)).Columns.AutoFit()
|
| 111 |
#targetSht.Columns.AutoFit()
|
| 112 |
|
| 113 |
## Set the pretty case name at the top of the media log
|
| 114 |
targetSht.Cells(1,2).Value = "%s Media Log"%caseName
|
| 115 |
targetSht.Cells(1,2).Font.Name = "Calibri"
|
| 116 |
targetSht.Cells(1,2).Font.Size = 16
|
| 117 |
|
| 118 |
targetxlBook.SaveAs(targetSpreadsheet)
|
| 119 |
targetxlBook.Close()
|
| 120 |
|
| 121 |
else:
|
| 122 |
print "ERROR: Not able to locate job number column!!"
|
| 123 |
xlBook.Close(SaveChanges=False)
|
| 124 |
|
