new functionality

[Josef Gschwendtner]

Hi,
we have a thirdparty delphi component which replaces in a word-file a Tag (e.g. "#mytag") by a certain value --> this works fine and is a great function.
(the tag is just part of the normal text)

Same functionality exists for excel-files. But for excel it does't work very well because the tag is a string and this is making problems in formulas.

Example:
At designtime of the excel-template ...
... Cell A1 has Tag "#NumVal1"
... Cell A2 has Tag "#NumVal2"
... Cell A3 has formula "=A1+A2"

During runtime this delphi component replaces the tags with numeric values.

The problem is, that during design of the excel-template it is not possible to give the cells the right numeric format because they have string values (the tags).
Also the formula shows an error.

Our idea to solve this problem:
The idea is, to solve this Aufgabe with XLSReadWritw.
We think it would be a good function to write the "tag" into the comment of a cell.
A new XLSReadWritw-function would search for a tag within the cell-comments and then fills the related cell with the given (numeric) value.

Do you understand my concern?
Can you imagine to realize such a functionality?

Best regards,
Josef

[larsa]

Hello

You can easily create something that replaces tags with values. Here is an example:

Code: Select all

procedure ReplaceTags(XLS: TXLSReadWriteII5);
var
  Str  : string;
  s,r,c: integer;
  Sheet: TXLSWorksheet;
begin
  for s := 0 to XLS.Count - 1 do begin
    Sheet := XLS[s];
    Sheet.CalcDimensions;
    for r := 0 to Sheet.LastRow do begin
      for c := 0 to Sheet.LastCol do begin
        Str := Sheet.AsString[c,r];
        if (Str <> '') and (Str[1] = '#') then begin
          if Str = '#NumVal1' then
            Sheet.AsFloat[c,r] := 100
          else if Str = '#NumVal2' then
            Sheet.AsFloat[c,r] := 200
          else if Str = '#NumVal3' then
            Sheet.AsFloat[c,r] := 300;
        end;
      end;
    end;
  end;
end;

[Josef Gschwendtner]

Hi,
but the tag would be in the comment of the cell.
How would this work?

Regards,
Josef

[larsa]

Hello

Why do you want the tag in a comment? It just makes things more complicated.

[Josef Gschwendtner]

Hi,

--Why do you want the tag in a comment? It just makes things more complicated.

If I write the tag as cell-value, then I have two problems:
- I can not set the proper numeric fomat in the cell
- formulas which use the cell do show errors (because the tag is not a numeric value)

I should give you a hint of what we like to do:
- we do not create the excel-file - this is be done by our customers (with tags, formulas and formats as they wish).
- our app knows certain tags which it should replace by a value from the database.

Regards,
Josef

[larsa]

Hello

OK, I understand. Here is a new example using comments.

Code: Select all

procedure TfrmMain.ReplaceTags(XLS: TXLSReadWriteII5);
var
  Str  : string;
  i    : integer;
  s,r,c: integer;
  Sheet: TXLSWorksheet;
begin
  for s := 0 to XLS.Count - 1 do begin
    Sheet := XLS[s];
    Sheet.CalcDimensions;

    for i := 0 to Sheet.Comments.Count - 1 do begin
      Str := Sheet.Comments[i].PlainText;
      if (Str <> '') and (Str[1] = '#') then begin
        c := Sheet.Comments[i].Col;
        r := Sheet.Comments[i].Row;
        if Str = '#NumVal1' then
          Sheet.AsFloat[c,r] := 100
        else if Str = '#NumVal2' then
          Sheet.AsFloat[c,r] := 200
        else if Str = '#NumVal3' then
          Sheet.AsFloat[c,r] := 300;
      end;
    end;
  end;
end;

[Josef Gschwendtner]

Thank you, I think this should work!