EasyExcelPlus

A .NET Standard library which help you easily work with excel. Extension of Epplus and IExcelDataReader

Installation

https://www.nuget.org/packages/EasyExcelPlus

Install-Package EasyExcelPlus

API

using EasyExcel;

Method

ExcelData<T> ImportToList<T>(ImportModel model)

Using IExcelDataReader to read data in excel file convert to List. Position property of model T is the same column in excel. Row and column will start 1.

Parameters Return Example

        [HttpPost("UploadFile")]
        public async Task<IActionResult> UploadFile()
        {
            IFormCollection formCollection = await Request.ReadFormAsync();
            IFormFile file = formCollection.Files[0];

            ExcelData<SaleModel> result = ExcelHelper.ImportToList<SaleModel>(new ImportModel
            {
                FileStream = file.OpenReadStream(),
                FileName = file.FileName,
                StartDataRow = 6,
                HeaderRow = 2,
                HeaderMapProperties = new Dictionary<int, string>
                {
                    {1, "Row ID"},
                    {2, "Order ID"},
                    {3, "Order Date"},
                    {4, "Ship Date"},
                    {5, "Sales"},
                    {6, "Quantity"},
                    {7, "Discount"},
                }
            });

            return Ok(result);
        }

byte[] ExportToBytes<T>(ExportModel<T> model, Action<ExcelWorksheet> doCustom = null)

Export excel as byte[].

Parameters Example

        [HttpGet("ExportFile")]
        public IActionResult ExportFile()
        {
            ExportModel<SaleModel> export = new ExportModel<SaleModel>
            {
                ListData = <here is list data to export>
                HeaderRow = 5,
                StartDataRow = 7,
                StartColumn = 1
            };
            byte[] memoryStream = ExcelHelper.ExportToBytes(export,
                worksheet => // worksheet of Epplus -> To custom file
                {
                    worksheet.Cells[1, 1].Value = "List transaction";
                    worksheet.Cells[1, 1, 2, 7].Merge = true; //Merge columns start and end range
                    worksheet.Cells[1, 1, 2, 7].Style.Font.Bold = true; //Font should be bold
                    worksheet.Cells[1, 1, 2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Alignment is center
                    worksheet.Cells[1, 1, 2, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center; // Alignment is center
                    worksheet.Cells[1, 1, 2, 7].Style.Font.Size = 14;

                    worksheet.Cells[3, 1, 3, 7].Value = $"From : {DateTime.Today.AddDays(-3):dd/MM/yyyy} - To : {DateTime.Today:dd/MM/yyyy}";
                    worksheet.Cells[3, 1, 3, 7].Merge = true;
                    worksheet.Cells[3, 1, 3, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells[3, 1, 3, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                    worksheet.Cells[export.StartDataRow, 3, export.StartDataRow + export.ListData.Count(), 3].Style.Numberformat.Format = "dd/MM/yyyy";
                    worksheet.Cells[export.StartDataRow, 4, export.StartDataRow + export.ListData.Count(), 4].Style.Numberformat.Format = "dd/MM/yyyy";
                });

            string fileNameExport = $"ExcelData_{DateTime.Now:yyyyMMddhhmmss}.xlsx";

            return File(memoryStream, System.Net.Mime.MediaTypeNames.Application.Octet, fileNameExport);
        }