Быстрая функция чтения данных с листа Excel

22.10.14

Разработка - Универсальные функции

Прочитал статью "Универсальное решение работы с Ексель". И опять чтение происходит путем перебора ячеек листа Ексель. Для больших файлов это очень медленно. Решил просмотреть ВСЕ обработки из рубрики 1С + Excel...

Прочитал статью "Универсальное решение работы с Ексель". И опять чтение происходит путем перебора ячеек листа Ексель. Для больших файлов это очень медленно. Решил просмотреть ВСЕ обработки из рубрики 1С + Excel с целью определить - стоит ли писать о способе чтения Excel файла которым сам пользуюсь.

Итог - почти во всех обработках происходит чтение файла путем перебора каждой ячейки выбранного диапазона листа Ексель. Только в //infostart.ru/projects/3962/ - использует  вариантный массив, а также чтение с использованием ADO. И то вариантный массив читается поячеечно.

Много обработок с закрытым кодом - о них ничего сказать не могу. 

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

Итак. При чтении файла Excel я использую вариантный массив. Он позволяет быстро получить ВСЮ таблицу листа в память, а также получать данные массива целыми колонками. Тем самым время на чтение области файла Excel в таблицу значений сокращается в десятки раз.

Вот текст функции.

Функция ПрочитатьЛистExcel(ТЗ = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт

Если ЛистЭксель = Неопределено Тогда
   
ЛистЭксель = ПолучитьCOMОбъект(,"Excel.Application");
КонецЕсли;
Если
ВсегоСтрок = 0 Тогда
   
ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
КонецЕсли;
Если
ВсегоКолонок = 0 Тогда
   
ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;
КонецЕсли;
Если
ТЗ = Неопределено Тогда
   
ТЗ =  Новый ТаблицаЗначений;
    Для
Счетчик = 1 По ВсегоКолонок Цикл
       
ТЗ.Колонки.Добавить("Колонка"+Счетчик, Новый ОписаниеТипов("Строка"));
    КонецЦикла;
КонецЕсли;
Для
Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл
   
НоваяСтрока = ТЗ.Добавить();
КонецЦикла;

Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

Для
Счетчик = 0 По ВсегоКолонок-1 Цикл
   
ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;
ЛистЭксель = Неопределено;
Возврат
ТЗ;
КонецФункции

 

Комментировать функцию, думаю, нет необходимости. Вот собственно и все, что я хотел сказать. 

 

 

 

Excel ComSaveArray

См. также

Вставляем картинку из буфера обмена (платформа 1С 8.3.24)

Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Задача: вставить картинку из буфера обмена на форму средствами платформы 1С.

1 стартмани

18.03.2024    2659    0    John_d    8    

53

GUID в 1С 8.3 - как с ними быть

Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Пришлось помучиться с GUID-ами немного, решил поделиться опытом, мало ли кому пригодится.

12.02.2024    4595    atdonya    22    

45

Переоткрытие внешних обработок

Универсальные функции Платформа 1С v8.3 Бесплатно (free)

На заключительных этапах, когда идет отладка или доработка интерфейса, необходимо много раз переоткрыть внешний объект. Вот один из способов автоматизации этого.

30.11.2023    3953    ke.92@mail.ru    16    

61

Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    8802    YA_418728146    6    

141

Печать непроведенных документов для УТ, КА, ERP. Настройка печати по пользователям, документам и печатным формам

Пакетная печать Печатные формы Адаптация типовых решений Универсальные функции Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Абонемент ($m)

Расширение для программ 1С:Управление торговлей, 1С:Комплексная автоматизация, 1С:ERP, которое позволяет распечатывать печатные формы для непроведенных документов. Можно настроить, каким пользователям, какие конкретные формы документов разрешено печатать без проведения документа.

2 стартмани

22.08.2023    2071    21    progmaster    7    

3

Расширение: Быстрые отборы через буфер [Alt+C] Копировать список, [Alt+V] Вставить список, [Ctrl+C] Копировать из файлов

Инструментарий разработчика Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x Абонемент ($m)

Копирует в буфер значения из списков, из ячеек отчетов, таблиц, настроек списков, других отборов и вставляет в выбранную настройку отбора. Работает с Объект не найден. Работает как в одной так и между разными базами 1С. Использует комбинации [Alt+C] Копировать список, [Alt+V] Вставить список. Также для копирования данных используется стандартная [Ctrl+C] (например из открытого xls, mxl, doc и т.п. файла скопировать список наименований)

1 стартмани

13.10.2022    16139    133    sapervodichka    112    

129

Система контроля ведения учета [БСП]

Универсальные функции Механизмы типовых конфигураций БСП (Библиотека стандартных подсистем) Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

В данном материале рассмотрим типовой алгоритм подсистемы контроля учета БСП в конфигурациях на примерах.

18.07.2022    7241    quazare    8    

109
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
86. wildhog 469 12.04.13 17:18 Сейчас в теме
(85) Eugeneer, не стал писать про деньги )) Для многих это существенный минус...
Надеюсь продажи Вашей обработки говорят об обратном ))
90. kiruha 388 24.02.14 10:00 Сейчас в теме
(82)
Здорово конечно, но приведенный автором статьи алгоритм + дополнение по записи от kadr полностью покрывает все проблемы - 0.4 сек на 80 000 ячеек.
В дальнейшем Вам стоит указывать и на это альтернативное решение
87. uri1978 137 24.05.13 12:36 Сейчас в теме
Отличный метод. Автору большой плюс!
88. MusaRB 14.10.13 12:55 Сейчас в теме
Спасибо автору! Работает действительно очень быстро.
VyacheslavShilov; +1 Ответить
89. ram3 165 24.02.14 09:43 Сейчас в теме
Подскажите, как, используя данную функцию, получить значения приведенными к строке, как это бывает при использовании Лист.Cells(i,j).Text.
Область.NumberFormat = "@"; - не помогает.
Проблема в том, что например вот такую строку "0000483159" получаю в виде числа 483 159, не знаю как победить...
100. kuza_87 28 05.03.14 13:11 Сейчас в теме
Интересно, а можно как-нибудь наоборот.Создать Таблицу значений и быстро выгрузить её в Эксель???
101. KillHunter 7 05.03.14 13:36 Сейчас в теме
ну как вариант:
Запрос=Новый Запрос("ВЫБРАТЬ
| Товары.*
|ИЗ
| Документ.ПоступлениеТоваровУслуг.Товары КАК Товары
|ГДЕ
| Товары.Ссылка = &ПоступлениеСсылка");
Запрос.УстановитьПараметр("ПоступлениеСсылка",Ссылка); // Подставить ссылку на твой документ

Таблица=Запрос.Выполнить().Выгрузить();

Таб=Новый ТабличныйДокумент();
КоличествоКолонок=Таблица.Колонки.Количество();
// "Шапка"

Для НомерКолонки=2 по КоличествоКолонок цикл
Таб.Область(1,НомерКолонки).Текст=Таблица.Колонки[НомерКолонки-1].Имя;
КонецЦикла;
// Строки

НомерСтроки=2;
Для каждого Строка Из Таблица цикл
Для НомерКолонки=2 по КоличествоКолонок цикл
Таб.Область(НомерСтроки,НомерКолонки).Текст=Строка[НомерКолонки-1];
КонецЦикла;
НомерСтроки=НомерСтроки+1;
КонецЦикла;
ИмяФайла="D:\Blabla.xls"; // <--- Указать имя твоего файла

Таб.Записать(ИмяФайла,ТипФайлаТабличногоДокумента.XLS);
jif; y.dyachenko; +2 Ответить
102. Feelthis 38 23.07.14 11:03 Сейчас в теме
Подскажите пожалуйста - в данном методе загрузка идет в таб значений, но она не доступна на клиенте. Excel у нас установлен только на клиенте - на сервере его нет. То есть чтение Excel можно сделать (как я понимаю) только на клиенте. Как быть в этом случае?
103. wildhog 469 23.07.14 11:21 Сейчас в теме
В этом случае вам нужно немного переделать функцию.
Чтение данных с листа в comsafearray выполнять на клиенте:
Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();

А все остальное связанное с таблицей значений выполнять уже на сервере, передавая на сервер результат чтения (в функции это переменная "Данные").

Если сделаете, пришлите результат, обновлю статью.
104. TrinitronOTV 14 23.07.14 15:11 Сейчас в теме
(103) а вот мне необходимо кроме самих значений с листа прочитывать цвет шрифта, то как может использоваться ваш метод (цвет используется для определения участия данного значения в расчетах или не участи в нём )?
105. wildhog 469 23.07.14 17:36 Сейчас в теме
(104) Приведенная функция предназначена в основном для чтения больших файлов, когда критична скорость выполнения.
Для чтения данных о шрифтах я думаю будет достаточно посмотреть свойства ячейки с данными.
Как к ним добраться можно прочитать на форуме, например тут, или тут
106. Feelthis 38 24.07.14 17:24 Сейчас в теме
(103) может кому процедура пригодиться (тем у кого Excel установлен только на клиенте и чтение происходит в табличный документ на форме):

&НаСервере
Процедура ОбработатьДанныеНаСервере(Данные, ВсегоСтрок, ВсегоКолонок)

	ТЗ =  Новый ТаблицаЗначений;
	
	Для Счетчик = 1 По ВсегоКолонок Цикл
		ТЗ.Колонки.Добавить("Колонка"+Счетчик, Новый ОписаниеТипов("Строка"));
	КонецЦикла;
	
	Для Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл
		НоваяСтрока = ТЗ.Добавить();
	КонецЦикла;
	
	Для Счетчик = 0 По ВсегоКолонок-1 Цикл
		ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
	КонецЦикла;
	
	//- здесь готова ТЗ для обработки в таб доке
	
	Для НомерСтроки = 1 По ТЗ.Количество() Цикл
		
		Для НомерКолонки = 1 По ТЗ.Колонки.Количество() Цикл
			ТабличныйДокумент.Область("R" + Формат(НомерСтроки, "ЧГ=") +"C" + Формат(НомерКолонки, "ЧГ=")).Текст = ТЗ[НомерСтроки-1]["Колонка"+(НомерКолонки)];
		КонецЦикла;
		
	КонецЦикла;	
	
КонецПроцедуры
Показать
igormiro; wildhog; +2 Ответить
107. DenisKin 20 04.12.14 15:38 Сейчас в теме
Супер функция! Ускорил загрузку файла с 65000 строк и 20 колонками, где-то раз в 10. Спасибо огромное!
108. Nucky 35 25.12.14 11:35 Сейчас в теме
Попробовал открыть файл в 170 с лишним тыс. строк и 30 столбцами, отладчик выдал, Value - чтение невозможно. Поставил "ВсегоСтрок" 100 тыс. процесс пошел.
109. Nucky 35 26.12.14 03:59 Сейчас в теме
111. Администратор 1С 41 21.04.15 07:49 Сейчас в теме
А тут ошибки нет случайно:

Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));

В этой строке параметры ВсегоСтрок,ВсегоКолонок - это номер последней строки и номер последного столбца.


А в этой:

Для Счетчик = 0 По ВсегоКолонок-1 Цикл
		ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
КонецЦикла;


ВсегоКолонок - это количество колонок
113. a-novoselov 1155 30.04.15 12:39 Сейчас в теме
(111)
Нет, тут нет ошибки. Используется именно загрузка всей колонки за одну операцию: ЗагрузитьКолонку(Массив значений, Номер колонки).
112. artfa 58 21.04.15 20:29 Сейчас в теме
Спасибо, ускорил чтение из екселя в десятки раз.
114. marinelle 26.05.15 12:00 Сейчас в теме
Доброго всем дня.
Подскажите ответ, если кто сталкивался.
Нужно загрузить файл ексель в 1С. Проблем с этим нет, если у пользователя достаточно прав на создание COMОбъект-а на уровне виндового сервера или локальной машины.
А если этих прав нет и при попытке загрузить вылетает ошибка на этой строке Excel = Новый COMОбъект("Excel.Application"); в сообщениях пишет "Ошибка при вызове конструктора ComОбъект".
Админские права на локальную машину или сервера для создания COMОбъект никто давать пользователям не будет.
===============
Как с этим бороться???
115. wildhog 469 26.05.15 15:35 Сейчас в теме
(114)
Насколько помню, админские права для создания com объекта не требуются. У моих пользователей их точно нет.
Обычно, если пользователь, выполняющий создание COMОбъект-а, может на том же компьютере запустить полноценный Excel, то никаких проблем с правами быть не должно.
116. marinelle 27.05.15 11:57 Сейчас в теме
(115) добавили пользователю права на сервере, все заработало, без доп.прав на сервере вылетает ошибка.
Я так же первый раз с этим столкнулась (((
120. Morlok 04.05.16 21:02 Сейчас в теме
Вот спасибо, добрый человек, 30 минут превратились в 30 секунд ;)
121. lidia3004 04.10.16 15:27 Сейчас в теме
А что делать, если выдает сообщение "Значение не является значением объектного типа(Выгрузить)"?
122. wildhog 469 05.10.16 09:16 Сейчас в теме
(121) Наверное указан не верный диапазон данных. С какими параметрами вызываете функцию?
123. serg-lom89 72 12.10.17 11:19 Сейчас в теме
просто шикарно..моментально загрузило!спасибо)
125. strange2007 144 17.06.21 13:44 Сейчас в теме
Отличный алгоритм, но автор, уверяю, большие таблицы подразумевают группировку счётчиков и надо использовать Формат, когда создаётся новое имя колонки. Оно вроде мелочь, но всё равно
126. wildhog 469 20.06.21 15:03 Сейчас в теме
(125) Если известны форматы колонок, то да, конечно, нужно типизировать колонки таблицы значений. Хотя бы для использования в запросах, там без этого никак.
Приведенная функция только иллюстрация как максимально быстро прочитать данные из Excel просто зная (или не зная) нужный диапазон данных.
Про группировку счетчиков не понял...
Оставьте свое сообщение