用写作来的OleDb练成 [英] Writing to excel using OleDb

问题描述

我试图导出数据的行从SQL出类拔萃，但我的插入命令，似乎每一次失败。我已经花了很多时间一个很好的协议努力创造这一点，但我终于跑起来靠在墙上。

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.

Excel文档是一个由IRS产生的，我们不出声来修改上面的任何一行行16 16为标题行，一切都低于需求对SQL的数据。该标题名都在他们的空间，这似乎是在那里我遇到了麻烦。

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.

在第16行开始列名：
与会者名，出席者姓，与会者PTIN，程序号，CE小时获奖项目，竣工日期

Starting at row 16 the column names are: Attendee First Name, Attendee Last Name, Attendee PTIN, Program Number, CE Hours Awarded Program, Completion Date

这是我在尝试写脱颖而出

This is how I am attempting to write to 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();     
}

我得到的错误发生在adapter.Update（dataTable中）被称为是如下：

The error I get happens when adapter.Update(dataTable) is called and is as follows

$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}

这是令人沮丧，因为我直接拉扯每个字段的列名由colNames为得到[I] =的String.Format（[{0}]，dataTable.Columns [I] .ColumnName）。我发现我所需要的[]考虑到在列名的空间，但在这一点上我不知道是什么问题。当我看excel文件的一切似乎是正确的我。

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.

推荐答案

我居然找到您想要一个微软的文章有完成整个代码 - 你可以复制可能与放;贴上你最喜欢哪个解决方案。这里的链接：

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:

的http：//支持。 microsoft.com/kb/306023

这似乎是一个与 CopyRecordset 是最简单的方法虽然他们做解释就是我提到的（使用制表符分隔的文件）

It seems like the one with CopyRecordset is your easiest approach, although they do explain the one I mentioned (using a tab-delimited file).

修改：这是我为求原来的答案完整。见上面，而不是链接查看更多细节和可能更好的解决方案。

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.

这是不是一个回答你的问题，而是改变你的方法的建议（如果你能） 。 Excel中添加往往通过COM调用数据时很慢，我以为OLEDB使用COM内部。根据我的经验最快的（而巧合最少痛苦的方式），将数据输出到Excel中产生的所有数据的制表符分隔文本文件，然后只需将文件导入Excel和使用COM互操作的表上执行任何格式。当我生成Excel报表就这样，我的大部分报告用于生成几乎100倍比使用Excel COM对象模型更快。 （我不，知道这将是OLEDB调用相同的，因为我从来没有使用OLEDB使用Excel，但我愿意打赌OLEDB适配器使用COM内部。）

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.)

这也会照顾你的嵌入式领域的问题，因为标签是列分隔符。

This would also take care of your embedded space problem since tab would be the column separator.

在你的特殊情况，我想导入的文本文件到Excel到一个新的工作表，然后复制和放大器;将其粘贴到国税局表，在正确的位置。完成后，临时表可以被删除。

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.

这篇关于用写作来的OleDb练成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！