xlscalculate3.pas

Advanced Data Import Component Suite for Borland Delphi and C++ Builder allows you to import your da

    for i := 1 to High(BOOL_ERR_STRINGS) do begin
      if (VarType(Peek) = varString) and (Peek = BOOL_ERR_STRINGS[i]) then begin
        Pop;
        Push(true);
        Exit;
      end;
    end;
    Push(false);
  end;

  procedure DoAVERAGE;
  var
    i, Cnt: integer;
    V: Variant;
  begin
    R := 0;
    Cnt := 0;
    for i := 0 to ArgCount - 1 do begin
      if (VarType(Peek) and varArray) = varArray then begin
        V := Peek;
        case VarArrayHighBound(V, 1) of
          1, 2: Inc(Cnt);
          3, 4: Cnt := Cnt + (V[2] - V[0] + 1) * (V[3] - V[1] + 1);
          else raise Exception.Create(sInvalidArraySize);
        end;
        DoOptimizedFunction(fidSum);
        R := R + Peek;
      end
      else begin
        if VarType(Peek) in [varSmallint, varInteger, varSingle, varDouble,
          varCurrency, varDate, varBoolean, {$IFDEF VCL6}varShortInt, varWord,
          varLongWord, varInt64,{$ENDIF} varByte] then begin
          Inc(Cnt);
          R := R + Peek;
        end;
      end;
      Pop;
    end;
    R := R / Cnt;
  end;

begin
  Result := true;
  case ID of
    fidCount  : DoCOUNT;
    fidIf     : DoIF;
    fidIsNa   : DoIsNA;
    fidIsError: DoIsERROR;
    fidSum    : CallOptimizedFunction;
    fidAverage: DoAVERAGE;
    fidMax    : CallOptimizedFunction;
    fidMin    : CallOptimizedFunction;
    else Result := false;
  end;
  if Result then Push(R);
end;

function CalculateFormula(Cell: TbiffCell; ParsedData: PByteArray;
  DataSize: integer): Variant;
var
  Ptr: Pointer;
  Str: WideString;
  B: byte;
  Calculator: TxlsCalculator;
  C, R, i, j: integer;
  RefArea: Variant;
  DataCell: TbiffCell;

  procedure DecodeArea(ColIn, RowIn: integer; var ColOut, RowOut: integer);
  begin
    if (ColIn and $4000) = 0
      then ColOut := Shortint(ColIn and $FF)
      else ColOut := Cell.Col + Shortint(ColIn and $FF);
    if (ColIn and $8000) = 0
      then RowOut := RowIn
      else RowOut := Cell.Row + Smallint(RowIn);
  end;

  procedure CalculateFunction(ID, ArgCount: integer);
  var
    i: integer;
    ArrArgs: Variant;
    V: Variant;
    XLS: TxlsFile;
  begin
    ArrArgs := VarArrayCreate([0, ArgCount - 1], varVariant);
    for i := 0 to ArgCount - 1 do
      ArrArgs[ArgCount - 1 - i] := Calculator.Pop;

    V := NULL;

    XLS := Cell.WorkSheet.Workbook.ExcelFile;
    if Assigned(XLS.OnFunction) then
      XLS.OnFunction (XLS, XLS_FUNCTIONS[ID].Name, ArrArgs, V);

    if VarType(V) = NULL
      then Calculator.Push('[Unknown func ' + Str + ']')
      else Calculator.Push(V);
      
    VarArrayRedim(ArrArgs, 0);
  end;

begin
  Result := NULL;

  Calculator := TxlsCalculator.Create(Cell);
  try
    Ptr := ParsedData;
    while Integer(Ptr) - Integer(ParsedData) < DataSize do begin
      case Byte(Ptr^) of
        0: Break;
        ptgExp: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          Calculator.Push('<Shared>');
          //Break;
        end;
        ptgAdd..ptgPercent: begin
          Calculator.DoOperator(Byte(Ptr^));
          Ptr := Pointer(Integer(Ptr) + 1);
        end;
        ptgParen: Ptr := Pointer(Integer(Ptr) + 1);
        ptgMissArg: Ptr := Pointer(Integer(Ptr) + 1);
        ptgStr: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          B := Byte(Ptr^);
          Ptr := Pointer(Integer(Ptr) + 1);
          Str := ByteArrayToStr(Ptr, B);
          Ptr := Pointer(Integer(Ptr) + B);
        end;
        ptgAttr: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          if (Byte(Ptr^) and $04) = $04 then begin
            Ptr := Pointer(Integer(Ptr) + 1);
            Ptr := Pointer(Integer(Ptr) + (Word(Ptr^) + 2) * SizeOf(Word) - 3);
          end
          else if (Byte(Ptr^) and $10) = $10 then
            Calculator.DoOptimizedFunction(fidSum);
          Ptr := Pointer(Integer(Ptr) + 3);
        end;
        ptgSheet: Ptr := Pointer(Integer(Ptr) + 11);
        ptgEndSheet: Ptr := Pointer(Integer(Ptr) + 5);
        ptgErr: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          Calculator.Push(ErrCodeToString(Byte(Ptr^)));
          Ptr := Pointer(Integer(Ptr) + 1);
        end;
        ptgBool: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          if Byte(Ptr^) = 0
            then Calculator.Push(false)
            else Calculator.Push(true);
          Ptr := Pointer(Integer(Ptr) + 1);
        end;
        ptgInt: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          Calculator.Push(Smallint(Ptr^));
          Ptr := Pointer(Integer(Ptr) + 2);
        end;
        ptgNum: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          Calculator.Push(Double(Ptr^));
          Ptr := Pointer(Integer(Ptr) + 8);
        end;
        ptgRef, ptgRefV, ptgRefA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGRef(Ptr)^ do begin
            DataCell := Cell.WorkSheet.Cells[Row, Col and $3FFF];
            if Assigned(DataCell) and DataCell.IsFloat
              then Calculator.Push(DataCell.AsFloat)
              else Calculator.Push(0);
            Ptr := Pointer(Integer(Ptr) + SizeOf(TBIFF_PTGRef));
          end;
        end;
        ptgRefN, ptgRefNV, ptgRefNA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGRef(Ptr)^ do begin
            DecodeArea(Cell.Col, Cell.Row, C, R);
            Calculator.Push(VarArrayOf([C, R]));
            Ptr := Pointer(Integer(Ptr) + SizeOf(TBIFF_PTGRef));
          end;
        end;
        ptgArea, ptgAreaV, ptgAreaA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGArea(Ptr)^ do begin
            Calculator.Push(VarArrayOf([Col1 and $3FFF, Row1, Col2 and $3FFF, Row2]));
            Ptr := Pointer(Integer(Ptr) + SizeOf(TBIFF_PTGArea));
          end;
        end;
        ptgAreaN, ptgAreaNV, ptgAreaNA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGArea(Ptr)^ do begin
            DecodeArea(Col1, Row1, C, R);
            RefArea := VarArrayOf([C, R, 0, 0]);
            DecodeArea(Col2, Row2, C, R);
            RefArea[2] := C;
            RefArea[3] := R;
          end;
          Calculator.Push(RefArea);
        end;
        ptgRefErr, ptgRefErrV, ptgRefErrA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          Ptr := Pointer(Integer(Ptr) + 1);
          Calculator.Push(BOOL_ERR_STR_REF);
        end;
        ptgAreaErr, ptgAreaErrV, ptgAreaErrA: begin
          Ptr := Pointer(Integer(Ptr) + 7);
          Ptr := Pointer(Integer(Ptr) + 2);
          Calculator.Push(BOOL_ERR_STR_REF);
        end;
        ptgName, ptgNameV, ptgNameA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGName(Ptr)^ do begin
            Calculator.Push(Cell.WorkSheet.Workbook.Globals.NameList[NameIndex - 1].Name);
            Ptr := Pointer(Integer(Ptr) + SizeOf(TBIFF_PTGName));
          end;
        end;
        {ptgNameX, ptgNameXV, ptgNameXA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGNameX(Ptr)^ do begin
            Calculator.Push(XLS.GetName(ntExternName,ExtSheet,NameIndex));
            P := Pointer(Integer(P) + SizeOf(TPTGNameX8));
          end;
        end;}
        ptgArray, ptgArrayV, ptgArrayA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
  //        P2 := Pointer(Integer(PBuf) + PRecFORMULA(PBuf)^.ParseLen);


  //        S := GetArray;
  //        Stack.Add(Copy(S,1,Length(S) - 1) + '}');
  //        P := Pointer(Integer(P) + 7);
        end;
        ptgFunc, ptgFuncV, ptgFuncA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          if Word(Ptr^) <= High(XLS_FUNCTIONS) then begin
            if not Calculator.DoFunction(TXLS_FUNCTION_ID(Word(Ptr^))) then
              CalculateFunction(Byte(Ptr^), XLS_FUNCTIONS[Byte(Ptr^)].Min);
            Ptr := Pointer(Integer(Ptr) + 1);
          end
          else begin
            Calculator.Push('?<' + IntToStr(Word(Ptr^)) + '>?');
            Ptr := Pointer(Integer(Ptr) + 1);
          end;
          Ptr := Pointer(Integer(Ptr) + 1);
        end;
        ptgFuncVar, ptgFuncVarV, ptgFuncVarA: begin
          Ptr := Pointer(Integer(Ptr) + 1);
          i := Byte(Ptr^) and $7F;
          j := -1;
          Ptr := Pointer(Integer(Ptr) + 1);
          if Word(Ptr^) <= High(XLS_FUNCTIONS) then
            j := Word(Ptr^) and $7FFF;

          if j <= Integer(High(TXLS_FUNCTION_ID))
            then Calculator.DoVarFunction(TXLS_FUNCTION_ID(j), i)
            else CalculateFunction(j,i);

          Ptr := Pointer(Integer(Ptr) + 1);
          Ptr := Pointer(Integer(Ptr) + 1);
        end;
        ptgRef3d, ptgRef3dV, ptgRef3dA, ptgRefErr3d,
        ptgRefErr3dV, ptgRefErr3dA: begin
          //i := Byte(Ptr^);
          Ptr := Pointer(Integer(Ptr) + 1);
          with PBIFF_PTGRef3D(Ptr)^ do begin
            {if i in [ptgRefErr3d, ptgRefErr3dV]
              then Str := '!#REF!'
              else Calculator.Push(XLS.GetNameValue(ntExternSheet,Index,Col and $3FFF,Row));}

            Ptr := Pointer(Integer(Ptr) + SizeOf(TBIFF_PTGRef3D));
          end
        end;
        {ptgArea3d, ptgArea3dV, ptgArea3dA, ptgAreaErr3d,
        ptgAreaErr3dV, ptgAreaErr3dA: begin
          i := Byte(Ptr^);
          Ptr := Pointer(Integer(Ptr) + SizeOf(TBIFF_PTGRef3D));
          with PBIFF_PTGArea3D(Ptr)^ do begin
            if Index < Cell.WorkSheet.Workbook.Sheets.Count
              then Str := Cell.WorkSheet.Workbook.Sheets[Index]. XLS.Sheets[Index].Name
            else
              S := '<??? Ref3d>';
            if i in [ptgAreaErr3d,ptgAreaErr3dV] then
              S := S + '!#REF!'
            else
              VStack.Push(VarArrayOf([Col1 and $3FFF,Row1,Col2 and $3FFF,Row2,Index]));
            P := Pointer(Integer(P) + SizeOf(TPTGArea3d8));
          end
          else with PPTGArea3d7(P)^ do begin
            if Smallint(SheetIndex) >= 0 then
              S := '[EXTERN ???]:'
            else
              S := '';
            if (IndexFirst = $FFFF) or (IndexLast = $FFFF) then
              S := S + '[DELETED]'
            else if IndexFirst = IndexLast then
              S := S + XLS.Sheets[IndexLast].Name
            else
              S := S + XLS.Sheets[IndexFirst].Name + ':' + XLS.Sheets[IndexLast].Name;
            if i in [ptgAreaErr3d,ptgAreaErr3dV] then
              VStack.Push(S + '!#REF!')
            else
              VStack.Push(VarArrayOf([Col1,Row1 and $3FFF,Col2,Row2 and $3FFF,SheetIndex]));
            P := Pointer(Integer(P) + SizeOf(TPTGArea3d7));
          end;
        end;}
        else begin
          Calculator.Push(Format(sUnknownPTG, [Byte(Ptr^)]));
          Break;
        end;
      end;
    end;
    Result := Calculator.Pop;
  finally
    Calculator.Free;
  end;
end;

end.