Писать, чтобы преуспеть, используя OleDb

Question

Я пытаюсь экспортировать строки данных из sql в excel, но моя команда вставки, похоже, каждый раз дает сбой. Я потратил немало времени, пытаясь создать это, но, наконец, наткнулся на стену.

Документ Excel - это документ, сгенерированный IRS, и мы не можем изменить что-либо выше строки 16. Строка 16 - это строка заголовка, и все, что ниже, должно быть данными из sql. Во всех именах заголовков есть пробелы, и, похоже, именно здесь я сталкиваюсь с проблемами.

Начиная со строки 16, имена столбцов следующие: Имя участника, Фамилия участника, PTIN участника, Номер программы, Программа награжденных часов CE, Дата завершения.

Вот как я пытаюсь написать, чтобы преуспеть

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) и выглядит следующим образом:

$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, мне все кажется правильным.

Answer 1

Я действительно нашел для вас статью Microsoft, в которой готов весь код - вы можете скопировать и вставить любое решение, которое вам больше всего нравится. Вот ссылка:

http://support.microsoft.com/kb/306023

Кажется, что с CopyRecordset это ваш самый простой подход, хотя они и объясняют тот, который я упомянул (используя файл с разделителями табуляции).

Изменить: вот мой оригинальный ответ для полноты. Вместо этого см. Ссылку выше для получения более подробной информации и возможного лучшего решения.

Это не ответ на ваш вопрос, а предложение изменить ваш подход (если вы можете). Excel обычно работает очень медленно при добавлении данных через вызовы COM, и я предполагаю, что OleDB использует COM внутри. По моему опыту, самым быстрым (и по совпадению наименее болезненным способом) для вывода данных в Excel было создание разделенного табуляцией текстового файла со всеми данными, а затем просто импортирование файла в Excel и использование COM-взаимодействия для выполнения любого форматирования на листе., Когда я генерировал отчеты Excel таким образом, большинство моих отчетов создавалось почти в 100 раз быстрее, чем при использовании объектной модели Excel COM. (Я не знаю, будет ли это так же для вызовов OleDB, так как я никогда не использовал OleDB с Excel, но я готов поспорить, что адаптер OleDB использует COM внутри.)

Это также позаботится о вашей проблеме со встроенным пространством, так как tab будет разделителем столбцов.

В вашей конкретной ситуации я бы импортировал текстовый файл в Excel на новый лист, а затем скопировал и вставил его в лист IRS в нужном месте. Когда закончите, временный лист может быть удален.

Answer 2

Какие имена полей в таблице базы данных? Можете ли вы упомянуть их вместе с именами столбцов заголовка Excel?