In this article I will explain with an example, how to read and display
Excel file data using
OpenXml in ASP.Net MVC.
Install DocumentFormat.OpenXml package
Model
The Model class consists of following properties.
public class CustomerModel
{
///<summary>
/// Gets or sets CustomerId.
///</summary>
public int CustomerId { get; set; }
///<summary>
/// Gets or sets Name.
///</summary>
public string Name { get; set; }
///<summary>
/// Gets or sets Country.
///</summary>
public string Country { get; set; }
}
Namespaces
You will need to import the following namespaces.
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
Controller
The Controller consists of following Action methods.
Action method for handling GET operation
Inside this Action method, simple the View is returned.
Action method for handling POST operation for uploading and reading Excel file
This Action method gets called when the Import Button is clicked and it accepts HttpPostedFileBase class as a parameter.
First, an object of Generic List collection of CustomerModel class is created.
The uploaded Excel file is saved to a Folder (Directory) named Uploads and the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, FOR EACH loop is executed over the fetched rows and using GetValue method (explained later), the records are copied into the Generic List collection of CustomerModel class objects.
Finally, a Generic List collection of CustomerModel class object is returned to the View.
GetValue
The GetValue function accepts SpreadsheetDocument and Cell class objects as a parameter where a cell value is determined and initialized.
Then, a check is performed if cell datatype NOT equal to NULL and cell value is string then, it returns the cell value.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase postedFile)
{
List<CustomerModel> customers = new List<CustomerModel>();
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
//Open the Excel file in Read Mode using OpenXml.
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
{
//Read the first Sheets from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
//Get the Worksheet instance.
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
//Fetch all the rows present in the Worksheet.
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//Add rows to cell.
if (row.RowIndex.Value > 1)
{
List<Cell> cells = row.Descendants<Cell>().ToList();
customers.Add(new CustomerModel
{
CustomerId = Convert.ToInt32(this.GetValue(doc, cells[0])),
Name = this.GetValue(doc, cells[1]).ToString(),
Country = this.GetValue(doc, cells[2]).ToString()
});
}
}
}
}
return View(customers);
}
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
string value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
}
return value;
}
}
View
HTML Markup
Inside the View, the CustomerModel class is declared as IEnumerable which specifies that it will be available as a Collection.
The HTML Form has been created using the Html.BeginForm method which accepts the following parameters.
ActionName – Name of the Action. In this case the name is Index.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
HtmlAttributes – This array allows to specify the additional Form Attributes. Here we need to specify enctype = “multipart/form-data” which is necessary for uploading Files.
The View also consists of an HTML FileUpload element and a Submit Button.
Submitting the Form
When the
Submit Button is clicked then, the Model is checked for NULL and if it is not NULL then, a FOR EACH loop will be executed over the Model which will generate the HTML Table rows with the
Excel file data.
@using OpenXml_Excel_GridView_MVC.Models
@model IEnumerable<CustomerModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="file" name="postedFile" />
<input type="submit" value="Import" />
}
@if (Model != null)
{
<hr />
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<th>Customer Id</th>
<th>Name</th>
<th>Country</th>
</tr>
@foreach (CustomerModel customer in Model)
{
<tr>
<td>@customer.CustomerId</td>
<td>@customer.Name</td>
<td>@customer.Country</td>
</tr>
}
</table>
}
</body>
</html>
Screenshots
The Excel File
![Read and Display Excel in ASP.Net MVC]()
Grid displaying Excel data
![Read and Display Excel in ASP.Net MVC]()
Downloads
