Unlock Efficient Data Exchange: Import & Export Excel in ASP.NET Core

This article will guide you through the process of seamlessly importing and exporting Excel files in ASP.NET Core 3.1 Razor Pages. We will cover the following essential topics:

>>  Importing an Excel file in .NET Core and previewing the uploaded file
>>  Exporting the Excel file 

NPOI Package: A Powerful Tool for Excel File ManagementNPOI is a versatile, open-source tool that supports xls, xlsx, and docx extensions. As the .NET equivalent of the POI Java project at http://poi.apache.org/, NPOI enables you to read and write XLS, DOC, PPT files. It offers a wide range of features, including styling, formatting, data formulas, and image extraction, among others. A significant advantage of NPOI is that it does not require Microsoft Office to be installed on the server. You can utilize NPOI anywhere, making it a valuable component for your projects. For more information on how to unlock efficient data exchange, check out computerstechnicians.

This post demonstrates the fundamental functionalities of NPOI, but you can accomplish much more with NPOI, such as styling individual cells or rows, creating Excel formulas, and other tasks. The NPOI package supports both “xls” and “xlsx” extensions, utilizing HSSFWorkbook and XSSFWorkbook classes. The HSSFWorkbook class is for “xls”, whereas the other is for “xlsx”. To learn more about NPOI, refer to the official documentation.

Streamlining Excel Import and Export in ASP.NET Core 3.1 Razor Pages

Let’s create a .NET Core web application with .NET Core 3.1. Open VS 2019 ➠ Select ASP.NET Core web application

In this example, we create an input file for uploading the Excel file, and after uploading it, append the Excel data into a DIV. Then, we download that Excel data and also create an Excel file using dummy data. The design is shown below:

Import and Export Excel in ASP.NET Core 3.1 Razor Pages 

            <a href="@Url.Action(">Retrieve File</a>





     

Code Fragment Breakdown

This HTML code fragment integrates a file upload interface and a corresponding upload button. The accompanying jQuery script enables the asynchronous upload of Excel files, while also conducting client-side validation to verify file selection and extension accuracy.

Upon successful completion of the request, the server response is appended to the HTML. For a detailed understanding of handling Ajax requests with ASP.NET Core 3.1 razor pages, refer to this article, and for uploading files in ASP.NET Core 3.1 Razor Pages, consult this resource.

To facilitate file import, create a post method in the Homecontroller.cs file, saving the uploaded file to the wwwroot folder, and subsequently append it to the div.

public ActionResult Import()
        {
            IFormFile file = Request.Form.Files[0];
            string folderName = "UploadExcel";
            string webRootPath = _hostingEnvironment.WebRootPath;
            string newPath = Path.Combine(webRootPath, folderName);
            StringBuilder sb = new StringBuilder();
            if (!Directory.Exists(newPath))
            {
                Directory.CreateDirectory(newPath);
            }
            if (file.Length > 0)
            {
                string sFileExtension = Path.GetExtension(file.FileName).ToLower();
                ISheet sheet;
                string fullPath = Path.Combine(newPath, file.FileName);
                using (var stream = new FileStream(fullPath, FileMode.Create))
                {
                    file.CopyTo(stream);
                    stream.Position = 0;
                    if (sFileExtension == ".xls")
                    {
                        HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
                        sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
                    }
                    else
                    {
                        XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
                        sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
                    }
                    IRow headerRow = sheet.GetRow(0); //Get Header Row
                    int cellCount = headerRow.LastCellNum;
                    sb.Append("<table class='table table-bordered'><tr>");
                    for (int j = 0; j < cellCount; j++)
                    {
                        NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j);
                        if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
                        sb.Append("<th>" + cell.ToString() + "</th>");
                    }
                    sb.Append("</tr>");
                    sb.AppendLine("<tr>");
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue;
                        if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                sb.Append("<td>" + row.GetCell(j).ToString() + "</td>");
                        }
                        sb.AppendLine("</tr>");
                    }
                    sb.Append("</table>");
                }
            }
            return this.Content(sb.ToString());
        }

To access the file, follow the instructions outlined in the code snippet below:

public ActionResult Download()
        {
            string filePath = "wwwroot/UploadExcel/CoreProgramm_ExcelImport.xlsx";
            byte[] fileBytes = System.IO.File.ReadAllBytes(filePath);
            System.IO.File.WriteAllBytes(filePath, fileBytes);
            MemoryStream ms = new MemoryStream(fileBytes);
            return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, "employee.xlsx");
        }

To create and export the file, let’s leverage sample employee data and export it in a corresponding manner.

public async Task<IActionResult> Export()
        {
            string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = @"Employees.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 XSSFWorkbook();
                ISheet excelSheet = workbook.CreateSheet("employee");
                IRow row = excelSheet.CreateRow(0);

                row.CreateCell(0).SetCellValue("EmployeeId");
                row.CreateCell(1).SetCellValue("EmployeeName");
                row.CreateCell(2).SetCellValue("Age");
                row.CreateCell(3).SetCellValue("Sex");
                row.CreateCell(4).SetCellValue("Designation");

                row = excelSheet.CreateRow(1);
                row.CreateCell(0).SetCellValue(1);
                row.CreateCell(1).SetCellValue("Jack Supreu");
                row.CreateCell(2).SetCellValue(45);
                row.CreateCell(3).SetCellValue("Male");
                row.CreateCell(4).SetCellValue("Solution Architect");

                row = excelSheet.CreateRow(2);
                row.CreateCell(0).SetCellValue(2);
                row.CreateCell(1).SetCellValue("Steve khan");
                row.CreateCell(2).SetCellValue(33);
                row.CreateCell(3).SetCellValue("Male");
                row.CreateCell(4).SetCellValue("Software Engineer");

                row = excelSheet.CreateRow(3);
                row.CreateCell(0).SetCellValue(3);
                row.CreateCell(1).SetCellValue("Romi gill");
                row.CreateCell(2).SetCellValue(25);
                row.CreateCell(3).SetCellValue("FeMale");
                row.CreateCell(4).SetCellValue("Junior Consultant");

                row = excelSheet.CreateRow(4);
                row.CreateCell(0).SetCellValue(4);
                row.CreateCell(1).SetCellValue("Hider Ali");
                row.CreateCell(2).SetCellValue(34);
                row.CreateCell(3).SetCellValue("Male");
                row.CreateCell(4).SetCellValue("Accountant");

                row = excelSheet.CreateRow(5);
                row.CreateCell(0).SetCellValue(5);
                row.CreateCell(1).SetCellValue("Mathew");
                row.CreateCell(2).SetCellValue(48);
                row.CreateCell(3).SetCellValue("Male");
                row.CreateCell(4).SetCellValue("Human Resource");

                workbook.Write(fs);
            }
            using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
            {
                await stream.CopyToAsync(memory);
            }
            memory.Position = 0;
            return File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
        }

Let's initiate the application and examine the results.

Access the Source Code at Github.com/CoreProgramm/.

Securing Your Data: Creating Immutable Copies of SQL and MySQL Databases

Top Discounts on Dell's Premium Laptops: Unbeatable Offers on XPS 13, XPS 15, and XPS 17 Models

Recommended Reading

Unlock Angular’s Full Potential: Inject Services with Ease in 5 Minutes!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Comment *

Name *

Email *

Save my name, email, and website in this browser for the next time I comment.