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.