how to compare two excel sheets content having same columns…while comparing two excel sheet data i want the data which doesnt match…using Aspose.cells
Hi,
Well, you may compare both worksheets data cell by cell manually, you may utilize Cell.Value, Cell.StringValue attributes. Also, there are String.Equals and CompareTo methods if these can be useful for your needs. You need to scan the cells data in a loop to compare it with other sheeet’s cells data for comparison. I am afraid, there is no better way to do that but to do it manually.
Thank you.
Hi Deepi,
I have the same requirement of comparing excel using aspose.cells.
It would highly appreciable if you can share solution/code for the same.
Hi Shivani,
Well, there is no specific APIs or solution available. Also, I am afraid, there is no better way to accomplish the task. As suggested in my previous reply (in this thread), you have to compare spreadsheets’ data cell by cell manually using your own code, you may utilize Cell.Value, Cell.StringValue attributes, etc… Also, there are .NET’s String.Equals and CompareTo methods if these can be useful for your needs. I think you need to check the cells data in a loop to compare it with other sheet’s cells data for comparison.
Thank you.
Hi Amjad,
I was trying to save compared excel file into image, but as when number of columns increases font size of cells reduces automatically. Please suggest a way where i can save excel in image with same formatting.
Hi,
Please try using Worksheet.PageSetup.PrintGridlines attribute to set it to true before taking images for the worksheet or rendering to PDF (if you are saving to it).
Also, see the document for your further reference:
Hope, this helps you a bit.
Thank you.
Hi,
The issue might not be related to Aspose.Cells. Well, if there are long list of rows/cols with data/contents in the worksheet and you are using OnePagePerSheet option or the relevant PageSetup options to render the big worksheet into single page, then you might get output image with small data/contents. So, you should disable the relevant option(s) and render separate images for different pages of the worksheet before rendering image(s). For your information, Aspose.Cells renders images as per the print preview of Ms Excel for the worksheet(s) (when shown after setting your relevant Page Setup options).
If you still think it is an issue (regarding formatting) with Aspose.Cells component, please provide your template Excel file, output image(s) and sample code (runnable) to reproduce the issue on our end, we will check it soon.
Thank you.
Hi,
Thank you for your response, The problem is after setting OnePagePerSheet to false if i save excel in image with columns more than 10 , it autofits into one page with reduced content size.
Please let us know is there any way where we can save excel into image with as much columns as we want with normal font formatting.
Thanks
Shivani
Hi,
As I told you earlier, Aspose.Cells renders images/PDF as per the print preview of Ms Excel for the worksheet (when shown after setting your relevant Page Setup options, etc.). Could you take the print preview of the sheet in MS Excel manually and check if you see different view than the output image generated by Aspose.Cells APIs.
And, to render image with maximum columns, you may try to use AllColumnsInOnePagePerSheet option, see the sample code below for your reference:
e.g
Sample code:
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(@“e:\test2\Book1.xls”);
Aspose.Cells.Rendering.ImageOrPrintOptions imgOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();
imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Png;
imgOptions.AllColumnsInOnePagePerSheet = true;
Aspose.Cells.Worksheet sheet = wb.Worksheets[0];
Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);
System.Drawing.Bitmap bitmap = sr.ToImage(0);
bitmap.Save(@“e:\test2\outimage1.png”, System.Drawing.Imaging.ImageFormat.Png);
Hope, this helps a bit.
Thank you.
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.