Простейший макрос для экселя и абсолютное непонимание в OO

[aiva]

Здравствуйте, уважаемые. Третьи сутки бьюсь над элементарным макросом, который в экселе пишется из 5 строк, а в ОО я просто не могу решить эту проблему.
Есть документ Calc со множеством листов. Нужен макрос, который по срабатыванию будет копировать только данные (числа и форматы ячеек) с определенного диапазона в буфер обмена с неактивного листа без переключения фокуса с активного листа. К примеру работая на листе "Журнал" необходимо по срабатыванию макроса скопировать в буфер обмена значения и форматы ячеек  с листа "в_отчет", оставаясь при этом на активном листе "журнал". Копирование данных на другой лист или даже в другой документ через getDataArray()/setDataArray() я освоил. А вот с копированием в буфер обмена - полное фиаско.
Иностранные форумы пока результатов не дали, ChatGPT не особо умеет в StarBasic и десятки вариантов кода просто не работают. На данное время добился только такого варианта:

Код Выделить Развернуть

Sub copy2clipboard()
 Dim oDoc As Object
    Dim oSheet As Object
    Dim oSourceRange As Object
    Dim oDispatcher As Object

    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("for_reports")
    oSourceRange = oSheet.getCellRangeByName("B2:AS270")

    'oDoc.CurrentController.Select(oSourceRange)

    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDispatcher.executeDispatch(oDoc.CurrentController.Frame, ".uno:Copy", "", 0, Array())
    thiscomponent.currentcontroller.setActiveSheet Thiscomponent.sheets.getbyname("balance")
End SubНо это костыли. Иногда проскакивают переключения активных листов, особенно если комп медленный, а документ большой. Вдобавок ко всему копируется и оформление ячеек, и формулы. Понятно, что можно вставить "значения и форматы", но вот с разноцветными ячейками такое не прокатывает. Неужели нет никакой альтернативы.
Помогите пожалуйста.

[sokol92]

Для использования в какой программе предназначены данные, которые копируются в буфер обмена?
Если в LibreOffice, то можно использовать методы интерфейса XTransferableSupplier. Это не приводит к изменению системного буфера обмена.
Примеров в сети много.

[economist]

Можно проще, без буфера и диспетчера:

Код Выделить Развернуть

Option VBASupport 1

Sub Main
Sheets("Отчет").[A1:A3] = Sheets("Журнал").[A1:A3]
End Sub

PS С буфером все усложняется. Может стоит экспортировать данные в TXT/CSV-файл для другого приложения? Или речь все-таки о внутри-Calc-овских движняках?

[sokol92]

@aiva, лучше всего приведите свой "макрос из 5 строк" для VBA, а мы постараемcя найти эквивалент для LO Basic.

[aiva]

Для использования в какой программе предназначены данные, которые копируются в буфер обмена?
Если в LibreOffice, то можно использовать методы интерфейса XTransferableSupplier. Это не приводит к изменению системного буфера обмена.
Примеров в сети много.

Да, вставлять надо в другой документ Calc, причем каждый раз в разные места, иначе использовал бы SetArray.
Сейчас объясню поподробнее. Есть большой документ где куча листов "журнал", "отчет" и тд, и есть отдельный документ, назовем его "промежуточные итоги" к примеру. И вот заполняя "журнал" мне надо в определенный момент жмакнуть кнопку макроса, чтобы он взял данные с листа "отчет" с конкретного диапазона ячеек (только форматы и значения, без всяких цветовых маркировок(девочки и пенсионеры тоже работают)) и закинул их в буфер обмена. Чтобы вставить их куда надо (в неопределенное место, тк количество строк всегда разное) в отдельный документ "промежуточные итоги".
Если это можно реализовать внутренними средставами LO через TransferableSupplier, предложите решение. 99% что второй документ - тоже таблица Calc

[aiva]

лучше всего приведите свой "макрос из 5 строк" для VBA, а мы постараемcя найти эквивалент для LO Basic.

Код Выделить Развернуть

Dim abook As Workbook
    abook.Activate
    book.Worksheets("report").Activate
    Range("C72:AR224").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    book.Activate
    book.Worksheets("balance").Activate
    Range("A1").SelectВообще абсолютно элементарный макрос. Вот прям простой и рабочий как топор. И при вставке там же в MSO не копируются цветовые разметки ячеек. Только значения и только форматы чисел.
А здесь какая то фигня выходит

[sokol92]

Метод Range.Copy(при отсутствии параметра) копирует всю информацию о ячейках в буфер обмена, включая, естественно, формулы, условное форматирование, данные для проверки ячеек...
Что Вы называете "цветовой разметкой ячеек"?

[aiva]

Метод Range.Copy(при отсутствии параметра) копирует всю информацию о ячейках в буфер обмена, включая, естественно, формулы, условное форматирование, данные для проверки ячеек...
Что Вы называете "цветовой разметкой ячеек"?

разноцветные ячейки, условное форматирование используется по полной. если 0 то ячейка красненькая и тд.

[economist]

Код из #2 одной строкой копирует только значения, без цвета, формул, усл. форматирования.

Судя по коду из #5 условия задачи до сих пор озвучены не все: копировать нужно только видимые ячейки, без скрытых вручную и авто-отфильтрованных? 

[aiva]

Код из #2 одной строкой копирует только значения, без цвета, формул, усл. форматирования.

Судя по коду из #5 условия задачи до сих пор озвучены не все: копировать нужно только видимые ячейки, без скрытых вручную и авто-отфильтрованных? 

Именно так. Я попытался понять методы интерфейса XTransferableSupplier но... похоже я слишком неумный.
Проще уже пальцем ткнуть в итоговую реализацию и сказать "делай так", а я попытаюсь разобраться

[sokol92]

Похоже, что проблема со специальной вставкой ячеек (первое упоминание в этой теме).

В Excel при использовании специальной вставки ячеек есть опции "формулы и форматы чисел" и "значения и форматы чисел". То есть, из всех опций форматирования ячейки переносится только числовой формат (NumberFormat).
В Calc при специальной вставке есть "большая" кнопка "Значения и форматы", но при этом вставляются форматы ячеек, а не только числовые форматы.

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

[aiva]

всем известный ChatGPT предолжил мне вариант с временным листом. вроде работает, но тоже как-то медленновато.

Код Выделить Развернуть

Sub CopyCellsToClipboard()
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oSourceRange As Object
    Dim oTempSheet As Object
    Dim oTempRange As Object
    Dim oDispatcher As Object

    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("Отчет")
    oSourceRange = oSheet.getCellRangeByName("B2:AS270")

    ' Создание временного листа
    oTempSheet = oDoc.createInstance("com.sun.star.sheet.Spreadsheet")
    oDoc.Sheets.insertByName("TempSheet", oTempSheet)
    oTempSheet = oDoc.Sheets.getByName("TempSheet")

    ' Копирование исходного диапазона на временный лист
    oTempRange = oTempSheet.getCellRangeByName("B2:AS270")
    oTempRange.setDataArray(oSourceRange.getDataArray())

    ' Замена всех формул их значениями
    oTempRange.FormulaArray = oTempRange.DataArray

    ' Выбор временного диапазона и копирование его в буфер обмена
    oDoc.CurrentController.Select(oTempRange)

    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDispatcher.executeDispatch(oDoc.CurrentController.Frame, ".uno:Copy", "", 0, Array())

    ' Удаление временного листа
    oDoc.Sheets.removeByName("TempSheet")
End Sub

[aiva]

В итоге пока вообще сделал так:
Создал отдельный лист - точную копию листа "отчет" - "отчет2", но без условного форматирования, без цветных шашечек и прочей ерунды.
Расставил на каждой ячейке нужное ей форматирование. (где-то числа, где-то время, где-то текст)
В каждую ячейку прописал формулу абсолютного соответствия ячейке основного листа "отчет"
И пользуюсь своим старым макросом (из первого поста) для копирования данных в буфер.
В нужный документ уже вставляю по CTRL+SHIFT+V - значения и форматы.
Хотел макрос для быстрой вставки по сочетанию клавиши. - оно мне вставляет значения без форматов, хотя все нужные параметры прописаны. Приходтся вставлять по CTRL+SHIFT+V... логика?
вот код для вставки

Код Выделить Развернуть

sub Macro1
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "SVDT"
args2(1).Name = "FormulaCommand"
args2(1).Value = 0
args2(2).Name = "SkipEmptyCells"
args2(2).Value = false
args2(3).Name = "Transpose"
args2(3).Value = false
args2(4).Name = "AsLink"
args2(4).Value = false
args2(5).Name = "MoveMode"
args2(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args2())


end sub

[economist]

вставляет значения без форматов, хотя все нужные параметры прописаны. Приходтся вставлять по CTRL+SHIFT+V... логика?

Видимо вставляется с форматом по умолчанию (стилем), а он без цветов, потому цвета и "сбрасываются". Вставка только значений есть в книжках Питоньяка.

То к чему вы пришли (два листа-отчета: один без форматов, а второй - с цветами) - популярное и рабочее решение. Это позволит уйти от CTRL+SHIFT+V и команд диспетчера и не полагаться на пряморукость пользователя в части выделения областей и фильтрации.

Если критерии автофильтров "стандартны" - их можно реализовать раскрывающимся списком вверху листа в Стандартный фильтр (и вы легко можете получить сам критерий). Для Автофильтра есть "План Б", считать или отобразить макросом критерия фильтра прямо в ячейке, сделать на него ссылку с "отчета" на "базу". И тогда можно отфильтровать значение на месте (в отчете), простым записанным макросом.

Если в отчете что-то агрегируется - возможно Сводная таблица (конструктором или даже формулой) может оказаться более удобным решением.