Запрос по данным листа calc. Возможно ли?

Автор quarantine85, 26 февраля 2020, 15:45

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

quarantine85

Добрый день!

Подскажите, пожалуйста, возможно ли сделать SQL запрос к данными на странице в Calc?

Суть задачи:
Есть некая несортированная таблица на одном из листов (лист скрыт от пользователя, сортировать нельзя), в столбцах: даты, суммы, текстовые значения.

Необходимо макросом Basic получить, например, сортированный массив по датам или наименованиям.

Реально ли как то реализовать такое? Или есть какой то более грамотный вариант решить задачу?
Нужно реализовать без использования base.

economist

Реально, можно, и даже без макросов. И даже с возможностью интерактивно указывать, скажем, период выборки.

Но фраза "без использования Base" означает что вы отказываетесь от того что работает "искаропки" в пользу непонятного. Это не "более грамотный путь".

А с Base это делается за 5 минут:

Файл - Создать БД - Соединиться - Эл. таблица - Обзор - Сохранить

Запросы - Создать в режиме SQL - Ввести (см. ниже) - Сохранить "Запрос1"

SELECT * FROM НекийЛист GROUP BY Наименование, Дата

Теперь вы в Calc или Writer жмете Ctrl+Shift+F4 и перетаскиваете "Запрос1" в документ. Всё.   



Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

eeigor

#2
В макросе присвойте нужный диапазон массиву (одним действием зараз) и выполните сортировку массива. Но вы можете отсортировать исходные данные на листе в другом месте (это проще и быстрее) средствами самого Calc в макросе.
Зачем нужен SQL?
В Excel давно используют PowerQuery (язык "М"). Однако та самая надстройка (SQL Query, вроде) в Excel ещё жива, с интерфейсом, застрявшим в 90-х...
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

kompilainenn

Цитата: eeigor от  2 марта 2020, 21:38В Excel используют PowerQuery (язык "М"), а SQL - это "застрял" в 90-х...
Ну и где в LibreOffice этот самый (язык "М")?
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

eeigor

#4
Сортируйте на другом (временном) листе (или в другом свободном месте) средствами электронной таблицы... в макросе, если надо. Пример ниже от А.Питоньяка. Я новичок в LO Basic, но попробовал, всё получилось сразу. Excel намного легче, но я вынужденно перехожу на LO.
В примере - сортировка по трем столбцам.

Sub TestSorting()
 Dim oSheet
 oSheet = ThisComponent.Sheets(0)  'первый лист
 Call SortCustomers(oSheet,  6)  'кол-во строк в таблице вместе с заголовками столбцов
End Sub

' Sort the sheet for convenience
Sub SortCustomers(oSheet, iRows As Integer)
 Dim oRange
 Dim aSortFields(2) As new com.sun.star.util.SortField
 Dim aSortDesc(1) As new com.sun.star.beans.PropertyValue

 ' Sort options:
 ' (1) Customer's name
 ' (2) Customer's email (in case different persons have identical names)
 ' (3) Slot ID
 With aSortFields(0)
   .Field = 0
   .SortAscending = True
 End With
 With aSortFields(1)
   .Field = 1
   .SortAscending = True
 End With
 With aSortFields(2)
   .Field = 2
   .SortAscending = True
 End With
 
 With aSortDesc(0)
   .Name = "SortFields"
   .Value = aSortFields()
 End With
 With aSortDesc(1)
   .Name = "ContainsHeader"
   .Value = True
 End With

 oRange = oSheet.getCellRangeByPosition(0, 0, 4, iRows)
 oRange.sort(aSortDesc())
End Sub
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

economist

#5
Все эти способы равнобесполезны, т.к. мы не знаем что нужно ТС сделать с полученным отбором.

Но у Base+SQL все-таки шансов больше, т.к. это наиболее "натоптанный" функционал, насквозь пронзивший LO.

Язык M был очередным скачком MS "наудачу" в сторону от всех популярных технологий. Но, в отличие от VBA, - чуда не случилось.

SQL создавался как язык для экономистов-аналитиков. Но не стал им. Чаще всего сисадмины таскают им из огня каштаны (опрашивают всякие WMI/LDAP/AD). Другая крайность - трехстраничные портянки запросов в 1С8, из-за того что вместо 100 таблиц сделано... 8 тысяч таблиц. Вся прелесть SQL уничтожена необходимостью джойнить в среднем отчете не менее 4-5 таблиц.

M - оказался сильно косноязычным и поэтому им пользуются единицы. Сравните число роликов/семинаров/книг для М и для более понятных Pandas etc и вообще всевозможных ORM, реализовавших "однострочный" цепной SQL.

Там где в SQL нужно городить ворох WITH, CTE, подзапросов, объединений в одном листинге - Pandas даст результат в одну строку в блокноте Jupyter. И задачу ТС я в Pandas решу за 3 минуты (и в три строки).  
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

economist

Цитата: eeigor от  2 марта 2020, 22:16Сортируйте на листе средствами электронной таблицы... в макросе, если надо.

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

eeigor

#7
Сортировать надо в любом удобном месте (другом, главное - на листе и средствами листа). Макрос - выше. Предварительно скопировать данные из источника.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

quarantine85

Цитата: eeigor от  2 марта 2020, 22:16
Сортируйте на другом (временном) листе (или в другом свободном месте) средствами электронной таблицы... в макросе, если надо. Пример ниже от А.Питоньяка. Я новичок в LO Basic, но попробовал, всё получилось сразу. Excel намного легче, но я вынужденно перехожу на LO.
В примере - сортировка по трем столбцам.

Sub TestSorting()
  Dim oSheet
  oSheet = ThisComponent.Sheets(0)  'первый лист
  Call SortCustomers(oSheet,  6)  'кол-во строк в таблице вместе с заголовками столбцов
End Sub

' Sort the sheet for convenience
Sub SortCustomers(oSheet, iRows As Integer)
  Dim oRange
  Dim aSortFields(2) As new com.sun.star.util.SortField
  Dim aSortDesc(1) As new com.sun.star.beans.PropertyValue

  ' Sort options:
  ' (1) Customer's name
  ' (2) Customer's email (in case different persons have identical names)
  ' (3) Slot ID
  With aSortFields(0)
    .Field = 0
    .SortAscending = True
  End With
  With aSortFields(1)
    .Field = 1
    .SortAscending = True
  End With
  With aSortFields(2)
    .Field = 2
    .SortAscending = True
  End With
 
  With aSortDesc(0)
    .Name = "SortFields"
    .Value = aSortFields()
  End With
  With aSortDesc(1)
    .Name = "ContainsHeader"
    .Value = True
  End With

  oRange = oSheet.getCellRangeByPosition(0, 0, 4, iRows)
  oRange.sort(aSortDesc())
End Sub

Так и реализовал, спасибо )

quarantine85

Цитата: economist от  2 марта 2020, 22:51
Все эти способы равнобесполезны, т.к. мы не знаем что нужно ТС сделать с полученным отбором.

Но у Base+SQL все-таки шансов больше, т.к. это наиболее "натоптанный" функционал, насквозь пронзивший LO.

Язык M был очередным скачком MS "наудачу" в сторону от всех популярных технологий. Но, в отличие от VBA, - чуда не случилось.

SQL создавался как язык для экономистов-аналитиков. Но не стал им. Чаще всего сисадмины таскают им из огня каштаны (опрашивают всякие WMI/LDAP/AD). Другая крайность - трехстраничные портянки запросов в 1С8, из-за того что вместо 100 таблиц сделано... 8 тысяч таблиц. Вся прелесть SQL уничтожена необходимостью джойнить в среднем отчете не менее 4-5 таблиц.

M - оказался сильно косноязычным и поэтому им пользуются единицы. Сравните число роликов/семинаров/книг для М и для более понятных Pandas etc и вообще всевозможных ORM, реализовавших "однострочный" цепной SQL.

Там где в SQL нужно городить ворох WITH, CTE, подзапросов, объединений в одном листинге - Pandas даст результат в одну строку в блокноте Jupyter. И задачу ТС я в Pandas решу за 3 минуты (и в три строки). 

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

Лист1:
Дата, Сумма, Текстовое значение, ID

Лист2:
ID, Наименование

Цель получить результат на Листе3:
Дата, Сумма, Текстовое значение, Наименование

Потому и спросил про SQL.

economist

Цитата: quarantine85 от 15 апреля 2020, 14:13Потому и спросил про SQL.
См. #1, там описан именно этот сценарий. Объединить данные можно:
1) SELECT ... LEFT OUTER JOIN FROM Лист1, Лист2  (т.е. объединением) 
2) SELECT ... (SELECT From Лист2) FROM Лист1 (т.е. вложенным запросом)

Если же задача "просто увидеть Наименования с Лист2" - можно не лезть никуда и просто написать формулу вида:

=ВПР(ID, Лист2, Наименование, ЛОЖЬ)

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