xlscommon3.pas

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

unit XLSCommon3;

interface

uses SysUtils;

type
  ExlsFileError = class(Exception);

const
  MAX_COL_COUNT          = 255;
  MAX_ROW_COUNT          = 65535; {zero based}
  MAX_SHEET_COUNT        = 250;

  //------

  MAX_RECORD_DATA_SIZE   = 8224;

  //------

  BIFF_BOF_VER           = $0600;

  //------

  BIFF_BOF_GLOBALS       = $0005;
  BIFF_BOF_WORKSHEET     = $0010;
  BIFF_BOF_CHART         = $0020;

  //------

  BIFF_FORMULA           = $0006;
  BIFF_EOF               = $000A;
  BIFF_NAME              = $0018;
  BIFF_CONTINUE          = $003C;
  BIFF_BOUNDSHEET        = $0085;
  BIFF_MULRK             = $00BD;
  BIFF_MULBLANK          = $00BE;
  BIFF_XF                = $00E0;
  BIFF_SST               = $00FC;
  BIFF_LABELSST          = $00FD;

  BIFF_BLANK             = $0201;
  BIFF_NUMBER            = $0203;
  BIFF_BOOLERR           = $0205;
  BIFF_STRING            = $0207;
  BIFF_RK                = $027E;
  BIFF_FORMAT            = $041E;
  BIFF_SHRFMLA           = $04BC;

  BIFF_BOF               = $0809;
  BIFF_BOF_3             = $0209;

  //------

  BOOL_ERR_ID_NULL       = $00;
  BOOL_ERR_ID_DIV_ZERO   = $07;
  BOOL_ERR_ID_VALUE      = $0F;
  BOOL_ERR_ID_REF        = $17;
  BOOL_ERR_ID_NAME       = $1D;
  BOOL_ERR_ID_NUM        = $24;
  BOOL_ERR_ID_NA         = $2A;

  BOOL_ERR_STR_NULL      = '#NULL!';
  BOOL_ERR_STR_DIV_ZERO  = '#DIV/0!';
  BOOL_ERR_STR_VALUE     = '#VALUE!';
  BOOL_ERR_STR_REF       = '#REF!';
  BOOL_ERR_STR_NAME      = '#NAME?';
  BOOL_ERR_STR_NUM       = '#NUM!';
  BOOL_ERR_STR_NA        = '#N/A';

type
  TBOOL_ERR_STRINGS = array[0..6] of WideString;

const
  BOOL_ERR_STRINGS: TBOOL_ERR_STRINGS =
    (BOOL_ERR_STR_NULL, BOOL_ERR_STR_DIV_ZERO, BOOL_ERR_STR_VALUE,
     BOOL_ERR_STR_REF, BOOL_ERR_STR_NAME, BOOL_ERR_STR_NUM, BOOL_ERR_STR_NA);

const
  ptgExp         = $01;
  ptgTbl         = $02;
  ptgAdd         = $03;
  ptgSub         = $04;
  ptgMul         = $05;
  ptgDiv         = $06;
  ptgPower       = $07;
  ptgConcat      = $08;
  ptgLT	         = $09;
  ptgLE	         = $0A;
  ptgEQ	         = $0B;
  ptgGE	         = $0C;
  ptgGT	         = $0D;
  ptgNE	         = $0E;
  ptgIsect       = $0F;
  ptgUnion       = $10;
  ptgRange       = $11;
  ptgUplus       = $12;
  ptgUminus      = $13;
  ptgPercent     = $14;
  ptgParen       = $15;
  ptgMissArg     = $16;
  ptgStr         = $17;
  ptgAttr        = $19;
  ptgSheet       = $1A;
  ptgEndSheet    = $1B;
  ptgErr         = $1C;
  ptgBool        = $1D;
  ptgInt         = $1E;
  ptgNum         = $1F;
  ptgArray       = $20;
  ptgFunc        = $21;
  ptgFuncVar     = $22;
  ptgName        = $23;
  ptgRef         = $24;
  ptgArea        = $25;
  ptgMemArea     = $26;
  ptgMemErr      = $27;
  ptgMemNoMem    = $28;
  ptgMemFunc     = $29;
  ptgRefErr      = $2A;
  ptgAreaErr     = $2B;
  ptgRefN        = $2C;
  ptgAreaN       = $2D;
  ptgMemAreaN    = $2E;
  ptgMemNoMemN   = $2F;
  ptgNameX       = $39;
  ptgRef3d       = $3A;
  ptgArea3d      = $3B;
  ptgRefErr3d    = $3C;
  ptgAreaErr3d   = $3D;
  ptgArrayV      = $40;
  ptgFuncV       = $41;
  ptgFuncVarV    = $42;
  ptgNameV       = $43;
  ptgRefV        = $44;
  ptgAreaV       = $45;
  ptgMemAreaV    = $46;
  ptgMemErrV     = $47;
  ptgMemNoMemV   = $48;
  ptgMemFuncV    = $49;
  ptgRefErrV     = $4A;
  ptgAreaErrV    = $4B;
  ptgRefNV       = $4C;
  ptgAreaNV      = $4D;
  ptgMemAreaNV   = $4E;
  ptgMemNoMemNV  = $4F;
  ptgFuncCEV     = $58;
  ptgNameXV      = $59;
  ptgRef3dV      = $5A;
  ptgArea3dV     = $5B;
  ptgRefErr3dV   = $5C;
  ptgAreaErr3dV  = $5D;
  ptgArrayA      = $60;
  ptgFuncA       = $61;
  ptgFuncVarA    = $62;
  ptgNameA       = $63;
  ptgRefA        = $64;
  ptgAreaA       = $65;
  ptgMemAreaA    = $66;
  ptgMemErrA     = $67;
  ptgMemNoMemA   = $68;
  ptgMemFuncA    = $69;
  ptgRefErrA     = $6A;
  ptgAreaErrA    = $6B;
  ptgRefNA       = $6C;
  ptgAreaNA      = $6D;
  ptgMemAreaNA   = $6E;
  ptgMemNoMemNA  = $6F;
  ptgFuncCEA     = $78;
  ptgNameXA      = $79;
  ptgRef3dA      = $7A;
  ptgArea3dA     = $7B;
  ptgRefErr3dA   = $7C;
  ptgAreaErr3dA  = $7D;

  tkBinary       = [ptgAdd..ptgRange];
  tkUnary        = [ptgUplus..ptgParen];
  tkArea3D       = [ptgArea3d, ptgArea3dV, ptgArea3dA];


LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

InternalNumberFormats: array[0..49] of WideString = (
'',
'0',
'0.00',
'#,##0',
'#,##0.00',
'_($#,##0_);($#,##0)',
'_($#,##0_);[Red]($#,##0)',
'_($#,##0.00_);($#,##0.00)',
'_($#,##0.00_);[Red]($#,##0.00)',
'0%',
'0.00%',
'0.00E+00',
'# ?/?',
'# ??/??',
'm/d/yy',
'd-mmm-y',
'd-mmm',
'mmmm-yy',
'h:mm AM/PM',
'h:mm:ss AM/PM',
'h:mm',
'h:mm:SS',
'm/d/yy h:mm',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'(#,##0_);(#,##0)',
'(#,##0_);[Red](#,##0)',
'(#,##0.00_);(#,##0.00)',
'(#,##0.00_);[Red](#,##0.00)',
'_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
'_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)',
'_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)',
'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)',
'mm:ss',
'[h]:mm:ss',
'mm:ss.0',
'# #0.0E+0',
'@');

type
  PBIFF_Header = ^TBIFF_Header;
  TBIFF_Header = packed record
    ID    : word;
    Length: word;
  end;

  PBIFF_XF = ^TBIFF_XF;
  TBIFF_XF = packed record
    FontIndex  : word;
    FormatIndex: word;
    Data1      : word;
    Data2      : word;
    Data3      : word;
    Data4      : word;
    Data5      : word;
    Data6      : longint;
    Data7      : word;
  end;

  PBIFF_PTGRef = ^TBIFF_PTGRef;
  TBIFF_PTGRef = packed record
    Row: word;
    Col: word;
  end;

  PBIFF_PTGRef3D = ^TBIFF_PTGRef3D;
  TBIFF_PTGRef3D = packed record
    Index: word;
    Row: word;
    Col: word;
  end;

  PBIFF_PTGArea = ^TBIFF_PTGArea;
  TBIFF_PTGArea = packed record
    Row1: word;
    Row2: word;
    Col1: word;
    Col2: word;
  end;

  PBIFF_PTGArea3D = ^TBIFF_PTGArea3D;
  TBIFF_PTGArea3D = packed record
    Index: word;
    Row1: word;
    Row2: word;
    Col1: word;
    Col2: word;
  end;

  PBIFF_PTGName = ^TBIFF_PTGName;
  TBIFF_PTGName = packed record
    NameIndex: word;
    Reserved: word;
  end;

  PBIFF_PTGNameX = ^TBIFF_PTGNameX;
  TBIFF_PTGNameX = packed record
    ExternSheetIndex: word;
    ExternNameIndex: word; // 1 based
    Reserved: word;
  end;

  TXLS_FUNCTION = packed record
    Min: byte;
    Max: byte;
//dee    Name: string;
    Name: AnsiString;
  end;

  TXLS_FUNCTION_ID = (fidCount, fidIf, fidIsNa, fidIsError, fidSum, fidAverage,
    fidMin, fidMax, fidRow, fidColumn, fidNA, fidNPV, fidSTDEV, fidDollar,
    fidFixed, fidSin, fidCos, fidTan, fidATan, fidPI, fidSqrt, fidExp, fidLN,
    fidLog10, fidAbs, fidInt);

  TXLS_FUNCTIONS = array[0..367] of TXLS_FUNCTION;

const
  XLS_FUNCTIONS: TXLS_FUNCTIONS = (
  {000} (Min: 01; Max: 99; Name: 'COUNT'),
  {001} (Min: 02; Max: 03; Name: 'IF'),
  {002} (Min: 01; Max: 01; Name: 'ISNA'),
  {003} (Min: 01; Max: 01; Name: 'ISERROR'),
  {004} (Min: 01; Max: 99; Name: 'SUM'),
  {005} (Min: 01; Max: 99; Name: 'AVERAGE'),
  {006} (Min: 01; Max: 99; Name: 'MIN'),
  {007} (Min: 01; Max: 99; Name: 'MAX'),
  {008} (Min: 00; Max: 01; Name: 'ROW'),
  {009} (Min: 00; Max: 01; Name: 'COLUMN'),
  {010} (Min: 01; Max: 01; Name: 'NA'),
  {011} (Min: 01; Max: 99; Name: 'NPV'),
  {012} (Min: 01; Max: 99; Name: 'STDEV'),
  {013} (Min: 01; Max: 02; Name: 'DOLLAR'),
  {014} (Min: 01; Max: 03; Name: 'FIXED'),
  {015} (Min: 01; Max: 01; Name: 'SIN'),
  {016} (Min: 01; Max: 01; Name: 'COS'),
  {017} (Min: 01; Max: 01; Name: 'TAN'),
  {018} (Min: 01; Max: 01; Name: 'ATAN'),
  {019} (Min: 00; Max: 00; Name: 'PI'),
  {020} (Min: 01; Max: 01; Name: 'SQRT'),
  {021} (Min: 01; Max: 01; Name: 'EXP'),
  {022} (Min: 01; Max: 01; Name: 'LN'),
  {023} (Min: 01; Max: 01; Name: 'LOG10'),
  {024} (Min: 01; Max: 01; Name: 'ABS'),
  {025} (Min: 01; Max: 01; Name: 'INT'),
  {026} (Min: 01; Max: 01; Name: 'SIGN'),
  {027} (Min: 02; Max: 02; Name: 'ROUND'),
  {028} (Min: 03; Max: 03; Name: 'LOOKUP'),
  {029} (Min: 01; Max: 03; Name: 'INDEX'),
  {030} (Min: 02; Max: 02; Name: 'REPT'),
  {031} (Min: 01; Max: 03; Name: 'MID'),
  {032} (Min: 01; Max: 01; Name: 'LEN'),
  {033} (Min: 01; Max: 01; Name: 'VALUE'),
  {034} (Min: 00; Max: 00; Name: 'TRUE'),