Страждущим - Excel.doc
Добавлено: 06 май 2005, 19:33
Аннотация:
По многочисленным просьбам страждущих, выкладываем доку по использованию Excel-функций в VIP. По последним сведениям, в версии Галактики 8.0 (трехзвенке) данные функции уже не поддерживаются, хотя в Галактике 8.0 (двухзвенке) еще работают
Общие сведения
Данная библиотека обеспечивает прямое обращение к функциям Excel. Дает возможность формировать и форматировать отчеты, а также использовать все стандартные функции Excel как из VIP-а, так и из паскаля.
Библиотека оформлена в виде 2-х dll-ек.
1. ExcelLib.dll - сама реализация функций, написанная на настоящем DELPHI4 (не Атлантис).
2. RegExcel.dll - регистрация функций для VIP-а, написанная на Атлантис DELPHI4.
Данное оформление позволяет использовать все возможности настоящего DELPHI4 (корректная обработка исключительны ситуаций и т.п.), а также безболезненную доработку библиотеки, не меняя GalNet.Exe.
При определении перечня функций, руководствовались следующими правилами:
• Часто применяемые операции при предполагаемой работе с Excel, оформлены как отдельные функции
• Разовые же операции предполагается применять с помощью формирования соответствующий макросов VB
Далее часто используемые операции, применяемые в макросах можно будет оформить как соответствующие функции.
Исходный код паскаля находится в каталоге \PAS\EXCEL\
Регистрация для VIP-а находится в каталоге \VIP\FUNCS\ExcelFun.Inc
Необходимые константы для работы с функциями находятся в каталоге \INC\Excel.inc
Направления использования
Примеры использования библиотеки:
• Формирование любого вида отчетов. Используются функции вывода и форматирования
• Экспорт внешних данных в формат Excel. Используются функции работы с макросами, для запуска соответствующего стандартного диалога Excel.
• Полноценая работа в макросами VB
• Формирование так называемых "сводных таблиц" (PivotTable), на основании ранее выведенных данных отчета или на основании любого внешнего источника данный (например dbf). Используются функции вывода и форматирования, а также функции работы с макросами
• Использование стандартных финансовых и других функций Excel для сложных вычислений (например в Финансовом анализе), без визуальной активизации самого Excel. Используются функции работы с макросами, для запуска соответствующей стандартной функции Excel.
• Использование стандартных функций "сводных таблиц" (PivotTable) для вычислений агрегатов по любой заранее заданной группировке, без визуальной активизации самого Excel. Используются функции работы с макросами, для запуска соответствующей стандартной функции Excel.
• Использование "сводных таблиц" (PivotTable) для формирования иерархических отчетов. Этот вариант только прорабатывается. Предполагается в стандартный иерархических отчетах просто переопределить функцию, которая выгружает данные в темповую таблицу (хранитель иерархии, кажется это TempDescr), таким образом, чтобы она по настройке выгружала их еще и в Excel. Далее запускается макрос, который строит "сводную таблицу" (PivotTable), согласно настройке, определенной в настройке стандартного иерархического отчета. Т.к. использование "сводных таблиц" , имеет смысл, далеко не ко всем иерархическим отчетам, то применение этого метода, будет иметь сравнительно небольшую трудоемкость, по сравнению с трудоемкостью написания самого стандартного иерархического отчета. К примеру у меня перевод стандартного иерархического отчета на отчет в "сводных таблиц" (PivotTable) занял около 3-х часов, причем переводил я не свой отчет, так что учитывалось еще время на то чтобы разобраться как он работает. Используются функции вывода и форматирования, а также функции работы с макросами.
• Использование "сводных таблиц" (PivotTable) для обычных отчетов, сформированных с помощью FCom. Этот вариант только прорабатывается. Предполагается использовать XML формат отчета, как источник внешних данных для "сводных таблиц" (PivotTable). Используются функции формирования отчетов в XML формат, а также функции работы с макросами, данной библиотеки.
• В перспективе выработанные методы работы со "сводными таблицами" (PivotTable), дадут базу для использования в Галактике стандартных OLAP средств.
• И еще ооочень много…
Использование макросов VB
Стратегия работы с макросами VB, предполагается следующая:
• Для текущего отчета макросы экспортируются из заранее созданного файла с макросами, написанного на VB (например MyMacros1.Bas)
• В самом VIP-е можно зашить формирование "системных", не изменяемых пользователем макросов.
• При формировании конкретного отчета в диалоговом режиме, можно дать возможность пользователю задать имя его собственного файла с макросами, который будет экспортирован в книгу текущего отчета, а также списком макросов, которые должны быть вызваны "до", "после" формирования отчета
Таким образом создается гибкость настройки отчета в Excel
Использование "сводных таблиц" (PivotTable)
Недостатки существующей методики формирования отчетов с использование "сводных таблиц" (PivotTable)
• Пока не найдена возможность простым образом задавать иерархию внутри одной и той же аналитики в "сводной таблице", в Excel предполагается, что для агрегатов внутри аналитики, будут созданы группы значений аналитики, т.е. вывод исходных данных осуществляется по "листовым значениям", а затем в "сводную таблицу" добавляются группы значений аналитики. Хотя все это относится Excel97, возможности Excel2000, еще не прорабатывались
• Несколько иной внешний вид иерархического отчета, который дает "сводная таблица" (OLAP вид), по сравнению с тем, который существует на сегоднешний день в Галактике
Преимущества методики формирования отчетов с использование "сводных таблиц" (PivotTable)
• Гораздо более высокая скорость агрегации данных
• Возможность легко перестраивать внешний вид и порядок группировок аналитик, как системно, так и самому пользователю, работая только "мышкой"
• Возможность расположения аналитик и по горизонтальной оси, чего нет в стандартных отчетах
• Использование стандартных функций "сводных таблиц" (PivotTable) только для вычислений агрегатов по любой заранее заданной группировке, без визуальной активизации самого Excel.
• Использование стандартных Excel интерфейсов для настройки отчетов
Методика использования библиотеки
Так как сама библиотека будет еще некоторое время дополняться и изменяться, то для использования ее функций предлагается следующая методика. Все используемые функции в текущем отчете рекомендуется переопределить в VIP-e. На случай, если изменится синтаксис функций.
Например:
Function MyCreateExcel(FName : string; isShowFile : boolean) : boolean;
{
MyCreateExcel := xlCreateExcel(FName, isShowFile);
}
При написании "системных" макросов предлагается следующая общеизвестная методика.
В Excel:
1. включить запись макроса
2. делаем то что хотим делать
3. выключить запись макроса
Кодируем в VIP запись в файл, например с помощью функции LogStrToFile. Более подробно описано в приведенных примерах.
Доступ к Excel
Function xlOpenNewExcel(Visible : boolean) : boolean;
Открывает Excel, никаких файлов не откравает. Всегда запускает Excel заново, если уже Excel был запущен, то к нему не подключается.Возвращает результат операции.
Visible
Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlOpenExcel (Visible : boolean) : boolean;
Открывает Excel, никаких файлов не откравает. Если Excel уже был запущен, то новый не создает, а подключается к нему.
Возвращает результат операции.
Visible
Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlCreateExcel(FileName : string; Visible : boolean) : boolean;
Создает и открывает Excel файл с именем FileName. Если Excel уже был запущен, то новый не создает, а подключается к нему.
Возвращает результат операции.
FileName имя файла
Visible Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlCreateExcelWithTemplate(TemplateName : string; Visible : boolean) : boolean;
Открывает Excel файл с именем FileName. Если Excel уже был запущен, то новый не создает, а подключается к нему.
Возвращает результат операции.
FileName имя файла
Visible Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlIsExcelValid : boolean;
Проверка инициализирован ли в данный момент Excel.
Рекомендуется проверять после выполнения xlCreateExcel или xlCreateExcelWithTemplate.
Возвращает результат операции.
Function xlDisplayAlerts(bValue : boolean = true) : boolean;
Устанавливает режим вывода стандартных Excel предупреждений
BValue Выводить или нет предепреждения
Function xlKillExcel : boolean;
Окончание работы с Excel.
Возвращает результат операции.
Работа с книгой Excel
Function xlOpenWorkBook(FileName : shortstring) : boolean;
Function xlSaveWorkBook(iNumber : shortint) : boolean;
Function xlSaveAsWorkBook(iNumber : shortint; FileName : shortstring) : boolean;
Function xlCloseWorkBook(iNumber : shortint) : boolean;
Function xlSaveWorkBookByName(sName : shortstring) : boolean;
Function xlSaveAsWorkBookByName(sName : shortstring; FileName : shortstring) : boolean;
Function xlCloseWorkBookByName(sName : shortstring) : boolean;
Function xlGetWorkBooksCount(var iCount : shortint) : boolean;
Function xlSetActiveWorkBook(wNumber : word) : boolean;
Установить активную книгу по ее порядковому номеру.
Возвращает результат операции.
Function xlSetActiveWorkBookByName(sName : string) : boolean;
Установить активную книгу по ее наименованию.
Возвращает результат операции.
Function xlGetActiveWorkBookName(var sName : string) : boolean;
Получить наименование активной книги.
Возвращает результат операции.
Работа с листом Excel
Function xlSetActiveSheet(iNumber : integer) : boolean;
Установить активным лист активной книги по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
Function xlSetActiveSheetByName(sName : string) : boolean;
Установить активным лист активной книги по его наименованию.
Возвращает результат операции.
sName : string наименование листа
Function xlGetSheetName(iNumber : integer; var sName : string) : boolean;
Получить наименование листа по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
sName : string наименование листа
Function xlSetSheetName(iNumber : integer; sName : string) : boolean;
Установить наименование по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
sName : string наименование листа
Function xlAddSheet : boolean;
Вставить новый лист. Вставляет в начало.
Function xlDeleteSheet(iNumber : integer) : boolean;
Удалить лист по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
Function xlDeleteSheetByName(sName : string) : boolean;
Удалить лист по его наименованию.
Возвращает результат операции.
sName : string наименование листа
Function xlGetSheetsCount(var iCount : integer) : boolean;
Получить количество листов в текущей книге.
Возвращает результат операции.
iCount : integer количество листов в текущей книге
Работа с поименованными областями рабочего листа
Function xlGetIndName(Name : shortstring) : integer;
Получить порядковый номер поименованной области. Если "0", значит не нашла.
Function xlDelRangeByName(Name : shortstring) : boolean;
Удалить поименованную область.
Function xlSetRangeName(Name : shortstring; RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Поименовать область
Function xlGetRangeByName(Name : shortstring; var RowU, ColL, RowD, ColR : longint) : boolean;
Получить координаты области
Работа с поименованными областями рабочей книги
Function xlGetIndNameInBook(Name : shortstring) : integer;
Получить порядковый номер поименованной области. Если "0", значит не нашла.
Function xlDelRangeByNameInBook(Name : shortstring) : boolean;
Удалить поименованную область.
Function xlSetRangeNameInBook(Name : shortstring; RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Поименовать область
Function xlGetRangeByNameInBook(Name : shortstring; var RowU, ColL, RowD, ColR : longint) : boolean;
Получить координаты области
Работа с буффером обмена
Function xlCopyToBuff(RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Скопировать в буффер, все из заданой области
Function xlPasteFormatFromBuff(RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Вставить из буффера только описания форматов
Function xlPasteAllFromBuff(RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Вставить из буффера все что было
Работа с макросами VB
Function xlRunMacro(sName : string) : boolean;
Выполнить макрос в текущей книге с заданным именем.
Возвращает результат операции.
sName : string имя макроса
Function xlImportModule(FileName : string) : boolean;
Импортировать в текущую книгу макроса из файла FileName
Возвращает результат операции.
FileName : string имя файла
Вывод значений
Function xlSetCellStringValue(Value : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа строковое значение Value
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Value : string выводимое значение
Function xlSetCellNumberValue(Value : double; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа числовое значение Value
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Value : double выводимое значение
Function xlSetCellFormula(sFormula : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа формулу sFormula
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
sFormula : string формула
Function xlGetCellValue(Raw, Col : longint ; var Value : variant) : boolean;
Взять значение заданной ячейки в переменную Value
Возвращает результат операции.
Raw, Col : longint координаты ячейки
Value : variant Значение ячейки
Function ShortStrToWin(str : string) : string;
Преобразование строки в Win-кодировку
Применяется, только при формировании макросов из VIP-а, все функции осуществляют преобразование автоматически.
Function ShortStrToDos(str : string) : string;
Преобразование строки в Dos-кодировку
Форматирование
Function xlFreeze(Raw, Col : longint) : boolean;
Закрепить область активного листа
Function xlUnFreeze : boolean;
Снять закрепление активного листа
Function xlSetFontSize(Size : word; RawU : longint; ColL : longint; RawD : longint; ColR : longint): boolean;
Установить в заданный диапазон размет шрифта Size
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Size : word размет шрифта
Function xlSetRowHeight(Size : word; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа высоту строчек в значение Size
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Size : word высота строки
Function xlSetColumnWidth(Size : word; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне активного листа ширину колонок в значение Size
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Size : word ширина колонок
Function xlSetFontColor(Color : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне цвет шрифта в значение Color
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Color : longint цвет шрифта
Function xlSetBackColor(Color : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне цвет фона в значение Color
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Color : longint цвет фона
Function xlSetFontStyle(FontStyle: byte; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне стиль шрифта в значение FontStyle
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
FontStyle: byte стиль шрифта
Function xlInsertRange(lShift : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Вставить заданный диапазон с заданным смещением lShift
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
lShift : longint смещение
Function xlWrapText(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Перенос по словам для заданного диапазона (стандартное форматирование Wrap Text)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlShrinkCells(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Подбор шрифта ячеек из заданного диапазона к размеру ячеек (стандартное форматирование Shrink to Fit)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlMergeCells(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Объединение ячеек из заданного диапазона (стандартное форматирование Merge Cells)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlAlignCells(lAlign : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Выронить заданный диапазон в соответствии с заданным выравниванием lAlign
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
lAlign : longint выравнивание
Function xlAlignCellsEx(lAlignHor, lAlignVer : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Выронить заданный диапазон в соответствии с заданным выравниванием lAlign
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
lAlignHor : longint выравнивание по горизонтали
lAlignVer : longint выравнивание по вертикали
Function xlSetNumberFormat(sFormat : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне числовой формат шрифта, заданный в sFormat
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
sFormat : string числовой формат шрифта
Function xlClearRangeContents(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Очистить контекст для заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlClearRangeFormats(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Очистить формат для заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlClearRangeComments(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Очистить комментарии для заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlFrameCells(wBorder : word; LineWeight, LineStyle, LineColor : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Задать обрамления wBorder ячеек из заданного диапазона линиями с размером LineWeight и стилем LineStyle и цветом LineColor
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
wBorder : word вид обрамления
LineWeight : longint размер линии
LineStyle : longint стиль линии
LineColor : longint цвет линии
Function xlAddCommentShort(Comment : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Задать комментарий Comment для ячеек из заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Comment : string комментарий
Function xlAutoFit(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Для ячеек из заданного диапазона (стандартное форматирование Auto Fit)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlGetUsedRange(var RawU, ColL, RawD, ColR : longint) : boolean;
Вернуть используемый диапазон ячеек
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Чтение-запись данных Excel с помощью вариантных массивов
Использование вариантных массивов позволяет на порядок увеличить скорость обмена информацией между Excel и вашим приложением.
Экспорт данных в книгу Excel
Function XLCREATEMATRIX(Rows, Cols : longint) : boolean;
Создает вариантный массив размерности [1 : Rows, 1 : Cols].
Function xlWriteToMatrix (Row, Col : longint; Value : variant) : boolean;
Записать число в массив
Function xlClearMatrix : boolean;
Очистить существующий массив.
Function xlWriteMatrixToExcel (RowU : longint = 0; ColL : longint = 0) : boolean;
Записать массив в книгу Excel с позиции (RowU, ColL)
Function xlFreeMatrix : boolean;
Освободить вариантный массив.
Импорт данных из книги Excel
Function xlReadMatrixFromExcel (RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Считать диапазон киги Excel в вариантный массив. Массив будет размерности [abs(RowD-RowU), abs(ColR-ColL)
Function xlReadFromMatrix (Row, Col : longint; var Value : variant) : boolean;
Считать ячейку (Row, Col) из вариантного массива в переменную Value.
Внимание! Вариантные массивы для экспорта и импорта совершенно различные и никак между собой не связаны. Для импорта никакой инициализации не нужно.
Примеры использования
Вывод значений и форматирование
var XlRes : boolean; XlRes := True;
XlRes := xlCreateExcel(('MyBook.xls'), True);
XlRes := xlIsExcelValid;
Do
{
if (not XlRes) Break;
var sSheetName : string;
sSheetName := 'My sheet';
ExRes := xlAddSheet; if (not XlRes) Break;
ExRes := xlSetSheetName(1, sSheetName); if (not XlRes) Break;
ExRes := xlSetActiveSheetByName(sSheetName); if (not XlRes) Break;
ExRes := xlSetCellStringValue('Hello word!!', 2, 2, 2, 2); if (not XlRes) Break;
ExRes := xlSetFontSize(14, 2, 2, 2, 2); if (not XlRes) Break;
ExRes := xlSetFontStyle(xlItalic or xlUnderline, 2, 2, 2, 2); if (not XlRes) Break;
}
while False;
xlKillExcel;
Формирование и выполнение макроса
var FBasName : string;
FBasName := 'c:\PivotM.bas'
DeleteFile(FBasName);
var XlRes : boolean; XlRes := True;
XlRes := xlCreateExcel(('MyBook.xls'), True);
XlRes := xlIsExcelValid;
Do
{
if (not XlRes) Break;
LogStrToFile(FBasName, 'Sub MyTypeHelloword ()');
LogStrToFile(FBasName, ' ActiveCell.FormulaR1C1 = "Hello word" ' );
LogStrToFile(FBasName, 'End Sub');
ExRes := xlImportModule(FBasName); if (not XlRes) Break;
ExRes := xlRunMacro(' MyTypeHelloword '); if (not XlRes) Break;
}
while False;
xlKillExcel;
Экспорт текстовых данных в Excel
var FBasName : string;
FBasName := 'c:\PivotM.bas'
DeleteFile(FBasName);
var XlRes : boolean; XlRes := True;
XlRes := xlCreateExcel(('MyBook.xls'), True);
XlRes := xlIsExcelValid;
Do
{
if (not XlRes) Break;
LogStrToFile(FBasName, 'Sub MyExportTxt()');
// для Excel 97
LogStrToFile(FBasName, ' Application.Dialogs(xlDialogOpen).Show ' );
// для Excel 2000
LogStrToFile(FBasName, ' Application.Dialogs.Item(xlDialogImportTextFile).Show ' );
LogStrToFile(FBasName, 'End Sub');
ExRes := xlImportModule(FBasName); if (not XlRes) Break;
ExRes := xlRunMacro(' MyExportTxt '); if (not XlRes) Break;
}
while False;
xlKillExcel;
По многочисленным просьбам страждущих, выкладываем доку по использованию Excel-функций в VIP. По последним сведениям, в версии Галактики 8.0 (трехзвенке) данные функции уже не поддерживаются, хотя в Галактике 8.0 (двухзвенке) еще работают
Общие сведения
Данная библиотека обеспечивает прямое обращение к функциям Excel. Дает возможность формировать и форматировать отчеты, а также использовать все стандартные функции Excel как из VIP-а, так и из паскаля.
Библиотека оформлена в виде 2-х dll-ек.
1. ExcelLib.dll - сама реализация функций, написанная на настоящем DELPHI4 (не Атлантис).
2. RegExcel.dll - регистрация функций для VIP-а, написанная на Атлантис DELPHI4.
Данное оформление позволяет использовать все возможности настоящего DELPHI4 (корректная обработка исключительны ситуаций и т.п.), а также безболезненную доработку библиотеки, не меняя GalNet.Exe.
При определении перечня функций, руководствовались следующими правилами:
• Часто применяемые операции при предполагаемой работе с Excel, оформлены как отдельные функции
• Разовые же операции предполагается применять с помощью формирования соответствующий макросов VB
Далее часто используемые операции, применяемые в макросах можно будет оформить как соответствующие функции.
Исходный код паскаля находится в каталоге \PAS\EXCEL\
Регистрация для VIP-а находится в каталоге \VIP\FUNCS\ExcelFun.Inc
Необходимые константы для работы с функциями находятся в каталоге \INC\Excel.inc
Направления использования
Примеры использования библиотеки:
• Формирование любого вида отчетов. Используются функции вывода и форматирования
• Экспорт внешних данных в формат Excel. Используются функции работы с макросами, для запуска соответствующего стандартного диалога Excel.
• Полноценая работа в макросами VB
• Формирование так называемых "сводных таблиц" (PivotTable), на основании ранее выведенных данных отчета или на основании любого внешнего источника данный (например dbf). Используются функции вывода и форматирования, а также функции работы с макросами
• Использование стандартных финансовых и других функций Excel для сложных вычислений (например в Финансовом анализе), без визуальной активизации самого Excel. Используются функции работы с макросами, для запуска соответствующей стандартной функции Excel.
• Использование стандартных функций "сводных таблиц" (PivotTable) для вычислений агрегатов по любой заранее заданной группировке, без визуальной активизации самого Excel. Используются функции работы с макросами, для запуска соответствующей стандартной функции Excel.
• Использование "сводных таблиц" (PivotTable) для формирования иерархических отчетов. Этот вариант только прорабатывается. Предполагается в стандартный иерархических отчетах просто переопределить функцию, которая выгружает данные в темповую таблицу (хранитель иерархии, кажется это TempDescr), таким образом, чтобы она по настройке выгружала их еще и в Excel. Далее запускается макрос, который строит "сводную таблицу" (PivotTable), согласно настройке, определенной в настройке стандартного иерархического отчета. Т.к. использование "сводных таблиц" , имеет смысл, далеко не ко всем иерархическим отчетам, то применение этого метода, будет иметь сравнительно небольшую трудоемкость, по сравнению с трудоемкостью написания самого стандартного иерархического отчета. К примеру у меня перевод стандартного иерархического отчета на отчет в "сводных таблиц" (PivotTable) занял около 3-х часов, причем переводил я не свой отчет, так что учитывалось еще время на то чтобы разобраться как он работает. Используются функции вывода и форматирования, а также функции работы с макросами.
• Использование "сводных таблиц" (PivotTable) для обычных отчетов, сформированных с помощью FCom. Этот вариант только прорабатывается. Предполагается использовать XML формат отчета, как источник внешних данных для "сводных таблиц" (PivotTable). Используются функции формирования отчетов в XML формат, а также функции работы с макросами, данной библиотеки.
• В перспективе выработанные методы работы со "сводными таблицами" (PivotTable), дадут базу для использования в Галактике стандартных OLAP средств.
• И еще ооочень много…
Использование макросов VB
Стратегия работы с макросами VB, предполагается следующая:
• Для текущего отчета макросы экспортируются из заранее созданного файла с макросами, написанного на VB (например MyMacros1.Bas)
• В самом VIP-е можно зашить формирование "системных", не изменяемых пользователем макросов.
• При формировании конкретного отчета в диалоговом режиме, можно дать возможность пользователю задать имя его собственного файла с макросами, который будет экспортирован в книгу текущего отчета, а также списком макросов, которые должны быть вызваны "до", "после" формирования отчета
Таким образом создается гибкость настройки отчета в Excel
Использование "сводных таблиц" (PivotTable)
Недостатки существующей методики формирования отчетов с использование "сводных таблиц" (PivotTable)
• Пока не найдена возможность простым образом задавать иерархию внутри одной и той же аналитики в "сводной таблице", в Excel предполагается, что для агрегатов внутри аналитики, будут созданы группы значений аналитики, т.е. вывод исходных данных осуществляется по "листовым значениям", а затем в "сводную таблицу" добавляются группы значений аналитики. Хотя все это относится Excel97, возможности Excel2000, еще не прорабатывались
• Несколько иной внешний вид иерархического отчета, который дает "сводная таблица" (OLAP вид), по сравнению с тем, который существует на сегоднешний день в Галактике
Преимущества методики формирования отчетов с использование "сводных таблиц" (PivotTable)
• Гораздо более высокая скорость агрегации данных
• Возможность легко перестраивать внешний вид и порядок группировок аналитик, как системно, так и самому пользователю, работая только "мышкой"
• Возможность расположения аналитик и по горизонтальной оси, чего нет в стандартных отчетах
• Использование стандартных функций "сводных таблиц" (PivotTable) только для вычислений агрегатов по любой заранее заданной группировке, без визуальной активизации самого Excel.
• Использование стандартных Excel интерфейсов для настройки отчетов
Методика использования библиотеки
Так как сама библиотека будет еще некоторое время дополняться и изменяться, то для использования ее функций предлагается следующая методика. Все используемые функции в текущем отчете рекомендуется переопределить в VIP-e. На случай, если изменится синтаксис функций.
Например:
Function MyCreateExcel(FName : string; isShowFile : boolean) : boolean;
{
MyCreateExcel := xlCreateExcel(FName, isShowFile);
}
При написании "системных" макросов предлагается следующая общеизвестная методика.
В Excel:
1. включить запись макроса
2. делаем то что хотим делать
3. выключить запись макроса
Кодируем в VIP запись в файл, например с помощью функции LogStrToFile. Более подробно описано в приведенных примерах.
Доступ к Excel
Function xlOpenNewExcel(Visible : boolean) : boolean;
Открывает Excel, никаких файлов не откравает. Всегда запускает Excel заново, если уже Excel был запущен, то к нему не подключается.Возвращает результат операции.
Visible
Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlOpenExcel (Visible : boolean) : boolean;
Открывает Excel, никаких файлов не откравает. Если Excel уже был запущен, то новый не создает, а подключается к нему.
Возвращает результат операции.
Visible
Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlCreateExcel(FileName : string; Visible : boolean) : boolean;
Создает и открывает Excel файл с именем FileName. Если Excel уже был запущен, то новый не создает, а подключается к нему.
Возвращает результат операции.
FileName имя файла
Visible Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlCreateExcelWithTemplate(TemplateName : string; Visible : boolean) : boolean;
Открывает Excel файл с именем FileName. Если Excel уже был запущен, то новый не создает, а подключается к нему.
Возвращает результат операции.
FileName имя файла
Visible Оставлять ли Excel "видимымм" после окончания работы, т.е. после вызова xlKillExcel
Function xlIsExcelValid : boolean;
Проверка инициализирован ли в данный момент Excel.
Рекомендуется проверять после выполнения xlCreateExcel или xlCreateExcelWithTemplate.
Возвращает результат операции.
Function xlDisplayAlerts(bValue : boolean = true) : boolean;
Устанавливает режим вывода стандартных Excel предупреждений
BValue Выводить или нет предепреждения
Function xlKillExcel : boolean;
Окончание работы с Excel.
Возвращает результат операции.
Работа с книгой Excel
Function xlOpenWorkBook(FileName : shortstring) : boolean;
Function xlSaveWorkBook(iNumber : shortint) : boolean;
Function xlSaveAsWorkBook(iNumber : shortint; FileName : shortstring) : boolean;
Function xlCloseWorkBook(iNumber : shortint) : boolean;
Function xlSaveWorkBookByName(sName : shortstring) : boolean;
Function xlSaveAsWorkBookByName(sName : shortstring; FileName : shortstring) : boolean;
Function xlCloseWorkBookByName(sName : shortstring) : boolean;
Function xlGetWorkBooksCount(var iCount : shortint) : boolean;
Function xlSetActiveWorkBook(wNumber : word) : boolean;
Установить активную книгу по ее порядковому номеру.
Возвращает результат операции.
Function xlSetActiveWorkBookByName(sName : string) : boolean;
Установить активную книгу по ее наименованию.
Возвращает результат операции.
Function xlGetActiveWorkBookName(var sName : string) : boolean;
Получить наименование активной книги.
Возвращает результат операции.
Работа с листом Excel
Function xlSetActiveSheet(iNumber : integer) : boolean;
Установить активным лист активной книги по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
Function xlSetActiveSheetByName(sName : string) : boolean;
Установить активным лист активной книги по его наименованию.
Возвращает результат операции.
sName : string наименование листа
Function xlGetSheetName(iNumber : integer; var sName : string) : boolean;
Получить наименование листа по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
sName : string наименование листа
Function xlSetSheetName(iNumber : integer; sName : string) : boolean;
Установить наименование по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
sName : string наименование листа
Function xlAddSheet : boolean;
Вставить новый лист. Вставляет в начало.
Function xlDeleteSheet(iNumber : integer) : boolean;
Удалить лист по его порядковому номеру.
Возвращает результат операции.
iNumber : integer; номере листа
Function xlDeleteSheetByName(sName : string) : boolean;
Удалить лист по его наименованию.
Возвращает результат операции.
sName : string наименование листа
Function xlGetSheetsCount(var iCount : integer) : boolean;
Получить количество листов в текущей книге.
Возвращает результат операции.
iCount : integer количество листов в текущей книге
Работа с поименованными областями рабочего листа
Function xlGetIndName(Name : shortstring) : integer;
Получить порядковый номер поименованной области. Если "0", значит не нашла.
Function xlDelRangeByName(Name : shortstring) : boolean;
Удалить поименованную область.
Function xlSetRangeName(Name : shortstring; RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Поименовать область
Function xlGetRangeByName(Name : shortstring; var RowU, ColL, RowD, ColR : longint) : boolean;
Получить координаты области
Работа с поименованными областями рабочей книги
Function xlGetIndNameInBook(Name : shortstring) : integer;
Получить порядковый номер поименованной области. Если "0", значит не нашла.
Function xlDelRangeByNameInBook(Name : shortstring) : boolean;
Удалить поименованную область.
Function xlSetRangeNameInBook(Name : shortstring; RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Поименовать область
Function xlGetRangeByNameInBook(Name : shortstring; var RowU, ColL, RowD, ColR : longint) : boolean;
Получить координаты области
Работа с буффером обмена
Function xlCopyToBuff(RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Скопировать в буффер, все из заданой области
Function xlPasteFormatFromBuff(RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Вставить из буффера только описания форматов
Function xlPasteAllFromBuff(RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Вставить из буффера все что было
Работа с макросами VB
Function xlRunMacro(sName : string) : boolean;
Выполнить макрос в текущей книге с заданным именем.
Возвращает результат операции.
sName : string имя макроса
Function xlImportModule(FileName : string) : boolean;
Импортировать в текущую книгу макроса из файла FileName
Возвращает результат операции.
FileName : string имя файла
Вывод значений
Function xlSetCellStringValue(Value : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа строковое значение Value
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Value : string выводимое значение
Function xlSetCellNumberValue(Value : double; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа числовое значение Value
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Value : double выводимое значение
Function xlSetCellFormula(sFormula : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа формулу sFormula
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
sFormula : string формула
Function xlGetCellValue(Raw, Col : longint ; var Value : variant) : boolean;
Взять значение заданной ячейки в переменную Value
Возвращает результат операции.
Raw, Col : longint координаты ячейки
Value : variant Значение ячейки
Function ShortStrToWin(str : string) : string;
Преобразование строки в Win-кодировку
Применяется, только при формировании макросов из VIP-а, все функции осуществляют преобразование автоматически.
Function ShortStrToDos(str : string) : string;
Преобразование строки в Dos-кодировку
Форматирование
Function xlFreeze(Raw, Col : longint) : boolean;
Закрепить область активного листа
Function xlUnFreeze : boolean;
Снять закрепление активного листа
Function xlSetFontSize(Size : word; RawU : longint; ColL : longint; RawD : longint; ColR : longint): boolean;
Установить в заданный диапазон размет шрифта Size
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Size : word размет шрифта
Function xlSetRowHeight(Size : word; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданный диапазон активного листа высоту строчек в значение Size
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Size : word высота строки
Function xlSetColumnWidth(Size : word; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне активного листа ширину колонок в значение Size
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Size : word ширина колонок
Function xlSetFontColor(Color : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне цвет шрифта в значение Color
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Color : longint цвет шрифта
Function xlSetBackColor(Color : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне цвет фона в значение Color
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Color : longint цвет фона
Function xlSetFontStyle(FontStyle: byte; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне стиль шрифта в значение FontStyle
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
FontStyle: byte стиль шрифта
Function xlInsertRange(lShift : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Вставить заданный диапазон с заданным смещением lShift
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
lShift : longint смещение
Function xlWrapText(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Перенос по словам для заданного диапазона (стандартное форматирование Wrap Text)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlShrinkCells(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Подбор шрифта ячеек из заданного диапазона к размеру ячеек (стандартное форматирование Shrink to Fit)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlMergeCells(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Объединение ячеек из заданного диапазона (стандартное форматирование Merge Cells)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlAlignCells(lAlign : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Выронить заданный диапазон в соответствии с заданным выравниванием lAlign
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
lAlign : longint выравнивание
Function xlAlignCellsEx(lAlignHor, lAlignVer : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Выронить заданный диапазон в соответствии с заданным выравниванием lAlign
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
lAlignHor : longint выравнивание по горизонтали
lAlignVer : longint выравнивание по вертикали
Function xlSetNumberFormat(sFormat : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Установить в заданном диапазоне числовой формат шрифта, заданный в sFormat
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
sFormat : string числовой формат шрифта
Function xlClearRangeContents(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Очистить контекст для заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlClearRangeFormats(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Очистить формат для заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlClearRangeComments(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Очистить комментарии для заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlFrameCells(wBorder : word; LineWeight, LineStyle, LineColor : longint; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Задать обрамления wBorder ячеек из заданного диапазона линиями с размером LineWeight и стилем LineStyle и цветом LineColor
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
wBorder : word вид обрамления
LineWeight : longint размер линии
LineStyle : longint стиль линии
LineColor : longint цвет линии
Function xlAddCommentShort(Comment : string; RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Задать комментарий Comment для ячеек из заданного диапазона
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Comment : string комментарий
Function xlAutoFit(RawU : longint; ColL : longint; RawD : longint; ColR : longint) : boolean;
Для ячеек из заданного диапазона (стандартное форматирование Auto Fit)
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Function xlGetUsedRange(var RawU, ColL, RawD, ColR : longint) : boolean;
Вернуть используемый диапазон ячеек
Возвращает результат операции.
RawU : longint; ColL : longint; RawD : longint; ColR : longint координаты диапазона
Чтение-запись данных Excel с помощью вариантных массивов
Использование вариантных массивов позволяет на порядок увеличить скорость обмена информацией между Excel и вашим приложением.
Экспорт данных в книгу Excel
Function XLCREATEMATRIX(Rows, Cols : longint) : boolean;
Создает вариантный массив размерности [1 : Rows, 1 : Cols].
Function xlWriteToMatrix (Row, Col : longint; Value : variant) : boolean;
Записать число в массив
Function xlClearMatrix : boolean;
Очистить существующий массив.
Function xlWriteMatrixToExcel (RowU : longint = 0; ColL : longint = 0) : boolean;
Записать массив в книгу Excel с позиции (RowU, ColL)
Function xlFreeMatrix : boolean;
Освободить вариантный массив.
Импорт данных из книги Excel
Function xlReadMatrixFromExcel (RowU : longint = 0; ColL : longint = 0; RowD : longint = 0; ColR : longint = 0) : boolean;
Считать диапазон киги Excel в вариантный массив. Массив будет размерности [abs(RowD-RowU), abs(ColR-ColL)
Function xlReadFromMatrix (Row, Col : longint; var Value : variant) : boolean;
Считать ячейку (Row, Col) из вариантного массива в переменную Value.
Внимание! Вариантные массивы для экспорта и импорта совершенно различные и никак между собой не связаны. Для импорта никакой инициализации не нужно.
Примеры использования
Вывод значений и форматирование
var XlRes : boolean; XlRes := True;
XlRes := xlCreateExcel(('MyBook.xls'), True);
XlRes := xlIsExcelValid;
Do
{
if (not XlRes) Break;
var sSheetName : string;
sSheetName := 'My sheet';
ExRes := xlAddSheet; if (not XlRes) Break;
ExRes := xlSetSheetName(1, sSheetName); if (not XlRes) Break;
ExRes := xlSetActiveSheetByName(sSheetName); if (not XlRes) Break;
ExRes := xlSetCellStringValue('Hello word!!', 2, 2, 2, 2); if (not XlRes) Break;
ExRes := xlSetFontSize(14, 2, 2, 2, 2); if (not XlRes) Break;
ExRes := xlSetFontStyle(xlItalic or xlUnderline, 2, 2, 2, 2); if (not XlRes) Break;
}
while False;
xlKillExcel;
Формирование и выполнение макроса
var FBasName : string;
FBasName := 'c:\PivotM.bas'
DeleteFile(FBasName);
var XlRes : boolean; XlRes := True;
XlRes := xlCreateExcel(('MyBook.xls'), True);
XlRes := xlIsExcelValid;
Do
{
if (not XlRes) Break;
LogStrToFile(FBasName, 'Sub MyTypeHelloword ()');
LogStrToFile(FBasName, ' ActiveCell.FormulaR1C1 = "Hello word" ' );
LogStrToFile(FBasName, 'End Sub');
ExRes := xlImportModule(FBasName); if (not XlRes) Break;
ExRes := xlRunMacro(' MyTypeHelloword '); if (not XlRes) Break;
}
while False;
xlKillExcel;
Экспорт текстовых данных в Excel
var FBasName : string;
FBasName := 'c:\PivotM.bas'
DeleteFile(FBasName);
var XlRes : boolean; XlRes := True;
XlRes := xlCreateExcel(('MyBook.xls'), True);
XlRes := xlIsExcelValid;
Do
{
if (not XlRes) Break;
LogStrToFile(FBasName, 'Sub MyExportTxt()');
// для Excel 97
LogStrToFile(FBasName, ' Application.Dialogs(xlDialogOpen).Show ' );
// для Excel 2000
LogStrToFile(FBasName, ' Application.Dialogs.Item(xlDialogImportTextFile).Show ' );
LogStrToFile(FBasName, 'End Sub');
ExRes := xlImportModule(FBasName); if (not XlRes) Break;
ExRes := xlRunMacro(' MyExportTxt '); if (not XlRes) Break;
}
while False;
xlKillExcel;
![:-]~](./images/smilies/207.gif ":-]~"){kind=small}
Excel_Lib.pdf называется.