C# 使用 OleDb 写入 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12807362/
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 to excel using OleDb
提问by Siegeon
I am attempting to export rows of data from sql to excel but my Insert Command seems to fail every time. I have spent a good deal of time trying to create this but I have finally run up against the wall.
我试图将数据行从 sql 导出到 excel，但我的插入命令似乎每次都失败。我花了很多时间试图创造这个，但我终于碰壁了。
The excel document is one that is generated by the IRS and we are not aloud to modify anything above row 16. Row 16 is the header row, and everything below that needs to be the data from sql. The header names all have spaces in them, and that seems to be where I am running into trouble.
excel 文档是由 IRS 生成的，我们不会大声修改第 16 行上方的任何内容。第 16 行是标题行，下面的所有内容都需要是来自 sql 的数据。标题名称中都有空格，这似乎是我遇到麻烦的地方。
Starting at row 16 the column names are: Attendee First Name, Attendee Last Name, Attendee PTIN, Program Number, CE Hours Awarded Program, Completion Date
从第 16 行开始，列名称为：参加者名字、参加者姓氏、参加者 PTIN、计划编号、CE 小时授予计划、完成日期
This is how I am attempting to write to excel
这就是我试图写到 excel 的方式
private void GenerateReport()
{
FileInfo xlsFileInfo = new FileInfo(Server.MapPath(CE_REPORTS_PATH + CE_PTIN_TEMPLATE + EXTENSION));
string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", xlsFileInfo.FullName);
//create connection
OleDbConnection oleDBConnection = new OleDbConnection(connectionString);
oleDBConnection.Open();
//create the adapter with the select to get
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$A16:F16]", oleDBConnection);
// Create the dataset and fill it by using the adapter.
DataTable dataTable = new DataTable();
adapter.FillSchema(dataTable, SchemaType.Source);
adapter.Fill(dataTable);
string[] colNames = new string[dataTable.Columns.Count];
string[] colParms = new string[dataTable.Columns.Count];
for (int i = 0; i < dataTable.Columns.Count; i++)
{
colNames[i] = String.Format("[{0}]", dataTable.Columns[i].ColumnName);
colParms[i] = "?";
}
// Create Insert Command
adapter.InsertCommand = new OleDbCommand(String.Format("INSERT INTO [Sheet1$] ({0}) values ({1})", string.Join(",", colNames), string.Join(",", colParms)), oleDBConnection);
// Create Paramaters
for (int i = 0; i < dataTable.Columns.Count; i++)
{
OleDbParameter param = new OleDbParameter(String.Format("@[{0}]", dataTable.Columns[i].ColumnName), OleDbType.Char, 255, dataTable.Columns[i].ColumnName);
adapter.InsertCommand.Parameters.Add(param);
}
// create a new row
DataRow newCERecord = dataTable.NewRow();
// populate row with test data
for (int i = 0; i < dataTable.Columns.Count; i++)
{
newCERecord[i] = "new Data";
}
dataTable.Rows.Add(newCERecord);
// Call update on the adapter to save all the changes to the dataset
adapter.Update(dataTable);
oleDBConnection.Close();
}
The error I get happens when adapter.Update(dataTable) is called and is as follows
当adapter.Update(dataTable)被调用时我得到的错误如下
$exception {"The INSERT INTO statement contains the following unknown field name: 'Attendee First Name'. Make sure you have typed the name correctly, and try the operation again."} System.Exception {System.Data.OleDb.OleDbException}
This is frustrating because I pull each field directly from the column name as gotten by colNames[i] = String.Format("[{0}]", dataTable.Columns[i].ColumnName). I discovered I needed the [] to account for the spaces in the column names, but at this point I am not sure what the problem is. When I look at the excel file everything seems correct to me.
这令人沮丧，因为我直接从 colNames[i] = String.Format("[{0}]", dataTable.Columns[i].ColumnName) 获取的列名中提取每个字段。我发现我需要 [] 来说明列名中的空格，但此时我不确定问题是什么。当我查看 excel 文件时，一切对我来说都是正确的。
采纳答案by xxbbcc
I actually found a Microsoft article for you that has the entire code done - you can likely copy & paste whichever solution you like most. Here's the link:
我实际上为您找到了一篇 Microsoft 文章，其中包含完整的代码 - 您可以复制和粘贴您最喜欢的任何解决方案。这是链接：
http://support.microsoft.com/kb/306023
http://support.microsoft.com/kb/306023
It seems like the one with CopyRecordsetis your easiest approach, although they do explain the one I mentioned (using a tab-delimited file).
CopyRecordset虽然他们确实解释了我提到的那个（使用制表符分隔的文件），但似乎是最简单的方法。
Edit: Here's my original answer for the sake of completeness. See the link above instead for more details and for a possible better solution.
编辑：为了完整起见，这是我的原始答案。有关更多详细信息和可能的更好解决方案，请参阅上面的链接。
This is not an answer to your question but a suggestion to change your approach (if you can). Excel tends to be very slow when adding data through COM calls and I assume OleDB uses COM internally. In my experience the fastest (and coincidentally the least painful way) to output data to Excel was to generate a tab-separated text file with all the data and then just import the file into Excel and use COM interop to perform any formatting on the sheet. When I generated Excel reports this way, most of my reports used to be generated almost 100x faster than using the Excel COM object model. (I don't know if this would be the same for OleDB calls, since I've never used OleDB with Excel but I'd be willing to bet the OleDB adapter uses COM internally.)
这不是对您问题的回答，而是对改变方法的建议（如果可以的话）。通过 COM 调用添加数据时，Excel 往往很慢，我假设 OleDB 在内部使用 COM。根据我的经验，将数据输出到 Excel 的最快（也是最不痛苦的方式）是生成一个包含所有数据的制表符分隔文本文件，然后将该文件导入 Excel 并使用 COM 互操作在工作表上执行任何格式设置. 当我以这种方式生成 Excel 报告时，我的大多数报告过去的生成速度几乎比使用 Excel COM 对象模型快 100 倍。（我不知道这对于 OleDB 调用是否相同，因为我从未将 OleDB 与 Excel 一起使用，但我愿意打赌 OleDB 适配器在内部使用 COM。）
This would also take care of your embedded space problem since tab would be the column separator.
这也可以解决您的嵌入空间问题，因为制表符将是列分隔符。
In your particular situation, I'd import the text file into Excel into a new sheet and then copy & paste it into the IRS sheet, at the right location. When done, the temporary sheet can be deleted.
在您的特定情况下，我会将文本文件导入 Excel 到一个新工作表中，然后将其复制并粘贴到 IRS 工作表中的正确位置。完成后，可以删除临时表。
回答by Senthil Kumar B
What are the field names in the database table ?? Can you mention them along with the excel header column names ?
数据库表中的字段名是什么？？您能将它们与 excel 标题列名称一起提及吗？
