In software development, Import/Export is a technique to read and write data from files. We have multiple data file format to store our valuable data like png, jpg, xls, xlsx, pdf, txt, sql, etc, from where we need to read and write data. Every technology has its own technique to read and write data from files. Here, I am using the c# programming language to perform this task through ASP.NET MVC Web Application.
Jump to Section
Note: I am using visual studio 2013, Follow the steps to create your application for Import and export excel file data.
Note: If a new window is open then select Empty then Click Ok.
Now, new Empty MVC application is created successfully.
public int id public string Expectation You can easily find and include this library via NuGet within your application as well through the following steps:
Both of these approaches will add the appropriate Excel assembly references to your application. Then when you actually need to execute a query within your code, you might use something like this:
// Reference to access the appropriate ExcelReader classes using Excel;Add the following code to ExcelData.cs class.
string _path; public ExcelData(string path) < _path = path; >public IExcelDataReader getExcelReader() < // ExcelDataReader works with the binary Excel file, so it needs a FileStream // to get started. This is how we avoid dependencies on ACE or Interop: FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read); // // We return the interface, so that IExcelDataReader reader = null; try < if (_path.EndsWith(".xls")) < reader = ExcelReaderFactory.CreateBinaryReader(stream); >if (_path.EndsWith(".xlsx")) < reader = ExcelReaderFactory.CreateOpenXmlReader(stream); >if (_path.EndsWith(".ods")) < reader = ExcelReaderFactory.CreateBinaryReader(stream); >return reader; > catch (Exception) < throw; >> public IEnumerable getWorksheetNames() < var reader = this.getExcelReader(); var workbook = reader.AsDataSet(); var sheets = from DataTable sheet in workbook.Tables select sheet.TableName; return sheets; >public IEnumerable getData(string sheet, bool firstRowIsColumnNames = true) < var reader = this.getExcelReader(); reader.IsFirstRowAsColumnNames = firstRowIsColumnNames; //string SheetName = getWorksheetNames().FirstOrDefault(); var workSheet = reader.AsDataSet().Tables[sheet]; var filteredRows = workSheet.Rows.Cast().Where(row => row.ItemArray.Any(field => !(field is System.DBNull))); // TTliteUtil.Util.WriteToEventLog("Sheet read: "+workSheet.ToString()); var rows = from DataRow a in filteredRows select a; return rows; > Reference to access the model classes
using Export_Excel.Models;using System.IO; using System.Data; public ActionResult Index() ViewBag.EmployeeList = ""; return View(); >public ActionResult Import(FormCollection formCollection) if (Request != null) DataTable dt = new DataTable(); HttpPostedFileBase file = Request.Files["UploadedFile"]; if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) string fileName = file.FileName; string path = Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["XlsFilePath"] + fileName); file.SaveAs(path); if (!System.IO.Directory.Exists(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["XlsFilePath"]))) System.IO.Directory.CreateDirectory(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["XlsFilePath"])); > var excelData = new ExcelData(path); var sData = excelData.getData("Sheet1"); List list = new List(); dt = sData.CopyToDataTable(); foreach (DataRow item in dt.Rows) Employee emp = new Employee(); emp.id = Convert.ToInt32(item["id"]); emp.Expectation = item["Expectation"].ToString(); list.Add(emp); > ViewBag.EmployeeList = list; TempData["EmployeeList"] = list; > > return View("Index"); >public ActionResult Export() List emps = TempData["EmployeeList"] as List; var grid = new System.Web.UI.WebControls.GridView(); grid.DataSource = emps; grid.DataBind(); Response.ClearContent(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment; filename=Expectations.xls"); Response.ContentType = "application/ms-excel"; Response.Charset = ""; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); grid.RenderControl(htw); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); ViewBag.EmployeeList = emps; return View("Index"); >@using Export_Excel.Models;@ ViewBag.Title = "Index"; > @using (Html.BeginForm("Import", "Home", FormMethod.Post, new < enctype = "multipart/form-data" >)) Select File : Import Data : >
@using (Html.BeginForm("Export", "Home")) Export Data : >
@ @foreach (var l in list) @l.id @l.Expectation > | Id | Expectation |
| 1 | Acknowledge emails within 2 hours |
| 2 | Be prepared for Daily Stand-up at 10:15 am |
| 3 | Commit code updates to repository daily |
| 4 | Ensuring the timely delivery of work as per quality standards. |
| 5 | Every update to production server must be signed off by QA |
| 6 | Follow variable naming conventions & guidelines for the particular platform. |
| 7 | Have 90% understanding of the project through SRS doc before commencing work. |
| 8 | Maintain politeness while talking to other colleagues |
| 9 | No use of social media either with phone or desktop. |
| 10 | Provide Developer Sign Off before handing over builds to QA |
I hope, this article would help you to understand about Import and Export data from Excel or HTML. In the end, You are expert in managing data from Excel sheet in MVC Application.
