August 30, 2017

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

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.

  • 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();  
       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;  
       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 };  
             DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
             List<String> columns = new List<string>();  
             foreach (System.Data.DataColumn column in table.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(column.ColumnName);  
             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());  
      return destination;  

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

No comments:

Post a Comment