Export to Excel From DB is too slow

My function works pretty slowly with big results from the db (420000 records and growing). Can some one give me any proposal to make it faster?

 public void GenerateExcelFileWithSPResultData(IEnumerable<Plans_Result> datasource, string excelFile)
    {

        var excelPackage = new ExcelPackage();
        ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add("Plans");


        ws.Cells[1, 1].Value = "ACC_DATE";
        ws.Cells[1, 2].Value = "DUE_DATE";
        ws.Cells[1, 3].Value = "IDENTIFIER_VALUE";
        ws.Cells[1, 4].Value = "INSTALLMENT_NO";
        ws.Cells[1, 5].Value = "PRINCIPAL_AMT_DUE";
        ws.Cells[1, 6].Value = "SPA";

        for (int i = 0; i < datasource.Count(); i++)
        {
            ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).ACC_DATE;
            ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).DUE_DATE;
            ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).IDENTIFIER_VALUE;
            ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).INSTALLMENT_NO;
            ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).PRINCIPAL_AMT_DUE;
            ws.Cells[i + 2, 6].Value = datasource.ElementAt(i).SPA;
        }

        using (ExcelRange rng = ws.Cells["A1:F1"])
        {
            rng.Style.Font.Bold = true;
            rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
            rng.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
            rng.Style.Font.Color.SetColor(Color.Black);
        }
        byte[] data = excelPackage.GetAsByteArray();

        File.WriteAllBytes(excelFile, data);
    }

If you can’t use the suggestions in the comments by t3chb0t or tinstaafl, you could maybe use the Open XML SDK.

In your current code it is rather inefficient that you are calling datasource.ElementAt(i)... for each property on the same data element. Depending on how ElementAt(i) is implemented, it can be a considerable bottleneck, to search for the same element each time calling ElementAt(i).

The solution is obviously to create a temporary Plans_Result-reference for each loop:

for (int i = 0; i < datasource.Count(); i++)
{
  Plans_result item = datasource.ElementAt(i) as Plans_result;
  ws.Cells[i + 2, 1].Value = item.ACC_DATE;
  ws.Cells[i + 2, 2].Value = item.DUE_DATE;
  ws.Cells[i + 2, 3].Value = item.IDENTIFIER_VALUE;
  ws.Cells[i + 2, 4].Value = item.INSTALLMENT_NO;
  ws.Cells[i + 2, 5].Value = item.PRINCIPAL_AMT_DUE;
  ws.Cells[i + 2, 6].Value = item.SPA;
}

Alternatively you could use a foreach-statement while counting the rows in a separate index variable:

int index = 2;
foreach (Plans_result item in datasource)
{
  ws.Cells[index, 1].Value = item.ACC_DATE;
  ...
  index++;
}

I would use the latter as it is more efficient, because ElementAt(i) may be rather slow for large vectors.