uxlsencodeformula.pas

TMS Component Pack Pro v4.2

unit UXlsEncodeFormula;
{$IFDEF LINUX}{$INCLUDE ../FLXCOMPILER.INC}{$ELSE}{$INCLUDE ..\FLXCOMPILER.INC}{$ENDIF}

interface

uses
  XlsFormulaMessages, XlsMessages, UXlsFormulaParser,
  UFlxStack, UXlsStrings, SysUtils, UXlsBaseRecordLists, UXlsRowColEntries, UFlxMessages;

type
  TParseString= class
  private
    ParsePos: integer;
    Fw: widestring;
    FParsedData: array of byte;
    FParsedArrayData: array of byte;
    MaxErrorLen: integer;
    DirectlyInFormula: string;

    LastRefOp: integer;

    FNameTable: TNameRecordList;
    FCellList: TCellList;

    StackWs: TWhiteSpaceStack;

    Default3DExternSheet: widestring;
    Force3d: boolean; //Named ranges

    function IsNumber(const c: widechar): boolean;
    function IsAlpha(const c: widechar): boolean;
    function IsAZ(const c: widechar): boolean;
    function ATo1(const c: widechar): integer;

    function NextChar: boolean;
    function PeekChar(var c: WideChar): boolean;
    function Peek2Char(var c: WideChar): boolean;
    function PeekCharWs(var c: WideChar): boolean;

    procedure GetNumber;
    procedure GetString;
    procedure GetAlpha;
    procedure GetArray;

    procedure GetFormulaArgs(const Index: integer; var ArgCount: integer);
    procedure GetFormula(const s: string);
    function  GetBool(const s: string): boolean;
    function IsErrorCode(const s: widestring; var b: byte): boolean;
    procedure GetError;
    procedure GetOneReference(var RowAbs, ColAbs: boolean;var Row, Col: integer);
    function  GetReference: boolean;

    procedure Factor;     // [Whitespace]* Function | Number | String | Cell Reference | 3d Ref | (Expression) | NamedRange | Boolean | Err | Array
    procedure RefTerm;    // Factor [ : | ' ' | , Factor]
    procedure NegTerm;    // [-]* RefTerm
    procedure PerTerm;    // NegTerm [%]*
    procedure ExpTerm;    // PerTerm [ ^ PerTerm]*
    procedure MulTerm;    // ExpTerm [ *|/ ExpTerm ]*
    procedure AddTerm;    // MulTerm [ +|- MulTerm]*
    procedure AndTerm;    // AddTerm [ & AddTerm]*
    procedure ComTerm;    // AndTerm [ = | < | > | <= | >= | <>  AndTerm]*
    procedure Expression;

    procedure SkipWhiteSpace;
    procedure UndoSkipWhiteSpace(const SaveParsePos: integer);
    procedure PopWhiteSpace;
    procedure AddParsed(const s: array of byte; const PopWs: boolean=true);
    procedure AddParsedArray(const s: array of byte);
    function FindComTerm(var Ptg: byte): boolean;
    procedure GetGeneric3dRef(const ExternSheet: widestring);
    procedure GetQuotedRef3d;
    procedure GetRef3d(const s: widestring);
    function GetExternSheet(const ExternSheet: widestring): word;
    procedure ConvertLastRefValueType(const RefMode: TFmReturnType);
    function GetLastRefOp: byte;
    class function GetPtgMode(const aptg: byte): TFmReturnType;
    procedure SetLastRefOp(const aptg: byte; const RefMode: TFmReturnType);
    procedure ConvertLastRefValueTypeOnce(const RefMode: TFmReturnType; var First: boolean);
    function IsDirectlyInFormula: boolean;
  public
    constructor Create(const aw: widestring; const aNameTable: TNameRecordList; const aCellList: TCellList);
    constructor CreateExt(const aw: widestring;
      const aNameTable: TNameRecordList; const aCellList: TCellList;
      const aForce3D: Boolean; const aDefault3DExternSheet: WideString;
      const ReturnType: TFmReturnType);

    destructor Destroy; override;
    procedure Parse;

    function TotalSize: integer;
    procedure CopyToPtr(const Ptr: PArrayOfByte; const aPos: integer);
    procedure CopyToPtrNoLen(const Ptr: PArrayOfByte; const destIndex: integer);
  end;

implementation


function GetRealPtg(const PtgBase: word; const ReturnType: TFmReturnType): word;
begin
  case ReturnType of
    fmArray: Result:=PtgBase+$40;
    fmRef  : Result:=PtgBase;
    else     Result:=PtgBase+$20;
  end; //case
end;

{ TParseString }

constructor TParseString.Create(const aw: widestring; const aNameTable: TNameRecordList; const aCellList: TCellList);
begin
  inherited Create;
  Fw:= aw;
  ParsePos:=1;
  StackWs:=TWhiteSpaceStack.Create;
  FNameTable:=aNameTable;
  FCellList:=aCellList;
  Force3D := false;
  MaxErrorLen:=Length(fmErrNull);
  if MaxErrorLen<Length(  fmErrDiv0 ) then MaxErrorLen:=Length(fmErrDiv0 );
  if MaxErrorLen<Length(  fmErrValue) then MaxErrorLen:=Length(fmErrValue);
  if MaxErrorLen<Length(  fmErrRef  ) then MaxErrorLen:=Length(fmErrRef  );
  if MaxErrorLen<Length(  fmErrName ) then MaxErrorLen:=Length(fmErrName );
  if MaxErrorLen<Length(  fmErrNum  ) then MaxErrorLen:=Length(fmErrNum  );
  if MaxErrorLen<Length(  fmErrNA   ) then MaxErrorLen:=Length(fmErrNA   );
end;

constructor TParseString.CreateExt(const aw: widestring; const aNameTable: TNameRecordList; const aCellList: TCellList;
                     const aForce3D: Boolean; const aDefault3DExternSheet: WideString; const ReturnType: TFmReturnType);
begin
  Create(aw, aNameTable, aCellList);
  Default3DExternSheet := aDefault3DExternSheet;
  Force3D := aForce3d;
end;

destructor TParseString.Destroy;
begin
  FreeAndNil(StackWs);
  inherited;
end;

function TParseString.GetLastRefOp: byte;
begin
  Result:= FParsedData[LastRefOp];
end;

procedure TParseString.SetLastRefOp(const aptg: byte; const RefMode: TFmReturnType);
var
  newptg: Byte;
begin
  newptg := Byte(aptg);
  if (Byte(aptg) and 96) <> 0 then
  begin
    case RefMode of
      fmRef:
          newptg := Byte((newptg and 159) or 32);
      fmValue:
          newptg := Byte((newptg and 159) or 64);
      fmArray:
          newptg := Byte(newptg or 96);
    end; //case
  end;
  FParsedData[LastRefOp] :=newptg;
end;

class function TParseString.GetPtgMode(const aptg: byte): TFmReturnType;
var
  PtgMode: TFmReturnType;
begin
  PtgMode := fmValue;
  if ((aptg = ptgRange) or (aptg = ptgIsect)) or (aptg = ptgUnion) then PtgMode := fmRef;

  case aptg and 96 of
    32:
      PtgMode := fmRef;
    96:
      PtgMode := fmArray;
  end; //case
  
  Result := PtgMode;
end;

procedure TParseString.ConvertLastRefValueType(const RefMode: TFmReturnType);
var
  aptg: byte;
  PtgMode: TFmReturnType;
begin
  if LastRefOp < 0 then
    raise Exception.Create(ErrInternal);
  
  aptg := GetLastRefOp;
  PtgMode := GetPtgMode(aptg);
  case RefMode of
    fmValue:
    begin
      if PtgMode <> fmArray then
        SetLastRefOp(aptg, fmValue);
    end;
    fmArray:
    begin
      SetLastRefOp(aptg, fmArray);
    end;
  end;
end;

procedure TParseString.ConvertLastRefValueTypeOnce(const RefMode: TFmReturnType; var First: boolean);
begin
  if (First) then ConvertLastRefValueType(RefMode);
  First:=false;
end;

procedure TParseString.GetRef3d(const s: widestring);
var
  c: WideChar;
begin
  c := ' ';
  if not PeekChar(c) or (c <> fmExternalRef) then
    raise Exception.CreateFmt(ErrUnexpectedChar, [c, ParsePos, Fw]);
  NextChar;
  GetGeneric3dRef(s);
end;

procedure TParseString.GetQuotedRef3d;
var
  e: WideChar;
  d: WideChar;
  More: Boolean;
  sq: widestring;
  c: WideChar;
  s: widestring;
begin
  SkipWhiteSpace;
  s := '';
  c := ' ';
  sq := fmSingleQuote;
  if not PeekChar(c) or (c <> sq) then
    raise Exception.CreateFmt(ErrUnexpectedChar, [c, ParsePos, Fw]);
  NextChar;

  repeat
    More := False;
    if PeekChar(c) and (c <> sq) then
    begin
      s:=s+c;
      NextChar;
      More := True;
    end
    else
    begin
      d := ' ';
      e := ' ';
      if PeekChar(d) and (d = sq) and Peek2Char(e) and (e = sq) then
      begin
        s:=s+sq;
        NextChar;
        NextChar;
        More := True;
      end;
    end;
  until not More;
  if not PeekChar(c) or (c <> sq) then
    raise Exception.CreateFmt(ErrUnterminatedString, [Fw]);
  NextChar;
  GetRef3d(s);
end;

procedure TParseString.Factor;
var
  c: widechar;
begin
  if PeekCharWs(c) then
  begin
    if ord(c)>255 then raise Exception.CreateFmt(ErrUnexpectedChar, [char(c), ParsePos, Fw]);
    if IsNumber(c) then GetNumber else
    if c= fmOpenParen then
    begin
      SkipWhiteSpace;
      NextChar;

			DirectlyInFormula := DirectlyInFormula + '0';
			try
        Expression;
      finally
				Delete(DirectlyInFormula, Length(DirectlyInFormula), 1);
      end;

      if not (PeekCharWs(c)) or (c<>fmCloseParen) then raise Exception.CreateFmt(ErrMissingParen, [Fw]);
      SkipWhiteSpace;
      NextChar;
      PopWhiteSpace;
      AddParsed([ptgParen]);
    end
    else if c=fmStr then GetString
    else if c=fmOpenArray then GetArray
    else if c=fmErrStart then GetError
    else if not GetReference then
      if IsAlpha(c) then GetAlpha
      else if c=fmSingleQuote then GetQuotedRef3d();
  end
  else
    raise Exception.CreateFmt(ErrUnexpectedEof, [Fw]);
end;

function TParseString.IsDirectlyInFormula: boolean;
begin
  if (Length(DirectlyInFormula) <=0) then Result:= false
  else
  begin
    Result := DirectlyInFormula[Length(DirectlyInFormula)]='1';
  end;
end;

procedure TParseString.RefTerm;
// Factor [ : | ' ' | , Factor]
var
  c: widechar;
  b: byte;
  First: boolean;
begin
  First:=true;
  Factor;
  //Pending: see how to fix intersect (on popwhitespace, if there are two references, is an intersect).
  //Union is only valid if we are not inside a function. For example A2:A3,B5 is ok. But HLookup(0,A2:A3,B5,1, true) is not ok.

  while PeekCharWS(c) and (((c=fmUnion) and not IsDirectlyInFormula)   or (c=fmRangeSep) {or (c=fmIntersect)}) do
  begin
	  ConvertLastRefValueTypeOnce(fmRef, First);
    SkipWhiteSpace;
    NextChar;
    Factor;
	  ConvertLastRefValueType(fmRef);

    if (c=fmUnion) then b:=ptgUnion else
    if (c=fmRangeSep) then b:=ptgRange else
    if (c=fmIntersect) then b:=ptgIsect else
    raise Exception.Create(ErrInternal);
    AddParsed(b);
  end;
end;

procedure TParseString.NegTerm;
//[-]* RefTerm
var
  c: widechar;
  i: integer;
  s: string;
begin
  s:='';
  while PeekCharWs(c) and ((c=fmMinus) or (c=fmPlus))do
  begin
    SkipWhiteSpace;
    NextChar;
    s:=s+c;
  end;
  RefTerm;
  if Length(s)>0 then
  begin
    ConvertLastRefValueType(fmValue);
    for i:=1 to Length(s) do
      if (s[i] = fmMinus) then AddParsed([ptgUminus]) else AddParsed([ptgUplus]);
  end;
end;

procedure TParseString.PerTerm;
// NegTerm [%]*
var
  c: widechar;
  First: boolean;
begin
  First:=true;
  NegTerm;
  while PeekCharWs(c) and (c=fmPercent) do
  begin
	  ConvertLastRefValueTypeOnce(fmValue, First);
    SkipWhiteSpace;
    NextChar;
    AddParsed([ptgPercent]);
  end;
end;

procedure TParseString.ExpTerm;
// PerTerm [ ^ PerTerm]*