Hi,
Hi,
It has been a few years (back in 2016) since Aspose responded to the inquiry of comparing workbooks, the only way to compare workbooks was to loop through worksheet cells one by one. That is not ideal and falls short for an enterprise solution. I would like to see an API that compares to workbooks and produce comparison result report, similar to desktop Office tool: Spreadsheet Compare. Is this feature supported in Aspose.Cells? If not, is it scheduled to be supported?
Well, Aspose.Cells follows Ms Excel standards and specifications and supports the features which Ms Excel incorporates. There is no comparison feature in Ms Excel, so it might not be supported. If you think there is relevant feature in Ms Excel, let us know with details and we will check it. I am afraid, you have devise the approach as suggested in one of our previous reply in the thread:
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);