Lazarus

Programming => Operating Systems => Windows => Topic started by: JoelH on November 26, 2020, 08:30:21 am

Title: [SOLVED] Excel shows no cell values transfered with ole
Post by: JoelH 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.


Title: Re: Excel shows no cell values transfered with ole
Post by: PascalDragon on November 26, 2020, 09:29:21 am
Note: would have been nice if you mentioned that you did a cross posting (https://www.lazarusforum.de/viewtopic.php?f=15&t=13272&sid=cc4f1efec55cbe2d476b20b8635d9ad5) from the German forum.
Title: Re: Excel shows no cell values transfered with ole
Post by: JoelH on November 26, 2020, 10:04:49 am
Quote from: PascalDragon on November 26, 2020, 09:29:21 am
Note: would have been nice if you mentioned that you did a cross posting (https://www.lazarusforum.de/viewtopic.php?f=15&t=13272&sid=cc4f1efec55cbe2d476b20b8635d9ad5) from the German forum.
Oh, sorry. I will do the next time.  I didn't know that the forums belong together.
Title: Re: Excel shows no cell values transfered with ole
Post by: mig-31 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.  

Title: Re: Excel shows no cell values transfered with ole
Post by: Sieben on November 26, 2020, 02:26:14 pm
I'd suggest using OLEVariant right away then.
Title: Re: Excel shows no cell values transfered with ole
Post by: PascalDragon on November 26, 2020, 02:39:05 pm
Quote from: JoelH on November 26, 2020, 10:04:49 am
Quote from: PascalDragon on November 26, 2020, 09:29:21 am
Note: would have been nice if you mentioned that you did a cross posting (https://www.lazarusforum.de/viewtopic.php?f=15&t=13272&sid=cc4f1efec55cbe2d476b20b8635d9ad5) 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.

Quote from: mig-31 on November 26, 2020, 12:33:51 pm
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]);
Title: Re: Excel shows no cell values transfered with ole
Post by: JoelH on November 26, 2020, 03:43:59 pm
Quote from: PascalDragon on November 26, 2020, 02:39:05 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.
Title: Re: Excel shows no cell values transfered with ole
Post by: PascalDragon on November 27, 2020, 03:56:43 pm
Quote from: JoelH on November 26, 2020, 03:43:59 pm
Quote from: PascalDragon on November 26, 2020, 02:39:05 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