Программирование на Delphi

FAQ VCL
Работа с MS Office\Word

:: Меню ::

:: На главную ::

:: FAQ ::

:: Заметки ::

:: Практика ::

:: Win API ::

:: Проекты ::

:: Скачать ::

:: Секреты ::

:: Ссылки ::

:: Сервис ::

:: Написать ::

:: MVP ::

:: RSS ::

Как развернуть Excel на весь экран и свернуть обратно?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.DisplayFullScreen := True;  // Развернуть
   E.DisplayFullScreen := False; // Свернуть
end;

Как скрыть/показать окно буфера обмена Excel?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.DisplayClipboardWindow := False; // Скрыть
   E.DisplayClipboardWindow := True;  // Показать
end;

Как скрыть/показать маркер комментария в ячейке Excel?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.DisplayCommentIndicator := False; // Скрыть
   E.DisplayCommentIndicator := True;  // Показать
end;

Как скрыть/показать окно свойств документа Excel?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.DisplayDocumentInformationPanel := False; // Скрыть
   E.DisplayDocumentInformationPanel := True;  // Показать
end;

Как скрыть/показать маркер примечания в ячейке Excel?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.DisplayNoteIndicator := False; // Скрыть
   E.DisplayNoteIndicator := True;  // Показать
end;

Как скрыть/показать полосы прокрутки в Excel?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.DisplayScrollBars := False; // Скрыть
   E.DisplayScrollBars := True;  // Показать
end;

Как определить последнюю ячейку в наборе (Range)?


// Способ первый
{uses
  ..., Excel_TLB;}

// Constants for enum Constants
type
  Constants = TOleEnum;
const
  xlAll = $FFFFEFF8;
  xlAutomatic = $FFFFEFF7;
  xlBoth = $00000001;
  xlCenter = $FFFFEFF4;
  xlChecker = $00000009;
  xlCircle = $00000008;
  xlCorner = $00000002;
  xlCrissCross = $00000010;
  xlCross = $00000004;
  xlDiamond = $00000002;
  xlDistributed = $FFFFEFEB;
  xlDoubleAccounting = $00000005;
  xlFixedValue = $00000001;
  xlFormats = $FFFFEFE6;
  xlGray16 = $00000011;
  xlGray8 = $00000012;
  xlGrid = $0000000F;
  xlHigh = $FFFFEFE1;
  xlInside = $00000002;
  xlJustify = $FFFFEFDE;
  xlLightDown = $0000000D;
  xlLightHorizontal = $0000000B;
  xlLightUp = $0000000E;
  xlLightVertical = $0000000C;
  xlLow = $FFFFEFDA;
  xlManual = $FFFFEFD9;
  xlMinusValues = $00000003;
  xlModule = $FFFFEFD3;
  xlNextToAxis = $00000004;
  xlNone = $FFFFEFD2;
  xlNotes = $FFFFEFD0;
  xlOff = $FFFFEFCE;
  xlOn = $00000001;
  xlPercent = $00000002;
  xlPlus = $00000009;
  xlPlusValues = $00000002;
  xlSemiGray75 = $0000000A;
  xlShowLabel = $00000004;
  xlShowLabelAndPercent = $00000005;
  xlShowPercent = $00000003;
  xlShowValue = $00000002;
  xlSimple = $FFFFEFC6;
  xlSingle = $00000002;
  xlSingleAccounting = $00000004;
  xlSolid = $00000001;
  xlSquare = $00000001;
  xlStar = $00000005;
  xlStError = $00000004;
  xlToolbarButton = $00000002;
  xlTriangle = $00000003;
  xlGray25 = $FFFFEFE4;
  xlGray50 = $FFFFEFE3;
  xlGray75 = $FFFFEFE2;
  xlBottom = $FFFFEFF5;
  xlLeft = $FFFFEFDD;
  xlRight = $FFFFEFC8;
  xlTop = $FFFFEFC0;
  xl3DBar = $FFFFEFFD;
  xl3DSurface = $FFFFEFF9;
  xlBar = $00000002;
  xlColumn = $00000003;
  xlCombination = $FFFFEFF1;
  xlCustom = $FFFFEFEE;
  xlDefaultAutoFormat = $FFFFFFFF;
  xlMaximum = $00000002;
  xlMinimum = $00000004;
  xlOpaque = $00000003;
  xlTransparent = $00000002;
  xlBidi = $FFFFEC78;
  xlLatin = $FFFFEC77;
  xlContext = $FFFFEC76;
  xlLTR = $FFFFEC75;
  xlRTL = $FFFFEC74;
  xlFullScript = $00000001;
  xlPartialScript = $00000002;
  xlMixedScript = $00000003;
  xlMixedAuthorizedScript = $00000004;
  xlVisualCursor = $00000002;
  xlLogicalCursor = $00000001;
  xlSystem = $00000001;
  xlPartial = $00000003;
  xlHindiNumerals = $00000003;
  xlBidiCalendar = $00000003;
  xlGregorian = $00000002;
  xlComplete = $00000004;
  xlScale = $00000003;
  xlClosed = $00000003;
  xlColor1 = $00000007;
  xlColor2 = $00000008;
  xlColor3 = $00000009;
  xlConstants = $00000002;
  xlContents = $00000002;
  xlBelow = $00000001;
  xlCascade = $00000007;
  xlCenterAcrossSelection = $00000007;
  xlChart4 = $00000002;
  xlChartSeries = $00000011;
  xlChartShort = $00000006;
  xlChartTitles = $00000012;
  xlClassic1 = $00000001;
  xlClassic2 = $00000002;
  xlClassic3 = $00000003;
  xl3DEffects1 = $0000000D;
  xl3DEffects2 = $0000000E;
  xlAbove = $00000000;
  xlAccounting1 = $00000004;
  xlAccounting2 = $00000005;
  xlAccounting3 = $00000006;
  xlAccounting4 = $00000011;
  xlAdd = $00000002;
  xlDebugCodePane = $0000000D;
  xlDesktop = $00000009;
  xlDirect = $00000001;
  xlDivide = $00000005;
  xlDoubleClosed = $00000005;
  xlDoubleOpen = $00000004;
  xlDoubleQuote = $00000001;
  xlEntireChart = $00000014;
  xlExcelMenus = $00000001;
  xlExtended = $00000003;
  xlFill = $00000005;
  xlFirst = $00000000;
  xlFloating = $00000005;
  xlFormula = $00000005;
  xlGeneral = $00000001;
  xlGridline = $00000016;
  xlIcons = $00000001;
  xlImmediatePane = $0000000C;
  xlInteger = $00000002;
  xlLast = $00000001;
  xlLastCell = $0000000B;
  xlList1 = $0000000A;
  xlList2 = $0000000B;
  xlList3 = $0000000C;
  xlLocalFormat1 = $0000000F;
  xlLocalFormat2 = $00000010;
  xlLong = $00000003;
  xlLotusHelp = $00000002;
  xlMacrosheetCell = $00000007;
  xlMixed = $00000002;
  xlMultiply = $00000004;
  xlNarrow = $00000001;
  xlNoDocuments = $00000003;
  xlOpen = $00000002;
  xlOutside = $00000003;
  xlReference = $00000004;
  xlSemiautomatic = $00000002;
  xlShort = $00000001;
  xlSingleQuote = $00000002;
  xlStrict = $00000002;
  xlSubtract = $00000003;
  xlTextBox = $00000010;
  xlTiled = $00000001;
  xlTitleBar = $00000008;
  xlToolbar = $00000001;
  xlVisible = $0000000C;
  xlWatchPane = $0000000B;
  xlWide = $00000003;
  xlWorkbookTab = $00000006;
  xlWorksheet4 = $00000001;
  xlWorksheetCell = $00000003;
  xlWorksheetShort = $00000005;
  xlAllExceptBorders = $00000007;
  xlLeftToRight = $00000002;
  xlTopToBottom = $00000001;
  xlVeryHidden = $00000002;
  xlDrawingObject = $0000000E;

var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   ShowMessage('Row: ' + IntToStr(E.ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row) + #13 +
      'Column: ' + IntToStr(E.ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column));
end;

// Способ второй
procedure TForm1.Button1Click(Sender: TObject);
begin
   ShowMessage('Row: ' + IntToStr(E.ActiveSheet.UsedRange.Row + E.ActiveSheet.UsedRange.Rows.Count - 1) + #13 +
      'Column: ' + IntToStr(E.ActiveSheet.UsedRange.Column + E.ActiveSheet.UsedRange.Columns.Count - 1));
end;

Как осуществить поиск в Excel?


// Constants for enum Constants
type
  Constants = TOleEnum;
const
  xlAll = $FFFFEFF8;
  xlAutomatic = $FFFFEFF7;
  xlBoth = $00000001;
  xlCenter = $FFFFEFF4;
  xlChecker = $00000009;
  xlCircle = $00000008;
  xlCorner = $00000002;
  xlCrissCross = $00000010;
  xlCross = $00000004;
  xlDiamond = $00000002;
  xlDistributed = $FFFFEFEB;
  xlDoubleAccounting = $00000005;
  xlFixedValue = $00000001;
  xlFormats = $FFFFEFE6;
  xlGray16 = $00000011;
  xlGray8 = $00000012;
  xlGrid = $0000000F;
  xlHigh = $FFFFEFE1;
  xlInside = $00000002;
  xlJustify = $FFFFEFDE;
  xlLightDown = $0000000D;
  xlLightHorizontal = $0000000B;
  xlLightUp = $0000000E;
  xlLightVertical = $0000000C;
  xlLow = $FFFFEFDA;
  xlManual = $FFFFEFD9;
  xlMinusValues = $00000003;
  xlModule = $FFFFEFD3;
  xlNextToAxis = $00000004;
  xlNone = $FFFFEFD2;
  xlNotes = $FFFFEFD0;
  xlOff = $FFFFEFCE;
  xlOn = $00000001;
  xlPercent = $00000002;
  xlPlus = $00000009;
  xlPlusValues = $00000002;
  xlSemiGray75 = $0000000A;
  xlShowLabel = $00000004;
  xlShowLabelAndPercent = $00000005;
  xlShowPercent = $00000003;
  xlShowValue = $00000002;
  xlSimple = $FFFFEFC6;
  xlSingle = $00000002;
  xlSingleAccounting = $00000004;
  xlSolid = $00000001;
  xlSquare = $00000001;
  xlStar = $00000005;
  xlStError = $00000004;
  xlToolbarButton = $00000002;
  xlTriangle = $00000003;
  xlGray25 = $FFFFEFE4;
  xlGray50 = $FFFFEFE3;
  xlGray75 = $FFFFEFE2;
  xlBottom = $FFFFEFF5;
  xlLeft = $FFFFEFDD;
  xlRight = $FFFFEFC8;
  xlTop = $FFFFEFC0;
  xl3DBar = $FFFFEFFD;
  xl3DSurface = $FFFFEFF9;
  xlBar = $00000002;
  xlColumn = $00000003;
  xlCombination = $FFFFEFF1;
  xlCustom = $FFFFEFEE;
  xlDefaultAutoFormat = $FFFFFFFF;
  xlMaximum = $00000002;
  xlMinimum = $00000004;
  xlOpaque = $00000003;
  xlTransparent = $00000002;
  xlBidi = $FFFFEC78;
  xlLatin = $FFFFEC77;
  xlContext = $FFFFEC76;
  xlLTR = $FFFFEC75;
  xlRTL = $FFFFEC74;
  xlFullScript = $00000001;
  xlPartialScript = $00000002;
  xlMixedScript = $00000003;
  xlMixedAuthorizedScript = $00000004;
  xlVisualCursor = $00000002;
  xlLogicalCursor = $00000001;
  xlSystem = $00000001;
  xlPartial = $00000003;
  xlHindiNumerals = $00000003;
  xlBidiCalendar = $00000003;
  xlGregorian = $00000002;
  xlComplete = $00000004;
  xlScale = $00000003;
  xlClosed = $00000003;
  xlColor1 = $00000007;
  xlColor2 = $00000008;
  xlColor3 = $00000009;
  xlConstants = $00000002;
  xlContents = $00000002;
  xlBelow = $00000001;
  xlCascade = $00000007;
  xlCenterAcrossSelection = $00000007;
  xlChart4 = $00000002;
  xlChartSeries = $00000011;
  xlChartShort = $00000006;
  xlChartTitles = $00000012;
  xlClassic1 = $00000001;
  xlClassic2 = $00000002;
  xlClassic3 = $00000003;
  xl3DEffects1 = $0000000D;
  xl3DEffects2 = $0000000E;
  xlAbove = $00000000;
  xlAccounting1 = $00000004;
  xlAccounting2 = $00000005;
  xlAccounting3 = $00000006;
  xlAccounting4 = $00000011;
  xlAdd = $00000002;
  xlDebugCodePane = $0000000D;
  xlDesktop = $00000009;
  xlDirect = $00000001;
  xlDivide = $00000005;
  xlDoubleClosed = $00000005;
  xlDoubleOpen = $00000004;
  xlDoubleQuote = $00000001;
  xlEntireChart = $00000014;
  xlExcelMenus = $00000001;
  xlExtended = $00000003;
  xlFill = $00000005;
  xlFirst = $00000000;
  xlFloating = $00000005;
  xlFormula = $00000005;
  xlGeneral = $00000001;
  xlGridline = $00000016;
  xlIcons = $00000001;
  xlImmediatePane = $0000000C;
  xlInteger = $00000002;
  xlLast = $00000001;
  xlLastCell = $0000000B;
  xlList1 = $0000000A;
  xlList2 = $0000000B;
  xlList3 = $0000000C;
  xlLocalFormat1 = $0000000F;
  xlLocalFormat2 = $00000010;
  xlLong = $00000003;
  xlLotusHelp = $00000002;
  xlMacrosheetCell = $00000007;
  xlMixed = $00000002;
  xlMultiply = $00000004;
  xlNarrow = $00000001;
  xlNoDocuments = $00000003;
  xlOpen = $00000002;
  xlOutside = $00000003;
  xlReference = $00000004;
  xlSemiautomatic = $00000002;
  xlShort = $00000001;
  xlSingleQuote = $00000002;
  xlStrict = $00000002;
  xlSubtract = $00000003;
  xlTextBox = $00000010;
  xlTiled = $00000001;
  xlTitleBar = $00000008;
  xlToolbar = $00000001;
  xlVisible = $0000000C;
  xlWatchPane = $0000000B;
  xlWide = $00000003;
  xlWorkbookTab = $00000006;
  xlWorksheet4 = $00000001;
  xlWorksheetCell = $00000003;
  xlWorksheetShort = $00000005;
  xlAllExceptBorders = $00000007;
  xlLeftToRight = $00000002;
  xlTopToBottom = $00000001;
  xlVeryHidden = $00000002;
  xlDrawingObject = $0000000E;

// Constants for enum XlFindLookIn
type
  XlFindLookIn = TOleEnum;
const
  xlFormulas = $FFFFEFE5;
  xlComments = $FFFFEFD0;
  xlValues = $FFFFEFBD;

// Constants for enum XlLookAt
type
  XlLookAt = TOleEnum;
const
  xlPart = $00000002;
  xlWhole = $00000001;

// Constants for enum XlSearchDirection
type
  XlSearchDirection = TOleEnum;
const
  xlNext = $00000001;
  xlPrevious = $00000002;

// Constants for enum XlSearchOrder
type
  XlSearchOrder = TOleEnum;
const
  xlByColumns = $00000002;
  xlByRows = $00000001;

// Constants for enum XlDirection
type
  XlDirection = TOleEnum;
const
  xlDown = $FFFFEFE7;
  xlToLeft = $FFFFEFC1;
  xlToRight = $FFFFEFBF;
  xlUp = $FFFFEFBE;

var
  E: OleVariant;

procedure TForm1.Button2Click(Sender: TObject);
var
  exSheet, exRng, exCell: Variant;
  FirstAddress: string;
begin
   exSheet := E.WorkSheets[1]; // Первый лист в рабочей книге.
   exRng := exSheet.UsedRange; // Используемый на листе диапазон.

   // Поиск по книге
   exCell := exRng.Find(What := 7, // Важен тип передаваемого параметра
      After := E.ActiveCell, LookIn := SmallInt(xlValues),
      LookAt := SmallInt(xlPart), SearchOrder := xlByRows,
      SearchDirection := xlNext,
      MatchCase := False, // True - учитывать регистр букв, False - не учитывать
      SearchFormat := False // Предназначен для двухбайтовых символов,
                            // в версиях для Windows не используется
      );

   if not VarIsClear(exCell) then
   begin
      FirstAddress := exCell.Address;
      repeat
         Memo1.Lines.Add('Cell[' + IntToStr(exCell.Row) + ':' +
            IntToStr(exCell.Column) + '] = ' + String(exCell.Value));
         exCell := exRng.FindNext(After := exCell);
      until FirstAddress = exCell.Address;
   end
   else
      Memo1.Lines.Add('Не найдено');
end;

Как изменить ориентацию страницы?


// Constants for enum XlPageOrientation
type
  XlPageOrientation = TOleEnum;
const
  xlLandscape = $00000002;
  xlPortrait = $00000001;

var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   E.ActiveSheet.PageSetup.Orientation := xlLandscape;
end;

Как указать сквозные строки, которые будут печататься на каждой странице отчета?


var
  E: OleVariant;

procedure TForm1.Button1Click(Sender: TObject);
begin
   // Здесь мы указываем первую и вторую
   // строки для печати на каждой странице.
   E.ActiveSheet.PageSetup.PrintTitleRows := '$1:$2';
   // Аналогично можно задать указать столбцы
   // через свойство PrintTitleColumns
end;

При использовании материала - ссылка на сайт обязательна