1 // extensions: xls;xlsx;xlsm;xlsb;xlam
\r
3 // TortoiseSVN Diff script for Excel files
\r
5 // Copyright (C) 2004-2008 the TortoiseSVN team
\r
6 // This file is distributed under the same license as TortoiseSVN
\r
14 // Hiroki Najima <h.najima at gmail.com>, 2013
\r
15 // Michael Joras <michael@joras.net>, 2008
\r
16 // Suraj Barkale, 2006
\r
19 // ----- configuration -----
\r
21 // Fast mode does not copy Worksheets but require opened base document at the same time.
\r
22 var bFastMode = false;
\r
24 // ----- constants -----
\r
25 var vbCritical = 0x10;
\r
26 var vbExclamation = 0x30;
\r
27 //var vbInformation = 0x40;
\r
30 var xlMaximized = -4137;
\r
31 var xlArrangeStyleHorizontal = -4128;
\r
32 var xlCellValue = 1;
\r
33 //var xlExpression = 2;
\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
48 var aWarningMessages = Array();
\r
50 var objArgs = WScript.Arguments;
\r
51 if (objArgs.length < 2)
\r
53 Abort("Usage: [CScript | WScript] diff-xls.js base.xls new.xls", "Invalid arguments");
\r
56 var sBaseDoc = objArgs(0);
\r
57 var sNewDoc = objArgs(1);
\r
59 var objScript = new ActiveXObject("Scripting.FileSystemObject");
\r
61 if (objScript.GetBaseName(sBaseDoc) === objScript.GetBaseName(sNewDoc))
\r
63 Abort("File '" + sBaseDoc + "' and '" + sNewDoc + "' is same file name.\nCannot compare the documents.", "Same file name");
\r
66 if (!objScript.FileExists(sBaseDoc))
\r
68 Abort("File '" + sBaseDoc + "' does not exist.\nCannot compare the documents.", "File not found");
\r
71 if (!objScript.FileExists(sNewDoc))
\r
73 Abort("File '" + sNewDoc + "' does not exist.\nCannot compare the documents.", "File not found");
\r
76 sBaseDoc = objScript.GetAbsolutePathName(sBaseDoc);
\r
77 sNewDoc = objScript.GetAbsolutePathName(sNewDoc);
\r
83 objExcelApp = WScript.CreateObject("Excel.Application");
\r
87 Abort("You must have Excel installed to perform this operation.", "Excel Instantiation Failed");
\r
89 var fExcelVersion = parseInt(objExcelApp.Version, 10);
\r
91 // Open base Excel book
\r
92 var objBaseWorkbook;
\r
95 objBaseWorkbook = objExcelApp.Workbooks.Open(sBaseDoc, null, true);
\r
99 Abort("Failed to open '" + sBaseDoc + "'\nIt might not be a valid Excel file.", "File open error");
\r
102 // Open new Excel book
\r
103 var objNewWorkbook;
\r
106 objNewWorkbook = objExcelApp.Workbooks.Open(sNewDoc, null, true);
\r
110 Abort("Failed to open '" + sNewDoc + "'\nIt might not be a valid Excel file.", "File open error");
\r
113 // Show Excel window
\r
114 objExcelApp.Visible = true;
\r
117 if (objBaseWorkbook.ProtectWindows || objNewWorkbook.ProtectWindows)
\r
119 StoreWarning("Unable to arrange windows because one or both Workbooks are protected.");
\r
123 // Make windows a compare side by side view
\r
124 if (fExcelVersion >= vOffice2003)
\r
126 objExcelApp.Windows.CompareSideBySideWith(objExcelApp.Windows(2).Caption);
\r
128 objExcelApp.Application.WindowState = xlMaximized;
\r
129 objExcelApp.Windows.Arrange(xlArrangeStyleHorizontal);
\r
132 if (!bFastMode && objNewWorkbook.ProtectWindows)
\r
134 StoreWarning("Fall back to fast mode because " + objNewWorkbook.Name + " is protected.");
\r
138 // Create a special workbook for formula convertion.
\r
139 var objSpecialWorkbook = objExcelApp.Workbooks.Add;
\r
141 // Mark differences in sNewDoc red
\r
142 var length = objNewWorkbook.Worksheets.Count;
\r
143 for (var i = 1; i <= length; i++)
\r
145 var objBaseWorksheet = objBaseWorkbook.Worksheets(i);
\r
146 var objNewWorksheet = objNewWorkbook.Worksheets(i);
\r
148 UnhideWorksheet(objBaseWorksheet);
\r
149 UnhideWorksheet(objNewWorksheet);
\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
159 objDummyWorksheet.Tab.ColorIndex = 16; // 16:Dark gray RGB(128,128,128)
\r
163 if (objNewWorksheet.ProtectContents)
\r
165 StoreWarning("Unable to mark differences to " +
\r
166 ToAbsoluteReference(objNewWorksheet) +
\r
167 " because the Worksheet is protected.");
\r
171 objNewWorksheet.Cells.FormatConditions.Delete();
\r
175 sFormula = "=INDIRECT(\"" + ToAbsoluteReference(objBaseWorksheet) + "!\"&ADDRESS(ROW(),COLUMN()))";
\r
179 sFormula = "=INDIRECT(\"Dummy_for_Comparison" + i + "!\"&ADDRESS(ROW(),COLUMN()))";
\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
187 // Close the special workbook quietly
\r
188 objSpecialWorkbook.Saved = true;
\r
189 objSpecialWorkbook.Close;
\r
191 // Activate first Worksheet
\r
192 objBaseWorkbook.Sheets(1).Activate();
\r
193 objNewWorkbook.Sheets(1).Activate();
\r
195 // Suppress save dialog if nothing changed
\r
196 objBaseWorkbook.Saved = true;
\r
197 objNewWorkbook.Saved = true;
\r
199 // Show warnings if exist
\r
205 // ----- functions -----
\r
207 // Show Message Dialog
\r
208 // VBcript's MsgBox emulation
\r
209 function MsgBox(sMessage, iButtons, sTitle)
\r
211 var objShell = new ActiveXObject("WScript.Shell");
\r
212 objShell.popup(sMessage, 0, sTitle, iButtons);
\r
215 // Show an error message and quit script with cleanup Excel Application Object.
\r
216 function Abort(sMessage, sTitle)
\r
218 MsgBox(sMessage, vbCritical, sTitle);
\r
221 objExcelApp.Quit();
\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
232 if (objWorksheet.Visible)
\r
234 if (fExcelVersion >= vOffice2003)
\r
236 if (objWorksheet.Tab.ColorIndex !== xlNone)
\r
238 if (objWorksheet.Parent.ProtectStructure)
\r
240 StoreWarning("Unable to set tab color to " +
\r
241 ToAbsoluteReference(objWorksheet) +
\r
242 " because the Workbook's structure is protected.");
\r
246 objWorksheet.Tab.ColorIndex = xlNone;
\r
253 if (objWorksheet.Parent.ProtectStructure)
\r
255 StoreWarning("Unable to unhide " +
\r
256 ToAbsoluteReference(objWorksheet) +
\r
257 " because the Workbook's structure is protected.");
\r
261 objWorksheet.Visible = true;
\r
262 if (fExcelVersion >= vOffice2003)
\r
264 objWorksheet.Tab.ColorIndex = 10; // 10:Green RGB(0,128,0)
\r
270 // Generate Absolute Reference Formula of Worksheet.
\r
271 function ToAbsoluteReference(objWorksheet)
\r
273 return "[" + objWorksheet.Parent.Name + "]" + objWorksheet.Name;
\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
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
288 // Accumulate a warning message.
\r
289 function StoreWarning(sMessage)
\r
291 aWarningMessages[aWarningMessages.length] = sMessage;
\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
298 if (aWarningMessages.length === 0)
\r
302 var sMessage = "The following warnings occurred while processing.\n";
\r
303 for (var i = 0; i < aWarningMessages.length; i++)
\r
307 sMessage += "... And more " + (aWarningMessages.length - i) + " messages";
\r
310 sMessage += "[" + (i + 1) + "] " + aWarningMessages[i] + "\n";
\r
312 MsgBox(sMessage, vbExclamation, "Warning");
\r