i want to display the excel file in datatable

Question

i want to display my excel file which already in my application local folder how to do am get byte code in view string filepath = Server.MapPath( "~/Files/" )+sfilename ; byte [] filedata = System.IO.File.ReadAllBytes(filepath); string contentType = MimeMapping.GetMimeMapping(filepath); var cd = new System.Net.Mime.ContentDisposition { FileName = sfilename, Inline = true , }; Response.AppendHeader( "Content-Disposition" , cd.ToString()); return File(filedata, contentType); am getting the content now i want to display that in ajax success(data)

Leon D · Answer

Try this: Workbook workbook = new Workbook(); workbook.LoadFromFile(@”..\FandH.xlsx”); Worksheet sheet = workbook.Worksheets[0]; DataTable dt = sheet.ExportDataTable(); For more information, visit this link: https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Import/Export-Import-Data-from-Excel-to-Datatable.html

Salman Beg · Answer

Yup. Mark as answer if it helps you. Thanks.

ejaz mirza · Answer

thanks i got the solution the error in binary convertion in the comments only

Salman Beg · Answer

Hi. Are you getting any kind of error in that solutions? Thanks

ejaz mirza · Answer

salman beg not working that solution

Salman Beg · Answer

Hi. Follow this article, Which will convert the byte[] to datatable. https://www.c-sharpcorner.com/blogs/deserialize-a-bytearray-into-datatable1 Thanks.

ejaz mirza · Answer

there is no database the file is directly in local folder we have to access it by using server.mappath(); the file exist in local folder only i dont have any database interactions

Sreekanth Reddy · Answer

Hi ejaz, private void ExcelToDataTable(string FilePath, string Extension, string isHDR) { string conStr=""; switch (Extension) { case ".xls": //Excel 97-03 conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"] .ConnectionString; break; case ".xlsx": //Excel 07 conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"] .ConnectionString; break; } conStr = String.Format(conStr, FilePath, isHDR); OleDbConnection connExcel = new OleDbConnection(conStr); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); connExcel.Close(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; oda.Fill(dt); connExcel.Close(); } Connection String in web.config : connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/> connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/> For more details follow the below URL : https://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx Mark as answer, If it helps. Thank you.