При организации быстрой загрузки больших прайс-листов и каталогов в 1С разработчики часто используют провайдеры Microsoft.ACE.OLEDB.12.0 или Microsoft.Jet.OLEDB.4.0 через объект ADODB.Connection. Однако при обработке файлов, выгруженных из сторонних веб-сервисов или учетных программ, возникает неприятная ошибка: первая колонка таблицы (колонка A) полностью игнорируется, а чтение начинается со второй колонки (колонки B).
При этом если открыть проблемный файл в Microsoft Excel и просто сохранить его, загрузка начинает проходить корректно. Разберем по шагам, почему возникает эта проблема, как исправить текст SQL-запроса и какие альтернативные быстрые способы загрузки файлов доступны в платформе 1С:Предприятие.
Посмотрим на классический пример подключения к файлу Excel через OLEDB и выборки данных с помощью объекта ADODB.Recordset:
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;""";
ADODBConnection = Новый COMОбъект("ADODB.Connection");
ADODBConnection.ConnectionString = ConnectionString;
ADODBConnection.Open();
ADODBConnection.CursorLocation = 3; // adUseClient
ТекстЗапроса = "SELECT * FROM [" + ИмяЛиста + "]";
ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
ADODBRecordset.Open(ТекстЗапроса, ADODBConnection);
КолвоКолонокExcel = ADODBRecordset.Fields.Count;
Для ит = 0 ПО КолвоКолонокExcel - 1 Цикл
Поле = ADODBRecordset.Fields.Item(ит);
// В проблемном файле Fields.Item(0) содержит данные колонки B вместо колонки A
КонецЦикла;
Выясним причину такого поведения драйвера OLEDB при обработке файлов:
$ в имени листа: В синтаксисе OLEDB конструкция [Лист1$] указывает на весь лист целиком, тогда как обращение [Лист1] (без знака $) драйвер воспринимает как обращение к именованному диапазону (Named Range). Если точного диапазона нет, OLEDB пытается рассчитать границы самостоятельно и нередко сдвигает начало таблицы.dimension внутри файла листа. Когда файл пересохраняется в Excel, программа пересчитывает разметку, и ошибка исчезает.Чтобы заставить драйвер принудительно читать данные начиная с первой колонки (столбца A), необходимо добавить символ $ и явно зафиксировать диапазон колонок:
// Явное указание диапазона колонок от A до нужной границы (например, Z)
ИмяЛиста = "PITSTOP.COM.UA";
ТекстЗапроса = "SELECT * FROM ['" + ИмяЛиста + "$A:Z']";
// Либо с указанием диапазона строк:
// ТекстЗапроса = "SELECT * FROM ['" + ИмяЛиста + "$A1:ZZ100000']";
ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
ADODBRecordset.Open(ТекстЗапроса, ADODBConnection);
Обратите внимание: если имя листа содержит точки, дефисы или пробелы, его следует обязательно обрамлять одинарными кавычками внутри квадратных скобок: ['Имя.Листа$'].
Также можно получить точное системное имя таблицы программно через метаданные подключения:
СхемаТаблиц = ADODBConnection.OpenSchema(20); // 20 = adSchemaTables
Пока Не СхемаТаблиц.EOF Цикл
ИмяТаблицы = СхемаТаблиц.Fields("TABLE_NAME").Value; // Возвращает имя со знаком $ и нужным экранированием
СхемаТаблиц.MoveNext();
КонецЦикла;
Если требуется платформенное решение без использования внешних COM-библиотек и драйверов Windows, рассмотрим метод Прочитать объекта ТабличныйДокумент. Он работает кроссплатформенно (включая Linux-серверы):
ТабДок = Новый ТабличныйДокумент;
// Указываем способ чтения как текст для ускорения разбора
ТабДок.Прочитать(СокрЛП(ФайлEXCEL), СпособЧтенияЗначенийТабличногоДокумента.Текст);
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;
ТЗ = Новый ТаблицаЗначений;
Для Счетчик = 1 По ВсегоКолонок Цикл
ТЗ.Колонки.Добавить("Колонка" + Счетчик, Новый ОписаниеТипов("Строка"));
КонецЦикла;
Для НомерСтроки = 1 По ВсегоСтрок Цикл
НоваяСтрока = ТЗ.Добавить();
Для НомерКолонки = 1 По ВсегоКолонок Цикл
НоваяСтрока[НомерКолонки - 1] = ТабДок.ПолучитьОбласть(НомерСтроки, НомерКолонки).ТекущийОбъект.Текст;
КонецЦикла;
КонецЦикла;
Если на клиентском компьютере установлен пакет Microsoft Office, можно использовать COM-объект Excel.Application с выгрузкой всей области ячеек в массив одним обращением. Это работает в разы быстрее построчного перебора ячеек через COM:
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.Visible = Ложь;
ExcelПриложение.DisplayAlerts = Ложь;
Книга = ExcelПриложение.Workbooks.Open(СокрЛП(ФайлEXCEL));
Лист = Книга.Worksheets(1);
ВсегоСтрок = Лист.Cells.SpecialCells(11).Row;
ВсегоКолонок = Лист.Cells.SpecialCells(11).Column;
Область = Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок));
ДанныеМассива = Область.Value.Выгрузить();
Книга.Close(Ложь);
ExcelПриложение.Quit();
ТЗ = Новый ТаблицаЗначений;
Для Счетчик = 1 По ВсегоКолонок Цикл
ТЗ.Колонки.Добавить("Колонка" + Счетчик);
КонецЦикла;
Для Счетчик = 0 По ВсегоКолонок - 1 Цикл
ТЗ.ЗагрузитьКолонку(ДанныеМассива[Счетчик], Счетчик);
КонецЦикла;
Для загрузки сверхбольших файлов формата .xlsx (сотни тысяч строк) максимальную скорость и минимальное потребление оперативной памяти обеспечивает потоковое чтение встроенными средствами 1С:
.xlsx файл как архив через ЧтениеZipФайла во временный каталог.xl/sharedStrings.xml с помощью объекта ЧтениеXML.xl/worksheets/sheet1.xml, восстанавливая значения строк на лету.Этот подход работает за секунды, не зависит от наличия установленных драйверов OLEDB и платформенной разрядности (x86/x64).