如何从Excel单元格中提取链接URL [英] How to extract link url from Excel cell

问题描述

我有一个C＃Web作业，可下载并读取Excel文件。其中一列包含我要保存在数据库中的链接。我目前正在使用ExcelDataReader将Excel文件转换为数据集，然后在各行之间循环以获取数据。转换后，该列此时仅是一个包含链接文本的字符串。

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.

从其他一些阅读中看来，在Excel中，超链接存储在其他位置，并且该信息将Excel文件转换为DataSet时不会保留。

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.

我没有设置使用ExcelDataReader，但是想找到一种解决方案来提取这些链接URL而无需

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;
}

推荐答案

我最终能够使用EPPLUS获取超链接数据以读取我的excel文件。

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

代码：

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;

这篇关于如何从Excel单元格中提取链接URL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！