使用C＃在excel文件中添加多个超链接的优化方式 [英] Optimized way of adding multiple hyperlinks in excel file with C#

问题描述

我想询问是否有一些实用的方式在Excel工作表中添加多个超链接与C＃..？我想生成一个网站列表和锚定超链接到他们，所以用户可以点击这样的超链接并访问该网站。
到目前为止，我已经有了简单的嵌套for语句，它循环遍历给定excel范围内的每个单元格，并向该单元格添加超链接：

 （int i = 0; i< _range.Rows.Count; i ++）
 {
 Microsoft.Office.Interop.Excel.Range row = _range.Rows [i] ; 
 for（int j = 0; j< row.Cells.Count; j ++）
 {
 Microsoft.Office.Interop.Excel.Range cell = row.Cells [j]; 
 cell.Hyperlinks.Add（cell，adresses [i，j]，_optionalValue，_optionalValue，_optionalValue）; 
} 
} 
  

代码正在按预期工作，但它是非常由于超级链接数千个调用缓慢的方法。
有趣的是，来自Office.Interop.Excel的方法set_Value可以通过一个简单的调用添加数千个字符串，但没有类似的方法来添加超链接（Hyperlinks.Add只能添加一个超链接）。

所以我的问题是，有没有办法优化在C＃中添加超链接到excel文件，当你需要添加大量的超链接？ >

任何帮助将被解除。

解决方案

我正在使用VS2010和MS Excel
我有同样的问题（通过Range.Hyperlinks.Add添加300个超链接需要大约2分钟）。

运行时问题是因为许多范例实例。

解决方案：

使用单范围实例，并用= HYPERLINK（target，[friendlyName]）Excel-Formula添加超链接。 / p>

示例：

 列表< string> urlsList = new List< string>（）; 
 urlsList.Add（http://www.gin.de）; 
 // ^^ n次... 
 
 //创建带有内容的形状数组
 object [，] content = new object [urlsList.Count，1]; 
 foreach（urlsList中的url url）
 {
 content [i，1] = string.Format（= HYPERLINK（\{0} \），url）; 
} 
 
 //获取范围
 string rangeDescription = string.Format（A1：A {0}，urlsList.Count + 1）// excel索引开始于1 
 Xl.Range xlRange = worksheet.Range [rangeDescription，XlTools.missing]; 
 
 //设置值终于
 xlRange.Value2 = content; 
  

...只需1秒...

I wanted to ask if there is some practical way of adding multiple hyperlinks in excel worksheet with C# ..? I want to generate a list of websites and anchor hyperlinks to them, so the user could click such hyperlink and get to that website. So far I have come with simple nested for statement, which loops through every cell in a given excel range and adds hyperlink to that cell:

for (int i = 0; i < _range.Rows.Count; i++)
        {
            Microsoft.Office.Interop.Excel.Range row = _range.Rows[i];
            for (int j = 0; j < row.Cells.Count; j++)
            {
                Microsoft.Office.Interop.Excel.Range cell = row.Cells[j];
                cell.Hyperlinks.Add(cell, adresses[i, j], _optionalValue, _optionalValue, _optionalValue);
            }
        }

The code is working as intended, but it is Extremely slow due to thousands of calls of the Hyperlinks.Add method. One thing that intrigues me is that the method set_Value from Office.Interop.Excel can add thousands of strings with one simple call, but there is no similar method for adding hyperlinks (Hyperlinks.Add can add just one hyperlink).

So my question is, is there some way to optimize adding hyperlinks to excel file in C# when you need to add a large number of hyperlinks...?

Any help would be apreciated.

解决方案

I am using VS2010 and MS Excel 2010. I have the very same problems (adding 300 hyperlinks via Range.Hyperlinks.Add takes approx. 2 min).

The runtime issue is because of the many Range-Instances.
Solution:
Use a single range instance and add Hyperlinks with the "=HYPERLINK(target, [friendlyName])" Excel-Formula.

Example:

List<string> urlsList = new List<string>();
urlsList.Add("http://www.gin.de");
// ^^ n times ...

// create shaped array with content 
object[,] content = new object [urlsList.Count, 1];
foreach(string url in urlsList)
{
    content[i, 1] = string.Format("=HYPERLINK(\"{0}\")", url);
}

// get Range
string rangeDescription = string.Format("A1:A{0}", urlsList.Count+1) // excel indexes start by 1
Xl.Range xlRange = worksheet.Range[rangeDescription, XlTools.missing];

// set value finally
xlRange.Value2 = content;

... takes just 1 sec ...

这篇关于使用C＃在excel文件中添加多个超链接的优化方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！