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");
}
.XLS, .CSV, etc...have you consideredMicrosoft.Interop.ExcelLib