C# 用OLEDB写入excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/57987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Writing into excel file with OLEDB
提问by Danielb
Does anyone know how to write to an excel file (.xls) via OLEDB in C#? I'm doing the following:
有谁知道如何在 C# 中通过 OLEDB 写入 excel 文件 (.xls)？我正在做以下事情：
OleDbCommand dbCmd = new OleDbCommand("CREATE TABLE [test$] (...)", connection);
dbCmd.CommandTimeout = mTimeout;
results = dbCmd.ExecuteNonQuery();
But I get an OleDbException thrown with message:
但是我收到一个带有消息的 OleDbException 抛出：
"Cannot modify the design of table 'test$'. It is in a read-only database."
“无法修改表 'test$' 的设计。它在只读数据库中。”
My connection seems fine and I can select data fine but I can't seem to insert data into the excel file, does anyone know how I get read/write access to the excel file via OLEDB?
我的连接看起来很好，我可以很好地选择数据，但我似乎无法将数据插入到 excel 文件中，有谁知道我如何通过 OLEDB 获得对 excel 文件的读/写访问权限？
采纳答案by Zorantula
You need to add ReadOnly=False;to your connection string
您需要添加ReadOnly=False;到您的连接字符串
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fifa_ng_db.xls;Mode=ReadWrite;ReadOnly=false;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";
回答by Michael Haren
A couple questions:
几个问题：
- Does the user that executes your app (you?) have permission to write to the file?
- Is the file read-only?
- What is your connection string?
- 执行您的应用程序的用户（您？）是否有权写入文件？
- 文件是只读的吗？
- 你的连接字符串是什么？
If you're using ASP, you'll need to add the IUSER_* user as in this example.
如果您使用的是 ASP，则需要添加 IUSER_* 用户，如本例所示。
回答by Danielb
- How do I check the permissions for writing to an excel file for my application (I'm using excel 2007)?
- The file is not read only, or protected (to my knowledge).
- My connection String is:
- 如何检查为我的应用程序写入 excel 文件的权限（我使用的是 excel 2007）？
- 该文件不是只读的，也不是受保护的（据我所知）。
- 我的连接字符串是：
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fifa_ng_db.xls;Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fifa_ng_db.xls;Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""
回答by Duncan Smart
Further to Michael Haren's answer. The account you will need to grant Modify permissions to the XLS file will likely be NETWORK SERVICE if this code is running in an ASP.NET application (it's specified in the IIS Application Pool). To find out exactly what account your code is running as, you can do a simple:
进一步迈克尔哈伦的回答。如果此代码在 ASP.NET 应用程序中运行（在 IIS 应用程序池中指定），则您需要授予 XLS 文件修改权限的帐户可能是 NETWORK SERVICE。要准确找出您的代码正在运行的帐户，您可以执行以下简单操作：
Response.Write(Environment.UserDomainName + "\" + Environment.UserName);
回答by Duncan Smart
I was also looking for and answer but Zorantula's solution didn't work for me. I found the solution on http://www.cnblogs.com/zwwon/archive/2009/01/09/1372262.html
我也在寻找并回答，但 Zorantula 的解决方案对我不起作用。我在http://www.cnblogs.com/zwwon/archive/2009/01/09/1372262.html上找到了解决方案
I removed the ReadOnly=falseparameter and the IMEX=1extended property.
我删除了ReadOnly=false参数和IMEX=1扩展属性。
The IMEX=1property opens the workbook in import mode, so structure-modifying commands (like CREATE TABLEor DROP TABLE) don't work.
该IMEX=1属性以导入模式打开工作簿，因此结构修改命令（如CREATE TABLE或DROP TABLE）不起作用。
My working connection string is:
我的工作连接字符串是：
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=Yes;\";"
回答by Duncan Smart
I was running under ASP.NET, and encountered both "Cannot modify the design..." and "Cannot locate ISAM..." error messages.
我在 ASP.NET 下运行，遇到了“无法修改设计...”和“无法找到 ISAM...”的错误消息。
I found that I needed to:
我发现我需要：
a) Use the following connection string:
a) 使用以下连接字符串：
Provider=Microsoft.Jet.OLEDB.4.0;Mode=ReadWrite;Extended Properties='Excel 8.0;HDR=Yes;';Data Source=" + {path to file};
Provider=Microsoft.Jet.OLEDB.4.0;Mode=ReadWrite;Extended Properties='Excel 8.0;HDR=Yes;';Data Source=" + {path to file};
Note I too had issues with IMEX=1and with the ReadOnly=falseattributes in the connection string.
注意我也遇到了连接字符串中IMEX=1的ReadOnly=false属性问题。
b) Grant EVERYONE full permissions to the folder in which the file was being written. Normally, ASP.NET runs under the NETWORK SERVICE account, and that already had permissions. However, the OleDb code is unmanaged, so it must run under some other security context. (I am currently too lazy to figure out which account, so I just used EVERYONE.)
b) 授予每个人对正在写入文件的文件夹的完全权限。通常，ASP.NET 在 NETWORK SERVICE 帐户下运行，并且该帐户已经具有权限。但是，OleDb 代码是非托管的，因此它必须在其他一些安全上下文中运行。（我目前懒得弄清楚哪个帐户，所以我只使用了每个人。）
回答by Erwin Aarnoudse
I also had the same problem. Only remove the extended property IMEX=1. That will solve your problem. Your table will be created in your Excel file...
我也有同样的问题。只删除扩展属性IMEX=1。那将解决您的问题。您的表格将在您的 Excel 文件中创建...
