unit Excel;

interface

type XlChartType = (
    xl3DArea = -4098,
    xl3DAreaStacked = 78,
    xl3DAreaStacked100 = 79,
    xl3DBarClustered = 60,
    xl3DBarStacked = 61,
    xl3DBarStacked100 = 62,
    xl3DColumn = -4100,
    xl3DColumnClustered = 54,
    xl3DColumnStacked = 55,
    xl3DColumnStacked100 = 56,
    xl3DLine = -4101,
    xl3DPie = -4102,
    xl3DPieExploded = 70,
    xlArea = 1,
    xlAreaStacked = 76,
    xlAreaStacked100 = 77,
    xlBarClustered = 57,
    xlBarOfPie = 71,
    xlBarStacked = 58,
    xlBarStacked100 = 59,
    xlBubble = 15,
    xlBubble3DEffect = 87,
    xlColumnClustered = 51,
    xlColumnStacked = 52,
    xlColumnStacked100 = 53,
    xlCloneBarClustered = 102,
    xlCloneBarStacked = 103,
    xlCloneBarStacked100 = 104,
    xlCloneCol = 105,
    xlCloneColClustered = 99,
    xlCloneColStacked = 100,
    xlCloneColStacked100 = 101,
    xlCylinderBarClustered = 95,
    xlCylinderBarStacked = 96,
    xlCylinderBarStacked100 = 97,
    xlCylinderCol = 98,
    xlCylinderColClustered = 92,
    xlCylinderColStacked = 93,
    xlCylinderColStacked100 = 94,
    xlDoughnut = 4120,
    xlDoughnutExploded = 80,
    xlLine = 4,
    xlLineMarkers = 65,
    xlLineMarkersStacked = 66,
    xlLineMarkersStacked100 = 67,
    xlLineStacked = 63,
    xlLineStacked100 = 64,
    xlPie = 5,
    xlPieExploded = 69,
    xlPieOfPie = 68,
    xlPyramidBarClustered = 109,
    xlPyramidBarStacked = 110,
    xlPyramidBarStacked100 = 111,
    xlPyramidCol = 112,
    xlPyramidColClustered = 106,
    xlPyramidColStacked = 107,
    xlPyramidColStacked100 = 108,
    xlRadar = -4151,
    xlRadarFilled = 82,
    xlRadarMarkers = 81,
    xlStockHLC = 88,
    xlStockOHLC = 89,
    xlStockVHLC = 90,
    xlStockVOHLC = 91,
    xlSurface = 83,
    xlSurfaceTopView = 85,
    xlSurfaceTopViewWireframe = 86,
    xlSurfaceWireframe = 84,
    xlXYScatter = -4169,
    xlXYScatterLines = 74,
    xlXYScatterLinesNoMarkers = 75,
    xlXYScatterSmooth = 72,
    xlXYScatterSmoothNoMarkers = 73);

type TExcel = class
private
    MSExcelType: System.Type;
    MSExcel: System.Object;
    workbooks, workbook: System.Object;
    sheet: System.Object;
public
    //Instantiate Microsoft Excel
    constructor Create();

    procedure Show();
    //leave Excel to the user, disconnect this program of it
    procedure GiveUserControl();

    function NewSheet(): System.Object;
    //Write value in cell.
    procedure SetValue(range: System.Object; value: System.Object);
    //Write formula in cell.
    procedure SetFormula(range: System.Object; formula: System.String);
    // add a chart with dimensions and referencing range
    procedure AddChart(x1, y1, x2, y2: System.Int32; range: System.Object; typeOfChart: XlChartType);

    //Get a range object that contains cell (column, row).
    function GetRange(column, row: System.Int32): System.Object; overload;
    function GetRange(column, row, col2, row2: System.Int32): System.Object; overload;

end;

implementation

uses System.Reflection;

constructor TExcel.Create();
begin
  inherited Create();
  MSExcelType := System.Type.GetTypeFromProgID('Excel.Application', True);
  MSExcel := Activator.CreateInstance(MSExcelType);

  //Get a new workbook.
	//oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
  workbooks := MSExcelType.InvokeMember(
    'Workbooks', BindingFlags.GetProperty, nil, MSExcel, nil);
  workbook := MSExcelType.InvokeMember(
    'Add', BindingFlags.InvokeMethod, nil, workbooks, nil);
end;

function TExcel.NewSheet(): System.Object;
begin
  //	oSheet = (Excel._Worksheet)oWB.ActiveSheet;
  sheet :=  MSExcelType.InvokeMember(
    'ActiveSheet', BindingFlags.GetProperty, nil, workbook, nil);
  result := sheet;
end;

function TExcel.GetRange(column, row: System.Int32): System.Object; 
var
  parameters: array of System.Object;
begin
  SetLength(parameters, 2);
  parameters[0] := Convert.ToChar(column + Convert.ToInt16('A') - 1) + row.ToString();
  parameters[1] := Missing.Value;
  result := MSExcelType.InvokeMember( 'Range',
    BindingFlags.GetProperty, nil, sheet, parameters );
end;

function TExcel.GetRange(column, row, col2, row2: System.Int32): System.Object;
var
  parameters: array of System.Object;
begin
  SetLength(parameters, 2);
  parameters[0] := Convert.ToChar(column + Convert.ToInt16('A') - 1) + row.ToString();
  parameters[1] := Convert.ToChar(col2 + Convert.ToInt16('A') - 1) + row2.ToString();
  result := MSExcelType.InvokeMember( 'Range',
    BindingFlags.GetProperty, nil, sheet, parameters );
end;

procedure TExcel.SetValue(range: System.Object; value: System.Object);
var
  parameters: array of System.Object;
begin
  //Write value in cell.
  SetLength(parameters, 1);
  parameters[0] := value;
  MSExcelType.InvokeMember( 'Value', BindingFlags.SetProperty,
    nil, range, parameters );

end;

procedure TExcel.SetFormula(range: System.Object; formula: System.String);
var
  parameters: array of System.Object;
begin
  SetLength(parameters, 1);
  parameters[0] := formula;
  //rng.Formula = "=@Sum(B5..B8)";
  MSExcelType.InvokeMember( 'Formula', BindingFlags.SetProperty,
    nil, range, parameters );
  //rng.Calculate();
  MSExcelType.InvokeMember(
    'Calculate', BindingFlags.InvokeMethod, nil, range, nil);
end;

procedure TExcel.AddChart(x1, y1, x2, y2: System.Int32; range: System.Object; typeOfChart: XlChartType);
var
  charts: System.Object;
  chartObj, chart: System.Object;
  parameters: array of System.Object;
begin
  SetLength(parameters, 4);
  parameters[0] := System.Object(x1);
  parameters[1] := System.Object(y1);
  parameters[2] := System.Object(x2);
  parameters[3] := System.Object(y2);

  //office.ChartObjects charts = (office.ChartObjects)ws.ChartObjects(missing);
  //office.ChartObjectchartObj = charts.Add(150, 20, 500, 300);

  charts := MSExcelType.InvokeMember(
    'ChartObjects', BindingFlags.GetProperty, nil, sheet, nil);
  chartObj := MSExcelType.InvokeMember(
    'Add', BindingFlags.InvokeMethod, nil, charts, parameters);
  chart := MSExcelType.InvokeMember(
    'Chart', BindingFlags.GetProperty, nil, chartObj, nil);

  SetLength(parameters, 2);
  parameters[0] := range;
  parameters[1] := Missing.Value;

  // chartObj.Chart.SetSourceData(chartRange, missing);
  chart.GetType().InvokeMember(
    'SetSourceData', BindingFlags.InvokeMethod, nil, chart, parameters);

  //chartObj.Chart.ChartType = office.XlChartType.xlLine;
  setLength(parameters, 1);
  parameters[0] := System.Object(typeOfChart);
  chart.GetType().InvokeMember(
    'ChartType', BindingFlags.SetProperty, nil, chart, parameters);

end;

procedure TExcel.Show();
begin
  MSExcelType.InvokeMember(
    'Visible', BindingFlags.SetProperty, nil, MSExcel, [True]);
end;

procedure TExcel.GiveUserControl();
begin
  //Make sure Excel is visible and give the user control
  //of Microsoft Excel's lifetime.
  Show();
  MSExcelType.InvokeMember(
    'UserControl', BindingFlags.SetProperty, nil, MSExcel, [True]);
  MSExcel := nil;
  MSExcelType := nil;
end;

end.