Active_prgs/Redgrave/RelativitySearchReport.py

"""

ATT-RelativitySearchReport

Created by:
Emanuel Borges
05.6.2025

This very simple program will take a Relativity saved search information export and convert it to a more useful report that can be shared with others and contains
detail contained in the notes section of the search.  This assumes the best practice of notating what the search does in the Notes field.

"""


import os, csv, pathlib
from win32com.client import Dispatch

version = "0.05"


def GenerateSearchReport(rawReportFile, outputFilename, relativityInstancePath, workspaceArtifactID):
    """test"""
    headerRow = ["Artifact ID", "Search Name", "Notes"]
    
    reportMatrix = {}

    with open(rawReportFile,encoding='UTF-8') as csvFile:
        csvReader = csv.DictReader(csvFile,delimiter=',',quotechar='"')
        for row in csvReader:

            pathParts = pathlib.Path(row['Path'].replace(row['Name'],''))
            rowFolderName = "\\".join(pathParts.parts[1:-1])

            if rowFolderName in list(reportMatrix.keys()):
                reportMatrix[rowFolderName].append((row['Name'],row['Notes'],f"{relativityInstancePath}go?id={workspaceArtifactID}-{row['Artifact ID']}", row['Artifact ID']))
            else:
                reportMatrix[rowFolderName] = [(row['Name'],row['Notes'],f"{relativityInstancePath}go?id={workspaceArtifactID}-{row['Artifact ID']}", row['Artifact ID']),]
            #print(f"{row['Artifact ID']} | {rowFolderName} | {row['Name']} | {row['Notes']} | {relativityInstancePath}go?id={workspaceArtifactID}-{row['Artifact ID']}" )

    folderGroupList = list(reportMatrix.keys())
    folderGroupList.sort()


    xlApp = Dispatch('Excel.Application')
    xlBook = xlApp.Workbooks.Add()
    sht = xlBook.Worksheets(1)
    colNumb = 1
    for fName in headerRow:
        sht.Cells(1,colNumb).Value = fName
        colNumb +=1
    
    rowNumb = 2

    for folderName in folderGroupList:
        colNumb = 2
        print(folderName)
        sht.Cells(rowNumb,colNumb).Value = folderName
        sht.Cells(rowNumb,colNumb).Interior.Color = 12632256
        sht.Cells(rowNumb,colNumb+1).Interior.Color = 12632256
        folderSearches = reportMatrix[folderName]
        folderSearches.sort()
        rowNumb +=1
        for srch in folderSearches:
            colNumb = 1
            print(f"\t{srch}")
            ## Write the artifact ID
            sht.Cells(rowNumb,colNumb).Value = srch[-1]
            colNumb +=1
            ##  Write the search name
            sht.Cells(rowNumb,colNumb).Value = srch[0]
            ##  Make it a real hyperlink
            sht.Hyperlinks.Add(sht.Range(f"B{rowNumb}"), Address = srch[2])
            colNumb +=1
            ##  Write the Notes
            sht.Cells(rowNumb,colNumb).Value = srch[1]
            colNumb +=1

            #for value in srch:
            #    sht.Cells(rowNumb,colNumb).Value = value
            #    colNumb +=1
            rowNumb +=1
            
    sht.Columns("A:A").Hidden = True
    sht.Columns("B:B").AutoFit()
    sht.Columns("C:C").ColumnWidth = 150
    sht.Columns("C:C").WrapText = True
    
    xlBook.SaveAs(outputFilename)
    xlApp.Quit()


if __name__ == '__main__':
    rawReportFile = r"C:\Test_Dir\ATT\export_20250522_174604.csv"
    outputFilename = r"C:\Test_Dir\ATT\export_20250522_174604_OUT2.XLSX"
    

    ##  For Lighthouse
    relativityInstancePath = 'https://relativity4.lighthouseglobal.com/Relativity/'

    ##  For shiny
    #workspaceArtifactID = '1267840'
    ##  For Vermont
    workspaceArtifactID = '1530226'


    GenerateSearchReport(rawReportFile, outputFilename, relativityInstancePath, workspaceArtifactID)
Revision:	898
Committed:	Wed May 28 14:27:56 2025 UTC (9 months, 4 weeks ago) by nino.borges
Content type:	text/x-python
File size:	3749 byte(s)
Log Message:	Added ability for column B to be auto fit and for column C to be 150 and auto wrap.
#	User	Rev	Content
1	nino.borges	882	"""
2
3			ATT-RelativitySearchReport
4
5			Created by:
6			Emanuel Borges
7			05.6.2025
8
9			This very simple program will take a Relativity saved search information export and convert it to a more useful report that can be shared with others and contains
10			detail contained in the notes section of the search. This assumes the best practice of notating what the search does in the Notes field.
11
12			"""
13
14
15			import os, csv, pathlib
16	nino.borges	883	from win32com.client import Dispatch
17	nino.borges	882
18	nino.borges	898	version = "0.05"
19	nino.borges	882
20
21	nino.borges	892	def GenerateSearchReport(rawReportFile, outputFilename, relativityInstancePath, workspaceArtifactID):
22			"""test"""
23	nino.borges	883	headerRow = ["Artifact ID", "Search Name", "Notes"]
24	nino.borges	892
25	nino.borges	883	reportMatrix = {}
26
27			with open(rawReportFile,encoding='UTF-8') as csvFile:
28	nino.borges	882	csvReader = csv.DictReader(csvFile,delimiter=',',quotechar='"')
29			for row in csvReader:
30	nino.borges	883
31			pathParts = pathlib.Path(row['Path'].replace(row['Name'],''))
32			rowFolderName = "\\".join(pathParts.parts[1:-1])
33
34			if rowFolderName in list(reportMatrix.keys()):
35			reportMatrix[rowFolderName].append((row['Name'],row['Notes'],f"{relativityInstancePath}go?id={workspaceArtifactID}-{row['Artifact ID']}", row['Artifact ID']))
36			else:
37			reportMatrix[rowFolderName] = [(row['Name'],row['Notes'],f"{relativityInstancePath}go?id={workspaceArtifactID}-{row['Artifact ID']}", row['Artifact ID']),]
38			#print(f"{row['Artifact ID']} \| {rowFolderName} \| {row['Name']} \| {row['Notes']} \| {relativityInstancePath}go?id={workspaceArtifactID}-{row['Artifact ID']}" )
39
40			folderGroupList = list(reportMatrix.keys())
41			folderGroupList.sort()
42
43
44			xlApp = Dispatch('Excel.Application')
45			xlBook = xlApp.Workbooks.Add()
46			sht = xlBook.Worksheets(1)
47			colNumb = 1
48			for fName in headerRow:
49			sht.Cells(1,colNumb).Value = fName
50			colNumb +=1
51
52			rowNumb = 2
53
54			for folderName in folderGroupList:
55			colNumb = 2
56			print(folderName)
57			sht.Cells(rowNumb,colNumb).Value = folderName
58			sht.Cells(rowNumb,colNumb).Interior.Color = 12632256
59			sht.Cells(rowNumb,colNumb+1).Interior.Color = 12632256
60			folderSearches = reportMatrix[folderName]
61			folderSearches.sort()
62			rowNumb +=1
63			for srch in folderSearches:
64			colNumb = 1
65			print(f"\t{srch}")
66			## Write the artifact ID
67			sht.Cells(rowNumb,colNumb).Value = srch[-1]
68			colNumb +=1
69			## Write the search name
70			sht.Cells(rowNumb,colNumb).Value = srch[0]
71			## Make it a real hyperlink
72			sht.Hyperlinks.Add(sht.Range(f"B{rowNumb}"), Address = srch[2])
73			colNumb +=1
74			## Write the Notes
75			sht.Cells(rowNumb,colNumb).Value = srch[1]
76			colNumb +=1
77
78			#for value in srch:
79			# sht.Cells(rowNumb,colNumb).Value = value
80			# colNumb +=1
81			rowNumb +=1
82	nino.borges	898
83	nino.borges	883	sht.Columns("A:A").Hidden = True
84	nino.borges	898	sht.Columns("B:B").AutoFit()
85			sht.Columns("C:C").ColumnWidth = 150
86			sht.Columns("C:C").WrapText = True
87
88	nino.borges	883	xlBook.SaveAs(outputFilename)
89	nino.borges	892	xlApp.Quit()
90
91
92			if __name__ == '__main__':
93			rawReportFile = r"C:\Test_Dir\ATT\export_20250522_174604.csv"
94			outputFilename = r"C:\Test_Dir\ATT\export_20250522_174604_OUT2.XLSX"
95
96
97			## For Lighthouse
98			relativityInstancePath = 'https://relativity4.lighthouseglobal.com/Relativity/'
99
100			## For shiny
101			#workspaceArtifactID = '1267840'
102			## For Vermont
103			workspaceArtifactID = '1530226'
104
105
106	nino.borges	898	GenerateSearchReport(rawReportFile, outputFilename, relativityInstancePath, workspaceArtifactID)