.Net Core: Reading data from CSV & Excel files

Question

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

Calkins

If I were you, I'd use the libraries most appropriate for that format rather than trying to find a one size fits all tool. Have your code use EPPlus if it's an Excel file, CsvHelper if it's a CSV file. – Papal

@Papal thanks, well I just wanted to know in case there was such library available so that I dont have to repeat steps. Yes if none is available then I may use 2 libraries as you suggested. – Calkins

Answer 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);
    }     
}

Circulation

I am uploading the file from UI what do I need to pass to new FileInfo(csvFile). Here csvFile you have is the physical location of file but I dont have that. – Calkins

It depends what type of object you are trying to load from. You could just the contents into a string and load from that. – Circulation

Sorry was travelling so could not reply. I tried your code. It works while reading an excel fine but the has issues while reading data from csv. If I have more than one row in my csv it separates each cell data by comma and doesnt distinguish by next row. See my updated post of what code I had which I used to read excel rows. – Calkins

Answer 2

Here's using Aspose, which is unfortunately not free, but wow it works great. My API is using the streaming capability with Content-Type: multipart/form-data rather than the IFormFile implementation:

[HttpPut]
[DisableFormValueModelBinding]
public async Task<IActionResult> UploadSpreadsheet()
{
    if (!MultipartRequestHelper.IsMultipartContentType(Request.ContentType))
    {
        return BadRequest($"Expected a multipart request, but got {Request.ContentType}");
    }

    var boundary = MultipartRequestHelper.GetBoundary(MediaTypeHeaderValue.Parse(Request.ContentType), _defaultFormOptions.MultipartBoundaryLengthLimit);
    var reader = new MultipartReader(boundary, HttpContext.Request.Body);

    var section = (await reader.ReadNextSectionAsync()).AsFileSection();

    //If you're doing CSV, you add this line:
    LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);

    var workbook = new Workbook(section.FileStream, loadOptions);
    Cells cells = workbook.Worksheets[0].Cells;
    var rows = cells.Rows.Cast<Row>().Where(x => !x.IsBlank);

    //Do whatever else you want here

Moleskin

Answer 3

Please try with below code

 private string uploadCSV(FileUpload fl)
    {
        string fileName = "";
        serverLocation = Request.PhysicalApplicationPath + "ExcelFiles\\";
        fileName = fl.PostedFile.FileName;
        int FileSize = fl.PostedFile.ContentLength;
        string contentType = fl.PostedFile.ContentType;
        fl.PostedFile.SaveAs(serverLocation + fileName);

        string rpath = string.Empty, dir = string.Empty;
        HttpContext context = HttpContext.Current;
        string baseUrl = context.Request.Url.Scheme + "://" + context.Request.Url.Authority + context.Request.ApplicationPath.TrimEnd('/') + '/';

        try
        {
            rpath = serverLocation + fileName;//Server.MapPath(dir + fileName);

            using (Stream InputStream = fl.PostedFile.InputStream)
            {
                Object o = new object();
                lock (o)
                {
                    byte[] buffer = new byte[InputStream.Length];
                    InputStream.Read(buffer, 0, (int)InputStream.Length);
                    lock (o)
                    {
                        File.WriteAllBytes(rpath, buffer);
                        buffer = null;
                    }
                    InputStream.Close();
                }
            }

        }
        catch (Exception ex)
        {
            lblSOTargetVal.Text = ex.Message.ToString();
        }
        return rpath;
    }

Mallen

This looks like you're just copying a file without attempting to parse it or do anything CSV related. – Isabelleisac

This code doesn't work at all. It tries to save the same file twice. The second attempt will fail because the file is already read and saved by fl.PostedFile.SaveAs. – Odilo

Answer 4

Use the Open XML SDK package and add insert working solution for it.

Occult