locked
Importing excel data directly into an ASP form RRS feed

  • Question

  • User-2072575867 posted

    Is there a way to import data in an excel sheet and have this data populate an asp input form (i.e. - textboxes, etc...)? I am NOT interested in importing excel data directly into a sql database, the reason being I want the user to verify the data before it goes into the database.

    Presently, the user populates an existing worksheet then inputs this data into an asp form. These steps are redunant but for tracking purposes, but I can not get rid of using excel. Any guidance on this issue would be appreciated.

     

     

    Tuesday, December 11, 2007 1:41 PM

Answers

  • User2022958948 posted

    Hi,

    Based on my understanding, you want to get data from Excel files directly and display the data on the web page. If I have misunderstood you, please feel free to let me know.

    To achieve this, you can use OLE flexiblly. On the other hand, you can also use Microsoft.Office.Interop.Excel. There is a sample as below you can try.

    using Microsoft.Office.Interop.Excel;
    using System.Text;
    using System.Reflection;

     public DataSet GetExcel(string fileName)
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;
            Range oRng;
            try 
            {
                //  creat a Application object
                oXL = new ApplicationClass();
                //   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
    
                //   get   WorkSheet object 
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                DataRow dr;
    
                StringBuilder sb = new StringBuilder();
                int jValue = oSheet.UsedRange.Cells.Columns.Count;
                int iValue = oSheet.UsedRange.Cells.Rows.Count;
                //  get data columns
                for (int j = 1; j <= jValue; j++)
                {
                    dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
                }
    
                //string colString = sb.ToString().Trim();
                //string[] colArray = colString.Split(':');
    
                //  get data in cell
                for (int i = 1; i <= iValue; i++)
                {
                    dr = ds.Tables["dtExcel"].NewRow();
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                        string strValue = oRng.Text.ToString();
                        dr["column" + j] = strValue;
                    }
                    ds.Tables["dtExcel"].Rows.Add(dr);
                }
                return ds;
            }
            catch (Exception ex)
            {
                Label1.Text = "Error: ";
                Label1.Text += ex.Message.ToString();
                return null;
            }
            finally
            {
                Dispose();
            } 

     You can call this method like this:

            DataSet ds = GetExcel("c:\\abcd.xls");   
            GridView1.DataSource = ds;
            GridView1.DataBind();
     Hope this can help.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 13, 2007 1:28 AM
  • User1802045989 posted

    You can read data from Excel in the same way as you do from the database like sql server using ODBC. Go thru this link on knowing how to use ODBC

    http://www.vbdotnetheaven.com/Uploadfile/mahesh/OdbcDotNetDataProvider04252005030215AM/OdbcDotNetDataProvider.aspx

     

    You will be able to use all the normal stuff like the datasets and datareaders and get the data from the excel file.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 13, 2007 1:52 AM
  • User-2072575867 posted

    Hi,

    Thank you for your help. I figured it out. I downloaded XP PIA using the following link:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

    It downloaded all the office .dlls including Excel and it worked great.

    Thanks again. 

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 20, 2007 8:20 PM

All replies

  • User-1655763558 posted

     You can use a datareader to read the Excel values in, no different than if the values come from a database.  you can then populate your form controls while reading through the datareader.

    Tuesday, December 11, 2007 1:59 PM
  • User2022958948 posted

    Hi,

    Based on my understanding, you want to get data from Excel files directly and display the data on the web page. If I have misunderstood you, please feel free to let me know.

    To achieve this, you can use OLE flexiblly. On the other hand, you can also use Microsoft.Office.Interop.Excel. There is a sample as below you can try.

    using Microsoft.Office.Interop.Excel;
    using System.Text;
    using System.Reflection;

     public DataSet GetExcel(string fileName)
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;
            Range oRng;
            try 
            {
                //  creat a Application object
                oXL = new ApplicationClass();
                //   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
    
                //   get   WorkSheet object 
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                DataRow dr;
    
                StringBuilder sb = new StringBuilder();
                int jValue = oSheet.UsedRange.Cells.Columns.Count;
                int iValue = oSheet.UsedRange.Cells.Rows.Count;
                //  get data columns
                for (int j = 1; j <= jValue; j++)
                {
                    dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
                }
    
                //string colString = sb.ToString().Trim();
                //string[] colArray = colString.Split(':');
    
                //  get data in cell
                for (int i = 1; i <= iValue; i++)
                {
                    dr = ds.Tables["dtExcel"].NewRow();
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                        string strValue = oRng.Text.ToString();
                        dr["column" + j] = strValue;
                    }
                    ds.Tables["dtExcel"].Rows.Add(dr);
                }
                return ds;
            }
            catch (Exception ex)
            {
                Label1.Text = "Error: ";
                Label1.Text += ex.Message.ToString();
                return null;
            }
            finally
            {
                Dispose();
            } 

     You can call this method like this:

            DataSet ds = GetExcel("c:\\abcd.xls");   
            GridView1.DataSource = ds;
            GridView1.DataBind();
     Hope this can help.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 13, 2007 1:28 AM
  • User1802045989 posted

    You can read data from Excel in the same way as you do from the database like sql server using ODBC. Go thru this link on knowing how to use ODBC

    http://www.vbdotnetheaven.com/Uploadfile/mahesh/OdbcDotNetDataProvider04252005030215AM/OdbcDotNetDataProvider.aspx

     

    You will be able to use all the normal stuff like the datasets and datareaders and get the data from the excel file.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 13, 2007 1:52 AM
  • User-2072575867 posted

    Thank you for your reply, I took your advise and I am using a datareader to find my values and populate my controls. I have an excel sheet (c"\asp.xls") with a  Range named "First". The named range only contains one cell value, I am able to get this value and popluate my Textbox "Textbox1" as shown below.

     Here is my question. Since I want to find the values of multiple ranges within this worksheet -named "Sheet1", how can I accomplish this?

    Ideally, I want to select the entire the sheet then tie mulitple textboxes to several ranges within the same worksheet. I have tried setting: SQLString= "Select FROM [First]" but I am stuck at setting the textbox.text to the ranges? Any help would be appreciated.

    1    <%@ Page Language="VB" %>
    2    <%@ Import Namespace= "System.Data.OleDb" %>

    3   
    4    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    5   
    6    <script runat="server">
    7        Dim DbConnection As OleDbConnection
    8        Dim DbCommand As OleDbCommand
    9        Dim DbReader As OleDbDataReader
    10       Dim SQLString As String
    11      
    12       Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    13           DbConnection = New OleDbConnection( _
    14           "Provider= Microsoft.Jet.OleDb.4.0;" & _
    15           "Data Source = c:\asp.xls;" & _
    16           "Extended Properties='Excel 8.0;HDR=NO;IMEX=1'")
    17          
    18           DbConnection.Open()
    19          
    20           SQLString = "SELECT * FROM [First]"
    21          
    22           DbCommand = New OleDbCommand(SQLString, DbConnection)
    23          
    24           DbReader = DbCommand.ExecuteReader()
    25          
    26           DbReader.Read()
    27          
    28           TextBox1.Text = DbReader.Item(0).ToString
    29               
    30           DbReader.Close()
    31           DbConnection.Close()
    32          
    33          
    34       End Sub
    35   </script>
    36  
    37   <html xmlns="http://www.w3.org/1999/xhtml" >
    38   <head runat="server">
    39       <title>Untitled Page</title>
    40   </head>
    41   <body>
    42       <form id="form1" runat="server">
    43       <div>
    44           <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 100; left: 23px; ;
    45               top: 47px"
    ></asp:TextBox>
    46            
    47      
    48       </div>
    49       </form>
    50   </body>
    51   </html>

     
    Monday, December 17, 2007 5:35 PM
  • User2022958948 posted

    Hi,

    If you want to get the data from Sheet of Excel, you can try my codes provided above. It can define the Sheet Object which you can get the data by.

    Hope this can help.

    Monday, December 17, 2007 8:30 PM
  • User-2072575867 posted

    Vince,

    Newbie Questions: Using the Interop method you mentioned earlier and I download the Interop.Excel.dll file:

    1. Will I need to do upload the dll file to the server when this application goes live?

     2. If so, how?

    3. If this file is uploaded to the server, will the end user need this .dll file?

    Tuesday, December 18, 2007 11:03 AM
  • User2022958948 posted

    Hi,

    Q1: Yes, but you needn't download this dll file which is in the Visual Stadio. You can add it as reference.

    Q2: There are several steps as below.

    1. In Visual Stadio, right-click your web site in the Solution Explorer.

    2. Choose "Add Reference".

    3. In ".NET" tab, please choose "Microsoft.Office.Interop.Excel". And click "OK".

    4. Please add "using Microsoft.Office.Interop.Excel;" into your page using Excel Object.

    After that, you can achieve using Excel Object and client needn't download this dll file which is just on the server.

    Q3: The end users needn't the dll file which can be added in your website.

    Hope this help you.

     

     

    Tuesday, December 18, 2007 8:23 PM
  • User-2072575867 posted

    Thank you very much for sharing your knowlege on this issue.

    I attempted to add the reference in visual Web Developer 2005 but under the .NET tab I did not see "Microsoft.Office.Interop.Excel" listing. I went into the COM Tab and added the following items (I am not sure if this helps?):

    Micrsoft Office 11.0 Object Library

    Microsoft Excel 8.0 Object Library

    After this process, I attempted to import the namespace but the only option I have is Microsoft.Office.Core 

    I think it has something to do with the fact I am using an older version of Excel (97 SR-2)?

    Is there a web reference I could use? or other option? 

    Thank you again. 

     

    Wednesday, December 19, 2007 11:04 AM
  • User2022958948 posted

    Hi,

    If you add Microsoft Excel 8.0 Object Library, it will add Office.Core.dll and Office.Interop.Excel.dll as references. If the Office.Interop.Excel.dll is not added,

    as far as I know, it's associated with the Microsoft Office edition you installed. You have to install Office Excel 2003 or above. To download Office.Interop.Excel.dll, you can check the below:

    Download URL:  http://www.dll4you.com/download/get.php?file=microsoft.office.interop.excel.dll&site=13&ver=11.0.5530&com=Microsoft%20Corporation&des=Microsoft.Office.Interop.Excel  It's not free.(I'm sorry I haven't found free link.)

    Wednesday, December 19, 2007 9:05 PM
  • User-2072575867 posted

    Hi,

    Thank you for your help. I figured it out. I downloaded XP PIA using the following link:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

    It downloaded all the office .dlls including Excel and it worked great.

    Thanks again. 

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 20, 2007 8:20 PM
  • User-842335439 posted

    Suppose that one column contains name of the countries & other column contains their national flags in the icon form. now i want imports data from the sheet into dataset along with icons. please help me.

    Thursday, December 27, 2007 2:13 AM
  • User-1029853154 posted

    Sorry to revive an old thread, but should I instead post this in a new thread? Let me know if so.

     

    I have tried converting the c# code provided by Vince Xu into vb.net, although have stumbled onto a few issues. Would someone please kindly help me out and find out why this translated code will not function correctly. Having an issue on line 40, 44 and 61,62.

    Line 40 error: Variable 'j' hides a variable in an enclosing block.

    Line 44 error:  Overload resolution failed because no accessible 'Item' can be called without a narrowing conversion:
        'Public Default Property Item(columnName As String) As Object': Argument matching parameter 'columnName' narrows from 'Double' to 'String'.
        'Public Default Property Item(columnIndex As Integer) As Object': Argument matching parameter 'columnIndex' narrows from 'Double' to 'Integer'.

    Line 61&62 error: Declartion expected.

     

    Imports Microsoft.Office.Interop.Excel
    Imports System.Data
    Imports System.Text
    Imports System.Reflection

    Partial Class Default3
    Inherits System.Web.UI.Page

    Public Function GetExcel(ByVal fileName As String) As DataSet
    Dim oXL As Application
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim oRng As Range
    Try
    ' creat a Application object
    oXL = New ApplicationClass
    ' get WorkBook object
    oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)
    ' get WorkSheet object
    oSheet = CType(oWB.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
    Dim dt As System.Data.DataTable = New System.Data.DataTable("dtExcel")
    Dim ds As DataSet = New DataSet
    ds.Tables.Add(dt)
    Dim dr As DataRow
    Dim sb As StringBuilder = New StringBuilder
    Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
    Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
    ' get data columns
    Dim j As Integer = 1
    Do While (j <= jValue)
    dt.Columns.Add(("column" + j), System.Type.GetType("System.String"))
    j = (j + 1)
    Loop
    'string colString = sb.ToString().Trim();
    'string[] colArray = colString.Split(':');
    ' get data in cell

    Dim i As Integer = 1
    Do While (i <= iValue)
    dr = ds.Tables("dtExcel").NewRow
    Dim j As Integer = 1
    Do While (j <= jValue)
    oRng = CType(oSheet.Cells(i, j), Microsoft.Office.Interop.Excel.Range)
    Dim strValue As String = oRng.Text.ToString
    dr(("column" + j)) = strValue
    j = (j + 1)
    Loop
    ds.Tables("dtExcel").Rows.Add(dr)
    i = (i + 1)
    Loop
    Return
    ds
    Catch ex As Exception
    Label1.Text = "Error: "
    Label1.Text = (Label1.Text + ex.Message.ToString)
    Return Nothing
    Finally

    Dispose()
    End Try
    End Function

    Dim
    ds As DataSet = GetExcel("c:\\abcd.xls")
    GridView1.DataSource = ds
    GridView1.DataBind

    End Class

      

    Many Thanks..

    Eldin

    Sunday, August 17, 2008 7:49 AM
  • User-1951191914 posted

     

    The Method oXL.Workbooks.Open(...) is taking a lot of time for me.... I checked and it takes exactly 8.16 seconds or so. Could you please guide me through this issue, I think it is to do with Reflection, as you are giving Missing.Value as the argument, may be I am wrong, Please help.
    Friday, October 3, 2008 10:32 AM
  • User-852578096 posted

    Hi,

    I used the above function in a class, and compiled the class in the DLLs which I am using in my project. However the Dispose command in the finally section is giving an error. 


    And if I work without the dispose clause, it creates a multiple instances of Mcrosoft Excel on Server. I tried to write the dispose command on the page, but it didnt worked.


    Class Definition :



    public class Helpers
    {
            public DataSet GetExcel(string fileName)
            {
                try
                {
                    Application oXL;
                    Workbook oWB;
                    Worksheet oSheet;
                    Range oRng;
    
                    //  creat a Application object
                    oXL = new Application();
    
    
                    //   get   WorkBook  object
                    oWB = oXL.Workbooks.Open(fileName);
    
                    //   get   WorkSheet object 
                    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                    System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    DataRow dr;
    
                    StringBuilder sb = new StringBuilder();
                    int jValue = oSheet.UsedRange.Cells.Columns.Count;
                    int iValue = oSheet.UsedRange.Cells.Rows.Count;
    
                    //  get data columns
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
                        string strValue = oRng.Text.ToString();
                        dt.Columns.Add(strValue, System.Type.GetType("System.String"));
                    }
    
                    //string colString = sb.ToString().Trim();
                    //string[] colArray = colString.Split(':');
    
                    //  get data in cell
                    for (int i = 2; i <= iValue; i++)
                    {
                        dr = ds.Tables["dtExcel"].NewRow();
                        for (int j = 1; j <= jValue; j++)
                        {
                            oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                            string strValue = oRng.Text.ToString();
                            dr[j - 1] = strValue;
    
                       }
                        ds.Tables["dtExcel"].Rows.Add(dr);
                    }
                    return ds;
                }
                catch (Exception ex)
                {
                    Utilities.LogException(ex);
                    return null;
    
                }
                finally
                {
                    //dispose the objects
                }
            }
        }




    public class Helpers
        {
            /// <summary>
            /// DataSet ds = GetExcel("c:\\abcd.xls");   
            /// GridView1.DataSource = ds;
            /// GridView1.DataBind();
            /// </summary>
            public DataSet GetExcel(string fileName)
            {
                try
                {
                    Application oXL;
                    Workbook oWB;
                    Worksheet oSheet;
                    Range oRng;
                    //  creat a Application object
                    oXL = new Application();
                    //   get   WorkBook  object
                    oWB = oXL.Workbooks.Open(fileName);
                    //   get   WorkSheet object 
                    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                    System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    DataRow dr;
                    StringBuilder sb = new StringBuilder();
                    int jValue = oSheet.UsedRange.Cells.Columns.Count;
                    int iValue = oSheet.UsedRange.Cells.Rows.Count;
                    //  get data columns
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
                        string strValue = oRng.Text.ToString();
                        dt.Columns.Add(strValue, System.Type.GetType("System.String"));
                    }
                    //string colString = sb.ToString().Trim();
                    //string[] colArray = colString.Split(':');
                    //  get data in cell
                    for (int i = 2; i <= iValue; i++)
                    {
                        dr = ds.Tables["dtExcel"].NewRow();
                        for (int j = 1; j <= jValue; j++)
                        {
                            oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                            string strValue = oRng.Text.ToString();
                            dr[j - 1] = strValue;
                        }
                        ds.Tables["dtExcel"].Rows.Add(dr);
                    }
                    return ds;
                }
                catch (Exception ex)
                {
                    Utilities.LogException(ex);
                    return null;
                }
                finally
                {
                    //dispose the objects
                }
            }
        }


    and on the page i am writing the following code:

    protected void Button1_Click(object sender, EventArgs e)
    {
            Helpers hlp = new Helpers();
            System.Data.DataSet ds = hlp.GetExcel(@"F:\Students.xls");
            GridView1.DataSource = ds;
            GridView1.DataBind();
            Dispose();
    }
    
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
            Helpers hlp = new Helpers();
            System.Data.DataSet ds = hlp.GetExcel(@"F:\Students.xls");
            GridView1.DataSource = ds;
            GridView1.PageIndex = e.NewPageIndex;
            GridView1.DataBind();
            Dispose();
    }



    Wednesday, February 3, 2010 3:37 PM
  • User-1865318666 posted

    Change:

     System.Data.DataSet ds = hlp.GetExcel(@"F:\Students.xls"); 


    To:

     System.Data.DataSet ds = hlp.GetExcel(Server.MapPath("F:\Students.xls")); 


    Friday, April 23, 2010 7:52 PM
  • User1044073608 posted

    It is Fine Working while running in localhost(VS2008)

    but if i tried this on iis it is not working

    Please Update me

    Thank you

    Saturday, May 29, 2010 2:55 PM
  • User1044073608 posted

    It is Fine Working while running in localhost(VS2008)

    but if i tried the in iis it is not working

    Please Update me

    Thank you

    Hi,

    Based on my understanding, you want to get data from Excel files directly and display the data on the web page. If I have misunderstood you, please feel free to let me know.

    To achieve this, you can use OLE flexiblly. On the other hand, you can also use Microsoft.Office.Interop.Excel. There is a sample as below you can try.

    using Microsoft.Office.Interop.Excel;
    using System.Text;
    using System.Reflection;

     public DataSet GetExcel(string fileName)
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;
            Range oRng;
            try 
            {
                //  creat a Application object
                oXL = new ApplicationClass();
                //   get   WorkBook  object
                oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
    
                //   get   WorkSheet object 
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                DataRow dr;
    
                StringBuilder sb = new StringBuilder();
                int jValue = oSheet.UsedRange.Cells.Columns.Count;
                int iValue = oSheet.UsedRange.Cells.Rows.Count;
                //  get data columns
                for (int j = 1; j <= jValue; j++)
                {
                    dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
                }
    
                //string colString = sb.ToString().Trim();
                //string[] colArray = colString.Split(':');
    
                //  get data in cell
                for (int i = 1; i <= iValue; i++)
                {
                    dr = ds.Tables["dtExcel"].NewRow();
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                        string strValue = oRng.Text.ToString();
                        dr["column" + j] = strValue;
                    }
                    ds.Tables["dtExcel"].Rows.Add(dr);
                }
                return ds;
            }
            catch (Exception ex)
            {
                Label1.Text = "Error: ";
                Label1.Text += ex.Message.ToString();
                return null;
            }
            finally
            {
                Dispose();
            } 

     You can call this method like this:

            DataSet ds = GetExcel("c:\\abcd.xls");   
            GridView1.DataSource = ds;
            GridView1.DataBind();

     Hope this can help.


    Saturday, May 29, 2010 2:57 PM