Доступ к Excel через OleDb

1-post.md

Провайдеры данных

Для работы с Excel 2003 (.Xls) можно использовать провайдер Microsoft Jet OLE DB 4.0.

SELECT * FROM OPENROWSET(
	'Microsoft.Jet.OLEDB.4.0', 
	'Excel 12.0;Database=d:\tmp\TimeSheets.xlsx;HDR=YES;IMEX=1', 
	'SELECT * FROM [Sheet1$]');

Для работы с Excel 2007 (.Xlsx) - Microsoft ACE OLEDB 12.0.

SELECT * FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=d:\tmp\TimeSheets.xlsx;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]');

В Windows 10 открыть настройки источников данных ODBC можно написав "Источники данных ODBC" или через Панель управления \ Администрирование.

Extended Properties

• HDR=YES|NO. HDR=YES означает, что первую строку листа, следует рассматривать как заголовки колонок. Т.о. значение из первой строки можно использовать как имена полей в sql запросах (любых: select, insert, update, delete).
• IMEX=1|3. 1 - открыть соединение для чтения. 3 - для записи.

Создание Linked Server в Sql Server для доступа к Excel

EXEC sp_addLinkedServer
    @server= N'XLSX_2010',
    @srvproduct = N'Excel',
    @provider = N'Microsoft.ACE.OLEDB.12.0',
    @datasrc = N'd:\tmp\TimeSheets.xlsx',
    @provstr = N'Excel 12.0; HDR=Yes';
GO

После создания связанного сервера можно будет просмотреть имена доступных листов.

Затем, чтобы обратиться к сервису:

SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]') или SELECT * FROM [XLSX_2010]...[Лист1$]

Обращение к лиcтам, диапазонам, полям

Для обращения к листу из SQL запроса нужно использовать имя листа, например: [Sheet1$] или [Лист1$]. Обращение к диапазону: [Sheet1$A16:F16].

Вставка данных в произвольное место

Примеры указания диапазона при вставке строк (insert)

• [table1$B4:E20]
• [table1$S4:U]
• [table1$F:G]

При вставке должны выполняться следующие условия:

• Первая строчка указанного диапазона дожна входить в диапазон ячеек с данными. Чтобы создать на листе диапазон с данными достаточно в углах некоторого прямоугольного диапазона (в левом верхнем и правом нижнем) вписать значение (C4:I7 на скриншоте). Т.е. сама первая строчка указанного в insert диапазона данные содержать не обязана, достаточно, чтобы она просто входила в такой диапазон. Иначе возникнет ошибка "This table contains cells that are outside the range of cells defined in this spreadsheet"
• Хвост диапазона должен содержать пустые строки (хотя бы одну).

Пример: Дан лист, где заполнены только 2 ячейки: C4, I7. После выполнения команды INSERT INTO [table1$E6:G] VALUES(2, 'FF','2014-01-03') результат будет как на скриншоте. Поясним: строка E6:G6 является первой строкой диапазона для вставки. Она входит в заполненный диапазон C4:I7. Поэтому данные были вставлены на следующей пустой строке - 8. Из этого примера становится ясно, что через OleDb нельзя работать с несколькими независимыми диапазонами на одном листе, используя вставку (update будет работать).

Ошибки

• System.Data.OleDb.OleDbException (0x80004005): Operation must use an updateable query. Соединение открыто для чтение, при этом происходит попытка внести изменения (выполнить insert, update или delete). Решение: открыть соединение для записи, установив свойство провайдера в строке соединения IMEX=3 (см. выше).
• System.Data.OleDb.OleDbException (0x80004005): "This table contains cells that are outside the range of cells defined in this spreadsheet". Такая ошибка возникает при подпытке обновить (update) или вставить (insert) значения в диапазоне, в котором отсутствуют данные на листе.
  1. Если нужно произвести запись в определенные ячейки инструкцией update, то

Ссылки

• https://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB
• https://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked
• https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null
• http://www.ashishblog.com/importexport-excel-xlsx-or-xls-file-into-sql-server/
• https://yoursandmyideas.com/2011/02/05/how-to-read-or-write-excel-file-using-ace-oledb-data-provider/
• https://stackoverflow.com/questions/46373895/how-to-open-a-huge-excel-file-efficiently Несколько способов открыть большой Excel файл, в т.ч. с помощью OleDb.

Insert-range.png

read.fsx

open System.Data.OleDb

// Settings
let root = __SOURCE_DIRECTORY__
let excelFile = sprintf @"%s\..\10.05.2016-06.10.2017.xlsx" root


let getConnStr filePath = sprintf "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='%s';Extended Properties=\"Excel 12.0;HDR=YES;\"" filePath

let getConnection str = new OleDbConnection(str)

let getFieldNames (reader: OleDbDataReader) = List.init (reader.FieldCount-1) reader.GetName

let readerToSeq (reader: OleDbDataReader) = 
  let lastIndex = reader.FieldCount-1
  seq {
    while reader.Read () do
      yield List.init lastIndex (fun i -> reader.GetValue(i))
  }

let read file = 
  use conn = file |> getConnStr |> getConnection
  conn.Open ()
  use command = new OleDbCommand ("select * from [Sheet0$]", conn)
  use reader = command.ExecuteReader()
  let headers = reader |> getFieldNames 
  headers |> printfn "%A"
  reader |> readerToSeq |> List.ofSeq |> printfn "%A"

read excelFile

write.fsx

open System.Data.OleDb

// Settings
let root = __SOURCE_DIRECTORY__
let excelFile = sprintf @"%s\output.xlsx" root


let getConnStr filePath = sprintf "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='%s';Extended Properties=\"Excel 12.0;HDR=YES;\"" filePath

let getConnection str = new OleDbConnection(str)

let writeTo file = 
  let execute conn sql = 
    use cmd = new OleDbCommand(sql, conn)
    cmd.ExecuteNonQuery()
  use conn = file |> getConnStr |> getConnection
  conn.Open ()
  let exec sql = execute conn sql |> ignore
  [
    "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE )"
    "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01')"
    "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03')"
    "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03')"
    "UPDATE [table1] SET name = 'DDDD' WHERE id = 3"
  ] 
  |> List.iter exec

writeTo excelFile

Спасибо за код. Пригодился.
Не подскажите ли мне, как узнать номер строки у ячейки? Мне нужно циклами SQL обойти таблицу, которая начинается с именованной ячейки.
Значение самой ячейки я получаю так
Set rs = objConn.execute("SELECT * FROM CustomerName")
Т.е. в rs записываю значение из ячейки с именем CustomerName.
Но как мне в переменную записать номер строки этой ячейки?

Точно сказать не могу, но подозреваю, что через OleDb узнать координаты именованной области невозможно. Это можно сделать средствами Office Open XML SDK.

Да, как оказалось, это сделать не получится.
Я нашел временный выход. Не очень красивый, но пока так - это запуск питоновского скрипта из VBS. Используя библиотеку xlrd я читаю xls, а через minidom записываю xml
Вот только, передачу параметров (адрес xml файла) из VBS в PYTHON скрипт реализовать получилось, но как передать обратно в VBS параметр из питона (например, по окончанию работы скрипта передать параметр main = "OK") я не нашел.

Если есть возможность выполнять VB скрипты, значит есть доступ ко всей объектной модели файла Excel и использование питона наряду с VB излишнее усложнение. Т.е. либо полностью питон использовать для получения данных из excel, либо VB, либо еще что-то, но достаточно одного языка программирования :).