Default Displaying Excel In A Datagrid Issue

[picassosson]

I am trying to display an excel spreadsheet on a page using datagrid, but for some reason it is filtering out some of the cells, leaving a blank space instead. Also, the "$" sign won't show up in most of the columns.Could someone please take a look at my site and file, and give a suggestion as to why this would be happening? I've tried a bunch of troubleshooting on it, with no success.


Web Page (Not all excel cells load):

http://www.toddrawson.com/excel_aspnet/index6.aspx

Excel File:

http://www.toddrawson.com/excel_aspnet/2DLegalTender.xls

Code For Datagrid:

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>

<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim objDataSet As New DataSet()

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(".") & "\2DLegalTender.xls;" & _
"Extended Properties=""Excel 8.0;"""


Dim objDataAdapter As New OledbDataAdapter("SELECT * FROM [Sheet1$]", strConn)

objDataAdapter.Fill(objDataSet)

DataGrid1.DataSource = objDataSet.Tables(0).DefaultView
DataGrid1.DataBind()
End Sub
</script>

  	<p><asp:Label id=Label1 runat="server"><B></B></asp:Label></p>
<asp:datagrid id="DataGrid1" runat="server" 
borderstyle="Ridge"
borderwidth="2px" 
cellpadding="3"
cellspacing="1" 
bordercolor="White" 
backcolor="White"
gridlines="None">
<selecteditemstyle font-bold="True" forecolor="White"
backcolor="#9471DE">
</selecteditemstyle>
<itemstyle borderwidth="1px" 
forecolor="Black"
borderstyle="Solid" 
bordercolor="#FF8000" 
backcolor="#DEDFDE">
</itemstyle>
<headerstyle font-bold="True" borderwidth="1px"
forecolor="#E7E7FF" borderstyle="Double" bordercolor="DarkSlateBlue"
backcolor="#4A3C8C"></headerstyle>
<footerstyle forecolor="Black"
backcolor="#C6C3C6"></footerstyle>
<pagerstyle horizontalalign="Right" forecolor="Black"
backcolor="#C6C3C6"></pagerstyle>

</asp:datagrid></p>

[CroCrew]

Hello picassosson,

Give this a try:

Change your data grid to this:

<asp:datagrid id="DataGrid1" runat="server" AutoGenerateColumns="False" >
    <Columns>
        <asp:BoundColumn DataField="F1" HeaderText="Field 1" />
        <asp:BoundColumn DataField="F2" HeaderText="Field 2" DataFormatString="{0:c}" />
        <asp:BoundColumn DataField="F3" HeaderText="Field 3" />
        <asp:BoundColumn DataField="F4" HeaderText="Field 4" />
        <asp:BoundColumn DataField="F5" HeaderText="Field 5" />
        <asp:BoundColumn DataField="F6" HeaderText="Field 6" />
        <asp:BoundColumn DataField="F7" HeaderText="Field 7" DataFormatString="{0:c}" />
    </Columns>
</asp:datagrid>

Then change your on load event to this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
	Dim objDataSet As New DataSet()
	Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("Includes") & "\2DLegalTender.xls;Extended Properties='Excel 8.0; HDR=No; IMEX=1'"
	Dim objDataAdapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)

	objDataAdapter.Fill(objDataSet)

	DataGrid1.DataSource = objDataSet.Tables(0)
	DataGrid1.DataBind()
End Sub

Happy Coding,
CroCrew~