Формирование отчетов из VFP в Excel. Шпаргалка.

Валентин Ярычевский

1. Общие действия
   • Открытие при создании нового документа
   • Открытие для существующего документа (Excel исчезает после выхода)
   • Выход
   • Для ускорения автоматической работы надо поставить
   • Запоминание результата в файл
   • Размеры окна
   • Разное
   • Запуск макроса
   • Удаление макроса
   • Возможность параллельной работы на Excele во время формирования отчета
2. Работа с ячейками
   • Размеры
   • Стиль ссылок
   • Ссылка на ячейку или диапазон
   • Выделение ячеек
   • Уменьшение писанины с одной ячейкой
   • Уменьшение писанины с диапазоном
   • Выравнивание текста в ячейках
   • Шрифты, фонты
   • Вставка рисунка в ячейку
   • Как добавить разрыв страницы в Excel'е из VFP
   • Как узнать координаты ячейки
   • Использование формул
   • Линии на границах ячейки или диапазона
   • Процедура для облегчения работы с линиями
   • Вырезание, копирование, вставка
   • Заморозка строк и столбцов при скроллинге
3. Печать

Общие действия.

RELEASE ole1, ole2

Открытие при создании нового документа

* способ, когда не исчезает после выхода:
ole1 = CreateObject("Excel.Application")
ole1.WorkBooks.add
ole2=ole1.Application
* способ, когда таб Excel исчезает после выхода:
ole1 = GetObject("","Excel.Sheet")
ole2 = ole1.Application

Открытие для существующего документа (Excel исчезает после выхода)
fil_excel = cfg_tgdef + "RECHNUNG_"+nominv_r+".xls"
filnam_r = "RECHNUNG_"+nominv_r+".xls"
ole1 = GetObject(fil_excel)
ole1.Application.Windows(filnam_r).Activate
ole2 = ole1.Application
ole2.Visible = .T.

Выход
ole1.Application.Quit

Для ускорения автоматической работы надо поставить

• В начале работы, сразу после создания обьекта
  ole2.WindowState = xlMinimized && минимизировать окно
ole2.ScreenUpdating = .F. && не отображать изменения на экране
  
• В конце работы, перед показом отчета
  ole2.ScreenUpdating = .T.
ole2.WindowState = xlMaximized

Запоминание результата в файл
ole2.ActiveWorkBook.Save && в тот же файл
ole2.ActiveWorkBook.SaveAs(fil_excel) && в новый файл

Размеры окна
#DEFINE xlMinimized -4140
#DEFINE xlMaximized -4137
#DEFINE xlNormal -4143
ole2.WindowState = xlMinimized && свернуть окно
ole2.WindowState = xlNormal && нормальное окно
ole2.WindowState = xlMaximized && максимальное окно

Разное
ole2.DisplayAlerts =.F. && Убрать запрос "Сохранить файл?" (перед выходом)
ole2.DisplayFormulaBar = .F. && Убрать панель формул
ole2.Version && Возвращает версию Excel

Запуск макроса
Ole2.Run("EXPORT", m.file) && "Export" - имя макроса, m.file- параметр

Удаление макроса
workbooks("book1").VBProject.VBComponents(5).CodeModule.DeleteLines 2,1
Удаляет строку номер 2 из модуля номер 5

Возможность параллельной работы на Excele во время формирования отчета

• 1-й способ:
  Ole1 = CREATEOBJECT('Excel.Application') && создание осн. обьекта
Ole1f = CREATEOBJECT('Excel.Application') && cоздание фиктивного обьекта
  Тогда при открытии XLS-документа будет задействована вторая ссылка (т.е. ole2f), а сам работаешь с первой, ole1.Visible останется равной FALSE. В нужном месте (после того как полностью сформировал свой отчет) можно вставить release ole2, который на работе XLS-документов, если таковые открыты к тому времени, не отразится.
• 2-й способ
  local ole1 as Excel.Application
ole1 = CreateObject("Excel.Application")
ole1.IgnoreRemoteRequests = .t.
  После этого у xlApp отключается механизм DDE, при помощи которого Винда и открывает в нем другую книгу. Теперь при открытии xls запустится новая копия экселя. После формирования отчета
  xlApp.IgnoreRemoteRequests = .f. && А можно этого и не делать

Работа с ячейками

Размеры
.RowHeight = 2 && высота строки (2 - узкая, прокладка, 15-20 - нормальная)
.ColumnWidth = 5 && ширина столбца (примерно в символах Courier, 9)

Стиль ссылок
Ole2.ReferenceStyle = значение
#DEFINE xlA1 1
#DEFINE xlR1C1 -4150

Ссылка на ячейку или диапазон
Ole2.Cells(2,4) && ячейка D2
Ole2.Range(ole2.Cells(2,4), ole2.Cells(3,6)) && D2:F3
То есть в Cells() указывается сначала строка (ряд), а затем столбец, причем для столбца вместо букв (указывает номер (A - 1, B - 2, …)

Выделение ячеек
Ole2.Cells(2,4).Select
Ole2.Range(ole2.Cells(2,4), ole2.Cells(3,6)).Select

Уменьшение писанины с одной ячейкой
WITH Ole2.Cells(2,4)
.Select
….
ENDWITH

Уменьшение писанины с диапазоном
Ole2.Range(ole2.Cells(2,4), ole2.Cells(3,6)).Select
WITH Selection
…..
ENDWITH

Разное при работе с ячейками или диапазоном
.MergeCells = .T. && обьединение ячееке
.Value = zag3_r && присвоение значения (можно без .Value: ole2.Cells(2,3)="aa")
.WrapText = .T. && текст переносится автоматом по словам внутри яч
.NumberFormat = "@" && не цифровой формат (число в виде текста)
.NumberFormat = "0.00" && числовой, всегда 2 дес знака после запятой
.interior.colorindex = число && цвет фона другой (38,42,…)

Выравнивание текста в ячейках
.HorizontalAlignment = число && по горизонтал
.VerticalAlignment = число && по вертикали
#DEFINE xlLeft 1 && выравнивания слева (сверху)
#DEFINE xlRightg 4 && справа по горизонтали
#DEFINE xlCenterg 3 && для горизонтали по центру
#DEFINE xlCenterv 2 && для вертикали по центру
Выравнивание всех (или выделенных) строк (или столбцов) по самой высокой (широкому) выполняется одной командой после заполнения
AutoFit.

Шрифты, фонты
.Font.Name = cfg_fntcou && имя фонта
.Font.Size = 8 && размер фонта
.Font.Bold = .T. && Жирный шрифт (.F. - не жирный)
.Font.Italic = .T. && Курсив
.Font.Underline = число && подчеркнуть всю ячейку (According) или символы
#DEFINE xlUnderlineStyleDouble -4119
#DEFINE xlUnderlineStyleDoubleAccounting 5
#DEFINE xlUnderlineStyleNone -4142
#DEFINE xlUnderlineStyleSingle 2
#DEFINE xlUnderlineStyleSingleAccounting 4
Замечание. Иногда при тексте на русском (особенно в колонтитуле) с фонтом Times появляется пробел внутри слова. Этот пробел удаляется путем использования другого фонта (например, Arial).

Вставка рисунка в ячейку
Range("B6").Select
ActiveSheet.Pictures.Insert("C:\VFP\EL\vaz_logo.gif").Select

Как добавить разрыв страницы в Excel'е из VFP
Ole2.Range("A15").Select
Ole1.ActiveWindow.SelectedSheets.HPageBreaks.Add(ole2.ActiveCell)
где ole2 и ole1 - объекты страницы и Excel.
Можно в одну строчку и без перемещения курсора
Ole2.HPageBreaks.Add(ole2.Range("A15"))

Как узнать координаты ячейки
Selection.Address, Selection.Row, Selection.Column,
Selection.Rows.Count, Selection.Column1.Count

Использование формул

• 1-й способ (не сработал):
  ole2.ActiveCell.Formula = '=SUM(D7:D20)'
  
• 2-й способ:
  .FormulaR1C1 = "=<формула>"
  Пример
  ole2.Cells(5,6).Select
r = "=RC[+3]"
FOR i=2 TO tlmmk_r
r = r + "+RC["+ALLT(STR(i*3,6))+"]"
ENDFOR
ole2.ActiveCell.FormulaR1C1 = r && записать формулу
  Пример
  FOR j= 1 to 5 && столбцы
FOR i = 1 TO 3 && строки
Ole2.cells[i,j].value = I * 10 + j
ENDFOR
WITH ole2.cells[4,j]
.Select
.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ENDWITH
ENDFOR

Линии на границах ячейки или диапазона
* .linestyle
#DEFINE xlNone -4142 &&(&HFFFFEFD2) без линии
#DEFINE xlAutomatic -4105 &&(&HFFFFEFF7) ??
#DEFINE xlDouble -4119 &&(&HFFFFEFE9) двойная
#DEFINE xlMedium -4138 && средняя
#DEFINE xlContinuous 1 && нормальная

* .borders(i)
#DEFINE xlDiagonalDown 5 && линия наклона вниз (из левого верхнего)
#DEFINE xlDiagonalUp 6 && линия наклона вверх (из левого нижнего)
#DEFINE xlEdgeLeft 7 && левая сторона ячейки
#DEFINE xlEdgeTop 8 && верхняя сторона ячейки
#DEFINE xlEdgeBottom 9 && нижняя сторона ячейки
#DEFINE xlEdgeRight 10 && правая сторона ячейки
11 - внутри диапазона ячеек вертикальные линии
12 - внутри диапазона ячеек горизонтальные линии

* .Weight
#DEFINE xlThin 2 && тонкая линия
#DEFINE xlThick 4 && толстая линия

Процедура для облегчения работы с линиями
PROCEDURE cellborders && линии ячейки
PARAMETERS p1_cb, p2_cb, p3_cb, p4_cb
&& 1 - i нач, 2 - i кон, 3 - style, 4 - weight
FOR i_cb = p1_cb TO p2_cb
WITH .Borders(i_cb)
.LineStyle = p3_cb
IF p3_cb <> xlNone
.Weight = p4_cb
.ColorIndex = xlAutomatic
ENDIF
ENDWITH
ENDFOR
Вызов процедуры (пример):
DO cellborders WITH 7,12,xlnone,xlthin

Вырезание, копирование, вставка
ole2.Range(ole2.Cells(4,7),ole2.Cells(4,8)).Select
ole2.Selection.Copy && копирование из диапазона в буфер
ole2.Range(ole2.Cells(5,7),ole2.Cells(5,8)).Select
ole2.ActiveSheet.Paste && вставка в выделенных диапазон
ole2.CutCopyMode = .F. && убираем мерцающий бордюр выделения
Если надо сделать перенос, то вместо Copy используется Cut
ole2.Selection.Cut && вырезка из диапазона в буфер
Желательно, чтобы в диапазоне, куда вставляем из буфера, не было ничего записано, то есть чтобы было чисто.

Заморозка строк и столбцов при скроллинге.
ole2.Cells(5,3).Select
ole2.ActiveWindow.FreezePanes = .T.
Будут заморожены 4 строки сверху от выделенной ячейки и 2 стобца слева от выделенной ячейки. То есть выделенная ячейка - угловая, которая не входит в заморозку.

Печать.
With ole2.ActiveSheet.PageSetup
.printGridLines = .F. && не печатать линии грида
.Orientation = 2 && 2 - альбом, 1 - портрет
.PaperSize = 9
.PrintTitleRows = "$1:$5" && cквозные строки (любые, но без разрыва)
.PrintTitleColumns = "$A:$F" && сквозные столбца (без разрыва)
.RightHeader = "&P" && Номер листа печати в правом колонтитуле
* стр.& [Страница]+1 && печать со 2 листа (или с какого хочешь
* .RightHeader = "Стр &P из &N" && Страница и всего страниц
* .RightHeader = "&P"+CHR(10)+"&D" && страница и строкой ниже дата
.TopMargin = 0.0 && поле сверху 0.5
.BottomMargin = 0.0 && поле снизу
.HeaderMargin = 0.0 && колонтитул сверху
.FooterMargin = 0.0 && колонтитул снизу
.RightMargin = 0.5 && поле справа
.LeftMargin = 0.5 && поле слева
.Order = 2 && порядок листов при неск. полосах: 1 - И, 2 - Z
* .CenterHorizontally = .T. && центровка листа печати по горизонтали
* .CenterVertically = .T. && центровка по вертикали
* .FitToPagesWide = 1 && сжатие отчет по ширине до 1 листа
* .FitToPagesTail = .F. && не сжимать отчет по числу страниц
* .Zoom = .N. && эта плюс две предыд. строки - для сжатия на 1 лист по ширине
EndWith ActiveSheet.HPageBreaks.Count && Число страниц печати

Автор: Валентин Ярычевский

По вопросам, связанным с этим веб-узлом, обращайтесь по адресу: © Michael Drozdov, 2000-2005. Все права защищены. Последнее изменение страницы: Вт, 23 августа 2005