Export large number of records from Database to Excel using C#

Objective

In this blog, I've explained how we can export large number of records in Excel file within very few seconds. I have implemented this solution in one of the project, where I need to export around 100,000 records in less than a minute.

Approach

• First step is to download the using NuGet Package with following command:

      

 

• It will add DocumentFormat.OpenXml.dll into your project:

    

 

• I had a situation where I was getting data in Generic List, So I converted Generic List to DataTable first. (Note: If you have all the records in DataTable itself, you don't need to execute below function!)

 public static DataSet ToDataSet<T>(this IList<T> list)  
 {  
       Type elementType = typeof(T);  
       DataSet ds = new DataSet();  
       DataTable t = new DataTable();  
       ds.Tables.Add(t);  
       foreach(var propInfo in elementType.GetProperties())  
       {  
            Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;  
            t.Columns.Add(propInfo.Name, ColType);  
       }  
       foreach (T item in list)  
       {  
            DataRow row = t.NewRow();  
            foreach (var propInfo in elementType.GetProperties())  
            {  
                     row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;  
            }  
            t.Rows.Add(row);  
       }  
       return ds;  
 }  

• Now use below code to export the records in Excel file.

 //Convert Generic List to DataSet  
 DataSet ds = ToDataSet(objList);   
 //Specify the column names which would be the Headers in Excel File when exported.  
 ds.Tables[0].Columns[0].ColumnName = "Account Name";  
 ds.Tables[0].Columns[1].ColumnName = "NAV Date";  
 ds.Tables[0].Columns[2].ColumnName = "NAV ₹";  
 var filename = ExportDataSet(ds);  
 return filename;  
 private static string ExportDataSet(DataSet ds)  
 {  
      string destination = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + ".xlsx");  
      using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))  
      {  
           var workbookPart = workbook.AddWorkbookPart();  
           workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();  
           workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();  
           foreach (System.Data.DataTable table in ds.Tables)  
           {  
             var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();  
             var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();  
             sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);  
             DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();  
             string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);  
             uint sheetId = 1;  
             if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)  
             {  
                       sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s =>s.SheetId.Value).Max() + 1;  
             }  
             DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };  
             sheets.Append(sheet);  
             DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
             List<String> columns = new List<string>();  
             foreach (System.Data.DataColumn column in table.Columns)  
             {  
                  columns.Add(column.ColumnName);  
                  DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
                  cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
                  cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);  
                  headerRow.AppendChild(cell);  
             }  
             sheetData.AppendChild(headerRow);  
             foreach (System.Data.DataRow dsrow in table.Rows)  
             {  
                  DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
                  foreach (String col in columns)  
                  {  
                      DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
                      cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
                      cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());  
                      newRow.AppendChild(cell);  
                  }  
                  sheetData.AppendChild(newRow);  
             }  
           }  
      }  
      return destination;  
 }  

If you have any questions you can reach out our SharePoint Consulting team here.