Active_prgs/Evidox/EvidoxJSParser.py

"""

EvidoxJSParser

Created by
Emanuel Borges
03.20.2018

A program or just set of methods that will parse and create a new report from the JS.


"""

from win32com.client import Dispatch

def GatherColumnTableEnd(sheet, rowStart, colStart):
    """Takes a JS sheet and returns the start and end range of what has data in it."""
    right = colStart
    while sheet.Cells(rowStart, right + 1).Value not in [None,""]:
        right = right +1

    colEnd = right

    return colStart, colEnd

def GatherRowTableEnd(sheet, rowStart, col):
    """using only the col field, will test all rows, starting at rowstart and returns the end row"""
    bottom = rowStart
    while sheet.Cells(bottom + 1, col).Value not in [None, '']:
        bottom = bottom +1

    rowEnd = bottom
    return rowEnd


if __name__ == '__main__':
    xlApp = Dispatch('Excel.Application')
    ##  Turn off any messages about saving over existing target files.
    xlApp.DisplayAlerts = False

    ## matrix of casename, (sourceJS, targetJS)
    #reportsToCreate = {'Bielins - Bennardo':
    #                   (r"C:\Test-PY\liveTest\Bielins-Bennardo-Job Summary.xlsx",r"C:\Test-PY\liveTest\Bielins - BennardoTESTJSLiveReport.xlsx"),
    #                   'SharkNinja - Keurig':
    #                   (r"C:\Test-PY\liveTest\SharkNinja-Keurig-Job Summary.xlsx",r"C:\Test-PY\liveTest\SharkNinja - KeurigTESTJSLiveReport.xlsx")}

    reportsToCreate = {'I Grace Productions':
                       (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"),
                       'McLaughlin Northrup - McDonald Fracassa':
                       (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")}
    
    for reportCaseItem in reportsToCreate.keys():
        caseName = reportCaseItem
        sourceSpreadsheetPath,targetSpreadsheet = reportsToCreate[reportCaseItem]

        print "Now processing %s..."% caseName
        xlBook = xlApp.Workbooks.Open(sourceSpreadsheetPath)
        #xlBook = xlApp.Workbooks.Open(r"\\iadcifs01\job\Lamb Barnosky\I Grace Productions\I Grace Productions--Job Summary.xlsx")
        #targetSpreadsheet = r"C:\Test-PY\liveTest\OutputJSReport.xlsx"
        sht = xlBook.Worksheets(1)
        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']
        acceptedHeadderColor = 2
        
        mediaLogHeadderRow = 15
        mediaLogJobNumberColumn = None
        mediaLogColSizeMatrix = {}

        ##  Unhide all hidden rows.  Set to 10,000 rows for now.
        sht.Rows('1:10000').Hidden = False
        sht.Rows.AutoFit()

        ##  Get a list of the colums that you should grab based on the testing
        sheetFieldsToGrab = []
        colStart,colEnd = GatherColumnTableEnd(sht,mediaLogHeadderRow,1)
        for i in range(colEnd):
            i = i + 1
            #print "Testing %s"%i
            #print "color is %d"% sht.Cells(mediaLogHeadderRow,i).Interior.ColorIndex
            ##  First test the color
            if sht.Cells(mediaLogHeadderRow,i).Interior.ColorIndex == acceptedHeadderColor:
                ##  Then test that the test is in the approved list
                cellText = sht.Cells(mediaLogHeadderRow,i).Value
                if cellText in acceptedFieldsList:
                    sheetFieldsToGrab.append(i)
                    mediaLogColSizeMatrix[i] = sht.Cells(mediaLogHeadderRow,i).ColumnWidth
                    ##  Try to find the Job Number field too
                    if cellText == acceptedFieldsList[0]:
                        mediaLogJobNumberColumn = i
        print sheetFieldsToGrab

        ##  Using the jobNumber column, gather the highest row number
        if mediaLogJobNumberColumn:
            rowEnd = GatherRowTableEnd(sht, mediaLogHeadderRow, mediaLogJobNumberColumn)
            print "End row is %d"% rowEnd
            targetxlBook = xlApp.Workbooks.Add()
            targetSht = targetxlBook.Worksheets(1)

            ##  First let's set a starting column on the target
            targetColumnStart = 2
            
            for fieldNumber in sheetFieldsToGrab:
                targetMediaLogHeadderRow = mediaLogHeadderRow-mediaLogHeadderRow +3
                targetRowEnd = rowEnd-mediaLogHeadderRow + 1
                sht.Range(sht.Cells(mediaLogHeadderRow,fieldNumber),sht.Cells(rowEnd,fieldNumber)).Copy(targetSht.Range(targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart),targetSht.Cells(targetRowEnd,targetColumnStart)))
                #targetSht.Columns.AutoFit()
                targetSht.Cells(targetMediaLogHeadderRow,targetColumnStart).ColumnWidth = mediaLogColSizeMatrix[fieldNumber]
                
                targetColumnStart = targetColumnStart + 1
                
            #targetSht.Range(targetSht.Cells(2,targetMediaLogHeadderRow),targetSht.Cells(targetColumnStart,targetMediaLogHeadderRow)).Columns.AutoFit()
            #targetSht.Columns.AutoFit()

            ##  Set the pretty case name at the top of the media log
            targetSht.Cells(1,2).Value = "%s Media Log"%caseName
            targetSht.Cells(1,2).Font.Name = "Calibri"
            targetSht.Cells(1,2).Font.Size = 16
            
            targetxlBook.SaveAs(targetSpreadsheet)
            targetxlBook.Close()
            
        else:
            print "ERROR: Not able to locate job number column!!"
        xlBook.Close(SaveChanges=False)
    
Revision:	633
Committed:	Wed Mar 28 13:54:39 2018 UTC (8 years ago) by nino.borges
Content type:	text/x-python
File size:	5974 byte(s)
Log Message:	A folder for my Evidox programs
#	User	Rev	Content
1	nino.borges	633	"""
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