我从互联网上搜索了很多,但不幸的是,它们都没有奏效 . How to copy sheets from a workbook to another workbook,Cutting and Pasting Cell Values in Excel using C#,Error: Pastespecial method of range class failed .
问题是,如果我在同一工作簿中创建工作表,代码工作得非常好 . 但是,如果我创建了一个新的工作簿,那么粘贴将无效 .
//copy function: copy to a new worksheet in the same workbook
var copyFromRange = oldSheet.Range[
(Excel.Range)oldSheet.Cells[1, 1],
oldSheet.Cells[firstPackageStartLine - 1, lastColIgnoreFormulas]];
copyFromRange.Copy(Type.Missing);
var copyHeaderRange = newSheet.Range[(Excel.Range)newSheet.Cells[1, 1], newSheet.Cells[firstPackageStartLine - 1, lastColIgnoreFormulas]];
copyHeaderRange.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//Copy to a new workbook, won't work
var excel = new Excel.Application();
var newWorkbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
var newSheet = (Excel.Worksheet)newWorkbook.Worksheets.Add();
newSheet.Name = "sheetName";
//copy function, same as above. But this won't work, PasteSpecial would throw null reference error.
//If I changed the copyHeaderRange to below, then it will throw: PasteSpecial method of Range class failed.
var copyHeaderRange = (Excel.Range)newSheet.Cells[1, 1];
//If the copyRange is defined as below, it will throw null reference error while pastespecial
var copyHeaderRange = newSheet.Range[(Excel.Range)newSheet.Cells[1, 1], newSheet.Cells[firstPackageStartLine - 1, lastColIgnoreFormulas]];
我也试过打开一个新的工作簿,而不是开始一个新的工作簿,将无法正常工作 . 试图在复制之前激活工作表,也不会工作 .
知道为什么会这样吗?
1 回答
如果复制范围中有任何合并的单元格 - 即使它们不超出该范围的坐标单元格 - 并且您正在创建Excel应用程序类的新实例,我认为此错误是预期的 .
您可以修改使用相同的Excel实例(未经测试),而不是
excel
实例: