This code sample shows how to read an XLS file (Excel) using the OleDb Managed Provider, and then display it, using a DataGrid. For this particular sample, after downloading the Lotto Texas Results in xls file format, I just trimmed it a little for speed and ease of viewing. If no other .xls file is available, you can download the full file here:
Download Lottery file
(You might need to right click on the link and ‘select target’ to download it, if you have Excel installed on your computer)
To make this work on your system, just adjust the path to match your needs on your system.
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <meta name="GENERATOR" Content="ASP Express 2.1"> <title>Displaying an XLS file in a DataGrid</title> <script language="VB" runat="server"> dim x as integer Sub Page_Load(Source as Object, E as EventArgs) GetXLS() End Sub Sub GetXLS() Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\lotto.xls;Extended Properties=Excel 8.0;" Dim MySQL as String="Select * from [lotto$]" Dim Conn As New OleDb.OleDbConnection(strConn) Dim da As New System.Data.OleDb.OleDbDataAdapter(MySQL, Conn) Dim ds As New DataSet() Conn.Open() da.Fill(ds, "lotto") MyDataGrid.Datasource=ds.Tables(0).DefaultView MyDataGrid.DataBind conn.close() End Sub Sub DataBound (s as Object, e as DatagridItemEventArgs) Select Case e.Item.ItemType Case ListItemType.Header e.Item.Cells(0).Text="<b>Month</b>" e.Item.Cells(1).Text="<b>Day</b" e.Item.Cells(2).Text="<b>Year</b>" e.Item.Cells(3).Text="<b>First</b>" e.Item.Cells(4).Text="<b>Second</b>" e.Item.Cells(5).Text="<b>Third</b>" e.Item.Cells(6).Text="<b>Fourth</b>" e.Item.Cells(7).Text="<b>Fifth</b>" e.Item.Cells(8).Text="<b>Sixth</b>" For x=0 to 8 e.item.cells(x).Font.Size= FontUnit.Point (12) next Case ListItemType.Item, ListItemType.AlternatingItem For x=0 to 8 e.item.Cells(x).HorizontalAlign=HorizontalAlign.Center e.item.cells(x).Font.Size= FontUnit.Point (10) next End Select End Sub </script> </head> <body> <div align="center"> <form runat="server" method="post"> <b><font Size="5" Color="#0000FF" Face="Arial">Lotto Texas Results</font></b> <asp:Datagrid runat="server" Id="MyDataGrid" GridLines="Both" cellpadding="2" cellspacing="0" Headerstyle-BackColor="#8080C0" Headerstyle-Font-Name="Arial" BackColor="#8080FF" Font-Name="Arial" Font-Size="10" AlternatingItemStyle-BackColor="#C0C0C0" AlternatingItemStyle-Font-Name="Arial" AlternatingItemStyle-Font-Size="11" BorderColor="Black" OnItemDataBound="DataBound"> </asp:DataGrid> </form> </div></body> </html>