| blob | 1decd4f5eb0c77e30a063bbd787472a0e552a9cd |
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, 2012-2015 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: steveking $\r
10 // $Date: 2015-10-26 20:01:19 +0100 (Mo, 26 Okt 2015) $\r
11 // $Rev: 26930 $\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 = [];\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 // Create a special workbook for formula convertion.\r
139 var objSpecialWorkbook = objExcelApp.Workbooks.Add;\r
140 \r
141 // Mark differences in sNewDoc red\r
142 var length = objNewWorkbook.Worksheets.Count;\r
143 for (var i = 1; i <= length; i++)\r
144 {\r
145 var objBaseWorksheet = null;\r
146 if (i <= objBaseWorkbook.Worksheets.Count)\r
147 objBaseWorksheet = objBaseWorkbook.Worksheets(i);\r
148 var objNewWorksheet = objNewWorkbook.Worksheets(i);\r
149 \r
150 if (objBaseWorksheet != null)\r
151 UnhideWorksheet(objBaseWorksheet);\r
152 UnhideWorksheet(objNewWorksheet);\r
153 \r
154 if (!bFastMode && (objBaseWorksheet != null))\r
155 {\r
156 objBaseWorkbook.Sheets(i).Copy(null, objNewWorkbook.Sheets(objNewWorkbook.Sheets.Count));\r
157 var objDummyWorksheet = objNewWorkbook.Sheets(objNewWorkbook.Sheets.Count);\r
158 objDummyWorksheet.Name = "Dummy_for_Comparison" + i;\r
159 objDummyWorksheet.Visible = true;\r
160 if (fExcelVersion >= vOffice2003)\r
161 {\r
162 objDummyWorksheet.Tab.ColorIndex = 16; // 16:Dark gray RGB(128,128,128)\r
163 }\r
164 }\r
165 \r
166 if (objNewWorksheet.ProtectContents)\r
167 {\r
168 StoreWarning("Unable to mark differences to " +\r
169 ToAbsoluteReference(objNewWorksheet) +\r
170 " because the Worksheet is protected.");\r
171 }\r
172 else\r
173 {\r
174 objNewWorksheet.Cells.FormatConditions.Delete();\r
175 var sFormula;\r
176 if (bFastMode && (objBaseWorksheet != null))\r
177 {\r
178 sFormula = "=INDIRECT(\"" + ToAbsoluteReference(objBaseWorksheet) + "!\"&ADDRESS(ROW(),COLUMN()))";\r
179 }\r
180 else\r
181 {\r
182 sFormula = "=INDIRECT(\"Dummy_for_Comparison" + i + "!\"&ADDRESS(ROW(),COLUMN()))";\r
183 }\r
184 sFormula = convertFormula(sFormula);\r
185 objNewWorksheet.Cells.FormatConditions.Add(xlCellValue, xlNotEqual, sFormula);\r
186 objNewWorksheet.Cells.FormatConditions(1).Interior.ColorIndex = 3; // 3:Red RGB(128,0,0)\r
187 }\r
188 }\r
189 \r
190 // Close the special workbook quietly\r
191 objSpecialWorkbook.Saved = true;\r
192 objSpecialWorkbook.Close();\r
193 \r
194 // Activate first Worksheet\r
195 objBaseWorkbook.Sheets(1).Activate();\r
196 objNewWorkbook.Sheets(1).Activate();\r
197 \r
198 // Suppress save dialog if nothing changed\r
199 objBaseWorkbook.Saved = true;\r
200 objNewWorkbook.Saved = true;\r
201 \r
202 // Show warnings if exist\r
203 ShowWarning();\r
204 \r
205 WScript.Quit(0);\r
206 \r
207 \r
208 // ----- functions -----\r
209 \r
210 // Show Message Dialog\r
211 // VBcript's MsgBox emulation\r
212 function MsgBox(sMessage, iButtons, sTitle)\r
213 {\r
214 var objShell = new ActiveXObject("WScript.Shell");\r
215 objShell.popup(sMessage, 0, sTitle, iButtons);\r
216 }\r
217 \r
218 // Show an error message and quit script with cleanup Excel Application Object.\r
219 function Abort(sMessage, sTitle)\r
220 {\r
221 MsgBox(sMessage, vbCritical, sTitle);\r
222 if (objExcelApp)\r
223 {\r
224 objExcelApp.Quit();\r
225 }\r
226 WScript.Quit(1);\r
227 }\r
228 \r
229 // Unhide the Worksheet if it is hidden.\r
230 // This also sets color to the tab, if Office2003 or later.\r
231 // - 46(Orange) : Hidden Worksheet\r
232 // - xlNone(default) : Not hidden Worksheet\r
233 function UnhideWorksheet(objWorksheet)\r
234 {\r
235 if (objWorksheet.Visible)\r
236 {\r
237 if (fExcelVersion >= vOffice2003)\r
238 {\r
239 if (objWorksheet.Tab.ColorIndex !== xlNone)\r
240 {\r
241 if (objWorksheet.Parent.ProtectStructure)\r
242 {\r
243 StoreWarning("Unable to set tab color to " +\r
244 ToAbsoluteReference(objWorksheet) +\r
245 " because the Workbook's structure is protected.");\r
246 }\r
247 else\r
248 {\r
249 objWorksheet.Tab.ColorIndex = xlNone;\r
250 }\r
251 }\r
252 }\r
253 }\r
254 else if (objWorksheet.Parent.ProtectStructure)\r
255 {\r
256 StoreWarning("Unable to unhide " +\r
257 ToAbsoluteReference(objWorksheet) +\r
258 " because the Workbook's structure is protected.");\r
259 }\r
260 else\r
261 {\r
262 objWorksheet.Visible = true;\r
263 if (fExcelVersion >= vOffice2003)\r
264 {\r
265 objWorksheet.Tab.ColorIndex = 10; // 10:Green RGB(0,128,0)\r
266 }\r
267 }\r
268 \r
269 }\r
270 \r
271 // Generate Absolute Reference Formula of Worksheet.\r
272 function ToAbsoluteReference(objWorksheet)\r
273 {\r
274 return "[" + objWorksheet.Parent.Name + "]" + objWorksheet.Name;\r
275 }\r
276 \r
277 // Convert a formula for workaround in some situation.\r
278 // Actually I don't know what will be changed between sFormula and FormulaLocal.\r
279 function convertFormula(sFormula)\r
280 {\r
281 var worksheet = objSpecialWorkbook.Sheets(1);\r
282 var originalContent = worksheet.Cells(1, 1).Formula;\r
283 worksheet.Cells(1, 1).Formula = sFormula;\r
284 sFormula = worksheet.Cells(1, 1).FormulaLocal;\r
285 worksheet.Cells(1, 1).Formula = originalContent;\r
286 return sFormula;\r
287 }\r
288 \r
289 // Accumulate a warning message.\r
290 function StoreWarning(sMessage)\r
291 {\r
292 aWarningMessages[aWarningMessages.length] = sMessage;\r
293 }\r
294 \r
295 // Show accumulated warning messages if exist.\r
296 // To avoid make huge message dialog, this limits message count to show.\r
297 function ShowWarning()\r
298 {\r
299 if (aWarningMessages.length === 0)\r
300 {\r
301 return;\r
302 }\r
303 var sMessage = "The following warnings occurred while processing.\n";\r
304 for (var j = 0; j < aWarningMessages.length; j++)\r
305 {\r
306 if (j >= 10)\r
307 {\r
308 sMessage += "... And more " + (aWarningMessages.length - j) + " messages";\r
309 break;\r
310 }\r
311 sMessage += "[" + (j + 1) + "] " + aWarningMessages[j] + "\n";\r
312 }\r
313 MsgBox(sMessage, vbExclamation, "Warning");\r
314 }\r