gifts2017

Выгружаем в EXCEL с помощью ADO (часть 2)

Опубликовал Дмитрий К (ll13) в раздел Обмен - Загрузка и выгрузка в Excel

Наверное, уже многие знают, что с помощью технологии ADO можно быстро читать большие объемы информации из книг excel. Но ведь с помощью данной технологии можно также легко создавать и заполнять файлы excel. Данная статья является продолжением статьи "Выгружаем в EXCEL с помощью ADO"

Данная статья является логическим продолжением статьи Выгружаем в EXCEL с помощью ADO, в ней пойдёт речь о типизации выгружаемых данных, а также о втором способе создания файла эксель (без использования объекта ADOX.Catalog).

Итак, сначала типизация колонок в создаваемом экселевском файле (для примера из первой статьи):

......

Table = Новый COMОбъект("ADOX.Table");
Table.Name = "Table";

// Допустимые типы
// adDouble = 5 Значение с плавающей точкой двойной точности
// adDAte = 7 Дата
// adCurrency = 6 Денежная сумма
// adBoolean = 11 Булево
// adVarWChar = 202 Символьная строка Unicode, заканчивающаяся NULL
// adLongVarWChar = 203 Длинное строковое значение

Column = Новый COMОбъект("ADOX.Column");
Column.Name = "ЧисловойСтолбец";
Column.Type = 5;
Table.Columns.Append(Column);

Column = Новый COMОбъект("ADOX.Column");
Column.Name = "ДатаСтолбец";
Column.Type = 7;
Table.Columns.Append(Column);

Column = Новый COMОбъект("ADOX.Column");
Column.Name = "СтроковыйСтолбец";
Column.Type = 202;
Table.Columns.Append(Column);

Column = Неопределено;
Catalog.Tables.Append(Table);

Ну а теперь второй способ создания файла эксель(без использования объекта ADOX.Catalog):

 

СтрокаПодключения = "
|Provider=Microsoft.Jet.OLEDB.4.0;
|Data Source="
+ИмяФайлаЭксельНовый+";
|Extended Properties=""Excel 8.0;HDR=YES"";"
;

Connection = Новый COMОбъект("ADODB.Connection");

Connection.ConnectionString = СтрокаПодключения;
Connection.Open();

Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;

Command.CommandText = "CREATE TABLE [МояТаблица] (Символьный char(255), Дата date, Целый int, Дробный float)";
Command.Execute();

Command.CommandText = "INSERT INTO [МояТаблица] (Символьный, Дата, Целый, Дробный) values ('АБВГДЕЁ', '12/4/1955', '1234567', '12345,6789')";
Command.Execute();

// А вот так можно удалить все данные на листе, при этом сам лист останется
//Command.CommandText = "DROP TABLE [МояТаблица]";
//Command.Execute();

Command = Неопределено;
Connection.Close();

Единственное чему я не нашел пока применения, так это оператору ALTER, ни в какой форме он у меня не заработал... а по документации с сайта microsoft должен ...

Вроде бы ничего не забыл и нигде не ошибся :) Вот такая небольшая и лаконичная статья получилась ...

 

P.S. И всё таки забыл ... :) Записи таблицы можно изменять следующим образом(вдруг кому понадобится):

Command.CommandText = "UPDATE [МояТаблица] SET Символы='йцукен' WHERE Целое=12345";
Command.Execute();

Мне НЕ УДАЛОСЬ :

1). Удалять записи. (DELETE FROM [МояТаблица] WHERE Символы='АБВГДЕЁ')

2). Изменять структуру таблицы путём добавления и удаления столбцов, а так же изменения их типа. (Операторы: ALTER TABLE [МояТаблица] DROP COLUMN Дата, ALTER TABLE [МояТаблица] ADD НоваяКолонка data)

ОГРОМНАЯ просьба если кому-то удастся выполнить данные операции (или другие НЕ ОПИСАННЫЕ в данных статьях) сообщите мне. Заранее благодарен.

 

Источник: http://www.obrabotki.com/1s-excel-ado-2/

См. также

Подписаться Добавить вознаграждение

Комментарии

1. dmitriy d (dimaxmaster) 19.04.10 17:34
Плюсую, очень полезная вещь. Автор молодец.
2. oleg oleg (legioner) 21.04.10 03:50
Только почему в заголовке 7.7, когда код для 8ки?
3. Александр Цегельников (markers) 21.04.10 06:58
Интересно вот, есть ли возможность навести визуальный лоск через ADO (Просто мечтаю..), например там цвета/рамочки/шрифты...
Надо полагать что нельзя :(
А так выгружать через ADO скажем прайс, а потом по строчке красить и т.д., теряется весь смысл :( Но автору всё-равно спасибо!
4. david (dav405) 21.04.10 08:49
Тут у меня проблема на ту же тему(Jet):
http://infostart.ru/public/65304/
Смогли ли победить?

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

A caution about editing Excel data with ADO: When you insert text data into Excel with ADO, the text value is preceded with a single quote. This may cause problems later in working with the new data.

Короче, не работает....
5. Григорий Пейсхович (pga_dim) 21.04.10 09:48
Спасибо , дай ссылку где у Microsoft синтаксис подсмотреть по командам ADODB и по строке подключения .
6. Dmitry Afanasyev (afanasko) 21.04.10 10:46
Хорошая статья!

У меня получилось сделать апдейт. Использовал следующий синтаксис команды:
UPDATE [МояТаблица] SET [Символьный] = 'йцукен' WHERE [Целый]=1234567

С удалением данных из таблицы возникнут проблемы. Данный драйвер не поддерживает удаление данных в связанных таблицах. Для удаления данных нужно использовать драйвер Fox Pro.
7. Дмитрий К (ll13) 21.04.10 12:17
(2) Технология ADO универсальна и код под 7.7 можно переписать за 5 минут...
8. Дмитрий К (ll13) 21.04.10 12:20
(3) Визуальный лоск через ADO Вы особо не наведете... технология ADO - это технология работы с базами данных со всеми вытекающими последствиями, достоинствами и недостатками ...
9. Александр Цегельников (markers) 21.04.10 12:32
(8) Да я знаю, просто мечтаю.... есть просто задача ускорить формирование прайсов с 3-4 минут * 6446 строк * 7 колонок на более быстрое... Более быстрое это ADO, но с оформлением проблемы...
Сейчас у меня сделано прямое формирование прайс-листов через COM + COMSafetyArrays.
10. Дмитрий К (ll13) 21.04.10 12:34
(5)
http://support.microsoft.com/kb/316934/ru
http://www.connectionstrings.com/
Если что-то интересное найдете непременно напишите мне.
11. laview (v7plus) 27.04.10 14:37
Почему в созданной таблице excel стоит ' первым символом во всех ячейках, даже в заголовках?
12. Дмитрий К (ll13) 27.04.10 16:00
(11) Потому что у экселя данный символ - это признак символьных данных.
13. laview (v7plus) 28.04.10 13:24
Это я знаю. Как его убрать?
При создании колонок вроде явно пишем
Column.Type = 202;
imex = 1;
в строке соединения нелязя указать.
Что делать?
14. demonice (demonice) 03.05.10 17:00
Присоединяюсь к вопросу как убрать "'"?
15. Дмитрий К (ll13) 04.05.10 10:42
(13),(14) Все символьные типы, провайдер OLEDB предваряет этим знаком (так сказано на сайте microsoft), как от него избавиться я не знаю, если узнаете напишите тут. Но лично мне он никогда не мешал, более того даже в файле эксель который заполняется "вручную" предварять этим знаком символьные данные - хороший тон.
16. rasswet (rasswet) 13.12.10 15:50
с Экселем 2010 не работает.
строка подключения, которая работает Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\install\Export.xlsx;Extended Properties="Excel 12.0;HDR=NO;IMEX=1";
но при вставке таблицы пишет, что доступ только на чтение.
17. Максим Кузнецов (Makushimo) 26.12.11 14:52
(6) afanasko,
Объясните для тех кто в танке.

В статье инфа о том, как создать таблицу Ексель и в нее добавить строки,
и о том, как найти строку и изменить ее.

а Если нужно добавить строку в существующий лист файла Ексель, то как это сделать?
18. Dmitry Afanasyev (afanasko) 26.12.11 17:32
(17) Makushimo,
Если с помощью АДО, то очевидно, что нужно открыть существующий файл Эксель:
СтрокаПодключения = "
|Provider=Microsoft.Jet.OLEDB.4.0;
|Data Source="+ИмяФайлаЭксельСуществующий+";
|Extended Properties=""Excel 8.0;HDR=YES"";";

Connection = Новый COMОбъект("ADODB.Connection");

Connection.ConnectionString = СтрокаПодключения;
Connection.Open();
...Показать Скрыть


после этого данные добавляются легко:
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;

Command.CommandText = "INSERT INTO [МояТаблица] (Символьный, Дата, Целый, Дробный) values ('АБВГДЕЁ', '12/4/1955', '1234567', '12345,6789')";
Command.Execute();
...Показать Скрыть
19. Максим Кузнецов (Makushimo) 30.12.11 06:26
(18) afanasko,
Мой код
Процедура КнопкаВыполнитьНажатие()
ВыбФайл = "D:\МойФайл.xls";

СтрокаПодключения = "
|Provider=Microsoft.Jet.OLEDB.4.0;
|Data Source="+ВыбФайл+";
|Extended Properties=""Excel 8.0;HDR=YES"";";
Connection = Новый COMObject("ADODB.Connection");

Попытка
Connection.ConnectionString = СтрокаПодключения;
Connection.Open();
Исключение
Сообщить("Ошибка чтения: "+СокрЛП(ВыбФайл)+" "+ОписаниеОшибки());
Возврат;
КонецПопытки;


Лист = "[Лист1$]";
Command = Новый COMObject("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandText = "INSERT INTO "+Лист+"(Поле1, Поле2, Поле3, Поле4) values ('АБВГДЕЁ', 'ввв', 'ФАВУ', 'ФФФфФФ')";
Command.Execute();

Connection.Close();
КонецПроцедуры

Поле1, Поле2, Поле3, Поле4 - это названия колонок в первой строке листа1
выдает ошибку:

{Форма.Форма(233)}: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft JET Database Engine): Несоответствие типов данных в выражении условия отбора.
Command.Execute();
по причине:
Произошла исключительная ситуация (Microsoft JET Database Engine): Несоответствие типов данных в выражении условия отбора.

я явно где-то чего-то недопонял.
скажите где?
20. Dmitry Afanasyev (afanasko) 30.12.11 09:46
(19) Makushimo,
Данный код у меня работает без проблем. Может ты пытаешься вставить строковые данные в колонки с типом значения "Дата" или "Число"?
21. Алекс Ю (AlexO) 16.01.12 02:23
А почему везде примеры соединения через ADO только для изменений из 1C в Excel?
и нигде не нашел наоборот - из Excel в 1С. Везде только через Application (OLE).
А вроде чеерз ADO быстрее работает? Или уже без разницы? например, на загрузке 10 тыс строк.
(18) по-моему, вот это:
Extended Properties=""Excel 8.0...
обозначает версию Excel, и это Excel 2003 - а какая строка подключения для 2007-2010?
и где вообще можно подсмотреть строки подключения провайдера?
22. Алекс Ю (AlexO) 16.01.12 02:30
(0) а в чем отличие подключения ADOX или через провайдера DB? по скорости, функциональности, или еще что?
23. Алекс Ю (AlexO) 16.01.12 03:23
+(21) вот нашел (также и для (9) ):
Why Use ADO?
The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel:
Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.
Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.
ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format is maintained. If you require "conditional" formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.

"Зачем использовать ADO?
Использование ADO для передачи в- или получения данных из- книги Excel дает вам, разработчикам, несколько преимуществ над Automation в Excel:
- Выполнение. Microsoft Excel - это "внепроцессный" (запускается вне процессов) ActivX-сервер. ADO запускается "в процессах", и сохраняет преимущество как надстройки богатства возможностей внепроцессных вызовов.
- Масштабируемость. Для Web-приложений не всегда желательно использовать Automation в Microsoft Excel. ADO же предоставляет более масштабируемое решение для управления данными (чтение-запись) в книге Excel.
ADO может быть использвано конкретно для передачи данных в Книгу. Вы не можете использовать ADO для форматирования или внесения формул в ячейки.
Однако, вы можете перенести данные в Книгу с предварительным форматированием и обработкой. Если же вам потребуется "условное" форматирование уже после вставки данных в Книгу, вы можете добавить нужное форматирование с помощью Automation или с помощью макросов в Книге Excel."
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973
25. Дмитрий К (ll13) 08.02.12 14:02
(21) AlexO, Примеров чтения из Excel -> 1С через АДО полным полно.
Будет время напишу подробную статью посвященную этой теме, которая прольет свет на несколько не очевидных моментов.
26. Ivan Haos (haous) 08.02.12 17:22
Спасибо. Полезные вещи почерпнул для себя.
27. Юрий Зайцев (Yury1001) 14.02.12 13:37
(0) Большое спасибо, плюсую дважды, первый реально рабочий материал по теме на сайте который позволил в пределах часа написать выгрузку большого объема данных в excel.
За две минуты обработка добилась ошибки:

Command.Execute();
{D:\DATABASE\ВИКОSALES.ERT(ХХ)}: Microsoft JET Database Engine: Переполнение электронной таблицы.

Но это уже проблема клиента, что у него больше 65536 строк:)))

Ещё раз мой респект!

P.S. На 7.7 всё арбайтен, капитально!
28. Юрий Зайцев (Yury1001) 14.02.12 13:43
(19) Про несоответствие типов добавлю, 1С неявно разделяет число десятичной ТОЧКОЙ, а передавать нужно обязательно ЗАПЯТУЮ, то есть явно Заменой или Форматом, а так же для 1С 8, думаю, нужно убирать разделитель триад (неразрывный пробел по умолчанию).
29. Сергей (serggo) 09.04.12 12:12
Господа, а возможно ли сразу используя ADODB - запрос, прочитать не value из ячеек, а именно текст (Аналог ExDoc.Sheets(1).Cells(а,1).Text)?

При переборе recorset'a юзаю:

Пока НЕ RS.EOF() Цикл
         Артикул = RS.Fields(АртНом).value;
;


Естественно, при замене value на text ругается
30. Андрей (Genneral) 20.04.12 01:34
Промучался целый день, пытаясь сделать выгрузку в Excels через "Provider=Microsoft.Jet.OLEDB.4.0" регламентным заданием на стороне сервера 1с, на клиенте работает на сервере нет, и права крутил и что только не делал, оказалось все просто: Клиент 32-бит , Сервер 64-бит, а с 64-бит данный провайдер не работает, вот такой "нюанс".
31. IrinaKostroma (IrinaKostroma) 26.04.12 18:52
Народ, а кто-нибудь сталкивался при переносе данных с тем что если в данных есть апостроф
(например в наименовании товара), то ругается и не грузит данные.
Если данные без апострофов то все работает нормально...
Что делать в таких случаях?

Код такой(часть):
Command.CommandText = "
|INSERT INTO [Table] VALUES ('"+парТаб.КодПоставщика+"','"+парТаб.КодПроизводителя+"','"+парТаб.НаименованиеТовараПоставщика+"','"+парТаб.ЦенаПоставщика+"')";
Command.Execute();
32. Алексей Роза (DoctorRoza) 04.02.13 16:54
Нужная информация, респект автору. ;-)
33. Dmitriy Dyagilev (Dim Dimy4) 28.06.13 14:52
Тема раскрыта не полностью.
Попробуйте использовать в Excel именованный диапазон.
При чтении по диапазонам все путем.
А вот при записи, если у вас Именованный диапазон это 1 ячейка (и таких диапазонов несколько подряд в столбик).
Делаешь апдейт через РекордСет этого диапазона. Вместо того что бы обновить значение в ячейке именованного диапазона (адрес !$B$2) он обновляет ячейку в первой строке (с адресом !$B$1). Соответственно обновляешь следующий именованный диапазон (адрес !$B$4) опять обновляется ячейка с адресом (!$B$1).
Кто с таким сталкивался и как решить?
34. Виталий Быков (sick_russian) 04.07.13 03:52
А не подскажите пример запуска макроса в книге через адо?
35. Алексей Михайлов (Kinestetik) 26.02.15 09:56
Люди, такая проблема: в процедуре создаётся нетипизованная ТЗ, в которую из EXCEL через ADO выкачиваются данные. Проблема в следующем: есть ячейка с дробным числом: 0,54 - АДО её считывает, в рекордсете она есть, а при попадании в ТЗ -> NULL....

Файло прикладываю
Прикрепленные файлы:
МатрицаЗаказовБоржоми.epf
Распоряжение_ТИ000009253.XLS
36. Алексей Михайлов (Kinestetik) 26.02.15 11:43
Народ, решилось всё. Был запрятан узкий столбец, из за него нулл выходил
37. Dmitriy Dyagilev (Dim Dimy4) 20.04.15 12:50
(33) Dim Dimy4,
пришлось делать через объект COMSafeArray
38. Artem Mavrin (artemavrin) 28.04.15 16:20
(14) demonice, может кому еще полезно будет, чтоб избавиться от апострофа ' в строковых данных необходимо сменить провайдера на Provider=Microsoft.ACE.OLEDB.12.0
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа