Hi Sumedha,
Here is the comparison script:
function Main()
{
var oExcel = Sys.OleObject("Excel.Application");
var aSheets = Sys.OleObject("Scripting.Dictionary");
//var vSheet = "Sheet1";
//aSheets.Add(vSheet, vSheet);
var fileName = "C:\\Temp\\book1.xls";
var aInfo1 = createInfo(oExcel, fileName, aSheets);
var fileName = "C:\\Temp\\book1.xls";
var aInfo2 = createInfo(oExcel, fileName, aSheets);
var oResult = compare(aInfo1, aInfo2);
if (oResult.length > 0)
{
Log.Warning(oResult);
}
else
{
Log.Message("The books are the same.");
}
oExcel.Quit();
}
function createInfo(oExcel, fileName, aSheets)
{
oExcel.Workbooks.Close();
oExcel.Workbooks.Open(fileName);
var oSheets = oExcel.Worksheets;
sheetCount = oSheets.Count;
if (aSheets.Count == 0)
{
for (var sheetId = 1; sheetId < sheetCount + 1; sheetId++)
{
var oSheet = oSheets.Item(sheetId);
aSheets.Add(oSheet.Name, oSheet.Name);
}
}
var vSheets = Sys.OleObject("Scripting.Dictionary");
for (var sheetId = 1; sheetId < sheetCount + 1; sheetId++)
{
var oSheet = oSheets.Item(sheetId);
if (aSheets.Item(oSheet.Name) == null)
{
continue;
}
var vSheet = Sys.OleObject("Scripting.Dictionary");
var rowCount = oSheet.UsedRange.Rows.Count;
for (var rowId = 1; rowId < rowCount + 1; rowId++)
{
var rowObj = oSheet.Rows.Item(rowId);
var vRow = Sys.OleObject("Scripting.Dictionary");
var cellCount = rowObj.Cells.Count;
for (var cellId = 1; cellId < cellCount + 1; cellId++)
{
var cellObj = rowObj.Cells.Item(cellId);
vRow.Add(cellObj.Column, cellObj.Text);
}
vSheet.Add(rowId, vRow);
}
vSheets.Add(oSheet.Name, vSheet);
}
return vSheets;
}
function compare(aInfo1, aInfo2)
{
var strCompare = "";
if (aInfo1.Count != aInfo1.Count)
{
strCompare = strCompare + "Wrong sheet count!"
return strCompare;
}
var vKeys = aInfo1.Keys().toArray();
var sheetCount = aInfo1.Count;
for (var sheetId = 0; sheetId < sheetCount; sheetId++)
{
if (!aInfo2.Exists(vKeys[sheetId]))
{
strCompare = strCompare + "Sheet names are different!";
return strCompare;
}
var oSheet1 = aInfo1.Item(vKeys[sheetId]);
var oSheet2 = aInfo2.Item(vKeys[sheetId]);
var rowCount1 = oSheet1.Count;
var rowCount2 = oSheet2.Count;
if (rowCount1 != rowCount2)
{
strCompare = strCompare + "Wrong used row count in the '" + vKeys[sheetId] + "' sheet."
return strCompare;
}
for (var rowId = 0; rowId < rowCount1; rowId++)
{
var rowObj1Key = oSheet1.Keys().toArray()[rowId];
if (!oSheet2.Exists(rowObj1Key))
{
strCompare = strCompare + "The '" + rowObj1Key + "' row key was not found.";
return strCompare;
}
var oRow1 = oSheet1.Item(rowObj1Key);
var oRow2 = oSheet2.Item(rowObj1Key);
var cellCount = oRow1.Count;
for (var cellId = 0; cellId < cellCount; cellId++)
{
var celObj1Key = oRow1.Keys().toArray()[cellId]
if (!oRow2.Exists(celObj1Key))
{
strCompare = strCompare + "The '" + celObj1Key + "' cell was not found." + "\r\n";
return strCompare;
}
var oCell1 = oRow1.Item(celObj1Key);
var oCell2 = oRow2.Item(celObj1Key);
if (!SameText(oCell1, oCell2))
{
strCompare = strCompare + "The " + vKeys[sheetId] + ":\r\n The [" + rowId + ", " + cellId + "] cell was changed." + "\r\n" + " The '" + oCell1 + "' value." + "\r\n" + " The '" + oCell2 + "' value." + "\r\n";
}
}
}
}
return strCompare;
}
The script will be added to the How To section later.