3

I have a WebApi wich will receive an excel file uploaded by the user as multipart/form-data. I need to read the content of that file in order to update the database. I was thinking on using EPPlus but I can't access the file. Here is the code

public class MyController : APIController
{
    [Route("import")]
    [HttpPost]
    public async Task<HttpResponseMessage> importFile()
    {
        if (!Request.Content.IsMimeMultipartContent())
            return Request.CreateErrorResponse(HttpStatusCode.BadRequest, "ERROR");

        Stream stream = await Request.Content.ReadAsStreamAsync();

        var excel = new ExcelPackage(stream);
        var workbook = excel.Workbook;

        var sheet = excel.Workbook.Worksheets.First();

        ...

}

The error is on the var sheet = excel.Workbook.Worksheets.First() because the Workbook doesn't have any Worksheet (but the physical file has 2).

What am I doing wrong? Is it the Stream?

I'm trying to have separate library for each type of Excel file (.xls or .xlsx) but I'm not able to make this work with the .xls files. I'm using ExcelDataReader and the code is now like this:

public async Task<HttpResponseMessage> importFile()
    {
        if (!Request.Content.IsMimeMultipartContent())
            return Request.CreateErrorResponse(HttpStatusCode.BadRequest, "NOT MULTIPART");

        Stream stream = await Request.Content.ReadAsStreamAsync();

        //open xlsx file            
        var excel = new ExcelPackage(stream);
        var workbook = excel.Workbook;
        try
        { 
            var sheet = excel.Workbook.Worksheets.First();

            //working fine with EPPlus for .xlsx files 
            return Request.CreateResponse(HttpStatusCode.OK, errors);                

        }catch(Exception)//open xls file
        {  
           //if its a .xls file it will throw an Exception              
        }

        //using ExcelDataReader to open .xls file
        IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        DataSet result = excelReader.AsDataSet();

        // the DataSet is null, stream is setted as ReadOnlyStream and stream.length is throwing an ObjectDisposedException
        return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, "erro");            
    }
5
  • Is the uploaded file excel 2007/2010? Commented Dec 5, 2014 at 15:23
  • can you explain the format of the file is it a .XLS, .CSV, etc... have you considered Microsoft.Interop.Excel Lib Commented Dec 5, 2014 at 15:25
  • It's a 97/2003 excel (.XLS) Commented Dec 5, 2014 at 15:33
  • @NunoRibeiro - EPPlus does not support older file formats - epplus.codeplex.com Commented Dec 5, 2014 at 16:04
  • can I use Microsot.Interop.Excel to read data from an uploaded Excel? Commented Dec 5, 2014 at 16:06

1 Answer 1

1

You can use Microsoft.Interop.Excel to read xls files, but even Microsoft disaproved this technique, because it is slow and it is not designed to run on servers. Furthermore, their support just ended.

As alternative you can use EasyXLS library. You can use it to read XLS files.

Take a look on this code sample that explains how to import an Excel file into a SQL table: http://www.easyxls.com/manual/FAQ/import-excel-to-sql.html

Sign up to request clarification or add additional context in comments.

1 Comment

since EasyXLS it's not free of charge, I will try first the free libraries

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.