Страницы: 1
RSS
Как сделать экспорт умной таблицы, созданной через PQ в XML из excel?, Как сделать экспорт умной таблицы, созданной через PQ в XML из excel?
 
Доброго времени суток. Прошу вас помочь, как сделать экспорт данных из Эксель в XML ? Суть истории следующая:

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

Первые два блока организованы в виде рядов ячеек с имеющимися в них значениями, а третий формируется, как умная таблица, формируемая из сводной при помощи Power query, которую справа я растянул и дополнил столбцами, в которых прописаны формулы, преобразующие под заданный формат данные из левой половины.

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

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

Если я то, что получил при помощи PQ скопирую в другое место на листе и сделаю тоже умной таблицей, то сопоставление по её столбцам происходит по схеме xsd без вопросов, но это не тот результат, какой мне нужен. Каждый день обновлять и копировать полученный список  и подставлять в поля простой таблицы никто не будет...

Задача стоит следующая - сделать так, чтобы перечень документов, который требуется сопоставить для выгрузки в XML по схеме, автоматически изменялся, вот для чего и создавался запрос через PQ на сводную таблицу, т.к. только при помощи нее собирается перечень нужного вида.  При выборе другого №ФЭС сводная таблица обновляется, изменяется информация в сводной таблице, изменяется и её размер, происходит обновление запроса и содержимое умной таблицы через PQ само собой изменяется.

Т.к. эксель выдал ошибку, тогда я пристроил к таблице 1 вторую умную таблицу, куда данные перетаскиваются формулами, но связать её с той, что слева, так, чтоб она под неё изменялась, удалялись лишние строки или добавлялись, не удается. Первая обновляется данными и изменяется в размере, а вторая нет и формулы в ней не тянутся вслед за увеличением строк.

Пробовал тоже самое на разных листах - результат тот же - ошибка "Невозможно выполнить операцию, поскольку её результат будет перекрывать внешний диапазон или сводную таблицу".

Во вложении  файл с моей конструкцией. и вопросы:

- Может ли кто-нибудь помочь создать код на vba для обработки табл.2, чтобы синхронизировать её с табл.1 или вообще на выгрузку данных с конкретных полей целиком в XML , минуя эти карты, сопоставления и прочее?

- почему на таблицу-результат обработки PQ эксель выдает такую ошибку при сопоставлении полей XML карте и не дает использовать сформированные таким образом умные таблицы для сопоставления с данными карты? Можно ли как-то это обойти?  
 
Цитата
Дмитрий kh121 написал:
- почему на таблицу-результат обработки PQ эксель выдает такую ошибку при сопоставлении полей XML карте и не дает использовать сформированные таким образом умные таблицы для сопоставления с данными карты? Можно ли как-то это обойти?  
думаю, потому что такая таблица для Excel - внешний диапазон (если обращали внимание, при первой загрузке на лист из запроса PQ сначала видно "ExternalData1...". Скорее всего, не предусмотрено сопоставление полей с динамическим диапазоном, основанном на внешних данных.
По макросу не подскажу, но, скорее всего, именно там лежит решение
F1 творит чудеса
 
Дмитрий kh121, а удалось вам решить эту задачу? Та же история, Google привёл сюда. Так же нужно собрать с разных листов данные в PQ и вывести по схеме в XML. Может есть какое то решение?
 
Цитата
написал:
Дмитрий kh121, а удалось вам решить эту задачу? Та же история, Google привёл сюда. Так же нужно собрать с разных листов данные в PQ и вывести по схеме в XML. Может есть какое то решение?
Да, удалось, стандартное сдедство эксель для экспорта xml по схеме в этом случае не подходит, оно не умеет работать с динамическими диапазонами, умными и сводными таблицами. Мне для этого понадобилось изучить основы кода vba, искать готовые примеры в сети для их адаптации и написания своего vba-макроса для экспорта в xml.

Весь код xml-файла прописывать надо построчно, создавая дерево узлов xml. А для умной таблицы нужен цикл, чтоб каждую строку из неё представлять в xml, как требует структура xml-файла.


Хотя профи по vba скажут, что это топорно и можно сделать как-то ещё, не прописывая десятка два строк с тегами, а подцепить как-то схему xml, чтоб рисовало xml-файлы по ней, но я сделал, как смог, никто из этих "профи" и пальцем не пошевелил, чтоб объяснить что к чему и помочь. Главное, что оно работает и в случае изменения структуры xml-файла можно быстро адаптировать строки кода под неё.
 
Дмитрий kh121, Спасибо вам за ответ. Как я понимаю, у меня есть минимум два пути: 1. Изучить основы VBA кода. 2. Попросить вас поделиться кодом вашего решения и адаптировать к своим задачам. Попробую пойти по пути наименьшего сопротивления и попросить вас поделиться кодом :)
 
Цитата
Newsky13,
Вот здесь есть соседняя тема с готовым файлом-примером, код которого был мной использован, как основа. https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=114559&a...

Я взял его и настроил на вывод данных со сводной таблицы, в которой содержатся данные по товарке, дате, сумме. Т.к. мне по структуре xml-файла надо каждую строку представлять, какочередной  дочерний узел материнского узла "документ-основание". Цикл проходится по всем строкам, сколько бы их не было в таблице. И дописал к нему отдельно строки кода, нужные для вывода данных остальных блоков xml-файла, ячейки которых статичны.

Вам не обойтись без использования вкладки "разработчик" и изучения основы, как писать в макрос код, чтобы задавать материнские и дочерние узлы xml, согласно требуемой структуре для вашего файла.
Изменено: Дмитрий kh121 - 07.01.2023 16:58:00
Страницы: 1
Наверх