Excel Export
Following Example helps you to export excel from database in a generic way using EPPlus library.
Requirement
EPPlus Nuget Package
Code to Repository
public static JsonResult ExportToExcel<T>(List<T> list, string filename) { //Create excel file to export MemoryStream dataStream = new MemoryStream(); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (var package = new ExcelPackage(dataStream)) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromCollection(list, true); package.Save(); } dataStream.Position = 0; filename = filename + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; byte[] byteData = dataStream != null ? ConvertStreamToByte(dataStream) : null; string byteString = byteData != null ? Convert.ToBase64String(byteData) : null; return new JsonResult(new { filename = filename, bytes = byteString, fileType = "application/vnd.ms-excel" }); }
Supporting Method
private static byte[] ConvertStreamToByte(System.IO.MemoryStream stream) { long originalPosition = 0; if (stream.CanSeek) { originalPosition = stream.Position; stream.Position = 0; } try { byte[] readBuffer = new byte[4096]; int totalBytesRead = 0; int bytesRead; while ((bytesRead = stream.Read(readBuffer, totalBytesRead, readBuffer.Length - totalBytesRead)) > 0) { totalBytesRead += bytesRead; if (totalBytesRead == readBuffer.Length) { int nextByte = stream.ReadByte(); if (nextByte != -1) { byte[] temp = new byte[readBuffer.Length * 2]; Buffer.BlockCopy(readBuffer, 0, temp, 0, readBuffer.Length); Buffer.SetByte(temp, totalBytesRead, (byte)nextByte); readBuffer = temp; totalBytesRead++; } } } byte[] buffer = readBuffer; if (readBuffer.Length != totalBytesRead) { buffer = new byte[totalBytesRead]; Buffer.BlockCopy(readBuffer, 0, buffer, 0, totalBytesRead); } return buffer; } finally { if (stream.CanSeek) { stream.Position = originalPosition; } } }
Code to Controller
[HttpPost] public IActionResult CheckDuplicateArticleExportToExcel(viewmodelname request) { var response = _repository.GetGenerateReports(request); string fileName = "downloadExcel"; //Export to excel return ExportReports.ExportToExcel(response, fileName); }
Code to JavaScript
function ExportToExcel() { var request = { FromDate: $("#dtFromDate").val(), ToDate: $("#dtToDate").val() } $.ajax({ type: 'POST', url: "/ControllerName/IActionMethodName", data: request, contentType: "application/json; charset=utf-8", dataType: "json", success: function (r) { var blob = new Blob([Base64ToBytes(r.bytes)], { type: r.fileType }); var link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); var fileName = r.filename; link.download = fileName; link.click(); URL.revokeObjectURL(link.href); }, error: function (err) { console.log(err) } }); }
JavaScript Supporting Method
function Base64ToBytes(base64) { return Uint8Array.from(atob(base64), c => c.charCodeAt(0)); };
You can hit ExportToExcel() call in document ready method in JavaScript document by pointing any id written in HTML