Calc + dll + Base

[SobyDa]

У А.Питоньяка есть отдельная книга, посвященная Base. Эта книга имеется в виду?

Нет, про неё не знал. Благодарю!

Не сочтите за наглость. Нет ли рабочих примеров по импорту данных (структурированных) в calc из txt или csv?

[sokol92]

Кратко загрузка и выгрузка текстовых файлов описана в разделе "12.4.6. Loading and saving documents" книги А.Питоньяка OOME_4_0.odt.
Опции соответствующих фильтров описаны здесь, "раздел Filter Options for the CSV Filter".

Примеры применения можно найти по контексту "csv" как на нашем форуме, так и на "международном" https://ask.libreoffice.org/.
Можно csv-файлы импортировать непосредственно на лист файла Calc с помощью метода AreaLinks.insertAtPosition.

[SobyDa]

Чётко, ясно, быстро, исчерпывающе!
Благодарю всех за ответы!

[economist]

Нет ли рабочих примеров по импорту данных (структурированных) в calc из txt или csv?

Штатный механизм "линковки" readonly-файлов в Calc тоже рабочий:
Лист - Вставить лист из файла
Лист - Связь с внешними данными

При импорте CSV макросом - стоит иметь ввиду, что если CSV хоть в чем-то "кривой", например там кривые, не ISO, даты, смесь десятичных разделителей тчк/зпт и тп - важно избежать соблазна довести данные до кондиции формулами Calc. Он умеет, кстати, в CSV хранить даже формулы и вычислять их при открытии, для простых случаев бывает удобно. Но для сложных...

Лучше создать файл-пустышку Base, *.ODB - с подключением с CSV/TXT. Плюсы:
- данные и логика хранятся отдельно;
- в Base доступен язык SQL и создание view/представлений. Большинство конвертаций данных его силами - быстры;
- есть "мало-пользовательский" доступ к данным на чтение по локальной сети;
- данные из ODB легко "затянуть" в любое приложение OpenOffice|LibreOffice по нажатию Ctrl+Shift+F4. Этот способ - удобная "витрина данных" в компании, всё под рукой, разложено по таблицам/запросам/view. Ничего подобного у MS Office нет в плане простоты и удобства. Такие данные - "неубиваемы", они само-восстанавливаются при открытии файла, что позволяет создать комфортную среду для работы;
- макросом тоже можно очень быстро получить данные из ODB (а значит не только из CSV, но и вообще из любой промышленной базы данных). На Форуме см. примеры по строке поиска doImport. У диапазона базы данных - свой Автофильтр, свой Стиль, нет и намека на торможение. Изменив всего лишь одной свойство (SQL-запрос) этого диапазона - мы получим новый набор, или версию старого, например с сортировкой. Если это делать силами Calc - будет намного дольше.     

Наконец, для совсем "больших данных" (миллионы строк CSV), которые должны агрегироваться в сотни-тысячи в Calc - можно задействовать мощь библиотеки Pandas, примеры есть на Форуме. В ней быстро работают импорты до десятка миллионов строк, а сам программный код - легок для понимания. Кол-во примеров кода в Сети - огромно. Вот типовой пример получения сводных данных из "сырых" проводок 1С для отчета по кредиторской задолженности, техника SAC - Split, Apply, Combine во всей красе, "в одну строку" (ну почти):

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

# работает в LibreOffice Python, макрос можно внедрить в ODS/ODB/ODT-файл ч/з расширение APSO
import pandas as pd
df = pd.read_csv('D:/Проводки.txt', sep=';') # считали 1 млн строк за 5 секунд
# Получим и сохраним список Top-100 кредиторов, за 1 секунду
df.query(" кт==60 ").groupby('субконто1').sum().sort_values('сумма',
ascending=False).head(100).to_excel('D:/Отчет.ods')

Замечу что CSV формат - не типизирован, есть более эффективные форматы для хранения данных в Pandas - PKL, Apache Arrow итд. Они обеспечивают в 10Х меньший размер при в 5X более быстром чтении и записи. Такое ускорение позволяет получать любой отчет/результат в реальном времени или с задержкой до секунды. Calc-у в этом случае остается почетная миссия форматирования готовых табличных данных, экспорт в PDF итд.

[SobyDa]

А вот за это отдельная благодарность!
Т.е. получается, что лучше данные изначально забирать в odb базу и уже там с ними выполнять всякое?
А после получения итоговых значений, вписывать в табличку CAlc? Или интерфейс самой Base развит достаточно, чтобы в ней делать графики, нативные таблицы и выгрузки в виде отдельных запароленных таблиц формата Excel?

[eeigor]

Вот вам примерный код для закачки макросом таблиц из Base методом doImport.
Источник REGISTERED_DATABASE должен быть создан заранее.
В примере подтягиваются все таблицы, каждая на отдельный лист.

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

REM  *****  BASIC  *****

Option Explicit
Rem Replace with your actual data source name (registered database).
Const REGISTERED_DATABASE$ = "data"  'registered name of database link


Sub ImportDatabaseTables(Optional bBreakLink As Boolean)
''' Imports data tables from an external database (registered data source) into a new sheet.
'''
''' Argument:
''' BreakLink (Optional, False is default if missing)
''' If True, breaks the link with the database range.
''' Based on the code written by @Villeroy.

On Local Error GoTo HandleErrors
Dim oDBCtx As Object, oDataSource As Object, oConnection As Object
Dim aTableNames$()
Dim oDoc As Object, oSheets As Object, oDBRanges As Object, oDBRange As Object
Dim oCellRange As Object, oAddr As Object  'New com.sun.star.table.CellRangeAddress
Dim sTableName$, sDBRangeName$
Dim i%

If IsMissing(bBreakLink) Then bBreakLink = False

oDBCtx = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDBCtx.getByName(REGISTERED_DATABASE)
oConnection = oDataSource.getConnection("", "")  'attempts to establish a database connection
' NOTE: .getConnection("user", "password") for a real database log-in.
aTableNames() = oConnection.Tables.ElementNames

oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_default", 0, Array())
oSheets = oDoc.Sheets
oDBRanges = oDoc.DatabaseRanges
' Add additional sheets as needed according to the number of tables in oDataSource.
For i = oSheets.Count To UBound(aTableNames)
oSheets.insertNewByName("Sheet" & i + 1, i + 1)
Next

Dim aImportDsc(3) As New com.sun.star.beans.PropertyValue  'import descriptor
' Import source tables.
For i = 0 To UBound(aTableNames)
sTableName = aTableNames(i)
sDBRangeName = "Import" & i + 1
oSheets(i).Name = sTableName

oAddr = createUnoStruct("com.sun.star.table.CellRangeAddress")
oAddr.Sheet = i
' If Not oDBRanges.hasByName(sDBRangeName) Then
oDBRanges.addNewByName(sDBRangeName, oAddr)
' End If
Rem oDBRange = oDBRanges(sDBRangeName)  '#Err!
oDBRange = oDBRanges.getByName(sDBRangeName)  'oDBRanges(i)
With oDBRange
' .ContainsHeader = True  'Contains column labels (default is True)
' .TotalsRow = False  'Contains totals row (default is False)
.MoveCells = True  'Insert or delete cells (default is False)
.KeepFormats = True 'Keep formatting (default is False)
' .StripData  = False  'Don't save imported data (default is False)

' .RefreshPeriod = 0  'specifies the time between two refresh actions in seconds
End With
oCellRange = oDBRange.ReferredCells

' Set the arguments for an import.
aImportDsc(0).Name = "DatabaseName"  'registered name of database link
aImportDsc(0).Value = REGISTERED_DATABASE
aImportDsc(1).Name = "SourceType"
aImportDsc(1).Value = com.sun.star.sheet.DataImportMode.TABLE  '2=TABLE
' Set the source table.
aImportDsc(2).Name = "SourceObject"
aImportDsc(2).Value = sTableName
Rem aImportDsc(3).Name = "IsNative"  'specifies whether the SQL statement is given...
Rem aImportDsc(3).Value = False      '...directly to the database or is parsed before

oCellRange.doImport(aImportDsc)  'imports data from an external database

' Break the link with the database range, if requested.
If bBreakLink Then oDBRanges.removeByName(sDBRangeName)
Next
oConnection.close()
Exit Sub

HandleErrors:
Msgbox "#" & Err & ": " & Error _
, MB_ICONSTOP, "macro:ImportDatabaseTables"
End Sub  'ImportDatabaseTables

''' Not used.
Function GetImportDescriptor(sRegisteredDatabase$, nSrcType&, sSrcObject$ _
 , Optional IsNative As Boolean) As Object

''' Remarks:
''' string <ConnectionResource> indicates a connection URL,
''' which locates a database driver.
''' ☛ https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1DatabaseImportDescriptor.html#a7579089cbb59e1181309d7dfc6ffa557
''' ⁈ Currently not used as part of an import descriptor, but the name
''' of the registered data source is passed in place of the database name.
'''
Dim a(3) As New com.sun.star.beans.PropertyValue
a(0).Name = "DatabaseName"  'registered name of database link
a(0).Value = sRegisteredDatabase
a(1).Name = "SourceType"  'NONE|SQL|TABLE|QUERY
a(1).Value = nSrcType  'com.sun.star.sheet.DataImportMode
a(2).Name = "SourceObject"
a(2).Value = sSrcObject
If IsMissing(IsNative) Then IsNative = False
Rem a(3).Name = "IsNative"  'specifies whether the SQL statement is given...
Rem a(3).Value = IsNative   '...directly to the database or is parsed before

GetImportDescriptor = a
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub RefreshAllDBRanges()
Dim oDBRangesEnum As Object, oDBRange As Object

oDBRangesEnum = ThisComponent.DatabaseRanges.createEnumeration()
Do While oDBRangesEnum.hasMoreElements()
oDBRange = oDBRangesEnum.nextElement()
oDBRange.refresh()
Loop

' For Each oDBRange In ThisComponent.DatabaseRanges
' oDBRange.refresh()
' Next
End Sub
Примечание. Вместо параметра DatabaseName при импорте в действительности в настоящее время используется имя зарегистрированного источника БД.

[economist]

лучше данные изначально забирать в odb базу и уже там с ними выполнять всякое?

Лучше 100%-но, если данных много. БД хороша тем что в ней есть SQL, который работает быстро и внятно. В Calc Автофильтром можно тоже что угодно "выбрать" на "посмотреть", но повторно его использовать - сложно. По результатам моего мониторинга - 9 из 30 бухгалтеров около часа в день тратили на Автофильтр, на его снос и возврат к 2-3 сочетаниям условий. SQL-запрос решает эту проблему.

Научить пользователей писать запрос SELECT * FROM ... WHERE ... оказалось достаточно просто, нужно было всего лишь никого не слушать и поверить в персонал. Оказалось что достаточно подсунуть пользователям ДВА правила автозамены для PuntoSwithcer или карамбы (печатаем 3 буквы и получаем готовое рабочее SELECT-выражение - для выборки строки или получения группировки, т.е. готового отчета):

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

sel    -> SELECT * FROM ПРОВ WHERE Д=10 AND К=60 WHERE СУБД1 LIKE '%ЦЕМЕНТ%'
selsel -> SELECT *, SUM(СУММА), SUM(КОЛВО) FROM ПРОВ WHERE Д=10 AND К=60 GROUPBY СУБД1, СУБК1, СУБК2

Результат: через месяц - треть любителей Автофильтра освоила SELECT. Правда такой приятный синтаксис, без кавычек, дают не все движки RBDMS, а только самая быстрая: SQLite. Она даже установки в LO не требует (нужно лишь скопировать 3 файла).

С другими движками (HSQLDB, PostgreSQL, MySQL, dbf, Text/CSV) - нужна обкавычка полей. Но даже к ней привыкнуть можно, она меньше достает чем "ненаглядный" Автофильтр.

[economist]

после получения итоговых значений, вписывать в табличку CAlc? Или интерфейс самой Base развит достаточно, чтобы в ней делать графики, нативные таблицы и выгрузки в виде отдельных запароленных таблиц формата Excel?

SQL позволяет соединить данные разных таблиц, конвертировать форматы и мн. другое. В целом он более быстр чем ворох функций в Calc =ВПР()/LOOKUP(), =ЗНАЧЕН() итд. А главное - SQL умеет работать с пустотами в данных, из-за которых в сложных таблицах Calc можно увидеть до половины функций =ЕСЛИ(ЕОШИБКА(...)...), ЕНД() итд.
 
Но в Base нет средств форматирования, а "Отчеты" в Basе (стандартные, плюс два Расширения для отчетов с офсайтов) - это объекты/документы Writer, в которых все хорошо с текстом, благодаря Стилям, но с таблицами - не очень.

У меня были похожие на вашу задачи. Множество SELECT-запросов я вытащил в Calc (*.ODS) и обновляю их авто или макросом. Плюс такой связки - наличие сразу двух "автофильтров" поверх: один на уровне диапазона БД, второй - обычный. Но обновление диапазона БД - сносит ручное форматирование столбцов, поэтому не обойтись без макросов. Они же делают экспорт в запароленные XLSM (прямо ваш кейс).

Радует что "форматирующие" записанные рекордером макросы на VBA в Excel (параметры страницы, печати, формат ячеек итп фигня) - почти 100% работают в Calc. И это огромное подспорье при переезде. Ведь переписать их на чистом LO Basic с его сложным API - та еще задачка. Там неизбежно красноглазие и кратно большие трудозатраты. Лично я с ней не справился и продолжаю использовать большие "макрорекордерные" куски VBA в Calc. Они за 10 лет полета отваливались от силы пару раз, и потом, благодаря разрабам, эти регрессии чинили. А пока чинили - все сидели на portable-версии. У меня их на компьютере больше десятка, они друг другу не мешают даже открытые, с опцией SingleAppInstance=false в файле ...\LibreOfficePortable\App\AppInfo\Launcher\LibreOfficePortable.ini

На будущее - уже сейчас есть свободные альтернативы Calc/Excel-отчетам, которые лучше во всем: от скорости работы и разработки - до web-раздачи. Они не имеют никаких ограничений. Это DS/ML-инструменты. В их основе - медленный Python, который нагло использует чужие быстрые либы на С++, экосистему JS, Fortran итд.

О том насколько это "другая планета" - можно судить по анимированной слайд-диаграмме: https://vizzuhq.github.io/ipyvizzu-story/examples/complex/complex.html Ничего красивее и нагляднее в смысле отчетов я не видел (разверните на весь экран, жмите стрелки на клаве, если не началась анимация). Вот, имхо, к чему нужно стремиться.