Larger Data Writes in Excel File Faster

  1. using Excel = Microsoft.Office.Interop.Excel;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Diagnostics;  
  5. using System.Linq;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8. using System.Data;  
  9.   
  10.   
  11. namespace LargeDataWriteInExcel   
  12. {  
  13.   
  14.     class Program   
  15.     {  
  16.         private static DataTable GetResult()   
  17.         {  
  18.             //get data from db.  
  19.             DataTable dt = new DataTable();  
  20.             dt.Columns.Add("Sl.No", typeof(int));  
  21.             dt.Columns.Add("Name", typeof(string));  
  22.             dt.Columns.Add("Age", typeof(int));  
  23.             for (int rowIndex = 0; rowIndex < 50000; rowIndex++)   
  24.             {  
  25.                 var Row = dt.NewRow();  
  26.                 Row["Sl.No"] = rowIndex + 1;  
  27.                 Row["Name"] = "Abrar " + rowIndex + 1;  
  28.                 Row["Age"] = 25;  
  29.                 dt.Rows.Add(Row);  
  30.             }  
  31.             return dt;  
  32.         }  
  33.         private static Tuple < Object[, ], int, int > CopyToArray(DataTable _dataTable)   
  34.         {  
  35.             int indexRow = 0;  
  36.             int indexCol = 0;  
  37.             int totalRows = _dataTable.Rows.Count;  
  38.             int countColumns = _dataTable.Columns.Count;  
  39.             int startIndex = 0;  
  40.             object[, ] objRecored = new object[totalRows + 1, countColumns];  
  41.   
  42.             foreach(DataColumn col in _dataTable.Columns)   
  43.             {  
  44.                 objRecored[indexRow, indexCol++] = col.ToString();  
  45.             }  
  46.   
  47.             indexCol = 0;  
  48.             indexRow += 1;  
  49.   
  50.             for (startIndex = 0; startIndex < totalRows; startIndex++)   
  51.             {  
  52.                 foreach(DataColumn col in _dataTable.Columns)   
  53.                 {  
  54.                     objRecored[indexRow, indexCol++] = _dataTable.Rows[startIndex][col].ToString();  
  55.                 }  
  56.                 indexCol = 0;  
  57.                 indexRow++;  
  58.             }  
  59.   
  60.   
  61.             return new Tuple < object[, ], int, int > (objRecored, totalRows + 1, countColumns);  
  62.         }  
  63.         private static void GenerateExcel(DataTable _dataTable, string _saveFileLocation)   
  64.         {  
  65.             try   
  66.             {  
  67.                 object[, ] Record = null;  
  68.                 int TotalRowsCount = 1;  
  69.                 int TotalColumnCount = 1;  
  70.   
  71.                 if (_dataTable.Rows.Count > 0)   
  72.                 {  
  73.   
  74.                     var Items = CopyToArray(_dataTable);  
  75.                     Record = Items.Item1;  
  76.                     TotalRowsCount = Items.Item2;  
  77.                     TotalColumnCount = Items.Item3;  
  78.                 }  
  79.   
  80.                 Microsoft.Office.Interop.Excel._Application _Application = new Microsoft.Office.Interop.Excel.Application();  
  81.                 Microsoft.Office.Interop.Excel._Workbook _Workbook = _Application.Workbooks.Add(Type.Missing);  
  82.                 Microsoft.Office.Interop.Excel._Worksheet _WorkSheet = (Microsoft.Office.Interop.Excel._Worksheet) _Workbook.Worksheets[1];  
  83.                 _WorkSheet.Name = "My Data";  
  84.                 //-------------------------------------header Range-----------------------  
  85.   
  86.                 Microsoft.Office.Interop.Excel.Range headerRg_first = (Microsoft.Office.Interop.Excel.Range) _WorkSheet.Cells[1, 1];  
  87.                 Microsoft.Office.Interop.Excel.Range headerRg_last = (Microsoft.Office.Interop.Excel.Range) _WorkSheet.Rows.Cells[1, TotalColumnCount];  
  88.                 Microsoft.Office.Interop.Excel.Range headerRange = _WorkSheet.get_Range(headerRg_first, headerRg_last);  
  89.   
  90.                 headerRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);  
  91.                 headerRange.EntireRow.Font.Bold = true;  
  92.                 headerRange.Font.Name = "Verdana";  
  93.                 headerRange.Font.Size = "10";  
  94.                 headerRange.RowHeight = 22;  
  95.                 headerRange.Borders.Color = System.Drawing.Color.Black;  
  96.   
  97.                 //------------------------------Record Range--------------------------------------------------------------------------  
  98.   
  99.                 Microsoft.Office.Interop.Excel.Range RecoredRg_first = (Microsoft.Office.Interop.Excel.Range) _WorkSheet.Cells[1, 1];  
  100.                 Microsoft.Office.Interop.Excel.Range RecoredRg_last = (Microsoft.Office.Interop.Excel.Range) _WorkSheet.Cells[TotalRowsCount, TotalColumnCount];  
  101.                 Microsoft.Office.Interop.Excel.Range RecordRange = _WorkSheet.get_Range(RecoredRg_first, RecoredRg_last);  
  102.   
  103.                 RecordRange.Value = Record;  
  104.                 RecordRange.Borders.Color = System.Drawing.Color.Black;  
  105.                 RecordRange.EntireColumn.AutoFit();  
  106.   
  107.                 if (System.IO.File.Exists(_saveFileLocation)) System.IO.File.Delete(_saveFileLocation);  
  108.                 _Workbook.SaveAs(_saveFileLocation, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);  
  109.                 _Workbook.Close();  
  110.                 _Application.Quit();  
  111.   
  112.   
  113.             }   
  114.             catch (Exception ex)  
  115.             {  
  116.                 //log the error...  
  117.             }  
  118.   
  119.         }  
  120.   
  121.         static void Main(string[] args)   
  122.         {  
  123.             DataTable largeDt = GetResult();  
  124.             GenerateExcel(largeDt, "c:\\abrar\\a.xls");  
  125.         }  
  126.     }  
  127. }