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
            * Remove filters
            * Determine 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):
    """Converts row and column numbers to Excel format."""
    LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    result = []
    while columnNumb:
        columnNumb, remdr = divmod(columnNumb-1, 26)
        result[:0] = LETTERS[remdr]
    return ''.join(result)+str(rowNumb)

def ProcessFile(absFilePath):
    """The main method for this program that processes a single file."""
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Application.ScreenVisible = False
    print "\nNow processing %s\n"%absFilePath
    wb = excel.Workbooks.Open(absFilePath)
    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..."
        
        r = ws.Range("A1:%s"%ReturnExcelFormat(lastrow,lastcol))
        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(absFilePath)
    ##  ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
    wb.SaveAs(pth + "Fixed"+ext)
    wb.Close()
    print "Saved and closed."
    excel.Application.ScreenUpdating = True

if __name__ == '__main__':
    startDir = r"R:\0-Labels_and_Forms\Many Tool for Excel files"
    #startDir = r"C:\Test\FinalTest"

    raw_input("\n\nExcel Empty Row Remover\nVersion 1.0.0\nCreated by Emanuel Borges\n\nThis program will processs all of the files in the following directory.\n%s\n\n\n Press any key to start.\n\n"%startDir)
    for f in os.listdir(startDir):
        if os.path.isfile(os.path.join(startDir,f)):
            ProcessFile(os.path.join(startDir,f))
    raw_input("\nAll Files Processed.\n\nPress Enter to continue...")
Revision:	595
Committed:	Sat Nov 7 14:54:52 2015 UTC (10 years, 4 months ago) by nino.borges
Content type:	text/x-python
File size:	4733 byte(s)
Log Message:	This is after refactoring and is the version that I gave to app support and ESI
#	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	nino.borges	595	* Remove filters
18			* Determine the largest sheet area both for rows and columns
19	nino.borges	592	* Hide these "empty" rows.
20			* Save as a new version of the file.
21
22			"""
23
24			import os
25			import win32com.client as win32
26			from win32com.client import constants as c
27
28	nino.borges	594	def ReturnExcelFormat(rowNumb,columnNumb):
29	nino.borges	595	"""Converts row and column numbers to Excel format."""
30	nino.borges	594	LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
31			result = []
32			while columnNumb:
33			columnNumb, remdr = divmod(columnNumb-1, 26)
34			result[:0] = LETTERS[remdr]
35			return ''.join(result)+str(rowNumb)
36
37			def ProcessFile(absFilePath):
38	nino.borges	595	"""The main method for this program that processes a single file."""
39	nino.borges	594	excel = win32.gencache.EnsureDispatch('Excel.Application')
40			excel.Application.ScreenVisible = False
41	nino.borges	595	print "\nNow processing %s\n"%absFilePath
42			wb = excel.Workbooks.Open(absFilePath)
43	nino.borges	594	excel.Application.ScreenUpdating = False
44
45			sheetCount = wb.Sheets.Count
46			for sheetNumb in range(1,sheetCount+1):
47			ws = wb.Worksheets(sheetNumb)
48			lastrow = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Row
49			emptyList = []
50			lastcol = ws.Cells.Find(What="*",After=ws.Range("A1"),LookAt=c.xlPart,LookIn=c.xlFormulas,SearchOrder=c.xlByRows,SearchDirection=c.xlPrevious,MatchCase=False).Column
51			print "working on worksheet %s. %s rows"%(sheetNumb,lastrow)
52
53			## Removing any filters in a try, since it will error if there isnt a filter applied.
54			print "Removing any filters..."
55			try:
56			ws.ShowAllData()
57			except:
58			pass
59			print "Done."
60			print "Unhiding all rows..."
61			## Unhiding all rows
62			ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
63			print "Done."
64			print "Unhiding all columns..."
65			## Unhiding all columns
66			ws.Columns.EntireColumn.Hidden = False
67			print "Done."
68
69			print "processing sheet..."
70	nino.borges	595
71			r = ws.Range("A1:%s"%ReturnExcelFormat(lastrow,lastcol))
72	nino.borges	594	rows = r.Rows.Count
73			emptyRowBeg = False
74			for i in range(1,rows+1):
75			if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
76			if emptyRowBeg:
77			pass
78			else:
79			emptyRowBeg = i
80			else:
81			if emptyRowBeg:
82			emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
83			emptyRowBeg = False
84			for x in emptyList:
85			beg,end = x.split(",")
86			ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
87
88
89			for i in range(1,rows+1):
90			if excel.WorksheetFunction.CountA(r.Rows(i)) == 0:
91			if emptyRowBeg:
92			pass
93			else:
94			emptyRowBeg = i
95			else:
96			if emptyRowBeg:
97			emptyList.append(("%s,%s")%(emptyRowBeg,i-1))
98			emptyRowBeg = False
99			for x in emptyList:
100			beg,end = x.split(",")
101			ws.Rows("%d:%d"%(int(beg),int(end))).EntireRow.Hidden = True
102
103			print "Sheet processed."
104			print "Saving..."
105	nino.borges	595	pth,ext = os.path.splitext(absFilePath)
106	nino.borges	594	## ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
107			wb.SaveAs(pth + "Fixed"+ext)
108			wb.Close()
109			print "Saved and closed."
110	nino.borges	595	excel.Application.ScreenUpdating = True
111	nino.borges	594
112	nino.borges	592	if __name__ == '__main__':
113	nino.borges	595	startDir = r"R:\0-Labels_and_Forms\Many Tool for Excel files"
114			#startDir = r"C:\Test\FinalTest"
115	nino.borges	592
116	nino.borges	595	raw_input("\n\nExcel Empty Row Remover\nVersion 1.0.0\nCreated by Emanuel Borges\n\nThis program will processs all of the files in the following directory.\n%s\n\n\n Press any key to start.\n\n"%startDir)
117			for f in os.listdir(startDir):
118			if os.path.isfile(os.path.join(startDir,f)):
119			ProcessFile(os.path.join(startDir,f))
120			raw_input("\nAll Files Processed.\n\nPress Enter to continue...")