The AutoFitColumns() method is extremely slow when working on a worksheet with a significant amount of data. I wrote a short sample console program in VB to reproduce the problem. Here’s the output I get when I run it:
Getting DataTable…
Done! (20 columns, 25000 rows, 15 characters per cell)
AutoFitting columns…
Done! 22.268 seconds
Press any key to continue . . .
In Excel 2003, if I run “Format/Column/AutoFit” on a worksheet with the exact same data, the operation completes in like half a second. 22.26 seconds vs. 0.5 seconds is a very big performance gap. Here’s the code:
Imports Aspose.Cells
Imports System.IO
Module AutoFitColumnsDemo
Const _numOfRows As Integer = 25000
Const _numOfColumns As Integer = 20
Const _cellContents As String = “XXXXXXXXXXXXXXX”
Sub Main()
’ set Aspose Cells license
Dim asposeCellsLicense As New Aspose.Cells.License
asposeCellsLicense.SetLicense(New MemoryStream(My.Resources.Aspose_Total_License))
’ generate sample datatable
Console.WriteLine(String.Empty)
Console.WriteLine(“Getting DataTable…”)
Dim dt As DataTable = GetSampleData()
Console.WriteLine(String.Format(“Done! ({0} columns, {1} rows, {2} characters per cell)”, dt.Columns.Count, dt.Rows.Count, _cellContents.Length))
Console.WriteLine(String.Empty)
’ paste data onto worksheet
Dim wb As New Aspose.Cells.Workbook
wb.Worksheets(0).Cells.ImportDataTable(dt, True, “A1”)
Dim stopWatch As New Stopwatch
Console.WriteLine(“AutoFitting columns…”)
stopWatch.Reset()
stopWatch.Start()
wb.Worksheets(0).AutoFitColumns()
stopWatch.Stop()
Console.WriteLine(String.Format(“Done! {0} seconds”, stopWatch.ElapsedMilliseconds / 1000))
Console.WriteLine(String.Empty)
Console.Write(“Press any key to continue . . .”)
Console.ReadKey(True)
End Sub
‘’’
‘’’ creates and returns a datatable with sample data
‘’’
Function GetSampleData() As DataTable
Dim dt As New DataTable
For c As Integer = 0 To _numOfColumns - 1
dt.Columns.Add(“column” & c)
Next
For rowCount As Integer = 0 To _numOfRows - 1
Dim dr As DataRow = dt.Rows.Add
For i As Integer = 0 To _numOfColumns - 1
dr.Item(i) = _cellContents
Next
Next
Return dt
End Function
End Module
I’ve also attached an Excel 2007 spreadsheet that contains the same data that would be generated by the sample program. The columns haven’t been autofit yet, so you can open it up in Excel and compare the speeds of Excel’s column autofitting and Aspose.Cell’s AutoFitColumns.