Writing comments to Excel worksheet using Aspose.Cells for Java slow

Aspose.Cells Product Family
1

I am evaluating Aspose.Cells for Java, and the program I have developed is working fine. There is one issue: I am creating a ~26000-row, 22-column spreadsheet with various formatting (font color), and many of the cells have comments written to them.

The writing of the comments seems to be very slow. The program runs in 1 hour, 8 minutes. If I simply comment out the line that writes the comment to the cell (the comment.setNote call), the program runs in 12 seconds.

Are there plans to make this more efficient? Or is there something that can be done to speed up comment writes? I am writing to the spreadsheet, as suggested in the documentation, row-by-row for performance optimization.

Thank you very much for your time.

-- Sean

This message was posted using Aspose.Live 2 Forum
2

Hi Sean,

We have noticed the deterioration in terms of performance when adding lots of comments. We will enhance it.

Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-13821.

We will update you when it is sorted out.

Thank you.

3

Hi,


When adding a Comment to a cell, it is needed to check whether there has been an existing Comment for this cell. When there are a large amount of Comments, this check will cost some time. Please try the attached version. Now we have added an overload method for user to avoid such check:

Comment com.aspose.cells.Shapes.addComment(int row, int column, boolean checkExisted, byte placementType)

Parameters:

row row index

column column index

checkExisted whether to check existing Comments to check whether there has been an existing Comment object with same row and column. If there are large amount of Comments and user can be sure that there is no existing Comment at given row and column, disable this flag can improve performance to certain extent.

placementType the placement type of Comment. Setting it to PlacementType.MOVE will get best performance.

Returns:

Comment Object.

For your situation, I think you can invoke this method like following to improve the performance:

for(int i=0; i<26000; i++)
{
for(int j=0; j<22; j++)
{

shapes.addComment(i,j,false,PlacementType.MOVE).setNote("…");
//disable the check and set placement as MOVE.

}

}<o:p></o:p>


Thank you.

Thank you.

4

Thank you very much for your quick reply.

I installed the modified version and changed the addComment call as you suggessted.

It did run faster, technically. The run time went from 1 hour, 8 minutes down to 1 hour, 2 minutes. Commenting out the code that writes the comments to the sheet, the code runs in 12 - 13 seconds.

The part of my code that writes the comments is given here:

if (CommentData[row][column] != null) {
Comment comment = worksheet.getShapes().addComment(row,column,false,PlacementType.MOVE);
Font font = comment.getFont();
font.setSize(10);
comment.setNote(CommentData[row][column]);
comment.setWidth(CommentWidth[row][column] * 7);
comment.setHeight(CommentHeight[row][column] * 17);
comment.setFont(font);
}

where CommentData[row][column] is processed and assigned elsewhere in the code. This whole block is essentially in a nested for…next loop such that it is processed row-by-row and not column-by-column.

I would normally figure I that wrote inefficient code, but, as I stated, if I comment out the section of code listed above (comments get processed and assigned but not written to the worksheet), the code runs in 12 - 13 seconds as opposed to over an hour otherwise. It appears as though writing the comments is the bottleneck.

Any other ideas? Is there a more efficient way to code the comment writes than that which I have listed above?

Again, thank you very much for your time.


5

One thing I forgot to mention. With the updated version, I can open the resulting .xls file in Excel just fine, but Excel crashes when I exit (this did not happen before), and I have to go into the Task Manager and kill the Excel.exe process.

Thanks!


6

Hi,

We have improved the performance of searching existing comments, so, we have removed the "checkExisted" parameter in v2.1.1.22 and the method now has been changed to:

Comment com.aspose.cells.Shapes.addComment(int row, int column, byte placementType)

Please try the new fix (attached). For the generated file, if it still causes Excel crashed, I am afraid, we need your sample project to trace the issue because by our test the generated file can be opened and closed by Excel fine.

Here is my test code and the resultant file is also attached.

Sample code:

Workbook workbook = new Workbook();

Worksheets worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.getSheet(0);

Cells cells = worksheet.getCells();
Shapes shapes = worksheet.getShapes();
long time = System.currentTimeMillis();
long timetotal = time;

for(int i=0; i<20000; i++)
{
Row row = cells.getRow(i);
for(int j=0; j<22; j++)
{
Cell cell = row.getCell(j);
Comment comment = shapes.addComment(i,j,PlacementType.MOVE);

Font font = comment.getFont();
font.setSize(10);
comment.setNote(i+"-"+j);
comment.setWidth(30);
comment.setHeight(40);
comment.setFont(font);
}

if(i%100 == 0)
{
System.out.println("finish " + i);
System.out.println("time " + (System.currentTimeMillis() - time));
time = System.currentTimeMillis();
}
}

System.out.println("total time " + (System.currentTimeMillis() - timetotal));
timetotal = System.currentTimeMillis();
workbook.save("d:\\Files\\outComments.xls");

Thank you.


7

Once again, thank you very much for your quick reply.

I loaded in the v.2.1.1.23 version, re-compiled, and re-ran my program. It still runs in 1 hour, 2 minutes (no change from v2.1.1.22), but the Excel crashing problem has disappeared.


8

If you wish, I can send you my java code and input files so you can take a look at the live case.

Thanks!

9

Hi,

It will be much better for us to trace the issue if you can provide us your template file and code. Before that, would you please check whether you have given enough memory to JVM to execute your program? When program runs to the memory limit, most of time will be used for GC and application's progress will become much slower. By our test, to create 20000x22 Comments, it needs at least 950M memory. When give less memory to JVM, the progress will be very slow and finally maybe cause OutOfMemory error.

Thank you.

10

The memory available to the JVM is 2GB, I believe.

I re-ran the program with the -Xmx2000m option, giving the JVM 2GB of memory.

The program ran in one hour (two minutes less than without the -Xmx option).

Please let me know the e-mail address to which I can send my program and input files, and I’ll be happy to do so.

Thank you very much!

11

Hi,

To email please follow these under mentioned steps,

1: click the Contact button in the Post, such as one of Amjad's reply: https://forum.aspose.com/t/141183.

2: In the drop down list options click "Send Amjad Sahi an Email”.

3: Attach the template file and send it (you may zip the file prior attaching it).

4: Once you have done it, kindly confirm us on this thread.

We will check you issue(s) soon.

Thank you

12

Hi,

We have not received your template file and sample code yet.

But could you try the new fix v2.1.1.26 (attached) instead of v2.1.1.23 as we have fixed a
known bug in the older fix v2.1.1.23.


If you still find the issue, kindly post your sample file with code via mail (as we suggested in previous reply) to us, we will check your issue asap.


Thank you.
<o:p></o:p>


13

Hi Sean,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for your test code and template files.

After check your code, we found the performance issue has nothing to do with creating Comments but is caused due to Worksheet.autoFitColumn() operation. When the cells set is large, auto-fit operation needs much time. For your situation, because there are few custom styles and data types, we think you can use an alternative and simpler procedure to process column width if auto-fit operation is really needed. Please see the attached code that has been modified based on your original code. Please look into the codes with heading comment like "/*******************************ASPOSE: "

In the code we get the maximum text of cell value (simply by the text length) in every column and take it's width as the auto-fit width of this column. The column width got by this way may be not so accurate because text width is not determined by its length only, but it does save much more time when removing many other complex operations such as Alignment, rotation, row height for every cell/value in the large cells set.

Thank You & Best Regards,

14

Thank you for your time.

I’ll look into it, but what you are saying doesn’t make sense. As I said above, if I comment out the section that writes the comments to the cells, leaving in the column auto-fits, the 2-hour runtime goes down to 12 - 13 seconds. That indicates that the bottleneck is with the comments, not the auto-fits.

Please try that with the code I gave you – comment out the if block where the comments are written and re-compile/re-run the program, and you will see the same results.

I will try as you suggested, but our eval license ran out last Friday. Can it be extended?

Again, thank you very much for your time.


– Sean

15

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We will further look into your issue and get back to you soon.

seanpo:
I will try as you suggested, but our eval license ran out last Friday. Can it be extended?

For extension in Evaluation License, you may contact our Sales team by using the following forum link:

http://www.aspose.com/community/forums/aspose.purchase/220/showforum.aspx

Thank You & Best Regards,

16

Wow! You’re right! I commented out the for loop that auto-fit the colums and left in the comment writes, and it ran in 15 seconds! Amazing!

This must mean that auto-fitting a column must go through and process comments since, if I leave out the comment writes, but there is just as much cell data, the program runs in 12 - 13 seconds.

Is there a way the auto-fit can bypass checking comments in the columns?

Thank you very much! This is great!

17

One last thing. Excel is once again crashing upon closing of the generated .xls file with the latest version of Aspose.Cells you sent (2.1.1.26).

Can this be fixed again?

Thanks!


– Sean

18

Wow! You’re right! I commented out the for loop that auto-fit the
colums and left in the comment writes, and it ran in 15 seconds!
Amazing!

This
must mean that auto-fitting a column must go through and process
comments since, if I leave out the comment writes, but there is just as
much cell data, the program runs in 12 - 13 seconds.

Is there a way the auto-fit can bypass checking comments in the columns?

Thank you very much! This is great!

One last thing. Excel is once again crashing upon closing of the
generated .xls file with the latest version of Aspose.Cells you sent
(2.1.1.26).

Can this be fixed again?

Thanks!


– Sean

19

Hi,

Thank you for considering Aspose.

We will look into your issue and get back to you soon.

Thank You & Best Regards,

20

The issues you have found earlier (filed as 13821) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.