Автоматические поставщики запросов

Доступно с версии 2.384.99 через фасад XLSX-рабочей книги на Windows; автоматический выбор поставщика происходит только при явном обновлении запроса

Обновление существующего запроса

Workbook.QueryProviders.BaseDirectory := 'C:\Data';
Workbook.QueryProviders.MaxInputBytes := 64 * 1024 * 1024;
Workbook.QueryProviders.MaxResultCells := 2000000;
Workbook.QueryProviders.TimeoutSeconds := 30;
Status := Sheet.RefreshQueryTable('ImportedData', 1000000);

TXLSXWorkbook.QueryProviders: TXLSQueryProviderDispatcher возвращает принадлежащий книге диспетчер из lxQueryProviders; освобождать его не нужно

TXLSXWorksheet.RefreshQueryTable(const AName: WideString; AMaxRows: Integer = 1000000; AOnProgress: TXLSQueryRefreshProgress = nil): Integer и перегрузка, принимающая AIndex с отсчётом от нуля, выбирают существующий запрос и работают через этот диспетчер

Существующие перегрузки, принимающие явный TXLSQueryTableProvider, по-прежнему используют этого поставщика напрямую, включая прежний отказ при его отсутствии; автоматический диспетчинг для них не применяется

Обновление возвращает 1 при успехе, 0 при отмене или -1 при сбое, с диагностическими кодами 1401 и 1400 внутри xlsOperationRefresh; прежнее транзакционное применение результата, проверка схемы, литеральные строки и поведение форматирования сохраняются

Эти диагностики называются xlsDiagnosticQueryRefreshCancelled и xlsDiagnosticQueryRefreshFailed; блокировка записи книги захватывается до преобразования статуса, поэтому замороженное read-only представление или другой отказ блокировки записи приводит к исключению

Открытие и сохранение книги никогда не подтягивает данные подключений, не вычисляет запросы и не реагирует на сохранённые метаданные RefreshOnLoad; явно обновите каждый нужный запрос, прежде чем сохранять его результат

Поддерживаемые встроенные поставщики

• xlckText читает локальный текст с разделителями или фиксированной шириной полей со строгим декодированием UTF-8, UTF-16LE, UTF-16BE, Windows-1252 или Latin-1, настроенными начальными строками и поддерживаемыми типами полей, включая шесть порядков дат и пропускаемые поля; вход с разделителями также поддерживает multiline-записи в кавычках
• xlckAdo, xlckOleDb и xlckOdbc используют установленные в Windows драйверы ADO и СУБД с подключениями и наборами записей только для чтения; CommandType = 2 выбирает SQL-текст, а CommandType = 3 — команду таблицы
• xlckWeb использует Windows WinHTTP для анонимного GET-запроса HTTP или HTTPS за одной непустой прямоугольной HTML-таблицей, выбираемой по положительному индексу или имени; поддерживаемые кодировки ответа — UTF-8, UTF-16, Windows-1252 и Latin-1

BaseDirectory задаёт базу только для относительных локальных текстовых путей; строки подключения к БД и Web-URL остаются явными метаданными подключения

Если кодировка не указана, принимается только ASCII-вход, если кодировку не опознаёт поддерживаемая метка порядка байтов (BOM); для не-ASCII входа нужна явная поддерживаемая кодировка или метка порядка байтов

Для Text-подключения с разделителями задайте TextPrompt = False, TextDelimited = True и ровно один разделитель, без схлопывания подряд идущих разделителей; новые подключения по умолчанию запрашивают параметры и используют разделитель-табуляцию, поэтому сбросьте TextTab, выбирая другой разделитель

Текст фиксированной ширины

Задайте TextPrompt = False и TextDelimited = False, затем добавьте TextFields в порядке строго возрастающих Position с отсчётом от нуля, начиная с нуля; каждое поле заканчивается на следующей позиции или на физическом терминаторе записи, а xltiftSkip исключает своё поле из результата

Позиции считаются в декодированных кодовых единицах UTF-16, а не во входных байтах; граница, разрезающая суррогатную пару, приводит к отказу, а CRLF, LF и CR завершают записи независимо от настроек разделителя и ограничителя

Заполнение полей обрезается перед преобразованием, включая явно типизированный текст, что соответствует проверенному нативному импорту фиксированной ширины; кавычки, табуляции и символы разделителей внутри поля остаются литеральным входом, а короткие записи дают пустые конечные поля, не меняя объявленную схему

Без TextFields каждая запись — одно общее поле; TextFirstRow выбирает первую запись схемы, Query.Headers пропускает значения этой записи, а явно пустые записи остаются строками

Действуют прежние лимиты на строки, входные байты, ячейки результата и 32767 кодовых единиц на поле; неверные метаданные, ошибки преобразования или избыточный вход очищают промежуточные данные до транзакционного обновления листа, а при отмене прежние ячейки результата сохраняются

Connection.TextPrompt := False;
Connection.TextDelimited := False;
Connection.TextFields.Add(xltiftGeneral, 0);
Connection.TextFields.Add(xltiftText, 8);
Status := Sheet.RefreshQueryTable('Imported');

Для Web-подключения задайте WebHtmlTables = True и WebHtmlFormat = 'none'; поддерживаемые запросы отклоняют встроенные учётные данные, фрагменты, аутентификацию и перенаправления

Встроенные поставщики отклоняют неподдерживаемые метаданные в своих типах подключений; обновление из БД отклоняет команды OLAP и серверные команды, непрямую адресацию через файл подключения, сохранённые пароли и запросы учётных данных, обновление Text отклоняет диалоги выбора файла, а обновлению Web нужны анонимные метаданные таблицы

Типизированные параметры базы данных

SQL-команды с текстом поддерживают позиционные маркеры ?, привязываемые через ADO Command в порядке Connection.Parameters; значения параметров никогда не подменяют SQL-текст, а имена лишь помечают привязки, не меняя позиционный порядок

Предварительная проверка подсчитывает маркеры вне строк в одинарных кавычках, идентификаторов в двойных кавычках или обратных апострофах, идентификаторов в квадратных скобках, строчных комментариев и вложенных блочных комментариев, включая удвоенные экранирования кавычек и скобок; незакрытые кавычки или комментарии и несовпадение количества маркеров приводят к отказу до открытия подключения

Используйте ParameterType = 'value' с явным ValueKind — xlcpvInteger, xlcpvDouble, xlcpvBoolean или xlcpvString — и соответствующим свойством значения; ноль, False и пустые строки являются значениями, а xlcpvNone не выводит значение во время выполнения

Connection.CommandType := 2;
Connection.CommandText := 'SELECT Amount FROM Sales WHERE Amount > ?';
with Connection.Parameters.Add do
begin
  Name := 'MinimumAmount';
  ParameterType := 'value';
  ValueKind := xlcpvInteger;
  IntegerValue := 0;
  SqlType := 4;
end;
Status := Sheet.RefreshQueryTable('SalesQuery');

Используйте ParameterType = 'cell', ValueKind = xlcpvCell и CellReference для полностью квалифицированной ссылки на локальный лист вида Inputs!$A$1 или 'Sales Input'!B2; имена листов в кавычках удваивают апострофы, а диапазоны, внешние книги, имена и неквалифицированные ссылки на ячейки отклоняются

Обновление листа снимает слепок сохранённых скалярных значений и доступных кэшей формул без пересчёта и материализации packed-ячеек; отсутствие кэша формулы и ячейки с ошибкой приводят к отказу, а отсутствующая или пустая ячейка даёт SQL null только при явном поддерживаемом SQL-типе

Слепок ячейки сохраняет свой Variant-тип, допуская знаковые Int64, Currency, типизированные даты и null, не сводя их к сохраняемым литеральным полям 32-битных целых или Double; напрямую сохраняемые литералы дат, null и Int64 лежат вне нативной модели метаданных параметров, поэтому для таких значений используйте типизированные привязки к ячейкам

SqlType использует коды SQL-типов ODBC, которые явно отображаются на типы ADO; это не значение ADO DataTypeEnum

Поддерживаемые явные типы также принимают SQL null; массивы, Variant-ссылки, ошибки, неконечные числа, неподдерживаемые SQL-типы и преобразования, теряющие целочисленную точность, отклоняются, а NUMERIC и DECIMAL требуют метаданных точности и масштаба, которых этот API привязки не предоставляет

ADO получает типизированное значение и объявленный размер, причём для пустого текста выделяется хотя бы одна кодовая единица; за диалект SQL, поддерживаемые типы параметров и преобразования результатов отвечают нативные драйверы, поэтому установленный поставщик всё ещё может отклонить корректную привязку или иметь более узкие числовые и датные возможности

Jet и ACE используют типизированные привязки OLE DATE для проверенных значений DATE, TIME и TIMESTAMP, чтобы хранить дату и время независимо от зависящих от локали текстовых проекций timestamp; правила проверки DATE и TIME сохраняются, а возможности проекции null остаются специфичными для конкретного поставщика

Параметры требуют SQL-команды с текстом и ограничены 1024 на выборку, 255 кодовых единиц на имя и 32767 кодовых единиц на текстовое значение; текст команды, ссылки на параметры, имена и полезная нагрузка также расходуют бюджет входных байтов

Диалоги запроса параметров и неизвестные атрибуты расширений отклоняются; RefreshOnChange остаётся сохранённой метаданной и не запускает фоновое обновление, а открытие и сохранение не разрешают ячейки и не выполняют команды

Прямой Fetch поддерживает литералы; FetchWithCellResolver(Connection, Query, MaxRows, out Data, var Abort, AResolver) принимает callback TXLSQueryParameterCellResolver для значений ячеек, чей Boolean-результат говорит о наличии сохранённого значения

Callback живёт только в рамках вызова и не сохраняется; автоматическое обновление листа подставляет свой локальный resolver, а зарегистрированные пользовательские поставщики по-прежнему имеют приоритет и сами определяют семантику параметров

Обновление Web поддерживает простые таблицы text/html без объединённых ячеек, вложенных выбранных таблиц и контента, управляемого скриптами; неподдерживаемые раскладки и сущности отклоняются, а не выдаются частичными данными

Регистрация пользовательского поставщика

Workbook.QueryProviders.RegisterProvider(xlckWeb, CustomProvider);
try
  Status := Sheet.RefreshQueryTable('RemoteData');
finally
  Workbook.QueryProviders.RegisterProvider(xlckWeb, nil);
end;

TXLSQueryProviderDispatcher.Create создаёт независимый диспетчер для прямого использования; книга создаёт и владеет собственным экземпляром

RegisterProvider(AKind: TXLSConnectionKind; AProvider: TXLSQueryTableProvider) устанавливает заимствованный обработчик для объявленного типа подключения, а nil снимает его; обработчик должен жить дольше регистрации

Зарегистрированный обработчик имеет приоритет над встроенным поставщиком и может поддерживать специфичную для приложения аутентификацию или типы подключений; смена регистрации, смена конфигурации и рекурсивный диспетчинг отклоняются во время выборки, а неверные значения enum отклоняются до обращения к массиву

Fetch(Connection, Query, MaxRows, out Data, var Abort) получает отсоединённые метаданные при обновлении листа и возвращает прямоугольные TXLSQueryResultData; отмена или исключения очищают промежуточные результаты до проброса

Лимиты ресурсов

MaxInputBytes по умолчанию равен 67108864 байт и ограничивает вход Text или Web и полезную нагрузку результатов поддерживаемых БД; MaxResultCells по умолчанию равен 2000000 ячеек и ограничивает прямоугольный результат

TimeoutSeconds по умолчанию равен 30 и настраивает поддерживаемые нативные фазы обращения к БД или HTTP; это не гарантированный дедлайн для всей операции и не для пользовательского поставщика

Все три настройки и MaxRows должны быть положительными, а таймаут — помещаться в нативное целое число миллисекунд; диспетчер отклоняет избыточные строки, столбцы или ячейки без усечения, а байтовые и входные лимиты поддерживают встроенные поставщики, и за пользовательские выборки отвечает зарегистрированный обработчик

Обновление листа проверяет все значения результата и восстанавливает ячейки и метаданные запроса или таблицы после отмены или ошибок приложения; пользовательские поставщики по-прежнему сами отвечают за соблюдение собственных лимитов внешних операций

Нативные привязки результатов XLSX

Запросы к БД на основе таблиц используют связь таблица–query table, согласованные ID полей, идентичности столбцов и скрытое локальное имя назначения; автономные поддерживаемые назначения Text и Web сохраняют свой диапазон результата

TXLSXTable.ColumnUniqueNames[Index]: WideString даёт нативные идентичности столбцов с отсчётом от нуля и сохраняет их при присваивании, копировании и повторном открытии вместе с ColumnQueryTableFieldIds

Устаревшее Text-подключение, привязанное напрямую как внешняя таблица, не является поддерживаемой нативной формой экспорта, и сохранение отклоняет его до вывода; явно обновлённые текстовые значения могут заполнить обычную таблицу, а установленный текстовый драйвер ADO/ODBC может дать нативный запрос к БД на основе таблицы

Несвязанные или неподдерживаемые импортированные связи и extension XML остаются нетронутыми; эта возможность не превращает непрозрачные внешние графы в поддерживаемые обновляемые запросы

См. API подключений и транзакционных запросов: там описаны проверка назначений, progress-callbacks и окружающая модель метаданных