Click here to Skip to main content
15,180,027 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,
Please can anyone help me to write code to display Excel data in HTML table uisng MVC framework.

This is the code what i have written in class library file(TableBS).
The below method im calling in controller class using the object.
Iam gettg the exception in (SELECT * FROM [Sheet1$]", conn) as excepted.

C#
 public string getTable() 
 { 
	XElement xml = null; 
	try 
	{ 
		string strConnectionString; 
		string filename = @"Demo.xls"; // Ignore Path... Shortened to make more readable (MK)
		
		if (Path.GetExtension(filename).ToLower() == ".xlsx") 
		{ 
			strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0", filename); 
			OleDbConnection conn = new OleDbConnection(strConnectionString); 
			conn.Open(); 
			OleDbCommand dataCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", conn); 
			OleDbDataAdapter da = new OleDbDataAdapter(dataCommand); 
			DataSet ds = new DataSet(); 
			da.Fill(ds); 
			xml = new XElement("Table"); 
			xml.Add(from data in ds.Tables[0].AsEnumerable() select data); 
		} 
		else if (Path.GetExtension(filename).ToLower() == ".xls") 
		{ 
			strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", filename); 
			OleDbConnection conn = new OleDbConnection(strConnectionString); 
			conn.Open(); 
			OleDbCommand dataCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", conn); 
			OleDbDataAdapter da = new OleDbDataAdapter(dataCommand); 
			DataSet ds = new DataSet(); da.Fill(ds); 
			xml = new XElement("Table"); 
			xml.Add(from data in ds.Tables[0].AsEnumerable() select data); 
		} 
		return xml.ToString(); 
	} 
	catch (Exception ex) 
	{ 
		Log(ex.Message.ToString(), "MenuBS", "getTable"); return "<Table/>"; 
	} 
	finally 
	{ 
		xml = null; 
	} 
}


Thanks
Madhusudhan
Posted
Updated 6-Dec-10 7:49am
v4
Comments
[no name] 6-Dec-10 8:32am
   
First you open Visual Studio, then begin to type. What else? Are you having an actually problem? Have you tried anything?
madhusudhan.k 6-Dec-10 8:41am
   
This is the code what i have written in class library file(TableBS).the below method im calling in controller class using the object. Iam gettg the exception in ("SELECT * FROM [Sheet1$]", conn); as )excepted.


public string getTable()
{
XElement xml = null;
try
{
string strConnectionString ;
string filename = @"C:\Documents and Settings\madhusudhank\My Documents\Visual Studio 2008\Projects\AqumenSport\AqumenSportWeb\Content\Demo.xls";
if (Path.GetExtension(filename).ToLower() == ".xlsx")
{
strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0", filename);
OleDbConnection conn = new OleDbConnection(strConnectionString);
conn.Open();
OleDbCommand dataCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(dataCommand);
DataSet ds = new DataSet();
da.Fill(ds);
xml = new XElement("Table");
xml.Add(from data in ds.Tables[0].AsEnumerable()
select data);
}
else if (Path.GetExtension(filename).ToLower() == ".xls")
{

strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", filename);
OleDbConnection conn = new OleDbConnection(strConnectionString);
conn.Open();
OleDbCommand dataCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(dataCommand);
DataSet ds = new DataSet();
da.Fill(ds);
xml = new XElement("Table");
xml.Add(from data in ds.Tables[0].AsEnumerable()
select data);
}




return xml.ToString();
}
catch (Exception ex)
{

Log(ex.Message.ToString(), "MenuBS", "getTable");
return "<Table/>";
}
finally
{
xml = null;
}
}
fjdiewornncalwe 6-Dec-10 12:49pm
   
I have added your code block into the question itself. To make everything easier to read, please remove the comment that you have added... Cheers.
madhusudhan.k 7-Dec-10 0:01am
   
Hi Marcus Kramer,
Thanks for ur help, but again its throwing exception as the file path is incorrect. When I give the correct file path means it will throw exception as I said u before.

Regards
Madhusudhan

1 solution

Lets discuss a generic excel connection a bit:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"

The HDR=YES tells the JET engine to use the first row as
column headers.
Suppose you have two columns in excel "Name" and "Amount" in the first
row. Now if you use the HDR=YES than it will treat the first row as
column identifiers, not as data. Else it will treat is as data.

Also there is the IMEX=1 option. That will tell the engine to treat
intermixed data types in the same column(numbers, text...) as text.

If you don't use it and have two different data types on the same column
an exception is bound to happen. For more info visit here

As a different suggestion Try not using a data adapter but create a class
to mimic the file structure and use a datareader to read the contents while making sure you ignore null/empty values/rows.
:)
   
Comments
madhusudhan.k 7-Dec-10 5:38am
   
Thanks a lot Moshu
madhusudhan.k 7-Dec-10 6:02am
   
Hi Moshu,
This is my conection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1"

but Im gettg exception as: could not find installable ISAM.

Please can help me.

Thanks
Madhusudhan
Dan Mos 7-Dec-10 6:26am
   
Ahh, 64 bit OS? That's the problem. There's a fix for that. Specifically compile your code for x86.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900