Выгружаем в EXCEL с помощью ADO

Программирование - Практика программирования

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

Итак, постановка задачи: Создать с помощью механизма ADO файл excel и выгрузить в него из справочника "Номенклатура" код и наименование.

ИмяФайлаЭксель = "D:\Export.xls"; // Имя создаваемого файла

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

// Создаем объект ADOX.Catalog
Catalog = Новый COMОбъект("ADOX.Catalog");
Catalog.ActiveConnection = СтрокаПодключения;

// Создаем таблицу и добавляем в неё два столбца
Table = Новый COMОбъект("ADOX.Table");
Table.Name = "Table";
Table.Columns.Append("Code");
Table.Columns.Append("Description");

// Присоединяем таблицу
Catalog.Tables.Append(Table);
Table = Неопределено;
Catalog = Неопределено;

// Создаем соединение
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandType = 1;

// Получаем номенклатуру для выгрузки
Запрос = Новый Запрос("
|ВЫБРАТЬ
|    Номенклатура.Код,
|    Номенклатура.Наименование
|ИЗ
|    Справочник.Номенклатура КАК Номенклатура"
);

Результат = Запрос.Выполнить();
Выборка = Результат.Выбрать();

// Заполняем таблицу данными выборки
Пока Выборка.Следующий() Цикл
       
Command.CommandText = "
        |INSERT INTO [Table] VALUES ('"
+Выборка.Код+"','"+Выборка.Наименование+"')";
       
Command.Execute();
КонецЦикла;

// Закрываем соединение
Command = Неопределено;
Connection.Close();
Connection = Неопределено;

Все, готово !

Плюсы данного способа формирования файла excel:

- Большая скорость работы.

- Не требуется запуск самого процесса excel.exe, как это происходит в случае использования OLE-сервера excel.application, а следовательно на него не тратятся ресурсы. Кроме того нет необходимости отслеживать и перехватывать ошибки, связанные с процессом excel.exe, те кто использовал OLE-cоединение - тот поймет о чем я говорю, мало радости когда в случае программной или пользовательской ошибки процесс excel.exe остается висеть в памяти.

- Не требуется самого приложения EXCEL, достаточно что бы на компьютере был установлен OLEDB провайдер Microsoft.Jet.OLEDB.4.0 (он входит в состав практически всех ОС семейства Windows) Это особенно актуально, когда данный код выполняется на сервере 1С:Предприятия, например в качестве регламентного задания.

Минусы тоже имеются:

- Нельзя красиво оформить ячейки - шрифт, рамки и т.д.

P.S. Возможен "комбинированный" способ: с помощью ADO формируется файл, а затем с помощью OLE "наводится марафет".

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

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

Скачать файлы

Наименование Файл Версия Размер
ADO_1s.rar
.rar 3,97Kb
16.06.15
4
.rar 3,97Kb 4 Скачать

См. также

Комментарии
1. Алексей Козаченко (borman) 71 02.02.10 07:39 Сейчас в теме
Вот здесь можете посмотреть готовую обработку для УТ, которая создает книгу excel со страницами.
Да простит меня автор за саморекламу 8-) .
2. RE4RAESER (resear4er@yandex.ru) 03.02.10 16:23 Сейчас в теме
Многовато писанины.
Почему бы не воспользоваться напр. таким кодом для выгрузки данных в excel:
ИмяФайлаЭксель = "D:\Export.xls";
Макет = ЭтотОбъект.ПолучитьМакет("Макет");
ТабДок = Новый ТабличныйДокумент;
// Получаем номенклатуру для выгрузки
Запрос = Новый Запрос(" ВЫБРАТЬ
| Номенклатура.Код,
| Номенклатура.Наименование
|ИЗ
| Справочник.Номенклатура КАК Номенклатура");
Результат = Запрос.Выполнить().Выбрать();
Пока Результат.Следующий() Цикл
строка = Макет.ПолучитьОбласть("строка");
строка.Параметры.Заполнить(результат);
ТабДок.Вывести(строка);
КонецЦикла;
ТабДок.Записать(ИмяФайлаЭксель,ТипФайлаТабличногоДокумента.XLS97);

Код отработал довольно быстро: 12500 запис. за 12сек.
3. Дмитрий К (ll13) 738 03.02.10 16:37 Сейчас в теме
(2) А вы попробуйте выполнить свой код на сервере ;)
А Вы что на все случаи жизни макеты рисуете ?
4. RE4RAESER (resear4er@yandex.ru) 03.02.10 16:44 Сейчас в теме
II13 спасибо за ответ! Теперь понял, где данную статью можно применить. :)

P.S Нет, не на все :)
5. dmitriy d (dimaxmaster) 08.04.10 16:04 Сейчас в теме
Автору огромный +. Не знал "силу маханизма" АDO. Спасибо!
6. dmitriy d (dimaxmaster) 18.04.10 18:39 Сейчас в теме
Вопрос к автору: выгрузка происходит в строке, т.е. все данные выгружаются как строка. А нет ли возможности выгрузить Число как Число?
7. Дмитрий К (ll13) 738 18.04.10 19:55 Сейчас в теме
(6) Где-то так :
Table = Новый COMОбъект("ADOX.Table");
Table.Name = "TestTable";

// Допустимые типы
// 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 = Неопределено;
Catalog.Tables.Append(Table);

8. dmitriy d (dimaxmaster) 19.04.10 14:32 Сейчас в теме
9. PlatonovStepan (Jogeedae) 39 21.04.10 02:20 Сейчас в теме
поясните:
Command.CommandType = 1;

10. Дмитрий К (ll13) 738 21.04.10 12:14 Сейчас в теме
(9) Тип передаваемой команды
1 - текстовая команда SQL
Ещё может быть хранимая процедура и по-моему таблица(но точно не помню)
Но с ними не экспериментировал .... если осилите напишите :)
11. Александр Цегельников (markers) 186 29.07.10 06:55 Сейчас в теме
(2) Ваш вариант не всегда годен, к примеру проблема с обрезанием кода номенклатуры при сохранении в эксель (обрезает ведущие нули), нельзя сделать ссылку и много ещё чего..
12. Eddie Mos (Eddmos) 25.10.10 14:50 Сейчас в теме
А можно к модулю приблудить позиции
при выводе из номенклатуры
штрих код и стоимость товара????

Возможен ли обратный вариант
загрузка из Excel в 1С
например в "Реализация товаров и услуг: продажа" опять же по тем позициям
код штрих наименование

спс
13. Дмитрий К (ll13) 738 25.10.10 20:17 Сейчас в теме
(12)
А можно к модулю приблудить позиции
при выводе из номенклатуры
штрих код и стоимость товара????
Разумеется, я не вижу здесь никаких проблем.

Возможен ли обратный вариант
загрузка из Excel в 1С
например в "Реализация товаров и услуг: продажа" опять же по тем позициям
код штрих наименование
На данном сайте таких обработок целая куча...
14. Марк (marku) 53 20.11.10 00:08 Сейчас в теме
Спасибо Автору ll13 :idea:
вот еще в сети нашел для 2007:
Excel 2007 Connection String
"Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;\"";

СтрокаПодключения = "
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source="+ИмяФайлаЭксель+";
|Extended Properties=""Excel 12.0;HDR=No;"";";
15. Валентин Елфимов (ratinc) 22.02.11 20:05 Сейчас в теме
Созданный таким образом файл затем открывается с предупреждением что он повреждён. Если сохранять xlsx то вообще не открывается
16. Алекс Ю (AlexO) 116 16.01.12 02:29 Сейчас в теме
(14) Наверное, HDR=Yes надо ставить.
17. Алекс Ю (AlexO) 116 16.01.12 02:51 Сейчас в теме
(14) хотя нет, это не влияет в данном случае (но я до конца не уверен):
Where "HDR=Yes" means that there is a header row in the cell range
(or named range), so the provider will not include the first row of the
selection into the recordset. If "HDR=No", then the provider will include
the first row of the cell range (or named ranged) into the recordset.
"Где "HDR=Yes" означает, что на открываемом листе есть строка-заголовок в диапазоне ячеек выбранных данных(или именованном диапазоне), т.о. Подключение не включит первую строку набора в Рекордсет (набор записей или набор данных).
Если же "HDR=No", то Подключение включит первую строку диапазона ячеек (или именованного диапазона) в Рекордсет."
18. Сергей Белов (Unicorn31) 45 24.02.12 22:32 Сейчас в теме
адо использовал раньше для работы с sql базами- работает на ура... как то не приходило в голову , что для 1с можно использовать... спасибо за идею
19. IrinaKostroma (IrinaKostroma) 23.08.12 20:34 Сейчас в теме
Все бы хорошо в этом способе выгрузки, но есть проблемка
почему то в созданном файле Excel в начале каждого слова в столбце стоит апостроф

Кто знает можно ли сделать чтобы он не появлялся при выгрузке?
20. Алексей Роза (DoctorRoza) 04.02.13 16:51 Сейчас в теме
Ох, помогла статья, очень нужная оказалась. :)
21. Виталий Быков (sick_russian) 08.07.13 10:32 Сейчас в теме
А как сделать выгрузку чтобы не создавать файл а открыть для записи существующий?
22. Dmitriy Dyagilev (Dim Dimy4) 09.04.14 10:21 Сейчас в теме
Столкнулся с проблемой выгрузки из 1С 8 в Excel по ADO.
Задача "Выгружать данные в именованный диапазон ячеек", при чём существует несколько именованных диапазонов ячеек. Расположены они разнообразно на листе.

Пример: заданы несколько именованных диапазонов.
1. Имя диапазона "Дата", адрес диапазона "A1";
2. Имя диапазона "Товар", адрес диапазона "A3:A3000";
3. Имя диапазона "Артикул", адрес диапазона "B3:B3000";
4. Имя диапазона "Количество", адрес диапазона "C3:C3000";
и т.д.

Делаешь поиск диапазона на листе, находит все ок.
Делаешь запись в ячейки диапазона значений, пишет. Но проставляет не первую, вторую, третью, т.д. ячейку адреса диапазона , а первую,вторую, третью, т.д. ячейку столбца.
Например записываешь наименование номенклатуры в диапазон "Товар". Записывается:
Товар1 вместо ячейки "A3" в ячейку "A1";
Товар2 вместо ячейки "A4" в ячейку "A2";
Товар3 вместо ячейки "A5" в ячейку "A3";
и т.д. Кто сталкивался с такой проблемой и какое решение находили?
23. Елена К (Ele1234567) 29.04.14 11:18 Сейчас в теме
Очень интересное решение, обязательно нужно попробовать на практике. Сейчас как раз задача по выгрузке данных в регламентном задании в эксель и вот на сервере с этим возникают загвоздки.
24. Vodoley (Vodoley) 2 28.07.14 18:47 Сейчас в теме
наткнулся на то, что :
1. после имени листа ставлю $ иначе не находит

2. вопрос - как записать в эксель числовые данные так, чтобы он их и принял как числовые (без треугольничков в углах ячейки, мол число отформатировано как текст ??
25. 1801 19.09.14 18:11 Сейчас в теме
Попробовал - работает, переделал этот шаблон под выгрузку регистра накопления "Продажи" в Excel. (Аналитикам так удобней работать с данными, путем построения в Excel сводных таблиц в различных разрезах.) Большое вам спасибо!

26. 1801 19.09.14 18:26 Сейчас в теме
Вопрос: можно ли вместо апострофа использовать другой разделитель? У нас в справочнике в наименовании товара апостроф иногда встречается и обработка на этих местах спотыкается.
27. Dmitriy Dyagilev (Dim Dimy4) 20.04.15 12:49 Сейчас в теме
(22) Dim Dimy4,
пришлось делать через объект COMSafeArray, поищите информацию.
Оставьте свое сообщение