Чтение файлов *.xlsx средствами 1С 8.2

Формат файлов *.xlsx представляет собой фактически ZIP-архив, внутри которого находятся xml-файлы, что позволяет считывать значения ячеек исключительно средствами 1С 8.2

Внутри архива *.xlsx практический интерес для получения значений ячеек представляют три вида файлов:

1. workbook.xml — содержит описание рабочей книги (количество листов)

2. sharedStrings.xml — содержит строковые значения ячеек всех листов

3. Файлы sheet1.xml…sheetN.xml — значения ячеек конкретного (по номеру, независимо от названия) листа.

Почти все значения внутри файлов sheet1.xml…sheetN.xml представляют собой числа, причем для строчных значений это индекс строки из файла sharedStrings.xml, а для даты — число дней с начала XX века.

Теги f содержат написание формулы, теги v — значение ячейки, независимо от того было ли оно «вбито» напрямую, или это результат формулы.

Данная обработка исключительно средствами 1С 8.2 производит распаковку архива *.xlsx и парсинг вышеуказанных файлов.

14.06.12

Заодно уж добавил и docxreader

43 Comments

anton.fly7 13.06.2012 at 11:32

а формулы считает?

Reply ↓
Abadonna 13.06.2012 at 11:38

(1) anton.fly7,

формулы без проблем, только я не посчитал нужным их сами выводить, всё равно ж, в основном, значения нужны.

<c r=»B4″>

<f>SUM(B1:B3)</f>

<v>10920</v>

</c>

Тег f — формула, тег v — значение. Лично меня сама формула не интересует

Reply ↓
andrewks 13.06.2012 at 14:59

ага, сам примерно такое делал, только для ods (OpenOffice) — там тоже зип-архив, а внутриях хмл, который также можно распарсить

Reply ↓
serega3333 13.06.2012 at 15:36

зачетно, для формул нужно очень

Reply ↓
CheBurator 13.06.2012 at 19:54

Автор, пиши ещё!

Reply ↓
CaSH_2004 13.06.2012 at 20:48

Любопытно а какая скорость то чтения, есть какое-то сравнение? Например чтением через тот-же КОМ-Эксель?

Reply ↓
andrewks 13.06.2012 at 22:26

(6) по сравнению с КОМ-Эксель, думаю, на порядок

Reply ↓
cool.vlad4 13.06.2012 at 23:12

А чем отличается от http://infostart.ru/public/19139/ ?

Reply ↓
Abadonna 14.06.2012 at 05:12

(8) кхм… наверное тем, что я не мониторю все обработки :)))

В смысле, мы с Василием всегда плюсы друг-другу ставили, но никогда даже и не смотрели, что там за разработки 😉

Эту либо вообще не видел, либо в упор не помню

Reply ↓
Abadonna 14.06.2012 at 05:23

(6) CaSH_2004,

Проверял на матрице 10 000 строк х 5 колонок, ровно 7 секунд на моем рабочем

(Intel® Core™2 Duo CPU E7500 2.93GHz, ОЗУ 2 ГБ)

Обработку, скорее всего, можно и оптимизировать, эта скорее как пример на данный момент.

Может, и XML будет быстрее парсить как текстовик…

Reply ↓
Abadonna 14.06.2012 at 18:37
(8) cool.vlad4,

>А чем отличается от http://infostart.ru/public/19139/ ?

Посмотрел ради интереса, да абсолютно всем отличается! Код даже близко рядом не стоял, а что касается структуры архива — дык это мелкосфот придумал, а не мы с Василием.

И уж никогда бы мне в голову не пришло
```
МассивБукв = Новый Массив;
МассивБукв.Добавить(«A»);
МассивБукв.Добавить(«B»);
МассивБукв.Добавить(«C»);
МассивБукв.Добавить(«D»);
……….
```
Испокон веков я делаю такие массивы так:

Стр=»ABCDEFGHIJKLMNOPQRSTUVWXYZ»;

Reply ↓

Поручик 15.06.2012 at 14:38

(11) А я бывало и так

МассивБукв = Новый Массив;
Для Код = КодСимвола(«A») По КодСимвола(«Z») Цикл МассивБукв.Добавить(Символ(Код)); КонецЦикла;

Reply ↓

Jonny_wk 17.06.2012 at 18:25

Интересно

Reply ↓
Den_D 21.06.2012 at 14:31

Скоро мы все перейдем на XML общение ))

И даже между собой исключительно по родительским узлам будем общаться)))

Reply ↓
Bassgood 25.06.2012 at 22:50

(0) Заметил, что не всегда экселевский файл читается корректно при помощи этого метода (колонки читаются не в том порядке, в котором они расположены в экселе, и некоторые значения ячеек читаются неправильно), в чем причина я понять так и не смог (заметил лишь только то, что если его содержимое скопировать в новый файл — то все читается нормально), скорее всего имеется какая то заморочка с преобразованием эксель файла в XML формат, потому что при помощи COM файл читается нормально (если интересно, то приложил этот эксель файл к сообщению). Вот такая вот загагулина.

Reply ↓
Abadonna 26.06.2012 at 05:10

(15) Zigfridish,

я это тоже заметил, но пока особо не разбирался. Обработка абсолютно прозрачная, с комментами, может кто и найдет, где собака порылась;) Будет время — сам еще внимательно попроверяю

Reply ↓
SergDi 27.06.2012 at 11:55

(10) 7 секунд это круто

Reply ↓
fillipok 28.06.2012 at 10:18

Спасибо за обработку))в добавку:

1. файлы OpenOffice таблицы формата .ods открываются таким же способом только естественно файл с данными xml другой))(вроде и .odt так же, но не проверял).

Reply ↓
Konder.Djironimo 30.06.2012 at 09:10

Спасибо! Все было в свое время заточено под *.xls но потом с переходом на *.xlsx пришлось всех оставлять на 2003, а тут всех обрадуем 😉 еще раз спасибо!

Reply ↓
Dethmond 03.07.2012 at 08:54

Хорошо что стал поддерживать *.xlsx. Спасибо

Reply ↓
Abadonna 03.07.2012 at 18:29

(20)Честное слово, это не я! :))))) Это мелкософт поддерживает

Reply ↓
Dethmond 03.07.2012 at 21:03

(21) ладно, Microsoft`у тоже спасибо))

Reply ↓
khaoos 04.07.2012 at 04:50

Классно придумано. Да и на другие статьи автора обращу внимание, интересные вещи пишет. Молодец!

Reply ↓
1cmax 05.07.2012 at 00:21

Кстати, если в файле нет ни одной ячейки со строками

файл sharedStrings.xml не создается

и обработка валится с ошибкой.

стоит добавить проверку

Процедура ПарсингSharedStrings()

XML=Новый ЧтениеXML;

//Добавлено: maxval 03.07.2012

Имя = Объект.ВременнаяПапка+»sharedStrings.xml»;

Ф = новый Файл(Имя);

Если Не ф.Существует() Тогда

Возврат;

КонецЕсли;

XML.ОткрытьФайл(Имя);

/// maxval 03.07.2012

Reply ↓
1cmax 05.07.2012 at 00:21

реализовал все те же принципы под Управляемые формы, в.т.ч. Веб-клиент:

http://infostart.ru/public/142187/

Reply ↓
Abadonna 05.07.2012 at 00:31

(24) 1cmax,

Ага, я как-то поленился проверить…

Reply ↓
1cmax 05.07.2012 at 00:34

(15) Zigfridish, Аналогичную багу нашел.. эх.. сначала думал что для любого сервака универсальное решение. а то серваки на линуксе. там никаких ком объектов…

Reply ↓
cerg110 13.07.2012 at 07:23

Как раз что скинули файл для загрузки в xsls. Как будет возможность сразу скачаю. Спасибо за полезную обработку.

Reply ↓
yuraos 28.03.2013 at 09:06

(21)

Честное слово, это не я! :))))) Это мелкософт поддерживает

говорят он уже пожалел об этом

—

в следующем офисе вроде грозятся зделать бинарный закрытый формат

🙂

Reply ↓
Spartan 28.03.2013 at 18:37
(0) небольшой косячок… в блоке
```
 //— находим строчные значения из файла  sharedStrings.xml
МассивСтрок=Новый СписокЗначений;
ПарсингSharedStrings();
//— находим индексы строк и прочие значения из файла xlworksheetssheetN.xml
```
МассивСтрок д.б. именно массивом, а не списком значений, иначе при получении значения из ячейки со строкой имеем тип «ЭлементСпискаЗначений», а не «Строка».
```
 Если ЭтоСтрока Тогда
Попытка
Значение=МассивСтрок.Получить(Число(XML.Значение));
Исключение
КонецПопытки;
```
Визуально в табличном поле этого не видно, но при работе с получившейся таблицей могут быть неожиданности.

Reply ↓
Spartan 28.03.2013 at 18:41

(15) А где в Вашем файле получаются неправильные значения при чтении обработкой? А то файл немаленький — визуально очень сложно заметить. Мне нужно понять, насколько это критично для моей задачи.

Reply ↓
Abadonna 28.03.2013 at 18:43

(31)Блин, ну код же открытый. Мне она без надобности, к старому я не возвращаюсь;)

Reply ↓
Spartan 28.03.2013 at 19:35

(32) Да не, вопросов нет… За код спасибо! Это я написал для тех, кто будет использовать. 😉

Reply ↓
Abadonna 28.03.2013 at 19:52

(33) Spartan, я просто не заметил сгоряча, что (31) не мне :)))

Reply ↓
Spartan 29.03.2013 at 09:41

(15),(31) Все, увидел… посмотрел сегодня на свежую голову.

P.S. Нашел в чем косяк — сейчас разбираюсь как победить. Получается неверный массив строк из файла sharedStrings.xml, если в нем присутствуют пустые строки.

Reply ↓

Spartan 29.03.2013 at 10:45

(0),(15),(27) Как-то так, видимо:

Функция ПарсингSharedStrings()
XML=Новый ЧтениеXML;
//XML.ОткрытьФайл(ВременнаяПапка+»sharedStrings.xml»);
//Добавлено: maxval 03.07.2012
Имя = ВременнаяПапка+»sharedStrings.xml»;
Ф = новый Файл(Имя);
Если Не Ф.Существует() Тогда
Возврат Ложь;
КонецЕсли;
XML.ОткрытьФайл(Имя);
/// maxval 03.07.2012
Пока XML.Прочитать() Цикл
//Если XML.Имя=»#text» Тогда
// МассивСтрок.Добавить(XML.Значение);
//КонецЕсли;
// Spartan — 29.03.2013 — начало
Если XML.Имя = «t» И XML.ТипУзла = ТипУзлаXML.НачалоЭлемента Тогда
Если XML.Прочитать() И XML.Имя = «#text» Тогда
МассивСтрок.Добавить(XML.Значение);
Иначе
МассивСтрок.Добавить(«»);
КонецЕсли;
КонецЕсли;
// Spartan — 29.03.2013 — конец
КонецЦикла;
XML.Закрыть();

Возврат Истина;
КонецФункции

Показать

Reply ↓

Lapitskiy 05.03.2015 at 14:10

(36) Spartan, не недостаточно, когда несколько листов, неверно определяет.

Reply ↓
freep777 06.08.2015 at 14:44

Обработка замечательная!!! Спасибо огромнющее за нее!

Только вот не пойму, на одном файле нормально работает, а на другом выдает ошибку

{Форма.Форма.Форма(80)}: Индекс находится за границами массива

ТЗ[НомерСтроки-1][НомерКолонки-1]=Значение;

Помогите, пожалуйста, разобраться.

(к сообщению прикреплены файлы — на «Книга2» — работает, а на «Книга1» — выдает ошибку)… Вроде ничем таким особым файлы не различаются??? Не могу понять…

Reply ↓
ksnik 13.04.2019 at 20:50

(38) ответ найден, смотрите приложенные мной скриншоты — временные файлы,

вот этот лист загружается успешно

а на следующем скриншоте нумерация строк начинается не с единицы, что ведет к рассинхронизации номеров строк файла и таблизы значений 1с,

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

Reply ↓

ksnik 13.04.2019 at 21:45

(38) вот такая доработка (исправление) парсит файл правильно

Функция ПарсингSheet(Лист=1)
ТЗ=Новый ТаблицаЗначений;
XML=Новый ЧтениеXML;
ИмяФайла=»sheet»+Строка(Лист)+».xml»;
XML.ОткрытьФайл(ВременнаяПапка+ИмяФайла);

Пока XML.Прочитать() Цикл
Если (XML.Имя=»row») И (XML.КоличествоАтрибутов()>0) Тогда
ТекущаяСтрока=XML.ЗначениеАтрибута(0);
//Космачев+
//Стр=ТЗ.Добавить();
РазницаСтрок = ТекущаяСтрока — ТЗ.Количество();
Для СчРазницаСтрок = 1 По РазницаСтрок Цикл
Стр=ТЗ.Добавить();
КонецЦикла;
//Космачев-
НомерСтроки=Число(ТекущаяСтрока);

Показать

Reply ↓

ksnik 13.04.2019 at 22:08

Спасибо автору за легкий код! Очень хорошая обработка! Я еще добавил в нее номера строк и колонок вот так, процедуру ТЗИПриПолученииДанных надо подключить через обработчик события табличного поля:

// Добавим в начало модуля переменную необходимую для автоматической нумерации строк
Перем ТЗАнализа;
…
//считывание ячеек заданного листа
Процедура ОсновныеДействияФормыПарсинг(Кнопка)
Если Не ФайлСчитан Тогда
Предупреждение(«Не открыт файл xlsx!»,5);
Возврат;
КонецЕсли;
ТЗИ.Очистить();
//— находим строчные значения из файла  sharedStrings.xml
МассивСтрок=Новый СписокЗначений;
ПарсингSharedStrings();
//— находим индексы строк и прочие значения из файла xlworksheetssheetN.xml
Если НомерЛиста>КоличествоЛистов Тогда
Сообщить(«Указан номер листа, превышающий общее количество листов. Установлен Лист 1″,СтатусСообщения.Информация);
НомерЛиста=1;
КонецЕсли;
//Космачев 13.04.2019 +добавил номера строк и колонок и уменьшил ширину пустых колонок+
ТЗАнализа = ПарсингSheet(НомерЛиста);
ЭлементыФормы.ТЗИ.Значение=ТЗАнализа;
ЭлементыФормы.ТЗИ.СоздатьКолонки();
СписокНомеров = Новый СписокЗначений;
Для Каждого СтрТЗ Из ТЗАнализа Цикл
Для Каждого КолТЗ Из ТЗАнализа.Колонки Цикл
Если СокрЛП(ТЗАнализа[ТЗАнализа.Индекс(СтрТЗ)][ТЗАнализа.Колонки.Индекс(КолТЗ)])>»» Тогда
Если СписокНомеров.НайтиПоЗначению(ТЗАнализа.Колонки.Индекс(КолТЗ))=Неопределено Тогда
СписокНомеров.Добавить(ТЗАнализа.Колонки.Индекс(КолТЗ));
КонецЕсли;
КонецЕсли;
КонецЦикла;
КонецЦикла;
н = 0;
Для Каждого КолонкаТЗИ Из ЭлементыФормы.ТЗИ.Колонки Цикл
КолонкаТЗИ.ТекстШапки = СокрЛП(н);
Если СписокНомеров.НайтиПоЗначению(н)=Неопределено Тогда
КолонкаТЗИ.Ширина = 1;
КонецЕсли;
н = н + 1;
КонецЦикла;
ЭлементыФормы.ТЗИ.Колонки.Вставить(0,»НПП»);
КонецПроцедуры

Процедура ТЗИПриПолученииДанных(Элемент, ОформленияСтрок)
Для Каждого ОфСтроки Из ОформленияСтрок Цикл
//ОфСтроки.Ячейки.Колонка1.УстановитьТекст(СокрЛП(ТЗАнализа.Индекс(Элемент.ТекущаяСтрока)));//+1
ОфСтроки.Ячейки.Колонка1.УстановитьТекст(СокрЛП(ТЗАнализа.Индекс(ОфСтроки.ДанныеСтроки)));//+1
КонецЦикла;
КонецПроцедуры
//Космачев-
Процедура ПриОткрытии()

Показать

и уменьшил ширину колонок в которых ни в одной строке нет данных

Reply ↓

ksnik 14.04.2019 at 00:54
(16) колонки перепутаны потому что колонки создаются не единовременно в порядке определеном в файе но иначе, по мере обнаружения заполненных значений ячеек при сканировании листа процедурой ПарсингSheet.
```
  ИначеЕсли XML.Имя=»#text» Тогда
Разница=НомерКолонки-ТЗ.Колонки.Количество();
Для А=1 По Разница Цикл
ТЗ.Колонки.Добавить();
КонецЦикла;
Значение=XML.Значение;
```
В данном случае помогло бы предварительое скаирование для определения кол-ва строк и колонок в файле (или м.б. считывание этих значений из полей файла), затем создание колонок и второй проход по файлу парсером уже с заполнением ТЗ для сохранения правилього порядка. Если много пустых их можно удалить позже.

Простейшим способом упорядочения колонок будут заголовки колонок на исходном импортируемом листе.

Reply ↓

ksnik 14.04.2019 at 10:19

(42) Вот программная реализация удаления путаницы и упорядочения колонок

//Космачев 13.04.2019 +Фиксируем колонки чтоб не переставлялись+

Функция ПарсингSheet1проход(Лист=1)

XML=Новый ЧтениеXML;
ИмяФайла=»sheet»+Строка(Лист)+».xml»;
XML.ОткрытьФайл(ВременнаяПапка+ИмяФайла);
КолвоКолонок = 0;
Пока XML.Прочитать() Цикл
Если (XML.Имя=»row») И (XML.КоличествоАтрибутов()>0) Тогда
ТекущаяСтрока=XML.ЗначениеАтрибута(0);
НомерСтроки=Число(ТекущаяСтрока);
ИначеЕсли XML.Имя=»c» Тогда
ИмяЯчейки=XML.ЗначениеАтрибута(0);
НомерКолонки=СтрЗаменить(ИмяЯчейки,ТекущаяСтрока,»»);
НомерКолонки=КолонкаЧислом(НомерКолонки);
ИначеЕсли XML.Имя=»#text» Тогда
Если НомерКолонки>КолвоКолонок Тогда
КолвоКолонок = НомерКолонки;
КонецЕсли;
КонецЕсли;
КонецЦикла;
Возврат КолвоКолонок;
КонецФункции


Функция ПарсингSheet(Лист=1)
//Космачев 13.04.2019 +Фиксируем колонки чтоб не переставлялись+
КолвоКолонок = ПарсингSheet1проход(НомерЛиста);
Сообщить(КолвоКолонок);
ТЗ=Новый ТаблицаЗначений;
Для Сч = 1 По КолвоКолонок Цикл
ТЗ.Колонки.Добавить();
КонецЦикла;

//Космачев 13.04.2019 —Фиксируем колонки чтоб не переставлялись—

XML=Новый ЧтениеXML;

…

//Космачев 13.04.2019 +убираем динамическое создание колонок — фиксируем колонки чтоб не переставлялись+
//Разница=НомерКолонки-ТЗ.Колонки.Количество();
//Для А=1 По Разница Цикл
// ТЗ.Колонки.Добавить();
//КонецЦикла;

Показать

Reply ↓

Чтение файлов *.xlsx средствами 1С 8.2

Related Posts

43 Comments

Leave a Comment Отменить ответ