comments to xls and xlsx Files by saving

[emeyer]

After Email contact I finally could create an account here so we go on:
First part of my Email:

I want to save comments to an xls File. The example is made for XlsX
> files. So when I open a xls-file with it and save it, nothings happen.
>
> So I wrote an own example. There I am saving comments in two different ways.
> First I change the text of an comment with:
>

Code: Select all

>   for i := 0 to Lmax do begin
>       if (Lcol = ImportedTXLS[0].Comments[i].Col) and (Lrow = 
> ImportedTXLS[0].Comments[i].Row) then
>       begin
> 
> ImportedTXLS[0].Comments.AsPlainText[ImportedTXLS[0].Comments[i].Col,I
> mporte dTXLS[0].Comments[i].Row] := 'new text';
> 
>       end;
>   end;

> The second way was to add a comment by:
> ImportedTXLS[0].Comments.Add(Lcol,Lrow,'new Text','Created by:');
> Both is working for Xlsx fine. But it is not working with xls. Well I
> am able to load comments from an xls-file and when I save the file the
> comments are still existing. But when I change them or add them I can
> see the changed results in my grid which is loading from
> ...AsPlainText but after saving the object in the xls file is still
> the old situation (like I told this way works with xlsx).

//////////////////////////////////////////////////////////////
Part of the Answer from Lars:
//////////////////////////////////////////////////////////////

Will fix this in the next update, ready next week. If you want to test it
now, you can use this code:

Code: Select all

uses
  BIFF5, BIFF_SheetData5, BIFF_DrawingObj5,

var
  N : TDrwNote;
begin
  N := XLS.BIFF[0].DrawingObjects.Notes.Add;
  N.CellCol := 1;
  N.CellRow := 1;
  N.Text := 'Comment text';
end

M

//////////////////////////////////////////////////////////////
Hey Lars hey others,

thanks a lot for the answers. You are very fast - always less then 24h!
I tried what you advised me but it didn't work, xlsx still fine, xls not.

Code: Select all

Privat 
    FImportedTXLS : TXLSReadWriteII5;
///....
//Save comment: 
procedure TFAxalotTest.CommentFromMemoToXLSImporter;
var
  i, Lmax, Lcol, Lrow: Integer;
  LCommentAvi : Boolean;
  N : TDrwNote;
begin
  if FImportedTXLS.Version = xvExcel97 then
    N := FImportedTXLS.BIFF[0].DrawingObjects.Notes.Add;

  LCommentAvi := False;
  Lcol := StringGrid1.Col;
  Lrow := StringGrid1.Row;
  Lmax := FImportedTXLS[0].Comments.Count-1;
  for i := 0 to Lmax do begin
    try
      if (Lcol = FImportedTXLS[0].Comments[i].Col) and (Lrow = FImportedTXLS[0].Comments[i].Row) then
      begin
        if FImportedTXLS.Version = xvExcel97 then
        begin
          N.CellCol := FImportedTXLS[0].Comments[i].Col;
          N.CellRow := FImportedTXLS[0].Comments[i].Row;
          N.Text := Memo1.Text;
        End
        else if FImportedTXLS.Version = xvExcel2007 then begin
          //Commentars are fields like a list so just cells with comments are saved.
          //If you want to know if a comment exist you have to search for it in the list:
           FImportedTXLS[0].Comments.AsPlainText[FImportedTXLS[0].Comments[i].Col, FImportedTXLS[0].Comments[i].Row] := Memo1.Text;
           LCommentAvi := True;
        end;
      end;
    Except
      Memo1.Lines.Add('Fehler');
    end;
  end;
  if not LCommentAvi then begin
    //ImportedTXLS[0].Comments.Add(Lcol,Lrow,Memo1.Text,'Erstellt von: AVAPLAN');
  end;
end;

//Save Data: 

Code: Select all

procedure TFAxalotTest.Action2Execute(Sender: TObject);
var
  LDateiName     : String;
begin
  //do
  if FileSaveDialog1.Execute then
  begin
    if ExtractFileExt(FileSaveDialog1.FileName) <> '' then
      LDateiName := FileSaveDialog1.Files[0];
      if LDateiName.EndsWith('.xls',True) then
      begin
        FImportedTXLS.Version := xvExcel97;
      end
      else if LDateiName.EndsWith('.xlsx',True) then
      begin
        FImportedTXLS.Version := xvExcel2007;
      end
      else
      begin
        FImportedTXLS.Version := xvExcel97;
        LDateiName := LDateiName +'.xls';
      end;
    FImportedTXLS.Filename := LDateiName;
    FImportedTXLS.Write;
  end;
//ShowMessage('save');
end;

So what is it, what I do wrong?

Regards
Eric

[larsa]

Hello

What is it that you want to do? Do you want to add comments to an existing file, or do you want to change the comment text of existing comments?

[emeyer]

Finally both. At the moment just change.

[larsa]

Hello

This is fixed in update 5.20.53

You can now use:

Code: Select all

var  
  Cmt : TXLSComment;
begin
  // Assuming that there are comments in the file.
  XLS[0].Comments[0].PlainText := 'Changed comment';
  // Comment in cell B2
  Cmt := XLS[0].Comments.Find(1,1);
  if Cmt <> Nil then
    Cmt.PlainText := 'Changed comment';

  // Add comments
  XLS[0].Comments.Add(1,3,'','Commemt 3');
  XLS[0].Comments.Add(1,4,'','Commemt 4');