Work with Excel (any version)
lazarus / 34-Windows

----------------------------------------------------------------------
bambamns · 2012-05-10 04:17

HI,

I'd tried to find some solution to work with Excel like I did using
Delphi, so I hope it will help to others.

This method needs Excel to be installed on client machine because it
use OLE  to access it.

open/read excel file ... new form with button, stringgrid and edit
Code: [Select]uses .....  comobj;

procedure TForm1.Button1Click(Sender: TObject);

Var XLApp: OLEVariant;
      x,y: byte;
      path: variant;

begin
 XLApp := CreateOleObject('Excel.Application'); // comobj
 try
   XLApp.Visible := False;         // Hide Excel
   XLApp.DisplayAlerts := False;
   path := edit1.Text;
   XLApp.Workbooks.Open(Path);     // Open the Workbook
   for x := 1 to 4 do
    begin
     for y := 1 to 6 do
      begin
       SG.Cells[x,y] := XLApp.Cells[y,x].Value;
      end;
    end;
 finally
   XLApp.Quit;
   XLAPP := Unassigned;
  end;                                                               


if you made some changes and you wont them to write in excel you can
use :
Code: [Select]XLApp.Cells[x,y].Value := SG.Cells[y,x];
if you wont to save :
Code: [Select]XLApp.ActiveWorkBook.Save;

I hope it helps - it can be used with any version of excel
(95,2003,2007,2010)

----------------------------------------------------------------------
BigChimp · 2012-05-10 05:14

Nice. There's of course also fpspreadsheet which doesn't require Excel
to be installed... but is a bit less compatible with Excel than...
Excel

See also:
http://wiki.lazarus.freepascal.org/Office_Automation#Using_COM_Automat
ion_to_interact_with_OpenOffice_and_Microsoft_Office... you could
perhaps add this Excel example there?

Thanks for posting!

----------------------------------------------------------------------
bambamns · 2012-05-10 18:24

I've posted it on wiki http://wiki.lazarus.freepascal.org/Office_Autom
ation#Reading.2FWriting_an_Excel_file_using_OLE and I hope it will be
helpful.

With this method I managed to use cell formating, formulas ......

Just record an macro in Excel , copy it to Lazarus code , change it to
use OleVariant variables and it will be like it is a Excel VBA, bu
runned from compiled Lazarus code.

----------------------------------------------------------------------
aviatorz · 2013-01-24 14:56

I found this... to get te last row and the last column used on active
excel sheet.

Code: [Select]LastRow := XLApp.ActiveSheet.UsedRange.Rows.Count;
LastColumn:=XLApp.ActiveSheet.UsedRange.Columns.Count;

or if wanna get the last row and last column of a sheet

Code: [Select]lastrow :=
XLApp.WorkBooks[1].WorkSheets[1].UsedRange.Rows.Count;
lastcolumn:=XLApp.WorkBooks[1].WorkSheets[1].UsedRange.Columns.Count;

----------------------------------------------------------------------
CM630 · 2013-05-16 06:10

I have a problem- I cannot determine if there is an open instance of
Excel.
I use this code:

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var
  XL: Variant;
begin
    XL := CreateOleObject('Excel.Application');
    if (XL.Visible= True) then ShowMessage ('Excel is open') else
ShowMessage ('Excel is not open');
    XL.Quit;
end; 
and it always says Excel is not open.

What am I doing wrong?
I am using MS Office 2003 (11) on Windows XP.

----------------------------------------------------------------------
Jurassic Pork · 2013-05-16 06:32

hello,
try this code :
Code: [Select]var
  XL: Variant;
begin
  try
    XL := GetActiveOleObject('Excel.Application');
  except
    try
      ShowMessage('Excel not already open create XL Object');
      // If no instance of Excel is running, try to Create a new Excel
Object
      XL := CreateOleObject('Excel.Application');
    except
      ShowMessage('Cannot start Excel/Excel not installed ?');
      Exit;
    end;
  end;
end;       
friendly, J.P

----------------------------------------------------------------------
CM630 · 2013-05-16 07:13

Thanks, it seems okay, just a line XL.Visible:=True needs to be added
after XL := CreateOleObject('Excel.Application');!

I just came into two other problems with Word.
1.     wrd.ActiveDocument.Application.Selection.start:=wrd.ActiveDocum
ent.Application.Selection.end+1; causes an error: Fatal: Syntax error,
"identifier" expected but "END" found
This seems like a bug in Lazarus to me.

2.  wrd.ActiveDocument.Application.Selection.Text:='АБВГД'; produces
some weird symbols, or exactly АБВГД.
SysToUTF8('АБВГД'); results in ꃐ鋑ꃐ胢킘馀ꃐ胢킜

SysToUTF8(utf8tosys('АБВГД'))  results in  郐釐鋐鏐铐

utf8tosys('АБВГД') results in 쇀쏂
Any idea what conversion should do?

----------------------------------------------------------------------
ludob · 2013-05-16 12:54

Quote1.     wrd.ActiveDocument.Application.Selection.start:=wrd.Active
Document.Application.Selection.end+1; causes an error: Fatal: Syntax
error, "identifier" expected but "END" found
This seems like a bug in Lazarus to me.end is a reserved word in
pascal. It can't be uses as a method name. You could raise a bug
report but I'm not sure something can be done about it. The typelib
importer adds
an underscore in front of reserved words to avoid this but that is not
possible with late binding.

Quote2.  wrd.ActiveDocument.Application.Selection.Text:='АБВГД';
produces some weird symbols, or exactly АБВГД.You have to use
widestring with OLE/COM/ActiveX.

----------------------------------------------------------------------
Timewarp · 2013-05-16 13:46

Quoteend is a reserved word in pascalIt's reserved in Delphi also, but
works cases like this. Bug in FPC

Probably works

wrd.ActiveDocument.Application.Selection.start:=wrd.ActiveDocument.App
lication.Selection.&end+1;

----------------------------------------------------------------------
Jurassic Pork · 2013-05-16 15:15

it seems to be OK to escape reserved word with ampersand  with FPC >
2.5.1 (  is it true ? ) .

here an  example using a variable "end" and an "end"  in automation  :
Code: [Select]var
  XL: Variant;
  x,y: byte;
  &end : byte;
begin
  try
    XL := GetActiveOleObject('Excel.Application');
  except
    try
      ShowMessage('Excel not already open create XL Object');
      // If no instance of Excel is running, try to Create a new Excel
Object
      XL := CreateOleObject('Excel.Application');
    except
      ShowMessage('Cannot start Excel/Excel not installed ?');
      Exit;
    end;


   XL.Visible := True;
   XL.DisplayAlerts := False;
   XL.Workbooks.Open('f:\demo.xls');     // Open the Workbook
   for &end := 1 to 4 do
    begin
     for y := 1 to 6 do
      begin
       SG.Cells[&end,y] := XL.Cells[y,&end].Value;  // fill stringgrid
with values
      end;
    end;
   // select the last cell, before a blank in the first Row:
   // Const xltoRight  -4161
    XL.Range['A1'].&End[-4161].Select;
//   XL.Quit;
//   XL := Unassigned;
  end;
end;    
strange i am in mode {$mode objfpc} not in mode delphi ????

Friendly, J.P

----------------------------------------------------------------------
ludob · 2013-05-17 06:35

Quote from: Timewarp on May 16, 2013, 03:46:32 pmQuoteend is a
reserved word in pascalIt's reserved in Delphi also, but works cases
like this. Bug in FPC

Please create a bug report. The problem won't get fixed by giving
irrefutable statements on the forum

----------------------------------------------------------------------
CM630 · 2013-05-17 08:01

Quote2.  wrd.ActiveDocument.Application.Selection.Text:='АБВГД';
produces some weird symbols, or exactly АБВГД.You have to use
widestring with OLE/COM/ActiveX.
[/quote]

So I did wrd.ActiveDocument.Application.Selection.Text:= UTF8Decode(
#13#10+ 'АБВГД' +#13#10); but it crashes the app.
After that I did
Code: [Select]function StrToWidestr (aString: String): WideString;
begin
  Result:= UTF8Decode(aString);
end;       

and wrd.ActiveDocument.Application.Selection.Text:= StrToWidestr(
#13#10+ 'АБВГД' +#13#10);
That way everything is okay, I just wonder if the first way should
really cause a crash.

If nobody else files the .END bug, I will do it these days, I am just
not sure if I can describe it properly.

Currently I have a very odd problem with putting images into
Word(Error: not enough storage is available to complete this
operation), probably I'll ask here later.

Edit: It occurred that the problems is caused, because I use
GetTempDir, which results in an 8.3 string, and OLE does not
understand it. Converting the filename to widestring solved the
problem.
As a conclusion, there is something wrong in Jurassic Pork's source:
XL.Workbooks.Open('f:\demo.xls');  should not be used without
conversion to widestring.

----------------------------------------------------------------------
ludob · 2013-05-17 17:41

QuoteSo I did wrd.ActiveDocument.Application.Selection.Text:=
UTF8Decode( #13#10+ 'АБВГД' +#13#10); but it crashes the app.UTFDecode
returns a unicodestring which is not a widestring. OLE uses BSTR
(http://msdn.microsoft.com/en-
us/library/windows/desktop/ms221069(v=vs.85).aspx). Only widestring is
compatible with BSTR in FPC.

EDIT: forgot to reply the question:
QuoteThat way everything is okay, I just wonder if the first way
should really cause a crash.BSTR uses a 4 byte length indicator in
front of the string, So you can easily imagine what happens if there
is something else than the expected length in front of the string.
Also BSTR's are allocated with SysAllocString and freed with
SysFreeString. These are windows functions and have nothing to do with
FPC memory management. Widestring takes care of all that.  But passing
reference counted unicodestrings to OLE can easily break things in a
very nasty way.

----------------------------------------------------------------------
CM630 · 2013-05-18 13:15

Quote from: ludob on May 17, 2013, 07:41:07 pmQuoteSo I did
wrd.ActiveDocument.Application.Selection.Text:= UTF8Decode( #13#10+
'АБВГД' +#13#10); but it crashes the app.UTFDecode returns a
unicodestring which is not a widestring. OLE uses BSTR
(http://msdn.microsoft.com/en-
us/library/windows/desktop/ms221069(v=vs.85).aspx). Only widestring is
compatible with BSTR in FPC.

In this case there is a mistake here: http://lazarus-
ccr.sourceforge.net/docs/rtl/system/utf8decode.html, it says Convert
an UTF-8 encoded unicode string to a widestring

So is there is a better way, than mine? If I get things right- OLE
uses BSTR, which Lazarus does not support, but widestrings might be
good enough?

----------------------------------------------------------------------
ludob · 2013-05-18 18:03

QuoteIn this case there is a mistake here: http://lazarus-
ccr.sourceforge.net/docs/rtl/system/utf8decode.html, it says Convert
an UTF-8 encoded unicode string to a widestringThe function prototype
says Code: [Select]function UTF8Decode(const s:
UTF8String):UnicodeString; and the text says widestring. That is
indeed a mistake on Windows. On unix, widestring and unicodestring are
identical.

QuoteSo is there is a better way, than mine? If I get things right-
OLE uses BSTR, which Lazarus does not support, but widestrings might
be good enough?Widestring on windows is compatible with BSTR. So there
is support for BSTR in Lazarus/FPC.

Use a simple variable as intermediate step:
Code: [Select]var w:widestring;
begin
...
w:= UTF8Decode( #13#10+ 'АБВГД' +#13#10);
wrd.ActiveDocument.Application.Selection.Text:=w;
...
end;EDIT:
Or use a typecast:
Code: [Select]wrd.ActiveDocument.Application.Selection.Text:=
WideString(UTF8Decode( #13#10+ 'АБВГД' +#13#10));

----------------------------------------------------------------------
CM630 · 2013-05-22 05:46

Quote from: ludob on May 18, 2013, 08:03:59 pmUse a simple variable as
intermediate step:
Code: [Select]var w:widestring;
begin
...
w:= UTF8Decode( #13#10+ 'АБВГД' +#13#10);
wrd.ActiveDocument.Application.Selection.Text:=w;
...
end;In fact that was my first solution, then I made a function, which
makes things easier.

Quote from: ludob on May 18, 2013, 08:03:59 pmOr use a typecast:
Code: [Select]wrd.ActiveDocument.Application.Selection.Text:=
WideString(UTF8Decode( #13#10+ 'АБВГД' +#13#10));This seems to me like
a righter solution.

Edit: I added some stuff in the wiki.

----------------------------------------------------------------------
CM630 · 2013-06-18 12:12

Quote from: ludob on May 17, 2013, 08:35:50 amQuote from: Timewarp on
May 16, 2013, 03:46:32 pmQuoteend is a reserved word in pascalIt's
reserved in Delphi also, but works cases like this. Bug in FPC

Please create a bug report. The problem won't get fixed by giving
irrefutable statements on the forum
Reported as bug №24620.

----------------------------------------------------------------------
totya · 2015-04-05 19:25

Hi!

Quote from: bambamns on May 10, 2012, 06:17:46 am
wrong:

Code: [Select]       SG.Cells[x,y] := XLApp.Cells[y,x].Value;

good:

Code: [Select]       SG.Cells[x,y] := XLApp.Cells[x,y].Value;

----------------------------------------------------------------------
TopherIII · 2017-10-19 07:55

Hi I know this is a bit of an old post but can someone please help me?

I am working on an assignment for school and i am trying to store data
in a string grid into excel but it keeps coming up with an error.

Here is my code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,  ExtCtrls,
Grids, ExtDlgs,comobj; type   { TForm1 }   TForm1 = class(TForm)   
btnDate: TButton;    Button1: TButton;    Button2: TButton;   
CalendarDialog1: TCalendarDialog;    lbledtPath: TLabeledEdit;   
lbledtName: TLabeledEdit;    lbledtAddress: TLabeledEdit;   
lbledtphonenumber: TLabeledEdit;    lbledtmoneyowing: TLabeledEdit;  
lbledtDate: TLabeledEdit;    StringGrid1: TStringGrid;    procedure
btnDateClick(Sender: TObject);    procedure Button1Click(Sender:
TObject);    procedure Button2Click(Sender: TObject);    procedure
FormCreate(Sender: TObject);  private    { private declarations } 
public    { public declarations }  end; var  Form1:
TForm1; implementation type  TData = record     
Name,Address,PhoneNumber : String;      MoneyOwing : integer;     
Interest,rate : real;  end;var  Data : TData; 
count,datediff,count1,count2 : integer;  Added : tdatetime;  numdays :
double; {$R *.lfm} { TForm1 } procedure TForm1.btnDateClick(Sender:
TObject);begin  if calendardialog1.execute then  lbledtDate.text :=
datetostr(calendardialog1.date);end; procedure
TForm1.Button1Click(Sender: TObject);begin  data.name :=
lbledtname.text;  data.address := lbledtaddress.text; 
data.phonenumber := lbledtphonenumber.text;  data.moneyowing :=
strtoint(lbledtmoneyowing.text);  added :=
strtodate(lbledtdate.text);   stringgrid1.cells [0,count] :=
data.name;  stringgrid1.cells [1,count] := data.address; 
stringgrid1.cells [2,count] := data.phonenumber;  stringgrid1.cells
[3,count] := inttostr(data.moneyowing);  stringgrid1.cells [5,count]
:= datetostr(added);   numdays := date - added;  datediff :=
trunc(numdays);  data.rate := 1.0025;   for count1 := 1 to datediff -1
do  data.rate := data.rate * 1.0025;  data.interest :=
(data.moneyowing * data.rate) - data.moneyowing;   stringgrid1.cells
[4,count] := floattostr(data.interest);    inc(count); 
stringgrid1.rowCount := count + 1; end; procedure
TForm1.Button2Click(Sender: TObject);Var XLApp: OLEVariant;      x,y:
byte;      path: variant; begin XLApp :=
CreateOleObject('Excel.Application'); // comobj try   XLApp.Visible :=
False;         // Hide Excel   XLApp.DisplayAlerts := False;   path :=
lbledtPath.Text;   XLApp.Workbooks.Open(Path);     // Open the
Workbook   for x := 1 to count do    begin     for y := 0 to 5 do    
begin       XLApp.Cells[x,y].Value := stringgrid1.Cells[y,x];     
end;    end; finally   XLApp.Quit;   XLAPP := Unassigned; 
end; end; procedure TForm1.FormCreate(Sender: TObject);begin  count :=
1;  stringgrid1.rowcount := 2;end; end. 
I would really appreciate the help

----------------------------------------------------------------------
wp · 2017-10-19 08:47

You don't tell us which error occurs and in which order the two
buttons are clicked. If Button2 is clicked first then the stringgrid
contains only two rows as set up in FormCreate, and when the Excel
file contains more than 1 row your program will crash when you try to
write something into a non-existing row. So, in Form2Click, set the
RowCount of the grid accordingly.

I see another problem related to the cell indexes: I am not 100% sure
about this but Microsoft has been a "Basic" company where indexes
start at 1; therefore I suspect that in Excel automation indexes start
at 1 as well. But the code in your y loop starts at index y := 0.

Another strange thing is the variable "count". In Button1Click you use
it to set the RowCount of the grid, but in Button2Click you use it in
the x, i.e. column loop. Something is wroing here unless your
worksheet is square.

----------------------------------------------------------------------
HJE · 2023-01-04 11:00

No need for a bug report on the “… Selection.End” issue.  (also for: 
“… Range.End”)
This can easily be fixed by placing an & before the End, like this >>
“… Selection.&End”   or  “… Range.&End”.
This is not required in Delphi, but is required in Lazarus.