Excel report read using Web Api

Question

How to read excel file using NPOI in Web Api?

Pasang Tamang · Answer

Hi, Check below code [HttpPost] public IActionResult ReadExcel([FromForm] IFormFile file) { if (file == null || file.Length == 0) { return BadRequest("File not provided"); } // Read the file into a MemoryStream var stream = new MemoryStream(); file.CopyTo(stream); stream.Position = 0; // Detect the file type IWorkbook workbook; if (file.FileName.EndsWith(".xls")) { workbook = new HSSFWorkbook(stream); } else if (file.FileName.EndsWith(".xlsx")) { workbook = new XSSFWorkbook(stream); } else { return BadRequest("Invalid file type. Only .xls and .xlsx files are supported."); } // Get the first sheet var sheet = workbook.GetSheetAt(0); // Iterate through the rows var rows = sheet.GetRowEnumerator(); while (rows.MoveNext()) { var row = (IRow)rows.Current; // Iterate through the cells var cells = row.GetEnumerator(); while (cells.MoveNext()) { var cell = (ICell)cells.Current; // Do something with the cell value var cellValue = cell.ToString(); } } return Ok(); } You can check article from below link also for detail explanation. https://www.c-sharpcorner.com/article/read-posted-excel-file-from-ajax-using-npoi-in-asp-mvc/. Regards, Pasang

Vishal Joshi · Answer

Hello Sujay, You can do it by installing NPOI in your project. Install it with the below command. or install it using NuGet Package manager. dotnet add package NPOI Try the below Sample code to read Excel Report. using NPOI.XSSF.UserModel; using NPOI.HSSF.UserModel; // Attemps to read workbook as XLSX, then XLS, then fails. public IWorkbook ReadWorkbook(string path) { IWorkbook book; try { FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); // Try to read workbook as XLSX: try { book = new XSSFWorkbook(fs); } catch { book = null; } // If reading fails, try to read workbook as XLS: if (book == null) { book = new HSSFWorkbook(fs); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Excel read error", MessageBoxButton.OK, MessageBoxImage.Error); this.DialogResult = false; this.Close(); return; } } Thanks

Excel report read using Web Api

Answers (2)