1С:Вопросы и ответы » Загрузка файлов Excel. Весь лист Excel в память

1С:Вопросы и ответы > 1С:Ответы на вопросы > Загрузка файлов Excel. Весь лист Excel в память

Загрузка файлов Excel. Весь лист Excel в память

29.10.2018

Загрузка файлов Excel

Примеры программного кода для загрузки данных из файлов Excel в информационную базу 1С. Пример загрузки всего листа из Excel.

&НаСервере
Процедура ИсправитьСтудентовНаСервере(ИмяФайла)
н = 1;
п = 1;
а = 1;
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.WorkBooks.Open(ИмяФайла);
Сообщить("Обработка файла Microsoft Excel...");
Исключение
Сообщить("Ошибка при открытии файла с помощью Excel! Загрузка не будет произведена!");
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;

Попытка
Excel.Sheets(1).Select();
ЛистExcel= Excel.Sheets(1);
Исключение
Excel.ActiveWorkbook.Close();
Excel = 0;
Сообщить("Файл "+Строка(ИмяФайла)+" не соответствует необходимому формату! Первый лист не найден!");
ОтменитьТранзакцию();
Возврат;
КонецПопытки;

Версия = Лев(Excel.Version,Найти(Excel.Version,".")-1);
Если Версия = "8" Тогда
КолСтрок = Excel.Cells.CurrentRegion.Rows.Count;
КолКолонок = Макс(Excel.Cells.CurrentRegion.Columns.Count, 13);
Иначе
КолСтрок = Excel.Cells(1,1).SpecialCells(11).Row;
КолКолонок = Excel.Cells(1,1).SpecialCells(11).Column;
Конецесли;

Для Строка = 1 по КолСтрок Цикл

ФИО= СОкрЛП(Строка(ЛистExcel.Cells(Строка,1).Value));

Отдельно = СтрЗаменить(ФИО, " ", Символы.ПС); 
Фамилия = СтрПолучитьСтроку(Отдельно, 1);
Имя = СтрПолучитьСтроку(Отдельно, 2);
Отчество = СтрПолучитьСтроку(Отдельно, 3);
Отчество2 = СтрПолучитьСтроку(Отдельно, 4);
Если ЗначениеЗаполнено(Отчество2) Тогда
//Сообщить(" !!! Пропущен: " + Фамилия + " " + Имя + " " + Отчество + " " + Отчество2);
п=п+1;
Иначе

Если ЗначениеЗаполнено(СокрЛП(Строка(ЛистExcel.Cells(Строка,2).Value))) Тогда

ЛистExcel.Cells(Строка, 2).NumberFormat ="ДД.ММ.ГГГГ";
ДатаРождения = СокрЛП(Строка(ЛистExcel.Cells(Строка,2).Value));
ДатаРождения = СтрЗаменить(СокрЛП(ДатаРождения), ".", "");
ДатаРождения = Лев(ДатаРождения, 8);
ГодР = Прав(СокрЛП(ДатаРождения), 4);
МесяцР = Сред(СокрЛП(ДатаРождения), 3, 2);
ЧислоР = Лев(СокрЛП(ДатаРождения), 2);
ДатаРождения = Дата(ГодР, МесяцР, ЧислоР); 

КонецЕсли;

ФизическоеЛицо= ПолучитьСсылкуНаФизлицоДР(Фамилия, Имя, Отчество, ДатаРождения);
Если ЗначениеЗаполнено(ФизическоеЛицо) Тогда

ФизическоеЛицоОбъект= ФизическоеЛицо.ПолучитьОбъект();
Если НЕ ФизическоеЛицоОбъект.Сотрудник= ПолучитьСтатус("Студент") Тогда
Сообщить(Строка(а) + " Не заполнено поле Студент: " + ФизическоеЛицо);
а=а+1;
КонецЕсли;
КонецЕсли; 
КонецЕсли; 
н=н+1; 
КонецЦикла;

Excel.DisplayAlerts = 0; 
Excel.Quit();
Excel.DisplayAlerts = 1;

Сообщить(">>Всего загружено данных:" + н + " шт.");
Сообщить(">>Всего из них пропущено:" + п + " шт.");
КонецПроцедуры

&НаКлиенте
Процедура ИсправитьСтудентов(Команда)

Выбор = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Выбор.Заголовок = "Выберите файл фомата XLS(XLSX) с данными обучающихся";
Выбор.ПолноеИмяФайла = "";
Выбор.МножественныйВыбор = Ложь;
Выбор.Фильтр = "Файл XLS(*.xls)|*.xls";
Если Выбор.Выбрать() Тогда
Путь= Выбор.Каталог; 
Имя= Выбор.ПолноеИмяФайла;
КонецЕсли; 

Попытка
Объект.трПуть = Путь;
РежимВопроса = РежимДиалогаВопрос.ДаНетОтмена;
Ответ = Вопрос("Загрузить данные?", РежимВопроса, 0);
Если Ответ = КодВозвратаДиалога.Да Тогда
ИсправитьСтудентовНаСервере(Имя);
КомандаСистемы("taskkill /F /IM excel.exe");
КонецЕсли;
Сообщить(">>Обработан файл: " + Имя);
Исключение
Сообщить("!Возникли проблемы при обработке файла: " + Имя);
КонецПопытки;

КонецПроцедуры

100

101

102

103

104

105

106

107

108

109

110

&НаСервере

Процедура ИсправитьСтудентовНаСервере(ИмяФайла)

н = 1;

п = 1;

а = 1;

Попытка

Excel = Новый COMОбъект("Excel.Application");

Excel.WorkBooks.Open(ИмяФайла);

Сообщить("Обработка файла Microsoft Excel...");

Исключение

Сообщить("Ошибка при открытии файла с помощью Excel! Загрузка не будет произведена!");

Сообщить(ОписаниеОшибки());

Возврат;

КонецПопытки;

Попытка

Excel.Sheets(1).Select();

ЛистExcel= Excel.Sheets(1);

Исключение

Excel.ActiveWorkbook.Close();

Excel = 0;

Сообщить("Файл "+Строка(ИмяФайла)+" не соответствует необходимому формату! Первый лист не найден!");

ОтменитьТранзакцию();

Возврат;

КонецПопытки;

Версия = Лев(Excel.Version,Найти(Excel.Version,".")-1);

Если Версия = "8" Тогда

КолСтрок = Excel.Cells.CurrentRegion.Rows.Count;

КолКолонок = Макс(Excel.Cells.CurrentRegion.Columns.Count, 13);

Иначе

КолСтрок = Excel.Cells(1,1).SpecialCells(11).Row;

КолКолонок = Excel.Cells(1,1).SpecialCells(11).Column;

Конецесли;

Для Строка = 1 по КолСтрок Цикл

ФИО= СОкрЛП(Строка(ЛистExcel.Cells(Строка,1).Value));

Отдельно = СтрЗаменить(ФИО, " ", Символы.ПС);

Фамилия = СтрПолучитьСтроку(Отдельно, 1);

Имя = СтрПолучитьСтроку(Отдельно, 2);

Отчество = СтрПолучитьСтроку(Отдельно, 3);

Отчество2 = СтрПолучитьСтроку(Отдельно, 4);

Если ЗначениеЗаполнено(Отчество2) Тогда

//Сообщить(" !!! Пропущен: " + Фамилия + " " + Имя + " " + Отчество + " " + Отчество2);

п=п+1;

Иначе

Если ЗначениеЗаполнено(СокрЛП(Строка(ЛистExcel.Cells(Строка,2).Value))) Тогда

ЛистExcel.Cells(Строка, 2).NumberFormat ="ДД.ММ.ГГГГ";

ДатаРождения = СокрЛП(Строка(ЛистExcel.Cells(Строка,2).Value));

ДатаРождения = СтрЗаменить(СокрЛП(ДатаРождения), ".", "");

ДатаРождения = Лев(ДатаРождения, 8);

ГодР = Прав(СокрЛП(ДатаРождения), 4);

МесяцР = Сред(СокрЛП(ДатаРождения), 3, 2);

ЧислоР = Лев(СокрЛП(ДатаРождения), 2);

ДатаРождения = Дата(ГодР, МесяцР, ЧислоР);

КонецЕсли;

ФизическоеЛицо= ПолучитьСсылкуНаФизлицоДР(Фамилия, Имя, Отчество, ДатаРождения);

Если ЗначениеЗаполнено(ФизическоеЛицо) Тогда

ФизическоеЛицоОбъект= ФизическоеЛицо.ПолучитьОбъект();

Если НЕ ФизическоеЛицоОбъект.Сотрудник= ПолучитьСтатус("Студент") Тогда

Сообщить(Строка(а) + " Не заполнено поле Студент: " + ФизическоеЛицо);

а=а+1;

КонецЕсли;

н=н+1;

КонецЦикла;

Excel.DisplayAlerts = 0;

Excel.Quit();

Excel.DisplayAlerts = 1;

Сообщить(">>Всего загружено данных:" + н + " шт.");

Сообщить(">>Всего из них пропущено:" + п + " шт.");

КонецПроцедуры

&НаКлиенте

Процедура ИсправитьСтудентов(Команда)

Выбор = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);

Выбор.Заголовок = "Выберите файл фомата XLS(XLSX) с данными обучающихся";

Выбор.ПолноеИмяФайла = "";

Выбор.МножественныйВыбор = Ложь;

Выбор.Фильтр = "Файл XLS(*.xls)|*.xls";

Если Выбор.Выбрать() Тогда

Путь= Выбор.Каталог;

Имя= Выбор.ПолноеИмяФайла;

КонецЕсли;

Попытка

Объект.трПуть = Путь;

РежимВопроса = РежимДиалогаВопрос.ДаНетОтмена;

Ответ = Вопрос("Загрузить данные?", РежимВопроса, 0);

Если Ответ = КодВозвратаДиалога.Да Тогда

ИсправитьСтудентовНаСервере(Имя);

КомандаСистемы("taskkill /F /IM excel.exe");

КонецЕсли;

Сообщить(">>Обработан файл: " + Имя);

Исключение

Сообщить("!Возникли проблемы при обработке файла: " + Имя);

КонецПопытки;

КонецПроцедуры

или весь лист 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(ТЗ = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт

Если ЛистЭксель = Неопределено Тогда

ЛистЭксель = ПолучитьCOMОбъект(,"Excel.Application");

КонецЕсли;

Если ВсегоСтрок = 0 Тогда

ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;

КонецЕсли;

Если ВсегоКолонок = 0 Тогда

ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;

КонецЕсли;

Если ТЗ = Неопределено Тогда

ТЗ = Новый ТаблицаЗначений;

Для Счетчик = 1 По ВсегоКолонок Цикл