Pfthb

Using .net core & c# here.

I have a UI from which user can upload the Excel or CSV files. Once they upload this goes to my web api which handles the reading of the data from these files and returns json.

My Api code as:

 [HttpPost("upload")]
 public async Task<IActionResult> FileUpload(IFormFile file)
 
 JArray data = new JArray();
 using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
 
 ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
 //Process, read from excel here and populate jarray
 
 return Ok(data );
 

In my above code I am using EPPlus for reading the excel file. For excel file it works all fine but it cannot read csv file which is the limitation of EPPlus.

I searched and found another library CSVHelper: https://joshclose.github.io/CsvHelper/ The issue with this is it does vice versa and can read from CSV but not from Excel.

Is there any library available which supports reading from both.

Or would it be possible use EPPlus only but convert uploaded CSV to excel on the fly and then read. (please note I am not storing the excel file anywhere so cant use save as to save it as excel)

Any inputs please?

--Updated - Added code for reading data from excel---

 int rowCount = worksheet.Dimension.End.Row;
 int colCount = worksheet.Dimension.End.Column;

 for (int row = 1; row <= rowCount; row++)
 
 for (int col = 1; col <= colCount; col++)
 
 var rowValue = worksheet.Cells[row, col].Value;
 
 

//With the code suggested in the answer rowcount is always 1

You can use EPPLus and a MemoryStream for opening csv files into an ExcelPackage without writing to a file. Below is an example. You may have to change some of the the parameters based on your CSV file specs.

[HttpPost("upload")]
public async Task<IActionResult> FileUpload(IFormFile file)

 var result = string.Empty;
 string worksheetsName = "data";

 bool firstRowIsHeader = false;
 var format = new ExcelTextFormat();
 format.Delimiter = ',';
 format.TextQualifier = '"';

 using (var reader = new System.IO.StreamReader(file.OpenReadStream()))
 using (ExcelPackage package = new ExcelPackage())
 
 result = reader.ReadToEnd();
 ExcelWorksheet worksheet = 
 package.Workbook.Worksheets.Add(worksheetsName);
 worksheet.Cells["A1"].LoadFromText(result, format, OfficeOpenXml.Table.TableStyles.Medium27, firstRowIsHeader);