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