Export And Import Excel File Using ClosedXML In ASP.NET MVC

In this article, we will learn how to export and import Excel files with ClosedXML package in ASP.NET MVC.

In software applications, sometimes a user needs to export or import the data in Excel format in order to perform several operations. In this article, we will learn how to export and import an Excel file with ClosedXML package in ASP.NET MVC. ClosedXML is a .NET Library for writing and manipulating the Excel 2007+ files. It’s available free on GitHub to use for the commercial project. For more details, click here to view the license on GitHub.
 
Export And Import Excel File Using ClosedXML In ASP.NET MVC
 

Import or Read Excel File

 
Let’s begin!
 
Create a new empty ASP.NET MVC web project in Visual Studio and install the ClosedXML library from NuGet Package Manager.
 
Export And Import Excel File Using ClosedXML In ASP.NET MVC
 
After that, add an Empty Controller, i.e., HomeController in the project. Add the below code in the Controller.
  1. public ActionResult UploadExcel() {  
  2.             return View();  
  3. }  
  4.   
  5. [HttpPost]  
  6. public ActionResult UploadExcel(HttpPostedFileBase file)  
  7. {  
  8.             DataTable dt = new DataTable();  
  9.             //Checking file content length and Extension must be .xlsx  
  10.             if (file != null && file.ContentLength>0 && System.IO.Path.GetExtension(file.FileName).ToLower()==".xlsx") {  
  11.                 string path = Path.Combine(Server.MapPath("~/UploadFile"), Path.GetFileName(file.FileName));  
  12.                 //Saving the file  
  13.                 file.SaveAs(path);  
  14.                 //Started reading the Excel file.  
  15.                 using (XLWorkbook workbook=new XLWorkbook(path)) {  
  16.                     IXLWorksheet worksheet= workbook.Worksheet(1);  
  17.                     bool FirstRow = true;  
  18.                     //Range for reading the cells based on the last cell used.  
  19.                     string readRange = "1:1";  
  20.                     foreach (IXLRow row in worksheet.RowsUsed()) {  
  21.                         //If Reading the First Row (used) then add them as column name  
  22.                         if (FirstRow)  
  23.                         {  
  24.                        //Checking the Last cellused for column generation in datatable  
  25.                             readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);  
  26.                             foreach (IXLCell cell in row.Cells(readRange)) {  
  27.                                 dt.Columns.Add(cell.Value.ToString());  
  28.                             }  
  29.                             FirstRow = false;  
  30.                         }  
  31.                         else {  
  32.                             //Adding a Row in datatable  
  33.                             dt.Rows.Add();  
  34.                             int cellIndex = 0;  
  35.                             //Updating the values of datatable  
  36.                             foreach (IXLCell cell in row.Cells(readRange))  
  37.                             {  
  38.                                 dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();  
  39.                                 cellIndex++;  
  40.                             }  
  41.                         }  
  42.                     }  
  43.                     //If no data in Excel file  
  44.                     if (FirstRow) {  
  45.                         ViewBag.Message = "Empty Excel File!";  
  46.                     }  
  47.                 }  
  48.             }  
  49.             else  
  50.             {  
  51.                 //If file extension of the uploaded file is different then .xlsx  
  52.                 ViewBag.Message = "Please select file with .xlsx extension!";  
  53.             }  
  54.             return View(dt);  
  55. }  
The GET action of "Upload Excel Action" will return a View as shown in the below image. And, in the POST action method, we are checking the file, its content length, and extension. We will show a message to the user if an incorrect file is uploaded with the help of ViewBag. In UploadExcel.cshtml View, we have added a file control with a Submit button so that we can post that file. For demonstration, we are reading the Excel file and writing its data in the DataTable (You can bind that with a list or model whatever fits with your project or as per your project requirement) and returning the DataTable to the View directly in order to show the content of the Excel document.
 
Export And Import Excel File Using ClosedXML In ASP.NET MVC
UploadExcel.cshtml code
  1. @using System.Data  
  2. @model DataTable  
  3. @{  
  4.     Layout = null;  
  5. }  
  6.   
  7. <!DOCTYPE html>  
  8.   
  9. <html>  
  10. <head>  
  11.     <meta name="viewport" content="width=device-width" />  
  12.     <title>ReadExcelFile</title>  
  13.     <style>  
  14.         table {  
  15.             border: 1px solid #000000;  
  16.             text-align: left;  
  17.             border-collapse: collapse;  
  18.             margin-top:20px;  
  19.         }  
  20.   
  21.             table td, table th {  
  22.                 border: 1px solid #000000;  
  23.                 padding: 5px 4px;  
  24.             }  
  25.   
  26.             table th {  
  27.                 background-color: #5396d2;  
  28.                 color:white;  
  29.             }  
  30.     </style>  
  31. </head>  
  32. <body>  
  33.     @using (Html.BeginForm("UploadExcel""Home", FormMethod.Post, new { enctype = "multipart/form-data" }))  
  34.     {  
  35.         <div>  
  36.             <label>Upload File:</label>  
  37.             <input type="file" id="file" name="file" />  
  38.             <input type="submit" value="Upload File" />  
  39.         </div>  
  40.         <!--Display Error Message-->  
  41.         <div style="color:red;">@ViewBag.Message</div>  
  42.         <!--Show the Data Table on the View after reading the Excel File-->  
  43.         if (Model != null)  
  44.         {  
  45.   
  46.             <table>  
  47.                 <tr>  
  48.                     @for (int i = 0; i < Model.Columns.Count; i++)  
  49.                     {  
  50.                         <th>@Model.Columns[i].ColumnName</th>  
  51.                     }  
  52.                 </tr>  
  53.                 @for (int i = 0; i < Model.Rows.Count; i++)  
  54.                 {  
  55.                     <tr>  
  56.                         @for (int j = 0; j < Model.Columns.Count; j++)  
  57.                         {  
  58.                             <td>@Model.Rows[i][j]</td>  
  59.                         }  
  60.                     </tr>  
  61.                 }  
  62.             </table>  
  63.         }  
  64.         <div>  
  65.   
  66.         </div>  
  67.     }  
  68. </body>  
  69. </html>  
Let’s run the application and upload an Excel file.
 
Preview
 
Export And Import Excel File Using ClosedXML In ASP.NET MVC
 
Let’s upload an empty file or file other than Excel. Then, we will get the below messages on View.
 
Export And Import Excel File Using ClosedXML In ASP.NET MVC

Export Data to Excel with ClosedXML

 
Let’s add another action for demonstration, i.e., WriteDataToExcel() in HomeController. I have created a GetData method which will return some dummy data in the DataTable. I have mentioned the name of the Data Table which will be shown as the Excel worksheet name. In a real project, that might be coming from the Business Layer. Then, we are creating an XLWorkbook object and adding Data Table in the worksheet. After that, we are saving the file as a memory stream and returning the file to the user.
  1. public DataTable getData() {  
  2.             //Creating DataTable  
  3.             DataTable dt = new DataTable();  
  4.             //Setiing Table Name  
  5.             dt.TableName = "EmployeeData";  
  6.             //Add Columns  
  7.             dt.Columns.Add("ID"typeof(int));  
  8.             dt.Columns.Add("Name"typeof(string));  
  9.             dt.Columns.Add("City"typeof(string));  
  10.             //Add Rows in DataTable  
  11.             dt.Rows.Add(1, "Anoop Kumar Sharma""Delhi");  
  12.             dt.Rows.Add(2, "Andrew""U.P.");  
  13.             dt.AcceptChanges();  
  14.             return dt;  
  15. }  
  16. // GET: Home  
  17. public ActionResult WriteDataToExcel()  
  18. {  
  19.             DataTable dt = getData();  
  20.             //Name of File  
  21.             string fileName = "Sample.xlsx";  
  22.             using (XLWorkbook wb = new XLWorkbook())  
  23.             {  
  24.                 //Add DataTable in worksheet  
  25.                 wb.Worksheets.Add(dt);  
  26.                 using (MemoryStream stream = new MemoryStream())  
  27.                 {  
  28.                     wb.SaveAs(stream);  
  29.                     //Return xlsx Excel File  
  30.                     return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);  
  31.                 }  
  32.             }  
  33. }  
Let’s run the application and hit the WriteDataToExcel action method. An Excel file named as Sample will be downloaded.
 
Export And Import Excel File Using ClosedXML In ASP.NET MVC
 
Hope this will help you.
 
Thanks.