使用c＃删除excel中的公式链接(remove formula links in excel using c#)

我正在将工作表从book1复制到book2。 该工作表包含具有公式的单元格。 在book2的工作表'sheet1'中，单元格包含返回到book1.sheet1 ='Y:\temp\\[book1.xls]sheet1'!A1

我的问题是如何Y:\temp\\[book1.xls]sheet1并只有sheet1!A1 ？ 基本上使book2成为源代码。

我努力了：

for (int i = 2; i <= numberOfSheets; i++) { Worksheet ws = (Worksheet)wbDestination.Sheets[i]; Range r = (Range)ws.UsedRange; bool success = (bool)r.Replace( @"'Y:\temp\[book1.xls]sheet1'", "Sheet1", XlLookAt.xlWhole, XlSearchOrder.xlByRows, false, m, m, m); }

这不起作用，并获得excel无法找到任何匹配的消息。

I am copying worksheet from book1 to book2. The worksheet contains cells with formulas. On worksheet 'sheet1' in book2 the cell contains a link back to book1.sheet1 ='Y:\temp\\[book1.xls]sheet1'!A1

My question is how to I strip out the Y:\temp\\[book1.xls]sheet1 and just have sheet1!A1 ? Basically making book2 the source.

I have tried:

for (int i = 2; i <= numberOfSheets; i++) { Worksheet ws = (Worksheet)wbDestination.Sheets[i]; Range r = (Range)ws.UsedRange; bool success = (bool)r.Replace( @"'Y:\temp\[book1.xls]sheet1'", "Sheet1", XlLookAt.xlWhole, XlSearchOrder.xlByRows, false, m, m, m); }

This doesn't work and get a message that excel was unable to find any matches.

最满意答案

尝试逐字字符串：

@"'Y:\temp\[book1.xls]sheet1'"

代替

"'Y:\temp\[book1.xls]sheet1'"

在逐字字符串中，所有这些特殊字符都按字面解释。

I solved this for my needs. I hope it helps someone out. I used the following code.

Array links = wbOut.LinkSources(Microsoft.Office.Interop.Excel.XlLink.xlExcelLinks) as Array; if (links != null) { for (int i = 1; i <= links.Length; i++) { wbOut.ChangeLink(@"c:\temp\book1.xls", @"c:\temp\book2.xls", XlLinkType.xlLinkTypeExcelLinks); } }使用c＃删除excel中的公式链接(remove formula links in excel using c#)

我正在将工作表从book1复制到book2。 该工作表包含具有公式的单元格。 在book2的工作表'sheet1'中，单元格包含返回到book1.sheet1 ='Y:\temp\\[book1.xls]sheet1'!A1

我的问题是如何Y:\temp\\[book1.xls]sheet1并只有sheet1!A1 ？ 基本上使book2成为源代码。

我努力了：

for (int i = 2; i <= numberOfSheets; i++) { Worksheet ws = (Worksheet)wbDestination.Sheets[i]; Range r = (Range)ws.UsedRange; bool success = (bool)r.Replace( @"'Y:\temp\[book1.xls]sheet1'", "Sheet1", XlLookAt.xlWhole, XlSearchOrder.xlByRows, false, m, m, m); }

这不起作用，并获得excel无法找到任何匹配的消息。

I am copying worksheet from book1 to book2. The worksheet contains cells with formulas. On worksheet 'sheet1' in book2 the cell contains a link back to book1.sheet1 ='Y:\temp\\[book1.xls]sheet1'!A1

My question is how to I strip out the Y:\temp\\[book1.xls]sheet1 and just have sheet1!A1 ? Basically making book2 the source.

I have tried:

for (int i = 2; i <= numberOfSheets; i++) { Worksheet ws = (Worksheet)wbDestination.Sheets[i]; Range r = (Range)ws.UsedRange; bool success = (bool)r.Replace( @"'Y:\temp\[book1.xls]sheet1'", "Sheet1", XlLookAt.xlWhole, XlSearchOrder.xlByRows, false, m, m, m); }

This doesn't work and get a message that excel was unable to find any matches.

最满意答案

尝试逐字字符串：

@"'Y:\temp\[book1.xls]sheet1'"

代替

"'Y:\temp\[book1.xls]sheet1'"

在逐字字符串中，所有这些特殊字符都按字面解释。

I solved this for my needs. I hope it helps someone out. I used the following code.

Array links = wbOut.LinkSources(Microsoft.Office.Interop.Excel.XlLink.xlExcelLinks) as Array; if (links != null) { for (int i = 1; i <= links.Length; i++) { wbOut.ChangeLink(@"c:\temp\book1.xls", @"c:\temp\book2.xls", XlLinkType.xlLinkTypeExcelLinks); } }