This project has moved. For the latest updates, please go here.

Loading DataTables with Formulas, empty cells and values

Oct 2, 2013 at 6:57 PM
Edited Oct 2, 2013 at 6:58 PM
Hi. I am loading large Data Tables (19269 rows and 6 columns,1624 rows and 146 columns) using LoadFromDataTable. The performance is great. My problem is that my data has values, empty cells and formulas.

The workbook I generate is formatted by a 3rd party software when I open it in Excel. I found that I need to do the following to fix the worksheet:
for (int col = 1; col <= ColNum; col++) 
                    for (int row = 1; row <= RowNum; row++)
                        using (ExcelRange exrange = worksheet.Cells[row, col])
                            if (string.IsNullOrEmpty(exrange.Text) || string.IsNullOrWhiteSpace(exrange.Text))
                            else if (exrange.Text.StartsWith("="))
                                exrange.Formula = exrange.Text;
This is very slow (8 min for the 1624 rows and 146 columns table). Any suggestions on how to improve this?

Thank you!
Oct 2, 2013 at 7:17 PM
From Faq...

I have a lot of data I want to load. What should I think of to get the best performance?
If you have more than, let's say 5 000 cells in the sheet and experience performance problems, have this in mind...
Always load cells from top to bottom left to right. That means that if you add a formula on the data you load, it's faster to add it for every row than to add it for a range after you have loaded the data (for ex. this will take a while,Worksheet.Cells"C1:C100000".Formula="A1+B1").
If you want to use a shared formula, like the sample, make sure you create the cells in sequence (use something like worksheet.Cellsrow,forumlaColumn.Value=null to create the cell. Then you can add the formula after you have finished loading your data.
Avoid using the InsertRow and DeleteRow methods.

Take a look at this thread too...

Maybe you can make somes change in loadfromdatable.