Excel — основные методы и конструкции языка, подключение через COM — соединение (1С: v8 и v7: Программисту: Язык программирования) — 1C-h

Содержание

Обмен через табличный документ

Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:

  • Записать(<ИмяФайла>, <ТипФайлаТаблицы>) для выгрузки данных в файл;
  • Прочитать(<ИмяФайла>, <СпособЧтенияЗначений>) для загрузки данных из файла.

Внимание!

Метод Записать() доступен как на клиенте, так и на сервере. Метод Прочитать() доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.

Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:

ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);

Здесь ТабДок — сформированный табличный документ, ПутьКФайлу — имя файла для выгрузки, ТипФайлаТабличногоДокумента.XLSX — формат создаваемого файла. Поддерживаются следующие форматы Excel:

  • XLS95 — формат Excel 95;
  • XLS97 — формат Excel 97;
  • XLSX — формат Excel 2007.

Загрузка из файла осуществляется также достаточно просто:

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);

Здесь ПутьКФайлу — путь к загружаемому файлу Excel. СпособЧтенияЗначенийТабличногоДокумента.Значение определяет, каким образом нужно интерпретировать данные, считываемые из исходного документа. Доступны варианты:

  • Значение;
  • Текст.

Ответственность и соблюдение сроков

мы с особой тщательностью относимся
к этапу планирования

Все работы ведутся строго по регламентам,
благодаря этому все этапы работы завершаются в срок

Персональный
менеджер

осуществляющий контроль за
программистами, участвующими
в проекте

Все сроки определены
в договоре

и за нарушение сроков мы
несем отвтественность

Гарантия

Гарантия сроков, а также
полные характеристики и
требования согласно
техническому заданию
закреплены в договоре

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

В этой статье приведу несколько листингов которые облегчат понимание этой связки и помогут в написании своего кода. Последующие листинги опираются на предыдущие, поэтому если в листинге, например, используется переменная «ПриложениеЭксель», но не инициализируется, значит это было в одном из предыдущих листингов.

1. Создание объекта приложение Excel

Попытка
ПриложениеЭксель = Новый COMОбъект(“Excel.Application”)
Исключение
Предупреждение(“ОШИБКА создания приложения Excel, возможно программа не установлена!”);
КонецПопытки;

2. Программное открытие файла Excel, либо его создание при отсутсвии

ПутьКФайлу = “C:obmenzagruzka.xls”;
ФайлЭксель = Новый Файл(ПутьКФайлу);
Если ФайлЭксель.Существует() Тогда
Результат = ПриложениеЭксель.WorkBooks.Open(ПутьКФайлу);
Иначе
РабочаяКнига=ПриложениеЭксель.Workbooks.Add();
РабочаяКнига=ПриложениеЭксель.ActiveWorkbook;
ПриложениеЭксель.ActiveWorkbook.SaveAs(ПутьКФайлу,-4143);
Результат = ПриложениеЭксель.WorkBooks.Open(ПутьКФайлу);
КонецЕсли;

3. Обращение к листу открытого файла Excel

ЛистЭксель = ПриложениеЭксель.WorkBooks.Open(ПутьКФайлу).WorkSheets.Item(ИмяЛиста);

4. Обойти все листы и скрыть их по условию

КоличествоЛистов = ПриложениеЭксель.ActiveWorkbook.Worksheets.Count;
КоллекцияЛистов = ПриложениеЭксель.ActiveWorkbook.Worksheets;

Для ТекНомерЛиста = 1 По КоличествоЛистов Цикл

ТекЛист = КоллекцияЛистов(ТекНомерЛиста);
Если ТекЛист.Name = «Лист1» Тогда
ТекЛист.Visible = False;
КонецЕсли;
КонецЦикла;

5. Добавить лист в самый конец(по умолчанию он добавляется в самое начало)

НомерПоследнегоЛиста = ФайлЭксель.WorkSheets.Count;
ДобавленныйЛист = ФайлЭксель.WorkSheets.Add(,ФайлЭксель.WorkSheets(НомерПоследнегоЛиста));
ДобавленныйЛист.Name = ИмяЛиста;

6. Очистить лист

ДобавленныйЛист.Cells.Clear();

7. Получить указанную область листа. Здесь используются переменные НомСтрВерх, НомСтрНиз, НомКолВерх, НомКолНиз – они обозначают координаты самой левой верхней ячейки и самой правой нижней ячейки областей

ОбластьОграниченнаяСтроками = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Rows(“”+НомСтрВерх+”:”+НомСтрНиз);

ЯчейкаВерхняяЛевая = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Cells(НомСтрВерх, НомКолВерх);

ЯчейкаНижняяПравая = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Cells(НомСтрНиз, НомКолНиз);
ОбластьОграниченнаяЯчейками = ЭксельФайл.WorkSheets.Item(ИмяЛиста).Range(ЯчейкаВерхняяПравая,ЯчейкаНижняяЛевая);

8. Скопировать область из одной книги в другу. О константах Excel(используемых числах) читайте в разделе «В заключении» данной статьи.

НоваяКнига = ПриложениеЭксель.WorkBooks.Open(ПутьКНовомуФайлу); 
ЛистПриемник = НоваяКнига.WorkSheets.Item(ИмяЛистаПриемника);
ОбластьОграниченнаяЯчейками.Copy(); //записали в буфер
ЛистПриемник.Range(ЛистПриемник.Cells(ТекСтрока, ТекСтолбец), ЛистПриемник.Cells(ТекСтрока+КоличСтрок-1, ТекСтолбец+КоличСтолбцов-1)).PasteSpecial();
ЛистПриемник.Range(ЛистПриемник.Cells(ТекСтрока, ТекСтолбец), ЛистПриемник.Cells(ТекСтрока+КоличСтрок-1, ТекСтолбец+КоличСтолбцов-1)).PasteSpecial(8);// цифра 8 означает сохранить ширину столбцов как в источнике.

9. Функция по переводу Адреса стиля ссылок Excel из A1 в R1C1 на языке 1С.

Чтобы конвертировать стиль ссылок Excel с буквенными колонками в числовые можно из кода ниже сделать функцию с параметром АдресA1

ДлинаАдресаА1 = СтрДлина(АдресА1);Для с=1 По ДлинаАдресаА1 Цикл ТекСимвол = Сред(АдресА1,с,1); Если Найти(“0123456789”, ТекСимвол) > 0 Тогда //начался номер строки БуквИндексКолонки = Лев(АдресА1, с-1); ДлинаИндекса = СтрДлина(БуквИндексКолонки); НомерКолонкиЧислом = 0; Для с = 1 По ДлинаИндекса Цикл ТекСимв = Сред(БуквИндексКолонки, с, 1); ИндексБуквы = Найти(ВРег(“abcdefghijklmnopqrstuvwxyz”), ВРег(ТекСимв)); НомерКолонкиЧислом = НомерКолонкиЧислом * 26 + ИндексБуквы; КонецЦикла; НомерСтроки = Прав(АдресА1, ДлинаАдресаА1 – с + 1); НомерСтрокиЧислом = Число(НомерСтроки); Прервать; КонецЕсли;КонецЦикла;АдресR1C1 = “R” + НомерСтрокиЧислом + “C” + НомерКолонкиЧислом;

В заключении

В некоторых примерах параметрами в методы Excel передаются числа. Это происходит из-за того, что 1С не знает о константах Excel, например «xlCellTypeLastCell». Но к счастью эти константы имеют числовые значения. Чтобы узнать их откройте справку excel. Если справка не установлена, то можно посмотреть значение в режиме отладки Excel.

Чтение данных из Excel

Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:

Попытка Эксель =Новый COMОбъект(“Excel.Application”); // для v7 код будет: Эксель = СоздатьОбъект(“Excel.Application”); Исключение Сообщить(ОписаниеОшибки()); Возврат;КонецПопытки;

Теперь используя переменную Эксель можно управлять приложением Excel.

  • Внимание! Microsoft Excel должен быть установлен на компьютере!

Следующая команда откроет книгу:

Книга = Эксель.WorkBooks.Open(ПутьКФайлу);

Перед тем, как начать считывание данных, укажем лист книги, с которого будем считывать данные:

Лист = Книга.WorkSheets(НомерЛиста);

Нумерация листов книги начинается с 1. Общее количество листов можно получить, используя следующую команду:

КоличествоЛистов = Книга.Sheets.Count;

Лист можно выбрать по имени листа в книге:

Лист = Книга.WorkSheets(ИмяЛиста);

Имя листа в книге можно получить по номеру:

ИмяЛиста = Книга.Sheets(НомерЛиста).Name;

Точно так же можно задать имя листа:

Книга.Sheets(6).Name = “6 Резерв на отпуск”;

Итак, мы открыли книгу и выбрали лист, теперь посмотрим, сколько строк и колонок на выбранном листе:

ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column; ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

Получим значения ячейки листа в строке НомерСтроки и в колонке НомерКолонки:

Значение = Лист.Cells(НомерСтроки, НомерКолонки).Value;

Ниже приведен отрывок кода, запустив который мы прочитаем все данные с первой страницы:

Эксель = СоздатьОбъект(“Excel.Application”); Книга = Эксель.WorkBooks.Open(ПутьКФайлу); Лист = Книга.WorkSheets(1); ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column; ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row; Для Строка = 1 По ВсегоСтрок Цикл Для Колонка = 1 По ВсегоКолонок Цикл Значение = СокрЛП(Лист.Cells(Строка,Колонка).Value); КонецЦикла; КонецЦикла;

Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).

После выполнения действий необходимо закрыть книгу:

Эксель.Application.Quit();

Обмен через OLE

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

  • На компьютере конечного пользователя, если обмен происходит на стороне клиента;
  • На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.

Пример выгрузки:

// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application»);
// Отключение вывода предупреждений и вопросов
Эксель.DisplayAlerts = Ложь;
// Создание новой книги
Книга = Эксель.WorkBooks.Add();
// Позиционирование на первом листе
Лист = Книга.Worksheets(1);

// Запись значения в ячейку
Лист.Cells(НомерСтроки, НомерКолонки).Value = ЗначениеЯчейки;

// Сохранение файла
Книга.SaveAs(ИмяФайла);

// Закрытие Эксель и освобождение памяти
Эксель.Quit();
Эксель = 0;

Примеры чтения:

// —— ВАРИАНТ 1 ——

// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application»);
// Открытие книги
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
// Позиционирование на нужном листе
Лист = Книга.Worksheets(1);

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;

// Закрытие книги
Книга.Close(0);

// Закрытие Эксель и освобождение памяти
Эксель.Quit();
Эксель = 0;

// —— ВАРИАНТ 2 ——

// Открытие книги
Книга = ПолучитьCOMОбъект(ПутьКФайлу);
// Позиционирование на нужном листе
Лист = Книга.Worksheets(1);

// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;

// Закрытие книги
Книга.Application.Quit();

Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:

// —— ВАРИАНТ 1 ——
КоличествоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
     ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
КонецЦикла;

// —— ВАРИАНТ 2 ——
НомерСтроки = 0;
Пока Истина Цикл
     НомерСтроки = НомерСтроки + 1;
     ЗначениеЯчейки = Лист.Cells(НомерСтроки, НомерКолонки).Value;
     Если НЕ ЗначениеЗаполнено(ЗначениеЯчейки) Тогда
         Прервать;
     КонецЕсли;
КонецЦикла;

Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:

ВсегоКолонок = Лист.Cells(1, 1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;

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

В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:

Действие Код Комментарий
Работа с приложением
Установка видимости окна приложения Эксель.Visible = Ложь;
Установка режима вывода предупреждений (выводить/не выводить) Эксель.DisplayAlerts = Ложь;
Закрытие приложения Эксель.Quit();
Работа с книгой
Создание новой книги Книга = Эксель.WorkBooks.Add();
Открытие существующей книги Книга = Эксель.WorkBooks.Open(ИмяФайла);
Сохранение книги Книга.SaveAs(ИмяФайла);
Закрытие книги Книга.Close(0);
Работа с листом
Установка текущего листа Лист = Книга.WorkSheets(НомерЛиста);
Установка имени Лист.Name = Имя;
Установка защиты Лист.Protect();
Снятие защиты Лист.UnProtect();
Установка ориентации страницы Лист.PageSetup.Orientation = 2; 1 — книжная, 2 — альбомная
Установка левой границы Лист.PageSetup.LeftMargin = Эксель.CentimetersToPoints(Сантиметры);
Установка верхней границы Лист.PageSetup.TopMargin = Эксель.CentimetersToPoints(Сантиметры);
Установка правой границы Лист.PageSetup.RightMargin = Эксель.CentimetersToPoints(Сантиметры);
Установка нижней границы Лист.PageSetup.BottomMargin = Эксель.CentimetersToPoints(Сантиметры);
Работа со строками, колонками, ячейками
Установка ширины колонки Лист.Columns(НомерКолонки).ColumnWidth = Ширина;
Удаление строки Лист.Rows(НомерСтроки).Delete();
Удаление колонки Лист.Columns(НомерКолонки).Delete();
Удаление ячейки Лист.Cells(НомерСтроки, НомерКолонки).Delete();
Установка значения Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;
Объединение ячеек Лист.Range(Лист.Cells(НомерСтроки, НомерКолонки), Лист.Cells(НомерСтроки1, НомерКолонки1)).Merge();
Установка шрифта Лист.Cells(НомерСтроки, НомерКолонки).Font.Name = ИмяШрифта;
Установка размера шрифта Лист.Cells(НомерСтроки, НомерКолонки).Font.Size = РазмерШрифта;
Установка жирного шрифта Лист.Cells(НомерСтроки, НомерКолонки).Font.Bold = 1; 1 — жирный шрифт, 0 — нормальный
Установка курсива Лист.Cells(НомерСтроки, НомерКолонки).Font.Italic = 1; 1 — курсив, 0 — нормальный
Установка подчеркнутого шрифта Лист.Cells(НомерСтроки, НомерКолонки).Font.Underline = 2; 2 — подчеркнутый, 1 — нет

Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на  VBA записанного макроса.

Использование COMSafeArray

При выгрузке больших объемов данных из 1С в Excel для ускорения можно использовать объект COMSafeArray. Согласно определению из синтакс-помощника, COMSafeArray — объектная оболочка над многомерным массивом SafeArray из COM. Позволяет создавать и использовать SafeArray для обмена данными между COM-объектами. Проще говоря, это массив значений, который можно использовать для обмена между приложениями по технологии OLE.

// Создание COMSafeArray
МассивКом = Новый COMSafeArray(«VT_Variant», ВсегоКолонок, ВсегоСтрок);
// Заполнение COMSafeArray
Для Стр = 0 По ВсегоСтрок1 Цикл
     Для Кол = 0 По ВсегоКолонок1 Цикл
         МассивКом.SetValue(Кол, Стр, Значение);
     КонецЦикла;
КонецЦикла;
// Присвоение области листа Excel значений из COMSafeArray
Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок)).Value = МассивКом;

Обмен через ADO

Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.

Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.

Пример выгрузки:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект(«ADODB.Connection»);

// Установка строки соединения
Соединение.ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=»+ИмяФайла+«;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;»;
Соединение.Open(); // Открытие соединения

// Создание COM-объекта для команды
Команда = Новый COMОбъект(«ADODB.Command»);
Команда.ActiveConnection = Соединение;

// Присвоение текста команды для создания таблицы
Команда.CommandText = «CREATE TABLE [Лист1] (Колонка1 char(255), Колонка2 date, Колонка3 int, Колонка4 float)»;
Команда.Execute(); // Выполнение команды

// Присвоение текста команды для добавления строки таблицы
Команда.CommandText = «INSERT INTO [Лист1] (Колонка1, Колонка2, Колонка3, Колонка4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)»;
Команда.Execute(); // Выполнение команды

// Удаление команды и закрытие соединения
Команда = Неопределено;
Соединение.Close();
Соединение = Неопределено;

Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table. В этом случае код примет вид:

// Создание COM-объекта для работы с книгой
Книга = Новый COMОбъект(«ADOX.Catalog»);
Книга.ActiveConnection = Соединение;

// Создание COM-объекта для работы со структурой данных на листе
Таблица = Новый COMОбъект(«ADOX.Table»);
Таблица.Name = «Лист1»;
Таблица.Columns.Append(«Колонка1», 202);
Таблица.Columns.Append(«Колонка2», 7);
Таблица.Columns.Append(«Колонка3», 5);
Таблица.Columns.Append(«Колонка4», 5);

// Создание в книге листа с описанной структурой
Книга.Tables.Append(Таблица);
Таблица = Неопределено;
Книга = Неопределено;

В приведенном примере в методе

Таблица.Columns.Append(«Колонка1», 202);

во втором параметре указывается тип колонки. Параметр необязательный, вот некоторые значения типа колонки:

  • 5 — adDouble;
  • 6 — adCurrency;
  • 7 — adDate;
  • 11 — adBoolean;
  • 202 — adVarWChar;
  • 203 — adLongVarWChar.

Пример чтения:

// Создание COM-объекта для соединения
Соединение = Новый COMОбъект(«ADODB.Connection»);

// Установка строки соединения
Соединение.ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=»+ИмяФайла+«;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;»;
Соединение.Open(); // Открытие соединения

// Создание COM-объекта для получения выборки
Выборка = Новый COMОбъект(«ADODB.Recordset»);
ТекстЗапроса = «SELECT * FROM [Лист1$]»;

// Выполнение запроса
Выборка.Open(ТекстЗапроса, Соединение);

// Обход результата выборки
Пока НЕ Выборка.EOF() Цикл
     ЗначениеКолонки1 = Выборка.Fields.Item(«Колонка1»).Value; // Обращение по имени колонки
ЗначениеКолонки2 = Выборка.Fields.Item(0).Value; // Обращение по индексу колонки
     Выборка.MoveNext();
КонецЦикла;

Выборка.Close();
Выборка = Неопределено;
Соединение.Close();
Соединение = Неопределено;

В строке соединения параметр HDR определяет как будет восприниматься первая строка на листе. Возможны варианты:

  • YES — первая строка воспринимается как названия колонок. К значениям можно обращаться по имени и по индексу колонки.
  • NO — первая строка воспринимается как данные. К значениям можно обращаться только по индексу колонки.

В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:

  • Connection;
  • Command;
  • Recordset;
  • Record;
  • Fields;
  • Stream;
  • Errors;
  • Parameters;
  • Properties.

Выгрузка без программирования

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

Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как… главного меню.

Сохранение табличного документа 1С

В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.

Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:

  1. Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список…;
  2.   Сохранить табличный документ в требуемый формат.

Сохранение динамического списка
 

Остались вопросы?
Спросите в комментариях к статье.

Смотри также:

Преобразование объекта XDTO в текст
Работа с файлами DBF
Основные способы обмена 1С с другими системами

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...