Recent

Author Topic: [SOLVED] Excel shows no cell values transfered with ole  (Read 2054 times)

JoelH

  • Newbie
  • Posts: 3
[SOLVED] Excel shows no cell values transfered with ole
« on: November 26, 2020, 08:30:21 am »
In Delphi i use the following code to transfer data from a stringgrid to a Excelsheet.

Code: Pascal  [Select][+][-]
  1. procedure Tfrm_mdi_konto.doExcel(grid:TStringgrid);
  2. var App_Excel : Variant;
  3.     row, col : Integer;
  4. begin
  5.   app_excel := CreateOleObject('Excel.Application');
  6.   app_excel.visible := false;
  7.   app_excel.workbooks.add;
  8.   app_excel.workbooks[1].sheets[1].name := 'Uebersicht';
  9.   for row := 0 to grid.rowcount-1 do
  10.   begin
  11.     for col := 0 to grid.colcount-1 do
  12.     begin
  13.       //showmessage(grid.cells[col, row]);
  14.       app_excel.workbooks[1].sheets[1].cells[row+1, col+1] := grid.cells[col, row];
  15.     end;
  16.   end;
  17.   app_excel.visible := true;
  18. end;
Using Lazarus 2.0.10, FPC 3.2.0 and Excel 2016.

The Sheetname changes and there are real values in my Stringgrid, but the Excelsheet still is empty after the transfer.


« Last Edit: November 26, 2020, 03:44:27 pm by JoelH »

PascalDragon

  • Hero Member
  • *****
  • Posts: 5446
  • Compiler Developer
Re: Excel shows no cell values transfered with ole
« Reply #1 on: November 26, 2020, 09:29:21 am »
Note: would have been nice if you mentioned that you did a cross posting from the German forum.

JoelH

  • Newbie
  • Posts: 3
Re: Excel shows no cell values transfered with ole
« Reply #2 on: November 26, 2020, 10:04:49 am »
Note: would have been nice if you mentioned that you did a cross posting from the German forum.
Oh, sorry. I will do the next time.  I didn't know that the forums belong together.

mig-31

  • Sr. Member
  • ****
  • Posts: 305
Re: Excel shows no cell values transfered with ole
« Reply #3 on: November 26, 2020, 12:33:51 pm »
I think your problem is that you assign a string value to Excel cell. Last time, when I used shit named "OLE", I got the same error, which your show in German forum and I must assign a Variant value to Excel cell.

Code: Pascal  [Select][+][-]
  1. var
  2.   CellValue: Variant;
  3. begin
  4.   CellValue := grid.cells[col, row];
  5.   app_excel.workbooks[1].sheets[1].cells[row+1, col+1] := CellValue;
  6. end;
  7.  

Lazarus 2.2.6 - OpenSuse Leap 15.4, Mageia 8, CentOS 7

Sieben

  • Sr. Member
  • ****
  • Posts: 310
Re: Excel shows no cell values transfered with ole
« Reply #4 on: November 26, 2020, 02:26:14 pm »
I'd suggest using OLEVariant right away then.
Lazarus 2.2.0, FPC 3.2.2, .deb install on Ubuntu Xenial 32 / Gtk2 / Unity7

PascalDragon

  • Hero Member
  • *****
  • Posts: 5446
  • Compiler Developer
Re: Excel shows no cell values transfered with ole
« Reply #5 on: November 26, 2020, 02:39:05 pm »
Note: would have been nice if you mentioned that you did a cross posting from the German forum.
Oh, sorry. I will do the next time.  I didn't know that the forums belong together.

They don't belong together, but many people of the German forum are also active here and for example I was confused at first because I had thought I had read that question somewhere (didn't remember that it was on the German forum) and considering we have problems with spammers that copy other posts as their first posts...
Also this way one can see what other people already suggested.

I think your problem is that you assign a string value to Excel cell.

Right! Then it might indeed be fixed with my recent changes to ComObj, because the conversion of AnsiString variables to WideString did not work correctly.

As a workaround you could simply cast your string to WideString (not UnicodeString) when assigning the grid's cell to the worksheet's:

Code: Pascal  [Select][+][-]
  1. app_excel.workbooks[1].sheets[1].cells[row+1, col+1] := WideString(grid.cells[col, row]);

JoelH

  • Newbie
  • Posts: 3
Re: Excel shows no cell values transfered with ole
« Reply #6 on: November 26, 2020, 03:43:59 pm »

Right! Then it might indeed be fixed with my recent changes to ComObj, because the conversion of AnsiString variables to WideString did not work correctly.

As a workaround you could simply cast your string to WideString (not UnicodeString) when assigning the grid's cell to the worksheet's:

Code: Pascal  [Select][+][-]
  1. app_excel.workbooks[1].sheets[1].cells[row+1, col+1] := WideString(grid.cells[col, row]);

Thats it! With Widestring it works.

Thanks.

PascalDragon

  • Hero Member
  • *****
  • Posts: 5446
  • Compiler Developer
Re: Excel shows no cell values transfered with ole
« Reply #7 on: November 27, 2020, 03:56:43 pm »

Right! Then it might indeed be fixed with my recent changes to ComObj, because the conversion of AnsiString variables to WideString did not work correctly.

As a workaround you could simply cast your string to WideString (not UnicodeString) when assigning the grid's cell to the worksheet's:

Code: Pascal  [Select][+][-]
  1. app_excel.workbooks[1].sheets[1].cells[row+1, col+1] := WideString(grid.cells[col, row]);

Thats it! With Widestring it works.

Thank you for the feedback. That means that with the coming 3.2.2 it should work without the typecast. :)

 

TinyPortal © 2005-2018