| blob | 79773f7a588956759a95566e5d395e9744ede5be |
1 // extensions: xls;xlsx;xlsm;xlsb;xlam\r
2 //\r
3 // TortoiseSVN Diff script for Excel files\r
4 //\r
5 // Copyright (C) 2004-2008 the TortoiseSVN team\r
6 // This file is distributed under the same license as TortoiseSVN\r
7 //\r
8 // Last commit by:\r
9 // $Author$\r
10 // $Date$\r
11 // $Rev$\r
12 //\r
13 // Authors:\r
14 // Hiroki Najima <h.najima at gmail.com>, 2013\r
15 // Michael Joras <michael@joras.net>, 2008\r
16 // Suraj Barkale, 2006\r
17 //\r
18 \r
19 // ----- configuration -----\r
20 // Fast mode switch\r
21 // Fast mode does not copy Worksheets but require opened base document at the same time.\r
22 var bFastMode = false;\r
23 \r
24 // ----- constants -----\r
25 var vbCritical = 0x10;\r
26 var vbExclamation = 0x30;\r
27 //var vbInformation = 0x40;\r
28 \r
29 var xlNone = -4142;\r
30 var xlMaximized = -4137;\r
31 var xlArrangeStyleHorizontal = -4128;\r
32 var xlCellValue = 1;\r
33 //var xlExpression = 2;\r
34 //var xlEqual = 3;\r
35 var xlNotEqual = 4;\r
36 \r
37 //var vOffice95 = 7;\r
38 //var vOffice97 = 8;\r
39 //var vOffice2000 = 9;\r
40 //var vOffice2002 = 10;\r
41 var vOffice2003 = 11;\r
42 //var vOffice2007 = 12;\r
43 //var vOffice2010 = 14;\r
44 //var vOffice2013 = 15;\r
45 \r
46 // ----- main -----\r
47 \r
48 var aWarningMessages = Array();\r
49 \r
50 var objArgs = WScript.Arguments;\r
51 if (objArgs.length < 2)\r
52 {\r
53 Abort("Usage: [CScript | WScript] diff-xls.js base.xls new.xls", "Invalid arguments");\r
54 }\r
55 \r
56 var sBaseDoc = objArgs(0);\r
57 var sNewDoc = objArgs(1);\r
58 \r
59 var objScript = new ActiveXObject("Scripting.FileSystemObject");\r
60 \r
61 if (objScript.GetBaseName(sBaseDoc) === objScript.GetBaseName(sNewDoc))\r
62 {\r
63 Abort("File '" + sBaseDoc + "' and '" + sNewDoc + "' is same file name.\nCannot compare the documents.", "Same file name");\r
64 }\r
65 \r
66 if (!objScript.FileExists(sBaseDoc))\r
67 {\r
68 Abort("File '" + sBaseDoc + "' does not exist.\nCannot compare the documents.", "File not found");\r
69 }\r
70 \r
71 if (!objScript.FileExists(sNewDoc))\r
72 {\r
73 Abort("File '" + sNewDoc + "' does not exist.\nCannot compare the documents.", "File not found");\r
74 }\r
75 \r
76 sBaseDoc = objScript.GetAbsolutePathName(sBaseDoc);\r
77 sNewDoc = objScript.GetAbsolutePathName(sNewDoc);\r
78 objScript = null;\r
79 \r
80 var objExcelApp;\r
81 try\r
82 {\r
83 objExcelApp = WScript.CreateObject("Excel.Application");\r
84 }\r
85 catch (e)\r
86 {\r
87 Abort("You must have Excel installed to perform this operation.", "Excel Instantiation Failed");\r
88 }\r
89 var fExcelVersion = parseInt(objExcelApp.Version, 10);\r
90 \r
91 // Open base Excel book\r
92 var objBaseWorkbook;\r
93 try\r
94 {\r
95 objBaseWorkbook = objExcelApp.Workbooks.Open(sBaseDoc, null, true);\r
96 }\r
97 catch (e)\r
98 {\r
99 Abort("Failed to open '" + sBaseDoc + "'\nIt might not be a valid Excel file.", "File open error");\r
100 }\r
101 \r
102 // Open new Excel book\r
103 var objNewWorkbook;\r
104 try\r
105 {\r
106 objNewWorkbook = objExcelApp.Workbooks.Open(sNewDoc, null, true);\r
107 }\r
108 catch (e)\r
109 {\r
110 Abort("Failed to open '" + sNewDoc + "'\nIt might not be a valid Excel file.", "File open error");\r
111 }\r
112 \r
113 // Show Excel window\r
114 objExcelApp.Visible = true;\r
115 \r
116 // Arrange windows\r
117 if (objBaseWorkbook.ProtectWindows || objNewWorkbook.ProtectWindows)\r
118 {\r
119 StoreWarning("Unable to arrange windows because one or both Workbooks are protected.");\r
120 }\r
121 else\r
122 {\r
123 // Make windows a compare side by side view\r
124 if (fExcelVersion >= vOffice2003)\r
125 {\r
126 objExcelApp.Windows.CompareSideBySideWith(objExcelApp.Windows(2).Caption);\r
127 }\r
128 objExcelApp.Application.WindowState = xlMaximized;\r
129 objExcelApp.Windows.Arrange(xlArrangeStyleHorizontal);\r
130 }\r
131 \r
132 if (!bFastMode && objNewWorkbook.ProtectWindows)\r
133 {\r
134 StoreWarning("Fall back to fast mode because " + objNewWorkbook.Name + " is protected.");\r
135 bFastMode = true;\r
136 }\r
137 \r
138 // Mark differences in sNewDoc red\r
139 var length = objNewWorkbook.Worksheets.Count;\r
140 for (var i = 1; i <= length; i++)\r
141 {\r
142 var objBaseWorksheet = objBaseWorkbook.Worksheets(i);\r
143 var objNewWorksheet = objNewWorkbook.Worksheets(i);\r
144 \r
145 UnhideWorksheet(objBaseWorksheet);\r
146 UnhideWorksheet(objNewWorksheet);\r
147 \r
148 if (!bFastMode)\r
149 {\r
150 objBaseWorkbook.Sheets(i).Copy(null, objNewWorkbook.Sheets(objNewWorkbook.Sheets.Count));\r
151 var objDummyWorksheet = objNewWorkbook.Sheets(objNewWorkbook.Sheets.Count);\r
152 objDummyWorksheet.Name = "Dummy_for_Comparison" + i;\r
153 objDummyWorksheet.Visible = true;\r
154 if (fExcelVersion >= vOffice2003)\r
155 {\r
156 objDummyWorksheet.Tab.ColorIndex = 16; // 16:Dark gray RGB(128,128,128)\r
157 }\r
158 }\r
159 \r
160 if (objNewWorksheet.ProtectContents)\r
161 {\r
162 StoreWarning("Unable to mark differences to " +\r
163 ToAbsoluteReference(objNewWorksheet) +\r
164 " because the Worksheet is protected.");\r
165 }\r
166 else\r
167 {\r
168 objNewWorksheet.Cells.FormatConditions.Delete();\r
169 var sFormula;\r
170 if (bFastMode)\r
171 {\r
172 sFormula = "=INDIRECT(\"" + ToAbsoluteReference(objBaseWorksheet) + "!\"&ADDRESS(ROW(),COLUMN()))";\r
173 }\r
174 else\r
175 {\r
176 sFormula = "=INDIRECT(\"Dummy_for_Comparison" + i + "!\"&ADDRESS(ROW(),COLUMN()))";\r
177 }\r
178 objNewWorksheet.Cells.FormatConditions.Add(xlCellValue, xlNotEqual, sFormula);\r
179 objNewWorksheet.Cells.FormatConditions(1).Interior.ColorIndex = 3; // 3:Red RGB(128,0,0)\r
180 }\r
181 \r
182 }\r
183 \r
184 // Activate first Worksheet\r
185 objBaseWorkbook.Sheets(1).Activate();\r
186 objNewWorkbook.Sheets(1).Activate();\r
187 \r
188 // Suppress save dialog if nothing changed\r
189 objBaseWorkbook.Saved = true;\r
190 objNewWorkbook.Saved = true;\r
191 \r
192 // Show warnings if exist\r
193 ShowWarning();\r
194 \r
195 WScript.Quit(0);\r
196 \r
197 \r
198 // ----- functions -----\r
199 \r
200 // Show Message Dialog\r
201 // VBcript's MsgBox emulation\r
202 function MsgBox(sMessage, iButtons, sTitle)\r
203 {\r
204 var objShell = new ActiveXObject("WScript.Shell");\r
205 objShell.popup(sMessage, 0, sTitle, iButtons);\r
206 }\r
207 \r
208 // Show an error message and quit script with cleanup Excel Application Object.\r
209 function Abort(sMessage, sTitle)\r
210 {\r
211 MsgBox(sMessage, vbCritical, sTitle);\r
212 if (objExcelApp)\r
213 {\r
214 objExcelApp.Quit();\r
215 }\r
216 WScript.Quit(1);\r
217 }\r
218 \r
219 // Unhide the Worksheet if it is hidden.\r
220 // This also sets color to the tab, if Office2003 or later.\r
221 // - 46(Orange) : Hidden Worksheet\r
222 // - xlNone(default) : Not hidden Worksheet\r
223 function UnhideWorksheet(objWorksheet)\r
224 {\r
225 if (objWorksheet.Visible)\r
226 {\r
227 if (fExcelVersion >= vOffice2003)\r
228 {\r
229 if (objWorksheet.Tab.ColorIndex !== xlNone)\r
230 {\r
231 if (objWorksheet.Parent.ProtectStructure)\r
232 {\r
233 StoreWarning("Unable to set tab color to " +\r
234 ToAbsoluteReference(objWorksheet) +\r
235 " because the Workbook's structure is protected.");\r
236 }\r
237 else\r
238 {\r
239 objWorksheet.Tab.ColorIndex = xlNone;\r
240 }\r
241 }\r
242 }\r
243 }\r
244 else\r
245 {\r
246 if (objWorksheet.Parent.ProtectStructure)\r
247 {\r
248 StoreWarning("Unable to unhide " +\r
249 ToAbsoluteReference(objWorksheet) +\r
250 " because the Workbook's structure is protected.");\r
251 }\r
252 else\r
253 {\r
254 objWorksheet.Visible = true;\r
255 if (fExcelVersion >= vOffice2003)\r
256 {\r
257 objWorksheet.Tab.ColorIndex = 10; // 10:Green RGB(0,128,0)\r
258 }\r
259 }\r
260 }\r
261 }\r
262 \r
263 // Generate Absolute Reference Formula of Worksheet.\r
264 function ToAbsoluteReference(objWorksheet)\r
265 {\r
266 return "[" + objWorksheet.Parent.Name + "]" + objWorksheet.Name;\r
267 }\r
268 \r
269 // Accumulate a warning message.\r
270 function StoreWarning(sMessage)\r
271 {\r
272 aWarningMessages[aWarningMessages.length] = sMessage;\r
273 }\r
274 \r
275 // Show accumulated warning messages if exist.\r
276 // To avoid make huge message dialog, this limits message count to show.\r
277 function ShowWarning()\r
278 {\r
279 if (aWarningMessages.length === 0)\r
280 {\r
281 return;\r
282 }\r
283 var sMessage = "The following warnings occurred while processing.\n";\r
284 for (var i = 0; i < aWarningMessages.length; i++)\r
285 {\r
286 if (i >= 10)\r
287 {\r
288 sMessage += "... And more " + (aWarningMessages.length - i) + " messages";\r
289 break;\r
290 }\r
291 sMessage += "[" + (i + 1) + "] " + aWarningMessages[i] + "\n";\r
292 }\r
293 MsgBox(sMessage, vbExclamation, "Warning");\r
294 }\r