1С. Чтение XLS файлов

Приветствую, мои маленькие любители длинных запросов.

Сегодня мы с вами рассмотрим несколько способов реализации часто встречающейся задачи, которая неизменно вызывает определенные трудности у каждого начинающего программиста, а именно разбор XLS файлов.

«Какая уж тут сложность?!! Берешь Com объект Excel.Application, наискосок пробегаешь по  методичке MSDN Microsoft и готово!» — воскликнет один из наших более опытных коллег, уже успевший сваять две нетленки и раскурочить до неузнаваемости типовую УПП. А сложность есть, и она в неопределенности доступных ресурсов. Расшифруем эту красивую фразу чуть подробнее.

1. Предположим, что у нас все хорошо (нет) и сервером является что-то вроде Windows Server 2013 R2 (далее просто Win). И на нем даже установлен Microsoft Office. Или, будь не к ночи помянута, у нас файловая база.

В этом случае все очень просто:

Создаем EA = ComObject(«Excel.Application”);

Открываем книгу Книга = EA.Worlbooks.Open(ПолныйПутьКФайлу);

Получаем нужный лист Лист = Книга.WorkSheets(ИмяНужногоЛиста);

Теперь можно работать, как с обычным табличным документам при помощи метода Лист.Range(х,y);

Не забываем, что область заполненных данных мы можем определить через методы Лист.Cells.SpecialCells(11).Column  и Лист.Cells.SpecialCells(11).Row

 

Не забываем убирать за собой, а именно делать корректные обработки ошибки, и в случае возникновения оных закрывать подключение к COM объекту, иначе рискуем получить  заблокированный документ и сотни сеансов Excel на сервере:

Книга.Close();

EA.Application.Quit;

 

2. Априори утверждать, что на сервере будет установлена какая-либо версия Excel, нельзя. Особенно во времена, когда по организациям ходят мужчины в строгих костюмах и просят предъявить лицензионное соглашение на все, включая пасьянс на компьютере секретаря.

Тем не менее сервера на Win довольно-таки распространенная вещь. Допустим у нас именно такой, а вот на лицензионный Office денег не хватило.
В этом случае допустим следующий метод – чтение файла через драйвер Ado.
Стоит отдельно заметить, что это один из наиболее быстрых способов чтения файлов Excel.

Последовательность та же, что и в первом варианте, создаем сом объект и используем его методы.

АДО = Новый COMОбъект(«ADODB.Connection»);

АДО.ConnectionString =  «Provider=Microsoft.Jet.OLEDB.4.0;Data Source= «  + ПолныйПутьКФайлу + «;Extended Properties=»»Excel 8.0;HDR=YES;IMEX=1;»»»;

 АДО.Open();

А вот далее идет … запрос. Да, мы будем читать данные файла запросом, как данные любой другой базы данных.

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

ТекстЗапроса = «SELECT * FROM [« + ИмяНужногоЛиста + «$]»

РС.Open(ТекстЗапроса, АДО);

Чтение Recordset’а выполняется в цикле. Условием завершения цикла является значение, возвращаемое методом РС.EOF();

Перебор набора – методом РС. MoveNext();

Количество колонок и строк, хранится в свойствах РС.RecordCount и РС.Fields.Count. Не забываем, что это не методы, и следовательно скобки не нужны.

Позиционирование на конкретной ячейке документа — РС.Fields.Item(НомерКолонки).

 

Опять же, как и в варианте 1 не забываем убирать за собой, везде, где вероятность возникновения ошибки не абсолютно нулевая, т.е. весь алгоритм целиком, а так же в конце алгоритма.

РС.Close();

 АДО.Close();

 РС = Неопределено;

 АДО = Неопределено;

DeleteWorld();

3. Есть еще один вариант, сервер на WIn и OpenOffice. Такое сочетание встречается чуть менее, чем повсеместно в гос.конторах и прочих бюджетных учреждениях. Его, я конечно, же не буду рассматривать(с).
   На самом деле, этот метод абсолютно уступает чтению через ADO, как в быстродействии, так и простое построения алгоритмов.
4. В случае, если сервер развернут на Linux единственный вариант – это через использование стандартного 1Совского ЧтенияXML(ну или ПостроительDOM, для гурманов). Так же данные метод применим только для «соверменного» формата XLSX. Устаревший XLS таким образом прочитать не получится. Но не унывайте, есть решение и на этот случай (см.пункт 5)Дело в том, что файл формат XLSX это фактически архив, в котором хранятся XML файлы и служебные таблицы, так же в формате XML.

Строковые, численные и прочие примитивные значения ячеек в файле xl\sharedStrings.xml

Формулы в файле xl\ calcChain.xml

Разметки листов в xl\worksheets\sheetsX.xml

Оформления листов в файле xl\styles.xml

Указанные файлы можно прочитать при помощи объекта ЧтениеXML или более продвинутого ПостроительDOM.
В любом случае структура этих файлов достаточно сложна и достойна отдельной статьи. Так же с ней можно ознакомиться самостоятельно, просто открыв файл XLSX любым архиватором.

5. На сладкое оставим способ, доступный если у вас платформа 8.6.3 или старше.

Разработчики 1С позаботились о тех, несчастных, которым недоступна технология COM, ADO или приходится работать с устаревшим XLS.

Для этого для метода Прочитать() ТабличногоДокумента добавили параметр

СпособЧтенияЗначений типа СпособЧтенияЗначенийТабличногоДокумента.

Таким образом можно «скормить» файл табличному документу, а затем работать с ним.

Небольшой хинт, чтобы не парсить области табличного документу вручную, его, в свою очередь, можно подсунуть в качестве источника данные ПостроителюЗапросов.

ПостроительЗапросов = Новый ПостроительЗапроса;

ПостроительЗапросов.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());

ПостроительЗапросов.Выполнить();

ТаблицаЗначений = ПостроительЗапросов.Результат.Выгрузить();

И получить ТаблицуЗначений здорового человека, с которой справится и вчерашний студент.

 

В данной статье мы рассмотрели целых 5 способов чтения XLS|X файлов. Надеюсь, в следующий раз, когда вы столкнетесь с подобной задачей, она поможет вам разобрать многометровые листинги кода разборов XLS из результатов гугла, толстого троллинга от завсегдатаев мисты или тотального игнора на инфостарте, в поисках ответа, который до вас задавался тысячи раз.