locked
Hyperlinks in Excel via OleDB RRS feed

  • Question

  • User-718146471 posted

    Using OleDB, I want to access the hyperlink part of the cell but I'm stumped because I can't figure this out. Here is what I have thus far. Hope someone can help here.

     protected void Page_Load(object sender, EventArgs e)
        {
            String strConn =
                "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + Server.MapPath("Data\\ExcelData.xls") + ";" +
                "Extended Properties=Excel 8.0;";
    
            DataSet ds = new DataSet();
            // You must use the $ after the object you reference for spreadsheet name
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT [Class],[Number] FROM [Sheet1$] where [Number] like '______'", strConn);
            //
    
            da.Fill(ds);
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();
    
        }
    

    The hyperlink is in the Number field. So using OleDB, can I do this?
     

    Monday, November 29, 2010 2:26 PM

Answers

  • User-821857111 posted

    Hyperlinks belong to Excel, and cannot be treated like a value. See what comes out via OleDb. If it's a URL as text, format it using String.Format() to create your own hyperlink, or apply it to the NavigateUrl property of a Hyperlink control.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 29, 2010 5:36 PM
  • User-1102123764 posted

    Hi,

    I see you have resolved your issue, would you please share the solution? 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 20, 2010 10:41 PM

All replies

  • User-821857111 posted

    Hyperlinks belong to Excel, and cannot be treated like a value. See what comes out via OleDb. If it's a URL as text, format it using String.Format() to create your own hyperlink, or apply it to the NavigateUrl property of a Hyperlink control.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 29, 2010 5:36 PM
  • User-718146471 posted

    What I get from Excel is the text of the field, not the URL. So essentially, I think I'm going to have to use Excel Interop. Unless someone can tell me a way through OleDB how to access the URL information contained in the cell. 

    Tuesday, November 30, 2010 7:32 AM
  • User-718146471 posted

    Ok, so I broke down and finally have settled on using Excel Interop. I have a code stub I picked up off the internet and it seems to do what it does really well. i haven't had much chance to really play with interop so I could use a hand deciphering what the various functions here do. What I want to do is: 

    1. Snag from Excel only the first two columns.

    2. The second column will have hyperlinks that I want to utilize.

    3. The 2nd column needs to use a 6 digit number as a wildcard qualifier.

    Can anyone help me?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Office.Interop.Excel;
    using System.Text;
    using System.Reflection;
    using System.Data;
    
    // Code from http://www.devcurry.com/2009/07/import-excel-data-into-aspnet-gridview_06.html
    // Import excel Data into ASP.NET GridView using Interop Excel
    
    public partial class Training_test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds = GetExcel(Server.MapPath("Data\\ExcelData.xls"));
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        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"));
                }
                //  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)
            {
                return null;
            }
            finally
            {
                Dispose();
            }
        }
    }


     


     

    Tuesday, November 30, 2010 2:29 PM
  • User-1102123764 posted

    Hi,

    I see you have resolved your issue, would you please share the solution? 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 20, 2010 10:41 PM
  • User-718146471 posted

    The reason I marked it as resolved is because I have an answer; it can't be done though I really wish it could Frown . After doing a great deal of digging and asking several developers around me, there appears to be no way of getting hyperlink field data via OleDB. If someone can figure out how this is done, I would sure love to know. Maybe the development team at MS will take notice of this and create some type of Excel data source that can grab the extended parts of the cells.

    Tuesday, December 21, 2010 6:49 AM