Performance Excel Export

[KA]

Hi,

 I have Performance Issues with the Excel spreadprocessing export. To populate a 1000x1000 matrix takes 70 seconds.

Can you please have a look? Thank you!

 

namespace WpfApplication1
{
    public partial class MainWindow : Window
    {
        private readonly int SIZE = 1000;

        public MainWindow()
        {
            
            InitializeComponent();

            WorkbookFormatProvidersManager.RegisterFormatProvider(new XlsxFormatProvider());
            test();

        }

        public object[,] populateData()
        {
           
            var res = new object[SIZE,SIZE];


            for(int row=0;row<SIZE;row++)
                for(int col=0;col<SIZE;col++)
                    res[row,col] = row+col;

            return res;

        }

        public void test()
        {
            Stopwatch s = Stopwatch.StartNew();
            var sensorDataTable = populateData();
           
            s.Stop();
            Console.WriteLine(string.Format("populate {0} items: {1}ms", SIZE*SIZE, s.ElapsedMilliseconds));

            s = Stopwatch.StartNew();
            Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook = new Telerik.Windows.Documents.Spreadsheet.Model.Workbook();
            using (new UpdateScope(workbook.SuspendLayoutUpdate, workbook.ResumeLayoutUpdate))
            {
                using (new UpdateScope(
                    () => { workbook.History.IsEnabled = false; },
                    () => { workbook.History.IsEnabled = true; }))
                {
                    workbook.Sheets.Add(SheetType.Worksheet);
                    Telerik.Windows.Documents.Spreadsheet.Model.Worksheet worksheet = workbook.ActiveWorksheet;

                    for(int row=0;row<SIZE;row++)
                        for(int col=0;col<SIZE;col++)
                            worksheet.Cells[row, col].SetValue(sensorDataTable[row, col].ToString());

                    worksheet.Columns[worksheet.UsedCellRange].AutoFitWidth();
                }
            }

            String filename = @"c:\test.xlsx";

            IWorkbookFormatProvider formatProvider = WorkbookFormatProvidersManager.GetProviderByName("XlsxFormatProvider");
            using (var stream = new FileStream(filename, FileMode.CreateNew, FileAccess.ReadWrite))
            {
                formatProvider.Export(workbook, stream);
            }
            s.Stop();
            Console.WriteLine(string.Format("create and save excel: {0}ms", s.ElapsedMilliseconds));
        }

    }
}

[Nikolay Demirev]

Hi Kumar,

I have tested your code and there are two things I can suggest you that will improve the performance of your application.

1. If you know the exact type of the value you want to set to a cell use the SetValue overload which fit the desired CLR data type. Here you can find more information why you have to avoid setting string values.
2. If you do not really need to Auto Fit the columns do not perform that action, because it is slow operation. You could use a constant column width which will fit your expected values.

On my end the code you have sent takes 22.5 s to run. When I changed populateData method to return int instead of object and removed the ToString() while setting value the time needed to execute the code was 16,5 s. After removing the AutoFitWidth() method call the time dropped to 10 s.

Regards,
Nikolay Demirev
Telerik