从Excel单元格中的超链接文本中提取URL

I have a table full of Hyperlinked text in excel, so it's basically a bunch of names but when I click on one, it takes me to some URL in my default browser.

我在excel中有一个满是超链接文本的表格，所以它基本上是一串名字，但是当我点击一个，它会把我带到我的默认浏览器中的某个URL。

So I am extracting text from this excel table in my program, but the value I get when I extract from these hyperlink cells is that of the string inside, when I want the URL the string is linked to in the excel file.

我在程序中从这个excel表中提取文本，但是当我从这些超链接单元格中提取时得到的值是字符串的值，当我想要将字符串链接到excel文件中的URL时。

So I'm thinking there are two ways to do this. Either I can convert all the hyperlinked text in the excel file to the corresponding URLs, or I can use C# to somehow extract the URL value from the cell and not the text.

我想有两种方法。我可以将excel文件中的超链接文本转换为相应的URL，或者我可以使用c#从单元格中提取URL值而不是文本。

I don't know how to do either of these things, but any help would be greatly appreciated.

我不知道如何做这两件事，但任何帮助都将非常感谢。

C# code so far:

c#代码:

Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

//excelApp.Visible = true;

Excel.Workbook excelWorkbook = 
excelApp.Workbooks.Open("C:\\Users\\use\\Desktop\\list.xls",
0, false, 5, "", "",false, Excel.XlPlatform.xlWindows, "", 
true, false, 0, true, false, false);

Excel.Sheets excelSheets = excelWorkbook.Worksheets;

string currentSheet = "Sheet1";
Excel.Worksheet xlws = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

string myString = ((Excel.Range)xlws.Cells[2, 1]).Value.ToString();

As for the excel file, it's just one long row of names hyperlinked. For instance cell A2 would contain the text:

至于excel文件，它只是一长串的名称超链接。例如，cell A2将包含以下文本:

Yummy cookie recipe

美味的饼干食谱

And I want to extract the string:

我想提取这个字符串

http://allrecipes.com//Recipes/desserts/cookies/Main.aspx

7 个解决方案

#1

You could use a vba macro:

您可以使用vba宏:

Hit Alt+F11 to open the VBA editor and paste in the following:

按Alt+F11打开VBA编辑器，粘贴如下:

Function URL(rg As Range) As String
  Dim Hyper As Hyperlink
  Set Hyper = rg.Hyperlinks.Item(1)
  URL = Hyper.Address
End Function

And then you can use it in your Worksheet, like this:

然后你可以在你的工作表中使用它，像这样:

=URL(B4)

= URL(B4)

#2

VBA function:

VBA功能:

Hit Alt+F11 (Opens Visual Basic Editor)
点击Alt+F11(打开Visual Basic编辑器)
Click on Insert -> Module (adds a module to your excel file)
单击Insert ->模块(将模块添加到excel文件中)
Paste the code below for the function of GETURL
将GETURL函数的代码粘贴到下面
Hit Alt+Q (Closes the Visual Basic Editor)
点击Alt+Q(关闭Visual Basic编辑器)

Now use the =GETURL(cell) to get the URL
Example: =GETURL(A1) will return the URL for the Hyperlink displayed in cell A1

现在使用=GETURL(cell)来获取URL示例:=GETURL(A1)将返回在单元A1中显示的超链接的URL。

Function GETURL(HyperlinkCell As Range)
    GETURL = HyperlinkCell.Hyperlinks(1).Address
End Function

Source

源

#3

In your code just add

在代码中添加

string myString = ((Excel.Range)xlws.Cells[2, 1]).Cells.Hyperlinks[1].Address;

I obviously recommend doing some checks before accessing the "Hyperlinks" property.

我显然建议在访问“超链接”属性之前进行一些检查。

#4

Use Visual Studio Tools for Office (VSTO) to open Excel workbook and extract all hyperlinks.

使用Office的Visual Studio工具(VSTO)打开Excel工作簿并提取所有超链接。

I put a hyperlink into A1 of Sheet1 in Book1.xlsx: text = "example.com, address = "http://www.example.com"

我在Book1中添加了一个超链接到Sheet1的A1中。xlsx: text = "example.com "， address = "http://www.example.com"

_Application app = null;
try
{
    app = new Application();

    string path = @"c:\temp\Book1.xlsx";
    var workbook = app.Workbooks.Open(path, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

    var sheets = workbook.Worksheets;
    var sheet = (Worksheet)sheets.get_Item("Sheet1");

    var range = sheet.get_Range("A1", "A1");
    var hyperlinks = range.Cells.Hyperlinks.OfType<Hyperlink>();

    foreach (var h in hyperlinks)
    {
        Console.WriteLine("text: {0}, address: {1}", h.TextToDisplay, h.Address);
    }
}
finally
{
    if (app != null)
        app.Quit();
}

Output:

输出:

text: example.com, address: http://www.example.com/

#5

why not use Uri class to convert string into URL:

为什么不使用Uri类将字符串转换为URL ?

Uri uri = new Uri("http://myUrl/test.html");

#6

You can use VBA code to achieve this. Press Alt + F11 to open VB editor, Insert a Module and paste the code below:

您可以使用VBA代码来实现这一点。按Alt + F11打开VB编辑器，插入模块，粘贴如下代码:

Sub run()    
    On Error Resume Next    

    For Each hLink In Selection    
        Range(hLink.Address).Offset(0, 1) = hLink.Hyperlinks(1).Address    
    Next    
End Sub

Save your excel file[in excel 2007 and above save as macro enabled...]

保存您的excel文件[在excel 2007及以上保存为宏启用…]

#7

-1

I just ran into this issue and this is what worked for me:

我刚遇到这个问题，这对我很有效:

I used the FormulaR1C1 extension method for a range. So my code looked like this:

我使用了一个范围的公式c1扩展方法。我的代码是这样的:

                    for (int r = 2; r <= sheetRange.Rows.Count; r++)
                    {
                        documentRecord = new List<string>();
                        for (int c = 1; c <= wkCol; c++)
                        {
                            documentRecord.Add(sheetRange.Cells[r, c].FormulaR1C1); 
                        }
                        AllRecords.Add(documentRecord);
                    }

When the record is added to the list of records, the value of whatever the cell range was is formatted into a clickable-hyperlink.

当记录被添加到记录列表时，无论单元格范围是什么，其值都被格式化为点击-超链接。

#1