How to extract link url from Excel cell

Question

I have a c# webjob that downloads and then reads an Excel file. One of the columns contains links that I'd like to save in my database. I'm currently using ExcelDataReader to convert the Excel file to a DataSet and then looping through the rows to grab the data. After conversion the column in question at this point is only a string containing the link text.

From some other reading it sounds like in Excel, hyperlinks are stored elsewhere and that information isn't preserved when converting the Excel file to a DataSet.

I'm not set on using ExcelDataReader but would like to find a solution to extract these link URLs without having to pay for some third part software.

Here is the simple code I have so far as reference:

FileStream stream = File.Open(fileLocation, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;

DataSet result = excelReader.AsDataSet();

int count = 0;

foreach (DataRow row in result.Tables["WorkSheetName"].DataTable.Rows)
{
    var item = new myObject();

    item.Prop1 = long.Parse(row["Column3"].ToString());
    item.Prop2 = row["Column7"].ToString(); //The link, currently only seeing link text

    this.myDbContext.myTable.Add(item);
    await this.myDbContext.SaveChangesAsync();

    count += 1;
}

I dont understand, You have a string and you want to convert that to a URL? — Moshe D
– Moshe D, Commented Jan 18, 2017 at 19:34
In the Excel file it is a hyperlink. When reading the cell as a string it only extracts the link text and not the URL that the link leads to. — Alec Menconi
– Alec Menconi, Commented Jan 18, 2017 at 21:02
In VBA, the address property of the hyperlink attached to the cell would return the URL. The hyperlink object attached to the cell would be the first item in the Hyperlinks collection. So in VBA, it would look something like Range("A1").Hyperlinks(1).address for the URL. Can you do something similar in C#? — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 19, 2017 at 2:49
Ron, looks like I just needed to use a plugin that gave me access to that data, thanks. — Alec Menconi
– Alec Menconi, Commented Jan 19, 2017 at 19:10

Alec Menconi · Accepted Answer · 2017-01-19 19:09:35Z

I ended up being able to get the hyperlink data using EPPLUS to read my excel file.

Code:

var pck = new ExcelPackage(excelFileStream);
ExcelWorksheet ws = pck.Workbook.Worksheets.First();

DataTable dt = new DataTable(ws.Name);
int totalCols = ws.Dimension.End.Column;
int totalRows = ws.Dimension.End.Row;
int startRow = 3;
ExcelRange wsRow;
DataRow dr;
foreach (var firstRowCell in ws.Cells[2, 1, 2, totalCols])
{
    dt.Columns.Add(firstRowCell.Text);
}

for (int rowNum = startRow; rowNum <= totalRows; rowNum++)
{
    wsRow = ws.Cells[rowNum, 1, rowNum, totalCols];
    dr = dt.NewRow();
    int rowCnt = 0;
    foreach (var cell in wsRow)
    {
        if (rowCnt == 7)
        {
            if (cell.Hyperlink != null)
            {
                dr[cell.Start.Column - 1] = cell.Hyperlink.AbsoluteUri;
            }
        }
        else
        {
            dr[cell.Start.Column - 1] = cell.Text;
        }

        rowCnt++;
    }

    if (!String.IsNullOrEmpty(dr[7].ToString()))
    {
        dt.Rows.Add(dr);
    }
}

return dt;

Collectives™ on Stack Overflow

How to extract link url from Excel cell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related