从 Excel 单元格中的超链接文本中提取 URL [英] Extracting a URL from hyperlinked text in Excel cell
问题描述
我在 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 表中提取文本,但是当我从这些超链接单元格中提取时,我得到的值是里面的字符串,当我想要字符串在 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 文件中的所有超链接文本转换为相应的 URL,或者我可以使用 C# 以某种方式从单元格中提取 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.
我不知道如何做这些事情,但任何帮助将不胜感激.
I don't know how to do either of these things, but any help would be greatly appreciated.
到目前为止的 C# 代码:
C# code so far:
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();
至于 excel 文件,它只是一长排超链接的名称.例如单元格 A2 将包含文本:
As for the excel file, it's just one long row of names hyperlinked. For instance cell A2 would contain the text:
我想提取字符串:
http://allrecipes.com//Recipes/desserts/cookies/Main.aspx
推荐答案
您可以使用 vba 宏:
You could use a vba macro:
点击 Alt+F11 打开 VBA 编辑器并粘贴以下内容:
Hit Alt+F11 to open the VBA editor and paste in the following:
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)
这篇关于从 Excel 单元格中的超链接文本中提取 URL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!