If comments are being added to cells and then sorting is applied, then comments associated with the cells will not be sorted. The data itself will of course be sorted, but the comments will not follow with the data.
For example if a comment is added to cell A2 and then sorting is applied so that the data in that cell is sorted to cell A1, then the comment will remain at cell A2.
I use the following method to add comments with notes to cells:
I’ve tired different methods of sorting, but nothing seems to make the comments follow the sorted data. If try sorting using Excel, then comments will be sorted correctly, so this seems to be a bug unless I’m doing something wrong?
After an initial test, I am able to reproduce the issue as you mentioned. I found if comments are added to cells and then sorting is applied, then comments associated with the cells will not be sorted. The data itself will ofcourse be sorted but the comments will not follow with the data. For example if a comment is added to cell A2 and then sorting is applied so that the data in that cell is sorted to cell A1, then the comment will remain at cell A2.
e.g
Sample code:
Workbook workbook = new Workbook(“Bk_CommentsSorting1.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);
int commentIndex = worksheet.getComments().add(“A1”);
Comment comment = worksheet.getComments().get(commentIndex);
comment.setNote(“This is my comment1”);
int commentIndex1 = worksheet.getComments().add(“A2”);
Comment comment1 = worksheet.getComments().get(commentIndex1);
comment1.setNote(“This is my new comment”);
//Create your cell area
CellArea ca = CellArea.createCellArea(“A1”, “B5”);
//Create your sorter
DataSorter sorter = workbook.getDataSorter();
//Find the index, since we want to sort by column A, so we should know
//the index for sorter
int idx = CellsHelper.columnNameToIndex(“A”);
//Add key in sorter, it will sort in Ascending order
sorter.addKey(idx, SortOrder.ASCENDING);
//Perform sort
sorter.sort(worksheet.getCells(), ca);
workbook.save(“outBkCommentSorting1.xlsx”);
I have attached the input and output files for your reference. Also, if we try sorting using MS Excel, then comments will be sorted correctly.
I have logged a ticket with an id “CELLSJAVA-42224” for your issue. We will look into it and figure it out soon.
Once we fix your issue or we have some other update, we will share it with you here.
This is to inform you that we have fixed your issue CELLSJAVA-42224 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
Thanks for
the fix. However, after this update I now get a warning from Excel saying that
there’s a problem with the content of the generated file (see attachments). This issue happens with files that contain comments and where sorting has been applied as previously described.
Please note that it only happens in the new update (aspose-cells-17.4.0). If I roll back to a previous version I will not
get this issue.
This issue cannot be fixed properly unless you provide your sample Excel file. Previously, we created the Excel file based on your description and we found, the issue is fixed with that file. But since you have not provided your Excel file, so you are still facing problems. Please remember, every Excel file is unique internally and although, we fix issues generically but still some of the issues remain. So, we do need your sample Excel file to fix this issue completely.
no problem! I’ve attached two example files. The files have been created using the exact same piece of code and data. The only difference is that one was created with aspose-cells-17.3.0 and the other one with aspose-cells-17.4.0 (the one that has the issue mentioned above).
Thanks for your posting and considering Aspose.Cells.
Unfortunately, you provided us not the source Excel file but output Excel files. By opening your files in MS-Excel, we can observe that 17.4.0 file is corrupt, but without your source Excel file, we cannot replicate this issue.
So, what I did, I took your 17.3.0 file as your source Excel file and ran the following code and nothing bad happened with output Excel file.
So, we not only need your source Excel file but we also need your code. Because the following code is not replicating the issue, it means, this code is invalid.
Kindly, provide your code as well as source excel file, so that we look into this issue and fix it at the earliest.
Java
Workbook workbook = new Workbook(dirPath + “file_generated_with_aspose-cells-17.3.0.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);
int commentIndex = worksheet.getComments().add(“A1”);
Comment comment = worksheet.getComments().get(commentIndex);
comment.setNote(“This is my comment1”);
int commentIndex1 = worksheet.getComments().add(“A2”);
Comment comment1 = worksheet.getComments().get(commentIndex1);
comment1.setNote(“This is my new comment”);
//Create your cell area
CellArea ca = CellArea.createCellArea(“A1”, “B5”);
//Create your sorter
DataSorter sorter = workbook.getDataSorter();
//Find the index, since we want to sort by column A, so we should know
//the index for sorter
int idx = CellsHelper.columnNameToIndex(“A”);
//Add key in sorter, it will sort in Ascending order
sorter.addKey(idx, SortOrder.ASCENDING);
//Perform sort
sorter.sort(worksheet.getCells(), ca);
workbook.save(dirPath + “outBkCommentSorting1.xlsx”);
After an initial test, I am able to reproduce the issue as you mentioned by using the following sample code with your template file. I found that by sorting comments causes “Excel found unreadable content…” error when opening the output file into MS Excel. Even we can reproduce the issue by simply re-sorting comments in your good file:
e.g
Sample code:
Workbook workbook = new Workbook("file_generated_with_aspose-cells-17.3.0.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
//Create your cell area
CellArea ca = CellArea.createCellArea("A4", "G18");
//Create your sorter
DataSorter sorter = workbook.getDataSorter();
//Find the index, since we want to sort by column A, so we should know
//the index for sorter
int idx = CellsHelper.columnNameToIndex("A");
//Add key in sorter, it will sort in Ascending order
sorter.addKey(idx, SortOrder.ASCENDING);
//Perform sort
sorter.sort(worksheet.getCells(), ca);
workbook.save("out1.xlsx");
The output file is corrupted and MS Excel gives error when opening the file into it. I have logged a separate ticket with an id “CELLSJAVA-42265” for your issue. We will look into it soon.
Once we have an update on it, we will let you know here.
This is to inform you that we have fixed your issue CELLSJAVA-42265 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.