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

07.11.11

Интеграция - Файловый обмен (TXT, XML, DBF), FTP

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

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

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

Получить 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.

См. также

SALE! 15%

[ED3] Обмен для ERP 2.5, КА 2.5, УТ 11.5 БП 3.0, Розница, УНФ и других с EnterpriseData (универсальный формат обмена), правила обмена

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Россия Платные (руб)

Правила в универсальном формате обмена для ERP 2.5, КА 2.5, УТ 11.5, БП 3.0, Розница, УНФ, для последних версий конфигураций. Ссылки на другие конфигурации в описании публикации. Правила совместимы со всеми другими версиями конфигураций новыми и старыми, поддерживающими обмен в формате EnterpriseData. Не требуется синхронного обновления правил после обновления другой конфигурации, участвующей в обмене. Типовой обмен через планы обмена кнопкой Синхронизация вручную или автоматически по расписанию, или вручную обработкой.

25080 22572 руб.

12.06.2017    134933    722    291    

388

SALE! 20%

Перенос данных из ERP 2 / КА 2 / УТ 11 в БП 3.0. Переносятся документы, начальные остатки и справочники

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Платные (руб)

Перенос данных из ERP в БП 3 | из КА 2 в БП 3 | из УТ 11 в БП 3 | из ЕРП в БП 3 | В продаже с 2019г. | Воспользовались более 176 предприятий! | Сэкономьте время - используйте готовое решение для перехода! | Перенос разработан в формате КД 2 (правила конвертации данных) | Переносятся все возможные виды документов, начальных остатков и нормативно-справочная информация| Можно опционально выгружать каждую пару "номенклатура+характеристика" как отдельную номенклатуру | Есть выгрузка настроек счетов учета и зарплатных данных из ERP / КА 2 | Можно проверить на вашем сервере перед покупкой, обращайтесь!

34650 27720 руб.

15.04.2019    68417    178    138    

111

SALE! 20%

Перенос данных из ERP 2 / КА 2 в ЗУП 3. Переносятся остатки, документы и справочники

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x Россия Бухгалтерский учет Управленческий учет Платные (руб)

Перенос данных из ERP в ЗУП 3 | из КА 2 в ЗУП | Воспользовались более 79 предприятий! | Предлагаем приобрести готовые правила конвертации данных (КД 2) для переноса остатков, документов с движениями и справочной информации 3 | В продаже с 2020г. | Оперативно обновляем правила до актуальных релизов 1С | Есть перенос начальной задолженности по зарплате и начальной штатной расстановки на выбранную дату | Обороты за прошлые годы (данные для расчета среднего) переносятся свернуто в документ "Перенос данных" | Есть фильтр по организациям | Документы за текущий период переносятся сразу с движениями, поэтому не потребуется делать перерасчеты | Перенос можно проверить перед покупкой, обращайтесь!

43450 34760 руб.

03.12.2020    34169    80    58    

78

SALE! 10%

Перенос данных из УТ 10.3 в УТ 11.5. Переносятся документы (обороты за период), справочная информация и остатки

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 Оперативный учет 1С:Управление торговлей 10 1С:Управление торговлей 11 Россия Управленческий учет Платные (руб)

Перенос данных из 1С:Управление торговлей 10.3 в 1С:Управление торговлей 11.5 с помощью правил обмена. Переносятся остатки, документы (обороты за период), справочная информация. Правила проверены на конфигурациях УТ 10.3 (10.3.87.x) и УТ 11.5 (11.5.16.x).

28000 25200 руб.

23.07.2020    46290    196    64    

158

Перенос данных из Парус 10 в ЗГУ ред.3

Внешние источники данных Кадровый учет Файловый обмен (TXT, XML, DBF), FTP Обмен между базами 1C Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бухгалтерский учет Бюджетный учет Платные (руб)

Обработка позволяет перенести кадровую информацию и данные по заработной плате, фактических удержаниях, НДФЛ, вычетах, страховых взносах из базы Парус 10 учреждений в конфигурацию 1С:Зарплата и кадры государственного учреждения ред. 3 (ЗГУ) и начать с ней работать с любого месяца года.

60000 руб.

05.10.2022    9208    9    8    

10

SALE! 10%

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 9504 руб.

29.10.2014    210169    620    524    

439

SALE! 10%

Перенос данных из УПП 1.3 в БП 3.0. Переносятся документы (обороты за период), справочная информация и остатки

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Управление производственным предприятием 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Управленческий учет Платные (руб)

Перенос данных из 1С:Управление производственным предприятием 1.3 в 1С:Бухгалтерия предприятия 3.0 с помощью правил обмена. Переносятся остатки, документы (обороты за период), справочная информация. Правила проверены на конфигурациях УПП 1.3 (1.3.223.x) и БП 3.0 (3.0.149.x). Правила подходят для версии ПРОФ и КОРП.

28000 25200 руб.

15.12.2021    20247    132    38    

90

SALE! 10%

Перенос данных из БП 3.0 в УНФ 3.0 / УНФ 1.6. Переносятся остатки, документы и справочная информация

Обмен между базами 1C Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Управление нашей фирмой 1.6 1С:Бухгалтерия 3.0 1С:Управление нашей фирмой 3.0 Россия Платные (руб)

В продаже с 2018г. | Воспользовались более 41 предприятия! | Правила конвертации (КД 2) для переноса данных из БП 3 в УНФ | Переносятся все виды документов, начальные остатки и вся возможная справочная информация | Есть фильтр по организациям | Оперативно обновляем на новые релизы | Оказываем техподдержку | В комплект файлов входит инструкция, авторская версия обработки "Универсальный обмен...", актуальные правила переноса данных и архив старых версий переноса | Учет в БП 3 должен быть корректным, некорректные данные не переносятся | Можно бесплатно проверить на вашем сервере до покупки!

50722 45650 руб.

10.07.2018    67445    41    122    

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

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

Объясняю как модератор:
Нужно писать, чтобы человек мог понять из описания, что и как делается в публикации без скачивания файла, у людей за скачивание баллы снимаются, и не хочется, чтобы они зря качали.
6. medanskiy 30.10.11 19:47 Сейчас в теме
Занимательная технология. Возьму на заметку. Автору плюс.
7. DrAku1a 1679 31.10.11 03:32 Сейчас в теме
Плюс за технологию.

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

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

З.Ы. Кстати, в противоречие себе же вспомнил пару примеров: 1С не работает с книгой, а выгружает только один лист (может понадобиться несколько), и второй - 1С не сохраняет в *.xls поименованные области... Впрочем, еще много чего найти можно при желании - вплоть до макросов.
Но все-же с простейшим сохранением в XLS справится и сама платформа.
12. cool.vlad4 2 31.10.11 09:30 Сейчас в теме
(11) TXLSReadWriteII - кажется платный, а чтение и запись xlsx и docx средствами 1С уже делали. Смотри в профиле у Душелова. XML Spreadsheet это, конечно, хорошо, что автор обратил внимание, да вот только если потребуют результат именно в виде xls - все равно придется использовать Excel.Application.
16. slimper 201 31.10.11 11:20 Сейчас в теме
(11) XML Spreadsheet не поддерживает группировки, диаграммы, модули VBA, а в остальном это полноценная книга Excel.
17. cool.vlad4 2 31.10.11 11:42 Сейчас в теме
(16) Такие небольшие вопросики - 1. Если сохранять с расширением xls, чем чревато? 2. Как лучше делать шаблоны? Я делаю комментарии <--! Price --> и затем просто заменяю на нужный мне блок. Может есть способ лучше?
18. slimper 201 31.10.11 13:24 Сейчас в теме
(17) 1.У меня проблем не было. Может быть имел дело с не очень сложными отчетами. 2.Не могу дать конкретного совета. В "нормальных" языках я пользуюсь соответствующими пространствами имен, не прибегая к костылям.
21. cool.vlad4 2 01.11.11 10:35 Сейчас в теме
(18) В 1С в любом случае придется делать костыли. Ради такой мелочи ВК писать нет смысла. И еще вопрос - а не знаешь как выявить группировки в xml Spreadsheet (это которые outlinelevel)? макросом это очень долго...
22. slimper 201 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
24. cool.vlad4 2 01.11.11 14:03 Сейчас в теме
(22) спс, блин, так я и думал...эти некрософты не могли нормально сделать работу с группировками...
(23) Не лучше, просто другая. Это как раз те самые публикации Душелова , о которых я говорил выше - они вобще-то про форматы docx и xlsx, а здесь про способ формирования Excel файла - на основе xml. Удобно делать шаблоны.
10. slimper 201 31.10.11 07:28 Сейчас в теме
(10) Да, именно это я имел ввиду.
14. artbear 1448 31.10.11 10:21 Сейчас в теме
(0) Исправляй описание, иначе сниму публикацию до исправления
15. slimper 201 31.10.11 11:16 Сейчас в теме
(14) 1.Публикация проходит предмодерацию, на этом этапе никаких замечений не было.
2.Мне действительно непонятно, что нужно исправлять.
19. artbear 1448 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).
23. Трактор 1246 01.11.11 13:56 Сейчас в теме
25. alon 171 02.11.11 16:01 Сейчас в теме
Идея интересна своей простотой и неприхотливостью к использованию ВК.
Но я бы воспользовался технологией XSLT для наполнения шаблона. Все же 1С - не язык работы с текстом.
Представим, что надо заполнить заголовок, потом одну таблицу на первой странице, потом еще парочку на других. Сколько всяких "_num", "_kod" и "_nam" нам нужно будет заполнить? И как разрастется процедура анализа строк в 1С?
А вы помните наизусть, чем в xml заменяются спецсимволы типа "<", ">", "/"..?
27. slimper 201 02.11.11 16:47 Сейчас в теме
(25)(26) Согласен, можно и так. Как говорил Великий Кормчий - "Пусть расцветают сто цветов".
26. alon 171 02.11.11 16:01 Сейчас в теме
...
Другой вариант - создать простейший xml-файл с данными и преобразовать его в нужный вид с помощью xsl-шаблона. Функции работы с xml доступны в стандартной v7plus, и не придется думать о спецсимволах. А для работы с xsl есть множество инструментов визуальной разработки. Мышкой махать - не программы писать :)
28. cool.vlad4 2 02.11.11 16:54 Сейчас в теме
29. artbear 1448 02.11.11 17:07 Сейчас в теме
Полуофф. Народ, где можно почитать что-то внятное и не слишком мудреное для работы с xsl-шаблонами/схемами и формированием xml на их базе?
Чувствую, что-то интересное пропустил :)
30. alon 171 02.11.11 17:52 Сейчас в теме
(29)http://ru.wikipedia.org/wiki/Xslt :^)
Я начинал по книге Валикова Алексея "Технология XSLT" - доступно описано. В продаже нет наверное, в инете найдешь.
31. cool.vlad4 2 02.11.11 17:57 Сейчас в теме
32. vikorn 03.11.11 13:13 Сейчас в теме
33. svs74 9 05.11.11 08:22 Сейчас в теме
В принципе неплохой подход. спс автору за труд. Возможно пригодится. Но все же лучше работать с
Excel.Application на мой взгляд.
35. ZLENKO 398 10.09.12 18:04 Сейчас в теме
(33) Оно то может и лучше через Excel.Application, да вот только начиная с Excel 2007 почему то работа с ячейками на VBA настолько медленная, что выгрузить что то более менее объемное (типа прайса на несколько тыс. строк) просто нереально из-за быстрой деградации производительности. Перечитал интернет - многие жалуются на VBA в 2007-2010 и никакого решения этой проблемы не видно.
Пришлось сделать экспорт в два этапа - сначала готовим оформление ячеек в Excel при помощи VBA, а потом вставляем туда данные при помощи программно создаваемого содержимого буфера обмена - программно эмулируем действия пользователя КАНТРРЭЛ-ЦЭ и КАНТРЭЛ-ВЭ. В ближайшее время оформлю это в виде обработки - выложу тут.
34. AVVG 09.11.11 09:26 Сейчас в теме
Да очень классная вещь
Оставьте свое сообщение