При попытке ускорить загрузку данных из Microsoft Excel в 1С:Предприятие 7.7 программисты часто пытаются использовать метод получения всей области данных сразу через объект Range и его свойство Value. Однако, в отличие от более современных версий платформы (1С 8.х), в «семерке» этот прием приводит к ошибке «тип переменной не поддерживается». Проанализируем, почему это происходит, и разберем наиболее эффективные способы обхода этого ограничения.
Разберем техническую причину этой проблемы. Когда мы обращаемся к свойству Value объекта Range, который содержит более одной ячейки, COM-сервер Microsoft Excel пытается передать массив данных. Этот массив имеет специфический тип SAFEARRAY. Платформа 1С 7.7 была разработана в эпоху, когда COM-технологии в языке были реализованы в базовом виде, и ее внутренний движок просто не умеет преобразовывать многомерные массивы типа SAFEARRAY во внутренние типы данных 1С (такие как СписокЗначений или ТаблицаЗначений).
В 1С 8.х эта проблема решена на уровне ядра: при получении такого объекта платформа автоматически конвертирует его в FixedArray или аналогичную структуру. В 1С 7.7 мы вынуждены искать альтернативные пути. Рассмотрим подробнее каждый из них.
Самый простой, но и самый медленный способ — это перебор каждой ячейки в цикле. Основная потеря времени здесь происходит из-за того, что на каждое обращение к Excel.Cells(r, c).Value тратится время на межпроцессное взаимодействие между 1С и Excel.
Посмотрим на пример реализации такого обхода:
Excel = СоздатьОбъект("Excel.Application");
Книга = Excel.Workbooks.Open(ВыбИмяФайла);
Лист = Книга.Worksheets(1);
// Определяем количество строк по последней заполненной ячейке
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;
ВсегоКолонок = 9;
ТЗ = СоздатьОбъект("ТаблицаЗначений");
Для НомКол = 1 По ВсегоКолонок Цикл
ТЗ.НоваяКолонка("Кол" + НомКол);
КонецЦикла;
Для Стр = 3 По ВсегоСтрок Цикл
ТЗ.НоваяСтрока();
Для Кол = 1 По ВсегоКолонок Цикл
ТЗ.УстановитьЗначение(ТЗ.НомерСтроки, Кол, Лист.Cells(Стр, Кол).Value);
КонецЦикла;
КонецЦикла;
Важный совет: Чтобы немного ускорить этот процесс, обязательно отключайте обновление экрана в Excel и пересчет формул перед началом цикла с помощью команд Excel.ScreenUpdating = 0 и Excel.Calculation = -4135.
Если нам нужно прочитать данные быстро и без использования внешних компонент, лучшим выбором будет ADO (ActiveX Data Objects). В этом случае Excel-файл рассматривается как база данных, а листы — как таблицы. Это позволяет избежать медленного обращения к каждой ячейке через COM-интерфейс приложения Excel.
Рассмотрим по шагам реализацию через ADO:
// Создаем объекты ADO
Conn = СоздатьОбъект("ADODB.Connection");
RS = СоздатьОбъект("ADODB.Recordset");
// Строка соединения для файлов .xls (Excel 97-2003)
// Для .xlsx используйте провайдер Microsoft.ACE.OLEDB.12.0
StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ВыбИмяФайла + ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";";
Попытка
Conn.Open(StrConn);
Исключение
Предупреждение("Ошибка подключения к файлу!");
Возврат;
КонецПопытки;
// Текст запроса (Лист1$ — название листа)
ТекстЗапроса = "SELECT * FROM [Лист1$A3:I]";
RS.Open(ТекстЗапроса, Conn, 3, 1);
ТЗ = СоздатьОбъект("ТаблицаЗначений");
// Формируем колонки ТЗ на основании полей Recordset
Для к = 0 По RS.Fields.Count - 1 Цикл
ТЗ.НоваяКолонка("Кол" + к);
КонецЦикла;
// Загружаем данные
Пока RS.EOF() = 0 Цикл
ТЗ.НоваяСтрока();
Для к = 0 По RS.Fields.Count - 1 Цикл
ТЗ.УстановитьЗначение(ТЗ.НомерСтроки, к + 1, RS.Fields(к).Value);
КонецЦикла;
RS.MoveNext();
КонецЦикла;
RS.Close();
Conn.Close();
Этот метод работает в десятки раз быстрее, чем поячеечный перебор, так как данные передаются потоком.
Проанализируем ситуацию, когда нам необходимо максимально быстрое решение с поддержкой всех форматов и без установленного Microsoft Office на сервере. В этом случае стандартом для 1С 7.7 является компонента «Йоксель».
Она умеет читать файлы .xls напрямую на бинарном уровне и сразу преобразовывать их в табличный документ 1С или таблицу значений. Выясним, как это выглядит в коде:
// Загрузка компоненты
ЗагрузитьВнешнююКомпоненту("spreadsh.dll");
Таб = СоздатьОбъект("Таблица");
// Загружаем файл в объект таблицы Йокселя
Йокс = СоздатьОбъект("Spreadsheet");
Йокс.Открыть(ВыбИмяФайла);
// Теперь мы можем либо работать с объектом Йокс напрямую,
// либо выгрузить данные в ТЗ через вспомогательные модули.
// Йоксель позволяет обращаться к ячейкам очень быстро.
ВсегоСтрок = Йокс.ВысотаТаблицы;
ВсегоКолонок = Йокс.ШиринаТаблицы;
Главное преимущество Йокселя — это стабильность и отсутствие зависимости от версии установленного Excel.
Рассмотрим еще один хитроумный способ, который позволяет обойти ограничение типов данных. Мы можем программно заставить Excel сохранить нужный нам лист в текстовый формат с разделителями (CSV), а затем прочитать этот файл средствами 1С 7.7 через объект Текст.
Алгоритм действий следующий:
SaveAs с параметром формата 6 (xlCSV) или -4158 (xlTextWindows).Пример кода для сохранения:
Excel.ActiveWorkbook.SaveAs(ИмяВремФайла, 6); // 6 — это формат CSV
Excel.ActiveWorkbook.Close(0);
Excel.Quit();
// Читаем файл
Текст = СоздатьОбъект("Текст");
Текст.Открыть(ИмяВремФайла);
Для н = 1 По Текст.КоличествоСтрок() Цикл
Стр = Текст.ПолучитьСтроку(н);
// Разбираем строку по разделителю (например, точка с запятой)
КонецЦикла;
Этот метод невероятно быстр при чтении из 1С, так как объект Текст работает с файловой системой напрямую.
Если использование внешних компонент запрещено, а ADO не подходит по соображениям структуры файла, можно использовать «трюк» с буфером обмена. Суть в том, что Excel умеет копировать область ячеек в буфер в виде текста с табуляцией, а 1С 7.7 может вставить эти данные в свой объект Таблица.
Разберем последовательность действий:
Лист.Range("A1:Z100").Copy().Таблица (табличный документ).Таблица.ВставитьОбъект() или имитируем нажатие клавиш Ctrl+V.Важно: Этот метод считается «грязным», так как он очищает буфер обмена пользователя и может привести к непредсказуемым результатам, если пользователь во время загрузки решит что-то скопировать самостоятельно.
Подведем итог нашему анализу. Ошибка «тип переменной не поддерживается» — это неустранимое ограничение платформы 1С 7.7 в части работы с COM-массивами. Чтобы эффективно решить задачу загрузки из Excel, придерживайтесь следующих правил:
Помните, что архитектура 1С 7.7 требует более вдумчивого подхода к ресурсам, чем 1С 8.х, поэтому выбор оптимального метода загрузки напрямую влияет на стабильность работы всей системы.