NinoCode/Active_prgs/ExcelEmptyRowRemover.py

"""
Excel Empty Row Remover

Created by
Emanuel Borges
10.30.2015

Problem:
  An issue came up with thousands of Excel files where they needed to be tiffed but because they had
  millions of rows of empty but not technically empty data, they would tiff out in the millions of pages.
  Since there was formatting on those rows, LAW wouldnt remove the empty pages. Imaging in Relativity
  also didnt work, since it would just error out.
  
Solution: This program will take a directory of Excel files, open them, using COM, and do the following, for every
          sheet in the document:
            * Unhide all rows and columns
            * Determin the largest sheet area both for rows and columns
            * Hide these "empty" rows.
            * Save as a new version of the file.

"""

import os
import win32com.client as win32
from win32com.client import constants as c

def ReturnExcelFormat(rowNumb,columnNumb):
    """test"""
    LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    result = []
    while columnNumb:
        columnNumb, remdr = divmod(columnNumb-1, 26)
        result[:0] = LETTERS[remdr]
    return ''.join(result)+str(rowNumb)

def ProcessFile(absFilePath):
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Application.ScreenVisible = False
    wb = excel.Workbooks.Open(filePathAndName)
    excel.Application.ScreenUpdating = False

    sheetCount = wb.Sheets.Count
    for sheetNumb in range(1,sheetCount+1):
        ws = wb.Worksheets(sheetNumb)
        lastrow = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Row
        emptyList = []
        lastcol = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Column
        print "working on worksheet %s. %s rows"%(sheetNumb,lastrow)

        ## Removing any filters in a try, since it will error if there isnt a filter applied.
        print "Removing any filters..."
        try:
            ws.ShowAllData()
        except:
            pass
        print "Done."
        print "Unhiding all rows..."
        ## Unhiding all rows
        ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
        print "Done."
        print "Unhiding all columns..."
        ## Unhiding all columns
        ws.Columns.EntireColumn.Hidden = False
        print "Done."

        print "processing sheet..."
        ## got to be a better way to do this.
        LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
        result = []
        while lastcol:
            lastcol, rem = divmod(lastcol-1, 26)
            result[:0] = LETTERS[rem]
        r = ws.Range("A1:%s"%''.join(result)+str(lastrow))
        rows = r.Rows.Count
        emptyRowBeg = False
        for i in range(1,rows+1):
            if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
                if emptyRowBeg:
                    pass
                else:
                    emptyRowBeg = i
            else:
                if emptyRowBeg:
                    emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
                    emptyRowBeg = False
        for x in emptyList:
            beg,end = x.split(",")
            ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
            
    
    for i in range(1,rows+1):
        if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
            if emptyRowBeg:
                pass
            else:
                emptyRowBeg = i
        else:
            if emptyRowBeg:
                emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
                emptyRowBeg = False
    for x in emptyList:
        beg,end = x.split(",")
        ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True

    print "Sheet processed."
    print "Saving..."
    pth,ext = os.path.splitext(filePathAndName)
    ##  ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
    wb.SaveAs(pth + "Fixed"+ext)
    wb.Close()
    print "Saved and closed."

if __name__ == '__main__':
    startDir = r""
    
    ## Remove this after beta to loop through all files instead.
    filePathAndName = r'c:\test\test.xlsx'
    
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Application.ScreenVisible = False
    wb = excel.Workbooks.Open(filePathAndName)
    excel.Application.ScreenUpdating = False

    sheetCount = wb.Sheets.Count
    for sheetNumb in range(1,sheetCount+1):
        ws = wb.Worksheets(sheetNumb)
        lastrow = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Row
        emptyList = []
        lastcol = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Column
        print "working on worksheet %s. %s rows"%(sheetNumb,lastrow)

        ## Removing any filters in a try, since it will error if there isnt a filter applied.
        print "Removing any filters..."
        try:
            ws.ShowAllData()
        except:
            pass
        print "Done."
        print "Unhiding all rows..."
        ## Unhiding all rows
        ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
        print "Done."
        print "Unhiding all columns..."
        ## Unhiding all columns
        ws.Columns.EntireColumn.Hidden = False
        print "Done."

        print "processing sheet..."
        ## got to be a better way to do this.
        LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
        result = []
        while lastcol:
            lastcol, rem = divmod(lastcol-1, 26)
            result[:0] = LETTERS[rem]
        r = ws.Range("A1:%s"%''.join(result)+str(lastrow))
        rows = r.Rows.Count
        emptyRowBeg = False
        for i in range(1,rows+1):
            if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
                if emptyRowBeg:
                    pass
                else:
                    emptyRowBeg = i
            else:
                if emptyRowBeg:
                    emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
                    emptyRowBeg = False
        for x in emptyList:
            beg,end = x.split(",")
            ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
            
    
    for i in range(1,rows+1):
        if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
            if emptyRowBeg:
                pass
            else:
                emptyRowBeg = i
        else:
            if emptyRowBeg:
                emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
                emptyRowBeg = False
    for x in emptyList:
        beg,end = x.split(",")
        ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True

    print "Sheet processed."
    print "Saving..."
    pth,ext = os.path.splitext(filePathAndName)
    ##  ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
    wb.SaveAs(pth + "Fixed"+ext)
    wb.Close()
    print "Saved and closed."
Revision:	594
Committed:	Wed Nov 4 21:57:34 2015 UTC (10 years, 4 months ago) by nino.borges
Content type:	text/x-python
File size:	7263 byte(s)
Log Message:	This version completely works. This is before refactoring.
#	User	Rev	Content
1	nino.borges	592	"""
2			Excel Empty Row Remover
3
4			Created by
5			Emanuel Borges
6			10.30.2015
7
8			Problem:
9			An issue came up with thousands of Excel files where they needed to be tiffed but because they had
10			millions of rows of empty but not technically empty data, they would tiff out in the millions of pages.
11			Since there was formatting on those rows, LAW wouldnt remove the empty pages. Imaging in Relativity
12			also didnt work, since it would just error out.
13
14			Solution: This program will take a directory of Excel files, open them, using COM, and do the following, for every
15			sheet in the document:
16			* Unhide all rows and columns
17			* Determin the largest sheet area both for rows and columns
18			* Hide these "empty" rows.
19			* Save as a new version of the file.
20
21			"""
22
23			import os
24			import win32com.client as win32
25			from win32com.client import constants as c
26
27	nino.borges	594	def ReturnExcelFormat(rowNumb,columnNumb):
28			"""test"""
29			LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
30			result = []
31			while columnNumb:
32			columnNumb, remdr = divmod(columnNumb-1, 26)
33			result[:0] = LETTERS[remdr]
34			return ''.join(result)+str(rowNumb)
35
36			def ProcessFile(absFilePath):
37			excel = win32.gencache.EnsureDispatch('Excel.Application')
38			excel.Application.ScreenVisible = False
39			wb = excel.Workbooks.Open(filePathAndName)
40			excel.Application.ScreenUpdating = False
41
42			sheetCount = wb.Sheets.Count
43			for sheetNumb in range(1,sheetCount+1):
44			ws = wb.Worksheets(sheetNumb)
45			lastrow = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Row
46			emptyList = []
47			lastcol = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Column
48			print "working on worksheet %s. %s rows"%(sheetNumb,lastrow)
49
50			## Removing any filters in a try, since it will error if there isnt a filter applied.
51			print "Removing any filters..."
52			try:
53			ws.ShowAllData()
54			except:
55			pass
56			print "Done."
57			print "Unhiding all rows..."
58			## Unhiding all rows
59			ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
60			print "Done."
61			print "Unhiding all columns..."
62			## Unhiding all columns
63			ws.Columns.EntireColumn.Hidden = False
64			print "Done."
65
66			print "processing sheet..."
67			## got to be a better way to do this.
68			LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
69			result = []
70			while lastcol:
71			lastcol, rem = divmod(lastcol-1, 26)
72			result[:0] = LETTERS[rem]
73			r = ws.Range("A1:%s"%''.join(result)+str(lastrow))
74			rows = r.Rows.Count
75			emptyRowBeg = False
76			for i in range(1,rows+1):
77			if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
78			if emptyRowBeg:
79			pass
80			else:
81			emptyRowBeg = i
82			else:
83			if emptyRowBeg:
84			emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
85			emptyRowBeg = False
86			for x in emptyList:
87			beg,end = x.split(",")
88			ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
89
90
91			for i in range(1,rows+1):
92			if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
93			if emptyRowBeg:
94			pass
95			else:
96			emptyRowBeg = i
97			else:
98			if emptyRowBeg:
99			emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
100			emptyRowBeg = False
101			for x in emptyList:
102			beg,end = x.split(",")
103			ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
104
105			print "Sheet processed."
106			print "Saving..."
107			pth,ext = os.path.splitext(filePathAndName)
108			## ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
109			wb.SaveAs(pth + "Fixed"+ext)
110			wb.Close()
111			print "Saved and closed."
112
113	nino.borges	592	if __name__ == '__main__':
114			startDir = r""
115
116			## Remove this after beta to loop through all files instead.
117			filePathAndName = r'c:\test\test.xlsx'
118
119			excel = win32.gencache.EnsureDispatch('Excel.Application')
120			excel.Application.ScreenVisible = False
121			wb = excel.Workbooks.Open(filePathAndName)
122			excel.Application.ScreenUpdating = False
123
124			sheetCount = wb.Sheets.Count
125			for sheetNumb in range(1,sheetCount+1):
126			ws = wb.Worksheets(sheetNumb)
127			lastrow = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Row
128			emptyList = []
129			lastcol = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Column
130			print "working on worksheet %s. %s rows"%(sheetNumb,lastrow)
131
132	nino.borges	594	## Removing any filters in a try, since it will error if there isnt a filter applied.
133			print "Removing any filters..."
134			try:
135			ws.ShowAllData()
136			except:
137			pass
138			print "Done."
139	nino.borges	592	print "Unhiding all rows..."
140			## Unhiding all rows
141	nino.borges	594	ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
142	nino.borges	592	print "Done."
143			print "Unhiding all columns..."
144			## Unhiding all columns
145	nino.borges	594	ws.Columns.EntireColumn.Hidden = False
146	nino.borges	592	print "Done."
147
148			print "processing sheet..."
149			## got to be a better way to do this.
150			LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
151			result = []
152			while lastcol:
153			lastcol, rem = divmod(lastcol-1, 26)
154			result[:0] = LETTERS[rem]
155			r = ws.Range("A1:%s"%''.join(result)+str(lastrow))
156			rows = r.Rows.Count
157			emptyRowBeg = False
158			for i in range(1,rows+1):
159			if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
160			if emptyRowBeg:
161			pass
162			else:
163			emptyRowBeg = i
164			else:
165			if emptyRowBeg:
166			emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
167			emptyRowBeg = False
168			for x in emptyList:
169			beg,end = x.split(",")
170			ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
171
172
173			for i in range(1,rows+1):
174			if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
175			if emptyRowBeg:
176			pass
177			else:
178			emptyRowBeg = i
179			else:
180			if emptyRowBeg:
181			emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
182			emptyRowBeg = False
183			for x in emptyList:
184			beg,end = x.split(",")
185			ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
186
187			print "Sheet processed."
188			print "Saving..."
189	nino.borges	594	pth,ext = os.path.splitext(filePathAndName)
190	nino.borges	592	## ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
191			wb.SaveAs(pth + "Fixed"+ext)
192			wb.Close()
193			print "Saved and closed."