Recent

Author Topic: Work with Excel (any version)  (Read 47047 times)

bambamns

  • Full Member
  • ***
  • Posts: 223
Work with Excel (any version)
« 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)
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Work with Excel (any version)
« Reply #1 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... you could perhaps add this Excel example there?

Thanks for posting!
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

bambamns

  • Full Member
  • ***
  • Posts: 223
Re: Work with Excel (any version)
« Reply #2 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 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.
« Last Edit: May 10, 2012, 09:06:53 pm by bambamns »
Lazarus 1.8.4 + FPC 2.6.4 x86 (rebuild) and Lazarus 2.0, Windows 7 x64, unless otherwise specified

aviatorz

  • Newbie
  • Posts: 3
Re: Work with Excel (any version)
« Reply #3 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;

CM630

  • Hero Member
  • *****
  • Posts: 870
  • Не съм сигурен, че те разбирам.
    • http://sourceforge.net/u/cm630/profile/
Re: Work with Excel (any version)
« Reply #4 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.
« Last Edit: May 16, 2013, 08:13:13 am by paskal »
Лазар 2,0,6;W7 64bit or XP 32bit;FPC3,2,0;rev 62129

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Work with Excel (any version)
« Reply #5 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
« Last Edit: May 16, 2013, 08:38:13 am by Jurassic Pork »
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

CM630

  • Hero Member
  • *****
  • Posts: 870
  • Не съм сигурен, че те разбирам.
    • http://sourceforge.net/u/cm630/profile/
Re: Work with Excel (any version)
« Reply #6 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?
« Last Edit: May 16, 2013, 10:00:52 am by paskal »
Лазар 2,0,6;W7 64bit or XP 32bit;FPC3,2,0;rev 62129

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Work with Excel (any version)
« Reply #7 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.


Timewarp

  • Full Member
  • ***
  • Posts: 144
Re: Work with Excel (any version)
« Reply #8 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;

Jurassic Pork

  • Hero Member
  • *****
  • Posts: 753
Re: Work with Excel (any version)
« Reply #9 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
Jurassic computer : Sinclair ZX81 - Zilog Z80A à 3,25 MHz - RAM 1 Ko - ROM 8 Ko

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Work with Excel (any version)
« Reply #10 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 ;)

CM630

  • Hero Member
  • *****
  • Posts: 870
  • Не съм сигурен, че те разбирам.
    • http://sourceforge.net/u/cm630/profile/
Re: Work with Excel (any version)
« Reply #11 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.


« Last Edit: May 17, 2013, 01:10:24 pm by paskal »
Лазар 2,0,6;W7 64bit or XP 32bit;FPC3,2,0;rev 62129

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Work with Excel (any version)
« Reply #12 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.
« Last Edit: May 17, 2013, 08:46:54 pm by ludob »

CM630

  • Hero Member
  • *****
  • Posts: 870
  • Не съм сигурен, че те разбирам.
    • http://sourceforge.net/u/cm630/profile/
Re: Work with Excel (any version)
« Reply #13 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, 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?
Лазар 2,0,6;W7 64bit or XP 32bit;FPC3,2,0;rev 62129

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: Work with Excel (any version)
« Reply #14 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));
« Last Edit: May 18, 2013, 08:08:14 pm by ludob »