Импорт из Excel в 1С - импорт таблицы Excel в 1С (любого формата при помощи OLE DB)

из Excel в 1С

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

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

Для понимания материала рекомендую прочесть предыдущую статью Из Excel в 1С, получение списка листов (имен листов) из файла Excel.

В предыдущей статье есть ссылка на скачивание свежей версии драйверов MDAC, которые вы можете бесплатно установить на любой компьютер, чтобы импорт таблиц из Excel в 1С поддерживался для формата последней версии Microsoft Office (в состав которого входит и Excel).

Итак, вот перед вами код функции ФайлExcel_ИмпортироватьЛист()

// Функция импортирует (загружает данные) из указанного листа файла в формате Microsoft Excel в таблицу значений 1С
// Возвращается таблица значений, содержащая импортированные данные, либо Неопределено, если импорт данных не удался.
// Входные параметры:
//    - строка, полный путь и имя файла рабочей книги Excel, с расширением xls, xlsx
//    - строка или число, имя листа в рабочей книге (должно заканчиваться символом "$"), либо порядковый номер листа
//                        в книге Excel. Нумерация листов начинается с 1.
//    - тип булево, "Истина" или "Ложь".
//                                Если "Истина", тогда в качестве имен колонок таблицы значений (таблицы результата)
//                             будут использованы заголовки столбцов в исходном листе Excel. В этом случае первая строка импортируемого листа
//                             обязательно должна содержать заголовки в каждом столбце, и текст заголовка должен соответствовать правилам
//                             наименования идентификаторов в языке 1С (последовательность букв, цифр и знаков подчеркивания,
//                             начинающаяся только с букв или знака подчеркивания)
//                                Если "Ложь", тогда имена колонок результирующей таблицы значений будут сформированы автоматически, по принципу
//                             F1, F2...Fn В этом случае первая строка импортируемого листа может быть любой, к ней не предъявляется никаких
//                             требований.
//  Возвращаемая таблица значений будет содержать в себе строки и колонки, заполненные данными, в том же порядке, соответствующем строкам
//  и колонкам в исходном листе Excel
Функция ФайлExcel_ИмпортироватьЛист(ИмяФайла, ИмяИлиНомерЛиста = 1, ИспользоватьЗаголовок = Ложь)
  adFalse = 0;
  // формируем строку подключения к OLE DB источнику данных с именем "Microsoft.ACE.OLEDB.12.0"
  HDR = ?(ИспользоватьЗаголовок, "Yes", "No");
  СтрокаСоединения = ФорматСтроки("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%1; Extended Properties=""Excel 12.0;HDR=%2""", ИмяФайла, HDR);
  // если лист задан не именем, а номером, требуется сначала найти имя листа
  Если ТипЗнч(ИмяИлиНомерЛиста) = Тип("Число") Тогда
    // проверяем, чтобы номер листа был не меньше единицы
    Если ИмяИлиНомерЛиста < 1 Тогда
      Сообщить("Ошибка в параметре. Номер листа не может быть меньше единицы");
      Возврат Неопределено;
    КонецЕсли;
    // получаем массив всех листов в книге Excel
    МассивИменЛистов = ФайлExcel_ПолучитьСписокЛистов(ИмяФайла);
    // извлекаем из массива имен листов имя нужного листа (по его номеру, переданному при вызове функции)
    Если МассивИменЛистов  Неопределено Тогда
      Если ИмяИлиНомерЛиста > МассивИменЛистов.Количество() Тогда
        Сообщить("Ошибка. Номер запрошенного листа больше, чем общее число листов в книге Excel");
        Возврат Неопределено;
      КонецЕсли;
      ИмяЛиста_ = МассивИменЛистов[ИмяИлиНомерЛиста - 1]; // запоминаем имя листа для дальнейшего чтения данных
    Иначе
      Сообщить("Ошибка. Не удалось получить имя листа в книге Excel по его номеру");
      Возврат Неопределено;
    КонецЕсли;  
  Иначе
    // проверяем, чтобы последним символом в имени листа был знак $
    Если Прав(ИмяИлиНомерЛиста, 1)  "$" Тогда
      Сообщить("Ошибка в параметре. Имя листа должно оканчиваться знаком доллара '$'");
      Возврат Неопределено;
    КонецЕсли;  
    ИмяЛиста_ = ИмяИлиНомерЛиста; // запоминаем имя листа для дальнейшего чтения данных
  КонецЕсли;  
  // формируем текст запроса к источнику данных книги Excel. По-сути это SQL запрос с выборкой всех столбцов и строк указанной таблицы (листа)
  // В языке SQL такой запрос не гарантирует никакой порядок следования записей, но на практике, в нашем случае,
  // из листа Excel строки будут выбраны в правильном, последовательном порядке, от 1-ой строки к последней
  ТекстКоманды = ФорматСтроки("SELECT * FROM [%1]", ИмяЛиста_);
  // создаем специальный com-объект ADODB.Recordset, который будет содержать выбранные данные (некий аналог таблицы значений 1С)
  Recordset = Новый COMОбъект("ADODB.Recordset");
  Попытка
    // заставляем Recordset выполнить запрос к данным. Текст запроса в переменной ,
    // а параметры соединения указаны в строке , сформированной в начале функции
    // После успешного выполнения запроса объект Recordset содержит в себе копию данных из листа Excel
    // т.е. имеет внутри себя строки и колонки с данными.
    Recordset.Open(ТекстКоманды, СтрокаСоединения);
  Исключение
    Сообщить(ОписаниеОшибки());
    Возврат Неопределено;
  КонецПопытки;
  ТЗ = Новый ТаблицаЗначений;
  КоличествоКолонок = Recordset.Fields.Count; // получаем количество колонок с данными, после выполнения запроса
  // Создаем колонки результирующей таблицы значений, копируя имена колонок из объекта Recordset
  // Имена колонок либо автоматически сформированы системой, либо взяты из имен столбцов листа Excel
  // Это зависит от вх.параметра 
  Для НомерКолонки = 0 По КоличествоКолонок - 1 Цикл
    ТЗ.Колонки.Добавить(Recordset.Fields(НомерКолонки).Name);
  КонецЦикла;
  // перебираем записи(строки) объекта Recordset
  Пока Recordset.EOF() = adFalse Цикл
    НоваяСтрока = ТЗ.Добавить();
    //  копируем данные каждого столбца (поля) в новую строку таблицы значений
    Для НомерКолонки = 0 По КоличествоКолонок - 1 Цикл
      НоваяСтрока[НомерКолонки] = Recordset.Fields(НомерКолонки).Value;
    КонецЦикла;
    Recordset.MoveNext(); // переходим на следующую запись(строку) объекта Recordset
  КонецЦикла;  
  Возврат ТЗ;
КонецФункции

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

Наша функция ФайлExcel_ИмпортироватьЛист() извлекает информацию для импорта из Excel в 1С с одного конкретного листа. Поэтому, кроме имени файла-таблицы Excel , мы должны передать в функцию еще и имя нужного нам листа. Имя листа в Excel всегда заканчивается знаком "$", например "Лист1$", когда это имя используется для ссылки на нужный лист.

Чаще всего бывает так, что нужная нам информация находится на первом, по счету, листе файла Excel, поэтому вместо имени в функцию можно передать порядковый номер листа - 1. Ну или любой другой номер, если вам известно, на каком, по-порядку, листе находится интересующие данные. (Важно! Иногда нумерация листов в книге Excel не соответствует видимому порядку отображения листов при открытии книги. Например, лист, который вы видите первым по-счету, может иметь номер 4 или другой, не равный единице. Вам надо проверять это уже в процессе работы с конкретным файлом, или вместо номера использовать имя листа)

Мы имеем, таким образом, некую универсальность, при выборе нужного листа с данными для импорта из Excel в 1С. Это достигается тем, что изнутри функции ФайлExcel_ИмпортироватьЛист() вызывается другая функция, ФайлExcel_ПолучитьСписокЛистов() (описанная в прошлой статье), благодаря которой извлекается имя листа, соответствующее переданному номеру листа. А далее, запрос на чтение выстраивается с использованием заранее полученного имени листа Excel.

Функция ФайлExcel_ИмпортироватьЛист() имеет еще и третий параметр. Дело в том, что возвращаемая после импорта из Excel в 1С таблица значений имеет колонки. Разумеется, эти колонки должны иметь какие-то имена. Для управления наименованием колонок и нужен третий параметр, который я назвал ИспользоватьЗаголовок.

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

Надеюсь, что не запутал Вас. В случае, если мы присвоим параметру ИспользоватьЗаголовок = Ложь, результирующая таблица значений получить стандартные имена колонок, по порядку: "F1, F2 .. Fn" и первая строка исходного листа будет импортирована в первую строку таблицы значений.

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

Исходный файл-пример, чтобы показать импорт из Excel в 1С

Такой способ представляется мне более естественным, поэтому по умолчанию я и назначил ИспользоватьЗаголовок = Ложь. Следует еще и учесть, что имена колонок в таблице значений 1С должны строго соответствовать правилам именования идентификаторов 1С (не содержать пробелы и т.д.)

И если мы выберем режим использования заголовков (ИспользоватьЗаголовок = Истина), а содержимое ячеек первой строки исходного листа Excel не будет соответствовать правилам 1С, то функция вызовет исключение и аварийно завершится при попытке создания колонок таблицы значений, с "кривыми" именами.

Выше был размещен исходный текст отдельно взятой функции ФайлExcel_ИмпортироватьЛист().

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

Тестовая обработка по импорту из Excel в 1С

Тестовая обработка по импорту из Excel в 1С

В исходном коде тестовой обработки вы найдете комментарии, описывающие работу функций. Я надеюсь, что комментариев будет достаточно для понимания принципа работы функции импорта из Excel в 1С.

Если комментариев внутри исходного кода недостаточно, то пишите вопросы в комментариях к этой статье. Хотя, функцию можно с успехом использовать и без понимания принципов ее работы. Бывает, зачастую, просто некогда детально разбираться в вопросе, лишь бы "быстрее заработало". Тоже вариант! :)

Скачать тестовую обработку по импорту таблиц из Excel в 1С

КАК НАУЧИТЬСЯ ПРОГРАММИРОВАТЬ в 1С С НУЛЯ ?

КАК ЗАРАБАТЫВАТЬ до 150 000 РУБЛЕЙ В МЕСЯЦ?

ЗАПИШИСЬ НА БЕСПЛАТНЫЙ МИНИ-КУРС

ПРОГРАММИРОВАНИЕ в 1С

ДЛЯ НОВИЧКОВ !

Курс придет на электронную почту. Стань программистом, выполняя пошаговые задания.

Для участия нужен только компьютер и интернет

Бесплатный доступ на курс:



Комментарии для тех, кто VKontakte


8 комментариев: Импорт из Excel в 1С - импорт таблицы Excel в 1С

  • Ву говорит:

    Ошибка при вызове метода контекста (Open): Произошла исключительная ситуация (ADODB.Recordset): Не удается найти указанный поставщик. Вероятно, он установлен неправильно.

  • Ву говорит:

    все, отбой.
    теперь "Recordset.Open(ТекстКоманды, СтрокаСоединения)" не выполняется :(

  • Ву говорит:

    Спасибо, полезная вещь, но возникла одна проблемка: 1с спотыкается на функции ФорматСтроки.. как сделать7 :?:

Оставить комментарий

Ваш email не будет опубликован. Обязательные поля отмечены *

Вы можете использовать это HTMLтеги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Интернет-курс "Программирование в 1С с нуля"

Учись программировать и зарабатывать до
150 000 руб. в месяц! ТОЛЬКО СЕГОДНЯ - БЕСПЛАТНЫЙ ДОСТУП К КУРСУ!

Присоединяйся, тут все свои!