We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

WorkSheet.AutoFitColumns() performance

Aspose.Cells Product Family
#1

I'm wondering if the performance of the AutoFitColumns() method of hte WorkSheet object can be increased or if there is a work around to doing the autofit of columns faster.

I'm exporting about 40 thousand rows x 20 columns. Preparing the data (creating a data table, and importing from that data table plus adding ranges and styles for formatting) takes about 12-17 seconds. However, if I add a call to AutoFitColumns() then the process takes about 50-60 seconds, or nearly 4X slower. That is a pretty massive hit. I have profiled the application by using the instrumentation tools in visual studio and my only remaining bottle neck is the AutoFitColumns call.

Any suggestion is welcomed. Thanks.

#2

Hi,

Well, AutoFitColumns() is time consuming method which might take some time to auto-fit columns if you have lots of rows and columns in a worksheet. Could you try the attached version if it improves the performance to certain extent.

By the way, you may use AutoFitColumn() overloaded version of the method if you need some selective columns to be auto-fitted, it might improve the performance too.

If you still could not evaluate, kindly post your template file, we need to try the auto-fit operation upon your template file.

Thank you.

#3

Thanks Amjad. I wasn't able to use the file you posted since my license is an older version. I have created a sample data file for the purposes of testing. The file is an xml table export. I load this file using the readxml method of the datatable object as shown. then proceed to export it. I compressed the file as zip and attached it.

DataTable sourceTable = new DataTable();
sourceTable.ReadXml(@"C:\sample.xml");
Workbook newWorkBook = new Workbook();
Worksheet firstWorksheet = newWorkBook.Worksheets[0];
firstWorksheet.Cells.ImportDataTable(sourceTable, true, "A1");
DateTime startTime = DateTime.Now;


firstWorksheet.AutoFitColumns();


newWorkBook.Worksheets.ActiveSheetIndex = 1;
string outputFilePath = @"C:\" + System.IO.Path.ChangeExtension(System.IO.Path.GetRandomFileName(),"xls");
newWorkBook.Save(outputFilePath, FileFormatType.Excel2003);

DateTime endTime = DateTime.Now;

MessageBox.Show("File saved to: " + outputFilePath + "\n" + "Total Time (s): " + (endTime - startTime).TotalSeconds.ToString());

Thanks.
Leonardo.

#4

Hi,

Well, I tried your sample code with your xml file, it takes only 3 or 4 seconds to complete the task on my normal configured pc (i.e. Core 2 Due 2.00MHz, 2MB RAM).

Please comment out your licensing code and try my attached version to check completion time.

In any case to use our latest version/fix with its full capacity, you need to upgrade your subscription. You may contact Aspose.Purchase forum for further details.

Thank you.

#5

Thanks Amjad.

I comented the license code and tried your .dll. It is a lot faster. I will try a little more testing with our test systems and if the improvement is consistent we will upgrade.

Regards,

Leonardo.