Способы обмена данными между программами MS Office, 1С

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

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

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

 

Рисунок1 – Диалог обработки

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

Данные выбирались из столбца «Заказчик» таблицы, представленной на рисунке 2.

Рисунок 2 – Исходные значения в таблице.

Рассмотрим часть кода, в которой непосредственно происходит работа с таблицей EXCEL:

Ексель=СоздатьОбъект("Excel.Application"); // Создаем СOM объект

Книга=Ексель.Workbooks.Open(каталог + "\" + файл,, 1); // Открываем файл в EXCEL

значен=Ексель.Cells(Стр,Колонка).Value; // Присваиваем переменной значение из ячейки таблицы EXCEL

Пока ПустаяСтрока(значен)=0 Цикл // пока ячейки таблицы EXCEL не пусты

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

Если Флаг=1 тогда // При неповторяющихся значениях

Если Таб.Найтизначение(значен,,)=0 тогда // Если значение отсутствует в таблице значений

Спр.Новый(); // Создаем строку

Спр.УстановитьАтрибут("Наименование",значен);  // Записываем значение

Спр.Записать();

Таб.Новаястрока(); // Создаем строку

Таб.Установитьзначение(Таб.количествострок(),1,Таб.количествострок());

Таб.Установитьзначение(Таб.количествострок(),2,значен); // Записываем значения

Конецесли;

В системе "1С:Предприятие" (версия 8. 2.) новый экземпляр СОМ-объекта создается С ПОМОЩЬЮ КОНСТРУКЦИИ Идентификатор = СоздатьОбъект ("ИмяОбъекта"). Методы и свойства СОМ-объектов в дальнейшем становятся доступным через созданный объект.

Мы видим, что после создания объекта «Ексель», при котором происходит инициализация приложения, можем использовать язык VBA, с помощью которого осуществляется управление в таблице EXCEL («Cells(Стр,Колонка).Value»). Переменная «Книга» нужна для открытия конкретной таблицы. OLE идентификатор "Excel.Application" описан в самом приложении MS EXCEL.

При нажатии кнопки «Сформировать» происходит последоватнльное копирование значений в таблицу значений диалога обработки и в справочник.

Заполненный справочник и таблица значений представлены на рисунке 3.

Рисунок 3 – Заполненный справочник и таблица значений.

 

2. 8. Использование системы "1С:Предприятие"в качестве OLE Automation сервера (версия 8. 2.)

Для запуска системы "1С: Предприятие", в качестве OLE Automation сервера, из внешнего приложения, выполняется следующая последовательность действий:

Создается объект с OLE идентификатором:

VICEnterprise.Application — Версия независимый КЛЮЧ;

V77 .Application —  версия зависимый ключ;

V77S.Application -—  версия зависимый ключ, SQL-версия;

V77L.Application —  версия зависимый ключ, локальная  версия;

V77M.Application —  версия зависимый ключ, сетевая  версия.

Выполняется инициализация системы "1С:Предприятие" методом

Initialize().

Вызываются атрибуты и методы системы "1С:Предприятие", как OLE

Automation сервера "1С:Предприятие", в качестве OLE Automation сервера, имеет 4 метода:

Initialize — выполняет инициализацию системы "1С:Предприятие".

Createobject —  создает объект агрегатного типа  данных системы "1С: Предприятие" и возвращает ссылку на него.

EvaiExpr — вычисляет  выражение системы.

ExecuteBatch —  выполняет последовательность операторов

Для примера возьмем лист EXCEL в который нужно перенести значения из справочника, созданного в 1С. Расположим на нем командные кнопки. (Рисунок 4).

Рисунок 4 – Лист с кнопками.

Рассмотрим фрагмент кода, в котором происходит перенос:

Set V7 = CreateObject("V77.Application") 'Создаем объект - приложение 1С

Init = V7. Initialize(V7.RMTRADE, "/D", "") 'Инициализируем приложение

Sp = InputBox("Введите  название справочника", "Справочник", "Клиенты") 'Спрашиваем у пользователя  название справочника-истчника записей

Set Spr = V7. CreateObject("Справочник." & Sp) 'Используем введенный справочник

Cells(1, 1).Value = "Код" 'Подписываем колонки

Cells(1, 2).Value = "Наименование"

Stroka = 2 'Устанавливаем  первую строку для ввода

Spr.ПорядокКодов 'Сортируем справочник по коду

Spr.ВыбратьЭлементы 'Открываем справочник для выборки

Do While Spr.ПолучитьЭлемент() = 1 '

Cells(Stroka, 1).Value = Spr.Код 'копируем значения из  справочника в таблицу

Cells(Stroka, 2).Value = Spr.Наименование

Stroka = Stroka + 1 'Переходим  в следующую строку

Loop

Здесь в начале открывается база 1С, затем открывается справочник и копируются значения. Заполненная таблица выглядит следующим образом (Рисунок 5)

Рисунок 5 – Заполненная таблица.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

II. Практическая часть

1. Обобщение данных внутри программы Exsel

1. 1. Общая характеристика задачи

Рассмотрим следующую задачу.

Предприятие ООО "Энергосбыт" осуществляет деятельность, связанную с обеспечением электроэнергией физических и юридических лиц, и производит расчёты по предоставленным услугам. Данные, на основании которых производятся расчёты по оплате, представлены в табл. 2. 1.

1. Построить  таблицу согласно табл.2. 1.

2. Результаты  вычислений представить в виде  таблицы, содержащей данные о  расходе электроэнергии и сумму  к оплате (табл.2. 2), и в графическом виде.

3. Организовать  межтабличные связи для автоматического  формирования квитанции об оплате  электроэнергии.

4. Сформировать  и заполнить квитанцию об оплате  электроэнергии (табл.2. 3).

Таблица 2.1. Данные о показаниях электросчетчиков

Показания электросчетчиков Месяц: Декабрь 2005

Код плательщика	ФИО плательщика	Адрес	Показания счетчика на начало месяца, КВт	Показания счетчика на конец месяца, КВт
001	Коломиец И.И.	проспект Мира, 44-5	44578	44623
002	Гудзенчук А.А.	проспект Мира, 44-6	33256	33342
003	Матвеев К.К.	проспект Мира, 44-7	14589	14705
004	Сорокин М.М.	проспект Мира, 44-8	78554	78755
005	Ивлев С.С.	проспект Мира, 44-9	25544	25601

 

Таблица 2.2. Расчет оплаты электроэнергии

Расчет оплаты электроэнергии

тариф за 1 КВт – 1,40 руб.

Месяц: Декабрь 2005

ФИО плательщика	Код плательщика	Расход электроэнергии за месяц, КВт	К оплате, руб.
Коломиец И.И.	001
Матвеев К.К.	003
Ивлев С.С.	005
Гудзенчук А.А.	002
Сорокин М.М.	004
ИТОГО

 

Таблица 2.3. Квитанция на оплату электроэнергии

ООО "Энергосбыт"		Месяц:                                           20                г.
		Код плательщика                 001
КВИТАНЦИЯ НА ОПЛАТУ ЭЛЕКТРОЭНЕРГИИ
ФИО плательщика
ТАРИФ ЗА 1 КВт		1,40 руб.
Показания счетчика на начало месяца, КВт	Показания счетчика на конец месяца, КВт	Расход электроэнергии за месяц, КВт	К оплате, руб.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. 2. Описание алгоритма решения задачи

1. Запустить  табличный процессор MS Excel.

2. Создать  книгу с именем "Энергосбыт".

3. Лист 1 переименовать  в лист с названием «Показания электросчетчиков».

4. На рабочем  листе «Показания электросчетчиков» MS Excel создать таблицу "Показания электросчетчиков".

5. Заполнить  таблицу "Показания электросчетчиков" исходными данными (рис.2.1)

 

Рисунок 2. 1. Расположение таблицы "Показания электросчетчиков" на рабочем листе Показания электросчетчиков MS Excel

6. Лист 2 переименовать  в лист с названием «Расчет оплаты электроэнергии».

7. На рабочем  листе «Расчет оплаты электроэнергии» MS Excel создать таблицу "Расчет оплаты электроэнергии".

8. Заполнить  таблицу «Расчет оплаты электроэнергии»  исходными данными (рис. 2. 2.)

Рисунок 2. 2. Расположение таблицы "Расчет оплаты электроэнергии" на рабочем листе Расчет оплаты электроэнергии MS Excel

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

Занести в ячейку C4 формулу:

=ПРОСМОТР(B4;'Показания  электросчетчиков'!$A$4:$A$8;'Показания  электросчетчиков'!$E$4:$E$8)-ПРОСМОТР(B4;'Показания  электросчетчиков'!$A$4:$A$8;'Показания  электросчетчиков'!$D$4:$D$8)

Размножить введенную в ячейку C4 формулу для остальных ячеек (с C5 по C8) данной графы.

Таким образом, будет автоматически подсчитан Расход электроэнергии за месяц, кВт (рис. 2. 3.).

Вычислить ИТОГО расхода электроэнергии, пользуясь функцией «Автосумма», т.е. занести в ячейку C9 формулу: =СУММ(C4:C8).

Рисунок 2. 3. Автоматический подсчет расхода электроэнергии за месяц

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

Занести в ячейку D4 формулу: =C4*1,4

Размножить введенную в ячейку D4 формулу для остальных ячеек (с D5 по D8) данной графы.

Таким образом, будет автоматически подсчитана оплата электроэнергии за месяц, кВт (рис. 2.4).

Вычислить ИТОГО оплаты электроэнергии, пользуясь функцией «Автосумма», т.е. занести в ячейку D9 формулу: =СУММ(D4:D8).

Рисунок 2. 4. Автоматический подсчет оплаты электроэнергии за месяц

Лист 3 переименовать в лист с названием «Квитанция».

12. На рабочем листе «Квитанция» MS Excel создать таблицу «Квитанция на оплату электроэнергии».

13. Путем создания межтабличных связей заполнить созданную таблицу полученными данными из таблиц «Показания электросчетчиков» (рис.2.1.) и «Расчет оплаты электроэнергии» (рис.2.4.).

14. Лист 4 переименовать в лист с названием «График».

15. На рабочем листе «График» MS Excel создать сводную таблицу. Путем создания межтабличных связей автоматически заполнить графы «Расход электроэнергии», «кВт» и «Сумма» к оплате, руб. полученными данными из таблицы «Расчет оплаты электроэнергии» (рис.2.4.).

16. Результаты вычислений представить графически (рис.2. 5.).

ООО "Энергосбыт"
Расход электроэнергии и сумма к оплате за сентябрь 2005 г.
ФИО плательщика	Расход электроэнергии, КВт	Сумма к оплате, руб.
Коломиец И.И.	45	63,00
Матвеев К.К.	116	162,40
Ивлев С.С.	57	79,80
Гудзенчук А.А.	86	120,40
Сорокин М.М.	201	281,40

 

Рисунок 2. 5 Сводная таблица и графическое представление результатов вычислений

 

 

 

 

 

 

2.Перенос данных их Exsel в 1С

Код обработки 1С:

перем каталог; //Каталог данных

перем файл; //Файл данных

перем Справ;

Процедура ПриОткрытии()

Колонка=7; // Номер колонки по умолчанию