Export Excel ASP.Net Core MVC

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

Share on Facebook

Tweet