Вот, все-таки пришла беда, из 1С, сменили у источников наших "программу", и стала она выдавать списки не простые плоские списки (экселевские, или DBF, это же не важно, когда списки "плоские"), типа:
{цех, вид_отчисления, ФИО, [таб_номер, еще какие-то параметры чела], сумма}
а теперь в виде Excel-"сводная таблица" (PivotTable). Сменить формат источники не в силах, управлять количеством колонок тоже не в состоянии, у них получаются примерно похожие, но немного разные количества градаций, группировок (как количество полей в GROUP BY).

Ну, к собственно сводным отчетам (СО) не придраться, в экселе все правильно показывают, и частные суммы по цехам, и по видам отчисления, раскрывашки в виде квадратиков с +/-, внешне - просто зашибись. Но, зараза!, я не могу теперь за просто так создать плоский поименный список в DBF, все в том же вышеуказанном виде. Вобщем третий день сижу в интернетах, пробую всякие
PivotTables
PivotFields (RowFields, ColumnFields, PageFields)
PivotItems
Все это вкупе с .DataRange
Все это по разному выглядит, если .ShowDetail - раскрыто или не раскрыто на экране (т.е. надо раскрыть на экране в Cells эти строки, чтобы их вернуло свойство какого-нибудь PivotItems(i) или GetPivotDate() !)
Пробовал PivotTables(1).GetPivotData(поле_суммы, пары {имя_агрегатного_поля, значение_агрегатного_поля}) - не работает, когда поля скрыты, не работает, когда искомой комбинации нет. ("Не работает", значит ОЛЕ-ошибка. Заранее узнать, можно ли читать - не нашел свойства, и идеи...)
Конечно, можно все это выполнять в TRY...ENDTRY, но как-то не хорошо так сразу-то...

Вобщем 1й вопрос такой. Вот ребята предлагают сначала преобразовать сводную таблицу к "классическому" виду (она хотя бы более мне понятна для разбора программой):
pivotTable.RowAxisLayout xlTabularRow
И еще какие-то экселевские преобразования таблицы (см. спойлер1). Нельзя ли как нибудь выполнить в m.oExcel.Run() текст произвольной VBA-программы, (лежащей где?)? Ну, чтобы не переводить все это на фоксе... Может есть такой файл test.VBA, который можно выполнять как в фоксе DO (имя_файла.FXP)
Если эту VBA-программу написать в XLS-файле, то вроде можно: m.oExcel.Run("Test.xls!mmm")...

Вот, например, чего надо исполнить, чтобы привести СО в более-менее "пригодный" вид:

Попытки прочитать данные именно из PivotTable как-то уже... не асилил взаимосвязей этих Pivot-ов, не понимаю идею группировок, (сверток, которыми представлена исходная таблица), как из них обратно развернуть плоские данные, ...также сплошные ОЛЕ-ошибки, чуть что не так обратишься к свойству...

Может есть простая команда - "развернуть СО Pivot в плоскую таблицу" - поделитесь. Какие видел в интернете - несколько бредовые, с заточкой на специальный вид СО (в них нет слова Pivot, все сделано на обычных ячейках Cells... не понятно)


Вот попытки разбора СО. Сам XLS-файл приложить не могу

Данные (по сути "исходные") для сводной таблицы эксель держит в объекте PivotCache. Вот только прямого способа вынуть их оттуда нет.
Тут граждане буржуи извращаются почём зря (хотя идея несложная - привести сводную таблицу в хитрый вид "с одной ячейкой" и дёрнуть для этой ячейки ShowDetail - типа "показать из чего составлено" - эксель выкинет все "составившие эту сумму данные" на отдельный лист). stackoverflow.com
Альтернатива - сохранить в xlsx и залезть во внутренний xml - там по идее этот кэш лежит в прямом и прозрачно читаемом виде.

Ну и наиболее разумное решение (минимизация усилий и получение хорошего результата, а не абы чего и абы как) - попросить "источники" присылать данные в другом виде. ХОТЯ БЫ в том же экселевском документе на отдельный лист "исходник" кидать - а сводную уже по нему и строить...

Исполнить .vba в фоксе никак нельзя (про извращение с написанием своего "интерпретатора vba", точнее конрвертера из чистого VBA синтаксиса в COM-вызовы забудь). Но если позволяют политики безопасности, можно программно "прицеплять" такого рода файл с исходником к xls-у, ну и соответственно потом выполнять записанные там "макросы". Это всё через oBook.VBProject.VBComponents... можно провернуть (например импортировать "модуль" из файла). Но ещё раз - нужны права по безопасности. В частности галка "доверять доступ к VB проекту".

Нет в фоксе выполнять VBA-код не собираюсь, а вот заставить Excel это сделать, т.е. выполнить VBA-код, текст которого в фоксе существует в виде переменной (или, что тоже самое, в файле mmm.vba), или как макрос mmm в файле Test.xls, хотелось бы выполнять. Вроде m.oExcel.Run("Test.xls!mmm") должен это позволить.

По поводу заставить источники делать правильные XLS-файлы - это не реально, "там не наш товарищ правит бал". За наводки спасибо. Несколько дней упираюсь в этот СО, простые идеи уже кончились

"Если что-нибудь не получается, попробуй это сделать пьяным". (C) (Пива)

"Когда мне нечего сказать, я ничего не говорю". (с) (Симпле)

Я так больше для себя запишу результаты "изысканий".

В .xlsx есть два файла XML:
- pivotCacheDefinition1.xml - "справочники" - в теге <cacheFields count="13"> содержатся 13 групп типа <cacheField name="Ф И О"> (перечисление возможных ФИО)
- pivotCacheRecords1.xml - связи элементов справочника, в теге <pivotCacheRecords ... count="12345"> содержатся группы <R> по 13 элементов:
<r>
<x v="49" /> - это ссылка на элемент в справочнике №1 из 13 (ФИО)
<s v="1112123" /> - это ТН в непосредственном виде: справочник №2 пуст: <cacheField name="Таб. номер" numFmtId="49"> <sharedItems containsBlank="1" />
<n v="1" /> - и так далее, ссылки, или непосредственные значения
<n v="2017" />
<n v="128" />
<n v="0" />
<n v="479" />
<x v="0" />
<n v="1823.45" />
<n v="16666" />
<s v="Начальник смены" />
<s v="АБВГ филиал - АБВГ_Ново-Мухобойск - АБВГ_Мухобойный цех - АБВГ_ персонал АБВГ" />
<x v="1" />
</r>

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

Приём по ссылки не пробовал?

Если "вручную" делать: Оставить в сводной таблице только ОДНО поле в области значений (все прочие области должны быть пусты! Т.е. ВСЯ сводная таблица это одно поле в области значений). Брать можно любое поле - хоть числовое, хоть ФИО. То что он там считать будет - сумму или количество - совершенно неважно. В сводной таблице получится всего одна ячейка. По ней даблклик, или из контекстного меню "показать детали". Эксель создаст новый лист, куда и забабахает всю исходную таблицу. Из этого самого кэша - в "читаемом" виде, т.е. без "ссылок на справочники значений в колонке" - в обычном плоском виде.

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

Пока не пойму как "Оставить в сводной таблице только ОДНО поле в области значений".
На любой информативной конечно строке DblClick действительно показывает одну расшифрованную строку из 13 (сколько колонок в кэше) ячеек. Только не на новом Sheet, а вместо СО...
Да, DblClick на частной сумме раскрывает пофамильное содержимое этой суммы в 13 колонках! Это уже где-то рядом. Идею понял, только после этого DblClick пропадает их поля зрения исходный СО...
Ага, внизу есть ИТОГО, по всем категориям, на сумме 2кликаешь - и раскрывается пофамильный список в 13 ожидаемых колонках! Ура.

По правой мышке что-то не вижу "Показать детали"...
При записи макроса DblClick делает:
Range("B49").Select ' это ячейка итоговой суммы при "ИТОГО:" в самом низу СО
Selection.ShowDetail = True

Кнопку перемещения по листам спрятали куда-то сцуки, хотя в Immediate их видно:
? Application.Sheets.Count ' 3
Application.Sheets(3).Activate ' переключает на нужный лист


Теперь стали понятнее примеры ():

В примере 2:
Dim cn As ADODB.Connection ' ошибка User defined type not defined

В примере 3:
...ниасилил

Дальше осталось найти поле суммы ИТОГО (например B49), чтобы выполнить на нем .ShowDetail = True
Строки ИТОГО могут и отсутствовать... Заставлю оператора нажать эту кнопку руками, если увижу на текущем листе PivotTables.Count>0, чтобы он сделал текущим листом плоскую таблицу. Кстати, "Показать детали" по правой кнопке возникает только на агрегатных полях (там где сумма, причем состоит из нескольких физических сумм)

Игорь, еще раз спасибо за DblClick (он же Range("ИТОГО").ShowDetail=True), 3 дня не мог догадаться, что кнопка-раскрывашка в плоскую таблицу есть!



Исправлено 8 раз(а). Последнее : of63, 23.05.17 15:09

of63
Пока не пойму как "Оставить в сводной таблице только ОДНО поле в области значений".

В 2013 офисе такой интерфейс. В более старых диалог чуть иначе выглядит.
[attachment 27689 get_pt_data.png]

of63
Кнопку перемещения по листам спрятали куда-то

Бывает прячут ярлычки листов. Или делают другие листы "скрытыми". Кстати, если листов 3 (после однократного "показа деталей"), то может на одном из них как раз и лежит в чистом виде исходная таблица

of63
Теперь стали понятнее примеры

Да, только они в примере НОВУЮ сводную таблицу делают на основе кэша "старой". И тупо кидают туда в "область значений" первое из полей... Потом, после показа деталей, удаляют эту сводную таблицу.

of63
В примере 2:
Dim cn As ADODB.Connection ' ошибка User defined type not defined

Чтобы коннекцию пользовать нужно во-первых ссылку на ADODB компонент в References VBA проекта добавить. во-вторых нужно чтобы сама сводная таблица была создана на основе "внешних данных" (т.е. данные грубо говоря в dbf, мы из экселя к таблице подключились, и создали на основании этих данных сводную таблицу). в-третьих нужно чтобы этот источник данных был доступен, что КРАЙНЕ маловероятно в твоей ситуации

of63
В примере 3:
...ниасилил

Допиливание 1-го примера.
Снимают фильтры (сводную таблицу можно "отфильтровать", и тогда в "разворот" не все данные попадут), зачем-то ищут именно числовое поле для помещения в "значения" - хотя мне кажется это несущественно. По тому же первому полю ФИО всё нормально "развернётся" - просто в самой сводке будет не "сумма" а "количество записей". Ну и так "мелочи оформления"...

Дальше я придумаю (заставлю оператора раскрывать список, пока так). Ни один пример не оживил, но проще свой напишу, "чукча не читатель - чукча писатель".

> может на одном из листов как раз и лежит в чистом виде исходная таблица
Нет, лист 1, скрытых не вижу (источники сделали СО на том же листе, где были исходные плоские данные), 2й и 3й листы - после экспериментов добавились. Каждое 2нажатие раскрывашки частной/полной суммы делает новый лист.

> Бывает прячут ярлычки листов. Или делают другие листы "скрытыми".
Скрытых не видно, изначально листов 1. Теперь листы показываются, после нажатий на какие-то панельки экселя. Причем файл все той же старой даты, ничего в нем не изменилось, но стали показываться листы... Похоже эксель запомнил имя файла, на котором мне приспичило видеть листы, что ли, и теперь показывает. Но абс. точно что изначально не показывал, т.к. наш спец по экселю тоже вчера матерился по этому поводу... Кстати, он тоже не нашел кнопку раскрывашку, но как-то через всякие последовательноые "уплощения" таблицы достиг, но не вида PivotCache

Доб. Как выполнять одинаковый VBA-код для произвольного обрабатываемого файла .xls:
- делаем пустую таблицу, пишем в ней нужные макросы, например myMacros1, точнее программы Sub() и Function();
- сохраняем таблицу в файле .xlsm (таблица с макросами) - это будет аналог фоксового .PRG;
- сохраняем эту же таблицу в файле .xlam (надстройка Excel) - это будет аналог .FXP, т.е. как бы произвели компиляцию;
- для использования подпрограмм из файла .xlam надо открыть его (до или после открытия обрабатываемого файла) как oExcel.Workbooks.Open(файл.xlam). Он откроется, но виден как книга или лист не будет, но будут доступны его макросы! - это аналог фоксового SET PROCEDURE;
- макросы .xlam выполнять oExcel.Run("myMacros1") или с параметрами oExcel.Run("myMacros1", параметр1, ...)
Замечание: файл .xlam в Excel уже не редактируется, после подключения .xlam в Excel макросы остаются "ненаблюдаемыми", хотя выполняются в .Run(), впрочем все как в SET PROCEDURE TO файл.FXP фокса.



Исправлено 1 раз(а). Последнее : of63, 25.05.17 08:19

Насчет непоказываемых листов. Если это то, что я думаю, то надо выполнить типа "Вид" - "Упорядочить все". Тогда все листы становятся видны. И вааще по экселевским заморочкам почти всегда помогает звонок другу вопрос гуглю. Это я не в плане "нравоучений", а о том, что реально практически на все вопросы, которые я задавал, находились прямые ответы, в том числе и с кодами на VBA.



Исправлено 2 раз(а). Последнее : Simple777, 23.05.17 19:46