Reference in Formula is disappearing

Questions and answers on how to use XLSReadWriteII 3/4/5.
Post Reply
Mojoala
Posts: 19
Joined: Wed Feb 29, 2012 5:54 pm

Reference in Formula is disappearing

Post by Mojoala »

This is my last procedure before XLS.Write call
Delphi Code

Code: Select all

procedure TfrmXlsPBA.LoadOrderFormulas;
var OrdNumRef, SpotRef, CostRef, GrpRef1, GrpRef2, Formula : string;
    OrdStartRef, OrdEndRef, TrueRef : string;
begin
  XLS.BeginUpdate;

  OrdStartRef := ColRowToRefStr(0, StartRow, false, false);  //GETS A39
  OrdEndRef   := ColRowToRefStr(0, EndRow,   false, false);  //GETS A156
  TrueRef := OrdStartRef + ':' + OrdEndRef;                          / GETS A39:A156

  mdOrder.First;
  while not mdOrder.eof do
  begin
    OrdNumRef := mdOrderRef0.AsString;  // GETS A18
    SpotRef   := mdOrderRef1.AsString;     // GETS J18

    Formula := 'COUNTIF(' + TrueRef + ',"="&' + OrdNumRef + ')'; // GETS COUNTIF(A39:A156,"="&A18)
    XLS.Sheets[0].AsFormulaRef[ SpotRef ] := Formula;

    mdOrder.Next;
  end;
  XLS.EndUpdate;
end;
But once the Write is called and the excel spreadsheet popup Cell J18 contains COUNTIF(A39,"="&A18) instead. :A156 is dropped from the equation.
Any ideas why?
Yes I have stepped thru the code to verify the vales. A156 is the last row filled in with data.

TIA
Mojoala
Posts: 19
Joined: Wed Feb 29, 2012 5:54 pm

Re: Reference in Formula is disappearing

Post by Mojoala »

Okay I've got reproduceable Delphi Code:

Code: Select all

unit fMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, XLSReadWriteII4, DB, DBClient;

type
  TMain = class(TForm)
    XLS: TXLSReadWriteII4;
    btnExport: TButton;
    dlgExcelExport: TSaveDialog;
    procedure btnExportClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    function GetExcelExportFileName : Boolean;
    function GetSimpleMaxWidth(InVal : String) : Integer;

    procedure DoExport;
    procedure DoPrepWork;
    procedure SetDynamicVaribles;
    procedure SetColumnWidths;
    procedure SetUpMargins;

    procedure LoadData;

    procedure PopulateOrderList;
    procedure LoadOrderSummaryHeader;
    procedure LoadOrderSummaryDetail;

    procedure SetUpDaypartSummary;
    procedure LoadDaypartSummaryHeader;
    procedure LoadDaypartSummaryDetail;

    procedure SetUpDetailData;


    procedure LoadTheDataTitles;
    procedure LoadTheDataDetail;

    procedure LoadOrderFormulas;

const
  cap: array[0..13] of String =
    ( 'Order',   'Line ID', 'Spot ID', 'Daypart',  'Day',      'Date',
      'Time',    'Program', 'Ad ID',   'Length',  'Spot Type', 'Rate',
      '000',     '000'    );


  public
    ExcelFileName: string;
    Wid : array[0..13] of Integer;
    Fnt: TFont;
    LastRow, StartRow, EndRow : Integer;
    IsRtg : Boolean;
    OrderList : TStringList;
    mdOrder, mdDayPart, DayList, Details : array of TStringList;

  end;

var
  Main: TMain;

implementation

uses ShellAPI, Math, 
     SheetData4, CellFormats4, Cell4, BIFFRecsII4, ApplyFormat4, XLSNames4, XLSUtils4,
     XLSFonts4;

{$R *.dfm}
//----------------------------------------------------------------------------------------
procedure TMain.FormCreate(Sender: TObject);
begin
  Fnt := TFont.Create;
  Fnt.Name := 'Arial';
  Fnt.Size := 8;
  OrderList := TStringList.Create;
end;
//----------------------------------------------------------------------------------------
procedure TMain.FormDestroy(Sender: TObject);
begin
  Fnt.Free;
  OrderList.Free;   
end;
//----------------------------------------------------------------------------------------
procedure TMain.btnExportClick(Sender: TObject);
begin
  if GetExcelExportFileName then
  begin
    XLS.Filename := ExcelFileName;
    IsRtg := True;
    LastRow := 0;
    DoExport;
    XLS.Write;
    ShellExecute (0, '', PChar (ExcelFileName), PChar (''), '', 0);
  end;
end;
//----------------------------------------------------------------------------------------
function TMain.GetExcelExportFileName : Boolean;
begin
  Result := False;
  ExcelFileName := 'BloodyForumula.xls';
  dlgExcelExport.FileName := ExcelFileName;
  if dlgExcelExport.Execute then
  begin
    ExcelFileName := dlgExcelExport.FileName;
    Result := True;
  end;
end;
//----------------------------------------------------------------------------------------
procedure TMain.DoExport;
begin
  DoPrepWork;
  SetUpMargins;
  LoadData;
end;
//----------------------------------------------------------------------------------------
procedure TMain.DoPrepWork;
begin
  ExcelColorPalette[Integer(xc24)] := 8210719;  // Dark Dark blue
  ExcelColorPalette[Integer(xc25)] := 16051431; // Lighter Light Blue
  ExcelColorPalette[Integer(xc26)] := 15655386; // Darker Light Blue

  SetUpDetailData;
  SetDynamicVaribles;
  SetColumnWidths;
end;
//----------------------------------------------------------------------------------------
procedure TMain.SetUpDetailData;
const OrdNum : array[0..7] of string = ( '285642', '285642', '311792', '311792',
                                         '311793', '311793', '311794', '323518' );
 Abbr : array[0..7] of string = ( 'AM', 'MD', 'EF', 'PM', 'LN', 'LF', 'WK', 'AM' );
 sDay : array[0..7] of string = ( 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun', 'Mon');

var i : Integer;
begin
  SetLength(Details, 14);
  for i := 0 to 13 do Details[i] := TStringList.Create;
    

  for i := 0 to 7 do
  begin
    Details[0].Add(  OrdNum[i]                   );
    Details[1].Add(  IntToStr(i)                 );
    Details[2].Add(  '0'                         );
    Details[3].Add(  Abbr[i]                     );
    Details[4].Add(  sDay[i]                     );
    Details[5].Add(  DateToStr( Date + i)        );
    Details[6].Add(  TimeToStr ( Time )          );
    Details[7].Add(  'Once Upon A Time'          );
    Details[8].Add(  'TOP1211T'                  );
    Details[9].Add(  ':30'                       );
    Details[10].Add( 'Normal'                    );
    Details[11].Add( IntToStr( 500 + (i * 100) ) );
    Details[12].Add( FloatToStr ( 1.4 + i )      );
    Details[13].Add( FloatToStr ( 2.2 + i )      );
  end;

end;
//----------------------------------------------------------------------------------------
procedure TMain.SetDynamicVaribles;
var i,x, temp : integer;
begin
  for i := 0 to 13 do Wid[i] := 0;

  for i := 0 to 13 do
  begin
    Temp := GetSimpleMaxWidth( cap[i] ) + 5;
    Wid[i] := MAX( Temp, Wid[i] );

    for x := 0 to Details[i].Count - 1 do
    begin
      Temp := GetSimpleMaxWidth ( Details[i].Strings[x] ) + 5;
      Wid[i] := MAX( Temp, Wid[i] );
    end;
  end;

end;
//----------------------------------------------------------------------------------------
function TMain.GetSimpleMaxWidth(InVal : String) : Integer;
begin
  Result := Length(Inval);
end;
//----------------------------------------------------------------------------------------
procedure TMain.SetColumnWidths;
var col : integer;
begin
  XLS.BeginUpdate;
  for col := 0 to 17 do
  begin
    case col of
      0..12    : XLS.Sheets[0].Columns.SetColWidthChar(col, col, wid[col] );
      13,15,16 : XLS.Sheets[0].Columns.SetColWidthChar(col, col, wid[11]  );
      14       : XLS.Sheets[0].Columns.SetColWidthChar(col, col, wid[12]  );
    end;

  end;
  XLS.EndUpdate;
end;
//----------------------------------------------------------------------------------------
procedure TMain.SetUpMargins;
begin
  XLS.BeginUpdate;

  XLS.Fonts[0].Size := Fnt.Size;
  XLS.Fonts[0].Name := 'Arial';
  XLS.Sheets[0].ApplyFormat.Font(0, 0, 50, 220, Fnt);
  XLS.Sheets[0].PrintSettings.PaperSize := psLetter;
  XLS.Sheets[0].PrintSettings.FooterMargin := 0.25;
  XLS.Sheets[0].PrintSettings.HeaderMargin := 0.25;
  XLS.Sheets[0].PrintSettings.MarginLeft   := 0.25;
  XLS.Sheets[0].PrintSettings.MarginRight  := 0.25;
  XLS.Sheets[0].PrintSettings.MarginTop    := 0.50;
  XLS.Sheets[0].PrintSettings.MarginBottom := 0.75;

  XLS.Sheets[0].PrintSettings.Options := [psoLeftToRight];
  //XLS.Sheets[0].Options := XLS.Sheets[0].Options - [soGridLines];

  XLS.EndUpdate;
end;
//========================================================================================
//  LOAD DATA
//========================================================================================
procedure TMain.LoadData;
begin
  PopulateOrderList;
  LoadOrderSummaryHeader;
  LoadOrderSummaryDetail;

  SetUpDaypartSummary;
  LoadDaypartSummaryHeader;
  LoadDaypartSummaryDetail;

  LoadTheDataTitles;
  LoadTheDataDetail;

  LoadOrderFormulas;
end;
//========================================================================================
procedure TMain.PopulateOrderList;
var i : integer;
begin
  OrderList.Add('285642');
  OrderList.Add('311792');
  OrderList.Add('311793');
  OrderList.Add('311794');
  OrderList.Add('323518');

  SetLength(mdOrder, 5 );

  for i := 0 to 5 do mdOrder[i] := TStringList.Create;

end;
//----------------------------------------------------------------------------------------
procedure TMain.LoadOrderSummaryHeader;
const                            //1,2,3, 4, 5, 6,7,8,9,0,1,2,3,14,15,16,17,18,19,20,21
   c : array[1..21] of Integer = ( 0,9,7,11,12,14,0,1,3,5,7,8,9,10,11,12,13,14,15,16,17 );
   r : array[1..21] of Integer = ( 0,0,1, 1, 1, 1,2,2,2,2,2,2,2, 2, 2, 2, 2, 2, 2, 2, 2 );
 cap : array[1..15] of string =
    ( 'Order Summary',  'Post Summary', 'Total Order',  'Primary', 'Expected',
      'Delivered',      'Order #',      'Description',  'Est #',   'Flight Dates',
      'Total $',        '# Spots',      '# Spots',      'Total $', 'Demo'  );
 rCap : array[16..21] of String = ( 'GRP',    'CPP', 'GRP',    'CPP',  '+/-', 'Index');
 tCap : array[16..21] of String = ( 'Grimps', 'CPM', 'Grimps', 'CPM',  '+/-', 'Index');

 var row, col, myrow,i : Integer;  
     s : string;
begin

  MyRow := LastRow + 2;
  XLS.BeginUpdate;

  TRY
    XLS.Sheets[0].ApplyFormat.Box( 0, Myrow, 17, MyRow+2, cbsThin, xc24, xc24);

    for i := 1 to 21 do
    begin
      col := c[i];
      row := MyRow + r[i];

      if i in [1..15]  then s := cap[i];
      if i in [16..21] then
      begin
        if IsRtg then s := rCap[i] else s := tCap[i];
      end;

      XLS.Sheets[0].AsString[ col, row ]           := s;
      XLS.Sheets[0].Cell[     col, row ].FontColor := clWhite;

      case i of
        2,3,5,8,10 : XLS.Sheets[0].MergedCells.Add(col, row, col + 1, row);
        6          : XLS.Sheets[0].MergedCells.Add(col, row, col + 3, row);
      end;

      if True then

      if i in [1,8] then else XLS.Sheets[0].Cell[col, row].HorizAlignment := chaCenter;
    end;

    for i := 0 to 2 do
    begin
      XLS.Sheets[0].Cell[8,   MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[10,  MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[13,  MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[8,   MyRow + i].BorderRightColor := xcWhite;
      XLS.Sheets[0].Cell[10,  MyRow + i].BorderRightColor := xcWhite;
      XLS.Sheets[0].Cell[13,  MyRow + i].BorderRightColor := xcWhite;
    end;

  FINALLY
    XLS.EndUpdate;
  END;
  LastRow := MyRow + 2;
end;
//----------------------------------------------------------------------------------------
procedure TMain.LoadOrderSummaryDetail;
var x, row, col : integer;
    CostRef, GrpRef1, GrpRef2, Formula,  Ref : string;
begin

  row := LastRow;

  TRY
    for x := 0 to OrderList.Count - 1 do
    begin
      inc(row);
      XLS.Sheets[0].ApplyFormat.Box(  0, row,  6, row, cbsThin, xcWhite, xcBlack);
      XLS.Sheets[0].ApplyFormat.Box(  7, row,  8, row, cbsThin, xc25,    xcBlack);
      XLS.Sheets[0].ApplyFormat.Box(  9, row, 10, row, cbsThin, xc26,    xcBlack);
      XLS.Sheets[0].ApplyFormat.Box( 11, row, 13, row, cbsThin, xc25,    xcBlack);
      XLS.Sheets[0].ApplyFormat.Box( 14, row, 17, row, cbsThin, xc26,    xcBlack);

      XLS.Sheets[0].AsInteger[0, row] := StrToInt ( OrderList[x] );
      XLS.Sheets[0].Cell[0, row].HorizAlignment := chaCenter;

      XLS.Sheets[0].AsString[1, row] := 'Toyoto of Plano';

      XLS.Sheets[0].AsString[3, row] := 'DEC TV 12/11';
      XLS.Sheets[0].Cell[3, row].HorizAlignment := chaCenter;

      XLS.Sheets[0].AsString[5, row] := '12/26/2011 - 4/28/2012';

      XLS.Sheets[0].Cell[8, row].HorizAlignment := chaCenter;
      XLS.Sheets[0].Cell[10, row].HorizAlignment := chaCenter;

      XLS.Sheets[0].AsString[11, row] := 'DP2+';
      XLS.Sheets[0].Cell[11, row].HorizAlignment := chaCenter;

      CostRef := ColRowToRefStr(10, row, false, false);
      GrpRef1 := ColRowToRefStr(12, row, false, false);
      GrpRef2 := ColRowToRefStr(14, row, false, false);

      for col := 13 to 17 do
      begin
        if col <> 14 then
        begin
          case col  of
            13 : Ref := CostRef + '/' + GrpRef1;
            15 : Ref := CostRef + '/' + GrpRef2;
            16 : ref := GrpRef2 + '-' + GrpRef1;
            17 : ref := GrpRef2 + '/' + GrpRef1;
          end;

          Formula := 'IF(ISERROR(' + ref + '),"",' + ref + ')';
          XLS.Sheets[0].AsFormula[col, row] := Formula;
        end;
      end;

//      // store the ref values now for later use
      mdOrder[0].Add( ColRowToRefStr( 0, row, false, false) );
      mdOrder[1].Add( ColRowToRefStr( 9, row, false, false) );
      mdOrder[2].Add( CostRef );
      mdOrder[3].Add( GrpRef1 );
      mdOrder[4].Add( GrpRef2 );

    end;
  FINALLY
    XLS.EndUpdate;
  END;
  LastRow := row;

end;
//========================================================================================
procedure TMain.SetUpDaypartSummary;
var i : Integer;
begin
  SetLength ( DayList, 2 );

  for i := 0 to 1 do DayList[i] := TStringList.Create;

  DayList[0].Add('AM');
  DayList[0].Add('MD');
  Daylist[0].Add('All dayparts');

  DayList[1].Add('Early Morning');
  DayList[1].Add('DayTime');
  DayList[1].Add('');

  SetLength(mdDayPart, 5 );

  for i := 0 to 5 do mdDayPart[i] := TStringList.Create;
end;
//----------------------------------------------------------------------------------------
procedure TMain.LoadDaypartSummaryHeader;
const                            //1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
   c : array[1..12] of Integer = ( 0, 0, 5, 7, 3, 4, 5, 6, 7,  8,  9, 10);
   r : array[1..12] of Integer = ( 0, 1, 1, 1, 2, 2, 2, 2, 2,  2,  2,  2);
 cap : array[1..6] of string =
    ('Daypart Summary', 'Posting Dates', 'Expected', 'Delivered', '# Spots', 'Total $' );
 rCap : array[7..12] of String = ( 'GRP',    'CPP', 'GRP',    'CPP',  '+/-', 'Index');
 tCap : array[7..12] of String = ( 'Grimps', 'CPM', 'Grimps', 'CPM',  '+/-', 'Index');
var row, col, myrow,i : Integer;
    s : string;
begin
  MyRow := LastRow + 2;
  XLS.BeginUpdate;

  TRY
    XLS.Sheets[0].ApplyFormat.Box( 0, Myrow, 10, MyRow+2, cbsThin, xc24, xc24);

    for i := 1 to 12 do
    begin
      col := c[i];
      row := MyRow + r[i];

      case i of
        1     : s := cap[i];
        2     : s := cap[i] + ': ' + '12/26/2011 - 4/28/2012';
        3..6  : s := cap[i];
        7..12 : if IsRtg then s := rCap[i] else s := tCap[i];
      end;

      XLS.Sheets[0].AsString[ col, row ]           := s;
      XLS.Sheets[0].Cell[     col, row ].FontColor := clWhite;
      XLS.Sheets[0].Cell[     col, row ].FontStyle := [xfsBold];

      case i of
        3 : XLS.Sheets[0].MergedCells.Add(col, row, col + 1, row);
        4 : XLS.Sheets[0].MergedCells.Add(col, row, col + 3, row);
      end;

      if i > 2 then XLS.Sheets[0].Cell[col, row].HorizAlignment := chaCenter;
    end;

    for i := 0 to 2 do
    begin
      XLS.Sheets[0].Cell[4, MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[6, MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[4, MyRow + i].BorderRightColor := xcWhite;
      XLS.Sheets[0].Cell[6, MyRow + i].BorderRightColor := xcWhite;
    end;

  FINALLY
    XLS.EndUpdate;
  END;
  LastRow := MyRow + 2;
end;
//----------------------------------------------------------------------------------------
procedure TMain.LoadDaypartSummaryDetail;
var col, row, row1, row2, row3, RecCt, i : integer;
    CostRef, GrpRef1, GrpRef2, Formula,  Ref : string;
begin
  row := LastRow;
  row1 := LastRow + 1;
  RecCt := Daylist[0].Count;

  row2 := row1 + RecCt -2;
  row3 := row1 + RecCt -1;

  XLS.BeginUpdate;
  TRY
    XLS.Sheets[0].ApplyFormat.Box( 0, row1,  2, row2, cbsThin, xcWhite, xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 3, row1,  4, row2, cbsThin, xc26,    xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 5, row1,  6, row2, cbsThin, xc25,    xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 7, row1, 10, row2, cbsThin, xc26,    xcBlack);

    XLS.Sheets[0].ApplyFormat.Box( 0, row3,  2, row3, cbsThin, xcWhite, xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 3, row3,  4, row3, cbsThin, xc26,    xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 5, row3,  6, row3, cbsThin, xc25,    xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 7, row3, 10, row3, cbsThin, xc26,    xcBlack);

    for i := 0 to RecCt - 1 do
    begin

        inc(row);
        XLS.Sheets[0].AsString[ 0, row ] := DayList[0].Strings[i];

        if  DayList[0].Strings[i] =  'All dayparts' then
          XLS.Sheets[0].Cell[0, row].FontStyle := [xfsBold];

        XLS.Sheets[0].AsString[ 1, row ] := DayList[1].Strings[i];

        CostRef := ColRowToRefStr(4, row, false, false);
        GrpRef1 := ColRowToRefStr(5, row, false, false);
        GrpRef2 := ColRowToRefStr(7, row, false, false);

        for col := 6 to 10 do
        begin
          if col <> 7 then
          begin
            case col  of
               6 : Ref := CostRef + '/' + GrpRef1;
               8 : Ref := CostRef + '/' + GrpRef2;
               9 : ref := GrpRef2 + '-' + GrpRef1;
              10 : ref := GrpRef2 + '/' + GrpRef1;
            end;

            Formula := 'IF(ISERROR(' + ref + '),"",' + ref + ')';
            XLS.Sheets[0].AsFormula[col, row] := Formula;
          end;
        end;

        if  DayList[0].Strings[i] <> 'All dayparts' then
        begin
          mdDaypart[0].Add( ColRowToRefStr( 0, row, false, false) );
          mdDaypart[0].Add( ColRowToRefStr( 3, row, false, false) );
          mdDaypart[0].Add( ColRowToRefStr( 4, row, false, false) );
          mdDaypart[0].Add( ColRowToRefStr( 5, row, false, false) );
          mdDaypart[0].Add( ColRowToRefStr( 7, row, false, false) );
        end; 
    end;

  FINALLY
    XLS.EndUpdate;
  END;
  LastRow := row;
end;
//========================================================================================
procedure TMain.LoadTheDataTitles;
const
c : array[1..23] of Integer =
                       //1, 2, 3, 4, 5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
                        (0,11,14,11,14,0,1,2,3, 4, 5, 6, 7, 8, 9,10,11,12,13,14,15,16,17);
r : array[1..23] of Integer =
                        (0, 0, 0, 1, 1,2,2,2,2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2);

cap: array[1..17] of string =
      ( 'Spot Details', 'Expected', 'Delivered', '',          '',      'Order',
        'Line ID',      'Spot ID',  'Daypart',   'Day',       'Date',  'Time',
        'Program',      'Ad ID',    'Length',    'Spot Type', 'Rate' );
rCap : array[18..23] of String = ( 'Rtg', 'CPP', 'Rtg', 'CPP',  '+/-', 'Index');
tCap : array[18..23] of String = ( '000', 'CPM', '000', 'CPM',  '+/-', 'Index');
var row, col, myrow,i : Integer;
    s : string;
begin
  MyRow := LastRow + 2;
  XLS.BeginUpdate;

  TRY
    XLS.Sheets[0].ApplyFormat.Box( 0, Myrow, 17, MyRow+2, cbsThin, xc24, xc24);

    for i := 1 to 23 do
    begin
      col := c[i];
      row := MyRow + r[i];

      case i of
        1..3   : s := cap[i];
        4,5    : s := 'DP2+';
        6..17  : s := cap[i];
        18..23 : if IsRtg then s := rCap[i] else s := tCap[i];
      end;

      XLS.Sheets[0].AsString[ col, row ]           := s;
      XLS.Sheets[0].Cell[     col, row ].FontColor := clWhite;
      XLS.Sheets[0].Cell[     col, row ].FontStyle := [xfsBold];

      case i of
        2,4 : XLS.Sheets[0].MergedCells.Add(col, row, col + 2, row);
        3,5 : XLS.Sheets[0].MergedCells.Add(col, row, col + 3, row);
      end;

      if i > 1 then XLS.Sheets[0].Cell[col, row].HorizAlignment := chaCenter;
    end;

    for i := 0 to 2 do
    begin
      XLS.Sheets[0].Cell[10, MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[13, MyRow + i].BorderRightStyle := cbsThin;
      XLS.Sheets[0].Cell[10, MyRow + i].BorderRightColor := xcWhite;
      XLS.Sheets[0].Cell[13, MyRow + i].BorderRightColor := xcWhite;
    end;

  FINALLY
    XLS.EndUpdate;
  END;
  LastRow := MyRow + 2;
end;
//----------------------------------------------------------------------------------------
procedure TMain.LoadTheDataDetail;
var col, row, row1, row2, RecCt, i, x, n : integer;
    CostRef, GrpRef1, GrpRef2, Formula,  Ref, s : string;
    d : double;
begin
  row   := LastRow;
  row1  := LastRow + 1;
  RecCt := Details[0].Count;
  row2 := row1 + RecCt -1;
  StartRow := row +1;
  XLS.BeginUpdate;

  TRY
    XLS.Sheets[0].ApplyFormat.Box(  0, row1, 10, row2, cbsThin, xcWhite, xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 11, row1, 13, row2, cbsThin, xc25,    xcBlack);
    XLS.Sheets[0].ApplyFormat.Box( 14, row1, 17, row2, cbsThin, xc26,    xcBlack);

    for x := 0 to RecCt - 1 do
    begin
      inc(row);
      for i := 0 to 13 do
      begin
        if i = 13  then col := 14
        else            col := i;

        case i of
          0..2   : n := StrToInt(     Details[i].Strings[x] );
          3..10  : s :=               Details[i].Strings[x]  ;
          11..13 : d := StrToFloat (  Details[i].Strings[x] );
        end;

        case i of
          0..2   : XLS.Sheets[0].AsInteger[ col, row ] := n;
          3..10  : XLS.Sheets[0].AsString[  col, row ] := s;
          11     : XLS.Sheets[0].AsFloat[   col, row ] := d;
          12,13  :
            if d = -1 then
                   XLS.Sheets[0].AsString[  col, row ] := 'n/a'
            else   XLS.Sheets[0].AsFloat[   col, row ] := d;
        end;

        case i of
          0..6         : XLS.Sheets[0].Cell[col, row].HorizAlignment := chaCenter;
          9,10         : XLS.Sheets[0].Cell[col, row].HorizAlignment := chaCenter;
          11,13,15,17  : XLS.Sheets[0].Cell[col, row].HorizAlignment := chaRight;
          12,14,16     : XLS.Sheets[0].Cell[col, row].HorizAlignment := chaCenter;
        end;

        case i of
          11,13,15 : XLS.Sheets[0].Cell[col, row].NumberFormat := '$#,0  ';
          12,14    : XLS.Sheets[0].Cell[col, row].NumberFormat := '0.0';
          17       : XLS.Sheets[0].Cell[col, row].NumberFormat := '#0.00%';
        end;
      end;


      CostRef := ColRowToRefStr(11, row, false, false);
      GrpRef1 := ColRowToRefStr(12, row, false, false);
      GrpRef2 := ColRowToRefStr(14, row, false, false);

      for col := 13 to 17 do
      begin
        if col <> 14 then
        begin
          case col  of
            13 : Ref := CostRef + '/' + GrpRef1;
            15 : Ref := CostRef + '/' + GrpRef2;
            16 : ref := GrpRef2 + '-' + GrpRef1;
            17 : ref := GrpRef2 + '/' + GrpRef1;
          end;

          Formula := 'IF(ISERROR(' + ref + '),"",' + ref + ')';
          XLS.Sheets[0].AsFormula[col, row] := Formula;
        end;
      end;
    end;
  FINALLY
    XLS.EndUpdate;
  END;
  EndRow  := row;
  LastRow := row;
end;
//========================================================================================
procedure TMain.LoadOrderFormulas;
var OrdNumRef, SpotRef, CostRef, GrpRef1, GrpRef2, Formula : string;
    OrdStartRef, OrdEndRef, TrueRef : string;
    i, x : integer;
begin
  //showdataset(mdOrder);
  XLS.BeginUpdate;

  OrdStartRef := ColRowToRefStr(0, StartRow, False, False);
  OrdEndRef   := ColRowToRefStr(0, EndRow,   False, False);
  //ordEndRef := 'A154';
  TrueRef := OrdStartRef + ':' + OrdEndRef;

  x := mdOrder[0].Count;

  for i := 0 to x - 1 do
  begin
    OrdNumRef := mdOrder[0].Strings[i];
    SpotRef   := mdOrder[1].Strings[i];
    CostRef   := mdOrder[2].Strings[i];
    GrpRef1   := mdOrder[3].Strings[i];
    GrpRef2   := mdOrder[4].Strings[i];

    Formula := 'COUNTIF(' + TrueRef + ',"="&' + OrdNumRef + ')';
    XLS.Sheets[0].AsFormulaRef[ SpotRef ] := Formula;
  end;

  XLS.EndUpdate;
end;
//----------------------------------------------------------------------------------------
end.
Post Reply