Subscribe

Close

Thank you for visiting!

Please consider subscribing to the RSS feed or following me on Twitter.

Creating an Excel File with EPPlus

EPPlus is a very helpful C# Excel library. You can do almost anything you want with it!

Here's how to create a basic Excel file, fill it with some data, and let a user download it.

First of all, let's create the Excel file. We use a DataTable to create columns and rows with the correct cell information. A DataTable is very convenient to use when you know you're going to export to Excel. You'll see why very soon.

When you've filled your DataTable with the content you want, it's time to create a MemoryStream. We use this MemoryStream as a parameter in a using statement for EPPlus: using (var xlPackage = new ExcelPackage(ms))

You need to create a WorkBook and add a WorkSheet to it. Then we use the LoadFromDataTable method to add data to our Excel cells. Finally we save the Excel file and return the MemoryStream. Now we only need to do something with it :-)

public byte[] ExportExcel(int catalogId)
{
	var dt = new DataTable();
		dt.Columns.Add("ProductId", typeof(string));
		dt.Columns.Add("Price", typeof(string));
	
	var catalog = GetCatalog(catalogId);

	foreach (var product in catalog.Products)
	{
		dt.Rows.Add(product.ProductId, product.Price);
	}

	var ms = new MemoryStream();

	using (var xlPackage = new ExcelPackage(ms))
	{
		var wb = xlPackage.Workbook;
		var ws = wb.Worksheets.Add("WS1");
		ws.Cells.LoadFromDataTable(dt, true);

		xlPackage.Save();
		return ms.ToArray();
	}
	return null;
}

In our MVC controller we call the ExportExcel method and return a FileContentResult, giving the file a name as well:

public ActionResult ExportExcel(int catalogId)
{
	if (catalogId != -1)
	{
		var catalog = _catalogRepository.GetCatalog(Convert.ToInt32(catalogId));
		var file = _exportRepository.ExportExcel(catalogId);

		if (file != null)
		{
			return new FileContentResult(file,
				"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
				{ FileDownloadName = "My Excel File.xlsx" };
		}
	}

	return View("Index");
}

Finally, we need a download button in our View. It calls the ExportExcel action method on our controller and looks like this:

@Html.ActionLink("Download", "ExportExcel", new { catalogId = 7 })

This is all the basics you need to create and fill an Excel file with data using EPPlus.

To the top