importing from excel to gridview

Discussion in 'ASP .Net Web Controls' started by Morris Neuman, Dec 1, 2008.

  1. Hi,

    Is there a command on a .net form that can be initiated to import a
    spreadsheet to a gridview? I would like to have a button on a form that when
    clicked, a spreadsheet is imported and displayed on a gridview.

    If you can send link with info or provide info that I can use to research
    further, I would appreciate it. If you can provide genric code that would be
    great.
    --
    Thanks
    Morris
     
    Morris Neuman, Dec 1, 2008
    #1
    1. Advertising

  2. Hello Morris

    In order to import a spreadsheet to a GridView, you may consider using
    OLEDB. Here is the example code for your reference:
    http://www.codeproject.com/KB/grid/GridView_ImportExport.aspx

    <quote>
    string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
    ("Data Source=C:\\path\\test.xls;" +
    // or use instead of Excel 8.0 - Excel 5.0
    "Extended Properties=\"Excel 8.0;\""));
    string SSQL = "SELECT name , dept, salary from [sheet1$]";

    // here use oleDataReader
    OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);

    DataSet ds = new DataSet();
    //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    oleDA.Fill(ds);
    GridView1.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
    GridView1.DataBind();
    </quote>

    The idea is to read the spreadsheet data into a DataTable, then bind the
    table to GridView.

    Please note:
    You may get the suggestion of using Office automation to accomplish the
    task. Office automation is not supported to be used in ASP.NET:
    http://support.microsoft.com/kb/257757/
    Thus, we cannot use Office automation in this case.

    Please let me know if you have any other concerns, or need anything else.

    Regards,
    Jialiang Ge (, remove 'online.')
    Microsoft Online Community Support

    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

    MSDN Managed Newsgroup support offering is for non-urgent issues where an
    initial response from the community or a Microsoft Support Engineer within
    2 business day is acceptable. Please note that each follow up response may
    take approximately 2 business days as the support professional working with
    you may need further investigation to reach the most efficient resolution.
    The offering is not appropriate for situations that require urgent,
    real-time or phone-based interactions. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Jialiang Ge [MSFT], Dec 2, 2008
    #2
    1. Advertising

  3. Thanks for the quick response.

    I tried the code referenced in your email but get an error.

    I am using a master page and the import form is on the content page. As I
    do not have a code behind page but a single aspx page, I included the
    following in my MasterPage1.master
    <%@ Import Namespace="System.Data.OleDb" %>
    <%@ Import Namespace="System.IO" %>

    I then copied your code on to a test.aspx page with the Button3_Click as
    follows:
    protected void Button3_Click(object sender, EventArgs e)
    {
    string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
    ("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
    // or use instead of Excel 8.0 - Excel 5.0
    "Extended Properties=\"Excel 8.0;\""));
    string SSQL = "SELECT name , dept, salary from [sheet1$]";

    // here use oleDataReader
    OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
    DataSet ds = new DataSet();
    //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    oleDA.Fill(ds);
    GridView2.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
    GridView2.DataBind();

    }

    When I run the code I am getting the error:
    Compilation Error
    Description: An error occurred during the compilation of a resource required
    to service this request. Please review the following specific error details
    and modify your source code appropriately.

    Compiler Error Message: CS0246: The type or namespace name
    'OleDbDataAdapter' could not be found (are you missing a using directive or
    an assembly reference?)

    Source Error:
    Line 32:
    Line 33: // here use oleDataReader
    Line 34: OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
    Line 35: DataSet ds = new DataSet();
    Line 36: //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    Source File:
    c:\Inetpub\wwwroot\CMWebManager\SystemAdminOnly\Copies\Test.aspx Line: 34


    I have never worked with OleDbDataAdapter.

    What am I doing wrong?
    Do I need to have a code behind file?
    --
    Thanks for your time and help.
    Morris


    ""Jialiang Ge [MSFT]"" wrote:

    > Hello Morris
    >
    > In order to import a spreadsheet to a GridView, you may consider using
    > OLEDB. Here is the example code for your reference:
    > http://www.codeproject.com/KB/grid/GridView_ImportExport.aspx
    >
    > <quote>
    > string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
    > ("Data Source=C:\\path\\test.xls;" +
    > // or use instead of Excel 8.0 - Excel 5.0
    > "Extended Properties=\"Excel 8.0;\""));
    > string SSQL = "SELECT name , dept, salary from [sheet1$]";
    >
    > // here use oleDataReader
    > OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
    >
    > DataSet ds = new DataSet();
    > //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    > oleDA.Fill(ds);
    > GridView1.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
    > GridView1.DataBind();
    > </quote>
    >
    > The idea is to read the spreadsheet data into a DataTable, then bind the
    > table to GridView.
    >
    > Please note:
    > You may get the suggestion of using Office automation to accomplish the
    > task. Office automation is not supported to be used in ASP.NET:
    > http://support.microsoft.com/kb/257757/
    > Thus, we cannot use Office automation in this case.
    >
    > Please let me know if you have any other concerns, or need anything else.
    >
    > Regards,
    > Jialiang Ge (, remove 'online.')
    > Microsoft Online Community Support
    >
    > Delighting our customers is our #1 priority. We welcome your comments and
    > suggestions about how we can improve the support we provide to you. Please
    > feel free to let my manager know what you think of the level of service
    > provided. You can send feedback directly to my manager at:
    > .
    >
    > ==================================================
    > Get notification to my posts through email? Please refer to
    > http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
    >
    > MSDN Managed Newsgroup support offering is for non-urgent issues where an
    > initial response from the community or a Microsoft Support Engineer within
    > 2 business day is acceptable. Please note that each follow up response may
    > take approximately 2 business days as the support professional working with
    > you may need further investigation to reach the most efficient resolution.
    > The offering is not appropriate for situations that require urgent,
    > real-time or phone-based interactions. Issues of this nature are best
    > handled working with a dedicated Microsoft Support Engineer by contacting
    > Microsoft Customer Support Services (CSS) at
    > http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    > ==================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
     
    Morris Neuman, Dec 2, 2008
    #3
  4. Hello Morris,

    You need to add include the directives

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDb" %>

    in your test.aspx file. It is not necessary to add the above into the
    master page if the master page itself does not use OleDbDataAdapater.

    To help you better understand why we need to add the imports in the
    test.aspx file, please have a look at this example:

    Suppose that we have a C# Dll project. In the project, we have Container.cs
    that defines a class "Container", and Component.cs that defines a class
    "Component".

    In Container, it stores a component:
    class Container
    {
    Component component = new Component();
    }

    In Component, it has a method that reads data from a spreadsheet with OLEDB:

    class Component
    {
    public void ProcessData()
    {
    string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
    ("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
    // or use instead of Excel 8.0 - Excel 5.0
    "Extended Properties=\"Excel 8.0;\""));
    string SSQL = "SELECT name , dept, salary from [sheet1$]";

    // here use oleDataReader
    OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
    DataSet ds = new DataSet();
    //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    oleDA.Fill(ds);
    }
    }

    In this example, if we add
    using System.Data.OleDb;
    using System.Data;
    only to Container.cs, we will get several compilation errors saying that
    OleDbDataAdapter could not be found, (are you missing a using directive or
    an assembly reference?) as you saw.

    However, as long as we add
    using System.Data.OleDb;
    using System.Data;
    to Component.cs, the error is fixed.

    This Container.cs - Component.cs example is just like the Masterpage.master
    - test.aspx relationship in this case. Importing the namespaces solely in
    the master page does not "inherit" to the component pages (test.aspx),
    thus, we need to import the namespaces in the component pages.

    Any more clear? I hope that I'm not confusing you more. :)

    Regards,
    Jialiang Ge (, remove 'online.')
    Microsoft Online Community Support

    =================================================
    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    This posting is provided "AS IS" with no warranties, and confers no rights.
    =================================================
     
    Jialiang Ge [MSFT], Dec 3, 2008
    #4
  5. Thanks. Including the import namespace in the aspx page worked.

    Now I get this error:
    Server Error in '/CMWebManager' Application.
    --------------------------------------------------------------------------------

    No value given for one or more required parameters.
    Description: An unhandled exception occurred during the execution of the
    current web request. Please review the stack trace for more information about
    the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: No value given for one
    or more required parameters.

    Source Error:


    Line 44: oleDA = new OleDbDataAdapter(SSQL, conn);
    Line 45: //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    Line 46: oleDA.Fill(ds);
    Line 47: GridView2.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
    Line 48: GridView2.DataBind();

    Source File:
    c:\Inetpub\wwwroot\CMWebManager\SystemAdminOnly\Copies\Test.aspx Line: 46

    As I do not want to have the gridview prefilled per your example, I did not
    include the Page_Load or the fillGrid() events.

    My Button3_click code is as follows:
    protected void Button3_Click(object sender, EventArgs e)
    {
    string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
    ("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
    // or use instead of Excel 8.0 - Excel 5.0
    "Extended Properties=\"Excel 8.0;\""));
    string SSQL = "SELECT name , dept, salary from [sheet1$]";

    // here use oleDataReader
    OleDbDataAdapter oleDA = new OleDbDataAdapter();
    DataSet ds = new DataSet();
    oleDA = new OleDbDataAdapter(SSQL, conn);
    //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    oleDA.Fill(ds);
    GridView2.DataSource = ds.Tables[0].DefaultView; // or [ ds ]
    GridView2.DataBind();

    }

    The GridView2 is defined as per your sample code:
    <asp:GridView ID="GridView2" runat="server" BackColor="White"
    BorderColor="#E7E7FF"
    BorderStyle="None" BorderWidth="1px" CellPadding="3"
    GridLines="Horizontal">
    <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
    <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
    ForeColor="#F7F7F7" />
    <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C"
    HorizontalAlign="Right" />
    <HeaderStyle BackColor="#4A3C8C" Font-Bold="True"
    ForeColor="#F7F7F7" />
    <AlternatingRowStyle BackColor="#F7F7F7" />
    </asp:GridView>

    Three questions:
    1) why am I getting the error?
    2) where is the data table that is getting the data from the excel
    spreadsheet?
    3) The fillGrid() per your example initialiazes the gridview on pageload
    from an xml file - correct? If yes then how would the fillGrid change to get
    data from an Access table?

    I really appreciate your help.
    --
    Thanks
    Morris


    ""Jialiang Ge [MSFT]"" wrote:

    > Hello Morris,
    >
    > You need to add include the directives
    >
    > <%@ Import Namespace="System.Data" %>
    > <%@ Import Namespace="System.Data.OleDb" %>
    >
    > in your test.aspx file. It is not necessary to add the above into the
    > master page if the master page itself does not use OleDbDataAdapater.
    >
    > To help you better understand why we need to add the imports in the
    > test.aspx file, please have a look at this example:
    >
    > Suppose that we have a C# Dll project. In the project, we have Container.cs
    > that defines a class "Container", and Component.cs that defines a class
    > "Component".
    >
    > In Container, it stores a component:
    > class Container
    > {
    > Component component = new Component();
    > }
    >
    > In Component, it has a method that reads data from a spreadsheet with OLEDB:
    >
    > class Component
    > {
    > public void ProcessData()
    > {
    > string conn = ("Provider=Microsoft.Jet.OLEDB.4.0;" +
    > ("Data Source=C:\\Program Files\\CALLMaster\\Data\\test.xls;" +
    > // or use instead of Excel 8.0 - Excel 5.0
    > "Extended Properties=\"Excel 8.0;\""));
    > string SSQL = "SELECT name , dept, salary from [sheet1$]";
    >
    > // here use oleDataReader
    > OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
    > DataSet ds = new DataSet();
    > //oleDA.TableMappings.Add("Table","ExcelTest"); // Require
    > oleDA.Fill(ds);
    > }
    > }
    >
    > In this example, if we add
    > using System.Data.OleDb;
    > using System.Data;
    > only to Container.cs, we will get several compilation errors saying that
    > OleDbDataAdapter could not be found, (are you missing a using directive or
    > an assembly reference?) as you saw.
    >
    > However, as long as we add
    > using System.Data.OleDb;
    > using System.Data;
    > to Component.cs, the error is fixed.
    >
    > This Container.cs - Component.cs example is just like the Masterpage.master
    > - test.aspx relationship in this case. Importing the namespaces solely in
    > the master page does not "inherit" to the component pages (test.aspx),
    > thus, we need to import the namespaces in the component pages.
    >
    > Any more clear? I hope that I'm not confusing you more. :)
    >
    > Regards,
    > Jialiang Ge (, remove 'online.')
    > Microsoft Online Community Support
    >
    > =================================================
    > Delighting our customers is our #1 priority. We welcome your comments and
    > suggestions about how we can improve the support we provide to you. Please
    > feel free to let my manager know what you think of the level of service
    > provided. You can send feedback directly to my manager at:
    > .
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    > =================================================
    >
    >
    >
     
    Morris Neuman, Dec 4, 2008
    #5
  6. Hello Morris

    > 1) why am I getting the error?


    The error happens because the schema of the xls file is not right. It
    cannot find the column name, dept, salary in sheet1. The workbook in the
    example is created in this way:

    Open Excel. In the default "Sheet1" of the newly created workbook, input
    the data:

    A B C
    1 name dept salary
    2 test1 dep1 111
    3 test2 dep2 222

    Then save it as a xls in the specified path.

    > 2) where is the data table that is getting the data from the
    > excel spreadsheet?


    I'm not sure that I understand this question. The data is retrieved from
    Sheet1 (SELECT ... FROM [sheet1$]), and it's filled into our DataSet
    (DataSet ds = new DataSet();).

    > 3) The fillGrid() per your example initialiazes the gridview on
    > pageload from an xml file - correct? If yes then how would the fillGrid
    > change to get data from an Access table?


    Yes, fillGrid is used to initiate the content of GridView when the page is
    loaded (before the Button3 is clicked). To change it to read from an Access
    table, the code is almost the same as reading from a spreadsheet. Here is
    an example for your reference:

    http://www.codeproject.com/KB/database/adonet_datareader.aspx

    Have a nice week!

    Regards,
    Jialiang Ge (, remove 'online.')
    Microsoft Online Community Support

    =================================================
    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    This posting is provided "AS IS" with no warranties, and confers no rights.
    =================================================
     
    Jialiang Ge [MSFT], Dec 5, 2008
    #6
  7. Hello Morris,

    I am writing to check the status of the issue on your side. Would you mind
    letting me know the result of the suggestions? If you need further
    assistance, feel free to let me know. I will be more than happy to be of
    assistance.

    Have a great day!

    Regards,
    Jialiang Ge (, remove 'online.')
    Microsoft Online Community Support

    =================================================
    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    This posting is provided "AS IS" with no warranties, and confers no rights.
    =================================================
     
    Jialiang Ge [MSFT], Dec 10, 2008
    #7
  8. I found a detailed walkthrough of exactly what I was looking to do so am
    trying to work with that example.
    --
    Thanks for all your help.
    Morris


    ""Jialiang Ge [MSFT]"" wrote:

    > Hello Morris,
    >
    > I am writing to check the status of the issue on your side. Would you mind
    > letting me know the result of the suggestions? If you need further
    > assistance, feel free to let me know. I will be more than happy to be of
    > assistance.
    >
    > Have a great day!
    >
    > Regards,
    > Jialiang Ge (, remove 'online.')
    > Microsoft Online Community Support
    >
    > =================================================
    > Delighting our customers is our #1 priority. We welcome your comments and
    > suggestions about how we can improve the support we provide to you. Please
    > feel free to let my manager know what you think of the level of service
    > provided. You can send feedback directly to my manager at:
    > .
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    > =================================================
    >
    >
     
    Morris Neuman, Dec 16, 2008
    #8
  9. OK. If you meet with any problem with that example, please feel free to
    tell me.

    Regards,
    Jialiang Ge (, remove 'online.')
    Microsoft Online Community Support

    =================================================
    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    This posting is provided "AS IS" with no warranties, and confers no rights.
    =================================================
     
    Jialiang Ge [MSFT], Dec 17, 2008
    #9
  10. Morris Neuman

    Blessy Guest

    Hi,
    I want to export and import excel from and to datagrid using asp.net.
    Im able to export to excel without any issues. but when i try to import the
    exported excel file, its showing the error 'External table is not in the
    expected format.' Please help me.

    Thanks
    Blessy

    ""Jialiang Ge [MSFT]"" wrote:

    > OK. If you meet with any problem with that example, please feel free to
    > tell me.
    >
    > Regards,
    > Jialiang Ge (, remove 'online.')
    > Microsoft Online Community Support
    >
    > =================================================
    > Delighting our customers is our #1 priority. We welcome your comments and
    > suggestions about how we can improve the support we provide to you. Please
    > feel free to let my manager know what you think of the level of service
    > provided. You can send feedback directly to my manager at:
    > .
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    > =================================================
    >
    >
     
    Blessy, Dec 17, 2008
    #10
  11. Morris Neuman

    KurtWM Guest

    "Morris Neuman" wrote:
    Morris,

    Could you post that detailed walkthrough here, please?

    Thanks
    KurtWM


    > I found a detailed walkthrough of exactly what I was looking to do so am
    > trying to work with that example.
    > --
    > Thanks for all your help.
    > Morris
    >
    >
    > ""Jialiang Ge [MSFT]"" wrote:
    >
    > > Hello Morris,
    > >
    > > I am writing to check the status of the issue on your side. Would you mind
    > > letting me know the result of the suggestions? If you need further
    > > assistance, feel free to let me know. I will be more than happy to be of
    > > assistance.
    > >
    > > Have a great day!
    > >
    > > Regards,
    > > Jialiang Ge (, remove 'online.')
    > > Microsoft Online Community Support
    > >
    > > =================================================
    > > Delighting our customers is our #1 priority. We welcome your comments and
    > > suggestions about how we can improve the support we provide to you. Please
    > > feel free to let my manager know what you think of the level of service
    > > provided. You can send feedback directly to my manager at:
    > > .
    > >
    > > This posting is provided "AS IS" with no warranties, and confers no rights.
    > > =================================================
    > >
    > >
     
    KurtWM, Jan 26, 2009
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ed Bangle
    Replies:
    0
    Views:
    348
    Ed Bangle
    Nov 23, 2003
  2. Vivek
    Replies:
    1
    Views:
    1,385
  3. plb
    Replies:
    2
    Views:
    369
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,596
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. Replies:
    0
    Views:
    441
Loading...

Share This Page