# 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);
}

• I can recommend taking a look at Excel-OleDb and read/write excel sheets as if they were databases... this is super fast if you don't necessariliy have to do other things. – t3chb0t Jul 25 '18 at 9:55
• If you're getting your data in a DataTable, you can export it to xml. Excel can import xml directly. – user33306 Jul 25 '18 at 11:19

## 1 Answer

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.

• Oh yea, ElementAt is really bad here... unless the source is an IList then it wouldn't be so bad but still not optimal. However, in order to avoid the helper index I would use .Select((planResult, index) => (planResult, index: index + 2)) and return a tuple. – t3chb0t Jul 25 '18 at 13:26
• @Henrik Hansen you suggestion really help me. Now i export my result less then 30 sec. – Dragomir Dimitrov Jul 25 '18 at 13:31
• @t3chb0t: You can do that, but you are not avoiding the index. In fact you are creating n indices and tuples (where n == datasource.GetCount()). I don't see any benefit in that. :-) – user73941 Jul 25 '18 at 13:39
• the benefit is simplicity ;-] – t3chb0t Jul 25 '18 at 13:40