Как из storehouse выгрузить в excel

Обновлено: 02.07.2024

Есть в IT-отрасли задачи, которые на фоне успехов в big data, machine learning, blockchain и прочих модных течений выглядят совершенно непривлекательно, но на протяжении десятков лет не перестают быть актуальными для целой армии разработчиков. Речь пойдёт о старой как мир задаче формирования и выгрузки Excel-документов, с которой сталкивался каждый, кто когда-либо писал приложения для бизнеса.

Какие возможности построения файлов Excel существуют в принципе?

VBA-макросы. В наше время по соображениям безопасности идея использовать макросы чаще всего не подходит.
Автоматизация Excel внешней программой через API. Требует наличия Excel на одной машине с программой, генерирующей Excel-отчёты. Во времена, когда клиенты были толстыми и писались в виде десктопных приложений Windows, такой способ годился (хотя не отличался скоростью и надёжностью), в нынешних реалиях это с трудом достижимый случай.
Генерация XML-Excel-файла напрямую. Как известно, Excel поддерживает XML-формат сохранения документа, который потенциально можно сгенерировать/модифицировать с помощью любого средства работы с XML. Этот файл можно сохранить с расширением .xls, и хотя он, строго говоря, при этом не является xls-файлом, Excel его хорошо открывает. Такой подход довольно популярен, но к недостаткам следует отнести то, что всякое решение, основанное на прямом редактировании XML-Excel-формата, является одноразовым «хаком», лишенным общности.
Наконец, возможна генерация Excel-файлов с использованием open source библиотек, из которых особо известна Apache POI. Разработчики Apache POI проделали титанический труд по reverse engineering бинарных форматов документов MS Office, и продолжают на протяжении многих лет поддерживать и развивать эту библиотеку. Результат этого reverse engineering-а, например, используется в Open Office для реализации сохранения документов в форматах, совместимых с MS Office.

Но у прямого использования Apache POI есть и недостатки. Во-первых, это Java-библиотека, и если ваше приложение написано не на одном из JVM-языков, вы ей вряд ли сможете воспользоваться. Во-вторых, это низкоуровневая библиотека, работающая с такими понятиями, как «ячейка», «колонка», «шрифт». Поэтому «в лоб» написанная процедура генерации документа быстро превращается в обильную «лапшу» трудночитаемого кода, где отсутствует разделение на модель данных и представление, трудно вносить изменения и вообще — боль и стыд. И прекрасный повод делегировать задачу самому неопытному программисту – пусть ковыряется.

Но всё может быть совершенно иначе. Проект Xylophone под лицензией LGPL, построенный на базе Apache POI, основан на идее, которая имеет примерно 15-летнюю историю. В проектах, где я участвовал, он использовался в комбинации с самыми разными платформами и языками – а счёт разновидностей форм, сделанных с его помощью в самых разнообразных проектах, идёт, наверное, уже на тысячи. Это Java-проект, который может работать как в качестве утилиты командной строки, так и в качестве библиотеки (если у вас код на JVM-языке — вы можете подключить её как Maven-зависимость).

Xylophone реализует принцип отделения модели данных от их представления. В процедуре выгрузки необходимо сформировать данные в формате XML (не беспокоясь о ячейках, шрифтах и разделительных линиях), а Xylophone, при помощи Excel-шаблона и дескриптора, описывающего порядок обхода вашего XML-файла с данными, сформирует результат, как показано на диаграмме:

Шаблон документа (xls/xlsx template) выглядит примерно следующим образом:

Как правило, заготовку такого шаблона предоставляет сам заказчик. Вовлечённый заказчик с удовольствием принимает участие в создании шаблона: начиная с выбора нужной формы из «Консультанта» или придумывания собственной с нуля, и заканчивая размерами шрифтов и ширинами разделительных линий. Преимущество шаблона в том, что мелкие правки в него легко вносить уже тогда, когда отчёт полностью разработан.

Когда «оформительская» работа выполнена, разработчику остаётся

Создать процедуру выгрузки необходимых данных в формате XML.
Создать дескриптор, описывающий порядок обхода элементов XML-файла и копирования фрагментов шаблона в результирующий отчёт
Обеспечить привязку ячеек шаблона к элементам XML-файла с помощью XPath-выражений.

Если бы в форме, которую мы создаём, не было повторяющихся элементов с разным количеством (таких, как строки накладной, которых разное количество у разных накладных), то дескриптор выглядел бы следующим образом:

Здесь root – название корневого элемента нашего XML-файла с данными, а диапазон A1:Z100 – это прямоугольный диапазон ячеек из шаблона, который будет скопирован в результат. При этом, как можно видеть из предыдущей иллюстрации, подстановочные поля, значения которых заменяются на данные из XML-файла, имеют формат

(тильда, фигурная скобка, XPath-выражение относительно текущего элемента XML, закрывающая фигурная скобка).

Что делать, если в отчёте нам нужны повторяющиеся элементы? Естественным образом их можно представить в виде элементов XML-файла с данными, а помочь проитерировать по ним нужным образом помогает дескриптор. Повторение элементов в отчёте может иметь как вертикальное направление (когда мы вставляем строки накладной, например), так и горизонтальное (когда мы вставляем столбцы аналитического отчёта). При этом мы можем пользоваться вложенностью элементов XML, чтобы отразить сколь угодно глубокую вложенность повторяющихся элементов отчёта, как показано на диаграмме:

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

Есть и ещё один возможный вариант повторяющихся элементов: листы в книге Excel. Возможность организовать такую итерацию тоже имеется.

Рассмотрим чуть более сложный пример. Допустим, нам надо получить сводный отчёт наподобие следующего:

Пусть диапазон лет для выгрузки выбирает пользователь, поэтому в этом отчёте динамически создаваемыми являются как строки, так и столбцы. XML-представление данных для такого отчёта может выглядеть следующим образом:

Мы вольны выбирать названия тэгов по своему вкусу, структура также может быть произвольной, но с оглядкой на простоту конвертации в отчёт. Например, выводимые на лист значения я обычно записываю в атрибуты, потому что это упрощает XPath-выражения (удобно, когда они имеют вид @имяатрибута ).

Шаблон такого отчёта будет выглядеть так (сравните XPath-выражения с именами атрибутов соответствующих тэгов):

Теперь наступает самая интересная часть: создание дескриптора. Т. к. это практически полностью динамически собираемый отчёт, дескриптор довольно сложен, на практике (когда у нас есть только «шапка» документа, его строки и «подвал») всё обычно гораздо проще. Вот какой в данном случае необходим дескриптор:

Полностью элементы дескриптора описаны в документации. Вкратце, основные элементы дескриптора означают следующее:

element — переход в режим чтения элемента XML-файла. Может или являться корневым элементом дескриптора, или находиться внутри iteration . С помощью атрибута name могут быть заданы разнообразные фильтры для элементов, например
- name="foo" — элементы с именем тэга foo
- name="*" — все элементы
- name="tagname[@attribute='value']" — элементы с определённым именем и значением атрибута
- name="(before)" , name="(after)" — «виртуальные» элементы, предшествующие итерации и закрывающие итерацию.
- mode="horizontal" — режим вывода по горизонтали (по умолчанию — vertical)
- index=0 — ограничить итерацию только самым первым встреченным элементом
- sourcesheet —лист книги шаблона, с которого берётся диапазон вывода. Если не указывать, то применяется текущий (последний использованный) лист.
- range – диапазон шаблона, копируемый в результирующий документ, например “A1:M10”, или “5:6”, или “C:C”. (Применение диапазонов строк типа “5:6” в режиме вывода horizontal и диапазонов столбцов типа “C:C” в режиме вывода vertical приведёт к ошибке).
- worksheet – если определён, то в файле вывода создаётся новый лист и позиция вывода смещается в ячейку A1 этого листа. Значение этого атрибута, равное константе или XPath-выражению, подставляется в имя нового листа.
Ну что же, настало время скачать Xylophone и запустить формирование отчёта.
Возьмите архив с bintray или Maven Central (NB: на момент прочтения этой статьи возможно наличие более свежих версий). В папке /bin находится shell-скрипт, при запуске которого без параметров вы увидите подсказку о параметрах командной строки. Для получения результата нам надо «скормить» ксилофону все приготовленные ранее ингредиенты:

Открываем файл report.xlsx и убеждаемся, что получилось именно то, что нам нужно:

Так как библиотека ru.curs:xylophone доступна на Maven Central под лицензией LGPL, её можно без проблем использовать в программах на любом JVM-языке. Пожалуй, самый компактный полностью рабочий пример получается на языке Groovy, код в комментариях не нуждается:

У класса XML2Spreadsheet есть несколько перегруженных вариантов статического метода process , но все они сводятся к передаче всё тех же «ингредиентов», необходимых для подготовки отчёта.

Важная опция, о которой я до сих пор не упомянул — это возможность выбора между DOM и SAX парсерами на этапе разбора файла с XML-данными. Как известно, DOM-парсер загружает весь файл в память целиком, строит его объектное представление и даёт возможность обходить его содержимое произвольным образом (в том числе повторно возвращаясь в один и тот же элемент). SAX-парсер никогда не помещает файл с данными целиком в память, вместо этого обрабатывает его как «поток» элементов, не давая возможности вернуться к элементу повторно.

Использование SAX-режима в Xylophone (через параметр командной строки -sax или установкой в true параметра useSax метода XML2Spreadsheet.process ) бывает критически полезно в случаях, когда необходимо генерировать очень большие файлы. За счёт скорости и экономичности к ресурсам SAX-парсера скорость генерации файлов возрастает многократно. Это даётся ценой некоторых небольших ограничений на дескриптор (описано в документации), но в большинстве случаев отчёты удовлетворяют этим ограничениям, поэтому я бы рекомендовал использование SAX-режима везде, где это возможно.

Надеюсь, что способ выгрузки в Excel через Xylophone вам понравился и сэкономит много времени и нервов — как сэкономил нам.

Решил написать статью, о том, как сделать выгрузку данных в Excel файл по шаблону и считывать данные из Excel.
Началось всё с того, что на работе, дали указание, уйти от MS Office, на бесплатные аналоги.
У нас уже была система выгрузки, основанная на библиотеке “Microsoft.Office. Interop.Excel” и много готовых шаблонов, для выгрузки тех или иных отчётов.
Поэтому надо было найти бесплатную библиотеку, работающую с офисом. И сделать так, чтоб выгрузка работала по той же системе, что и раньше. Ибо переделывать все шаблоны и структуру, не хотелось.
Вот я и наткнулся на OpenXML. И думал, сейчас по быстрому найду решение в интернете и всё готово (т.к. на это было выделено мало времени). Но подходящего решения так и не нашёл, поэтому и решил написать эту статью, для тех у кого будет, такая же проблема.
Саму библиотеку, можно скачать бесплатно с сайта Micrisoft (я использовал в проекте OpenXML sdk 2.5 “ OpenXMLSDKV25.msi ”)
здесь.
После скачивания “OpenXMLSDKV25.msi ”, устанавливаем и заходим в папку
“C:\Program Files\Open XML SDK\V2.5\lib” там лежит библиотека, которая нам понадобится, мы её подключим к проекту (ниже будет описано, как).
Проект был написан на Visual Studio 2010 (Framework 4.0).
Ниже пример шаблона (сделан для теста) “C:\Templates\template.xlsx”.

И пример выгруженных данных (как это будет выглядеть в итоге, после выгрузки).

Ключевые слова:
DataField: — Означает, что на этом месте будут выведены наши банные из DataTable.
DataField:[название выводимого поля]
Label: — Означает, что на этом месте будут выводиться данные, которые надо вставить однократно из словаря
Label:[название ключа в словаре]
А это файл из которого мы будем считывать данные “C:\Loading\ReadMePlease.xlsx”.

Теперь создадим в VS2010, Решение в котором будет 4 проекта:
1) OpenXmlPrj – это консольный проект, для запуска теста.
2) Interfaces – это проект типа “Библиотека классов”, будет хранить наши интерфейсы данных для выгрузки.
3) Framework — это проект типа “Библиотека классов”, тут и будет происходить вся работа с Excel-ем.
4) Converter — это проект типа “Библиотека классов”, для конвертирования наших данных в DataTable (т.к. работа происходит с DataTable).

Теперь в проекте “Framework” создаём две папки и подключим ссылку на библиотеку OpenXML и WindowsBase:
“Create” – для работы с выгрузкой данных.
“Load” – для работы с загрузкой данных.
“lib” – в папку, добавим библиотеку OpenXML.
В папке “Create” создаём 4 класса.
1) Worker – это будет наш главный обработчик.

2) Footer – будет содержать строки и их ячейки идущие после наших данных.

3) CellForFooter – содержит в себе координаты ячейки и её значение, используется в Footer-е.

4) Field – будет содержать индекс строки, где находится DataField, координаты ячеек с DataField и название поля, значение которого надо вывести.

Приветствую всех.
При автоматизации небольших магазинов для хранения данных часто используют PostgreSQL. И часто возникает потребность экспортировать эти данные в Excel. В этой статье я расскажу вам как я решал эту задачу. Естественно, матерые специалисты вряд ли откроют для себя что-то новое. Однако, материал будет интересен тем кто «плавает» в этой теме.

Итак, естественно, самый просто и банальный способ экспортировать данные результатов запросов в csv-файлы, а затем открыть их в Excel. Это выглядит вот так:
- во-первых, вставка данных из PostgreSQL происходит именно на сервере;
- во-вторых, можно конечно заморочиться написать batch-скрипт, который будет удаленно вызывать этот запрос на сервере, затем этот файл скопировать на компьютер пользователя и инициировать открытие в Excel.
1. Идем по ссылке и в зависимости от разрядности компьютера скачиваем установщик ODBC драйвера. Установка его проста и не требует особых знаний.

2. Чтобы пользователи могли со своих компьютеров цепляться к БД не забудьте в файле pg_hba.conf установить параметры для IP-адресов, с которых можно производить подключения:

В данном примере, что все рабочие станции смогут подключаться к серверу с БД:

3. Далее через Excel просто генерируем файл динамического запроса к данным *.dqy. Далее этот файл просто можно менять по своему усмотрению. Можно прям ниже следующий текст взять, скопировать в блокнот и там отредактировать, сохранив файл *.dqy. Вводим имя файла и расширение dqy. Выбираем типа файла ВСЕ(All files):

DATABASE – указывается наименование БД к которой будет производиться подключение;
SERVER – адрес сервера;
PASSWORD – пароль на подключение к БД.

Обратите внимание, что в большом тексте указываются параметры подключения к БД и ваша БД. Также можно еще сконфигурировать множество параметров подключения

В последней строке пишется сам запрос. Далее сохраняем файл. Если на компьютере установлен Microsoft Excel, тогда файл сразу же приобретет пиктограмму:

При запуске файла будет выдано диалоговое окно. Смело нажимаем «Включить»:

И получаем результат запроса из БД:

Теперь можно создать несколько таких файлов и спокойно скопировать их на рабочий стол пользователя:

Кстати, я пошел немного дальше. Откопал старый добрый VB6. Можно так сделать с любым языком программирования. Сделал форму, которая по выбранной дате запрашивает данные из БД, путем генерации этого *.dqy файла:

Затем немного покодил (вот часть кода):

Результат получился тот же — данные из Excel, и пользователю удобно. Да, кстати, в строке:

если речь идет о 64-битном процессоре и драйвере ODBC, установленном для 64 бит, то надо писать:

Ну, и самое главное, несмотря на всю простоту способа, у него есть конечно недостатки: запрос можно писать только в одну строку, т.е. записать строку вот в таком виде не получиться. Нужно только в одну:

— Не сможет обрабатывать на изменение данных типа:
или

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

Интеграция StoreHouse 5 и r_keeper_7 позволяет синхронизировать базы и вести учет продаж и оставшихся продуктов.

Настройка интеграции

Интеграция r_keeper_7 и StoreHouse 5 работает через утилиту QUSH. Утилита подключается к базе данных SQL и берет оттуда информацию справочники и продажи r_keeper 7. Для подключения можно использовать любую базу данных:

После установки StoreHouse, на компьютере появляется служба сервера QUSH, который работает на порту 8087.

Для интеграции баз StoreHouse_5 и r_keeper_7 откройте утилиту Qush_Manager. По умолчанию пользователь - Admin. Пароля нет.

Зайдите в меню Файл > Связки с R_keeper.

Нажмите правой кнопкой мыши в левом поле и выберите Создать.

Введите название связи.
Далее вы увидите окно для связки StoreHouse 5 и данных вашей базы SQL.
Перейдите во вкладку Базы данных. Заполните настройки:

Технические настройки закончены, переходим к детальным настройкам импорта.

Детальные настройки

Перейдите во вкладку Рестораны, нажмите кнопку Обновить. После обновления вам будут доступны списки ваших ресторанов из базы r_keeper.

Вы можете выбрать либо все рестораны, либо рестораны объединенные одной франшизой или концепцией. Выбрав пункт Только выбранные, просто выделите необходимые рестораны.

Нажмите Сохранить.
Во вкладке Справочники выбираются группы меню ресторана, если их не существует вы можете создать новые прямо в QUSH. Для этого нажмите на квадратную кнопку с многоточием.
1. Не задано — значение по умолчанию.
2. Сервис-печать — разделения заказов по принтерам для поваров.
3. Классификация — разделения заказов на группы (холодное, горячее и пр)
4. Рейтинги кассиров — разделение заказов согласно рейтингу сумм на сотрудника
5. Группы для KPI-аналитик — разделение заказов согласно группам для KPI-аналитик. Группы заводятся вручную.
Далее по необходимости укажите пункты:
- Выгружать блюда без цены
- Выгружать предоплаты в заявки
- Рассчитывать налоги по настройкам r_keeper
Далее вам нужно определить будет ли учитываться НДС и НСП, заданные в менеджерской станции r_keeper.

Нажмите Сохранить.
Во вкладке Автозапуск задаются критерии и время автоматического импорта данных из r_keeper.
Здесь возможно задать время запуска, определить за какой период делать импорт, а также разрешить автоматическое и онлайн списание.

Поставьте галочку напротив пункта Уведомлять об ошибках по e-mail, чтобы получать уведомления при ошибке импорта.

Настройка автоматического списания

При импорте продаж из r_keeper_7 может быть настроено автоматическое списание.

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

Если же в расходе r_keeper были новые блюда, по которым не определены подразделения списания, не созданы комплекты либо комплекты созданы с ошибками, то система не сможет обработать заявки в полном объеме. В результате чего будет сформирован протокол ошибок. Данный протокол можно просмотреть в протоколе импорта, если импорт запускался вручную.

Если импорт запускается автоматически системой, то в случае возникновения ошибок при импорте или списании, можно настроить уведомление по электронной почте При соответствующих настройках сервер импорта будет импортировать данные из r_keeper_ автоматически. Для этого на закладке Автозапуск необходимо указать настройки.

Запускать импорт ежедневно – после выставления этого флага импорт данных из R-Keeper 7 будет запускаться автоматически каждый день в указанное время.
Время запуска : – в это поле с помощью клавиатуры внесите время начала автоматического импорта.
Продажи за… – в этом поле необходимо выбрать день, за который будут автоматически выгружаться продажи: Сегодня, Вчера или Позавчера.
- Если на момент импорта данных из r_keeper, данные в r_keeper были неактуальные — не все кассы закрыли день, сетевые проблемы передачи данных в накопительную базу r_keeper, то для обновления данных в StoreHouse сделайте импорт вручную. Перед запуском импорта удалите уже импортированные заявки.
- Если выставлен флаг Запускать импорт ежедневно, то при возникновении ошибки импорта система будет повторять попытки импорта — 8 раз с перерывом в 15 минут
- При ошибках импорта с созданием или обработкой заявок процесс импорта повторяться не будет.
Флаг Автоматическое списание – после операции Импорт из RK система будет пытаться создать накладные на основе импортированных заявок.
Уведомлять об ошибках по e-mail – если выставлен флаг Запускать импорт ежедневно, то при выставлении данного флага и настройках Уведомлений при возникновении ошибок импорта или автоматического списания, информация будет отправлена на e-mail-ы, указанные в настройках Уведомлений.
Следующий запуск: дата и время – в данном поле будет выведена информация о следующем автоматическом запуске импорта, если вставлен флаг Запускать импорт ежедневно

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

Проверка работы

Теперь когда настройка проведена, перейдите в раздел Задания. Здесь вы можете провести импорт данных в ручном режиме.

Выберите необходимый период и нажмите кнопку Запустить.
Если вы всё настройки верны, вы увидите в графе результат Успешно:

Если при импорте были ошибки, то история импорта будет выглядеть так:

Чтобы подробнее узнать о результатах импорта или ошибках, нажмите 2 раза на нужную строчку:

Если было настроено Автоматическое списание, то в протоколы будет выведена и информация по ошибкам списания.

Список ошибок списания в протоколе будут аналогичен списку ошибок в Предполагаемом расходе, формируемом при ручной обработке импортированных заявок.
Протокол при необходимости можно сохранить в форматах HTML и XML. Для этого откройте протокол, выберите пункт главного меню Задание и выберите пункт с соответствующим форматом.

В открывшемся окне выберите место сохранения файла. Также можете изменить имя сохраняемого файла. И нажмите кнопку Сохранить.

Решение проблем

Проблема. Продажи из r_keeper_7 не загружаются в StoreHouse 5
Причина. Продажи не попали в базу SQL. Зайдите в SQL и посмотрите таблицы ORDERS (заказы), PAYMENTS (оплаты), VISITS (визиты) — в них должны быть данные.
Решение. Проверьте настройки SQL, верно ли указана БД. Если всё верно, скачайте смены заново.

Читайте также:

Как из storehouse выгрузить в excel

Настройка интеграции

Детальные настройки

Настройка автоматического списания

Проверка работы

Решение проблем