Lazarus

Programming => Operating Systems => Windows => Topic started by: bambamns on May 10, 2012, 06:17:46 am

Title: Work with Excel (any version)
Post by: bambamns on May 10, 2012, 06:17:46 am
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)
Title: Re: Work with Excel (any version)
Post by: BigChimp on May 10, 2012, 07:14:34 am
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_Automation_to_interact_with_OpenOffice_and_Microsoft_Office (http://wiki.lazarus.freepascal.org/Office_Automation#Using_COM_Automation_to_interact_with_OpenOffice_and_Microsoft_Office)... you could perhaps add this Excel example there?

Thanks for posting!
Title: Re: Work with Excel (any version)
Post by: bambamns on May 10, 2012, 08:24:25 pm
I've posted it on wiki http://wiki.lazarus.freepascal.org/Office_Automation#Reading.2FWriting_an_Excel_file_using_OLE (http://wiki.lazarus.freepascal.org/Office_Automation#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.
Title: Re: Work with Excel (any version)
Post by: aviatorz on January 24, 2013, 03:56:09 pm
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;
Title: Re: Work with Excel (any version)
Post by: CM630 on May 16, 2013, 08:10:16 am
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.
Title: Re: Work with Excel (any version)
Post by: Jurassic Pork on May 16, 2013, 08:32:36 am
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
Title: Re: Work with Excel (any version)
Post by: CM630 on May 16, 2013, 09:13:17 am
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.ActiveDocument.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?
Title: Re: Work with Excel (any version)
Post by: ludob on May 16, 2013, 02:54:12 pm
Quote
1.     wrd.ActiveDocument.Application.Selection.start:=wrd.ActiveDocument.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.

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

Title: Re: Work with Excel (any version)
Post by: Timewarp on May 16, 2013, 03:46:32 pm
Quote
end is a reserved word in pascal
It's reserved in Delphi also, but works cases like this. Bug in FPC

Probably works

wrd.ActiveDocument.Application.Selection.start:=wrd.ActiveDocument.Application.Selection.&end+1;
Title: Re: Work with Excel (any version)
Post by: Jurassic Pork on May 16, 2013, 05:15:40 pm
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
Title: Re: Work with Excel (any version)
Post by: ludob on May 17, 2013, 08:35:50 am
Quote
end is a reserved word in pascal
It'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 ;)
Title: Re: Work with Excel (any version)
Post by: CM630 on May 17, 2013, 10:01:01 am
Quote
2.  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.


Title: Re: Work with Excel (any version)
Post by: ludob on May 17, 2013, 07:41:07 pm
Quote
So 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:
Quote
That 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.
Title: Re: Work with Excel (any version)
Post by: CM630 on May 18, 2013, 03:15:15 pm
Quote
So 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 (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?
Title: Re: Work with Excel (any version)
Post by: ludob on May 18, 2013, 08:03:59 pm
Quote
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
The 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.

Quote
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?
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));
Title: Re: Work with Excel (any version)
Post by: CM630 on May 22, 2013, 07:46:42 am
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;
In fact that was my first solution, then I made a function, which makes things easier.

Or use a typecast:
Code: [Select]
wrd.ActiveDocument.Application.Selection.Text:= WideString(UTF8Decode( #13#10+ 'АБВГД' +#13#10));
This seems to me like a righter solution. ;D

Edit: I added some stuff in the wiki (http://wiki.lazarus.freepascal.org/Office_Automation#Using_COM_Automation_to_interact_with_OpenOffice_and_Microsoft_Office).
Title: Re: Work with Excel (any version)
Post by: CM630 on June 18, 2013, 02:12:46 pm
Quote
end is a reserved word in pascal
It'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 (http://mantis.freepascal.org/view.php?id=24620).
Title: Re: Work with Excel (any version)
Post by: totya on April 05, 2015, 09:25:12 pm
Hi!


wrong:

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

good:

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

Title: Re: Work with Excel (any version)
Post by: TopherIII on October 19, 2017, 09:55:48 am
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][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  9.   ExtCtrls, Grids, ExtDlgs,comobj;
  10.  
  11. type
  12.  
  13.   { TForm1 }
  14.  
  15.   TForm1 = class(TForm)
  16.     btnDate: TButton;
  17.     Button1: TButton;
  18.     Button2: TButton;
  19.     CalendarDialog1: TCalendarDialog;
  20.     lbledtPath: TLabeledEdit;
  21.     lbledtName: TLabeledEdit;
  22.     lbledtAddress: TLabeledEdit;
  23.     lbledtphonenumber: TLabeledEdit;
  24.     lbledtmoneyowing: TLabeledEdit;
  25.     lbledtDate: TLabeledEdit;
  26.     StringGrid1: TStringGrid;
  27.     procedure btnDateClick(Sender: TObject);
  28.     procedure Button1Click(Sender: TObject);
  29.     procedure Button2Click(Sender: TObject);
  30.     procedure FormCreate(Sender: TObject);
  31.   private
  32.     { private declarations }
  33.   public
  34.     { public declarations }
  35.   end;
  36.  
  37. var
  38.   Form1: TForm1;
  39.  
  40. implementation
  41.  
  42. type
  43.   TData = record
  44.       Name,Address,PhoneNumber : String;
  45.       MoneyOwing : integer;
  46.       Interest,rate : real;
  47.   end;
  48. var
  49.   Data : TData;
  50.   count,datediff,count1,count2 : integer;
  51.   Added : tdatetime;
  52.   numdays : double;
  53.  
  54. {$R *.lfm}
  55.  
  56. { TForm1 }
  57.  
  58. procedure TForm1.btnDateClick(Sender: TObject);
  59. begin
  60.   if calendardialog1.execute then
  61.   lbledtDate.text := datetostr(calendardialog1.date);
  62. end;
  63.  
  64. procedure TForm1.Button1Click(Sender: TObject);
  65. begin
  66.   data.name := lbledtname.text;
  67.   data.address := lbledtaddress.text;
  68.   data.phonenumber := lbledtphonenumber.text;
  69.   data.moneyowing := strtoint(lbledtmoneyowing.text);
  70.   added := strtodate(lbledtdate.text);
  71.  
  72.   stringgrid1.cells [0,count] := data.name;
  73.   stringgrid1.cells [1,count] := data.address;
  74.   stringgrid1.cells [2,count] := data.phonenumber;
  75.   stringgrid1.cells [3,count] := inttostr(data.moneyowing);
  76.   stringgrid1.cells [5,count] := datetostr(added);
  77.  
  78.   numdays := date - added;
  79.   datediff := trunc(numdays);
  80.   data.rate := 1.0025;
  81.  
  82.   for count1 := 1 to datediff -1 do
  83.   data.rate := data.rate * 1.0025;
  84.   data.interest := (data.moneyowing * data.rate) - data.moneyowing;
  85.  
  86.   stringgrid1.cells [4,count] := floattostr(data.interest);
  87.  
  88.  
  89.   inc(count);
  90.   stringgrid1.rowCount := count + 1;
  91.  
  92. end;
  93.  
  94. procedure TForm1.Button2Click(Sender: TObject);
  95. Var XLApp: OLEVariant;
  96.       x,y: byte;
  97.       path: variant;
  98.  
  99. begin
  100.  XLApp := CreateOleObject('Excel.Application'); // comobj
  101.  try
  102.    XLApp.Visible := False;         // Hide Excel
  103.    XLApp.DisplayAlerts := False;
  104.    path := lbledtPath.Text;
  105.    XLApp.Workbooks.Open(Path);     // Open the Workbook
  106.    for x := 1 to count do
  107.     begin
  108.      for y := 0 to 5 do
  109.       begin
  110.        XLApp.Cells[x,y].Value := stringgrid1.Cells[y,x];
  111.       end;
  112.     end;
  113.  finally
  114.    XLApp.Quit;
  115.    XLAPP := Unassigned;
  116.   end;
  117.  end;
  118.  
  119. procedure TForm1.FormCreate(Sender: TObject);
  120. begin
  121.   count := 1;
  122.   stringgrid1.rowcount := 2;
  123. end;
  124.  
  125. end.
  126.  

I would really appreciate the help  :D
Title: Re: Work with Excel (any version)
Post by: wp on October 19, 2017, 10:47:20 am
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.
TinyPortal © 2005-2018