Performance Excel Export

2 posts, 0 answers
  1. KA
    KA avatar
    7 posts
    Member since:
    Jan 2013

    Posted 05 Apr 2015 Link to this post


     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()

                WorkbookFormatProvidersManager.RegisterFormatProvider(new XlsxFormatProvider());


            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();
                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; }))
                        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());


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

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


  2. Nikolay Demirev
    Nikolay Demirev avatar
    136 posts

    Posted 06 Apr 2015 Link to this post

    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.

    Nikolay Demirev

    See What's Next in App Development. Register for TelerikNEXT.

Back to Top