How to export list data to excel in C#

Thamsanqa Ngcongwane

7y2kLearn iOS Programming

The following is my c# code Im only stuck on the foreach part where by I have to insert data from list to excel.

public IActionResult ExportData() {
List < Users > ApplicationList = _context.Users.ToList();
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = @ "Candidates.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
var memory = new MemoryStream();
using(var fs = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.Write)) {
IWorkbook workbook;
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("Candidates");
IRow row = excelSheet.CreateRow(0);
foreach(var data in ApplicationList) {}
workbook.Write(fs);
}
using(var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open)) {
stream.CopyTo(memory);
}
memory.Position = 0;
return File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gopaiah Nekarukanti7y ago
You can also try OpenXml

https://www.aspsnippets.com/Articles/OpenXml-MVC-Example-Export-to-Excel-using-OpenXml-in-ASPNet-MVC.aspx
0
Leon D7y ago
You can use Free Spire.XLS library to insert or export data to excel. It's easy to use.
https://www.e-iceblue.com/Introduce/free-xls-component.html
0
Imran Shaikh7y ago
this is how i do,
this might help you as well...
First add in References
goto project right click and add references
and choose Microsoft.Office.Interop.Excel

internal static Microsoft.Office.Interop.Excel.Application xla;
internal static Microsoft.Office.Interop.Excel.Workbook wkbk;
internal static Microsoft.Office.Interop.Excel.Worksheet wksht;

xla = new Microsoft.Office.Interop.Excel.Application();
xla.Visible = true;
wkbk = xla.Workbooks.Add(true);
wksht = wkbk.Sheets.get_Item(1);
// add heading

wksht.Cells[1, 1].value = "heading 1";
wksht.Cells[1, 5].value = "heading 5";

// add alignment and formating

wksht.Range["A1:J1"].Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
wksht.Range["A1:J1"].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
wksht.Range["A1:J1"].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromA#c2d69a);
wksht.Range["A1:J1"].Font.Bold = true;

// add data to excel
// when reading from database

int i = 2;// excel start row / initial row number as first is heading
foreach (DataRow dr in ds.Tables[0].Rows)
{
wksht.Cells[i, 1].value = dr["A"];
wksht.Cells[i, 2].value = dr["B"];
wksht.Cells[i, 3].value = dr["C"];
wksht.Cells[i, 4].value = dr["D"];
wksht.Cells[i, 5].value = dr["E"];
i++;
}

// when reading from list

int i = 2;// excel start row / initial row number as first is heading
foreach (int item in list)
{
wksht.Cells[i, 1].value = item;
i++;
}
Hope this might resolve your problem
0