I’m calling Worksheet.AutoFitRows() on some worksheets that can have a very large number of rows (over 100,000). I know that this is a complex operation and will likely take a while to complete.
However, I’m seeing performance even worse than I expect. When a call AutoFitRows() on worksheets of different sizes, I see times like this:
| Rows |
Seconds |
| 1000 |
1.579 |
| 2000 |
3.5 |
| 2500 |
4.9 |
| 3000 |
6.57 |
| 4000 |
11.6755 |
| 5000 |
23.02 |
| 6000 |
40.89 |
| 7000 |
62.37 |
| 8000 |
84.24 |
If you graph this, you'll see that the time is increasing exponentially (2 ^ n) with the number of rows. I would expect the time to scale linearly with the number of rows.
Here's another way of looking at this. I timed how long it took to call AutoFitRows on the first 1000 rows of my worksheet (rows 0 - 999) - That takes 7 seconds. I then timed how long it took to call AutoFitRows on the last 1000: (rows 113417 - 114416). It took 29 seconds. My data is quite similar throughout the worksheet, so I would expect the time to be about the same no matter which 1000 rows I AutoFit. Instead, I see that the farther down the worksheet, the longer it takes to AutoFit.
Also, if I skip the AutoFitting in Aspose.Cells and instead load the file into Excel 2010, then I can AutoFit all 114417 rows in about 4 seconds. Aspose.Cells will take about 30 minutes.
Can you explain why AutoFitRows is being so slow, not scaling linearly, and taking 1000's of times longer than Excel? Are there some optimizations I can make to make this scale better?
Thanks.
Hi !
Please share your demo project to us, we will check it soon.
Thanks!
Attaching a project that demonstrates the problem. When I run this demo, I find that the problem is slightly different than I first reported - it’s reversed in this example. The lines at the beginning of the file now take longer to AutoFit than those at the end of the file (whereas in my real program, the ones at the end take longer). But the problem is still the same.
Here are the results of running the attached program on my machine:
Total Rows: 114418
First 1000 (0 - 999): 15.1625842
Last 1000 (113419 - 114418): 1.7900156
(0 - 999): 15.1323332
(1000 - 1999): 15.0704874
(2000 - 2999): 14.8659584
(3000 - 3999): 14.7335693
(4000 - 4999): 14.6139108
(5000 - 5999): 14.5098819
(6000 - 6999): 14.4035138
(7000 - 7999): 14.3988976
(8000 - 8999): 14.1649353
(9000 - 9999): 14.0619759
(10000 - 10999): 13.9438015
(11000 - 11999): 13.8241437
(12000 - 12999): 13.7086504
(13000 - 13999): 13.6082279
(14000 - 14999): 13.4731259
(15000 - 15999): 13.3708885
(16000 - 16999): 13.2566115
(17000 - 17999): 13.1420972
(18000 - 18999): 13.0299477
(19000 - 19999): 12.8960895
(20000 - 20999): 12.8276546
(21000 - 21999): 12.7240252
(22000 - 22999): 12.578465
(23000 - 23999): 12.4793765
(24000 - 24999): 12.3215798
(25000 - 25999): 12.2104991
(26000 - 26999): 12.110414
(27000 - 27999): 11.9990304
(28000 - 28999): 11.8990068
(29000 - 29999): 11.7491633
(30000 - 30999): 11.6500574
(31000 - 31999): 11.5238154
(32000 - 32999): 11.4224302
(33000 - 33999): 11.2896473
(34000 - 34999): 11.1778482
(35000 - 35999): 11.0487333
(36000 - 36999): 10.9508419
(37000 - 37999): 10.8189181
(38000 - 38999): 10.6910619
(39000 - 39999): 10.578728
(40000 - 40999): 10.4386293
(41000 - 41999): 10.3494244
(42000 - 42999): 10.237168
(43000 - 43999): 10.0921367
(44000 - 44999): 9.9657464
(45000 - 45999): 9.8953931
(46000 - 46999): 9.7274476
(47000 - 47999): 9.6219557
(48000 - 48999): 9.4968455
(49000 - 49999): 9.3713183
(50000 - 50999): 9.260706
(51000 - 51999): 9.1450233
(52000 - 52999): 9.0156226
(53000 - 53999): 8.9038383
(54000 - 54999): 8.7904102
(55000 - 55999): 8.7152869
(56000 - 56999): 8.5511309
(57000 - 57999): 8.4265084
(58000 - 58999): 8.3256555
(59000 - 59999): 8.2045891
(60000 - 60999): 8.1159229
(61000 - 61999): 7.9629421
(62000 - 62999): 7.8444003
(63000 - 63999): 7.7321113
(64000 - 64999): 7.5949383
(65000 - 65999): 7.48245
(66000 - 66999): 7.378483
(67000 - 67999): 7.2514355
(68000 - 68999): 7.1305365
(69000 - 69999): 7.0409592
(70000 - 70999): 6.9021303
(71000 - 71999): 6.7841766
(72000 - 72999): 6.6705079
(73000 - 73999): 6.5498132
(74000 - 74999): 6.4334529
(75000 - 75999): 6.3059757
(76000 - 76999): 6.1804391
(77000 - 77999): 6.071387
(78000 - 78999): 5.9557162
(79000 - 79999): 5.8260249
(80000 - 80999): 5.7250792
(81000 - 81999): 5.6322436
(82000 - 82999): 5.4813468
(83000 - 83999): 5.3595815
(84000 - 84999): 5.2440773
(85000 - 85999): 5.1226783
(86000 - 86999): 5.0091998
(87000 - 87999): 4.8870407
(88000 - 88999): 4.7853425
(89000 - 89999): 4.6540179
(90000 - 90999): 4.5449904
(91000 - 91999): 4.4210451
(92000 - 92999): 4.3172683
(93000 - 93999): 4.1879966
(94000 - 94999): 4.0716388
(95000 - 95999): 3.9506452
(96000 - 96999): 3.8396548
(97000 - 97999): 3.7309719
(98000 - 98999): 3.6055823
(99000 - 99999): 3.5222121
(100000 - 100999): 3.3800167
(101000 - 101999): 3.2564115
(102000 - 102999): 3.1430251
(103000 - 103999): 3.0248521
(104000 - 104999): 2.8965496
(105000 - 105999): 2.7847624
(106000 - 106999): 2.6651308
(107000 - 107999): 2.5565493
(108000 - 108999): 2.4279794
(109000 - 109999): 2.3156853
(110000 - 110999): 2.1997968
(111000 - 111999): 2.0888664
(112000 - 112999): 1.9605773
(113000 - 113999): 1.839833
(114000 - 114999): 0.7364638
Total Rows: 10000
Autofit 10000 rows: 7.091145
Autofit 9000 rows: 6.2600387
Autofit 8000 rows: 4.8268875
Autofit 7000 rows: 4.0566263
Autofit 6000 rows: 2.9613143
Autofit 5000 rows: 2.303967
Autofit 4000 rows: 1.5560943
Autofit 3000 rows: 1.0305158
Autofit 2000 rows: 0.5613438
Autofit 1000 rows: 0.2319365
Autofit 0 rows: 3E-07
Hi,
Thanks for your project.We have found the issue.
AutoFit must ignore the merged cells in MS Excel.
There are too many merged cells in the template file.
It works slow that checking whether the cell is merged.
We will improve it.
I downloaded and tried version 7.0.2.7, and it makes AutoFitRows work much, much faster. Thanks very much for that.
Unfortunately, version 7.0.2.7 seems to have a different bug. If I create a file with more than the 65536 that an xls file supports, then save as a Excel97To2003 format file, then when I open in Excel, I see an error: “Excel found unreadable content”.
This same test works perfectly if I use the 7.0.2.0 release. (Excel shows the first 65536 rows, as expected)
Here’s the code I used to create the file:
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);
<span style="color:#678cb1;">Workbook</span> wb <span style="color:#e8e2b7;">=</span> <span style="color:#93c763;">new</span> <span style="color:#678cb1;">Workbook</span>();
<span style="color:#678cb1;">Worksheet</span> ws <span style="color:#e8e2b7;">=</span> wb<span style="color:#e8e2b7;">.</span>Worksheets[<span style="color:#ffcd22;">0</span>];
<span style="color:#93c763;">for</span> (<span style="color:#93c763;">int</span> ii <span style="color:#e8e2b7;">=</span> <span style="color:#ffcd22;">1</span>; ii <span style="color:#e8e2b7;"><</span> <span style="color:#ffcd22;">80000</span>; ii<span style="color:#e8e2b7;">++</span>)
{
<span style="color:#678cb1;">Cell</span> c <span style="color:#e8e2b7;">=</span> ws<span style="color:#e8e2b7;">.</span>Cells[ii, <span style="color:#ffcd22;">1</span>];
c<span style="color:#e8e2b7;">.</span>PutValue(<span style="color:#ffcd22;">1</span>);
}
wb<span style="color:#e8e2b7;">.</span>Save(<span style="color:#efc210;">@"d:\TEMP\output80000.xls"</span>, <span style="color:#678cb1;">FileFormatType</span><span style="color:#e8e2b7;">.</span>Excel97To2003);
This does appear unrelated to the AutoFitRows fix, so let me know if you’d like me to start a new thread.
Hi,
We have fixed this issue. Please download: Aspose.Cells for .NET v7.0.3.0