Собрать данные из нескольких книг в один XLS

Автор ForumOOo (бот), 18 декабря 2018, 23:41

0 Пользователи и 1 гость просматривают эту тему.

ForumOOo (бот)

Компонент: Calc
Версия продукта: 5.x
Сборка: LibreOffice 5.4/4
ОС: WinXP

Добрый день!

Почитал сей форум, он показался дружелюбным.

Читаю перевод "OpenOffice.org Macros Explained.Master.pdf" и пытаюсь
понять его, но пока не очень. Опыт в MS Excel при этом весьма большой.

Начальство поставило задачу: на линукс-сервере посредством PHP
создаются xls-файлы (вроде бы Php_Excel_Writer). Около 50 штук. Нужно
собрать их в единый xls-файл, который позже будет перенесен на
windows-машину и должен читаться там. Собрать на php у них не
получилось, он в районе 20-го файла затыкается, и сказали писать макрос.

Файл должен выглядеть как пронумерованные от 1 до ... (пока 51) листы,
каждый из которых содержит соответствующий файл (fd_1.xls, fd_2.xls и
т.д; список имеется). Важно сохранять формат файла - ширину колонок и
цвета ячеек.

OOo Basic более-менее понятен, хоть там есть и особенности. Макрос по
основам Питоньяка для перебора файлов получился, там по сути лишь
создать URL из пути, а Dir так же работает. А вот как потом открыть
файл, добавить в текущий лист и вставить на него содержимое, причем
сохранив форматирование - не понимаю! Масса UNO-сервисов, голова кругом.

Попробовал записать в LibreOfiice Calc-е макрос - выясняется, что он
не записывает даже создание нового листа, да и из буфера вставляет с
потерей форматирования. В экселе я бы сделал Copy-Paste, а затем
специальную вставку с ширинами колонок.

Помогите, пожалуйста, подскажите, что применять или как через
диспетчер команды подать.<br/><br/>--<br/>Подпись: Михаил<br/>Эл. почта: m_bely@yahoo.com

JohnSUN

#1
Добро пожаловать на форум, Михаил!
На UNO и диспетчер заб... забудь, в общем. Неуклюжий инструмент. В принципе, работающий макрос сделать можно, но это крайне многословно и трудоёмко.
Расскажи подробнее об исходных файлах: сколько листов, размеры строк/колонок, как заданы цвета (прямая раскраска или условное форматирование), наличие формул, которые нужно сохранить... В общем, чем больше информации, тем лучше. А если еще и образцы файлов к следующему сообщению прикрепишь - ещё проще общаться будет.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Mike_B

#2
Цитата: JohnSUN от 19 декабря 2018, 00:23
Добро пожаловать на форум, Михаил!
На UNO и диспетчер заб... забудь, в общем. Неуклюжий инструмент. В принципе, работающий макрос сделать можно, но это крайне многословно и трудоёмко.
Расскажи подробнее об исходных файлах: сколько листов, размеры строк/колонок, как заданы цвета (прямая раскраска или условное форматирование), наличие формул, которые нужно сохранить... В общем, чем больше информации, тем лучше. А если еще и образцы файлов к следующему сообщению прикрепишь - ещё проще общаться будет.
Добрый день, Владислав, и спасибо большое Вам за отклик!

Отвечаю на вопросы:

0. Имеется макрос сбора, на который буду ссылаться ниже (файл to_pr1.xls), там стартует Sub auto_open() и запускает последовательно две процедуры auto_open1 (сборка листов) и auto_open2 (раскраска листов). Нас интересует сбор, раскраска уже есть в исходных.

Ссылаюсь только для информации, переписывать его "в лоб" не надо, основная идея в нем такая: выходной файл print_d.xls чистится от лишнего, на него в порядке массива добавляются листы, на которые вставляется содержимое первого (и единственного) листа из очередного файла, файл print_d.xls сохраняется.

1. Листы (52 шт.) в порядке сбора будут перечислены в массиве:

file = Array("92", "01", "01_d", "69", "81", "82", "83", "84", "85", "86", "95", "96", "97", "98", "93", "94", "47", "47_d", "99", "99", "70", "70_d", "99", "17", "02", "02_d", "51", "51_d", "04", "04_d", "54", "05", "07", "06", "06_d", "09", "09_d", "09_d1", "11", "11_d", "11_d1", "13", "60", "20", "64", "65", "66", "68", "68_1", "68_4", "68_2", "68_3")

Сами файлы именуются fd300XXX.xls, где XXX - как раз вот эти элементы массива. Т.е. получаются файлы fd30092.xls, fd30001.xls, fd30001_d.xls и так далее. Часть файлов у них в каталоге out_d, часть в каталоге fd300, это видно в auto_open1, но несущественно, поменяю или свалю в один каталог.

В каждом таком файле один лист, он уже имеет имя вида "лист 3" или "лист 35".

2. Размеры строк и колонок в выходном файле должны быть те же, что в исходных. В экселе это обеспечиывается строкой в макросе:

ActiveWorkbook.ActiveSheet.Copy Before:=Workbooks("print_d.xls").Worksheets("0")

3. Цвета ячеек заданы напрямую, формул и условной раскраски нет.

4. Прилагаю файлы: fd*.xls - примеры исходных данных (файлов около 50, я лишь часть взял), print_d.xls - выходной файл, каким он должен быть (опять же оставил лишь листы, которые как исходные приложены), to_pr1.xls - тут макрос. Сбор еще ничего, раскраску они жуть наваяли. Но она нам не нужна сейчас - исходные уже имеют цвета.

Если Вы сможете написать или подтолкнуть в нужном направлении, буду очень Вам признателен!

JohnSUN

#3
Честно признаюсь - файлы ещё не смотрел. Пишу прямо сейчас, чтобы ты не чувствовал себя одиноко  ;D
(Файлы сейчас посмотрю, обязательно)
Цитата: Mike_B от 19 декабря 2018, 12:57
переписывать его "в лоб" не надо, основная идея в нем такая
...
Если Вы сможете написать или подтолкнуть в нужном направлении, буду очень Вам признателен!
Я со вчерашнего вечера обдумывал твою задачу... Есть мысль. Не уверен, что это и будет окончательным решением, но, возможно, это действительно "толчок в нужном направлении"

Загляни, пожалуйста, в меню Calc'а Лист. Есть там такая позиция - "Вставить лист из файла". В результате этой операции мы, похоже, получим почти то, что от тебя ожидает начальство - и данные, и форматы.
Другими словами, мы один раз делаем книгу, которая через связи (на скриншоте флажок в красной рамке) высасывает данные из предопределённых листов нужных файлов, сохраняем её как шаблон (расширение файла .ots).
Теперь твоему макросу НЕ НУЖНО шуршать по всей папке выбирая нужные файлы и дёргая отдельные листы - макрос просто откроет шаблон (создастся новая книга, в которую подтянутся актуальные данные), пробежится по всем листам этой книги и разорвёт связи (данные и форматы останутся в ячейках, а от исходных книг "отвяжется"), сохранит результат в нужном месте с нужным именем.

Вот. А теперь я буду смотреть твои файлы.  ;D
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Mike_B

#4
Цитата: JohnSUN от 19 декабря 2018, 13:19Загляни, пожалуйста, в меню Calc'а Лист. Есть там такая позиция - "Вставить лист из файла". В результате этой операции мы, похоже, получим почти то, что от тебя ожидает начальство - и данные, и форматы.Другими словами, мы один раз делаем книгу, которая через связи (на скриншоте флажок в красной рамке) высасывает данные из предопределённых листов нужных файлов, сохраняем её как шаблон (расширение файла .ots).
Владислав, спасибо за идею. Но пока полностью реализовать не получилось.
Пункт такой в меню есть. Сидя в файле tab12.ods, в котором пробую макросы из книжки Питоньяка, вставил через него 2 листа в конец, сохранил как tab12.ots.
Теперь взял те самые xls и в Excel 2007 изменил одну из ячеек.
Открываю в Calc, ожидая увидеть изменения - но вижу сообщения Calc, что он это сделать не может - на скрине. Может, я еще не такой опытный и чего-то забыл отметить...

Mike_B

#5
Цитата: Mike_B от 19 декабря 2018, 14:13Может, я еще не такой опытный и чего-то забыл отметить...
Точно, я не отметил галку "Связь с файлом". Переделал. Смотрим теперь: в исходном файле было значение 4450, я вставил лист 3 при нём. Создал ots. Изменил в экселе на 5000 и цветом выделил. Открываю теперь ots - ругается (на скрине), но обновил же! В чем тогда причина ругани?

JohnSUN

(Тему переименовал - так вроде бы получше будет  ;D )
Цитата: Mike_B от 19 декабря 2018, 14:13
Теперь взял те самые xls и в Excel 2007 изменил одну из ячеек.
Открываю в Calc, ожидая увидеть изменения - но вижу сообщения Calc, что он это сделать не может - на скрине.
...чего-то забыл отметить...
Есть у меня смутное подозрение, что ты забыл закрыть файл в Экселе

О макросах... Просмотрел код. Понимаю, что эти "полторытыщистрок" написаны не за один присест, мог что-то упустить, что-то забыть.
Но на некоторых строка я больно спотыкался.
Например,
If (ThisPath & "\out_d\fd300" & file(i) & ".xls") <> "" Then
Не могу придумать ситуацию, когда условие будет ложным. Возможно, ты имел в виду
If Dir(ThisPath & "\out_d\fd300" & file(i) & ".xls") <> "" ThenВ таком случае обрати внимание на функцию FileExists
Сильно смутил этот длиннющий пассаж
  For i = 1 To 50
      Select Case i   ' Выбор листа
            Case 50
                Sheets("лист 2").Select...
Ты же буквально сотней строк выше используешь прекрасную конструкцию
file = Array("92", "01", "01_d", "69", "81"...Почему бы не повторить это и здесь?
А когда добрался до форматирования по значениям, до всех этих RGB() по условиям - вообще запутался. Это ты писал на случай если не удастся вытащить цвета из исходных файлов? Или действительно нужна дополнительная обработка?
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Mike_B

#7
Дело в том, что не я писал эти тыщи строк. )) Коллеги тут с год-полтора назад. Я им только показал, как форматировать цветом и IIf() научил. А остальное они сами, я другим занимался и не в курсе был. Сейчас смотрел и удивлялся. И да, Dir явно у них пропущено, тоже заметил.

Выбор Select-ом листов - согласен, надо иначе, вот в Либре есть Choose - пригодится. )) Но здесь Array сложнее применить, там был перебор с 0-го индекса, а тут нужно будет на необходимые позиции ставить значение или 2-мерный массив "индекс-значение", или словарь (Scripting.Dictionary)

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

Насчет экселя - нет, закрывал. Я ж сам программист и что такое блокировки, знаю. В экселе есть Application.DisplayAlert = False, вот тут это не поможет? Проверил пока что: если Либре сказать НЕ обновлять связи, она открывает старое значение (4450), если обновлять - несмотря на ругань, читает 5000 (правильно).

Дополняю: пересоздал файл с нуля, в Calc на экране приветствия - создать Таблицу Calc. Теперь без вопросов обновляет. Поехали дальше :), как потом связи разорвать? И как после мне пути исправить, ведь лежать в итоге будет на юникс-машине этот ots? Далее, как сказать, чтобы макрос в нем стартовал сам (автозапуск) и как потом сохранить результат в xls?

rami

Цитата: Mike_B от 19 декабря 2018, 12:57file = Array("92", "01", "01_d", "69", "81", "82", "83", "84", "85", "86", "95", "96", "97", "98", "93", "94", "47", "47_d", "99", "99", "70", "70_d", "99", "17", "02", "02_d", "51", "51_d", "04", "04_d", "54", "05", "07", "06", "06_d", "09", "09_d", "09_d1", "11", "11_d", "11_d1", "13", "60", "20", "64", "65", "66", "68", "68_1", "68_4", "68_2", "68_3")

Сами файлы именуются fd300XXX.xls, где XXX - как раз вот эти элементы массива. Т.е. получаются файлы fd30092.xls, fd30001.xls, fd30001_d.xls и так далее.
У вас жалуется на отсутствие файла fd30000_s.xls, насколько я вижу, из элементов указанного массива создать или найти такое имя файла нельзя

Mike_B

Цитата: rami от 19 декабря 2018, 15:49У вас жалуется на отсутствие файла fd30000_s.xls, насколько я вижу, из элементов указанного массива создать или найти такое имя файла нельзя
Их глюк, не все файлы мне дали. Потому пересоздал.

Mike_B

Цитата: Mike_B от 19 декабря 2018, 15:29Поехали дальше: как потом связи разорвать? И как после мне пути исправить, ведь лежать в итоге будет на юникс-машине этот ots? Далее, как сказать, чтобы макрос в нем стартовал сам (автозапуск) и как потом сохранить результат в xls?
Так, нашел: можно отредактировать внутри content.xml, там относительно текущего положения. Но проще будет положить в один каталог с данными. А связи: Правка - Связи - выделить все по Ctrl-A и разорвать связь. Получается, это нам и надо записать как макрос?

JohnSUN

Ну да, именно это и останется записать. Получается, что весь процесс импорта уже жёстко закодирован в самом файле. Нужно только выполнить что-то вроде этого
Sub CreateComplexBook
Const TEMPLATE_NAME = "path/path/filename.ots"
Const RESULT_NAME = "path/path/filename.xls"
Dim cDoc As Variant, oSheets As Variant, i As Long
GlobalScope.BasicLibraries.LoadLibrary("Tools")
If Not FileExists(TEMPLATE_NAME) Then Exit Sub
oDoc = OpenDocument(ConvertToURL(TEMPLATE_NAME), Array())
oSheets = oDoc.getSheets()
For i = 0 To oSheets.getCount()-1
oSheets.getByIndex(i).setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
Next i
oDoc.StoreAsURL(ConvertToURL(RESULT_NAME),Array())
oDoc.close(True)
End Sub
(Ха, в сто раз меньше кода получилось  ;D )
Эту процедуру не обязательно хранить в отдельном документе, можно положить в "Мои макросы". Кажется, в этом случае достаточно чего-то вроде
soffice -headless -norestore "macro:///Standard.Module1.CreateComplexBook"
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Mike_B

#12
Цитата: JohnSUN от 19 декабря 2018, 16:50Нужно только выполнить что-то вроде этого
Та-ак... Внес макрос в ots-файл, сохранил. Выполнил - появился xls-файл. Но эксель его не понимает. Заглянул внутрь - он не xls по сути, а ods. Если изменить расширение - в Либре открывается, все прекрасно, но как бы xls сделать? Что изменить в oDoc.StoreAsURL ?

Цитата: JohnSUN от 19 декабря 2018, 16:50Эту процедуру не обязательно хранить в отдельном документе, можно положить в "Мои макросы".
А если засунуть именно в ots-файл, как будет выглядеть строка запуска? У меня файл назван tab_s.ots, но, когда его открываешь, он зовется "Без имени 2" и макрос CreateComplexBook сидит там по адресу "Без имени 2 - Standard - Module1". Почему в файл хочу - чтобы потом переслать и могли положить на юникс-машину. Ведь "Мои макросы" останутся у меня на ПК.

А так да, строка запуска из "Мои макросы" работает. Шикарно! :) И кода действительно мало. Мне нравится!

Нашел решение: https://ask.libreoffice.org/en/question/75415/macro-save-as-xls/
Меняем на :

    Dim Args(0) As New com.sun.star.beans.PropertyValue
    Args(0).Name = "FilterName"
    Args(0).value = "MS Excel 97"

    oDoc.StoreAsURL(ConvertToURL(RESULT_NAME),Args())


Остался вопрос - как указать исполнять макрос из файла? Чтобы с ним вместе все передать?

JohnSUN

#13
Это не вопрос... Вот только смущает меня твоё желание и макрос, и "схему импорта" (все эти линки) в одной книге держать. Лучше уж два файла с одинаковыми именами, но расширениями ods и ots. "Мы с Тамарой ходим парой...". Тогда в макросе просто берешь ThisComponent.getURL(), меняешь предпоследнюю букву с "d" на "t" и имеешь имя шаблона.

А! Есть же еще вариант! Можешь макрос оформить в виде Расширения - отправишь им OTS (шаблон со связями) и OXT. Один раз установят и успокоятся.

Команда практически та же самая, но нужно указать имя книги, в которой макрос находится и путь к макросу станет чуть сложнее
soffice -headless -norestore путь/путь/книга_с_макросом.ods "macro://книга_с_макросом/Библиотека.Модуль.Процедура"Внимание! Все (честное слово - ВСЕ!) наступают на одни и те же грабли - или и в имени файла и в пути к макросу пишут "книга_с_макросом.ods", или наоборот - и там и там пишут просто "книга_с_макросом". Правильный синтаксис - для открываемой книги расширение нужно указывать, а для пути к макросу - не нужно.

У тебя Библиотека.Модуль.Процедура будет Standard.Module1.CreateComplexBook

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

PS. Надеюсь, ты уже понял, что не обязательно "редактировать внутри content.xml" - в том же Правка-Связи (в последних версиях "Ссылки на внешние файлы") можно для каждого линка задать правильный файл.
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

Mike_B

Цитата: JohnSUN от 19 декабря 2018, 18:27Это не вопрос... Вот только смущает меня твоё желание и макрос, и "схему импорта" (все эти линки) в одной книге держать.
А как мне тогда им передать? Ну покажу на своем компе, но ведь на рабочий надо не только ots со схемой импорта, но и макрос разрыва связей с сохранением тоже поместить? А когда несколько файлов - всегда риск, что не туда положат, забудут настроить, еще что. "Мы с Тамарой" - это хорошо, но "Omnia mea mecum porto" лучше. Когда писал скрипты на AutoIt, старался "все в себе" держать и извлекать по необходимости -- конфиг по умолчанию, картинку, вспомогательные вещи.

Цитата: JohnSUN от 19 декабря 2018, 18:27Лучше уж два файла с одинаковыми именами, но расширениями ods и ots.
То есть ods с макросом и ots как шаблон, макрос запускается из ods так же, как раньше? ThisComponent.getURL() мне нравится, в духе экселевого ThisWorkbook.Path

Цитата: JohnSUN от 19 декабря 2018, 18:27Есть же еще вариант! Можешь макрос оформить в виде Расширения - отправишь им OTS (шаблон со связями) и OXT.
А что такое OXT и с чем его едят? Что туда кладут?

Цитата: JohnSUN от 19 декабря 2018, 18:27Команда практически та же самая, но нужно указать имя книги, в которой макрос находится и путь к макросу станет чуть сложнее
Вот спасибо, я все записываю, серьезно. Такие вещи из головы не изобретешь, а где берут, не знаю. Если у Питоньяка - еще не всего прочитал. Перевод у меня от Дмитрия Чернова 2007-2008 и он, этот перевод, местами весьма корявый, а это не беллетристика, где можно пропустить описание природы...

Цитата: JohnSUN от 19 декабря 2018, 18:27После имени процедуры можно ещё и параметры в скобках задать - пути и имена шаблона и выходного файла, например. Ну и в описании процедуры эти значения перенести из констант в параметры.
По-моему, отличное решение! Надо будет с утра испробовать и, если работает, в дело применить.

Но это пути к данным и выходной, а все же, как лучше передать файл(-ы)? Схема понятно - она сидит в шаблоне ots, а код? Я человек в мире LO начинающий, поэтому с удовольствием послушаю советы опытных людей. Может, на пустом места проблему создаю по незнанию.