setDataArray(), setFormulaArray() и формат ячеек Calc

Автор ost, 4 мая 2021, 19:02

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

ost

Доброго.
Подскажите, пож., как вставить данные "оптом" в лист Calc с учетом форматов ячеек.

Получаю макросом массив, пригодный для вставки в лист Calc, содержащий данные из таблицы SQLite.
Все данные массива, в том числе даты (например "2021-04-21") и строковые данные, похожие на число (например, "000123"), в таком массиве имеют тип "Variant/String".

При вставке такого массива в лист Calc методом setDataArray() всем ячейкам вставляемого диапазона присваивается текстовый формат("@"). Форматы ячеек, существовавшие до вставки, теряются.
Неправильность: даты вставлены как текст.
Правильность: строковые данные, похожие на текст (например, "000123"), корректно вставлены с ведущими нулями.

При вставке такого массива в лист Calc методом setFormulaArray() форматы ячеек вставляемого диапазона не теряются. Вроде бы все хорошо, но строковые данные, похожие на число,
вставляются как число (без ведущих нулей)
Т.е. теперь
Неправильность: строковые данные, похожие на число (например, "000123"), не корректны  - теряются, например ведущие нули или если такое строковое "число" длиннее 16 разрядов, то в лист Calc вставляется число в экспоненциальном формате, не смотря на то, что формат ячейки остается текстовым ("@").
Правильность: корректная вставка всех остальных данных, кроме описанных выше.

Можно ли и как решить? Корректировать массив до его вставки в лист по setFormulaArray(), снабжая нужные элементы апострофом, не хочется. Как иначе - не пойму.




sokol92

Непустая ячейка в Calc, не содержащая формулу, имеет два типа (базовых) значений: число и текст. Соответственно, дата в ячейке - это число плюс соответствующий формат ячейки (в Excel так же).
В описании метода setDataArray отмечено, что элементы массива должны иметь тип Double или String. Если элемент массива имеет тип Date, то возникает ошибка.
Соответственно, в Вашем макросе даты можно формировать как Double, соответствующим столбцам нужно присвоить подходящий формат даты.
Владимир.

ost

#2
Цитата: sokol92 от  4 мая 2021, 19:42даты можно формировать как Double
Видимо, потому что SQLite хранит все данные как текст, я получаю по SQL-запросу данные в том числе и даты именно в текстовом формате.

Сейчас для формирования массива, пригодного для вставки в лист по setFormulaArray/setFormulaArray, у меня написана функция, в которой обходятся все элементы рекордсета
и укладываются в массив. Чтобы установить тип элемента массива в соответствии с хранимым значением нужно анализировать эти значения в ходе разбора содержимого рекордсета в этой функции. Чего хотелось бы избежать.

Массив для вставки в лист Calc выглядит так https://yadi.sk/i/kd5y0UhdFOVL1g
Предварительно, до вставки массива в лист ячейки отформатированы правильно, ячейки для вставки даты имеют формат даты, ячейки для вставки строк, похожих на число,  - формат текста и тд.

Результат вставки в лист по setFormulaArray() выглядит так:
-дата https://yadi.sk/i/yLXuPD6LcMpalg (хорошо: формат даты сохранился)
-строка, похожая на число https://yadi.sk/i/ed2AgFbkK-5OqA (плохо: строка вставлена как число не смотря на присвоенный ячейке ранее [и сохранившийся после вставки] формат "текст").
Использую setFormulaArray() вместо setDataArray() потому, что setFormulaArray() не изменяет форматы, присвоенные ячейкам ранее (до вставки массива в лист).

Напротив, setDataArray()  - меняет форматы.
Результат вставки в лист по setDataArray() выглядит так:
-дата https://yadi.sk/i/kDjhV7Qd-ietXQ (плохо: прежний формат ячейки заменен в результате вставки на формат "текст"), если затем сменить формат ячейки на "Дата" то, получим апостроф в ячейке https://yadi.sk/i/L_i7IsEH3JVahw (плохо)
-строка, похожая на число https://yadi.sk/i/-OzW5Jg5tiVdSQ (хорошо: просто потому, что ранее присвоенный ячейке формат изменен в результате вставки на этот же [текстовый] формат).

Ошибок при вставке в любом случае не возникает, т.к. тип элементов массива - String.






economist

#3
Затащить данные с SQLite (да и любой другой СУБД) в Calc можно 5-ю способами:
- через DataBaseRange (Ctrl+Shift+F4, а потом через Обновить или при открытии файла по запросу)
- методом doImport (макрос)
- методом setDataArray() (макрос)
- перебирая записи в ResultSet (макрос)
- перебирая записи в RowSet (макрос)

Чтобы затащить человекочитаемые даты SQLite  как даты в Calc, нужно чтобы:
1) В SQLite поле с ними имело тип DATE (по сути это тип TEXT, но с намеком для драйвера)
2) В SQLite дата была в ISO-формате - YYYY-MM-DD и никаком другом! Это, кстати, позволяет использовать тонну неимоверно быстрых функций для сложных расчетов с датами, так что нет никаких оснований не полюбить формат ГГГГ-ММ-ДД как родной. Подробнее тут: https://www.sqlite.org/lang_datefunc.html  

После загрузки любыми способом - получим из "даты" SQLite 2021-05-04 в Calc уже настоящую дату 04.05.21, выровненную вправо, как и число 44212, которое реально внутри ячейки и содержится. Все ненастоящие/кривые даты или заданные строкой другого формата - окажутся пустыми, что идеально соответствует логике.

Если в Calc для ячейки задать другой формат даты, например 04.05.2021 или текстовый - он применится, но всё равно сбросится на короткий формат даты 04.05.21 при первом же обновлении (такое поведение в 99% устроит всех, надеюсь).
Проверено на JDBC драйвере http://www.ch-werner.de/javasqlite/

PS: Если нужна дата со временем, то тип в SQLite - DATETTIME, а значение вида 04.05.2021 23:59:30
В Calc оно загрузится и отобразится в кратком формате времени: 04.05.21 23:59 Это уже не так здорово, но главное - в ячейке есть и секунды, просто дефолтный формат - без секунд. Полученные ячейки с датой/временем полностью готовы для матвычислений в Calc (разницы дат, функций итп).    
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

sokol92

Цитата: ost от  4 мая 2021, 20:50у меня написана функция, в которой обходятся все элементы рекордсета
и укладываются в массив
В момент обхода преобразуйте тексты, представляющие даты, в double с помощью конструкции:
Cdbl(Cdate(txt))
Владимир.

economist

Calc - почти идеальная "морда" для БД, если нужно много прочитать, кое-что посчитать и чутка записать обратно. При построении связок СУБД - Base - Calc часто всплывает проблема форматов, как у ТС.

Как я уже написал выше - даты в СУБД должны быть истинными датами. И формат ГГГГ-ММ-ДД - самый универсальный для SQLite/MySQL/PostgreSQL. Функции для работы с датами нужны часто, и хорошо если аргументы их будут готовыми, а не крошевом из обрывком типа SUBSTR(...)||SUBSTR(...)||SUBSTR(...)

Если число выводимых в Calc значений (строк*столбцов) более 1000 - уже стоит подумать о методе doImport - он заметно быстрее чем перебор rowset. А если строк десятки-сотни тысяч - то без вариантов, только он да setDataArray().

doImport(), setDataArray() и автообновление при открытии/по требованию DataBaseRange диапазона вида "Импорт1" - сбрасывает любое сделанное вручную форматирование, но зато он же обеспечивает получение чисел, дат, времени, строк, строк-псевдочисел с ведущими нулями (таб/инв-номера, ИНН/КПП итд) - именно в "родном" формате БД, так, как скорее всего и нужно. И быстрее всего. Любое не-число или не-дата - просто окажутся пустыми, это - мощнейший автобарьер для опечаток. Многие даже не представляют насколько корпоративные БД типа 1С загажены краевыми пробелами, 000 вместо ООО и смесью латиницы с кириллицей в ФИО, названиях итп. Нам, датасатанистам, приходится ежемесячно добавлять в протоколы чистки данных все новые извращения пользователей. Так вот, в Calc вы многие из этих ошибок увидите сразу, либо вычислите простой формулой.    
 
Если же формат не тот - можно парой строк макроса "накатить" сверху нужный. Или (что очень просто) - изменить его формулой/функцией "сбоку", а сам "Импорт1" можно вообще скрыть (столбцы).

Кстати, Calc умеет сам, кмк, очень удачно раскрашивать значения по их типу (Alt+F12 - Calc - Вид - Выделение значений цветом), иногда для интерфейсов - это всё, что нужно для фирменного лука :-)
- Текст - чёрный
- Формулы - зелёный (формулы могут быть ниже и/или правее диапазона "Импорт1" и они будут автозаполняться при изменении его размеров, что тоже удобно)
- Число/Дата/Время - темно-синий
- Защищённые яч. - светло-серый фон (темноват, но все эти цвета настраиваются в Сервис - Параметры - LibreOffice - Внешний вид). Так можно подчеркнуть те ячейки, которые можно менять в БД из интерфейса Calc. Защиту перед обновлением придется снять, потом вернуть (макросом). Помимо "красоты" - это очень надежный визуальный барьер для борьбы с опечатками. Если кто-то случайно всандолит краевой пробел в число, не ту десятичную ,. - Calc "почернеет" и проблема решится её создателем.  

Одним словом, Calc на удивление удобен при работе с большими БД. В MS Excel - то же самое, только хуже, а от утилиты MSquery32.exe так и веет 1997-м, и глючная она - до ужаса.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

ost

#6
Цитата: sokol92 от  5 мая 2021, 12:46В момент обхода преобразуйте тексты
Видимо, так и сделаю. Буду передавать в функцию кроме rowset'а, соответствующие его столбцам имена полей нужной таблицы БД отдельным массивом. Останется проанализировать текст полей и решить где применять Double.
Спасибо.

ost

Цитата: economist от  5 мая 2021, 14:52Calc - почти идеальная "морда" для БД,
Согласен. Уже был опыт подобного использования Calc. Понравилось.
Цитата: economist от  5 мая 2021, 14:52даты в СУБД должны быть истинными датами. И формат ГГГГ-ММ-ДД - самый универсальный для SQLite/MySQL/PostgreSQL. Фун
Даты в SQLite у меня хранятся именно так.
Идею с раскраской попробую.
Спасибо за развернутые концептуальные ответы.

economist

#8
Цитата: ost от  5 мая 2021, 18:01ГГГГ-ММ-ДД - Даты в SQLite у меня хранятся именно так.

- этого недостаточно для того чтобы дату в БД 2021-05-01 - Calc показал в ячейке как дату 01.05.21, ведь нужно чтобы тип поля в SQLite был DATE или DATETIME. А на уже работающей БД с уже созданными таблицами - поменять тип поля не так просто (рискну предположить что у вас именно эта ситуация). Поэтому приведу решение.

С версии 3.25.0 в SQLite можно переименовать столбец: alter table tbl1 rename column a to b - однако тип столбца так не изменишь. Есть три выхода, все по своему прекрасны, потому что учат впредь заранее продумывать структуру и типы данных :-)  

1) Вручную создать пустую таблицу с нужной структурой и типами, перелить туда данные, удалить старую и переименовать новую в имя старой. Хорошо прокачивает DML SQL и займет полчаса (первый раз, далее минуты три).

2) Поставить в старый FireFox 52 ESR/52.8.0esr, May 2018 - расширение SQLite Manager (либо скачать xulrunner и запустить это расширение в нём). Учитывая что это язык XUL - не исключено что поиском названных сущностей будут проблемы (Мозилла грозилась их вычистить и наверняка появилось много новодела с другими функциями). В общем старое XUL-расширение SQLite Manager позволяет в два клика поменять как тип, так и имя столбца. С ним это займет секунды. Однако изменить порядок столбцов так не выйдет, поэтому см. п. 3. Хотя, возможно из многих других SQL-редакторов или расширений-новоделов кто-то это умеет, их вообще полно хороших SQLiteDatabaseBrowser, Sqliteman, SQLiteStudio (все free+portable).

3) Но SQLite Manager вообще-то до сих пор самое крутое расширение для SQLite, подсвечивающее заливкой тип значения, сортирующее по клику на заголовках, позволяющее писать UDF на JS, логирующее и сохраняющее всю историю ваших запросов в отдельной таблице БД (так что можно отлаживать SQL-запросы с коллегами по сети, в реальном времени, итерациями - неслыханно). А ещё в нем есть готовый экспорт/импорт в 3 формата, включая SQL-дамп. То есть вы просто экспортируете таблицу в дамп или csv, копируете CREATE-инструкцию, пересоздаете новую таблицу с исправленной CREATE (типы, порядок, имена, индексы - все можно поменять) и импортируете в неё обратно. Займет минуты три.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

ost

#9
Цитата: economist от  5 мая 2021, 23:40этого недостаточно
Не могу добиться корректной вставки в лист Calc данных.

Что сделал:
Создал тестовую базу SQLite (3.35.4) https://yadi.sk/d/hPpvznJHR0N7wQ. Сразу назначил полям таблицы типы.
Заполнил ее данными: https://yadi.sk/d/t6cjprePLsmu5g
Настроил соответствующий источник данных в Windows (ODBC-драйвер http://www.ch-werner.de/sqliteodbc/ версия 0.9998)
Создал базу .odb, подключил ее к нужному ODBC-источнику и зарегистрировал ее в LO
Сделал тестовый файл Calc "TestOds.ods" с макросами: https://yadi.sk/i/m6YLU6GmebIItg. Отфориатировал столбцы.

Случай 1: Получил данные из RowSet по "getString" (строки 2-3 листа "t1" файла "TestOds.ods")
Случай 2: Получил данные из RowSet по "getDouble" (строки 4-5 листа "t1" файла "TestOds.ods")

В обоих случаях не удалось правильно вставить данные (правильные в "TestOds.ods" залил зеленым, неправильные - красным)

Видимо, как советовал Владимир, все же придется каким-то образом получать сведения о типе данных, хранящихся в "столбцах" RowSet'а, а затем использовать "getString" на текстовых столбцах и "getDouble" - на всех остальных.

Ссылка на папку со всеми файлами: https://yadi.sk/d/PAc96L5P0kZdaw

economist

#10
Из sqlite создал ODB с ODBC-драйвером (БД надо создавать всегда), все работает прекрасно, краткая дата и краткое дата-время возвращаются без каких либо допконвертаций по Ctrl+F4, а значит и вернутся таковыми при всех других способах (скрин):  


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