При написании публикации использовалась версия платформы 8.3.6.1760.
Модифицирована на версии платформы 8.3.10.2168.
Заметки из Зазеркалья: http://v8.1c.ru/o7/
Одним из полезных нововведений на платформе 8.3.6 стала возможность возможность импорта содержимого из файлов в форматах XLS (Microsoft Excel 97), Microsoft Office OpenXML (Microsoft Excel 2007- 2010) и ODS (OpenDocument) в табличный документ.
На платформе 8.3.10 появилась возможность считывания данных с отдельных листов книги EXCEL.
Данная возможность доступна как в интерактивном режиме, так и из встроенного языка.
Реализована поддержка вставки из буфера обмена областей, скопированных из Microsoft Excel и OpenOffice Calc.
Реализация импорта из EXCEL на встроенном языке стала возможна в результате нововведений в функционал объекта "ТабличныйДокумент":
Для метода "Прочитать" объекта "ТабличныйДокумент" реализован параметр "СпособЧтенияЗначений" (Новое системное перечисление "СпособЧтенияЗначенийТабличногоДокумента").
В данной публикации приводится пример реализации функционала импорта из файлов EXCEL (xlsx, xls, ods) в таблицу значений в режиме 1С:Предприятие на платформе 8.3.6.
Методы загрузки из внешнего источника:
- Метод "MS ADO" (Чтение файлов xls, xlsx, xlsb средствами Microsoft ADO): //infostart.ru/public/163640/
- Метод "MS EXCEL" (Чтение файлов xls, xlsx, xlsb с картинками средствами Microsoft Office): //infostart.ru/public/163641/
- Метод "LO CALC" (Чтение файлов xls, xlsx, xlsb, ods, sxc с картинками средствами LibreOffice): //infostart.ru/public/163642/
- Метод "NativeXLSX" (Чтение файлов xlsx с картинками средствами 1С. ПостроительDOM): //infostart.ru/public/300092/
- Метод "NativeXLSX". Предыдущий вариант (Чтение файлов xlsx средствами 1С. ЧтениеXML)://infostart.ru/public/225624/
- Метод "Excel1C" (Загрузка на платформе 8.3.6 с картинками. Чтение файлов xls, xlsx, ods): //infostart.ru/public/341855/
- Список листов файла: //infostart.ru/public/163724/
Данный функционал включен в обработку:
Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С: //infostart.ru/public/120961/
О файле EXCEL:
В общем случае файл EXCEL содержит несколько листов с данными.
Метод табличного документа "Прочитать" читает все листы в 1 (один) табличный документ на платформах 8.3.6-8.3.9, а на платформе 8.3.10 можно считать отдельные листы файла EXCEL.
Поэтому для использования данного функционала на версиях платформы 8.3.6-8.3.9 необходимо использовать файлы EXCEL, содержащие 1 (один) лист с данными, для платформы 8.3.10, такого ограничения нет.
О листе файла EXCEL:
Содержимое на листе должно быть разнесено по соответствующим колонкам по назначению.
В качестве примера содержимого листа может служить обычный прайс.
Рекомендутся ячейки таблицы на листе файла оформлять рамкой "Все границы".
Колонки результирующей таблицы значений формируются по количеству, определяемому свойством "ШиринаТаблицы" табличного документа.
В последствии для удобства пустые колонки удаляются из результирующей таблицы значений.
О типах значений файла EXCEL:
Числа - как "число", даты - как "дата", Стоки - как "строка", Булево - как "строка" вида "ИСТИНА" ("TRUE") или "ЛОЖЬ" ("FALSE").
В результирующую таблицу значений записываются типизованные значения.
О картинках файла EXCEL:
В исходном файле EXCEL это могут быть как ссылки на файлы, разделенные между собой символом "Перевод строки" в пределах одной ячейки, так и собственно картинки.
Картинки должны находиться в пределах ячейки. В одной ячейке может быть несколько картинок.
В результирующую таблицу значений записываются ссылки на файлы картинок.
Каждая ячейка колонки может, в общем случае, содержать несколько ссылок на файлы, разделенные между собой символом "Перевод строки".
Перем ВерсияПриложенияБезНомераСборки;
&НаСервере
Процедура ПриСозданииНаСервере(Отказ, СтандартнаяОбработка)
Перем СистемнаяИнформация, СписокЛистов;
СистемнаяИнформация = Новый СистемнаяИнформация;
ВерсияПриложенияБезНомераСборки = ВерсияБезНомераСборки(СистемнаяИнформация.ВерсияПриложения);
// Чтение в реквизит обработки, который может быть выведен на форму обработки.
// Объект.ТабличныйДокумент.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
// Запись табличного документа в файл на диске.
// Для работы с табличным документом предусмотрена утилита 1С http://v8.1c.ru/metod/fileworkshop.htm
// Объект.ТабличныйДокумент.Записать("D:\Товар1Лист.MXL", ТипФайлаТабличногоДокумента.MXL);
// Примеры вызова:
Если ВерсияПриложенияМеньше8310() Тогда
// Платформа 8.3.9 и более ранние (Файл EXCEL как 1 лист).
ИмяФайла = "D:\1_LIST.xlsx";
ТЗ = ЗагрузитьМетодом_EXCEL1C(ИмяФайла, ""); // Считать весь файл.
ТЗ = ЗагрузитьМетодом_EXCEL1C(ИмяФайла, "", 1, 3, 4); // Считать с 3-ой до 4-ой строки всего файла.
Иначе
// Платформа 8.3.10 (Облать = Лист данных).
ИмяФайла = "D:\N_LIST.xlsx";
СписокЛистов = ПолучитьСписокЛистов_EXCEL1C(ИмяФайла);
ТЗ = ЗагрузитьМетодом_EXCEL1C(ИмяФайла, СписокЛистов[0].Значение); // Считать 1-ый лист из списка по алфавиту.
ТЗ = ЗагрузитьМетодом_EXCEL1C(ИмяФайла, СписокЛистов[1].Значение); // Считать 2-ой лист из списка по алфавиту.
ТЗ = ЗагрузитьМетодом_EXCEL1C(ИмяФайла, СписокЛистов[1].Значение, 1, 3, 4); // Считать с 3-ой до 4-ой строки 2-го листа из списка по алфавиту.
КонецЕсли;
КонецПроцедуры
&НаСервере
Функция ПолучитьСписокЛистов_EXCEL1C(Знач ФайлEXCEL)
Перем ТабличныйДокумент, ОбластьТД;
Перем СписокЛистов;
СписокЛистов = Новый СписокЗначений;
ТабличныйДокумент = Новый ТабличныйДокумент;
Попытка
// Выполняется долго на больших файлах.
ТабличныйДокумент.Прочитать(ФайлEXCEL);
Исключение
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
Возврат Новый СписокЗначений;
КонецПопытки;
Для Каждого ОбластьТД ИЗ ТабличныйДокумент.Области Цикл
СписокЛистов.Добавить(ОбластьТД.Имя);
КонецЦикла;
Возврат СписокЛистов;
КонецФункции
// Метод "EXCEL1C" для файлов EXCEL с расширениями: xlsx, xls, ods.
//
// Параметры:
// ФайлEXCEL - Полное имя файла (путь к файлу с именем файла и расширением).
// ИмяЛиста - Имя листа файла (платформа 8.3.10).
// СтрокаЗаголовка (по умолчанию = 1) - Номер строки файла типа MXL, в которой расположены заголовки колонок.
// Не используется.
// В обработке 1-я строка анализируется для сопоставления колонок файла с реквизитами объектов 1С.
// НачСтрока (по-умолчанию = 0) - Номер начальной строки, начиная с которой считываются данные из файла.
// КонСтрока (по-умолчанию = 0) - Номер конечной строки, которой заканчиваются считываемые данные из файла.
// Если НачСтрока=0 и КонСтрока=0, то считывается вся таблица, находящаяся в файле.
// КолвоСтрокФайла - Количество строк в файле. Возвращается в вызвавшую процедуру.
//
// Возвращаемые значения:
// ТаблицаРезультат - Результат считывания данных из файла.
//
&НаСервере
Функция ЗагрузитьМетодом_EXCEL1C(Знач ФайлEXCEL, Знач ИмяЛиста = "", Знач СтрокаЗаголовка = 1, НачСтрока = 0, КонСтрока = 0, КолвоСтрокФайла = 0)
Перем ТабличныйДокумент, ОбластьФайла, КолВоКолонокФайла, ИмяКолонки, Область, ТекущаяОбласть, нСтрока, нКолонка, НоваяСтрокаТФ, ЗначениеЯчейки;
Перем ТаблицаРезультат;
ТабличныйДокумент = Новый ТабличныйДокумент;
Попытка
// Выполняется долго на больших файлах.
ТабличныйДокумент.Прочитать(ФайлEXCEL, СпособЧтенияЗначенийТабличногоДокумента.Значение); // СпособЧтенияЗначенийТабличногоДокумента - новый параметр платформы 8.3.6. Второе значение "Текст".
Исключение
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
Возврат Новый ТаблицаЗначений;
КонецПопытки;
ОбластьФайла = ТабличныйДокумент;
Если ВерсияПриложенияМеньше8310() Тогда
// Платформа 8.3.9 и более ранние (Файл EXCEL как 1 лист).
КолвоСтрокФайла = ОбластьФайла.ВысотаТаблицы;
КолВоКолонокФайла = ОбластьФайла.ШиринаТаблицы;
Иначе
// Платформа 8.3.10 (Облать = Лист данных).
ОбластьФайла = ТабличныйДокумент.ПолучитьОбласть(ИмяЛиста);
КолВоСтрокФайла = ОбластьФайла.ПолучитьРазмерОбластиДанныхПоВертикали();
КолВоКолонокФайла = ОбластьФайла.ПолучитьРазмерОбластиДанныхПоГоризонтали();
КонецЕсли;
// Проверка заполненности листа.
Если КолвоСтрокФайла = 0 Тогда
// Завершение работы.
// Закрытие Объектов.
ТабличныйДокумент = Неопределено;
Возврат Новый ТаблицаЗначений; // В случае ошибки возвращаем пустую таблицу значений.
КонецЕсли;
// Создание результирующей таблицы, в которую будут записываться считанные из файла данные.
ТаблицаРезультат = Новый ТаблицаЗначений;
// Формирование колонок результирующей таблицы.
// "НомерСтроки" - для наглядности и удобства.
// В зависимости от разрабатываемой обработки.
// "Сопоставлено" - может быть другим.
// Здесь же могут быть добавлены другие колонки, не формируемые из содержимого файла.
ТаблицаРезультат.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"), "№", 4);
ТаблицаРезультат.Колонки.Добавить("Сопоставлено", Новый ОписаниеТипов("Булево"), "Сопоставлено", 1);
Для ит = 1 ПО КолВоКолонокФайла Цикл
нКолонка = СтрЗаменить(ит, Символы.НПП, "");
ИмяКолонки = "N" + нКолонка;
ТаблицаРезультат.Колонки.Добавить(ИмяКолонки);
КонецЦикла;
// 1-я строка. Заголовки.
НоваяСтрокаТФ = ТаблицаРезультат.Добавить();
НоваяСтрокаТФ.НомерСтроки = 1;
Для ит=1 ПО КолВоКолонокФайла Цикл
нКолонка = СтрЗаменить(ит, Символы.НПП, "");
ИмяКолонки = "N" + нКолонка;
НоваяСтрокаТФ[ИмяКолонки] = ОбластьФайла.ПолучитьОбласть("R1" + "C"+нКолонка).ТекущаяОбласть.Текст;
// Используется при формировании таблицы на форме обработки.
ШиринаКолонки = ТаблицаРезультат.Колонки[ИмяКолонки].Ширина;
ДлинаСтроки = СтрДлина(СокрЛП(НоваяСтрокаТФ[ИмяКолонки]));
ТаблицаРезультат.Колонки[ИмяКолонки].Ширина = ?(ШиринаКолонки < ДлинаСтроки, ДлинаСтроки, ШиринаКолонки);
КонецЦикла;
НачСтрока = ?(НачСтрока = 0, 2, НачСтрока);
КонСтрока = ?(КонСтрока = 0, КолвоСтрокФайла, КонСтрока);
Для нСтрокаТФ = НачСтрока ПО КонСтрока Цикл
НоваяСтрокаТФ = ТаблицаРезультат.Добавить();
НоваяСтрокаТФ[0] = нСтрокаТФ;
нСтрока = СтрЗаменить(нСтрокаТФ, Символы.НПП, "");
Для нКолонкаТФ = 1 ПО КолВоКолонокФайла Цикл
нКолонка = СтрЗаменить(нКолонкаТФ, Символы.НПП, "");
Область = ОбластьФайла.ПолучитьОбласть("R"+нСтрока+"C"+нКолонка);
ТекущаяОбласть = Область.ТекущаяОбласть;
Попытка
ЗначениеЯчейки = ТекущаяОбласть.Значение; // Число, Дата.
Исключение
ЗначениеЯчейки = СокрЛП(ТекущаяОбласть.Текст); // Строка, Булево. (Булево как строка "ИСТИНА"/"ЛОЖЬ")
Если ЗначениеЗаполнено(ЗначениеЯчейки) Тогда
ЗначениеЯчейки = ПреобразоватьПростоеЗначениеИзСтрокиВТипизованноеЗначение1С(ЗначениеЯчейки);
Если ТипЗнч(ЗначениеЯчейки) = Тип("Строка") Тогда
ЗначениеЯчейки = СокрЛП(ЗначениеЯчейки);
КонецЕсли;
Иначе
ЗначениеЯчейки = Неопределено;
Если Область.Рисунки.Количество() > 0 Тогда // Изображение.
ЗначениеЯчейки = ПолучитьЗначениеЯчейкиОбластиТабличногоДокументаСКартинками(Область, нСтрока, нКолонка, "УИД");
КонецЕсли;
КонецЕсли;
КонецПопытки;
ИмяКолонки = "N" + нКолонка;
НоваяСтрокаТФ[ИмяКолонки] = ЗначениеЯчейки;
// Используется при формировании таблицы на форме обработки.
ШиринаКолонки = ТаблицаРезультат.Колонки[ИмяКолонки].Ширина;
ДлинаСтроки = СтрДлина(СокрЛП(НоваяСтрокаТФ[ИмяКолонки]));
ТаблицаРезультат.Колонки[ИмяКолонки].Ширина = ?(ШиринаКолонки < ДлинаСтроки, ДлинаСтроки, ШиринаКолонки);
КонецЦикла;
КонецЦикла;
// Юзабилити. Удалить пустые колонки.
УдалитьКолонкиСНулевойШириной(ТаблицаРезультат);
Возврат ТаблицаРезультат;
КонецФункции
&НаСервере
Процедура УдалитьКолонкиСНулевойШириной(ТаблицаРезультат)
Перем МассивПустыхКолонок;
// Найдем пустые колонки.
МассивПустыхКолонок = Новый Массив;
Для Каждого Колонка ИЗ ТаблицаРезультат.Колонки Цикл
Если Колонка.Ширина = 0 Тогда
МассивПустыхКолонок.Добавить(Колонка.Имя);
КонецЕсли;
КонецЦикла;
// Удалим пустые колонки.
Для Каждого ПустаяКолонка ИЗ МассивПустыхКолонок Цикл
ТаблицаРезультат.Колонки.Удалить(ПустаяКолонка);
КонецЦикла;
КонецПроцедуры
// ПРЕОБРАЗОВАНИЕ СТРОКИ К ТИПИЗОВАННОМУ ЗНАЧЕНИЮ 1С.
&НаСервере
Функция ПреобразоватьПростоеЗначениеИзСтрокиВТипизованноеЗначение1С(Знач ИсходноеЗначение)
Если НЕ ИсходноеЗначение = "" Тогда
Если ТолькоЦифрыИЗапятаяВСтроке(ИсходноеЗначение, Истина, Ложь) Тогда
Попытка
Возврат Число(ИсходноеЗначение);
Исключение
Возврат ИсходноеЗначение
КонецПопытки;
Иначе
Если ВРег(ИсходноеЗначение) = "ИСТИНА" ИЛИ ВРег(ИсходноеЗначение) = ("ИСТИНА"+Символы.ПС) ИЛИ ВРег(ИсходноеЗначение) = "TRUE" ИЛИ ВРег(ИсходноеЗначение) = ("TRUE"+Символы.ПС) Тогда
Возврат Истина;
ИначеЕсли ВРег(ИсходноеЗначение) = "ЛОЖЬ" ИЛИ ВРег(ИсходноеЗначение) = ("ЛОЖЬ"+Символы.ПС) ИЛИ ВРег(ИсходноеЗначение) = "FALSE" ИЛИ ВРег(ИсходноеЗначение) = ("FALSE"+Символы.ПС) Тогда
Возврат Ложь;
Иначе
Возврат ПреобразоватьИзСтрокиВДату(ИсходноеЗначение);
КонецЕсли;
КонецЕсли;
КонецЕсли;
Возврат ИсходноеЗначение
КонецФункции
// Проверяет, содержит ли строка только цифры и запятую.
//
// Параметры:
// СтрокаПроверки - Строка - Строка для проверки
// УчитыватьЛидирующиеНули - Булево - Флаг учета лидирующих нулей, если Истина, то ведущие нули пропускаются
// УчитыватьПробелы - Булево - Флаг учета пробелов, если Истина, то пробелы при проверке игнорируются
//
// Возвращаемое значение:
// Булево - Истина - строка содержит только цифры или пустая, Ложь - строка содержит иные символы.
//
&НаСервере
Функция ТолькоЦифрыИЗапятаяВСтроке(Знач СтрокаПроверки, Знач УчитыватьЛидирующиеНули = Истина, Знач УчитыватьПробелы = Истина)
Если ТипЗнч(СтрокаПроверки) <> Тип("Строка") Тогда
Возврат Ложь;
КонецЕсли;
Если НЕ УчитыватьПробелы Тогда
СтрокаПроверки = СтрЗаменить(СтрокаПроверки, " ", "");
КонецЕсли;
Если Сред(СтрокаПроверки, 1, 1) = "-" Тогда
СтрокаПроверки = Сред(СтрокаПроверки, 2, СтрДлина(СтрокаПроверки));
КонецЕсли;
Если ПустаяСтрока(СтрокаПроверки) Тогда
Возврат Истина;
КонецЕсли;
Если НЕ УчитыватьЛидирующиеНули Тогда
Позиция = 1;
// Взятие символа за границей строки возвращает пустую строку
Пока Сред(СтрокаПроверки, Позиция, 1) = "0" Цикл
Позиция = Позиция + 1;
КонецЦикла;
СтрокаПроверки = Сред(СтрокаПроверки, Позиция);
КонецЕсли;
// Если содержит только цифры, то в результате замен должна быть получена пустая строка
// Проверять при помощи ПустаяСтрока нельзя, так как в исходной строке могут быть пробельные символы
Возврат СтрДлина(
СтрЗаменить( СтрЗаменить( СтрЗаменить( СтрЗаменить( СтрЗаменить(
СтрЗаменить( СтрЗаменить( СтрЗаменить( СтрЗаменить( СтрЗаменить( СтрЗаменить(
СтрокаПроверки, "0", ""), "1", ""), "2", ""), "3", ""), "4", ""), "5", ""), "6", ""), "7", ""), "8", ""), "9", ""), ",", "")
) = 0;
КонецФункции
// Преобразование строки вида "01.01.13" или "01.01.2013" к значению типа "дата".
// Возможны друние форматы даты в файле EXCEL.
&НаСервере
Функция ПреобразоватьИзСтрокиВДату(Знач СтрокаДаты)
Перем ScrptCtrl, OutDate;
Попытка
ScrptCtrl = Новый COMОбъект("MSScriptControl.ScriptControl");
ScrptCtrl.Language="vbscript";
OutDate = ScrptCtrl.Eval("CDate(""" + СтрокаДаты + """)");
Возврат OutDate;
Исключение
//Сообщить(ОписаниеОшибки());
КонецПопытки;
Возврат СтрокаДаты;
КонецФункции
// ПОЛУЧЕНИЕ ЗНАЧЕНИЯ ДЛЯ РЕКВИЗИТА ТИПА "ФАЙЛ КАРТИНКИ".
&НаСервере
Функция ПолучитьЗначениеЯчейкиОбластиТабличногоДокументаСКартинками(Знач Область, Знач нСтрока, Знач нКолонка, Знач ПравилоИмяФайлаКартинки = "УИД")
Перем Рисунок, ит, ИмяФайлаРисунка;
Перем ЗначениеЯчейки;
ит = 0;
ЗначениеЯчейки = "";
Для Каждого Рисунок ИЗ Область.Рисунки Цикл
ит = ит + 1;
Если ПравилоИмяФайлаКартинки = "УИД" Тогда
ИмяФайлаРисунка = КаталогВременныхФайлов() + Новый УникальныйИдентификатор() + ".jpg";
Иначе
ИмяФайлаРисунка = КаталогВременныхФайлов() + "С" + нСтрока + "К" + нКолонка + ".jpg";
КонецЕсли;
Попытка
Рисунок.Картинка.Записать(ИмяФайлаРисунка);
ЗначениеЯчейки = ЗначениеЯчейки + ИмяФайлаРисунка + ?(ит < Область.Рисунки.Количество(), Символы.ПС, "");
Исключение
// Поле картинки недоступно для чтения.
КонецПопытки;
КонецЦикла;
Возврат ЗначениеЯчейки;
КонецФункции
// ОБЩЕГО НАЗНАЧЕНИЯ.
&НаКлиентеНаСервереБезКонтекста
Функция ВерсияБезНомераСборки(Знач Версия)
Перем Массив, Результат;
Массив = РазложитьСтрокуВМассивПодстрок(Версия, ".");
Если Массив.Количество() < 3 Тогда
Возврат Версия;
КонецЕсли;
Результат = "[Редакция].[Подредакция].[Релиз]";
Результат = СтрЗаменить(Результат, "[Редакция]", Массив[0]);
Результат = СтрЗаменить(Результат, "[Подредакция]", Массив[1]);
Результат = СтрЗаменить(Результат, "[Релиз]", Массив[2]);
Возврат Результат;
КонецФункции
&НаСервере
Функция ВерсияПриложенияМеньше8310()
Перем Версия;
Версия = СтрЗаменить(ВерсияПриложенияБезНомераСборки, ".", "");
Версия = Число(Версия);
Возврат Версия <= 839;
КонецФункции
&НаКлиентеНаСервереБезКонтекста
Функция РазложитьСтрокуВМассивПодстрок(Знач Стр, Разделитель = ",")
Перем МассивСтрок;
МассивСтрок = Новый Массив();
Если Разделитель = " " Тогда
Стр = СокрЛП(Стр);
Пока 1 = 1 Цикл
Поз = Найти(Стр, Разделитель);
Если Поз = 0 Тогда
МассивСтрок.Добавить(СокрЛП(Стр));
Возврат МассивСтрок;
КонецЕсли;
МассивСтрок.Добавить(СокрЛП(Лев(Стр, Поз - 1)));
Стр = СокрЛ(Сред(Стр, Поз));
КонецЦикла;
Иначе
ДлинаРазделителя = СтрДлина(Разделитель);
Пока 1 = 1 Цикл
Поз = Найти(Стр, Разделитель);
Если Поз = 0 Тогда
Если (СокрЛП(Стр) <> "") Тогда
МассивСтрок.Добавить(СокрЛП(Стр));
КонецЕсли;
Возврат МассивСтрок;
КонецЕсли;
МассивСтрок.Добавить(СокрЛП(Лев(Стр,Поз - 1)));
Стр = Сред(Стр, Поз + ДлинаРазделителя);
КонецЦикла;
КонецЕсли;
Возврат МассивСтрок;
КонецФункции
ВерсияПриложенияБезНомераСборки = "";
