Displaying Excel data in a table

badgolfer · October 30th, 2003, 12:39 AM

Hi

I need to display a simple Excel spreadsheet as a
table within an ASP.NET webform.

Is there an easy way of doing this?

I Know I can save an Excel spreadsheet as HTML format.
But... I need to have other functionality on my webform
so I need to have the Excel spreadsheet page as a form
component - ie a table or a grid would do.

Is there a component that allows me to import Excel
data? Which I can then bind to a table or grid?

Any help or suggestions much appreciated.

Cheers
Chas from Canterbury, UK
:)

stu9820 · October 30th, 2003, 09:14 AM

You must highlight all cells you are going to use and name it. In the example it is named 'Dell'

Sub Page_Load(Source As Object, E As EventArgs)
  Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                  "data source=C:\BegASPNETdb\datastores\inventory.xls;" & _
                  "Extended Properties=Excel 8.0;"
  Dim objConnection As New OleDbConnection(strConnection)

  Dim strSQL As String = "SELECT * FROM Items WHERE Source='Dell'"
  Dim objCommand As New OleDbCommand(strSQL, objConnection)

  objConnection.Open()
  dgInventory.DataSource = objCommand.ExecuteReader()
  dgInventory.DataBind()
  objConnection.Close()
End Sub

stu9820 · October 30th, 2003, 09:15 AM

HTML should look like this:

<html>
<head>
    <title>Reading from an Excel Workbook</title>
</head>

<body>
    <H3>Reading from an Excel Workbook</H3>
    <asp:DataGrid id="dgInventory" runat="server" />
</body>
</html>

You will also need to import these

System.Data
System.Data.OleDb

mahulda · October 4th, 2004, 05:43 AM

Quote:

quote:Originally posted by stu9820
You must highlight all cells you are going to use and name it. In the example it is named 'Dell'

Sub Page_Load(Source As Object, E As EventArgs)
Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"data source=C:\BegASPNETdb\datastores\inventory.xls;" & _
"Extended Properties=Excel 8.0;"
Dim objConnection As New OleDbConnection(strConnection)

Dim strSQL As String = "SELECT * FROM Items WHERE Source='Dell'"
Dim objCommand As New OleDbCommand(strSQL, objConnection)

objConnection.Open()
dgInventory.DataSource = objCommand.ExecuteReader()
dgInventory.DataBind()
objConnection.Close()
End Sub

Can you tell me how the Excel Sheet would be for the code you gave
This is what i used
The name of the file is Inventory.xls
Items is Cell A1 of the spreadsheet

Items Price
Item3 20
Item1 30
Item2 34

I get the following error
The Microsoft Jet database engine could not find the object 'Items'. Make sure the object exists and that you spell its name and the path name correctly.

-.. -..

mahulda · October 5th, 2004, 05:00 AM

I have resolved my problem.

But now i have a new issue.
I run my program and display the table using a datagrid.
Now if i want to open my Excel document theres an error saying the file inventory.xls can not be opened

-.. -..

elghoul_girl · October 31st, 2005, 06:07 AM

sorry guys may anyone please help me? i am trying to display the content of an excel sheet in a datagrid and i got this error

The Microsoft Jet database engine could not find the object 'myRange'. Make sure the object exists and that you spell its name and the path name correctly.

klim · January 6th, 2006, 04:25 AM

Mahulda, could you please tell how you resolved this problem:

The Microsoft Jet database engine could not find the object 'Items'. Make sure the object exists and that you spell its name and the path name correctly.

Thanks,
klim