Recent

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

CM630

  • Hero Member
  • *****
  • Posts: 1082
  • Не съм сигурен, че те разбирам.
    • http://sourceforge.net/u/cm630/profile/
Re: Work with Excel (any version)
« Reply #15 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.
« Last Edit: June 04, 2013, 10:04:17 am by paskal »
Лазар 3,2 32 bit (sometimes 64 bit); FPC3,2,2; rev: Lazarus_3_0 on Win10 64bit.

CM630

  • Hero Member
  • *****
  • Posts: 1082
  • Не съм сигурен, че те разбирам.
    • http://sourceforge.net/u/cm630/profile/
Re: Work with Excel (any version)
« Reply #16 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.
Лазар 3,2 32 bit (sometimes 64 bit); FPC3,2,2; rev: Lazarus_3_0 on Win10 64bit.

totya

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


TopherIII

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

wp

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

HJE

  • Newbie
  • Posts: 2
Re: Work with Excel (any version)
« Reply #20 on: January 04, 2023, 12:00:55 pm »
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.
« Last Edit: January 04, 2023, 12:03:08 pm by HJE »

 

TinyPortal © 2005-2018