Работа с MS Excel, обновление тарифа

При наличии модуля статистики пользователю доступны команды работы с файлами в формате MS Excel - чтение, создание и изменение. Эти команды введены в генератор отчетов с целью создания открытых макросов выгрузки и загрузки тарифа материальных ценностей и состояния складов, заявок на изготовление стеклопакетов и т.п., хранящихся в файлах *.xls с различным порядком столбцов.

В настоящий момент доступны следующий набор команд:
~EXCEL.Connect~ - инициализация OLE подключения к интерфейсу "Excel.Application" с автоматическим созданием новой книги,
~EXCEL.Connect:<Имя_файла>~ - инициализация OLE подключения к интерфейсу "Excel.Application" с автоматической загрузкой указанного файла *.xls,
~EXCEL.Connect:Открыть_Файл~ - инициализация OLE подключения к интерфейсу "Excel.Application" с диалоговым запросом выбора файла *.xls для загрузки.

Доступ к книгам, листам, строкам, столбцам и ячейкам осуществляется по соответствующим индексам:
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]~

В качестве индексов может быть как число, так и целочисленный параметр:
~EXCEL. Книга[1].Лист[1].Строка[ИндСтроки].Столбец[ИндСтолбца]~

Операции с ячейками

Вывод содержимого ячейки в выходной отчет:
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]~

Получение значения ячейки в параметр:
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]:AsInteger:Параметр~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]:AsString:Параметр~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]:AsFloat:Параметр~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]:AsDate:Параметр~

Заполнение ячейки текстом:
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]='Текст'~

Заполнение ячейки значением параметра:
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]=Параметр.AsInteger~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]=Параметр.AsString~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]=Параметр.AsFloat~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]=Параметр.AsDate~
~EXCEL. Книга[1].Лист[1].Строка[1].Столбец[1]=Параметр.AsText~

Форматирование строк, столбцов, ячеек

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

Указание названия листа:
~EXCEL.Книга[1].Лист[1].Название='Прайс-лист'~

Выравнивание ячейки:
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Выровнять=Слева~
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Выровнять=Центр~
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Выровнять=Справа~

Выравнивание для строк:
~EXCEL.Книга[1].Лист[1].Строка[1].Выровнять=Слева~
~EXCEL.Книга[1].Лист[1].Строка[1].Выровнять=Центр~
~EXCEL.Книга[1].Лист[1].Строка[1].Выровнять=Справа~

Выравнивание столбца:
~EXCEL.Книга[1].Лист[1].Столбец[1].Выровнять=Слева~
~EXCEL.Книга[1].Лист[1].Столбец[1].Выровнять=Центр~
~EXCEL.Книга[1].Лист[1].Столбец[1].Выровнять=Справа~

Установка высоты строки:
~EXCEL.Книга[1].Лист[1].Строка[1].Высота=25~

Установка ширины столбца:
~EXCEL.Книга[1].Лист[1].Столбец[1].Ширина=50~

Установка границы ячейки:
~EXCEL.Книга[1].Лист[1].Строка[1]. Столбец[1].Граница.Стиль=3~
~EXCEL.Книга[1].Лист[1].Строка[1]. Столбец[1].Граница.Толщина=3~

Установка ориентации текста строки:
~EXCEL.Книга[1].Лист[1].Строка[1].Ориентация=90~

Установка ориентации текста столбца:
~EXCEL.Книга[1].Лист[1].Столбец[1].Ориентация=90~

Установка ориентации текста ячейки:
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Ориентация=90~

Установка числового формата строки:
~EXCEL.Книга[1].Лист[1].Строка[1].Формат=0,00~

Установка числового формата столбца:
~EXCEL.Книга[1].Лист[1].Столбец[1].Формат=0,00~

Установка числового формата ячейки:
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Формат=0,00~

Установка атрибутов шрифта строки:
~EXCEL.Книга[1].Лист[1].Строка[1].Шрифт.Название='Arial'~
~EXCEL.Книга[1].Лист[1].Строка[1].Шрифт.Жирный=Да~
~EXCEL.Книга[1].Лист[1].Строка[1].Шрифт.Жирный=Нет~
~EXCEL.Книга[1].Лист[1].Строка[1].Шрифт.Размер=16~
~EXCEL.Книга[1].Лист[1].Строка[1].Шрифт.Цвет='Синий'~

Установка атрибутов шрифта столбца:
~EXCEL.Книга[1].Лист[1].Столбец[1].Шрифт.Название='Arial'~
~EXCEL.Книга[1].Лист[1].Столбец[1].Шрифт.Жирный=Да~
~EXCEL.Книга[1].Лист[1].Столбец[1].Шрифт.Жирный=Нет~
~EXCEL.Книга[1].Лист[1].Столбец[1].Шрифт.Размер=16~
~EXCEL.Книга[1].Лист[1].Столбец[1].Шрифт.Цвет='Синий'~

Установка атрибутов шрифта ячейки:
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Шрифт.Название='Arial'~
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Шрифт.Жирный=Да~
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Шрифт.Жирный=Нет~
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Шрифт.Размер=16~
~EXCEL.Книга[1].Лист[1].Строка[1].Столбец[1].Шрифт.Цвет='Синий'~

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

Работа с выделенным регионом ячеек

Выделение ячеек осуществляется двумя командами:
~EXCEL.Книга[1].Лист[1].Строка[2].Столбец[3].Регион.Начало~
~EXCEL.Книга[1].Лист[1].Строка[5].Столбец[7].Регион.Конец~

Объединение выделенных ячеек, команда должна следовать сразу после команд выделения ячеек:
~EXCEL.Книга[1].Регион.Объединить~

Для выделенных ячеек доступны команды форматирования текста, например:
~EXCEL.Книга[1].Регион.Шрифт.Жирный=Да~

Для установки авта фильтра по выделенным ячейкам в столбцах существует команда:
~EXCEL:Добавить.АвтоФильтр~

Примеры

<!-- Макрос обновления тарифа основной текстуры из указанного файла *.xls с предопределенной структурой (порядком требуемых столбцов):
1-й столбец содержит артикул
3-й столбец содержит основную текстуру
5-й столбец содержит цену тарифа в рублях -->



<!-- устанавливаем формат подстановки вещественного значения в SQL запрос (с точкой в качестве разделителя и без разделения разрядов) -->
~Точность:2:2:".":"x"~

<!-- запускаем MS Excel c выбором *.xls файла -->
~EXCEL.CONNECT:Открыть_файл~

<!-- в соответствии со структурой файла начинаем со 2-й строки -->
~AsInt:Номер=2~

~LABEL:Цикл~ <!-- начало цикла обработки строк -->

~EXCEL.Книга[1].Лист[1].Строка[Номер].Столбец[1]:AsString:Арт№~ ~EXCEL.Книга[1].Лист[1].Строка[Номер].Столбец[3]:AsString:Цвет~

<!-- если ячейка артикула или цвета пустая - прекратить обработку -->
~IFS(Арт№='')OR(Цвет='')THEN GOTO Конец~

<!-- значение 5-го столбца (Цена) помещаем в параметр `Стоимость` -->
~EXCEL.Книга[1].Лист[1].Строка[Номер].Столбец[5]:AsFloat:Стоимость~

<!-- получаем индекс текстуры по ее названию, указанному в 3-м столбце -->
~AsInt:Цвет=0~
~SQL1:Установить:SELECT cNumb FROM ColsLST~
~SQL1:Добавить:WHERE cName=:Цвет.AsString~
~SQL1:Начало~
~SQL1:cNumb:AsInt:Цвет~
~SQL1:Конец~

<!-- непосредственно обновляем тариф основной текстуры артикула -->
~SQL1:Установить:UPDATE ArtsVst SET clPrc=:Стоимость.AsFloat~
~SQL1:Добавить:WHERE (aNumb=:Арт№.AsString:)~
~SQL1:Добавить:AND(clNum=:Цвет.AsInt:)~
~SQL1:Выполнить~

~AsInt:Номер=Номер+1~ ~GOTO:Цикл~

~LABEL:Конец~

<!-- информационное сообщение - напоминание: программа Excel открыта -->
~Диалог:Обновление тарифа завершено.:<BR>:Не забудьте закрыть Excel !~
~Отчет:Закрыть~

К сведению
Набор команд работы с MS Excel будет постоянно дополняться в новых версиях программы.