Use an Excel .xlsx sheet as calculator (function) in Delphi?

Question

I have a fairly complex Excel sheet made by an external company. It uses several formulas, lookups etc.

From Delphi (version 6 or 7) , is it possible to have this sheet open in the background, allow a user to input a few variables, then pass these on to excel and have the calculated result returned to the delphi program ?

I know how to open excel and read the entire sheet using ADO, but here I need to send data both ways, by reference of range or coordinate.

This could be done as a unit written in Delphi, but the logic is already done in Excel, and it's simpler to write such calculations in Excel.

Simplified example:

procedure do_it;
var
  v1: integer;
  v2: integer; 
begin
   v1 := strtoint(inp1.text);   // operator provided numbers
   v2 := strtoint(inp2.text);

   theresult.caption := get_excel_value(v1, v2);   // get back result from excel formulas
end; 

function get_excel_value(v1, v2: integer) : string;
begin
   // pseudocode
   myExcel.range('A3').text := v1;
   myExcel.range('A4').text := v2;
   myExcel.recalculate;   // update excel sheet on demand
   result := myExcel.range('A10').text;
end;

Is it correct to assume that I need to be looking in the direction of Excel COM or Excel OLE ?

If possible to do - can the excel sheet call and use macros internally (invisible to Delphi) ?

Answer 1

Microsoft Office (Word, Excel and others) are applications which can be fully automated from another application. Everything you can do by hand can also be done programmatically from another application and of course from your Delphi application.

To ease the automation, Delphi is delivered with non-visual components which are "wrapper" around the underlying COM objects and exposes the same properties, methods and events as the COM interface.

Delphi has several sets of components for working with several Office versions. There are components for Office 2000, Office XP and Office 2010. 2010 version is available from Delphi XE2. Of course there are more Office versions, but don’t worry: Microsoft has carefully made his interfaces upward compatible. For example, if you use the Office 2000 components, you can still automate all Office versions from 2000 to the latest. The only restriction is that you cannot easily use new features if you use the old component.

Actually, it is better to use the older components which are capable of doing what you need to do!

This is because the compatibility in Microsoft office API goes upward and not downward.

Since there are several sets of components, you must make sure the correct version is installed.

o see the installed packages, launch Delphi and go to the menu / Component / Install Packages. You see a long list of all installed design time packages with a checkbox telling that the package is available or not.

Locate “Microsoft Office 2000 Sample Automation Server Wrapper Components” (Or Office XP) and check the checkbox in front of the one you plan to use. Click OK and verify that your component palette now include a tab “Servers”.

As an example, we will create a sample application to insert a sentence at the end of a Word document. This is quick and easy!

Create a new VCL forms application, drop a TWordApplication and a TButton on the form. Then add the code below as the button’s OnClick handler.

The quickest way to locate it is to enter WordApplication in the component palette search tool.

TForm1.Button1Click(Sender: TObject);
begin
  WordApplication1.Connect;
  WordApplication1.Visible := TRUE;
  WordApplication1.Selection.EndOf(wdStory, wdMove);
  WordApplication1.Selection.Text := 'Delphi Rocks !' + #13;
  WordApplication1.Selection.EndOf(wdStory, wdMove);
  WordApplication1.Disconnect;
end;

Answer 2

Adding to fpiette's good answer:

Another way to do it is without the components, but instead to directly create an Excel COM object on the fly in the code. Example:

uses
  ComObj, Variants;
var
  V: Variant;
  Square: Double;  // could also be of type string
begin
  try
    // Get a running Excel instance, if there is one
    V := GetActiveOleObject('Excel.Application');
  except
    on EOleSysError do  // Probably Excel was not running
    begin
      // Create a new Excel instance
      V := CreateOleObject('Excel.Application');
      V.Visible := True;  // Optional
    end;
  end;

  //
  // Do some work with Excel - a few examples
  //

  // If no workbook is open, then create one
  if VarIsClear(V.ActiveWorkbook) then
  begin
    V.Workbooks.Add;
  end;

  V.ActiveSheet.Cells[4, 1].Value := 7;  // Put a value into cell A4
  V.ActiveSheet.Cells[5, 1].Formula := '=A4 * A4';  // Put a formula into A5
  V.Calculate;  // Normally not needed, but in case the user has chosen manual calculation
  Square := V.ActiveSheet.Cells[5, 1].Value;  // Read the value of A5
end;

It should be noted, even if perhaps obvious, that all of this works only if Excel is installed on the computer where your Delphi program is run.