1 // extensions: xls;xlsx;xlsm;xlsb;xlam
3 // TortoiseSVN Diff script for Excel files
5 // Copyright (C) 2004-2008 the TortoiseSVN team
6 // This file is distributed under the same license as TortoiseSVN
14 // Hiroki Najima <h.najima at gmail.com>, 2013
15 // Michael Joras <michael@joras.net>, 2008
16 // Suraj Barkale, 2006
19 // ----- configuration -----
21 // Fast mode does not copy Worksheets but require opened base document at the same time.
22 var bFastMode = false;
24 // ----- constants -----
25 var vbCritical = 0x10;
26 var vbExclamation = 0x30;
27 //var vbInformation = 0x40;
30 var xlMaximized = -4137;
31 var xlArrangeStyleHorizontal = -4128;
33 //var xlExpression = 2;
39 //var vOffice2000 = 9;
40 //var vOffice2002 = 10;
42 //var vOffice2007 = 12;
43 //var vOffice2010 = 14;
44 //var vOffice2013 = 15;
48 var aWarningMessages = Array();
50 var objArgs = WScript.Arguments;
51 if (objArgs.length < 2)
53 Abort("Usage: [CScript | WScript] diff-xls.js base.xls new.xls", "Invalid arguments");
56 var sBaseDoc = objArgs(0);
57 var sNewDoc = objArgs(1);
59 var objScript = new ActiveXObject("Scripting.FileSystemObject");
61 if (objScript.GetBaseName(sBaseDoc) === objScript.GetBaseName(sNewDoc))
63 Abort("File '" + sBaseDoc + "' and '" + sNewDoc + "' is same file name.\nCannot compare the documents.", "Same file name");
66 if (!objScript.FileExists(sBaseDoc))
68 Abort("File '" + sBaseDoc + "' does not exist.\nCannot compare the documents.", "File not found");
71 if (!objScript.FileExists(sNewDoc))
73 Abort("File '" + sNewDoc + "' does not exist.\nCannot compare the documents.", "File not found");
76 sBaseDoc = objScript.GetAbsolutePathName(sBaseDoc);
77 sNewDoc = objScript.GetAbsolutePathName(sNewDoc);
83 objExcelApp = WScript.CreateObject("Excel.Application");
87 Abort("You must have Excel installed to perform this operation.", "Excel Instantiation Failed");
89 var fExcelVersion = parseInt(objExcelApp.Version);
91 // Open base Excel book
95 objBaseWorkbook = objExcelApp.Workbooks.Open(sBaseDoc, null, true);
99 Abort("Failed to open '" + sBaseDoc + "'\nIt might not be a valid Excel file.", "File open error");
102 // Open new Excel book
106 objNewWorkbook = objExcelApp.Workbooks.Open(sNewDoc, null, true);
110 Abort("Failed to open '" + sNewDoc + "'\nIt might not be a valid Excel file.", "File open error");
114 objExcelApp.Visible = true;
117 if (objBaseWorkbook.ProtectWindows || objNewWorkbook.ProtectWindows)
119 StoreWarning("Unable to arrange windows because one or both workbooks are protected.");
123 // Make windows a compare side by side view
124 if (fExcelVersion >= vOffice2003)
126 objExcelApp.Windows.CompareSideBySideWith(objExcelApp.Windows(2).Caption);
128 objExcelApp.Application.WindowState = xlMaximized;
129 objExcelApp.Windows.Arrange(xlArrangeStyleHorizontal);
132 if (!bFastMode && objNewWorkbook.ProtectWindows)
134 StoreWarning("Fallback to fast mode bacause " + objNewWorkbook.Name + " is protected.");
138 // Mark differences in sNewDoc red
139 var length = objNewWorkbook.Worksheets.Count;
140 for (var i = 1; i <= length; i++)
142 var objBaseWorksheet = objBaseWorkbook.Worksheets(i);
143 var objNewWorksheet = objNewWorkbook.Worksheets(i);
145 UnhideWorksheet(objBaseWorksheet);
146 UnhideWorksheet(objNewWorksheet);
150 objBaseWorkbook.Sheets(i).Copy(null, objNewWorkbook.Sheets(objNewWorkbook.Sheets.Count));
151 var objDummyWorksheet = objNewWorkbook.Sheets(objNewWorkbook.Sheets.Count);
152 objDummyWorksheet.Name = "Dummy_for_Comparison" + i;
153 objDummyWorksheet.Visible = true;
154 if (fExcelVersion >= vOffice2003)
156 objDummyWorksheet.Tab.ColorIndex = 16; // 16:Dark gray RGB(128,128,128)
160 if (objNewWorksheet.ProtectContents)
162 StoreWarning("Unable to mark differences to " +
163 ToAbsoluteReference(objNewWorksheet) +
164 " because the Worksheet is protected.");
168 objNewWorksheet.Cells.FormatConditions.Delete();
172 sFormula = "=INDIRECT(\"" + ToAbsoluteReference(objBaseWorksheet) + "!\"&ADDRESS(ROW(),COLUMN()))";
176 sFormula = "=INDIRECT(\"Dummy_for_Comparison" + i + "!\"&ADDRESS(ROW(),COLUMN()))";
178 objNewWorksheet.Cells.FormatConditions.Add(xlCellValue, xlNotEqual, sFormula);
179 objNewWorksheet.Cells.FormatConditions(1).Interior.ColorIndex = 3; // 3:Red RGB(128,0,0)
184 // Activate first Worksheet
185 objBaseWorkbook.Sheets(1).Activate();
186 objNewWorkbook.Sheets(1).Activate();
188 // Suppress save dialog if nothing changed
189 objBaseWorkbook.Saved = true;
190 objNewWorkbook.Saved = true;
192 // Show warnings if exist
198 // ----- functions -----
200 // Show Message Dialog
201 // VBcript's MsgBox emulation
202 function MsgBox(sMessage, iButtons, sTitle)
204 var objShell = new ActiveXObject("WScript.Shell");
205 objShell.popup(sMessage, 0, sTitle, iButtons);
208 // Show an error message and quit script with cleanup Excel Application Object.
209 function Abort(sMessage, sTitle)
211 MsgBox(sMessage, vbCritical, sTitle);
212 if (objExcelApp !== null)
219 // Unhide the Worksheet if it is hidden.
220 // This also sets color to the tab, if Office2003 or later.
221 // - 46(Orange) : Hidden Worksheet
222 // - xlNone(default) : Not hidden Worksheet
223 function UnhideWorksheet(objWorksheet)
225 if (objWorksheet.Visible)
227 if (fExcelVersion >= vOffice2003)
229 if (objWorksheet.Tab.ColorIndex !== xlNone)
231 if (objWorksheet.Parent.ProtectStructure)
233 StoreWarning("Unable to set tab color to " +
234 ToAbsoluteReference(objWorksheet) +
235 " because the Workbook's structure is protected.");
239 objWorksheet.Tab.ColorIndex = xlNone;
246 if (objWorksheet.Parent.ProtectStructure)
248 StoreWarning("Unable to unhide " +
249 ToAbsoluteReference(objWorksheet) +
250 " because the Workbook's structure is protected.");
254 objWorksheet.Visible = true;
255 if (fExcelVersion >= vOffice2003)
257 objWorksheet.Tab.ColorIndex = 10; // 10:Green RGB(0,128,0)
263 // Generate Absolute Reference Formula of Worksheet.
264 function ToAbsoluteReference(objWorksheet)
266 return "[" + objWorksheet.Parent.Name + "]" + objWorksheet.Name;
269 // Accumulate a warning message.
270 function StoreWarning(sMessage)
272 aWarningMessages[aWarningMessages.length] = sMessage;
275 // Show accumulated warning messages if exist.
276 // To avoid make huge message dialog, this limits message count to show.
277 function ShowWarning()
279 if (aWarningMessages.length === 0)
283 var sMessage = "The following warnings occurred while processing.\n";
284 for (var i = 0; i < aWarningMessages.length; i++)
288 sMessage += "... And more " + (aWarningMessages.length - i) + " messages";
291 sMessage += "[" + (i + 1) + "] " + aWarningMessages[i] + "\n";
293 MsgBox(sMessage, vbExclamation, "Warning");