In most of the applications where we gave the user the option to upload data using excel .User can upload an excel sheet containing multiple sheets.
But some times when the user uploads excel he may get the below error
"The Microsoft Jet database engine could not find the object 'Sheet1$_'.
Make sure the object exists and that you spell its name and the path name correctly."
Now when we go through the excel sheet uploaded by the user we couldn't find the Sheet with _.
So where does this sheet come from.The answer to this question is that when we apply filter to a sheet then Microsoft creates a hidden sheet to show filtered data.
So does this mean that we can remove the above error by just removing the filter.The answer is no .Even though we remove the filter from the sheet ,still the hidden sheet is there and while uploading excel this will throw error.
So how to solve this problem.Now we can solve this problem in 2 ways
1)Create a new excel sheet and copy all the sheets to that sheet and upload.
2)Handle the hidden sheet in code.
First option is fine when we have limited users and limited sheets .But we cannot ask a user to do create a new sheet with filter .So in this article i will show how to handle this issue in Asp.net MVC.
First of all we will create a ImportExcel in Home Controller which returns a View.This method will return a View for Get Request. Now we will create another method ImportExcel1 and decorate it with [HttpPost] Attribute.Since in MVC 2 Methods cannot have same method name,we can call 2 actions using Action Name attribute .So we will decorate ImportExcel1 with [ActionName("Importexcel")]. Now if we make a get request then ImportExcel will be called and for post request ImportExcel1 will be called.Below is the code to read excel files.
But some times when the user uploads excel he may get the below error
"The Microsoft Jet database engine could not find the object 'Sheet1$_'.
Make sure the object exists and that you spell its name and the path name correctly."
Now when we go through the excel sheet uploaded by the user we couldn't find the Sheet with _.
So where does this sheet come from.The answer to this question is that when we apply filter to a sheet then Microsoft creates a hidden sheet to show filtered data.
So does this mean that we can remove the above error by just removing the filter.The answer is no .Even though we remove the filter from the sheet ,still the hidden sheet is there and while uploading excel this will throw error.
So how to solve this problem.Now we can solve this problem in 2 ways
1)Create a new excel sheet and copy all the sheets to that sheet and upload.
2)Handle the hidden sheet in code.
First option is fine when we have limited users and limited sheets .But we cannot ask a user to do create a new sheet with filter .So in this article i will show how to handle this issue in Asp.net MVC.
First of all we will create a ImportExcel in Home Controller which returns a View.This method will return a View for Get Request. Now we will create another method ImportExcel1 and decorate it with [HttpPost] Attribute.Since in MVC 2 Methods cannot have same method name,we can call 2 actions using Action Name attribute .So we will decorate ImportExcel1 with [ActionName("Importexcel")]. Now if we make a get request then ImportExcel will be called and for post request ImportExcel1 will be called.Below is the code to read excel files.
Here we have created a static class Utility ,which contains a method ConvertXSLXtoDataSet. Below is the code for Utility class.using System; using System.Collections.Generic; using System.Data; using System.Data.OleDb; using System.IO; using System.Linq; using System.Web; using System.Web.Mvc; using ExcelUpload.Models; namespace ExcelUpload.Controllers { public class HomeController : Controller { public ActionResult ImportExcel() { return View(); } [ActionName("Importexcel")] [HttpPost] public ActionResult Importexcel1() { if (Request.Files["FileUpload1"].ContentLength > 0) { string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower(); string query = null; string connString = ""; string[] validFileTypes = { ".xls", ".xlsx" }; string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName); if (!Directory.Exists(path1)) { Directory.CreateDirectory(Server.MapPath("~/Content/Uploads")); } if (validFileTypes.Contains(extension)) { if (System.IO.File.Exists(path1)) { System.IO.File.Delete(path1); } Request.Files["FileUpload1"].SaveAs(path1); //Connection String to Excel Workbook if (extension.Trim() == ".xls") { connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; DataSet dt = Utility.ConvertXSLXtoDataSet(path1,connString); ViewBag.Data = dt; } else if (extension.Trim() == ".xlsx") { connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; DataSet dt = Utility.ConvertXSLXtoDataSet(path1, connString); ViewBag.Data = dt; } } else { ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format"; } } return View(); } } }
Now we will create a view that contains file upload control and a button.When a request for ImportExcel of Home Controller is made,we will show file upload control with button control.When we select a file and press button it will make a post request to Home Controller and ImportExcel1 method will be called.Below is the Razor View for both request.using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Web; using System.Data.OleDb; namespace ExcelUpload.Models { public static class Utility { public static DataSet ConvertXSLXtoDataSet(string strFilePath, string connString) { OleDbConnection oledbConn = new OleDbConnection(connString); DataTable dt = new DataTable(); DataSet ds = new DataSet(); try { oledbConn.Open(); using (DataTable Sheets = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { for (int i = 0; i < Sheets.Rows.Count; i++) { string worksheets = Sheets.Rows[i]["TABLE_NAME"].ToString(); OleDbCommand cmd = new OleDbCommand(String.Format("SELECT * FROM [{0}]", worksheets), oledbConn); OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; DataTable t = new DataTable(worksheets); oleda.Fill(t); ds.Tables.Add(t); } } } catch(Exception ex) { } finally { oledbConn.Close(); } return ds; } } }
@using System.Data; @{ ViewBag.Title = "Import Excel With Multiple Sheets"; Layout = "~/Views/Shared/_Layout.cshtml"; } <h2>Import Excel With Multiple Sheets</h2> <!--[if !IE]><!--> <style type="text/css"> /* Generic Styling, for Desktops/Laptops */ table { width: 100%; border-collapse: collapse; } /* Zebra striping */ tr:nth-of-type(odd) { background: #eee; } th { background: #333; color: white; font-weight: bold; } td, th { padding: 6px; border: 1px solid #ccc; text-align: left; } /* Max width before this PARTICULAR table gets nasty This query will take effect for any screen smaller than 760px and also iPads specifically. */ @@media only screen and (max-width: 760px), (min-device-width: 768px) and (max-device-width: 1024px) { /* Force table to not be like tables anymore */ table, thead, tbody, th, td, tr { display: block; } /* Hide table headers (but not display: none;, for accessibility) */ thead tr { position: absolute; top: -9999px; left: -9999px; } tr { border: 1px solid #ccc; } td { /* Behave like a "row" */ border: none; border-bottom: 1px solid #eee; position: relative; padding-left: 50%; } td:before { /* Now like a table header */ position: absolute; /* Top/left values mimic padding */ top: 6px; left: 6px; width: 45%; padding-right: 10px; white-space: nowrap; } /* Label the data */ td:before { content: attr(data-title); } } </style> <!--<![endif]--> @using (Html.BeginForm("ImportExcel","Home",FormMethod.Post,new { enctype = "multipart/form-data" } )) { <table> <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr> <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr> </table> } <div> @if (ViewBag.Data != null) { foreach (DataTable dt in (ViewBag.Data as System.Data.DataSet).Tables) { <h3>@dt.TableName.Replace("$","")</h3> <hr/> <table id=""> <thead> <tr> @foreach (DataColumn column in dt.Columns) { <th>@column.ColumnName.ToUpper()</th> } </tr> </thead> @if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { <tr> @foreach (DataColumn column in dt.Columns) { <td data-title='@column.ColumnName'> @dr[column].ToString() </td> } </tr> } } else { int count = dt.Columns.Count; <tr> <td colspan='@count' style="color:red;"> No Data Found. </td> </tr> } </table> } } else { if (ViewBag.Error != null) { <table> <tr> <td style="color:red;"> @(ViewBag.Error != null ? ViewBag.Error.ToString() : "") </td> </tr> </table> } } </div>
Now if we run the above code and call the ImportExcel Method of Home Controller,
it works fine until we upload excel having sheets without filter.But as soon as we upload
an excel sheet with filter we will get the error:-
"The Microsoft Jet database engine could not find the object 'Sheet1$_'.
Make sure the object exists and that you spell its name and the path name correctly."
So why we are getting this sheet with "_"? As you can see in the above code "ConvertXSLXtoDataSet" method,we are using a method "GetOleDbSchemaTable".This "GetOleDbSchemaTable" also returns hidden tables in the excel file. There is a simple workaround for this.We need to just check whether the sheet name contains "_" and ignore it while reading file.Below is the modified ConvertXSLXtoDataSet Method.
public static DataSet ConvertXSLXtoDataSet(string strFilePath, string connString) { OleDbConnection oledbConn = new OleDbConnection(connString); DataTable dt = new DataTable(); DataSet ds = new DataSet(); try { oledbConn.Open(); using (DataTable Sheets = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)) { for (int i = 0; i < Sheets.Rows.Count; i++) { string worksheets = Sheets.Rows[i]["TABLE_NAME"].ToString(); if (!worksheets.Contains("_")) { OleDbCommand cmd = new OleDbCommand(String.Format("SELECT * FROM [{0}]", worksheets), oledbConn); OleDbDataAdapter oleda = new OleDbDataAdapter(); oleda.SelectCommand = cmd; DataTable t = new DataTable(worksheets); oleda.Fill(t); ds.Tables.Add(t); } } } } catch(Exception ex) { } finally { oledbConn.Close(); } return ds; }
