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) ?

This is easily possible using COM automation, which is fully supported by Delphi. See the *Servers* tab on the component palette. If you don't have one, use @fpiette's instructions below to install the Office packages. For an example of writing a range of values to Excel, see https://stackoverflow.com/a/16642049/62576 A search of this site for *[delphi] excel* will find many additional examples for you. — Ken White, Jan 29 '21 at 15:11

score 6 · Accepted Answer · answered Jan 29 '21 at 14:17

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;

Thanks, this works. But in your example (for my old Delphi anyway) I had to add p1 and p2 as VAR of type OLEVariant, assign the values (wdStory, wdMove), then pass p1 and p2. Found it here: https://delphi.developpez.com/faq/?page=Selection-et-deplacement — MyICQ, Jan 30 '21 at 00:39
done, my apologies. For others, I can recommend the blog by F.Piette: http://francois-piette.blogspot.com/ well worth a read. — MyICQ, Jan 30 '21 at 19:54

score 4 · Answer 2 · answered Jan 29 '21 at 15:37

4

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.

answered Jan 29 '21 at 15:37

Matthias B

404
4
11

1

Thank you for adding this answer, because I think it is far preferable to using the wrapper-components mentioned in the other answer, especially if one accesses the COM objects via the type-library import units which are supplied with Delphi (e.g. Word2000.Pas). The main reason this way is preferable to the wrapper components is that the wrapper components are Delphi-version-specific, which can cause problems if you need to open a project which was created several Delphi versions ago. – MartynA Jan 29 '21 at 16:01
1

A drawback to direct use of the COM objects is it makes a lot of errors runtime errors as the compiler won't be able to catch if a method does not exist or what the parameters should be. The type-library import provides that information at design / compile time which can be helpful. One option if to develop using the imports until the code works and then convert to direct use. – Brian Jan 29 '21 at 16:57
1

Working with late binding is a pain. You lack the feature of the IDE that shows you the methods, properties, arguments. You cannot be sure your code is correct until you run every single line of it. Development will be slowed down considerably. IMO it is much better to use the components as I said in my answer. – fpiette Jan 29 '21 at 21:10
2

@fpiette but the OP could use early binding on the COM objects without going anywhere near the wrapper components. – MartynA Jan 29 '21 at 22:23
How would I do early binding on the COM objects without using the TypeLib/wrapper components? I have never used the components in practice (I just know they exist), and it was a good 'aha!' moment for me here, when I realized that using the components gives me guidance on what methods etc. are available (code completion etc.) But how can I get that, early binding, without the components? – Matthias B Jan 30 '21 at 17:21

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

2 Answers2