This project has moved and is read-only. For the latest updates, please go here.

very slow any ideas to speed up

Jan 27, 2015 at 1:34 PM
A Data Table of 3000 rows takes almost 10 seconds a row. This means the job will take over 8 hours to run. I really like this tool but this performance hit is killing me. Any ideas to speed it up.

Here is my dump routine.
private static void CreateData(ExcelWorksheet workSheet, ref int rowIndex, DataTable dt)
        {
            int colIndex = 0;
            int intNumberCheck;

            foreach (DataRow dr in dt.Rows) // Adding Data into rows
            {
                colIndex = 1;                
                rowIndex++;
                               
                foreach (DataColumn dc in dt.Columns)
                {                    
                    var cell = workSheet.Cells[rowIndex, colIndex];                    

                    bool isNumber = Int32.TryParse(dr[dc.ColumnName].ToString(), out intNumberCheck);

                    if (isNumber == true)
                    {
                        //Setting Value in cell
                        cell.Value = Convert.ToInt32(dr[dc.ColumnName]);
                    }
                    else
                    {
                        //Setting Value in cell
                        cell.Value = Convert.ToString(dr[dc.ColumnName]);
                    }

                    //Setting borders of cell
                    var border = cell.Style.Border;
                    border.Left.Style = border.Right.Style = ExcelBorderStyle.None;

                    workSheet.Cells.AutoFitColumns(colIndex);
                    cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

                    colIndex++;                                       

                }
            }
        }
Jan 29, 2015 at 6:57 AM
I would recommend to use LoadFromDataTable to import the datatable to a worksheet. Maybe you can also directly use a TableStyle during LoadFromDataTable instead of setting borders manually?
You can call AutoFitColumns on all columns (workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns()) after you loaded the DataTable.
I would also recommend to format complete columns as Numbers after you importet the datatable into the worksheet. That should also speed it up a lot.
Jan 29, 2015 at 11:36 AM
I think your major issue is call AutoFit in each step, if you do that your algorithm will be a O(4). Call after load all your cells and it will be O(2).
Jan 29, 2015 at 1:57 PM
Thanks so much.
I took out the formatting and now the loop is incredibly fast.
private static void CreateData(ExcelWorksheet workSheet, ref int rowIndex, DataTable dt)
        {
            int colIndex = 0;
            int intNumberCheck;

            foreach (DataRow dr in dt.Rows) // Adding Data into rows
            {
                colIndex = 1;                
                rowIndex++;
                                
                foreach (DataColumn dc in dt.Columns)
                {                    
                    var cell = workSheet.Cells[rowIndex, colIndex];                    

                    bool isNumber = Int32.TryParse(dr[dc.ColumnName].ToString(), out intNumberCheck);

                    if (isNumber == true)
                    {
                        //Setting Value in cell
                        cell.Value = Convert.ToInt32(dr[dc.ColumnName]);
                    }
                    else
                    {
                        //Setting Value in cell
                        cell.Value = Convert.ToString(dr[dc.ColumnName]);
                    }                   

                    colIndex++;                                       

                }
            }
        }