Загрузка данных из табличных документов, созданных в редакторе Microsoft Excel – довольно часто встречающаяся задача в работе любого программиста 1С. Программа эта широко распространена, любима пользователями и часто используется для хранения и обработки различного рода информации, будь то прайс-лист или данные по продажам за период.
Существует несколько различных методов работы с файлами Excel при загрузке данных в 1С, в том числе:
1. загрузка с помощью свойства Cells;
2. загрузка с помощью метода Range и последующим обращением к данным через метод GetValue();
3. загрузка с помощью метода Range и последующим обращением к данным через свойство Value;
4. загрузка с помощью технологии ADO [2] и использованием компоненты GameWithFire [3].
Рассмотрим вышеперечисленные методы подробнее.
1. Загрузка данных с помощью свойства Cells
Этот метод используется в обработке «ЗагрузкаДанныхИзТабличногоДокумента.epf», которая присутствует на диске ИТС, и основан он на обращении к объекту Excel.Application через технологию COM с последующим последовательным чтением данных в ячейках обращением к свойству Cells.
Значение = ExcelЛист.Cells(Row,Column).Text;
Собственно, здесь все достаточно просто:
а) подключаемся к Excel
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.WorkBooks.Open(ИмяФайла);
ExcelЛист = Excel.Sheets(1);
Исключение
Сообщить("Ошибка. Возможно, неверно указан номер листа книги Excel.");
Возврат Неопределено;
КонецПопытки;б) получаем диапазон данных на листе:
ActiveCell = Excel.ActiveCell.SpecialCells(11);
RowCount = ActiveCell.Row;
ColumnCount = ActiveCell.Column;в) последовательно считываем данные из файла:
Для Row = 1 По RowCount-1 Цикл
Для Column = 0 По ColumnCount-1 Цикл
Значение = Я.item(Row,Column+1).Value;
// здесь код, который использует данное значение
КонецЦикла;
КонецЦикла;Несомненно, такой способ гарантирует получение всех данных, содержащихся на листе Excel, однако он имеет весьма существенный недостаток – большую длительность работы при загрузке файлов больших объемов.
2. Загрузка данных с помощью метода Range и последующим обращением к данным через метод GetValue()
Этот метод так же основан на возможностях объекта Excel.Application, но, в отличие от загрузки с использованием свойства Cells, позволяет получить сразу все значения в виде двумерного массива. Работает следующим образом:
а) подключаемся к Excel (аналогично методу 1);
б) определяем диапазон используемых ячеек (аналогично методу 1)
в) считываем данные с помощью метода Range(начальнаяЯчейка, конечнаяЯчейка). Здесь начальная и конечная ячейки – это элементы свойства Cells, о котором шла речь выше.
Диапазон = ExcelЛист.Range(Я.item(1,1), Я.item(RowCount,ColumnCount)).Value;В результате, в переменной Диапазон мы получим значение типа COMSafeArray, который представляет собой объектную оболочку над многомерным массивом SAFEARRAY. Иначе говоря, мы получаем массив, который, как известно, является областью памяти. А с областью в оперативной памяти работа всегда быстрее.
При этом надо учитывать, что массив этот многомерный. Для нашего случая это будет двумерный массив.
г) последовательно получаем значения из массива с помощью метода GetValue(столбец, строка)
Для Row = 1 По RowCount-1 Цикл
Для Column = 0 По ColumnCount-1 Цикл
Значение = Диапазон.getValue(Column+1, Row);
// здесь код, который использует данное значение
КонецЦикла;
КонецЦикла;
3. Загрузка данных с помощью метода Range и последующим обращением к данным через свойство Value
Этот метод отличается от предыдущего только тем, что вместо работы с объектом типа COMSafeArray мы сразу получаем набор значений в виде стандартного массива 1С (тип Массив).
Также, в приведенном листинге вместо метода Range используется свойство UsedRange, которое, по сути, идентично значению, возвращаемому методом Range для всей используемой области листа (это свойство не годится, если нужно прочитать только какую-то часть ячеек, в отличие от метода Range).
а) подключаемся к Excel (как и в предыдущих случаях)
б) считываем данные используя свойство UsedRange и метод Выгрузить()
Данные = ExcelЛист.UsedRange.Value.Выгрузить();В результате, получаем двумерный массив типа Массив
в) последовательно получаем значения из массива
Для Row = 1 По RowCount-1 Цикл
Для Column = 0 По ColumnCount-1 Цикл
Значение = Данные[Column], [Row];
// здесь код, который использует данное значение
КонецЦикла;
КонецЦикла;Еще одним преимуществом данного метода является то, что каждый элемент массива Данные представляет собой массив, содержащий значение одной колонки считанного файла. Тем самым, мы можем сразу получить все возможные значения определенной колонки в виде массива, просто обратившись к соответствующему элементу переменной Данные.
4. Загрузка данных с помощью технологии ADO и использованием компоненты GameWithFire
Данный способ использует технологию ADO [2], которая реализуется с помощью библиотеки ADOdb [4] и позволяет получать доступ к любым СУБД, реализующим технологию ODBC. Собственно, Excel попадает в данный список и, следовательно, мы можем получить данные, используя указанную технологию.
Также, для указанного метода используется компонента с оригинальным названием GameWithFire, которая перекладывает результат запроса через ADO в привычную таблицу значений.
Ниже приведен листинг части кода, который демонстрирует данную возможность. Пример во многом использует материал, описанный в источнике [1].
Запрос = "SEL ECT * FR OM [Лист1$]";
Попытка
ПодключитьВнешнююКомпоненту("GameWithFire.ADOUtils");
Исключение
ОбщегоНазначенияКлиентСервер.СообщитьПользователю("Компонента GameWithFire не подключена!");
КонецПопытки;
ADOUtils = Новый ("AddIn.ADOUtils");
Connection = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения="Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "+ИмяФайла;
СтрокаПодключения=СтрокаПодключения+"; Extended Properties = "+"""Excel 8.0"+";HDR=NO;IMEX=1"";";
Попытка
Connection.Open(СтрокаПодключения);
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
Command =Новый COMОбъект("ADODB.Command");
//Создание объекта набора записей
RecordSet =Новый COMОбъект("ADODB.RecordSet");
//Указание активного соединения
Command.ActiveConnection = Connection;
//передает текст запроса
Command.CommandText = Запрос;
//определение типа команды
Command.CommandType =1;
//Выполнение и получение набора данных
RecordSet = Command.Execute();
ТЗ = ADOUtils.ADORecordsetToValueTable(Recordset);
// После того, как набор записей уже не нужен, его нужно закрыть
RecordSet.Close();
Connection.Close();В результате, получаем таблицу значений ТЗ, которую можем обойти любым известным образом.
Заключение
В заключение, мы хотели бы привести таблицу сравнения скорости работы вышеописанных методов и рекомендации для использования.
Замеры производились на файле Excel с числом строк 16 000 и числом колонок 20. Таким образом, общее число ячеек в области составило 320 000. Значения в таблице говорят сами за себя.
Таблица 1. Время загрузки данных из Excel в 1С при использовании различных методов
|
Метод загрузки |
Длительность, с |
|---|---|
|
С помощью Cells |
996 |
|
С помощью Range() и GetValue() |
38 |
|
С помощью Range() и свойство Value |
32 |
|
С помощью ADO |
3,3 |
Рекомендации по использованию:
1. Чтение с помощью Cells – когда нужно прочитать значения конкретных ячеек или небольших, несмежных областей.
2. Чтение с помощью Range – всегда, кроме случаев с очень большими файлами (сотни тысяч строк), так как в этом случае не нужно ничего, кроме Excel.
3. В случае, когда файлы очень большие, либо требуется особая выборка (связано это с тем, что технология ADO позволяет выполнять запрос на языке SQL к СУБД, а это весьма расширяет круг задач), так как требуется дополнительная компонента, да и строка соединения будет зависеть от версии используемого ПО.