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 593 by nino.borges, Tue Nov 3 22:48:00 2015 UTC vs.
Revision 594 by nino.borges, Wed Nov 4 21:57:34 2015 UTC

#	Line 24 | Line 24 | import os
24		import win32com.client as win32
25		from win32com.client import constants as c
26
27	+	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		if __name__ == '__main__':
114		startDir = r""
115
#	Line 43 | Line 129 | if __name__ == '__main__':
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.EntireRow.Hidden = False
141	>	ws.Rows("1:%d"%lastrow).EntireRow.Hidden = False
142		print "Done."
143		print "Unhiding all columns..."
144		## Unhiding all columns
145	<	ws.Columns.EntireRow.Hidden = False
145	>	ws.Columns.EntireColumn.Hidden = False
146		print "Done."
147
148		print "processing sheet..."
#	Line 93 | Line 186 | if __name__ == '__main__':
186
187		print "Sheet processed."
188		print "Saving..."
189	<	pth,ext = filePathAndName
189	>	pth,ext = os.path.splitext(filePathAndName)
190		##  ALWAYS SAVE THE WORKBOOK AND NOT JUST THE SHEET. WB NOT WS
191		wb.SaveAs(pth + "Fixed"+ext)
192		wb.Close()

Diff Legend

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