20.08.2014. Новая редакция с возможностью загрузки изображений из файла.
В данной статье приведен функционал, с помощью которого в обработке
"Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С": //infostart.ru/public/120961/
производится считывание данных из файлов табличного типа *xls, *.xlsx.
Методы загрузки из внешнего источника:
- Метод "MS ADO" (Чтение файлов xls, xlsx средствами Microsoft ADO): //infostart.ru/public/163640/
- Метод "MS EXCEL" (Чтение файлов xls, xlsx с картинками средствами Microsoft Office): //infostart.ru/public/163641/
- Метод "LO CALC" (Чтение файлов xls, xlsx, 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 - множество, но
"
— Вам билетёр нужен?
— Был нужен, да уже взяли.
— Может и я на что сгожусь?
— Может и сгодишься, если скалиться не будешь ...
"
"НЕУЛОВИМЫЕ МСТИТЕЛИ" (1966).
&НаКлиенте
Процедура ПриОткрытии(Отказ)
ФайлEXCEL= "D:\Товар.xls";
ИмяЛиста = Новый Структура ("ИмяЛиста, НомерЛиста","СФКартинок (2)", 16);
СтрокаЗаголовка = 1;
НачСтрока = 3;
КонСтрока = 4;
КолвоСтрокExcel = 0;
ЗагрузитьМетодом_MSEXCEL_НаСервере(ФайлEXCEL, ИмяЛиста, СтрокаЗаголовка, НачСтрока, КонСтрока, КолвоСтрокExcel);
КонецПроцедуры
&НаСервере
Процедура ЗагрузитьМетодом_MSEXCEL_НаСервере(ФайлEXCEL, ИмяЛиста, СтрокаЗаголовка, НачСтрока, КонСтрока, КолвоСтрокExcel)
ТаблицаРезультат = ЗагрузитьМетодом_MSEXCEL(ФайлEXCEL, ИмяЛиста, СтрокаЗаголовка, НачСтрока, КонСтрока, КолвоСтрокExcel);
КонецПроцедуры
// Метод "Microsoft EXCEL.Application".
//
// Параметры:
// ФайлEXCEL - Полное имя файла (путь к файлу с именем файла и расширением)
// ИмяНомерЛиста - Имя/Номер выбранного листа файла EXCEL.
// СтрокаЗаголовка (по умолчанию = 1) - Номер строки EXCEL, в которой расположены заголовки колонок.
// Не используется.
// В обработке 1-я строка анализируется для сопоставления колонок EXCEL с реквизитами 1С (справочники, докуметны, регистры).
// НачСтрока (по-умолчанию = 0) - Номер начальной строки, начиная с которой считываются данные из EXCEL.
// КонСтрока (по-умолчанию = 0) - Номер конечной строки, которой заканчиваются считываемые данные из EXCEL.
// Если НачСтрока=0 и КонСтрока=0, то считывается вся таблица, находящаяся на листе EXCEL.
// КолвоСтрокEXCEL - Количество строк на листе "ИмяЛиста" EXCEL. Возвращается в вызываемую процедуру.
//
// Возвращаемые значения:
// ТаблицаРезультат - Результат считывания с листа "ИмяЛиста" EXCEL.
//
&НаСервере
Функция ЗагрузитьМетодом_MSEXCEL(Знач ФайлEXCEL, Знач ИмяНомерЛиста, Знач СтрокаЗаголовка = 1, НачСтрока = 2, КонСтрока = 0, КолвоСтрокEXCEL)
Перем ИмяФайлаEXCEL, ИмяЛиста, НомерЛиста;
Перем EXCELApplication, Book, Sheet;
Перем ВсегоЛистов, НачальнаяКолонка, НачальнаяСтрока, КонечнаяКолонка, КонечнаяСтрока;
Перем ДиапазонДанных, СтрокДиапазона, ДиапазонЗаголовка, МассивЗаголовка, МассивЗначений;
Перем ТекИмя, ПозДоллар, Версия;
Перем ТаблицаРезультат, МассивИменКолонок, ИмяКолонки, Колонка, Заголовок, НоваяСтрока, нСтроки;
ИмяФайлаEXCEL = ПолучитьИмяФайлаБезРасширения(ФайлEXCEL);
// Нумерация MS EXCEL начинается с 1.
ИмяЛиста = ИмяНомерЛиста.ИмяЛиста;
НомерЛиста = ИмяНомерЛиста.НомерЛиста;
Попытка
// Инициализация основного объекта EXCEL.Application: Открытие соединения.
EXCELApplication = Новый COMОбъект("EXCEL.Application");
EXCELApplication.Visible = Ложь;
EXCELApplication.DisplayAlerts = Ложь;
Исключение
Попытка
EXCELApplication.Application.Quit();
Исключение
КонецПопытки;
EXCELApplication = Неопределено;
Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
Возврат Новый ТаблицаЗначений; // В случае ошибки возвращаем пустую таблицу значений.
КонецПопытки;
// Определение версии EXCEL.
Версия = Лев(EXCELApplication.Version,Найти(EXCELApplication.Version,".")-1);
// Инициализация дочернего объекта типа Книга EXCEL.
Book = EXCELApplication.WorkBooks.Open(ФайлEXCEL);
ВсегоЛистов = Book.Sheets.Count;
// Инициализация дочернего объекта Книги типа Лист EXCEL.
Если Лев(ИмяЛиста, 1) = "'" И Прав(ИмяЛиста, 1) = "'" Тогда
ИмяЛиста = УдалитьКавычки(ИмяЛиста, "'");
КонецЕсли;
Если Прав(ИмяЛиста, 1) = "$" Тогда
УдалитьПоследнийСимволВСтроке(ИмяЛиста, 1);
КонецЕсли;
Sheet = Book.WorkSheets(ИмяЛиста);
// Определение диапазона данных таблицы EXCEL.
НачальнаяКолонка = 1;
НачальнаяСтрока = 1;
Если Версия = "8" Тогда
КонечнаяКолонка = Sheet.Cells.CurrentRegion.Columns.Count;
КонечнаяСтрока = Sheet.Cells.CurrentRegion.Rows.Count;
Иначе
КонечнаяКолонка = Sheet.Cells.SpecialCells(11).Column;
КонечнаяСтрока = Sheet.Cells.SpecialCells(11).Row;
Конецесли;
// Параметр возвращаемый в вызывающую процедуру.
КолвоСтрокEXCEL = КонечнаяСтрока;
// Проверка заполненности листа.
Если КолвоСтрокEXCEL <= 1 И КонечнаяКолонка <= 1 Тогда
КолвоСтрокEXCEL = 0;
Сообщить(НСтр("ru = '" + ИмяЛиста + ": Лист, содержащий 1 строку считаем пустым.'"), СтатусСообщения.Внимание);
// Завершение работы.
// Закрытие Объектов.
Попытка
Book.Close();
Исключение
КонецПопытки;
Попытка
EXCELApplication.Application.Quit();
Исключение
КонецПопытки;
EXCELApplication = Неопределено;
Возврат Новый ТаблицаЗначений; // В случае ошибки возвращаем пустую таблицу значений.
КонецЕсли;
НачальнаяСтрока = ?(НачСтрока = 0 , НачальнаяСтрока , НачСтрока);
КонечнаяСтрока = ?(КонСтрока = 0 , КонечнаяСтрока , КонСтрока);
КонечнаяСтрока = ?(КонечнаяСтрока > КолвоСтрокEXCEL, КолвоСтрокEXCEL, КонечнаяСтрока);
НачальнаяСтрока = ?(НачальнаяСтрока > КолвоСтрокEXCEL, КолвоСтрокEXCEL, НачальнаяСтрока);
// Диапазон: Считываемые данные.
ДиапазонДанных = Sheet.Range(Sheet.Cells(НачальнаяСтрока, НачальнаяКолонка), Sheet.Cells(КонечнаяСтрока, КонечнаяКолонка));
СтрокДиапазона = ДиапазонДанных.Rows.Count;
// Создание результирующей таблицы, в которую будут записываться считанные из EXCEL данные.
ТаблицаРезультат = Новый ТаблицаЗначений;
// Формирование колонок результирующей таблицы.
// "НомерСтроки" - для наглядности и удобства.
// В зависимости от разрабатываемой обработки.
// "Сопоставлено" - может быть другим.
// Здесь же могут быть добавлены другие колонки, не формируемые из содержимого файла EXCEL.
ТаблицаРезультат.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"), "№", 4);
ТаблицаРезультат.Колонки.Добавить("Сопоставлено", Новый ОписаниеТипов("Булево"), "Сопоставлено", 1);
МассивИменКолонок = Новый Массив;
Для ит = 1 ПО КонечнаяКолонка Цикл
ТекИмя = Строка(ит);
МассивИменКолонок.Добавить(ТекИмя);
Если ДиапазонДанных.Columns(ит).Hidden Тогда // Скрытые колонки EXCEL пропустить.
Продолжить;
КонецЕсли;
ИмяКолонки = "N" + ТекИмя;
ТаблицаРезультат.Колонки.Добавить(ИмяКолонки);
КонецЦикла;
// ТаблицаРезультат: 1-я Строка-Заголовок.
// Если в Вашей обработке в результирующей таблице в качестве 1-ой строки не нужна Строка-Заголовок, то
// следует закомментировать следующий цикл:
ДиапазонЗаголовка = Sheet.Range(Sheet.Cells(1, НачальнаяКолонка), Sheet.Cells(1, КонечнаяКолонка));
МассивЗаголовка = ДиапазонЗаголовка.Value.Выгрузить();
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.НомерСтроки = 1;
ит = -1;
Для Каждого МассивЗначений ИЗ МассивЗаголовка Цикл
Для Каждого ЭлементМассива ИЗ МассивЗначений Цикл
ит = ит + 1;
ИмяКолонки = "N" + МассивИменКолонок[ит];
Колонка = ТаблицаРезультат.Колонки.Найти(ИмяКолонки);
Если Колонка = Неопределено Тогда // Имена скрытых колонок отсутствуют в массиве.
Продолжить;
КонецЕсли;
НоваяСтрока[ИмяКолонки] = СокрЛП(ЭлементМассива);
КонецЦикла;
КонецЦикла;
// ТаблицаРезультат: формирование строк по указанному диапазону: НачСтрока - КонСтрока.
ДиапазонДанных = ДиапазонДанных.Value;
нСтроки = НачальнаяСтрока-1;
Для НомерСтроки = 1 ПО СтрокДиапазона Цикл
нСтроки = нСтроки + 1;
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.НомерСтроки = нСтроки;
Для НомерКолонки = 1 ПО КонечнаяКолонка Цикл
ИмяКолонки = "N" + МассивИменКолонок[НомерКолонки-1];
Колонка = ТаблицаРезультат.Колонки.Найти(ИмяКолонки);
Если Колонка = Неопределено Тогда // Имена скрытых колонок отсутствуют в массиве.
Продолжить;
КонецЕсли;
ЗначениеЯчейки = ДиапазонДанных.GetValue(НомерКолонки, НомерСтроки); // Считывание данных в соответствии с их типом.
Если ЗначениеЯчейки = Неопределено Тогда
ЗначениеЯчейки = ПрочитатьКартинку_MSEXCEL(EXCELApplication, Sheet, НомерКолонки, нСтроки, ИмяФайлаEXCEL, НомерЛиста, "УИД");
КонецЕсли;
Если ЗначениеЗаполнено(ЗначениеЯчейки) Тогда
// Ширина колонки используется при формировании таблицы на форме обработки.
ШиринаКолонки = ТаблицаРезультат.Колонки[ИмяКолонки].Ширина;
ДлинаСтроки = СтрДлина(СокрЛП(ЗначениеЯчейки));
ТаблицаРезультат.Колонки[ИмяКолонки].Ширина = ?(ШиринаКолонки < ДлинаСтроки, ДлинаСтроки, ШиринаКолонки);
НоваяСтрока[ИмяКолонки] = ЗначениеЯчейки;
КонецЕсли;
КонецЦикла;
КонецЦикла;
УдалитьКолонкиСНулевойШириной(ТаблицаРезультат);
// Завершение работы.
// Закрытие Объектов.
Попытка
Book.Close();
Исключение
КонецПопытки;
Попытка
EXCELApplication.Application.Quit();
Исключение
КонецПопытки;
EXCELApplication = Неопределено;
Возврат ТаблицаРезультат;
КонецФункции
// Функция осуществляет экспорт изображения во внешний графический файл.
//
// Параметры:
// EXCELApplication - Объект типа "EXCEL.Application".
// Sheet - Обект типа "Лист книги EXCEL".
// НомерКолонки - Номер колонки листа.
// НомерСтроки - Номер строки листа.
// ИмяФайлаEXCEL - Короткое имя файла без расширения, из которого производится импорт.
// НомерЛиста - Номер листа книги EXCEL.
// ПравилоИмяФайлаКартинки - правило формирования имени выходного графического файла.
// - "УИД" (по умолчанию).
// - Иначе на основании имени исходного файла EXCEL, Номера листа, Номера строки, Номера колонки.
//
// Возвращаемые значения:
// ПолноеИмяФК - Полное имя графического файла.
//
&НаСервере
Функция ПрочитатьКартинку_MSEXCEL(Знач EXCELApplication, Знач Sheet, Знач НомерКолонки, Знач НомерСтроки, Знач ИмяФайлаEXCEL, Знач НомерЛиста, Знач ПравилоИмяФайлаКартинки = "УИД")
Перем GraphicObject, Chart;
Перем ПолноеИмяФК, ФайлКартинки;
Если Sheet.Shapes.Count() = 0 Тогда
Возврат Неопределено;
КонецЕсли;
Для Каждого GraphicObject ИЗ Sheet.Shapes Цикл
Если GraphicObject.Type = 13 И GraphicObject.TopLeftCell.Column = НомерКолонки И GraphicObject.TopLeftCell.Row = НомерСтроки Тогда
// GraphicObject.Name.
Если ПравилоИмяФайлаКартинки = "УИД" Тогда
ПолноеИмяФК = КаталогВременныхФайлов() + Новый УникальныйИдентификатор() + ".jpg";
Иначе
ПолноеИмяФК = КаталогВременныхФайлов() + ИмяФайлаEXCEL + "Л" + НомерЛиста + "С" + НомерСтроки + "К" + НомерКолонки + ".jpg";
КонецЕсли;
Попытка
GraphicObject.ScaleHeight(1,1); // Истинный размер по высоте.
GraphicObject.ScaleWidth(1,1); // Истинный размер по ширине.
GraphicObject.Copy();
Chart = EXCELApplication.ActiveSheet.ChartObjects().Add(0, 0, GraphicObject.Width, GraphicObject.Height).Chart();
Chart.Paste();
Chart.Export(ПолноеИмяФК);
Chart = Неопределено;
ФайлКартинки = Новый Файл(ПолноеИмяФК);
Если ФайлКартинки.Существует() Тогда
Возврат ПолноеИмяФК;
Иначе
Сообщить("Не удалось экспортировать картинку из строки " + НомерСтроки + " колонки " + НомерКолонки + " в " + ПолноеИмяФК);
Возврат Неопределено;
КонецЕсли;
Исключение
Сообщить(ОписаниеОшибки());
Сообщить("Не удалось экспортировать картинку из строки " + НомерСтроки + " колонки " + НомерКолонки + " в " + ПолноеИмяФК);
Возврат Неопределено;
КонецПопытки;
КонецЕсли;
КонецЦикла;
Возврат Неопределено;
КонецФункции
&НаСервере
Процедура УдалитьКолонкиСНулевойШириной(ТаблицаРезультат)
Перем МассивПустыхКолонок;
// Найдем пустые колонки.
МассивПустыхКолонок = Новый Массив;
Для Каждого Колонка ИЗ ТаблицаРезультат.Колонки Цикл
Если Колонка.Ширина = 0 Тогда
МассивПустыхКолонок.Добавить(Колонка.Имя);
КонецЕсли;
КонецЦикла;
// Удалим пустые колонки.
Для Каждого ПустаяКолонка ИЗ МассивПустыхКолонок Цикл
ТаблицаРезультат.Колонки.Удалить(ПустаяКолонка);
КонецЦикла;
КонецПроцедуры
// Выделяет из полного имени файла имя файла без расширения.
//
// Параметры
// ПолноеИмяФайла – Строка, содержащая имя файла, неважно с именем каталога или без.
//
// Возвращаемое значение:
// ИмяФайлаБезРасширения – короткое имя файла.
//
&НаСервере
Функция ПолучитьИмяФайлаБезРасширения(ПолноеИмяФайла)
Перем ФайлТМП, РасширениеФайла, ИмяФайлаБезРасширения;
ФайлТМП = РазложитьСтрокуВМассивПодстрок(ПолноеИмяФайла, "\");
ФайлТМП = ФайлТМП[ФайлТМП.Количество()-1];
РасширениеФайла = "." + ПолучитьРасширениеИмениФайла(ФайлТМП);
ИмяФайлаБезРасширения = СтрЗаменить(ФайлТМП, РасширениеФайла, "");
Возврат ИмяФайлаБезРасширения;
КонецФункции
// Выделяет из имени файла его расширение (набор символов после последней точки).
//
// Параметры
// ИмяФайла – Строка, содержащая имя файла, неважно с именем каталога или без.
//
// Возвращаемое значение:
// Строка – расширение файла.
//
&НаСервере
Функция ПолучитьРасширениеИмениФайла(Знач ИмяФайла)
Перем Расширение;
Расширение = ПолучитьСтрокуОтделеннойСимволом(ИмяФайла, ".");
Возврат Расширение;
КонецФункции
// Удаляет кавычки с начала и конца строки, если они есть.
//
// Параметры:
// Строка - входная строка;
//
// Возвращаемое значение:
// Строка - строка без двойных кавычек.
//
&НаСервере
Функция УдалитьКавычки(Знач Строка, Кавычка = """")
Пока Лев(Строка, 1) = Кавычка Цикл
Строка = Сред(Строка, 2);
КонецЦикла;
Пока Прав(Строка, 1) = Кавычка Цикл
Строка = Лев(Строка, СтрДлина(Строка) - 1);
КонецЦикла;
Возврат Строка;
КонецФункции
// Удаляет из строки указанное количество символов справа.
//
// Параметры:
// Текст - Строка - строка, в которой необходимо удалить последние символы;
// ЧислоСимволов - Число - количество удаляемых символов.
//
&НаСервере
Процедура УдалитьПоследнийСимволВСтроке(Текст, ЧислоСимволов)
Текст = Лев(Текст, СтрДлина(Текст) - ЧислоСимволов);
КонецПроцедуры
// Функция "расщепляет" строку на подстроки, используя заданный
// разделитель. Разделитель может иметь любую длину.
// Если в качестве разделителя задан пробел, рядом стоящие пробелы
// считаются одним разделителем, а ведущие и хвостовые пробелы параметра Стр
// игнорируются.
// Например,
// РазложитьСтрокуВМассивПодстрок(",один,,,два", ",") возвратит массив значений из пяти элементов,
// три из которых - пустые строки, а
// РазложитьСтрокуВМассивПодстрок(" один два", " ") возвратит массив значений из двух элементов
//
// Параметры:
// Стр - строка, которую необходимо разложить на подстроки.
// Параметр передается по значению.
// Разделитель - строка-разделитель, по умолчанию - запятая.
//
// Возвращаемое значение:
// массив значений, элементы которого - подстроки
//
&НаСервере
Функция РазложитьСтрокуВМассивПодстрок(Знач Стр, Разделитель = ",")
МассивСтрок = Новый Массив();
Если Разделитель = " " Тогда
Стр = СокрЛП(Стр);
Пока 1 = 1 Цикл
Поз = Найти(Стр, Разделитель);
Если Поз = 0 Тогда
МассивСтрок.Добавить(СокрЛП(Стр));
Возврат МассивСтрок;
КонецЕсли;
МассивСтрок.Добавить(СокрЛП(Лев(Стр, Поз - 1)));
Стр = СокрЛ(Сред(Стр, Поз));
КонецЦикла;
Иначе
ДлинаРазделителя = СтрДлина(Разделитель);
Пока 1 = 1 Цикл
Поз = Найти(Стр, Разделитель);
Если Поз = 0 Тогда
Если (СокрЛП(Стр) <> "") Тогда
МассивСтрок.Добавить(СокрЛП(Стр));
КонецЕсли;
Возврат МассивСтрок;
КонецЕсли;
МассивСтрок.Добавить(СокрЛП(Лев(Стр,Поз - 1)));
Стр = Сред(Стр, Поз + ДлинаРазделителя);
КонецЦикла;
КонецЕсли;
КонецФункции
// Функция возвращает часть строки после последнего встреченного символа в строке
&НаСервере
Функция ПолучитьСтрокуОтделеннойСимволом(Знач ИсходнаяСтрока, Знач СимволПоиска)
ПозицияСимвола = СтрДлина(ИсходнаяСтрока);
Пока ПозицияСимвола >= 1 Цикл
Если Сред(ИсходнаяСтрока, ПозицияСимвола, 1) = СимволПоиска Тогда
Возврат Сред(ИсходнаяСтрока, ПозицияСимвола + 1);
КонецЕсли;
ПозицияСимвола = ПозицияСимвола - 1;
КонецЦикла;
Возврат "";
КонецФункции
Преимущества:
1.Метод "Microsoft EXCEL" наиболее прост в применении (кодировании).
2. По скорости считывания находится на 2-ом месте после "Microsoft ADODB".
Особенности и Ограничения:
1. Для функционирования метода "Microsoft EXCEL" необходим установленный Microsoft Office. Коммерческий продукт.
2. Метод может не работать в клиент-серверном варианте, если у SQL-пользователя недостаточно прав в Windows.
3. 1-я строка файла EXCEL - строка, содержащая заголовки колонок.
С уважением к сообществу МА!
