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

Автор aiva, 18 июля 2023, 16:13

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

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

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

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

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

aiva

Цитата: sokol92 от 18 июля 2023, 16:35лучше всего приведите свой "макрос из 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

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

economist

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

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

aiva

Цитата: economist от 18 июля 2023, 17:08Код из #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

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

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

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

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

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