Difference between revisions of "ExcelAutomation/de"
Line 391: | Line 391: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=Auszug aus den Excelkonstanten= | =Auszug aus den Excelkonstanten= | ||
+ | <syntaxhighlight> | ||
+ | unit uXLConst; | ||
+ | |||
+ | interface | ||
+ | |||
+ | const | ||
+ | // Bibliothek XlSheetType | ||
+ | xlChart = -4109; | ||
+ | xlDialogSheet = -4116; | ||
+ | xlExcel4IntlMacroSheet = 4; | ||
+ | xlExcel4MacroSheet = 3; | ||
+ | xlWorksheet = -4167; | ||
+ | |||
+ | // Bibliothek XlWBATemplate | ||
+ | xlWBATChart = -4109; | ||
+ | xlWBATExcel4IntlMacroSheet = 4; | ||
+ | xlWBATExcel4MacroSheet = 3; | ||
+ | xlWBATWorksheet = -4167; | ||
+ | |||
+ | // Bibliothek XlPattern | ||
+ | xlPatternAutomatic = -4105; | ||
+ | xlPatternChecker = 9; | ||
+ | xlPatternCrissCross = 16; | ||
+ | xlPatternDown = -4121; | ||
+ | xlPatternGray16 = 17; | ||
+ | xlPatternGray25 = -4124; | ||
+ | xlPatternGray50 = -4125; | ||
+ | xlPatternGray75 = -4126; | ||
+ | xlPatternGray8 = 18; | ||
+ | xlPatternGrid = 15; | ||
+ | xlPatternHorizontal = -4128; | ||
+ | xlPatternLightDown = 13; | ||
+ | xlPatternLightHorizontal = 11; | ||
+ | xlPatternLightUp = 14; | ||
+ | xlPatternLightVertical = 12; | ||
+ | xlPatternNone = -4142; | ||
+ | xlPatternSemiGray75 = 10; | ||
+ | xlPatternSolid = 1; | ||
+ | xlPatternUp = -4162; | ||
+ | xlUp = -4162; | ||
+ | xlPatternVertical = -4166; | ||
+ | |||
+ | // Bibliothek XlBordersIndex | ||
+ | xlInsideHorizontal = 12; | ||
+ | xlInsideVertical = 11; | ||
+ | xlDiagonalDown = 5; | ||
+ | xlDiagonalUp = 6; | ||
+ | xlEdgeBottom = 9; | ||
+ | xlEdgeLeft = 7; | ||
+ | xlEdgeRight = 10; | ||
+ | xlEdgeTop = 8; | ||
+ | |||
+ | // Bibliothek XlLineStyle | ||
+ | xlContinuous = 1; | ||
+ | xlDash = -4115; | ||
+ | xlDashDot = 4; | ||
+ | xlDashDotDot = 5; | ||
+ | xlDot = -4118; | ||
+ | xlDouble = -4119; | ||
+ | xlSlantDashDot = 13; | ||
+ | xlLineStyleNone = -4142; | ||
+ | |||
+ | // Bibliothek XlChartType | ||
+ | xlColumnClustered = 51; | ||
+ | xlColumnStacked = 52; | ||
+ | xlColumnStacked100 = 53; | ||
+ | xl3DColumnClustered = 54; | ||
+ | xl3DColumnStacked = 55; | ||
+ | xl3DColumnStacked100 = 56; | ||
+ | xlBarClustered = 57; | ||
+ | xlBarStacked = 58; | ||
+ | xlBarStacked100 = 59; | ||
+ | xl3DBarClustered = 60; | ||
+ | xl3DBarStacked = 61; | ||
+ | xl3DBarStacked100 = 62; | ||
+ | xlLineStacked = 63; | ||
+ | xlLineStacked100 = 64; | ||
+ | xlLineMarkers = 65; | ||
+ | xlLineMarkersStacked = 66; | ||
+ | xlLineMarkersStacked100 = 67; | ||
+ | xlPieOfPie = 68; | ||
+ | xlPieExploded = 69; | ||
+ | xl3DPieExploded = 70; | ||
+ | xlBarOfPie = 71; | ||
+ | xlXYScatterSmooth = 72; | ||
+ | xlXYScatterSmoothNoMarkers = 73; | ||
+ | xlXYScatterLines = 74; | ||
+ | xlXYScatterLinesNoMarkers = 75; | ||
+ | xlAreaStacked = 76; | ||
+ | xlAreaStacked100 = 77; | ||
+ | xl3DAreaStacked = 78; | ||
+ | xl3DAreaStacked100 = 79; | ||
+ | xlDoughnutExploded = 80; | ||
+ | xlRadarMarkers = 81; | ||
+ | xlRadarFilled = 82; | ||
+ | xlSurface = 83; | ||
+ | xlSurfaceWireframe = 84; | ||
+ | xlSurfaceTopView = 85; | ||
+ | xlSurfaceTopViewWireframe = 86; | ||
+ | xlBubble = 15; | ||
+ | xlBubble3DEffect = 87; | ||
+ | xlStockHLC = 88; | ||
+ | xlStockOHLC = 89; | ||
+ | xlStockVHLC = 90; | ||
+ | xlStockVOHLC = 91; | ||
+ | xlCylinderColClustered = 92; | ||
+ | xlCylinderColStacked = 93; | ||
+ | xlCylinderColStacked100 = 94; | ||
+ | xlCylinderBarClustered = 95; | ||
+ | xlCylinderBarStacked = 96; | ||
+ | xlCylinderBarStacked100 = 97; | ||
+ | xlCylinderCol = 98; | ||
+ | xlConeColClustered = 99; | ||
+ | xlConeColStacked = 100; | ||
+ | xlConeColStacked100 = 101; | ||
+ | xlConeBarClustered = 102; | ||
+ | xlConeBarStacked = 103; | ||
+ | xlConeBarStacked100 = 104; | ||
+ | xlConeCol = 105; | ||
+ | xlPyramidColClustered = 106; | ||
+ | xlPyramidColStacked = 107; | ||
+ | xlPyramidColStacked100 = 108; | ||
+ | xlPyramidBarClustered = 109; | ||
+ | xlPyramidBarStacked = 110; | ||
+ | xlPyramidBarStacked100 = 111; | ||
+ | xlPyramidCol = 112; | ||
+ | xl3DColumn = -4100; | ||
+ | xlLine = 4; | ||
+ | xl3DLine = -4101; | ||
+ | xl3DPie = -4102; | ||
+ | xlPie = 5; | ||
+ | xlXYScatter = -4169; | ||
+ | xl3DArea = -4098; | ||
+ | xlArea = 1; | ||
+ | xlDoughnut = -4120; | ||
+ | xlRadar = -4151; | ||
+ | |||
+ | // Bibliothek Constants | ||
+ | xlAll = -4104; | ||
+ | xlAutomatic = -4105; | ||
+ | xlBoth = 1; | ||
+ | xlCenter = -4108; | ||
+ | xlChecker = 9; | ||
+ | xlCircle = 8; | ||
+ | xlCorner = 2; | ||
+ | xlCrissCross = 16; | ||
+ | xlCross = 4; | ||
+ | xlDiamond = 2; | ||
+ | xlDistributed = -4117; | ||
+ | xlDoubleAccounting = 5; | ||
+ | xlFixedValue = 1; | ||
+ | xlFormats = -4122; | ||
+ | xlGray16 = 17; | ||
+ | xlGray8 = 18; | ||
+ | xlGrid = 15; | ||
+ | xlHigh = -4127; | ||
+ | xlInside = 2; | ||
+ | xlJustify = -4130; | ||
+ | xlLightDown = 13; | ||
+ | xlLightHorizontal = 11; | ||
+ | xlLightUp = 14; | ||
+ | xlLightVertical = 12; | ||
+ | xlLow = -4134; | ||
+ | xlManual = -4135; | ||
+ | xlMinusValues = 3; | ||
+ | xlModule = -4141; | ||
+ | xlNextToAxis = 4; | ||
+ | xlNone = -4142; | ||
+ | xlNotes = -4144; | ||
+ | xlOff = -4146; | ||
+ | xlOn = 1; | ||
+ | xlPercent = 2; | ||
+ | xlPlus = 9; | ||
+ | xlPlusValues = 2; | ||
+ | xlSemiGray75 = 10; | ||
+ | xlShowLabel = 4; | ||
+ | xlShowLabelAndPercent = 5; | ||
+ | xlShowPercent = 3; | ||
+ | xlShowValue = 2; | ||
+ | xlSimple = -4154; | ||
+ | xlSingle = 2; | ||
+ | xlSingleAccounting = 4; | ||
+ | xlSolid = 1; | ||
+ | xlSquare = 1; | ||
+ | xlStar = 5; | ||
+ | xlStError = 4; | ||
+ | xlToolbarButton = 2; | ||
+ | xlTriangle = 3; | ||
+ | xlGray25 = -4124; | ||
+ | xlGray50 = -4125; | ||
+ | xlGray75 = -4126; | ||
+ | xlBottom = -4107; | ||
+ | xlLeft = -4131; | ||
+ | xlRight = -4152; | ||
+ | xlTop = -4160; | ||
+ | xl3DBar = -4099; | ||
+ | xl3DSurface = -4103; | ||
+ | xlBar = 2; | ||
+ | xlColumn = 3; | ||
+ | xlCombination = -4111; | ||
+ | xlCustom = -4114; | ||
+ | xlDefaultAutoFormat = -1; | ||
+ | xlMaximum = 2; | ||
+ | xlMinimum = 4; | ||
+ | xlOpaque = 3; | ||
+ | xlTransparent = 2; | ||
+ | xlBidi = -5000; | ||
+ | xlLatin = -5001; | ||
+ | xlContext = -5002; | ||
+ | xlLTR = -5003; | ||
+ | xlRTL = -5004; | ||
+ | xlVisualCursor = 2; | ||
+ | xlLogicalCursor = 1; | ||
+ | xlSystem = 1; | ||
+ | xlPartial = 3; | ||
+ | xlHindiNumerals = 3; | ||
+ | xlBidiCalendar = 3; | ||
+ | xlGregorian = 2; | ||
+ | xlComplete = 4; | ||
+ | xlScale = 3; | ||
+ | xlClosed = 3; | ||
+ | xlColor1 = 7; | ||
+ | xlColor2 = 8; | ||
+ | xlColor3 = 9; | ||
+ | xlConstants = 2; | ||
+ | xlContents = 2; | ||
+ | xlBelow = 1; | ||
+ | xlCascade = 7; | ||
+ | xlCenterAcrossSelection = 7; | ||
+ | xlChart4 = 2; | ||
+ | xlChartSeries = 17; | ||
+ | xlChartShort = 6; | ||
+ | xlChartTitles = 18; | ||
+ | xlClassic1 = 1; | ||
+ | xlClassic2 = 2; | ||
+ | xlClassic3 = 3; | ||
+ | xl3DEffects1 = 13; | ||
+ | xl3DEffects2 = 14; | ||
+ | xlAbove = 0; | ||
+ | xlAccounting1 = 4; | ||
+ | xlAccounting2 = 5; | ||
+ | xlAccounting3 = 6; | ||
+ | xlAccounting4 = 17; | ||
+ | xlAdd = 2; | ||
+ | xlDebugCodePane = 13; | ||
+ | xlDesktop = 9; | ||
+ | xlDirect = 1; | ||
+ | xlDivide = 5; | ||
+ | xlDoubleClosed = 5; | ||
+ | xlDoubleOpen = 4; | ||
+ | xlDoubleQuote = 1; | ||
+ | xlEntireChart = 20; | ||
+ | xlExcelMenus = 1; | ||
+ | xlExtended = 3; | ||
+ | xlFill = 5; | ||
+ | xlFirst = 0; | ||
+ | xlFloating = 5; | ||
+ | xlFormula = 5; | ||
+ | xlGeneral = 1; | ||
+ | xlGridline = 22; | ||
+ | xlIcons = 1; | ||
+ | xlImmediatePane = 12; | ||
+ | xlInteger = 2; | ||
+ | xlLast = 1; | ||
+ | xlLastCell = 11; | ||
+ | xlList1 = 10; | ||
+ | xlList2 = 11; | ||
+ | xlList3 = 12; | ||
+ | xlLocalFormat1 = 15; | ||
+ | xlLocalFormat2 = 16; | ||
+ | xlLong = 3; | ||
+ | xlLotusHelp = 2; | ||
+ | xlMacrosheetCell = 7; | ||
+ | xlMixed = 2; | ||
+ | xlMultiply = 4; | ||
+ | xlNarrow = 1; | ||
+ | xlNoDocuments = 3; | ||
+ | xlOpen = 2; | ||
+ | xlOutside = 3; | ||
+ | xlReference = 4; | ||
+ | xlSemiautomatic = 2; | ||
+ | xlShort = 1; | ||
+ | xlSingleQuote = 2; | ||
+ | xlStrict = 2; | ||
+ | xlSubtract = 3; | ||
+ | xlTextBox = 16; | ||
+ | xlTiled = 1; | ||
+ | xlTitleBar = 8; | ||
+ | xlToolbar = 1; | ||
+ | xlVisible = 12; | ||
+ | xlWatchPane = 11; | ||
+ | xlWide = 3; | ||
+ | xlWorkbookTab = 6; | ||
+ | xlWorksheet4 = 1; | ||
+ | xlWorksheetCell = 3; | ||
+ | xlWorksheetShort = 5; | ||
+ | xlAllExceptBorders = 6; | ||
+ | xlLeftToRight = 2; | ||
+ | xlTopToBottom = 1; | ||
+ | xlVeryHidden = 2; | ||
+ | xlDrawingObject = 14; | ||
+ | |||
+ | // Bibliothek XLPlacement | ||
+ | xlMoveAndSize = 1; | ||
+ | xlMove = 2; | ||
+ | xlFreeFloating = 3; | ||
+ | |||
+ | // Bibliothek XlRowCol | ||
+ | xlRows = 1; | ||
+ | xlColumns = 2; | ||
+ | |||
+ | // Bibliothek XlChartLocation | ||
+ | xlLocationAsNewSheet = 1; | ||
+ | xlLocationAsObject = 2; | ||
+ | xlLocationAutomatic = 3; | ||
+ | |||
+ | // Bibliothek XlAxisType | ||
+ | xlCategory = 1; | ||
+ | xlValue = 2; | ||
+ | xlSeriesAxis = 3; | ||
+ | |||
+ | // Bibliothek ColorConstants | ||
+ | vbBlack = 0; | ||
+ | vbBlue = 16711680; | ||
+ | vbCyan = 16776960; | ||
+ | vbGreen = 65280; | ||
+ | vbMagenta = 16711935; | ||
+ | vbRed = 255; | ||
+ | vbWhite = 16777215; | ||
+ | vbYellow = 65535; | ||
+ | |||
+ | implementation | ||
+ | |||
+ | end. | ||
+ | </syntaxhighlight> | ||
<br> | <br> | ||
<br> | <br> | ||
--[[User:Olaf|Olaf]] 17:13, 20 September 2012 (UTC) | --[[User:Olaf|Olaf]] 17:13, 20 September 2012 (UTC) | ||
[[Category:Tutorials/de]] | [[Category:Tutorials/de]] |
Revision as of 20:52, 26 September 2012
Excel als OleObjekt
Alle Möglichkeiten der Excelautomation aufzuzeigen ist nicht möglich ich hoffe aber für jeden Bedarf Anregungen gefunden zu haben.
Excel ist zu dem die automatisierungsfreundlichste Software von Microsoft.
Wenn Sie Umlaute usw. verwenden, dann sollten Sie wissen, dass Excel UTF16 codierte Strings verwendet.
Für die Oleautomation muss die Unit ComObj eingebunden werden.
Für die Verwendung von Umlauten usw. muss die Unit lclproc eingebunden werden.
Speicherplatz reservieren
var
varXLApp: OleVariant;
Excel OleObjekt erstellen
begin
...
try
varXLApp := CreateOleObject('Excel.Application'); // erstellt das Excelobjekt
except
WriteLn('MS Excel kann nicht gestartet werden.'); // Fehlerbehandlung
end;
...
end;
Excel OleObjekt beenden und Speicher freigeben
begin
...
varXLApp.Quit;
varXLApp := Unassigned; //Der Speicher muss am Ende wieder freigegeben werden, sonst entstehen Speicherlecks
...
end;
Excel Version ermitteln
begin
...
ShowMessage(varXLApp.Version);
...
end;
Excel OleObjekt: Datei öffnen und Datei schliessen
begin
...
varXLApp.Workbooks.Open(strPfad); // öffnet die Exceldatei (Pfad: maximal 255 Zeichen, Alphanumerisch)
...
varXLApp.Workbooks[1].Close;
...
end;
Excel OleObjekt: Fehlermeldungen und Warnungen deaktivieren
Dekativieren von bestimmten Fehlermeldungen und Warnungen kann die Softwaresteuerung vereinfachen.
begin
...
varXLApp.DisplayAlerts := False; // schaltet diverse Fehlermeldungen von Excel aus
...
end;
Excel OleObjekt: Sichtbarkeit steuern
Für Excel 2003 und älter gilt, dass das Unterdrücken der Anzeige die Verarbeitungsgeschwindigkeit beschleunigt.
Für alle Excelversionen gilt, dass es das das Flackern des Bildschirms verhindert.
begin
...
varXLApp.Visible := False; // macht Excel unsichtbar
varXLApp.ScreenUpdating = False; // schaltet das ScreenUpdating von Excel aus
varXLApp.ScreenUpdating = True; // schaltet das ScreenUpdating von Excel ein
varXLApp.Visible := True; // macht Excel sichtbar
...
end;
Excel OleObjekt: Workbooks
Workbook einfügen
begin
...
varXLApp.Workbooks.Add; // fügt ein neues Workbook ein
...
end;
Workbook speichern
begin
...
varXLApp.Workbooks[1].Save; // speichert ein Worksheet über seinen Index
varXLApp.Workbooks['Test.xls'].Save; // speicher ein Worksheet über seinen Namen
...
end;
Workbook unter neuem Namen speichern
begin
...
varXLApp.Workbooks[1].SaveAs('D:\Test.xls', varXLApp.Workbooks[1].fileformat);
...
end;
Excel OleObjekt: Worksheets
Excel OleObjekt: Worksheet einfügen
begin
...
varXLApp.WorkBooks[1].WorkSheets.Add; //fügt ein neues WorkSheet hinzu
...
end;
Worksheet umbenennen
begin
...
varXLApp.WorkBooks[1].WorkSheets[1].Name := 'Neuer Name'; // benennt ein Worksheet über seinen Index um
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Name := 'Neuer Name'; // benennt ein Worksheet über seinen Namen um
...
end;
Worksheet löschen
begin
...
varXLApp.WorkBooks[1].WorkSheets[3].Delete; // löscht ein Worksheet über seinen Index
varXLApp.WorkBooks[1].WorkSheets['Tabelle3'].Delete; // löscht ein Worksheet über seinen Namen
...
end;
Worksheet aktivieren
begin
...
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Activate;
...
end;
Excel OleObjekt: Formel einfügen
begin
...
// setzt den Autofilter auf die erste Zeile
varXLApp.Range['C6', 'C6'].Formula := '=Sum(A5:A7)'
...
end;
Excel OleObjekt Zellen und Ranges
Autofilter setzen
begin
...
// setzt den Autofilter auf die erste Zeile
varXLApp.WorkBooks[1].WorkSheets[1].Rows.Rows[1].AutoFilter;
...
end;
Excel OleObjekt: Zeilenbreite und Spaltenhöhe setzen
begin
...
// Setzt die Spaltenbreite für Spalte 1
varXLApp.WorkBooks[1].WorkSheets[1].Columns.Columns[1].ColumnWidth := 70;
// oder
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Columns.Columns[1].ColumnWidth := 70;
// Setzt die Spaltenhöhe für Zeile 1
varXLApp.WorkBooks[1].WorkSheets[1].Rows.Rows[1].RowHeight := 60;
// oder
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Rows.Rows[1].RowHeight := 60;
...
end;
Rahmen zeichnen
begin
...
// Zeichnet einen Rahmen um den Bereich "B2:C3"
// Der Parameter BorderAround hat folgende Syntax: BorderAround(Linienstil, Linenstärke, Farbe)
varXLApp.WorkBooks[1].WorkSheets[1].Range['B2:C3'].BorderAround(1, 2, 1);
...
end;
Hyperlinks und URLS einfügen
begin
...
// Fügt eine URL in Zelle "A2" in Sheet 2 ein
// Alternative 1
varXLApp.WorkBooks[1].Worksheets[2].Hyperlinks.add(
varXLApp.WorkBooks[1].Worksheets[2].Cells[2, 1], 'http://www.google.de');
// Alternative 2
varXLApp.WorkBooks[1].worksheets[2].Cells[2,1].Formula := '=HYPERLINK("http://www.google.de")';
// Fügt eine Emailadresse als Formel in Zelle "A3" in Sheet 2 ein
varXLApp.WorkBooks[1].worksheets[2].Cells[3,1].Formula := '=HYPERLINK("mailto:test.testmann.@test.com","Email")';
...
end;
Hintergrundbild einfügen
begin
...
varXLApp.WorkBooks[1].WorkSheets[1].SetBackGroundPicture('D:\Testbild.JPG'); // fügt das Hintergrundbild über Index ein
varXLApp.WorkBooks['Test.xls'].WorkSheets['Tabelle2'].SetBackGroundPicture('D:\Testbild.JPG'); // fügt das Hintergrundbild über Namen ein
...
end;
Wert einfügen
begin
...
// Alternative 1
varXLApp.WorkBooks[1].WorkSheets[1].Range['A5'].Value := 3;
varXLApp.WorkBooks[1].WorkSheets[1].Range['A6'].Value := 3;
varXLApp.WorkBooks[1].WorkSheets[1].Range['A7'].Value := 3;
// Alternative 2
varXLApp.WorkBooks[1].WorkSheets[1].Range['A5', 'A7'].Value := 3;
// Alternative 3
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Range['A5', 'A7'].Value := 3;
// Alternative ...
...
varXLApp.WorkSheets[1].Range['A15'].Value := UTF8ToUTF16('Überlegung'); // Übernimmt Umlaute usw. und benötigt
// dafür die Unit lclproc
...
end;
Formel einfügen
begin
...
// Alternative 1
varXLApp.WorkBooks[1].worksheets[2].Cells[6,3].Formula := '=Sum(A5:A7)';
// Alternative 2
varXLApp.WorkBooks[1].WorkSheets[2].Range['C6'].Formula := '=Sum(A5:A7)';
...
end;
Arbeiten mit Schriften
begin
...
// Legt die Schriftart fest
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Cells[1, 1].Font.Name := 'Arial';
// Legt die Schriftgröße fest
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Cells[1, 1].Font.Size := 20;
// Legt die Fettschrift fest
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Cells[1, 1].Font.Bold := True;
//Legt die Kursivschrift fest
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Cells[1, 1].Font.Italic := True;
// Unterstreicht den Eintrag
varXLApp.WorkBooks[1].WorkSheets['Tabelle1'].Cells[1, 1].Font.Underline := True;
...
end;
Excel OleObjekt: Chart erstellen
Die Beispiele gehen davon aus, dass das Excelobjekt (varXLApp) bereits existiert und die Exceldatei geöffnet ist.
Chart Speicher reservieren
var
varDiagramm: OleVariant;
Chart Speicher freigeben
begin
...
varDiagramm := Unassigned;
...
end;
Chart Objekt erstellen
begin
...
varDiagramm := varXLApp.Charts.Add; // Fügt ein Chart ein
...
end;
Chart aktivieren
begin
...
varDiagramm.Activate; // Aktiviert das Chart
...
end;
Chart Diagramtyp festlegen
begin
...
varDiagramm.ChartType := xlColumnClustered; // Legt den Diagramtyp fest
...
end;
Chart Datenherkunft (Datenquelle) festlegen
begin
...
varDiagramm.SetSourceData (Source := varXLApp.WorkSheets['Tabelle1'].Range['A1:E13'],
PlotBy := xlColumns); // Legt die Datenherkunft (das Excelsheet und den Bereich im Excelsheet) fest
...
end;
Chart verschieben festlegen
begin
...
// Diagramm im selben Tabellenblatt wie die Datentabelle.
varDiagramm.Location (Where := xlLocationAsObject, Name:= 'Tabelle1');
...
end;
Chart Titel
begin
...
varXLApp.Worksheets[1].ChartObjects[1].Chart.HasTitle := True; // Legt fest, dass das Chart einen Titel haben darf
varXLApp.Worksheets[1].ChartObjects[1].Chart.ChartTitle
.Characters.Text := 'Test'; // Legt den Namen des Chart Titels fest
varXLApp.Worksheets[1].ChartObjects[1].Chart.ChartTitle
.Font.Color := vbRed; // Legt die Schriftfarbe des Diagrammtitels fest
varXLApp.Worksheets[1].ChartObjects[1].Chart.ChartTitle
.Interior.Color := vbCyan; // Legt die Hintergrundarbe des Diagrammtitels fest
...
end;
Chart Achsenbeschriftung
begin
...
varXLApp.Worksheets[1].ChartObjects[1].Chart.HasAxis(xlCategory) := True; // Legt fest, dass das Chart Achsen hat
varXLApp.Worksheets[1].ChartObjects[1].Chart.Axes(xlCategory).TickLabels
.Font.Color := vbBlue; // Legt die Schriftfarbe der Achsenkategorie fest
varXLApp.Worksheets[1].ChartObjects[1].Chart.Axes(xlValue).HasTitle:= True; // Legt fest, dass das Chart eine Achsenbeschriftung haben darf
varXLApp.Worksheets[1].ChartObjects[1].Chart.Axes(xlValue).AxisTitle
.Characters.Text := 'Euro'; // Legt den Achsentitel fest, Achtung: es gibt Probleme mit dem Eurozeichen €
varXLApp.Worksheets[1].ChartObjects[1].Chart.Axes(xlValue).AxisTitle
.Font.Color := vbBlue; // Legt die Schriftfarbe des Achsentitels fest
varXLApp.Worksheets[1].ChartObjects[1].Chart.Axes(xlValue).AxisTitle
.Font.Size := 12; // Legt die Schriftgrösse des Achsentitels fest
...
end;
Chart Legende
begin
...
varXLApp.Worksheets[1].ChartObjects[1].Chart.SeriesCollection(1)
.Name := '="Eins"'; //Legt die Beschriftung der Chartlegende fest
...
end;
Auszug aus den Excelkonstanten
unit uXLConst;
interface
const
// Bibliothek XlSheetType
xlChart = -4109;
xlDialogSheet = -4116;
xlExcel4IntlMacroSheet = 4;
xlExcel4MacroSheet = 3;
xlWorksheet = -4167;
// Bibliothek XlWBATemplate
xlWBATChart = -4109;
xlWBATExcel4IntlMacroSheet = 4;
xlWBATExcel4MacroSheet = 3;
xlWBATWorksheet = -4167;
// Bibliothek XlPattern
xlPatternAutomatic = -4105;
xlPatternChecker = 9;
xlPatternCrissCross = 16;
xlPatternDown = -4121;
xlPatternGray16 = 17;
xlPatternGray25 = -4124;
xlPatternGray50 = -4125;
xlPatternGray75 = -4126;
xlPatternGray8 = 18;
xlPatternGrid = 15;
xlPatternHorizontal = -4128;
xlPatternLightDown = 13;
xlPatternLightHorizontal = 11;
xlPatternLightUp = 14;
xlPatternLightVertical = 12;
xlPatternNone = -4142;
xlPatternSemiGray75 = 10;
xlPatternSolid = 1;
xlPatternUp = -4162;
xlUp = -4162;
xlPatternVertical = -4166;
// Bibliothek XlBordersIndex
xlInsideHorizontal = 12;
xlInsideVertical = 11;
xlDiagonalDown = 5;
xlDiagonalUp = 6;
xlEdgeBottom = 9;
xlEdgeLeft = 7;
xlEdgeRight = 10;
xlEdgeTop = 8;
// Bibliothek XlLineStyle
xlContinuous = 1;
xlDash = -4115;
xlDashDot = 4;
xlDashDotDot = 5;
xlDot = -4118;
xlDouble = -4119;
xlSlantDashDot = 13;
xlLineStyleNone = -4142;
// Bibliothek XlChartType
xlColumnClustered = 51;
xlColumnStacked = 52;
xlColumnStacked100 = 53;
xl3DColumnClustered = 54;
xl3DColumnStacked = 55;
xl3DColumnStacked100 = 56;
xlBarClustered = 57;
xlBarStacked = 58;
xlBarStacked100 = 59;
xl3DBarClustered = 60;
xl3DBarStacked = 61;
xl3DBarStacked100 = 62;
xlLineStacked = 63;
xlLineStacked100 = 64;
xlLineMarkers = 65;
xlLineMarkersStacked = 66;
xlLineMarkersStacked100 = 67;
xlPieOfPie = 68;
xlPieExploded = 69;
xl3DPieExploded = 70;
xlBarOfPie = 71;
xlXYScatterSmooth = 72;
xlXYScatterSmoothNoMarkers = 73;
xlXYScatterLines = 74;
xlXYScatterLinesNoMarkers = 75;
xlAreaStacked = 76;
xlAreaStacked100 = 77;
xl3DAreaStacked = 78;
xl3DAreaStacked100 = 79;
xlDoughnutExploded = 80;
xlRadarMarkers = 81;
xlRadarFilled = 82;
xlSurface = 83;
xlSurfaceWireframe = 84;
xlSurfaceTopView = 85;
xlSurfaceTopViewWireframe = 86;
xlBubble = 15;
xlBubble3DEffect = 87;
xlStockHLC = 88;
xlStockOHLC = 89;
xlStockVHLC = 90;
xlStockVOHLC = 91;
xlCylinderColClustered = 92;
xlCylinderColStacked = 93;
xlCylinderColStacked100 = 94;
xlCylinderBarClustered = 95;
xlCylinderBarStacked = 96;
xlCylinderBarStacked100 = 97;
xlCylinderCol = 98;
xlConeColClustered = 99;
xlConeColStacked = 100;
xlConeColStacked100 = 101;
xlConeBarClustered = 102;
xlConeBarStacked = 103;
xlConeBarStacked100 = 104;
xlConeCol = 105;
xlPyramidColClustered = 106;
xlPyramidColStacked = 107;
xlPyramidColStacked100 = 108;
xlPyramidBarClustered = 109;
xlPyramidBarStacked = 110;
xlPyramidBarStacked100 = 111;
xlPyramidCol = 112;
xl3DColumn = -4100;
xlLine = 4;
xl3DLine = -4101;
xl3DPie = -4102;
xlPie = 5;
xlXYScatter = -4169;
xl3DArea = -4098;
xlArea = 1;
xlDoughnut = -4120;
xlRadar = -4151;
// Bibliothek Constants
xlAll = -4104;
xlAutomatic = -4105;
xlBoth = 1;
xlCenter = -4108;
xlChecker = 9;
xlCircle = 8;
xlCorner = 2;
xlCrissCross = 16;
xlCross = 4;
xlDiamond = 2;
xlDistributed = -4117;
xlDoubleAccounting = 5;
xlFixedValue = 1;
xlFormats = -4122;
xlGray16 = 17;
xlGray8 = 18;
xlGrid = 15;
xlHigh = -4127;
xlInside = 2;
xlJustify = -4130;
xlLightDown = 13;
xlLightHorizontal = 11;
xlLightUp = 14;
xlLightVertical = 12;
xlLow = -4134;
xlManual = -4135;
xlMinusValues = 3;
xlModule = -4141;
xlNextToAxis = 4;
xlNone = -4142;
xlNotes = -4144;
xlOff = -4146;
xlOn = 1;
xlPercent = 2;
xlPlus = 9;
xlPlusValues = 2;
xlSemiGray75 = 10;
xlShowLabel = 4;
xlShowLabelAndPercent = 5;
xlShowPercent = 3;
xlShowValue = 2;
xlSimple = -4154;
xlSingle = 2;
xlSingleAccounting = 4;
xlSolid = 1;
xlSquare = 1;
xlStar = 5;
xlStError = 4;
xlToolbarButton = 2;
xlTriangle = 3;
xlGray25 = -4124;
xlGray50 = -4125;
xlGray75 = -4126;
xlBottom = -4107;
xlLeft = -4131;
xlRight = -4152;
xlTop = -4160;
xl3DBar = -4099;
xl3DSurface = -4103;
xlBar = 2;
xlColumn = 3;
xlCombination = -4111;
xlCustom = -4114;
xlDefaultAutoFormat = -1;
xlMaximum = 2;
xlMinimum = 4;
xlOpaque = 3;
xlTransparent = 2;
xlBidi = -5000;
xlLatin = -5001;
xlContext = -5002;
xlLTR = -5003;
xlRTL = -5004;
xlVisualCursor = 2;
xlLogicalCursor = 1;
xlSystem = 1;
xlPartial = 3;
xlHindiNumerals = 3;
xlBidiCalendar = 3;
xlGregorian = 2;
xlComplete = 4;
xlScale = 3;
xlClosed = 3;
xlColor1 = 7;
xlColor2 = 8;
xlColor3 = 9;
xlConstants = 2;
xlContents = 2;
xlBelow = 1;
xlCascade = 7;
xlCenterAcrossSelection = 7;
xlChart4 = 2;
xlChartSeries = 17;
xlChartShort = 6;
xlChartTitles = 18;
xlClassic1 = 1;
xlClassic2 = 2;
xlClassic3 = 3;
xl3DEffects1 = 13;
xl3DEffects2 = 14;
xlAbove = 0;
xlAccounting1 = 4;
xlAccounting2 = 5;
xlAccounting3 = 6;
xlAccounting4 = 17;
xlAdd = 2;
xlDebugCodePane = 13;
xlDesktop = 9;
xlDirect = 1;
xlDivide = 5;
xlDoubleClosed = 5;
xlDoubleOpen = 4;
xlDoubleQuote = 1;
xlEntireChart = 20;
xlExcelMenus = 1;
xlExtended = 3;
xlFill = 5;
xlFirst = 0;
xlFloating = 5;
xlFormula = 5;
xlGeneral = 1;
xlGridline = 22;
xlIcons = 1;
xlImmediatePane = 12;
xlInteger = 2;
xlLast = 1;
xlLastCell = 11;
xlList1 = 10;
xlList2 = 11;
xlList3 = 12;
xlLocalFormat1 = 15;
xlLocalFormat2 = 16;
xlLong = 3;
xlLotusHelp = 2;
xlMacrosheetCell = 7;
xlMixed = 2;
xlMultiply = 4;
xlNarrow = 1;
xlNoDocuments = 3;
xlOpen = 2;
xlOutside = 3;
xlReference = 4;
xlSemiautomatic = 2;
xlShort = 1;
xlSingleQuote = 2;
xlStrict = 2;
xlSubtract = 3;
xlTextBox = 16;
xlTiled = 1;
xlTitleBar = 8;
xlToolbar = 1;
xlVisible = 12;
xlWatchPane = 11;
xlWide = 3;
xlWorkbookTab = 6;
xlWorksheet4 = 1;
xlWorksheetCell = 3;
xlWorksheetShort = 5;
xlAllExceptBorders = 6;
xlLeftToRight = 2;
xlTopToBottom = 1;
xlVeryHidden = 2;
xlDrawingObject = 14;
// Bibliothek XLPlacement
xlMoveAndSize = 1;
xlMove = 2;
xlFreeFloating = 3;
// Bibliothek XlRowCol
xlRows = 1;
xlColumns = 2;
// Bibliothek XlChartLocation
xlLocationAsNewSheet = 1;
xlLocationAsObject = 2;
xlLocationAutomatic = 3;
// Bibliothek XlAxisType
xlCategory = 1;
xlValue = 2;
xlSeriesAxis = 3;
// Bibliothek ColorConstants
vbBlack = 0;
vbBlue = 16711680;
vbCyan = 16776960;
vbGreen = 65280;
vbMagenta = 16711935;
vbRed = 255;
vbWhite = 16777215;
vbYellow = 65535;
implementation
end.
--Olaf 17:13, 20 September 2012 (UTC)