In this article I will explain with an example, how to read (import)
Excel file (sheet) using
OpenXml in ASP.Net MVC.
Install DocumentFormat.OpenXml package
Database
I have made use of the following table Customers with the schema as follows.
![ASP.Net MVC: Read (Import) Excel file using OpenXml]()
Note: You can download the database table SQL by clicking the download link below.
Namespaces
You will need to import the following namespaces.
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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, simply the View is returned.
Action method for handling POST operation
This Action method gets called when the Excel File is selected and the Import Button is clicked, and it gets the uploaded file in the HttpPostedFileBase parameter.
The uploaded Excel file is saved to a Folder (Directory) named Files and an object of DataTable is created.
Then, then it is opened and read using OpenXml SpreadsheetDocument class object.
After that, the instance of the first Sheet is determined and all the rows present in the Sheet are fetched.
Then, nested FOR EACH loop is executed over the fetched rows and GetValue method (explained later) is called and columns are added to the DataTable.
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.
Now a connection is established with the database and the SqlBulkCopy object is initialized and the name of the Table is specified using the DestinationTableName property.
Finally, the columns are mapped and all the rows from the DataTable are inserted into the
SQL Server Table.
Note: The mapping of columns of the DataTable and the
SQL Server table is optional and you need to do only in case where your DataTable and/or the
SQL Server Table do not have same number of columns or the names of columns are different.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase postedFile)
{
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Files/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
//Create a new DataTable.
DataTable dt = new DataTable();
//Open the Excel file in Read Mode using OpenXml.
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
{
//Read the first Sheet 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)
{
//Use the first row to add columns to DataTable.
if (row.RowIndex.Value == 1)
{
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Columns.Add(this.GetValue(doc, cell));
}
}
else
{
//Add rows to DataTable.
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = this.GetValue(doc, cell);
i++;
}
}
}
}
//Insert the Data read from the Excel file to Database Table.
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlBulkCopy sqlBulkCopy = newSqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Customers";
//[OPTIONAL]: Map the Excel columns with that of the database table.
sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
return View();
}
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
The View consists of an 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.
Note: The Form element must be specified with enctype attribute and its value must be multipart/form-data, otherwise the HttpPostedFileBase parameter will be NULL.
When
Submit Button is clicked, the selected file data is inserted into
SQL Server database Table.
@{
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" />
}
</body>
</html>
Screenshots
![ASP.Net Core: Read (Import) Excel file using OpenXml]()
The Excel File
![ASP.Net MVC: Read (Import) Excel file using OpenXml]()
Table containing the data from the Excel file
![ASP.Net MVC: Read (Import) Excel file using OpenXml]()
Downloads
