Как загрузить данные из Excel в ТаблицуЗначений 1С:Предприятие, если ПостроительЗапроса считывает только первую колонку?

← На главную

При работе с внешними данными в 1С:Предприятие часто возникает необходимость загружать информацию из файлов Excel. Одним из распространенных способов является использование объекта ТабличныйДокумент для чтения файла, а затем ПостроителяЗапроса для преобразования данных в ТаблицуЗначений. Однако пользователи часто сталкиваются с проблемой, когда ПостроительЗапроса считывает только первую колонку Excel-файла, игнорируя остальные. Давайте рассмотрим, почему это происходит и как эффективно решить эту проблему.

Выясняем причину некорректной работы ПостроителяЗапроса

Основная причина, по которой ПостроительЗапроса может некорректно обрабатывать данные и считывать только первую колонку, заключается в том, как он интерпретирует структуру ТабличногоДокумента, полученного из Excel. ПостроительЗапроса ожидает, что первая строка указанной ему области будет содержать заголовки будущих колонок ТаблицыЗначений. Если в этой строке возникают проблемы, весь процесс может работать неправильно.

Рассмотрим ключевые требования к строке заголовков, которые необходимо соблюдать:

Валидность имен колонок: Названия колонок должны соответствовать правилам именования идентификаторов в 1С. Это означает, что они не должны содержать пробелы, начинаться с цифры или включать специальные символы (точки, скобки, тире и другие). Хотя ПостроительЗапроса может автоматически заменять пробелы на символ подчеркивания, другие некорректные символы или сложная структура могут привести к ошибке. Если в заголовке есть пробелы, например, "Наименование товара", ПостроительЗапроса может преобразовать его в "Наименование_товара". Однако, если имя содержит, например, "Код (артикул)", это может вызвать проблемы.
Отсутствие объединенных ячеек: Если в строке заголовков присутствуют объединенные ячейки, ПостроительЗапроса не сможет корректно определить границы колонок. В таких случаях он часто "видит" только первую колонку, которая предшествует объединенной области, или только первую колонку из объединенной группы.
Отсутствие пустых ячеек: Пустая ячейка в строке заголовков может прервать процесс определения колонок. ПостроительЗапроса может воспринять пустую ячейку как конец определения списка колонок, и все последующие колонки будут проигнорированы.

Всегда внимательно проверяйте структуру вашего Excel-файла, особенно строку, которую вы планируете использовать как шапку таблицы.

Точное указание области чтения данных

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

Для этого мы используем метод Область() объекта ТабличныйДокумент с параметрами, указывающими начальную и конечную строку, а также колонки. Рассмотрим пример:


ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ВыборФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);

// Для отладки всегда полезно посмотреть, что получилось
// ТабДок.Показать(); 

// Если шапка начинается с 4-й строки и данные до 170-й строки, 6 колонок
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область(4, 1, 170, 6)); 
Построитель.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
Построитель.ЗаполнитьНастройки;
Построитель.Выполнить;
ТаблицаЗначений = Построитель.Результат.Выгрузить();

В этом примере ТабДок.Область(4, 1, 170, 6) указывает, что:

Первой строкой для чтения является 4-я строка. Именно ее ПостроительЗапроса будет использовать как строку заголовков.
Начальной колонкой является 1-я колонка.
Конечной строкой для чтения является 170-я строка.
Конечной колонкой является 6-я колонка.

Такой подход позволяет нам игнорировать "мусор" или нерелевантную информацию, находящуюся до фактических данных и заголовков.

Альтернативный способ чтения данных: СпособЧтенияЗначенийТабличногоДокумента.Текст

В некоторых случаях, когда требуется просто "выдернуть" все данные из Excel как строки, не заморачиваясь с автоматическим определением типов или названиями колонок, можно использовать другой способ чтения значений ТабличногоДокумента.

Вместо СпособЧтенияЗначенийТабличногоДокумента.Значение, который пытается преобразовать данные в их "истинный" тип (число, дата и т.д.), мы можем использовать СпособЧтенияЗначенийТабличногоДокумента.Текст.


ТабДок = Новый ТабличныйДокумент;
// Читаем все значения как текст
ТабДок.Прочитать(ВыборФайла, СпособЧтенияЗначенийТабличногоДокумента.Текст); 

// Далее можно использовать ПостроительЗапроса или ручной обход
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область(4, 1, 170, 6)); 
// ... остальные настройки Построителя

При таком подходе все ячейки будут прочитаны как строковые значения. Это может быть полезно, например, для штрихкодов, которые могут содержать ведущие нули или восприниматься Excel как числа в экспоненциальном формате. Однако, если вам нужны данные в конкретных числовых или датных форматах, вам придется вручную преобразовывать эти строки в нужный тип после загрузки в ТаблицуЗначений.

Решение проблемы с некорректным определением типов данных (например, штрихкод как число)

Очень распространенная проблема при загрузке данных из Excel — это когда значения, которые должны быть строками (например, штрихкоды, артикулы, коды товаров с ведущими нулями), автоматически преобразуются в числа. Например, штрихкод 4601234567890 может быть прочитан как 4.60123E+12 или вовсе потерять ведущие нули.

Рассмотрим надежные способы решения этой проблемы:

1. Форматирование ячеек в Excel

Самый простой и эффективный способ — подготовить файл Excel перед импортом. Перед сохранением или непосредственно перед началом импорта в самом файле Excel необходимо установить для столбца со штрихкодами (или любыми другими строковыми идентификаторами) текстовый формат ("Text").

Выделите нужный столбец в Excel.
Нажмите правой кнопкой мыши и выберите "Формат ячеек...".
На вкладке "Число" выберите категорию "Текстовый".
Сохраните изменения в файле Excel.

Это гарантирует, что 1С при чтении через СпособЧтенияЗначенийТабличногоДокумента.Значение воспримет данные как строку, сохранив все символы, включая ведущие нули.

2. Программное создание колонок с нужным типом и преобразование

Если у вас нет возможности изменять исходный файл Excel или вы хотите иметь полный контроль над типами данных в 1С, вы можете применить более контролируемый программный подход. Вместо прямой выгрузки из ПостроителяЗапроса в результирующую ТаблицуЗначений следует выполнить промежуточные шаги:

Сначала данные загружаются в промежуточную ТаблицуЗначений так, как они прочитались (возможно, с некорректными типами для штрихкодов).
Затем создается новая, "чистовая" ТаблицаЗначений с правильно настроенными колонками и явно указанными типами данных (например, колонка "Штрихкод" с типом Строка и определенной длиной).
Данные из промежуточной таблицы переносятся в чистовую с явным преобразованием типов, где это необходимо.

Посмотрим на пример кода для программного преобразования:


// ... код загрузки в ТабДок и работы с ПостроителемЗапроса ...
// Предположим, ТаблицаПромежуточная уже содержит данные из Excel, 
// где колонка "Штрихкод" могла прочитаться как число.
ТаблицаПромежуточная = Построитель.Результат.Выгрузить();

ТаблицаРезультат = Новый ТаблицаЗначений;

// Добавляем колонки в чистовую таблицу с нужными типами
ТаблицаРезультат.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
// Указываем тип Строка и максимальную длину для штрихкода
ТаблицаРезультат.Колонки.Добавить("Штрихкод", Новый ОписаниеТипов("Строка", , Новый КвалификаторыСтроки(50))); 
ТаблицаРезультат.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 3)));

Для Каждого СтрокаТЗ Из ТаблицаПромежуточная Цикл
    НоваяСтрока = ТаблицаРезультат.Добавить();
    
    // Заполняем значения полей, которые не требуют преобразования
    // Если имена колонок совпадают, можно использовать ЗаполнитьЗначенияСвойств
    // ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаТЗ); 

    // Пример явного заполнения и преобразования
    Если СтрокаТЗ.Свойство("Номенклатура") Тогда
        // Здесь потребуется найти ссылку на номенклатуру по наименованию или коду
        // Например: НоваяСтрока.Номенклатура = Справочники.Номенклатура.НайтиПоНаименованию(СтрокаТЗ.Номенклатура);
        НоваяСтрока.Номенклатура = СтрокаТЗ.Номенклатура; // Для примера
    КонецЕсли;

    Если СтрокаТЗ.Свойство("Количество") Тогда
        // Убедимся, что это число
        НоваяСтрока.Количество = ОбщегоНазначения.ЗначениеПоУмолчанию(СтрокаТЗ.Количество, 0); 
    КонецЕсли;

    // Преобразуем значение штрихкода в строку, убирая возможные разделители и приводя к нужному формату
    Если СтрокаТЗ.Свойство("Штрихкод") Тогда
        // Формат(Число, "ЧГ=0") преобразует число в строку без группировки разрядов
        // Это особенно полезно для длинных чисел, которые могут быть штрихкодами
        НоваяСтрока.Штрихкод = Формат(СтрокаТЗ.Штрихкод, "ЧГ=0"); 
        // Если штрихкод был строкой с пробелами или нежелательными символами, 
        // можно добавить очистку, например: СтрЗаменить(НоваяСтрока.Штрихкод, " ", "");
    Иначе
        НоваяСтрока.Штрихкод = "";
    КонецЕсли;
КонецЦикла;

// Теперь ТаблицаРезультат содержит данные с корректными типами

Этот подход дает вам полный контроль над тем, как каждое значение из Excel будет интерпретировано и сохранено в 1С.

Альтернативные методы загрузки данных в 1С

Хотя ТабличныйДокумент и ПостроительЗапроса удобны тем, что не требуют наличия установленного MS Office на клиентской машине или сервере, существуют и другие, часто более гибкие методы загрузки данных из Excel. Рассмотрим их подробнее:

1. Прямой обход ячеек ТабличногоДокумента

Как уже упоминалось в обсуждении, можно обойтись без ПостроителяЗапроса и вручную прочитать ячейки ТабличногоДокумента. Этот метод дает вам полный контроль над процессом:

Вы можете пропускать любое количество строк "шапки" или других нерелевантных данных.
Обрабатывать каждую ячейку индивидуально.
Сразу присваивать данным нужный тип при добавлении в ТаблицуЗначений.
Гибко обрабатывать ошибки или нестандартные ситуации.

Посмотрим на пример кода:


ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ВыборФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);

ТаблицаЗначенийРезультат = Новый ТаблицаЗначений;
ТаблицаЗначенийРезультат.Колонки.Добавить("КодНоменклатуры", Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(20)));
ТаблицаЗначенийРезультат.Колонки.Добавить("Наименование", Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(100)));
ТаблицаЗначенийРезультат.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(10, 2)));

// Определяем начальную и конечную строку для чтения данных
// Предположим, шапка находится в 3-й строке, данные начинаются с 4-й строки
НачальнаяСтрокаДанных = 4; 
КонечнаяСтрокаДанных  = ТабДок.ВысотаТаблицы; // ВысотаТаблицы - общее количество строк в документе

// Если нужно ограничить по количеству строк, можно использовать фиксированное значение
// КонечнаяСтрокаДанных = Мин(ТабДок.ВысотаТаблицы, 200); // Например, читать не более 200 строк

Для НомерСтроки = НачальнаяСтрокаДанных По КонечнаяСтрокаДанных Цикл
    // Проверим, не пустая ли строка, чтобы не добавлять лишнее
    Если ТабДок.Область("R" + НомерСтроки + "C1").Текст <> "" Тогда
        НоваяСтрока = ТаблицаЗначенийРезультат.Добавить();
        
        // Читаем значения из ячеек по номерам колонок
        ТекстЯчейкиКод = ТабДок.Область("R" + НомерСтроки + "C1").Текст;
        НоваяСтрока.КодНоменклатуры = ТекстЯчейкиКод; // Уже строка

        ТекстЯчейкиНаименование = ТабДок.Область("R" + НомерСтроки + "C2").Текст;
        НоваяСтрока.Наименование = ТекстЯчейкиНаименование; // Уже строка
        
        // Для числовых значений используем Значение
        ЗначениеЯчейкиКоличество = ТабДок.Область("R" + НомерСтроки + "C3").Значение;
        НоваяСтрова.Количество = ОбщегоНазначения.ЗначениеПоУмолчанию(ЗначениеЯчейкиКоличество, 0);
    КонецЕсли;
КонецЦикла;

Этот подход особенно полезен, когда структура Excel-файла сильно меняется или имеет сложный формат, который ПостроительЗапроса не может адекватно разобрать.

2. Использование COM-объекта ("Excel.Application")

Этот "классический" способ требует наличия установленного MS Excel на сервере или клиентской машине (в зависимости от того, где выполняется код). Он предоставляет наиболее полный доступ ко всем возможностям Excel:

Можно обращаться к листам по имени, что удобно, если данные могут быть на разных листах.
Получать не только значение ячейки (.Value), но и ее отформатированный текст (.Text). Использование .Text надежно решает проблему со штрихкодами, поскольку возвращает именно то строковое представление, которое вы видите в Excel (например, "00123" вместо 123).
Иметь доступ к сложным свойствам ячеек, таким как цвет, шрифт, комментарии и т.д.

Пример использования COM-объекта:


Попытка
    Excel = Новый COMОбъект("Excel.Application");
    Excel.Workbooks.Open(ВыборФайла);
    Лист = Excel.ActiveWorkbook.Sheets(1); // Или Sheets("ИмяЛиста")

    ТаблицаЗначенийРезультат = Новый ТаблицаЗначений;
    ТаблицаЗначенийРезультат.Колонки.Добавить("Штрихкод", Новый ОписаниеТипов("Строка",, Новый КвалификаторыСтроки(50)));
    ТаблицаЗначенийРезультат.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(10, 2)));

    НачальнаяСтрокаДанных = 2; // Предположим, шапка в 1-й строке
    Для НомерСтроки = НачальнаяСтрокаДанных По Лист.UsedRange.Rows.Count Цикл
        НоваяСтрока = ТаблицаЗначенийРезультат.Добавить();
        
        // Используем .Text для штрихкода, чтобы получить его как строку, включая ведущие нули
        НоваяСтрока.Штрихкод  = Лист.Cells(НомерСтроки, 1).Text; 
        НоваяСтрока.Количество = Лист.Cells(НомерСтроки, 2).Value;
    КонецЦикла;

    Excel.ActiveWorkbook.Close(0); // Закрыть книгу без сохранения
    Excel.Quit(); // Закрыть приложение Excel
Исключение
    Сообщить("Ошибка при работе с Excel: " + ОписаниеОшибки());
КонецПопытки;

Этот метод очень мощный, но имеет зависимость от установленного MS Office, что может быть проблемой на сервере или у некоторых пользователей.

3. Использование технологии ADO (ActiveX Data Objects)

Метод ADO позволяет обращаться к листу Excel как к таблице базы данных, используя SQL-запросы. Он не требует установленного MS Office (только наличия соответствующих драйверов OLE DB в системе, например, Microsoft.ACE.OLEDB.12.0 или Microsoft.Jet.OLEDB.4.0) и является одним из самых быстрых способов чтения больших объемов данных.

ADO более строго относится к типам данных, что может помочь избежать автоматического преобразования, если вы явно укажете форматы в запросе.
Позволяет фильтровать и сортировать данные прямо на уровне источника, уменьшая объем передаваемой информации.
При использовании ADO для строк, которые должны сохранять формат (например, штрихкоды), можно настроить схему импорта или использовать специфические строки соединения, чтобы Excel-драйвер не пытался автоматически определять тип столбца как числовой, если в нем есть смешанные данные.

Пример использования ADO:


Функция ПолучитьТаблицуИзExcelADO(ПутьКФайлу, ИмяЛиста, ПерваяСтрокаЗаголовков = Истина)
    Попытка
        Соединение = Новый COMОбъект("ADODB.Connection");
        СтрокаСоединения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" + ПутьКФайлу + """;Extended Properties=""Excel 12.0 XML;HDR=" + ?(ПерваяСтрокаЗаголовков, "Yes", "No") + ";IMEX=1"";";
        
        // HDR=Yes означает, что первая строка содержит заголовки
        // IMEX=1 означает, что в случае смешанных типов данных в столбце все будет читаться как текст
        
        Соединение.Open(СтрокаСоединения);
        
        Запрос = "SELECT * FROM [" + ИмяЛиста + "$]";
        // Если данные начинаются с определенной строки, можно использовать расширенные запросы
        // Например: "SELECT * FROM [Лист1$A4:F170]" для указания диапазона
        
        НаборЗаписей = Новый COMОбъект("ADODB.Recordset");
        НаборЗаписей.Open(Запрос, Соединение);
        
        ТаблицаЗначенийРезультат = Новый ТаблицаЗначений;
        Для Каждого Поле Из НаборЗаписей.Fields Цикл
            ТаблицаЗначенийРезультат.Колонки.Добавить(Поле.Name);
        КонецЦикла;
        
        Пока Не НаборЗаписей.EOF Цикл
            НоваяСтрока = ТаблицаЗначенийРезультат.Добавить();
            Для Каждого Поле Из НаборЗаписей.Fields Цикл
                Если Поле.Value = Null Тогда
                    НоваяСтрока[Поле.Name] = Неопределено;
                Иначе
                    НоваяСтрока[Поле.Name] = Поле.Value;
                КонецЕсли;
            КонецЦикла;
            НаборЗаписей.MoveNext();
        КонецЦикла;
        
        НаборЗаписей.Close();
        Соединение.Close();
        
        Возврат ТаблицаЗначенийРезультат;
        
    Исключение
        Сообщить("Ошибка при работе с ADO: " + ОписаниеОшибки());
        Возврат Неопределено;
    КонецПопытки;
КонецФункции

// Пример вызова
// ТаблицаДанных = ПолучитьТаблицуИзExcelADO("C:\Путь\К\Файлу.xlsx", "Лист1", Истина);

В строке соединения IMEX=1 играет критически важную роль для корректной обработки смешанных типов данных. Он указывает драйверу всегда считывать данные как текст, что предотвращает автоматическое преобразование штрихкодов в числа.

Заключение

Мы видим, что проблема некорректной загрузки данных из Excel, особенно когда ПостроительЗапроса считывает только одну колонку или неправильно определяет типы данных, имеет несколько эффективных решений. Выбор метода зависит от ваших требований к гибкости, скорости, а также от наличия установленного ПО (MS Office) и сложности структуры исходного Excel-файла. Всегда начинайте с тщательной проверки структуры файла Excel и используйте наиболее подходящий для вашей задачи метод.