ViewVC Help

View File | Revision Log | Show Annotations | View Changeset | Root Listing

root/ns_dev/Python/NinoCode/Active_prgs/ExcelEmptyRowRemover.py

Comparing Python/NinoCode/Active_prgs/ExcelEmptyRowRemover.py (file contents):
Revision 594 by nino.borges, Wed Nov 4 21:57:34 2015 UTC vs.
Revision 595 by nino.borges, Sat Nov 7 14:54:52 2015 UTC

#	Line 14 | Line 14 | Problem:
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
17	>	* Remove filters
18	>	* Determine the largest sheet area both for rows and columns
19		* Hide these "empty" rows.
20		* Save as a new version of the file.
21
#	Line 25 | Line 26 | import win32com.client as win32
26		from win32com.client import constants as c
27
28		def ReturnExcelFormat(rowNumb,columnNumb):
29	<	"""test"""
29	>	"""Converts row and column numbers to Excel format."""
30		LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
31		result = []
32		while columnNumb:
#	Line 34 | Line 35 | def ReturnExcelFormat(rowNumb,columnNumb
35		return ''.join(result)+str(rowNumb)
36
37		def ProcessFile(absFilePath):
38	+	"""The main method for this program that processes a single file."""
39		excel = win32.gencache.EnsureDispatch('Excel.Application')
40		excel.Application.ScreenVisible = False
41	<	wb = excel.Workbooks.Open(filePathAndName)
41	>	print "\nNow processing %s\n"%absFilePath
42	>	wb = excel.Workbooks.Open(absFilePath)
43		excel.Application.ScreenUpdating = False
44
45		sheetCount = wb.Sheets.Count
#	Line 64 | Line 67 | def ProcessFile(absFilePath):
67		print "Done."
68
69		print "processing sheet..."
70	<	## got to be a better way to do this.
71	<	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))
70	>
71	>	r = ws.Range("A1:%s"%ReturnExcelFormat(lastrow,lastcol))
72		rows = r.Rows.Count
73		emptyRowBeg = False
74		for i in range(1,rows+1):
#	Line 104 | Line 102 | def ProcessFile(absFilePath):
102
103		print "Sheet processed."
104		print "Saving..."
105	<	pth,ext = os.path.splitext(filePathAndName)
105	>	pth,ext = os.path.splitext(absFilePath)
106		##  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	+	excel.Application.ScreenUpdating = True
111
112		if __name__ == '__main__':
113	<	startDir = r""
114	<
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	<	## 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	<	print "Unhiding all rows..."
140	<	## Unhiding all rows
141	<	ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
142	<	print "Done."
143	<	print "Unhiding all columns..."
144	<	## Unhiding all columns
145	<	ws.Columns.EntireColumn.Hidden = False
146	<	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
113	>	startDir = r"R:\0-Labels_and_Forms\Many Tool for Excel files"
114	>	#startDir = r"C:\Test\FinalTest"
115
116	<	print "Sheet processed."
117	<	print "Saving..."
118	<	pth,ext = os.path.splitext(filePathAndName)
119	<	##  ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
120	<	wb.SaveAs(pth + "Fixed"+ext)
192	<	wb.Close()
193	<	print "Saved and closed."
116	>	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...")

Diff Legend

–	Removed lines
+	Added lines
<	Changed lines (old)
>	Changed lines (new)