Excel To DataTable - Adds an additional column F1

Posted by Sharpcnet under C# on 11/19/2013 | Points: 10 | Views : 1593 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I'm loading the excel records to a datatable. When this table is bound to a gridview, I see an additional column 'F1' and also an additional row with empty data. How can I prevent this.

For example, the excel sheet has these records:

Name L1 L2
----------------
abc 2 4
def 3 3

When bound to grid view, I see as an additional column(F1) and a row(3)

S.No Name L1 L2 F1
-------------------------
1 abc 2 4
2 def 3 3
3


ASPX: 

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="true">
 <columns>
   <asp:TemplateField HeaderText="S.No">
    <ItemTemplate>
      <%#Container.DataItemIndex+1%>
    </ItemTemplate>
   </asp:TemplateField>
 </columns>
</asp:GridView>

C# 

 DataTable dtExcel = new DataTable();
 DataTable dt = new DataTable();
 OleDbConnection conn = new OleDbConnection(connString);
 conn.Open(); 
 dtExcel = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
 string qry = "select * from [Sheet1$]";
 OleDbDataAdapter da = new OleDbDataAdapter(qry,conn);
 da.Fill(dt);
 conn.Close();
 gv.DataSource=dt;
 gv.DataBind();

Would just deleting the last column be a good approach.Why is it coming in the first place.
Another option would be deleting the column with name 'F1' in the DataTable, but what if that may also be a name of one of the original columns like L1, L2, ....

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Allemahesh on: 11/19/2013 [Member] [MVP] Silver | Points: 25

0	Dear Sharpcnet, Just replace your girdview with the below one and new gridview code:- AutoGenerateColumns="false" <asp:GridView ID="gv" runat="server" AutoGenerateColumns="false"> <Columns> <asp:TemplateField HeaderText="S.No"> <ItemTemplate> <%#Container.DataItemIndex+1%> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Name"> <ItemTemplate> <%# Eval("Name")%> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="L1"> <ItemTemplate> <%# Eval("L1")%> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="L2"> <ItemTemplate> <%# Eval("L2")%> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> Let me know if you have problems. Happy Coding, If it helps you or directs U towards the solution, MARK IT AS ANSWER Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Dear Sharpcnet,

Just replace your girdview with the below one and new gridview code:-

AutoGenerateColumns="false"

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false">

    <Columns>

        <asp:TemplateField HeaderText="S.No">

            <ItemTemplate>

                <%#Container.DataItemIndex+1%>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="Name">

            <ItemTemplate>

                <%# Eval("Name")%>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="L1">

            <ItemTemplate>

                <%# Eval("L1")%>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="L2">

            <ItemTemplate>

                <%# Eval("L2")%>

            </ItemTemplate>

        </asp:TemplateField>

    </Columns>

</asp:GridView>

Let me know if you have problems.

Happy Coding,
If it helps you or directs U towards the solution, MARK IT AS ANSWER

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 11/19/2013 [Member] Starter | Points: 25

0	What if the no. of columns to be bound is unknown. Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Allemahesh on: 11/19/2013 [Member] [MVP] Silver | Points: 25

0	If the no. of columns to be bound is unknown, then your gridview code is right. Like this:- <asp:GridView ID="gv" runat="server" AutoGenerateColumns="true"> <columns> <asp:TemplateField HeaderText="S.No"> <ItemTemplate> <%#Container.DataItemIndex+1%> </ItemTemplate> </asp:TemplateField> </columns> </asp:GridView> In this case, you need to be care full that your dt i.e datatable will contains only required column and rows. For that you need to alter your dt i.e datatable and delete the unwanted column and rows form that dt i.e datatable. Now the question you have asked that "deleting the column with name 'F1' in the DataTable, but what if that may also be a name of one of the original columns like L1, L2, ...." For this you need to check your dt and then delete ony those column that are not required. Let me know if you have problems. Happy Coding, If it helps you or directs U towards the solution, MARK IT AS ANSWER Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

If the no. of columns to be bound is unknown, then your gridview code is right.

Like this:-

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="true">

 <columns>

   <asp:TemplateField HeaderText="S.No">

    <ItemTemplate>

      <%#Container.DataItemIndex+1%>

    </ItemTemplate>

   </asp:TemplateField>

 </columns>

</asp:GridView>

In this case, you need to be care full that your dt i.e datatable will contains only required column and rows. For that you need to alter your dt i.e datatable and delete the unwanted column and rows form that dt i.e datatable.

Now the question you have asked that "deleting the column with name 'F1' in the DataTable, but what if that may also be a name of one of the original columns like L1, L2, ...."
For this you need to check your dt and then delete ony those column that are not required.

Let me know if you have problems.

Happy Coding,
If it helps you or directs U towards the solution, MARK IT AS ANSWER

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts