如何将一个工作表从一个.XLS文件复制到另一个文件? [英] How can I copy a single worksheet from one .XLS file to another?

问题描述

我正在努力将整个工作表从现有的.XLS文件复制到我在应用程序中创建的新Excel文件中.到目前为止，这是我的代码:

I'm struggling to copy an entire worksheet from an existing .XLS file into a new Excel file I'm creating within my application. This is my code so far:

Microsoft.Office.Interop.Excel.Workbook tempworkbook = workbooks.Open(
           Directory.GetCurrentDirectory() + "\\Template.xlsx", //FileName
           0, //UpdateLinks
           true, //ReadOnly
           5, //Format
           Type.Missing, //Password
           Type.Missing, //WriteResPassword
           true,//IgnoreReadOnlyRecommended
           Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, //Origin
           "\t", //Delimiter
           false, //Editable
           false, //Notify
           0, //Converter
           false, //AddToMRU
           1, //Local
           0 //CorruptLoad
           );
        Microsoft.Office.Interop.Excel.Worksheet tmp1 = (Microsoft.Office.Interop.Excel.Worksheet)tempworkbook.Sheets["GSM Data"];
        string test = tmp1.get_Range("B2", "B2").Value2.ToString(); //test to see if sheet can be accessed - value should be "Database";
        tmp1.Copy(Type.Missing, wsGSMData); //copy worksheet into wsGSMData (which is the sheet in the new XLS file) 

但是我在最后一行遇到的错误是:

However the error I'm getting on the last line is:

服务器引发异常.(来自HRESULT的异常:0x80010105(RPC_E_SERVERFAULT))

The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

从本质上讲，我提取为 tmp1 的工作表应放入名为 wsGSMData 的工作表中，该工作表(我总共有7个工作表之一)已在新的.XLS工作簿(称为子工作簿)中创建:

Essentially, the sheet I've extracted as tmp1 should go into the sheet called wsGSMData, which is a sheet (one of 7 sheets in total) that i've created within the new .XLS workbook (called subsetworkbook):

 Microsoft.Office.Interop.Excel.Worksheet wsGSMData = (Microsoft.Office.Interop.Excel.Worksheet)subsetworkbook.Sheets["Sheet7"];

我做错了什么?

推荐答案

您的Excel文件的访问模式可能出现问题，这会导致(HRESULT异常:0x80010105(RPC_E_SERVERFAULT)).

Probably you've got an issue with Access Mode for your Excel file, which causes (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).

尝试检查Excel菜单中的设置:查看>共享工作簿>允许多个用户进行更改... -您应尝试取消选中此复选框.

Try to check settings in Excel menu: Review > Share Workbook > Allow changes by more than one user... - you should try to deselect this check box.

然后返回您的代码并检查所有行，您可以在其中更改此设置，尤其是 SaveAs 函数.如果看到 xlShared ，请将其替换为 xlExclusive (请参见下面的示例).

Then go back to your code and check any lines, where you could possibly change this settings, especially SaveAs function. If you see xlShared, replace it with xlExclusive (see example below).

在尝试将图表添加到文件时，我遇到了同样的问题.Google搜索解决方案无法解决我的问题，因此我必须自己弄清楚问题出在哪里.尝试通过不同方法将图表添加到新文件后，我意识到此行中的上述异常:

I've had the same problem while trying to add charts to file. No googled solution can help in my case, so I've got to figure out, what's going wrong, by myself. After trying to add charts into a fresh new file by different methods, I realise that aforementioned exception in this line:

Microsoft::Office::Interop::Excel::ChartObject ^ExcelChartObj = ExcelChartObjs->Add(100, 100, 300, 250); //create chart object (left, top, width, height)

实际上是由以下事实引起的:原始文件从一开始就选中了允许多个用户更改"复选框，并且我自己的代码也将其重新保存为 xlShared -两次！(是的，双重保存不是错误，在我的情况下应该这样，因为我的程序解决了一个繁重的任务，在此过程中，我需要通过两个不同的步骤将数据输入到同一文件中)

was actually caused by the fact, that original file had "Allow changes by more than one user" checkbox selected from the start, and moreover, my own code also re-saved it as xlShared - twice! (Nope, double saving is not an error, it is how it should be in my case, cause my program solves a huge task, and I need to input data into the same file in two different steps during it)

这是工作代码(用于C ++/CLI Windows窗体)，最终使(HRESULT的异常:0x80010105(RPC_E_SERVERFAULT))在我的情况下消失了:

Here's the working code (for C++/CLI Windows Forms), which finally made (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)) to disappear in my case:

ExcelApp->ActiveWorkbook->SaveAs(ExcFileName, Microsoft::Office::Interop::Excel::XlFileFormat::xlOpenXMLWorkbook, Type::Missing, Type::Missing,
    Type::Missing, Type::Missing, Microsoft::Office::Interop::Excel::XlSaveAsAccessMode::xlExclusive, Type::Missing, Type::Missing, Type::Missing,
    Type::Missing, Type::Missing); //save Excel book (access mode - xlExclusive, not xlShared!)

我希望它也会对那里的人有所帮助！:)

I hope it will help someone out there too! :)

这篇关于如何将一个工作表从一个.XLS文件复制到另一个文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！