How to display Excel data in HTML table using MVC

Question

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 6-Dec-10 2:17am

madhusudhan.k

Updated 6-Dec-10 6:49am

fjdiewornncalwe

v4

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dan Mos · Answer 1 · 2010-12-06T23:32:00

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.
:)