gifts2017

Используем XML Spreadsheet для выгрузки в Excel

Опубликовал slimper (slimper) в раздел Обмен - Загрузка и выгрузка в Excel

Как выгрузить данные в хорошо оформленный  Excel отчет, без вызова Еxcel.Application? Попробуем решить эту задачу с помощью XML Spreadsheet, который появился еще в Office XP, но на мой взгляд находится в тени других способов взаимодействия 1С и Excel.

Получить XML Spreadsheet можно сохранив рабочую книгу Excel как "Таблица XML". В результате мы получаем XML документ, c которым можно работать или при помощи Excel,  или как с обычным текстовым документом. Использование второго способа и позволит нам решить поставленную задачу.

Последовательность действий такова: создаем форматированный  шаблон в Excel - заголовки, границы, именованные диапазоны, параметры страницы, сквозные строки (столбцы), и.т.д. Кроме того шаблон может содержать несколько рабочих листов. Сохраняем шаблон как "Таблица XML". В 1С пишем обрабатывающую процедуру, которая считает шаблон, как обычный текстовый файл, вставит в него соответствующие данные и сохранит результат в  рабочую книгу (*.xls).

В данном примере, для простоты мы будем выгружать справочник банков.  Создадим рабочую книгу, отформатируем ячейки, сохраним её как "Таблица XML".

Шаблон отчета

 

 

 

 

 

 

 

 

 

Затем откроем получившийся файл в текстовом редакторе (я рекомендую Notepad++ - мощный удобный и бесплатный). Вставим метки (//1 и //2), ограничивающие строку данных, для того чтобы можно было выделить этот блок при разборе текста (если открыть этот файл в Excel и затем его сохранить, эти символы исчезнут).

Шаблон строки, куда будут выводиться данные, будет выглядеть так:

Шаблон строки данных

 

 

 

 

Далее (в среде 1С) считываем XML-файл в строковую переменную, вырезаем шаблон строки и в цикле создаем набор  строк с нашими данными, заменяя метки _num, _cod и _nam на №п/п, код и наименование банка.  Затем склеиваем полученный набор строк с остальными фрагментами файла и меняем количество строк:

Количество строк

В нашем случае, заменяем ss:ExpandedRowCount="5" на ss:ExpandedRowCount="5+n-1", где n - количество строк в выборке. И наконец сохраняем  полученную строку в файл, c расширением xls.

Описанный метод применим не только к Excel, но и к другим документам  MS Office.  Относительная простота форматов xml-документов Office  позволяет создавать такие документы "на лету" прямо в коде 1С.

Шаблон создан на Excel 2003,  пример выгрузки  на платформе  8.2.14.537.

Скачать файлы

Наименование Файл Версия Размер
Пример обработки + xml шаблон 68
.rar 7,47Kb
31.10.11
68
.rar 7,47Kb Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Ю М (mtv:)) 30.10.11 08:30
(0) Довольно интересная технология.
Возьму на заметку. В некоторых случаях может очень даже пригодиться.
2. Алексей Роза (DoctorRoza) 30.10.11 11:44
За работу с xml + однозначно ..
3. Zoomby Zoomby (Zoomby) 30.10.11 11:50
Как выгрузить данные в хорошо оформленный  Excel отчет, без вызова Еxcel.Application?

Однозначно плюс. Нужно будет попробовать. Спасибо.
4. Артур Аюханов (artbear) 30.10.11 14:26
(0) Хочется поставить плюс, но после прочтения описания все-таки не получен ответ на главный вопрос "Как выгрузить данные в хорошо оформленный Excel отчет, без вызова Еxcel.Application" ?
получили шаблон Excel и что?
нужно скачать файла из статьи и запустить его в 1С или еще что-то?
Автор, доработай описание
ЗЫ качать не хочу, принципиально хочу понять из описания.
5. slimper (slimper) 30.10.11 15:38
(4) В приложении находится обработка, с открытым кодом и файл шаблона. Если есть приложение, это означает, что весь функционал в нем. Или об этом надо писать большими буквами?
6. Дмитрий Медянский (medanskiy) 30.10.11 19:47
Занимательная технология. Возьму на заметку. Автору плюс.
7. Андрей Акулов (DrAku1a) 31.10.11 03:32
Плюс за технологию.

Как выгрузить данные в хорошо оформленный Excel отчет

Сформировать табличный документ и сохранить его в Excel средствами 1С - не?
8. slimper (slimper) 31.10.11 05:57
(7) Да в приведенном примере - делаем руками шаблон, а затем подсовываем его 1С. Но никто не запрещает формировать эти шаблоны программно в среде 1С, ведь это обычные XML файлы - описание их форматов (примеры), причем не только для Excel можно найти в MSDN. Цель публикации, в том, что документы МS Office (2003 и ниже) могут быть представлены в виде XML файлов, а уже как это использовать/не использовать каждый решает сам. Кстати, начиная с Office 2007, Excel, Word и другие уже в нативном виде являются контейнерами XML файлов.
9. see1c ru (see1c.ru) 31.10.11 07:17
(8) в дополнение " Office 2007, Excel, Word и другие уже в нативном виде являются контейнерами XML файлов." , берем файл , например "Книга1.xlsx", переименовываем в "Книга1.zip". Распаковываем и смотрим XML :)
10. slimper (slimper) 31.10.11 07:28
(10) Да, именно это я имел ввиду.
11. Андрей Акулов (DrAku1a) 31.10.11 07:53
(8)(9)(10) Это все понятно. Молодцы!
Но я имел в виду, что у 1С имеется свой мкеханизм записи в Excel-файл. Но вот чте-е-ение...... С этим проблемка... И тут наверное проще булет ВК обойтись (по-моему ВК в инете имеются, если что - есть Delphi и компонент TXLSReadWriteII - вооружаемся и пишем).
Естественно, как вариант - берем *.zlsx - открываем его внутренним zip-упаковщиком, разбираем файлы внутри... Но для этого много и доло MSDN изучать нужно... может тут найдутся добровольцы?

З.Ы. Кстати, в противоречие себе же вспомнил пару примеров: 1С не работает с книгой, а выгружает только один лист (может понадобиться несколько), и второй - 1С не сохраняет в *.xls поименованные области... Впрочем, еще много чего найти можно при желании - вплоть до макросов.
Но все-же с простейшим сохранением в XLS справится и сама платформа.
12. Ийон Тихий (cool.vlad4) 31.10.11 09:30
(11) TXLSReadWriteII - кажется платный, а чтение и запись xlsx и docx средствами 1С уже делали. Смотри в профиле у Душелова. XML Spreadsheet это, конечно, хорошо, что автор обратил внимание, да вот только если потребуют результат именно в виде xls - все равно придется использовать Excel.Application.
13. Артур Аюханов (artbear) 31.10.11 10:19
slimper пишет:(4) В приложении находится обработка, с открытым кодом и файл шаблона. Если есть приложение, это означает, что весь функционал в нем. Или об этом надо писать большими буквами?

Объясняю как модератор:
Нужно писать, чтобы человек мог понять из описания, что и как делается в публикации без скачивания файла, у людей за скачивание баллы снимаются, и не хочется, чтобы они зря качали.
14. Артур Аюханов (artbear) 31.10.11 10:21
(0) Исправляй описание, иначе сниму публикацию до исправления
15. slimper (slimper) 31.10.11 11:16
(14) 1.Публикация проходит предмодерацию, на этом этапе никаких замечений не было.
2.Мне действительно непонятно, что нужно исправлять.
16. slimper (slimper) 31.10.11 11:20
(11) XML Spreadsheet не поддерживает группировки, диаграммы, модули VBA, а в остальном это полноценная книга Excel.
17. Ийон Тихий (cool.vlad4) 31.10.11 11:42
(16) Такие небольшие вопросики - 1. Если сохранять с расширением xls, чем чревато? 2. Как лучше делать шаблоны? Я делаю комментарии <--! Price --> и затем просто заменяю на нужный мне блок. Может есть способ лучше?
18. slimper (slimper) 31.10.11 13:24
(17) 1.У меня проблем не было. Может быть имел дело с не очень сложными отчетами. 2.Не могу дать конкретного совета. В "нормальных" языках я пользуюсь соответствующими пространствами имен, не прибегая к костылям.
19. Артур Аюханов (artbear) 31.10.11 15:11
(15) А (7) и (8) ни о чем не говорит?
ЗЫ на будущее - премодерация также не гарантия качества, одни модераторы также могут ошибаться, но другие их могут поправить.
20. Александр Капустин (kapustinag) 31.10.11 19:17
Поставил плюс.
И лишний раз убедился, что эволюция идет по спирали.
Именно такие решения (подготовка файла-шаблона средствами родного редактора, а затем его программный разбор и наполнение данными) больше десяти лет назад пришлось делать дважды:
- работа с шаблонами .txt и .html из программы на Си и Pro*C (Oracle-исты поймут)
- работа с шаблонами .doc, .rtf и .xls из программы на PL/SQL (тоже программный язык от Oracle).
21. Ийон Тихий (cool.vlad4) 01.11.11 10:35
(18) В 1С в любом случае придется делать костыли. Ради такой мелочи ВК писать нет смысла. И еще вопрос - а не знаешь как выявить группировки в xml Spreadsheet (это которые outlinelevel)? макросом это очень долго...
22. slimper (slimper) 01.11.11 12:11
(21) Если я правильно понял о группировках, то:
Items not persisted in the XML Spreadsheet format include:
OLE objects
Drawing shapes
Charts
Microsoft® Visual Basic® for Applications (VBA) code
Groups and outlines
Custom envelope information

Может будет полезно:
Office 2003: XML Reference Schemas
23. Трактор Трактор (Трактор) 01.11.11 13:56
24. Ийон Тихий (cool.vlad4) 01.11.11 14:03
(22) спс, блин, так я и думал...эти некрософты не могли нормально сделать работу с группировками...
(23) Не лучше, просто другая. Это как раз те самые публикации Душелова , о которых я говорил выше - они вобще-то про форматы docx и xlsx, а здесь про способ формирования Excel файла - на основе xml. Удобно делать шаблоны.
25. Андрей Гореликов (alon) 02.11.11 16:01
Идея интересна своей простотой и неприхотливостью к использованию ВК.
Но я бы воспользовался технологией XSLT для наполнения шаблона. Все же 1С - не язык работы с текстом.
Представим, что надо заполнить заголовок, потом одну таблицу на первой странице, потом еще парочку на других. Сколько всяких "_num", "_kod" и "_nam" нам нужно будет заполнить? И как разрастется процедура анализа строк в 1С?
А вы помните наизусть, чем в xml заменяются спецсимволы типа "<", ">", "/"..?
26. Андрей Гореликов (alon) 02.11.11 16:01
...
Другой вариант - создать простейший xml-файл с данными и преобразовать его в нужный вид с помощью xsl-шаблона. Функции работы с xml доступны в стандартной v7plus, и не придется думать о спецсимволах. А для работы с xsl есть множество инструментов визуальной разработки. Мышкой махать - не программы писать :)
27. slimper (slimper) 02.11.11 16:47
(25)(26) Согласен, можно и так. Как говорил Великий Кормчий - "Пусть расцветают сто цветов".
28. Ийон Тихий (cool.vlad4) 02.11.11 16:54
29. Артур Аюханов (artbear) 02.11.11 17:07
Полуофф. Народ, где можно почитать что-то внятное и не слишком мудреное для работы с xsl-шаблонами/схемами и формированием xml на их базе?
Чувствую, что-то интересное пропустил :)
30. Андрей Гореликов (alon) 02.11.11 17:52
(29)http://ru.wikipedia.org/wiki/Xslt :^)
Я начинал по книге Валикова Алексея "Технология XSLT" - доступно описано. В продаже нет наверное, в инете найдешь.
31. Ийон Тихий (cool.vlad4) 02.11.11 17:57
32. Игорь (vikorn) 03.11.11 13:13
33. Sergey Yudin (svs74) 05.11.11 08:22
В принципе неплохой подход. спс автору за труд. Возможно пригодится. Но все же лучше работать с
Excel.Application на мой взгляд.
34. Антон Васильев (AVVG) 09.11.11 09:26
35. 1с-программы.com (ZLENKO) 10.09.12 18:04
(33) Оно то может и лучше через Excel.Application, да вот только начиная с Excel 2007 почему то работа с ячейками на VBA настолько медленная, что выгрузить что то более менее объемное (типа прайса на несколько тыс. строк) просто нереально из-за быстрой деградации производительности. Перечитал интернет - многие жалуются на VBA в 2007-2010 и никакого решения этой проблемы не видно.
Пришлось сделать экспорт в два этапа - сначала готовим оформление ячеек в Excel при помощи VBA, а потом вставляем туда данные при помощи программно создаваемого содержимого буфера обмена - программно эмулируем действия пользователя КАНТРРЭЛ-ЦЭ и КАНТРЭЛ-ВЭ. В ближайшее время оформлю это в виде обработки - выложу тут.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа