There should be many ways you can achieve this, and here I list 2 as below:
1.Use the DocumentFormat.OpenXml, which is published by MS, but it's a little complicated. The demo code is here.
2.Use ExcelDataReader, which is very simple and supports both .xls and .xlsx. You can refer to this article to do it(note that IsFirstRowAsColumnNames property is abandoned, you can see my code below for this change).
And I write a demo with the 2nd method ExcelDataReader.Just for test purpose, I uploaded the excel to the azure web app directory like below:
And the following is the excel content:
Step 1: Create an asp.net MVC project, and then install the latest version ExcelDataReader and ExcelDataReader.DataSet via nuget package manager.
Step 2: Create a ExcelData.cs file in your project which used to read excel file:
Step 3: Write the following code in ExcelData.cs:
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
namespace WebApplication42
{
public class ExcelData
{
string _path;
public ExcelData(string path)
{
_path = path;
}
public IExcelDataReader GetExcelReader()
{
FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = null;
try
{
if (_path.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
if (_path.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
return reader;
}
catch (Exception)
{
throw;
}
}
//read the sheets name if you need
public IEnumerable<string> GetWorksheetNames()
{
var reader = this.GetExcelReader();
var workbook = reader.AsDataSet();
var sheets = from DataTable sheet in workbook.Tables select sheet.TableName;
return sheets;
}
//read data in a specified sheet
public IEnumerable<DataRow> GetData(string sheet)
{
var reader = this.GetExcelReader();
var workSheet = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
//indicates if use the header values
UseHeaderRow = true
}
}).Tables[sheet];
var rows = from DataRow a in workSheet.Rows select a;
return rows;
}
}
}
Step 4: In the controller, call the read excel method:
public ActionResult Excels()
{
ViewBag.Message = "the data from excel:";
string data = "";
//your excel path after uploaded, here I hardcoded it for test only
string path = @"D:\home\site\wwwroot\Files\ddd.xls";
var excelData = new ExcelData(path);
var people = excelData.GetData("sheet1");
foreach (var p in people)
{
for (int i=0;i<=p.ItemArray.GetUpperBound(0);i++)
{
data += p[i].ToString()+",";
}
data += ";";
}
ViewBag.Message += data;
return View();
}
Step 5: After publish to azure, launch the site and see the results-> all the data in excel are read:
