Threaded comments order after import to Office365

Hello! I have code snippet (Aspose.Cells 19.10) for create workbook and several threaded comments.
After import to OneDrive and open in Office365 Excel I got comments order randomized:
image.png (35.2 KB)

Please help with this problem, I want to save comments order.

Snippet (Java)
import com.aspose.cells.*;

public class AsposeThreadCommentsOrderMain {

    public static void main(String[] args) throws Exception {
        License license = new License();
        license.setLicense("aspose.lic");

        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);
        int author1Index = workbook.getWorksheets().getThreadedCommentAuthors()
                                   .add("Author 1", "author1", "OV");
        int author2Index = workbook.getWorksheets().getThreadedCommentAuthors()
                                   .add("Author 2", "author2", "OV");

        ThreadedCommentAuthor author1 = workbook.getWorksheets().getThreadedCommentAuthors().get(author1Index);
        ThreadedCommentAuthor author2 = workbook.getWorksheets().getThreadedCommentAuthors().get(author2Index);

        addThreadedComment(worksheet, "C2", "1", author1);
        addThreadedComment(worksheet, "C2", "2", author1);
        addThreadedComment(worksheet, "C2", "3", author1);
        addThreadedComment(worksheet, "C2", "4", author2);
        addThreadedComment(worksheet, "C2", "5", author1);
        addThreadedComment(worksheet, "C2", "6", author1);
        addThreadedComment(worksheet, "C2", "7", author1);
        addThreadedComment(worksheet, "C2", "8", author1);
        addThreadedComment(worksheet, "C2", "9", author1);
        addThreadedComment(worksheet, "C2", "10", author2);
        addThreadedComment(worksheet, "C2", "11", author1);
        addThreadedComment(worksheet, "C2", "12", author2);

        workbook.save("output.xlsx");
    }

    private static void addThreadedComment(Worksheet worksheet, String cellName, String text, ThreadedCommentAuthor author) {
        Cell cell = worksheet.getCells().get(cellName);
        CommentCollection commentCollection = cell.getWorksheet().getComments();
        Comment comment = commentCollection.get(cell.getRow(), cell.getColumn());
        if (comment == null) {
            int commentIdx = commentCollection.add(cell.getRow(), cell.getColumn());
            comment = commentCollection.get(commentIdx);
        }
        comment.getThreadedComments().add(text, author);
    }
}

@VasiliyKupchinskiy,

Thanks for the sample code segment and screenshot.

Please try our latest version/fix: Aspose.Cells for Java v20.1.7 (attached)
aspose-cells-20.1.7-java.zip (6.8 MB)

If you still find the issue with 20.1.7, kindly do share your output file (you may zip it prior attaching) and screenshot to highlight the issue, we will log a ticket to figure it out soon.

With 20.1.7 issue not resolved too. Comments order changed after import to OneDrive:
image.png (33.4 KB)

Output xlsx file from code snippet:
output.zip (8.1 KB)

@VasiliyKupchinskiy,

Thanks for the screenshot and output file.

Please notice I have logged a ticket with an id “CELLSJAVA-43122” for your issue. We will look into it to figure it out soon. The issue is logged as following:
CELLSJAVA-43122 - Issue with Threaded comments’ order after import to Office365 XLSX file format

Once we have an update on it, we will let you know.

1 Like

@VasiliyKupchinskiy,

Please try our latest version/fix: Aspose.Cells for Java v20.1.9 (attached)

Your issue should be fixed in it.

Let us know your feedback.
aspose-cells-20.1.9-java.zip (6.8 MB)

:confused:
I made about 10 attempts and got 1 success only (correct order) after import to OneDrive.

@VasiliyKupchinskiy,
We have observed the issue and re-opened the ticket for analysis. We will write back here when feedback is ready to share.

@VasiliyKupchinskiy,
Could you please share a template file with ordered threaded comments?

Sorry for late. I lost old templates, but reproduced with 3 attempts.

Generated xlsx files using aspose-cells-20.1.9-java.zip:
Archive.zip (8.7 KB)

After upload to OneDrive I got only “latest” (output_Tue Mar 03 15_10_29 KRAT 2020.xlsx) imported file with correct order, another 2 files imported with bad order.

For make sure how I see imported files you can check my OneDrive folder where imported files are located: Error

@VasiliyKupchinskiy,

Thanks for the file with correct order.

We will look into it soon.

Once we have an update on it, we will let you know.

@VasiliyKupchinskiy,

This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43122”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@VasiliyKupchinskiy,

Please try our latest version/fix: Aspose.Cells for Java v20.2.4:
aspose-cells-20.2.4-java.zip (7.0 MB)

Your issue should be fixed in it.

Let us know your feedback.

It works, thank you. Also, what about to set date of threaded comment (or maybe “empty” variant without any date)?

@VasiliyKupchinskiy,

Could you provide more details with sample file(s), screenshots (current output and expected output) and current code for your requirements, we will check it soon.

We want to specify custom date for every threaded comment.
For example we have a comments for cell like this:
image.png (23.9 KB)
(Pay attention on dates)

What we want:
image.png (78.4 KB)
(Pay attention on dates, it equals to dates from first screenshot)

So, we want to specify custom dates when adding a ThreadedComment. Another variant is not show any dates if possible (but save comments order in result file).

Code snippet for clarify

import com.aspose.cells.*;

import java.text.SimpleDateFormat;
import java.util.Date;

public class AsposeCellsThreadedCommentsDate {

public static void main(String[] args) throws Exception {
    License license = new License();
    license.setLicense("aspose.lic");

    Workbook workbook = new Workbook();
    Worksheet worksheet = workbook.getWorksheets().get(0);
    int author1Index = workbook.getWorksheets().getThreadedCommentAuthors()
                               .add("Author 1", "author1", "OV");

    ThreadedCommentAuthor author1 = workbook.getWorksheets().getThreadedCommentAuthors().get(author1Index);

    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    addThreadedComment(worksheet, "C2", "1", author1, dateFormat.parse("2020-03-09 10:11:33"));
    addThreadedComment(worksheet, "C2", "2", author1, dateFormat.parse("2020-03-10 10:11:36"));
    addThreadedComment(worksheet, "C2", "3", author1, dateFormat.parse("2020-03-11 10:11:39"));
    addThreadedComment(worksheet, "C2", "4", author1, dateFormat.parse("2020-03-11 10:11:45"));

    workbook.save("output_" + new Date().toString().replace(":", "_") + ".xlsx");
}

private static void addThreadedComment(Worksheet worksheet, String cellName, String text, ThreadedCommentAuthor author,
                                       Date date) {
    Cell cell = worksheet.getCells().get(cellName);
    CommentCollection commentCollection = cell.getWorksheet().getComments();
    Comment comment = commentCollection.get(cell.getRow(), cell.getColumn());
    if (comment == null) {
        int commentIdx = commentCollection.add(cell.getRow(), cell.getColumn());
        comment = commentCollection.get(commentIdx);
    }
    comment.getThreadedComments().add(text, author, date); // date ?
}

}

@VasiliyKupchinskiy,

Thanks for the screenshots and code segment.

Please also provide your expected Excel file with threaded comments that you can create in Office 365 manually, we will look into it.

@VasiliyKupchinskiy,

We evaluated your requirements further and we found you need not really to post your expected file as we understand your needs. We can support your demanded feature. The following ticket is logged for your new requirements:

CELLSJAVA-43141 - Add ThreadedComment.CreatedTime property

Once we have an update on it, we will let you know.

@VasiliyKupchinskiy,
Please try our latest version/fix: Aspose.Cells for Java v20.2.6:
aspose-cells-20.2.6-java.zip (7.0 MB)

Your issue should be fixed in it.

Let us know your feedback.

Works good, thank you very much!

@VasiliyKupchinskiy,
You are welcome.