Creating tables and charts in Excel through Delphi

Since this question has something in common with the previous one in terms of the skills of program work with MSOffice applications (and indeed with COM objects), I will try to explain the procedure for compiling my own Delphi code based on VBA. Finally we postpone the direct answer to the question itself.

First, you need to imagine a hierarchy of objects MS Office-applications. In Excel, it is somewhat simpler than in Word.

Main:

 - Application - глобальный объект, из него можно "достучаться" до чего угодно - Workbooks - коллекция книг, (Workbook) - каждая из которых содержит - Worksheets - коллекцию рабочих листов (Worksheet) - на которых мы и размещаем информацию. - Cells - ячейки листа. Именно с ними и производится бОльшая часть работы - размещение данных, объединение/разделение, изменение шрифта и т.п. 

Actually, by and large everything. The rest is not necessary to remember, because Excel / Word itself is a powerful assistant, allowing you to record all the "manual" actions of the user in macros.

Let's get started The first stage is the placement of data on the desired sheet. Here we use the hierarchy given earlier:

 var Excel, Book, Sheet:OleVariant; i:Integer; begin Excel:=CreateOleObject('Excel.Application'); Excel.Visible:=True; Book:=Excel.Workbooks.Add; Sheet:=Book.Worksheets[1]; for i := 1 to 5 do begin Sheet.Cells[i, 1].Formula:=i; Sheet.Cells[i, 2].Formula:=Sin(i); end; 

It is remarkable that most properties accept the type Variant, that is, practically any values: strings, integer, Double, etc.

There are several ways to set a cell value: Formula , FormulaR1C1 , Value , etc. It is necessary to understand their differences and use the necessary according to the task. Formula and FormulaR1C1 most commonly used FormulaR1C1

It is also useful to understand that each cell is actually a Range object, that is, it represents any arbitrary number of cells and working with several cells from the Range does not differ from working with one cell obtained through Cells .

Next, you need to add a chart to the sheet. We do not know how to do this, and because of the one-time task, remembering where this graph is in the hierarchy of objects is wasteful.

Go to the "View" menu, click "Macros - Record Macro" and click "OK". Go to the "Insert" tab and add a simple histogram. Next, select the data range (I took B1-B5 ), change the signatures (selected A1-A5 ), confirm the changes and stop the macro recording ("View" - "Macros" - "Stop Recording"). It now remains to enter the macro ("View" - "Macros" - "Macros" - "Edit"). It should look like this VBA code:

 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Range("B1:B5") ActiveChart.SeriesCollection(1).XValues = "=Лист1!$A$1:$A$5" 

And here we need help , because then not everything is so trivial. We analyze the lines:

ActiveSheet.Shapes.AddChart.Select - the last method ( Select ) does not interest us, because it simply selects the added shape on the sheet. For program work, object allocation is unnecessary. But AddChart adds a Shape object, and we need a Chart object. VBA goes further along the path of least resistance - it uses the Application.ActiveChart property ( ActiveChart.ChartType = xlColumnClustered , etc.). But this does not suit us, because (I repeat) when working with Excel it is not desirable to use “active elements”, we need to work with exactly what we added. We find out that the Shape object has a Chart property, that is what we need. Accordingly, we build all further work on this, translating the VBA code into Delphi:

  Shape:=Sheet.Shapes.AddChart; // повторюсь - Select нам не нужен. // но при желании потом можно сделать Shape.Select Shape.Chart.ChartType := xlColumnClustered; // вместо ActiveChart - наш Shape.Chart Shape.Chart.SetSourceData(Source:=Sheet.Range['B1:B5']); Shape.Chart.SeriesCollection(1).XValues := Format('=%s!$A$1:$A$5', [Sheet.Name]); 

It should be noted here that we avoid calling a possibly localized sheet name (it may depend on the Office language, the user may rename it, etc.). Therefore, we take the real name from the property Sheet.Name.

That's all. The complete code is:

 procedure TForm8.btn2Click(Sender: TObject); var Excel, Book, Sheet, Shape:OleVariant; i:Integer; begin Excel:=CreateOleObject('Excel.Application'); Excel.Visible:=True; Book:=Excel.Workbooks.Add; Sheet:=Book.Worksheets[1]; for i := 1 to 5 do begin Sheet.Cells[i, 1].FormulaR1C1:=i; Sheet.Cells[i, 2].FormulaR1C1:=Sin(i); end; Shape:=Sheet.Shapes.AddChart; Shape.Chart.ChartType := xlColumnClustered; Shape.Chart.SetSourceData(Source:=Sheet.Range['B1:B5']); Shape.Chart.SeriesCollection(1).XValues := Format('=%s!$A$1:$A$5', [Sheet.Name]); end; 

In conclusion, note about the used constants xlColumnClustered and the like: you can connect in uses a module Excel2010 , ExcelXP or another, available in your version of Delphi. But you can also substitute the values ​​of the constants manually, through Google search (it is better to take references to the source - microsoft). For example, a search on xlColumnClustered first link gives a list of constants XlChartType Enumeration

Chart:=b.Charts.Add; Before this line in b you need to put something, it is empty. Perhaps there should be E instead of b .