i want to display the excel file in datatable

ejaz mirza

7y1.1k.NET

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)

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D7y ago
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 Beg7y ago
Yup. Mark as answer if it helps you. Thanks.
ejaz mirza7y ago
thanks i got the solution
the error in binary convertion in the comments only
Salman Beg7y ago
Hi. Are you getting any kind of error in that solutions? Thanks
ejaz mirza7y ago
salman beg not working that solution
Salman Beg7y ago
Hi.
Follow this article, Which will convert the byte[] to datatable.
https://www.c-sharpcorner.com/blogs/deserialize-a-bytearray-into-datatable1
Thanks.
ejaz mirza7y ago
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 Reddy7y ago
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.