Recent

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

CM630

  • Hero Member
  • *****
  • Posts: 865
  • Не съм сигурен, че те разбирам.
    • 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 »
Лазар 2,0,6;W7 64bit or XP 32bit;FPC3,2,0;rev 62129

CM630

  • Hero Member
  • *****
  • Posts: 865
  • Не съм сигурен, че те разбирам.
    • 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.
Лазар 2,0,6;W7 64bit or XP 32bit;FPC3,2,0;rev 62129

totya

  • Hero Member
  • *****
  • Posts: 577
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: 6450
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10