Hi Amjad, Thanks for your reply. It is true that MS Excel does not natively support spreadsheet comparison in their EXE application, but MS did acquire 3rd party tool called Prodiance Spreadsheet Compare in 2011 and then repackaged it, to offer it as a MS Office tool in 2013 and forward. Please look at that MS Office Tool called Spreadsheet Compare. It would be a powerful game changer, if Aspose.Cells could provide similar comparison feature.
Thanks for providing us further details.
Well, Aspose.Cells is a library and does not provide UI. We need to analyze your suggested tool (Spreadsheet Compare) if we can incorporate the feature to compare two workbooks and produces some kind of textual comparison results (if possible). I have logged a ticket with an id “CELLSNET-46492” for your demanded feature. We will look into it soon.
Once we have an update on it, we will let you know.
Thank you. I am excited to hear you logged a ticket for this enhancement. I am eager to hear this implemented. I agree that Aspose.Cells is a library and does not provide a UI, which is preferred, but the comparison API that I am requesting would return a list of changes or a new excel stream with the changes listed, that anyone can then handle appropriately, such as displaying on their own UI or Excel file. I look forward to hearing back.
You are welcome. We have logged all your concerns in the ticket. As this ticket is logged to early, so it is still in queue and we will write back here as soon as some feedback is ready to provide here.
This is to inform you that we have planned to support the feature (i.e., comparing cells, columns and rows) in Q1 2019.
That is great news! Will the new feature indicate different types of changes, such as Structure change (new worksheet, new row, etc.) vs Value change vs Formula change, etc. Similar to MS Office Tool: Spreadsheet Compare.
Also, how will I know when this new feature is actually delivered and which version?
Well, in the first phase, we might not support everything when comparing workbooks. Anyways, we will come back with more details on supported features/aspects for workbook comparisons.
You will be notified here once there is an update or supported version is available.
Well, I am afraid, we cannot support Structural changes for comparisons. We will only support comparing cells, columns and rows in comparisons.
Hi @Amjad_Sahi ,
This is to inform you that we have planned to support the feature (i.e., comparing cells, columns and rows) in Q1 2019.
Is this feature released already?
I couldn’t find the feature in Aspose.Cells.
Thanks.
@junpei.tsutsui,
We are afraid it is not supported yet. We will let you know as soon as this feature will be supported.
Hello, Is there any update on this feature?
@draftable
We are afraid it is not supported yet. Once we have any new information, we will share it with you. We will get back to you soon.
We have invested a considerable amount of effort into researching this feature, but had to temporarily suspend its development due to its complexity and other important tasks.
For this feaure, one of the key challenges lies in what should we compare and how to manage the comparison results. There are so many elements in workbook, such as global document properties, various settings of worksheets, objects such as pictures, charts, cells and their settings and contents, and so on. What kind of granularity should be used? How should the comparison results for each detail be organized and transformed into the content of the report?
As component provider, our primary consideration is to offer users a universal and flexible interface that meets the requirement of most users. However, for this particular feature, it seems that the requirements vary too much with different users’ scenarios, making it hard to meet them with a single unified interface. Therefore, we are attempting to re-start this task from another direction. In the initial phase, we may provide a customized implementation based on specific user’s requirement. Later we may expand functionality and flexibility or refactor the feature on this foundation.
So, would you please share your thoughts about this feature, especially what kind of data needs to be compared, how to build the compared results and put them into the final report? Some examples such as template files and the expected report data will be much helpful for us to understand the requirement and provide solution. Thank you.
And currently if you only need to compare cell data, you may do it with below sample code:
Worksheet sheet1 = ...;
Worksheet sheet2 = ...;
Cells cells1 = sheet1.Cells;
Cells cells2 = sheet2.Cells;
int maxRow = Math.Max(cells1.MaxDataRow, cells2.MaxDataRow);
int maxCol = Math.Max(cells1.MaxDataColumn, cells2.MaxDataColumn);
for (int i = 0; i <= maxRow; i++)
{
for (int j = 0; j <= maxCol; j++)
{
Cell cell1 = cells1.CheckCell(i, j);
Cell cell2 = cells2.CheckCell(i, j);
if (cell1 == null)
{
if (cell2 == null || string.IsNullOrEmpty(cell2.StringValue))
{
continue;
}
//output extra cell in sheet2
}
else if (cell2 == null)
{
if (string.IsNullOrEmpty(cell1.StringValue))
{
continue;
}
//output extra cell in sheet2
}
else if (!cell1.StringValue.Equals(cell2.StringValue))
{
//output different cells
}
}
}
Is there a programmatic way to do this comparison? I’d like to be able to load two workbooks created by Aspose and verify the file size matches. If I load the same workbook into two different Workbook objects and compare the streams, that works, but as soon as i save one, the file sizes are thrown off even if absolutely no changes are made.
Example:
Workbook template = new("SourceFile.xlsx", new LoadOptions(LoadFormat.Xlsx));
template.Save("NewFile.xlsx", new XlsSaveOptions(SaveFormat.Xlsx));
Workbook expected = new("NewFile.xlsx", new LoadOptions(LoadFormat.Xlsx));
template.Save("NewFile2.xlsx", new XlsSaveOptions(SaveFormat.Xlsx));
Workbook actual = new("NewFile2.xlsx", new LoadOptions(LoadFormat.Xlsx));
var expectedStream = expected.SaveToStream();
var actualStream = actual.SaveToStream();
Assert.True(expectedStream.Length == actualStream.Length); //this fails
Your code snippet has some issues. See and try the updated code segment.
e.g.,
Sample code:
Workbook template = new("SourceFile.xlsx", new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
template.Save("NewFile.xlsx", new OoxmlSaveOptions(SaveFormat.Xlsx));
Workbook expected = new("NewFile.xlsx", new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
template.Save("NewFile2.xlsx", new OoxmlSaveOptions(SaveFormat.Xlsx));
Workbook actual = new("NewFile2.xlsx", new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
MemoryStream expectedStream = new MemoryStream();
expected.Save(expectedStream, SaveFormat.Xlsx);
MemoryStream actualStream = new MemoryStream();
actual.Save(actualStream, SaveFormat.Xlsx);
Assert.True(expectedStream.Length == actualStream.Length);
Hi Team,
We are using licensed version of aspose excels for various purposes in our selenium c# code. we have to automate the two excel workbook comparison . those workbook has huge data, running with for loop and compare is tedious and time taking process. is there is a way to automate the comparison in aspose with predefined methods/functions?
please help us
Would you please describe your requirement in more details, such as which kind of data you need to compare? Because there are so many and different aspects may be required by different users, it is hard for us to provide a general function for such kind of requirements with good performance. Let us know more details about which kind of data you need to compare, the how to match the pairs of cells in different workbook, …etc. so we can make further investigation and help to find the solution if possible.
I reviewed the entire thread and realized that managing all the settings for a full workbook comparison in Excel can be quite complex. However, my requirement is a bit different.
Instead of comparing the entire workbook, I want to compare two named ranges. Here’s the scenario:
- I have two versions of the same Excel file — Version1 and Version2.
- Both files contain a named range with the same name.
- In Version2, changes have been made within that named range, such as adding or deleting rows or columns, merging or unmerging cells, or updating specific cell values.
I need an output that accurately identifies and highlights all these changes within the named range.
@Coder365
Please refer to the following sample code. The sample code first creates two identical named ranges and compares them, then changes the data and style before comparing. After testing, we can obtain the expected results. You can modify CompareNamedRange(Range range1, Range range2)method to control the properties that need to be compared.
The sample code as follows:
Workbook wb1 = CreateWorkbook();
Range range1 = wb1.Worksheets.GetRangeByName("workbookScope");
Console.WriteLine("name: " + range1.Name);
Console.WriteLine("address: " + range1.Address);
Workbook wb2 = CreateWorkbook();
Range range2 = wb2.Worksheets.GetRangeByName("workbookScope");
Console.WriteLine("name: " + range2.Name);
Console.WriteLine("address: " + range2.Address);
Console.WriteLine("======================first compare======================");
Console.WriteLine("before change data:" + CompareNamedRange(range1, range2));
//change data
Console.WriteLine("======================change A1 data======================");
Cell a1 = range1.Worksheet.Cells["A1"];
object originalValue = a1.Value;
a1.Value = "change data";
Console.WriteLine("after change data:" + CompareNamedRange(range1, range2));
//set original value, Fallback value modification
Console.WriteLine("======================Fallback A1 data======================");
a1.Value = originalValue;
Console.WriteLine("Fallback value modification: " + CompareNamedRange(range1, range2));
//change style
Console.WriteLine("======================change A1 style======================");
Style a1Style = a1.GetStyle();
a1Style.Font.Color = Color.Red;
a1.SetStyle(a1Style);
Console.WriteLine("after change style:" + CompareNamedRange(range1, range2));
private static bool CompareNamedRange(Range range1, Range range2)
{
if ((range1 == null && range2 != null) || (range1 != null && range2 == null))
{
return false;
}
if (range1.Address != range2.Address)
{
return false;
}
if (range1.Name != range2.Name)
{
return false;
}
int startRow = range1.FirstRow;
int endRow = range1.FirstRow + range1.RowCount - 1;
int startColumn = range1.FirstColumn;
int endColumn = range1.FirstColumn + range1.ColumnCount - 1;
Cells cells1 = range1.Worksheet.Cells;
Cells cells2 = range2.Worksheet.Cells;
for (int i = startRow; i <= endRow; i++)
{
for (int j = startColumn; j <= endColumn; j++)
{
Cell cell1 = cells1.CheckCell(i, j);
Cell cell2 = cells2.CheckCell(i, j);
if (cell1 == null)
{
if (cell2 == null || string.IsNullOrEmpty(cell2.StringValue))
{
continue;
}
Console.WriteLine("different in " + cell2.Name);
return false;
}
else if (cell2 == null)
{
if (string.IsNullOrEmpty(cell1.StringValue))
{
continue;
}
Console.WriteLine("different in " + cell2.Name);
return false;
}
else if (!cell1.StringValue.Equals(cell2.StringValue) || !cell1.GetStyle().Equals(cell2.GetStyle()))
{
Console.WriteLine("different in " + cell2.Name);
return false;
}
}
}
return true;
}
private static Workbook CreateWorkbook()
{
// Create a new Workbook object
Workbook workbook = new Workbook();
// Get first worksheet of the workbook
Worksheet sheet = workbook.Worksheets[0];
// Get worksheet's cells collection
Cells cells = sheet.Cells;
// Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Sales");
cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Tennis");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");
cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");
cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);
// Create a range of Cells from Cell A1 to C8
Range workbookScope = cells.CreateRange("A1", "C8");
// Assign the nsame to workbook scope named range
workbookScope.Name = "workbookScope";
return workbook;
}
The output result:
name: workbookScope
address: A1:C8
name: workbookScope
address: A1:C8
======================first compare======================
before change data:True
======================change A1 data======================
different in A1
after change data:False
======================Fallback A1 data======================
Fallback value modification: True
======================change A1 style======================
different in A1
after change style:False
Hope helps a bit.