Экспорт из Excel в ORACLE

Красивое и очень простое решение по сохранению данных в БД ORACLE непосредственно из программы Excel придумано умельцами и подобрано мной. Внешне принцип действия этого механизма очень прост. Пользователь работает в обычном режиме с файлами Excel, без каких-либо ограничений редактируя документы. После окончания правки пользователь нажимает кнопку сохранения файла. Вместо безусловного сохранения файла на диске на экране пользователя отображается диалоговое окно с вопросом о необходимости сохранения данных на сервере. Если пользователь отказывается, то данные сохраняются обычным образом. Если соглашается, то данные помимо сохранения в файловой системе экспортируются на сервер с помощью обычного ADODB.

Для реализации всего этого механизма нужно создать XLA надстройку, реализующую весь необходимый функционал. Перехват кнопки "Save..." осуществляется с помощью следующего кода:

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

  On Error GoTo TerminateSub
  If MsgBox(Prompt:="Сохранить в БД?", Buttons:=vbYesNo) = vbYes Then
    On Error GoTo -1
    MyWorkbookSave
  End If

TerminateSub:
End Sub

Теперь необходимо реализовать функцию MyWorkbookSave. В этой функции нужно сделать следующее:

1. Подключиться к серверу
2. В цикле пройти по строкам листа и выполнить SQL команду с данными
3. Сохранить или откатить изменения
4. Закрыть соединение с сервером


Для простоты описания будем считать, что пользователь работает с первым листом книги Excel и вводит в него таблицу, состоящую из двух столбцов ID и NAME, которая сохраняется в БД как таблица MY_EXCEL_TABLE(ID NUMBER, NAME VARCHAR2).

Sub MyWorkbookSave()
  Dim conn As Object
  Dim cmd As Object
  'Информируем пользователя о начале процесса 
  Application.StatusBar = "Процесс запущен"

  'Открываем соединение
  Set conn = CreateObject("ADODB.Connection")
  conn.Open "Provider=MSDAORA.1;Password=MyPassword;User ID=MyUser;Data Source=MyDataSource;Persist Security Info=True"

  'Явно начинаем транзакцию во избежание режима AutoCommit=ON
  conn.BeginTrans

  'Создаем команду
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = conn
  cmd.CommandText = "insert into my_excel_table(id, name) values (?,?)"

  'Описываем параметры команды. В нашем случае их два (значения столбцов ID и NAME)
  cmd.Parameters.Append cmd.CreateParameter("id", 131, 1)
  cmd.Parameters.Append cmd.CreateParameter("name", 200, 1, 100)

  'Для упрощения примера считаем, что данные находятся в столбцах A1 и B1 первого в книге листа
  'Создаем соответствующий Range-объект
  Set sh1 = ActiveWorkbook.Sheets(1)
  Set r = sh1.Range(sh1.[A1], sh1.[B1].End(xlDown))

  'Проходим по всем заполненным строкам
  'В реальных документах строки создаются динамически и впоследствии не удаляются
  'поэтому значение созданных строк может отличатьcя от количества заполненных
  For iRow = 1 To r.Rows.Count
    cmd.Parameters("ID").Value = r.Cells(iRow, 1).Value
    cmd.Parameters("NAME").Value = NVL(r.Cells(iRow, 2).Value)
    cmd.Execute
  Next iRow

  'Сохраняем данные
  conn.CommitTrans

  'Удаляем команду и закрываем соединение
  Set cmd = Nothing
  conn.Close

  'Информируем пользователя о завершении процесса
  Application.StatusBar = "Процесс завершен"
End Sub

Собственно говоря, это все. Остальное - навороты. К ним относятся необходимые проверки на корректность сохраняемых данных, обработчики ошибок и проч. Очень важно обратить внимание на то, что в ADO по умолчанию выключен режим implicit transactions, поэтому каждая выполняемая SQL команда обрамляется командами начала и конца транзакции, то есть фактически после каждой команды принудительно выполняется commit. Чтобы отключить этот навязчивый режим, необходимо включить режим implicit transactions и объявлять и завершать транзакции явно. Для получения подробной информации о том, как это сделать, обратитесь к MSDN.