Основная функция: ВыгрузитьТЗвExcel_ADO.
Все значения из ТЗ выгружаются в виде строк. Если есть необходимость после выгрузки ТЗ работать с числами в Excel, то можно преобразовать ячейки, содержащие числа в виде строк, в числовые ячейки с помощью формулы =ЗНАЧЕН(ПОДСТАВИТЬ(Текст; "."; ",")) где Текст - это ссылка на ячейку, содержащую число в виде текста.
// Получает структуру для индикации прогресса цикла.
//
// Параметры:
// КоличествоПроходов – Число - максимальное значение счетчика;
// ПредставлениеПроцесса – Строка, "Выполнено" – отображаемое название процесса;
// ВнутреннийСчетчик - Булево, *Истина - использовать внутренний счетчик с начальным значением 1,
// иначе нужно будет передавать значение счетчика при каждом вызове обновления индикатора;
// КоличествоОбновлений - Число, *100 - всего количество обновлений индикатора;
// ЛиВыводитьВремя - Булево, *Истина - выводить приблизительное время до окончания процесса;
// РазрешитьПрерывание - Булево, *Истина - разрешает пользователю прерывать процесс.
// МинимальныйПериодОбновления - Число, *1 - с, обновлять не чаще чем этот период,
// 0 - по количеству обновлений,
// эта реализация не поддерживает дробные значения;
//
// Возвращаемое значение:
// Структура - которую потом нужно будет передавать в метод ЛксОбработатьИндикатор.
//
Функция ЛксПолучитьИндикаторПроцесса(Знач КоличествоПроходов = 0,
ПредставлениеПроцесса = "Выполнение", ВнутреннийСчетчик = Истина,
Знач КоличествоОбновлений = 100, ЛиВыводитьВремя = Истина, РазрешитьПрерывание = Истина,
МинимальныйПериодОбновления = 1) Экспорт
Индикатор = Новый Структура;
Если КоличествоПроходов = 0 Тогда
Состояние(ПредставлениеПроцесса + "...");
КоличествоПроходов = 1;
КонецЕсли;
Индикатор.Вставить("КоличествоПроходов", КоличествоПроходов);
Индикатор.Вставить("ПредставлениеПроцесса", ПредставлениеПроцесса);
Индикатор.Вставить("ЛиВыводитьВремя", ЛиВыводитьВремя);
Индикатор.Вставить("РазрешитьПрерывание", РазрешитьПрерывание);
Индикатор.Вставить("ДатаНачалаПроцесса", ТекущаяДата());
Индикатор.Вставить("МинимальныйПериодОбновления", МинимальныйПериодОбновления);
Индикатор.Вставить("ДатаСледующегоОбновления", Дата('00010101'));
Индикатор.Вставить("ВнутреннийСчетчик", ВнутреннийСчетчик);
Индикатор.Вставить("Шаг", ?(КоличествоОбновлений > 0, КоличествоПроходов / КоличествоОбновлений, 0));
Индикатор.Вставить("СледующийСчетчик", 0);
Индикатор.Вставить("Счетчик", 1);
Возврат Индикатор;
КонецФункции // ЛксПолучитьИндикаторПроцесса()
// Проверяет и обновляет индикатор. Нужно вызывать на каждом проходе индицируемого цикла.
//
// Параметры:
// Индикатор – Структура – индикатора, полученная методом ЛксПолучитьИндикаторПроцесса;
// Счетчик – Число – внешний счетчик цикла, используется при ВнутреннийСчетчик = Ложь.
//
Процедура ЛксОбработатьИндикатор(Индикатор, Счетчик = 0) Экспорт
Если Индикатор.ВнутреннийСчетчик Тогда
Счетчик = Индикатор.Счетчик;
КонецЕсли;
Если Индикатор.РазрешитьПрерывание Тогда
ОбработкаПрерыванияПользователя();
КонецЕсли;
ОбновитьИндикатор = Истина;
ТекущаяДата = ТекущаяДата();
Если Индикатор.МинимальныйПериодОбновления > 0 Тогда
Если ТекущаяДата >= Индикатор.ДатаСледующегоОбновления Тогда
Индикатор.ДатаСледующегоОбновления = ТекущаяДата + Индикатор.МинимальныйПериодОбновления;
Иначе
ОбновитьИндикатор = Ложь;
КонецЕсли;
КонецЕсли;
Если ОбновитьИндикатор Тогда
Если Индикатор.Шаг > 0 Тогда
Если Счетчик >= Индикатор.СледующийСчетчик Тогда
Индикатор.СледующийСчетчик = Цел(Счетчик + Индикатор.Шаг);
Иначе
ОбновитьИндикатор = Ложь;
КонецЕсли;
КонецЕсли;
КонецЕсли;
Если ОбновитьИндикатор Тогда
Индикатор.СледующийСчетчик = Цел(Счетчик + Индикатор.Шаг);
Если Индикатор.ЛиВыводитьВремя Тогда
ПрошлоВремени = ТекущаяДата - Индикатор.ДатаНачалаПроцесса;
Осталось = ПрошлоВремени * (Индикатор.КоличествоПроходов / Счетчик - 1);
Часов = Цел(Осталось / 3600);
Осталось = Осталось - (Часов * 3600);
Минут = Цел(Осталось / 60);
Секунд = Цел(Цел(Осталось - (Минут * 60)));
ОсталосьВремени = Формат(Часов, "ЧЦ=2; ЧН=00; ЧВН=") + ":"
+ Формат(Минут, "ЧЦ=2; ЧН=00; ЧВН=") + ":"
+ Формат(Секунд, "ЧЦ=2; ЧН=00; ЧВН=");
ТекстОсталось = "Осталось: ~" + ОсталосьВремени;
Иначе
ТекстОсталось = "";
КонецЕсли;
ТекстСостояния = Индикатор.ПредставлениеПроцесса + " "
+ Формат(Счетчик / Индикатор.КоличествоПроходов * 100, "ЧЦ=3; ЧДЦ=0") + "% " + ТекстОсталось;
Если ТипЗнч(Индикатор) = Тип("СтрокаТаблицыЗначений") Тогда
ТаблицаИндикаторов = Индикатор.Владелец();
ИндексИндикатора = ТаблицаИндикаторов.Индекс(Индикатор);
Если ИндексИндикатора > 0 Тогда
ТекстСостояния = ТаблицаИндикаторов[ИндексИндикатора - 1].ТекстСостояния + " >> " + ТекстСостояния;
КонецЕсли;
Индикатор.ТекстСостояния = ТекстСостояния;
КонецЕсли;
Состояние(ТекстСостояния);
КонецЕсли;
Если Индикатор.ВнутреннийСчетчик Тогда
Индикатор.Счетчик = Счетчик + 1;
КонецЕсли;
Если Счетчик = Индикатор.КоличествоПроходов Тогда
Состояние("");
КонецЕсли;
КонецПроцедуры // ЛксОбработатьИндикатор()
// Преобразование числа к виду, необходимому для последующей загрузки:
// - удаление символов разделителей разрядов;
// - использование "." в качестве символа-разделителя целой и дробной части;
// - представление нулевых чисел в виде "0", а не "".
Функция ЧислоВСтроку(ЗначениеЧисла) Экспорт
Если ТипЗнч(ЗначениеЧисла) = Тип("Число") Тогда
Возврат Формат(ЗначениеЧисла, "ЧРД=.; ЧГ=; ЧН=");
Иначе
Возврат ЗначениеЧисла;
КонецЕсли;
КонецФункции // ЧислоВСтроку(ЗначениеЧисла)
// Создает объект ADODB.Connection
// Для работы с EXCEL с помощью MS ADODB.Connection.
// Поддерживаемый тип файлов Excel: *.xls
//
// Для файлов *.xls (Excel 1997-2003): Jet.OLEDB.4.0
// Стандартное подключение, как правило, не требующее установки дополнительного ПО.
// Рекомендуется установить последний Service Pack Windows.
//
// Функция создает и открывает объект ADODB.Connection с подключением к файлу "ФайлExcel"
// При успешном создании и подключении возвращает объект ADODB.Connection,
// в случае возникновения ошибки возвращает "Неопределено"
Функция СоздатьADODBConnection(ФайлExcel) Экспорт
типФайла = Прав(СокрП(ФайлExcel), 4);
Если типФайла = ".xls" Тогда
СonnectionString = "
|Provider=Microsoft.Jet.OLEDB.4.0;
|Data Source=" + ФайлExcel + ";
|Extended Properties=""Excel 8.0;HDR=No;"""; //IMEX=1;
Иначе
Сообщить("Не распознано расширение файла " + ФайлExcel);
Возврат Неопределено;
КонецЕсли;
// Инициализация основного объекта ADODB.Connection. Открытие соединения.
Попытка
ADODBConnection = Новый COMОбъект("ADODB.Connection");
ADODBConnection.ConnectionString = СonnectionString;
ADODBConnection.Open();
Исключение
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
Возврат Неопределено;
КонецПопытки;
Возврат ADODBConnection;
КонецФункции // СоздатьADODBConnection(ФайлExcel)
// Получает Таблицу значений. Выгружает ее в файл Excel с помощью объектной модели ADO и ADOX
//
// Параметры:
// ТЗ – ТаблицаЗначений, выгружаемая Таблица значений;
// ФайлExcel – Строка, полное имя файла Excel, в который выгружается ТЗ;
// ИмяЛиста - имя листа Excel, в который выгружается ТЗ.
//
// Перед выгрузкой ТЗ Функция проверяет наличие файла с полным именем ФайлExcel.
// Если такого файла нет, то он создается и в нем создается новый лист с именем ИмяЛиста.
// Если ФайлExcel уже существует, то Функция создает в нем новый лист с именем ИмяЛиста.
// Если же в ФайлExcel существует лист с именем ИмяЛиста, то Функция сообщает об ошибке и возвращает Ложь.
//
// В первую строку листа книги записываются имена колонок ТЗ, далее выгружаются непосредственно строки ТЗ.
// Все значения из ТЗ выгружаются в виде строк.
// Числовые значения с помощью Функции ЧислоВСтроку(ЗначениеЧисла) преобразуются к виду, необходимому для последующей загрузки в 1С.
//
// Возвращаемое значение:
// Булево - результат выполнения выгрузки, Истина - выгрузка прошла успешно, Ложь - выгрузка завершилась с ошибкой.
//
Функция ВыгрузитьТЗвExcel_ADO(ТЗ, ФайлExcel, ИмяЛиста) Экспорт
ADODBConnection = СоздатьADODBConnection(ФайлExcel);
Если ADODBConnection <> Неопределено Тогда
// Создаем объект ADOX.Catalog (это книга Эксель)
Попытка
Catalog = Новый COMОбъект("ADOX.Catalog");
Catalog.ActiveConnection = ADODBConnection;
Исключение
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
Catalog = Неопределено;
ADODBConnection.Close();
ADODBConnection = Неопределено;
Возврат Ложь;
КонецПопытки;
//{Параметры метода Append объекта Columns: Name, Type (не обязательный), DefinedSize (не обязательный)
// Type - Целое число (long), тип данных параметра (строка, число, булево и т.д.).
// - adDouble, 5
// - adDate, 7
// - adCurrency, 6
// - adBoolean, 11
// - adVarWChar, 202
// - adLongVarWChar («memo»), введите 203
//}
// Создаем таблицу и добавляем в неё столбцы (это лист Эксель)
Попытка
Table = Новый COMОбъект("ADOX.Table");
Table.Name = ИмяЛиста;
// Создаем колонки листа
Для каждого тКол Из ТЗ.Колонки Цикл
Table.Columns.Append(тКол.Имя);
КонецЦикла;
// Присоединяем лист к книге
Catalog.Tables.Append(Table);
Исключение
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
Table = Неопределено;
Catalog = Неопределено;
ADODBConnection.Close();
ADODBConnection = Неопределено;
Возврат Ложь;
КонецПопытки;
// Заполняем лист данными выборки
Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = ADODBConnection;
Command.CommandType = 1; //adCmdText
Индикатор = ЛксПолучитьИндикаторПроцесса(ТЗ.Количество(), "Выгрузка таблицы " + ИмяЛиста + " ->", , , Ложь, , 0);
Для каждого СтрокаТЗ Из ТЗ Цикл
СтрЗнач = "";
Для каждого ячТЗ Из СтрокаТЗ Цикл
СтрЗнач = СтрЗнач + ",'" + ЧислоВСтроку(ячТЗ) + "'";
КонецЦикла;
СтрЗнач = "(" + Сред(СтрЗнач, 2) + ")";
Command.CommandText = "INSERT INTO [" + ИмяЛиста + "] VALUES " + СтрЗнач;
Попытка
Command.Execute();
Исключение
Сообщить("Не удалось записать строку с данными " + СтрЗнач + "; Номер строки ТЗ: " + Индикатор.Счетчик);
КонецПопытки;
ЛксОбработатьИндикатор(Индикатор);
КонецЦикла;
Command = Неопределено;
Table = Неопределено;
Catalog = Неопределено;
ADODBConnection.Close();
ADODBConnection = Неопределено;
Иначе
Возврат Ложь;
КонецЕсли;
Возврат Истина;
КонецФункции // ВыгрузитьТЗвExcel_ADO(ТЗ, ФайлExcel, ИмяЛиста)
При написании данной статьи использовались материалы из следующих публикаций:
- Загрузка из Excel в 1С тремя методами. Часть 1. MS ADODB.Connection автор StepByStep
- Выгружаем в EXCEL с помощью ADO автор ll13
- Правильная индикация прогресса цикла автор tormozit
Большое спасибо выше перечисленным авторам за интересные публикации!
Также использовалась информация из MSDN.
Плюсы и минусы работы с Excel через ADO описаны в работах ll13 и StepByStep, не буду повторяться.
