{
Example 1 : easiest way to export a dataset to Excel
scExcelExport1.Dataset:=Table1;
scExcelExport1.ExportDataset;
scExcelExport1.Disconnect;
}
{
Example 2 : use layout properties, add summary cells and save file
scExcelExport1.WorksheetName := 'MyDataset';
scExcelExport1.Dataset:=Table1;
scExcelExport1.StyleColumnWidth:=cwOwnerWidth;
scExcelExport1.ColumnWidth := 20;
scExcelExport1.HeaderText.Text := 'Header';
scExcelExport1.BeginRowHeader := 2;
scExcelExport1.FontTitles := LabelTitle.Font;
scExcelExport1.FontTitles.Orientation := 45;
scExcelExport1.BorderTitles.BackColor := clYellow;
scExcelExport1.BorderTitles.BorderColor := clRed;
scExcelExport1.BorderTitles.LineStyle := blLine;
scExcelExport1.BeginRowTitles := 5;
scExcelExport1.BeginColumnData := 3;
scExcelExport1.FontData := LabelData.Font;
scExcelExport1.SummarySelection := ssValues;
scExcelExport1.SummaryCalculation := scMAX;
scExcelExport1.ExcelVisible:=False;
try
scExcelExport1.ExportDataset;
if Assigned(scExcelExport1.ExcelWorkSheet) then
scExcelExport1.ExcelWorkSheet.Range['A1','A10'].Value := 'Delphi';
scExcelExport1.SaveAs('c:\test.xls',ffXLS);
finally
scExcelExport1.Disconnect;
end;
}
{
Example 3 : export more datasets
scExcelExport1.ExcelVisible:=True;
try
scExcelExport1.Dataset:=Table1;
scExcelExport1.WorksheetName:='1';
scExcelExport1.ConnectTo := ctNewExcel;
scExcelExport1.ExportDataset;
scExcelExport1.Dataset:=Table2;
scExcelExport1.WorksheetName:='2';
scExcelExport1.ConnectTo := ctNewWorkbook;
scExcelExport1.ExportDataset;
scExcelExport1.Dataset:=Table3;
scExcelExport1.WorksheetName:='3';
scExcelExport1.ConnectTo := ctNewWorksheet;
scExcelExport1.ExportDataset;
finally
scExcelExport1.Disconnect;
end;
}
unit scExcelExport;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, DB,
StdCtrls,
OleServer, // used for TConnectKind
{$IFDEF VER140}
// Delphi 6.0
Excel2000,
Variants;
{$ELSE}
// Delphi 5.0
Excel97; // Excel97 or Excel2000
{$ENDIF}
// Delphi 5.0 notes
// ----------------
// Delphi5 standard installation installs Excel97
// If you wants to use Excel2000 you need to apply the following modifications
// described in upd1rdme.txt coming with Delphi Update Pack 1
//
// Office 2000 Components
// ----------------------
// To install the Office 2000 components package:
//
// 1. Select File | Close all
// 2. Select Component | Install package
// 3. Remove package DclAxServer50.bpl to avoid
// name conflicts with Ofice 97 components.
// 4. Add package Dcloffice2k50.bpl, which contains
// the Office 2000 components.
type
TDataPipe = (dpDataSet, dpCustom);
TFileFormat = (ffXLS, ffHTM, ffCSV, ffXL97);
TStyleColumnWidth = (cwDefault, cwOwnerWidth, cwAutoFit, cwFieldDisplayWidth, cwFieldDataSize, cwEnhAutoFit);
// cwOwnerWidth : width specified with property ColumnWidth
// cwAutoFit : Excel autofit
// cwFieldDisplayWidth : width of DisplayWidth of TField
// cwFieldDataSize : width of Datasize of TField
// Datasize = amount of memory to store value, for datetime fields width is set to 16
// cwEnhAutoFit (enhanced autofit) : width of DisplayWidth of TField except when title is larger
TBorderWeight = (bwHairline,bwMedium,bwThick,bwThin);
TBorderLineStyle = (blContinuous, blDash, blDashDot, blDashDotDot,
blDot, blDouble, blSlantDashDot, blLine, blNone);
// blLine is same as blnContinous, but it is necessary for compatibility reasons
TSummarySelection = (ssNone, ssValues, ssGiven);
TSummaryCalculation = (scSUM, scMIN, scMAX);
THAlignment = (haGeneral, haLeft, haRight, haCenter);
TConnectTo = (ctNewExcel, ctNewWorkbook, ctNewWorksheet);
TxlFont = class(TFont)
private
FAlignment: THAlignment;
FBlnWrapText: Boolean;
FIntOrientation: Integer;
published
property Alignment: THAlignment read FAlignment write FAlignment;
property WrapText: Boolean read FBlnWrapText write FBlnWrapText;
property Orientation: Integer read FIntOrientation write FIntOrientation;
end;
TCellBorder = class(TPersistent)
private
FBackColor : TColor;
FBorderColor : TColor;
FBorderWeight : TBorderWeight;
FBorderLineStyle : TBorderLineStyle;
published
property BackColor : TColor read FBackColor write FBackColor default clWhite;
property BorderColor : TColor read FBorderColor write FBorderColor default clBlack;
property Weight : TBorderWeight read FBorderWeight write FBorderWeight default bwMedium;
property LineStyle : TBorderLineStyle read FBorderLineStyle write FBorderLineStyle default blNone;
end;
TOnExportEvent = procedure(Sender : TObject; IntRecordNumber : Integer) of object;
TOnGetCellBackgroundColorEvent = procedure(Sender : TObject; Field: TField; var ColorBackground : TColor) of object;
TOnGetFieldCount = procedure(Sender: TObject; var IntFieldCount : Integer) of object;
TOnGetFieldName = procedure(Sender: TObject; const IntFieldIndex : Integer; var StrFieldName : String) of object;
TOnGetFieldDisplayName = procedure(Sender: TObject; const IntFieldIndex: Integer; var StrFieldDisplayName : String) of object;
TOnGetFieldDisplayWidth = procedure(Sender: TObject; const IntFieldIndex: Integer; var IntFieldDisplayWidth : Integer) of object;
TOnGetFieldDataSize = procedure(Sender: TObject; const IntFieldIndex : Integer; var IntFieldDataSize : Integer) of object;
TOnGetFieldDataType = procedure(Sender: TObject; const IntFieldIndex : Integer; var FieldDataType : TFieldType) of object;
TOnGetFieldVisible = procedure(Sender: TObject; const IntFieldIndex : Integer; var BlnFieldVisible : Boolean) of object;
TOnGetEOF = procedure(Sender: TObject; var BlnEOF: Boolean) of object;
TOnGetFieldValue = procedure(Sender: TObject; const IntFieldIndex: Integer; var VarValue: Variant) of object;
TscExcelExport = class(TComponent)
private
FDataset : TDataset;
FIntRecordNo : integer;
FIntBeginRowHeader : Integer;
FIntBeginRowTitles : Integer;
FIntBeginRowData : Integer;
FIntBeginColumnData : Integer;
FIntBeginColumnHeader : Integer;
FIntEndRowData : Integer;
FExcelApplication : TExcelApplication;
FExcelWorkbook : TExcelWorkbook;
FExcelWorksheet : TExcelWorksheet;
FFieldNames : TStrings;
FBlnShowTitles : Boolean;
FBlnExcelVisible : Boolean;
FStrWorksheetName : String;
FIntColumnWidth : Integer;
FStyleColumnWidth : TStyleColumnWidth;
FConnectTo : TConnectTo;
FFontHeader: TxlFont;
FBorderHeader : TCellBorder;
FStrHeaderText: TStrings;
FFontTitles : TxlFont;
FBorderTitles : TCellBorder;
FFontData : TxlFont;
FBorderData : TCellBorder;
FFontSummary : TxlFont;
FBorderSummary : TCellBorder;
FSummarySelection : TSummarySelection;
FSummaryFields : TStrings;
FSummaryCalculation : TSummaryCalculation;
FIntBlockOfRecords : Integer;
FStrBeginColumnDataChar : String;
LCID : Integer;
FVisibleFieldsOnly: Boolean;
FOnExportRecords : TOnExportEvent;
FOnGetCellBackgroundColorEvent : TOnGetCellBackgroundColorEvent;
FDataPipe: TDataPipe;
FOnGetFieldCount: TOnGetFieldCount;
FOnGetFieldName: TOnGetFieldName;
FOnGetFieldDisplayName: TOnGetFieldDisplayName;
FOnGetFieldDisplayWidth: TOnGetFieldDisplayWidth;
FOnGetFieldDataSize: TOnGetFieldDataSize;
FOnGetFieldDataType: TOnGetFieldDataType;
FOnGetFieldVisible: TOnGetFieldVisible;
FOnGetEOF: TOnGetEOF;
FOnGetFieldValue: TOnGetFieldValue;
procedure SetBeginColumnData(const Value: Integer);
procedure SetBeginColumnHeader(const Value: Integer);
protected
procedure SetFontHeader(const Value: TxlFont);
procedure SetHeaderText(const Value: TStrings);
procedure SetFontTitles(Value : TxlFont);
procedure SetFontData(Value : TxlFont);
procedure SetFontSummary(Value : TxlFont);
procedure SetSummaryFields(Value : TStrings);
procedure SetVisibleFieldsOnly(const Value: Boolean);
procedure SetBeginRowHeader(const Value: Integer);
procedure SetBeginRowTitles(const Value: Integer);
procedure SetBeginRowData(const Value: Integer);
procedure SetColumnWidth;
function GetColumnCharacters(IntNumber : Integer) : String;
procedure SetFontAndBorderRange(DelphiFont : TxlFont; Border : TCellBorder; StrBeginCell, StrEndCell : String);
function SetNumberSeparator(const StrFormat: string): string;
procedure SetFormat;
function CanConvertFieldToCell(const IntFieldIndex: Integer) : Boolean;
function IsValueField(const IntFieldIndex: Integer) : Boolean;
function GetWidthFromDatasize(const IntFieldIndex: Integer) : Integer;
function GetFieldDataType(IntIndex : Integer): TFieldType;
procedure ExportHeader;
procedure ExportTitles;
procedure ExportFieldData;
procedure ExportSummary;
property FieldDataType[Index: Integer]: TFieldType read GetFieldDataType;
public
constructor Create(Owner: TComponent); override;
destructor Destroy; override;
// Read-only properties
// Line number of last row of data is filled in after the export
property EndRowData : Integer read FIntEndRowData;
// Link to the Excel worksheet, can be used to add extra data after the export
property ExcelWorkSheet : TExcelWorksheet read FExcelWorksheet write FExcelWorksheet;
procedure Notification(AComponent: TComponent; Operation: TOperation); override;
procedure Disconnect; virtual;
procedure ExportDataset(BlnOpenedExcel : Boolean = False); virtual;
procedure SaveAs(const StrFileName : String; const FileFormat : TFileFormat); virtual;
procedure PrintPreview(const BlnPrintGridLines : Boolean); virtual;
procedure LoadDefaultProperties;
published
// Show or hide excel (default True)
property ExcelVisible : Boolean read FBlnExcelVisible write FBlnExcelVisible default True;
// New instance of Excel application, new workbook or new worksheet (default is new instance of Excel)
property ConnectTo : TConnectTo read FConnectTo write FConnectTo default ctNewExcel;
// Use a TDataset given by the Dataset property
// or use the events when using another database object
property DataPipe: TDataPipe read FDataPipe write FDataPipe;
// Name of worksheet
property WorksheetName : String read FStrWorksheetName write FStrWorksheetName;
// Dataset which will be exported (TTable, TQuery, TClientDataset, TADODataset, ...)
property Dataset : TDataset read FDataset write FDataset;
// Style of columnswidth : Excel default, width of property ColumnWidth, AutoFit
property StyleColumnWidth : TStyleColumnWidth read FStyleColumnWidth write FStyleColumnWidth;
property ColumnWidth : Integer read FIntColumnWidth write FIntColumnWidth;
// Export only visible fields or all fields
property VisibleFieldsOnly : Boolean read FVisibleFieldsOnly write SetVisibleFieldsOnly default True;
// Font and border of header
// Fill in header texts
property FontHeader: TxlFont read FFontHeader write SetFontHeader;
property HeaderText: TStrings read FStrHeaderText write SetHeaderText;
property BorderHeader : TCellBorder read FBorderHeader write FBorderHeader;
// Font and border of titles
property ShowTitles : Boolean read FBlnShowTitles write FBlnShowTitles default True;
property FontTitles : TxlFont read FFontTitles write SetFontTitles;
property BorderTitles : TCellBorder read FBorderTitles write FBorderTitles;
// Font and border of data
property FontData : TxlFont read FFontData write SetFontData;
property BorderData : TCellBorder read FBorderData write FBorderData;
// Font and border of summary
property FontSummary : TxlFont read FFontSummary write SetFontSummary;
property BorderSummary : TCellBorder read FBorderSummary write FBorderSummary;
// Which fields will be summerized : all numeric fields, the fields of SummaryFields, none
property SummarySelection : TSummarySelection read FSummarySelection write FSummarySelection;
property SummaryFields : TStrings read FSummaryFields write SetSummaryFields;
// Calculation : SUM, MIN, MAX
property SummaryCalculation : TSummaryCalculation read FSummaryCalculation write FSummaryCalculation;
// Number of records which will be exported in one variant matrix (default 20)
// Try to increase and decrease this property for the optimal speed
property BlockOfRecords : Integer read FIntBlockOfRecords write FIntBlockOfRecords default 20;
// Begin row of titles and data
property BeginRowHeader : Integer read FIntBeginRowHeader write SetBeginRowHeader default 1;
property BeginRowTitles : Integer read FIntBeginRowTitles write SetBeginRowTitles default 1;
property BeginRowData : Integer read FIntBeginRowData write SetBeginRowData default 2;
// Begin column header and data/titles
property BeginColumnHeader : Integer read FIntBeginColumnHeader write SetBeginColumnHeader default 1;
property BeginColumnData : Integer read FIntBeginColumnData write SetBeginColumnData default 1;
// Event which is triggered after each export of a record
property OnExportRecords : TOnExportEvent read FOnExportRecords write FOnExportRecords;
// Event which is triggered for each field and record
// it can be used to change the color of the cell
// Only works when using a TDataset
property OnGetCellBackgroundColorEvent : TOnGetCellBackgroundColorEvent read FOnGetCellBackgroundColorEvent write FOnGetCellBackgroundColorEvent;
// Events for using this component without a TDataset
property OnGetFieldCount: TOnGetFieldCount read FOnGetFieldCount write FOnGetFieldCount;
property OnGetFieldName: TOnGetFieldName read FOnGetFieldName write FOnGetFieldName;
property OnGetFieldDisplayName: TOnGetFieldDisplayName read FOnGetFieldDisplayName write FOnGetFieldDisplayName;
property OnGetFieldDisplayWidth: TOnGetFieldDisplayWidth read FOnGetFieldDisplayWidth write FOnGetFieldDisplayWidth;
property OnGetFieldDataSize: TOnGetFieldDataSize read FOnGetFieldDataSize write FOnGetFieldDataSize;
property OnGetFieldDataType: TOnGetFieldDataType read FOnGetFieldDataType write FOnGetFieldDataType;
property OnGetFieldVisible: TOnGetFieldVisible read FOnGetFieldVisible write FOnGetFieldVisible;
property OnGetEOF: TOnGetEOF read FOnGetEOF write FOnGetEOF;
property OnGetFieldValue: TOnGetFieldValue read FOnGetFieldValue write FOnGetFieldValue;
end;
procedure Register;
implementation
uses ComObj; {, ActiveX;}
type
TOleEnum = type Integer; // Copied from ActiveX unit
procedure Register;
begin
RegisterComponents('SC', [TscExcelExport]);
end;
{ $R scExcelExport.dcr }
//------------------------------------------------------------------------------
constructor TscExcelExport.Create(Owner: TComponent);
begin
inherited;
FStrHeaderText := TStringList.Create;
FFontHeader := TxlFont.Create;
FFontTitles := TxlFont.Create;
FFontData := TxlFont.Create;
FFontSummary := TxlFont.Create;
FBorderHeader := TCellBorder.Create;
FBorderTitles := TCellBorder.Create;
FBorderData := TCellBorder.Create;
FBorderSummary := TCellBorder.Create;
LoadDefaultProperties;
FExcelApplication := TExcelApplication.Create(Self);
FExcelWorkbook := TExcelWorkbook.Create(Self);
FExcelWorksheet := TExcelWorksheet.Create(Self);
FFieldNames:=TStringList.Create;
FSummaryFields:=TStringList.Create;
FDataPipe := dpDataSet;
end;
//------------------------------------------------------------------------------
destructor TscExcelExport.Destroy;
begin
FStrHeaderText.Free;
FFontHeader.Free;
FFontTitles.Free;
FFontData.Free;
FFontSummary.Free;
FBorderHeader.Free;
FBorderTitles.Free;
FBorderData.Free;
FBorderSummary.Free;
FExcelWorksheet.Free;
FExcelWorkbook.Free;
FExcelApplication.Free;
FFieldNames.Free;
FSummaryFields.Free;
inherited;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.LoadDefaultProperties;
begin
FBorderTitles.FBackColor := clWhite;
FBorderTitles.FBorderColor := clBlack;
FBorderTitles.FBorderWeight := bwMedium;
FBorderTitles.FBorderLineStyle := blNone;
FBorderHeader.FBackColor := clWhite;
FBorderHeader.FBorderColor := clBlack;
FBorderHeader.FBorderWeight := bwMedium;
FBorderHeader.FBorderLineStyle := blNone;
FBorderData.FBackColor := clWhite;
FBorderData.FBorderColor := clBlack;
FBorderData.FBorderWeight := bwMedium;
FBorderData.FBorderLineStyle := blNone;
FBorderSummary.FBackColor := clWhite;
FBorderSummary.FBorderColor := clBlack;
FBorderSummary.FBorderWeight := bwMedium;
FBorderSummary.FBorderLineStyle := blNone;
FFontHeader.FAlignment := haGeneral;
FFontHeader.Name := 'MS Sans Serif';
FFontHeader.Size := 8;
FFontHeader.Color := clWindowText;
FFontHeader.Orientation := 0;
FFontHeader.Style := [];
FFontHeader.WrapText := False;
FFontData.FAlignment := haGeneral;
FFontData.Name := 'MS Sans Serif';
FFontData.Size := 8;
FFontData.Color := clWindowText;
FFontData.Orientation := 0;
FFontData.Style := [];
FFontData.WrapText := False;
FFontSummary.FAlignment := haGeneral;
FFontSummary.Name := 'MS Sans Serif';
FFontSummary.Size := 8;
FFontSummary.Color := clWindowText;
FFontSummary.Orientation := 0;
FFontSummary.Style := [];
FFontSummary.WrapText := False;
FFontTitles.FAlignment := haGeneral;
FFontTitles.Name := 'MS Sans Serif';
FFontTitles.Size := 8;
FFontTitles.Color := clWindowText;
FFontTitles.Orientation := 0;
FFontTitles.Style := [];
FFontTitles.WrapText := False;
FBlnExcelVisible := True;
FConnectTo := ctNewExcel;
FStrWorksheetName := '';
FStyleColumnWidth := cwDefault;
FVisibleFieldsOnly := True;
FBlnShowTitles := True;
FIntColumnWidth := 0;
FIntBlockOfRecords := 20;
FIntBeginRowHeader := 1;
FIntBeginRowTitles := 1;
FIntBeginRowData := 2;
FIntBeginColumnHeader := 1;
FIntBeginColumnData := 1;
FSummaryCalculation := scSUM;
FSummarySelection := ssNone;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.Notification(AComponent: TComponent;
Operation: TOperation);
begin
inherited;
if (Operation = opRemove) and (Assigned(FDataset)) and (AComponent = FDataset) then
begin
FDataset := nil;
end;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetHeaderText(const Value: TStrings);
begin
FStrHeaderText.Assign(Value);
if FStrHeaderText.Count = 0 then
FIntBeginRowHeader := 1;
if FIntBeginRowTitles < FIntBeginRowHeader + FStrHeaderText.Count then
SetBeginRowTitles(FIntBeginRowHeader + FStrHeaderText.Count);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetFontHeader(const Value: TxlFont);
begin
FFontHeader.Assign(Value);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetFontTitles(Value : TxlFont);
begin
FFontTitles.Assign(Value);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetFontData(Value : TxlFont);
begin
FFontData.Assign(Value);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetFontSummary(Value : TxlFont);
begin
FFontSummary.Assign(Value);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetSummaryFields(Value : TStrings);
begin
FSummaryFields.Assign(Value);
FSummaryFields.Text := UpperCase(FSummaryFields.Text);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetFontAndBorderRange(DelphiFont : TxlFont; Border : TCellBorder; StrBeginCell, StrEndCell : String);
const
vAlignment : array[THAlignment] of Cardinal = (xlHAlignGeneral, xlHAlignLeft, xlHAlignRight, xlHAlignCenter);
//Excel constants haven´t a sequence and tt´s use negative values too...
//Value to Excel constants of Border weight...
vBorderWeight : array [TBorderWeight] of integer =
({xlHairline}1,{xlMedium}-4138,{xlThick}4,{xlThin}2);
//Value to Excel constants of Border line style...
vBorderLineStyle: array [TBorderLineStyle] of integer =
({xlContinuous}1,{xlDash}-4115,{xlDashDot}4,{xlDashDotDot}5,
{xlDot}-4118,{xlDouble}-4119,{xlSlantDashDot}13,{xlContinuous}1,{xlLineStyleNone}-4142);
begin
// Convert Delphi font to the Excel font
with FExcelWorksheet.Range[StrBeginCell, StrEndCell].Font do
begin
Name := DelphiFont.Name;
Size := DelphiFont.Size;
Color := DelphiFont.Color;
Bold := fsBold in DelphiFont.Style;
Italic := fsItalic in DelphiFont.Style;
Underline := fsUnderline in DelphiFont.Style;
end;
if (not Assigned(FOnGetCellBackgroundColorEvent)) or (Border <> FBorderData) then
if Border.FBackColor <> clWhite then
FExcelWorksheet.Range[StrBeginCell, StrEndCell].Interior.Color := Border.FBackColor;
if Border.LineStyle <> blNone then
begin
with FExcelWorksheet.Range[StrBeginCell, StrEndCell] do
begin
try
// All border have to set separately
// Top border, bottom border, left border...
Borders[7].LineStyle := vBorderLineStyle[Border.LineStyle];
Borders[7].Weight := vBorderWeight[Border.Weight];
Borders[7].ColorIndex := Border.BorderColor;
Borders[8].LineStyle := vBorderLineStyle[Border.LineStyle];
Borders[8].Weight := vBorderWeight[Border.Weight];
Borders[8].ColorIndex := Border.BorderColor;
Borders[9].LineStyle := vBorderLineStyle[Border.LineStyle];
Borders[9].Weight := vBorderWeight[Border.Weight];
Borders[9].ColorIndex := Border.BorderColor;
Borders[10].LineStyle := vBorderLineStyle[Border.LineStyle];
Borders[10].Weight := vBorderWeight[Border.Weight];
Borders[10].ColorIndex := Border.BorderColor;
Borders[11].LineStyle := vBorderLineStyle[Border.LineStyle];
Borders[11].Weight := vBorderWeight[Border.Weight];
Borders[11].ColorIndex := Border.BorderColor;
Borders[12].LineStyle := vBorderLineStyle[Border.LineStyle];
Borders[12].Weight := vBorderWeight[Border.Weight];
Borders[12].ColorIndex := Border.BorderColor;
except
end;
end;
end;
FExcelWorksheet.Range[StrBeginCell, StrEndCell].Orientation:=DelphiFont.Orientation;
FExcelWorksheet.Range[StrBeginCell, StrEndCell].WrapText := DelphiFont.WrapText;
FExcelWorksheet.Range[StrBeginCell, StrEndCell].HorizontalAlignment := TOleEnum(vAlignment[DelphiFont.Alignment]);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetColumnWidth;
begin
if FStyleColumnWidth = cwOwnerWidth then
FExcelWorksheet.Range['A1',GetColumnCharacters(FFieldNames.Count)+'1'].ColumnWidth:=FIntColumnWidth
else
if FStyleColumnWidth = cwAutoFit then
FExcelWorksheet.Range['A1',GetColumnCharacters(FFieldNames.Count)+'1'].EntireColumn.Autofit;
// else cwFieldDisplayWidth, cwFieldDataSize and cwEnhAutoFit are set in ExportTitles
end;
//------------------------------------------------------------------------------
function TscExcelExport.SetNumberSeparator(const StrFormat: string): string;
var
i : integer;
begin
// replace international separator used into delphi with local separator
Result := StrFormat;
// Don't use StrReplace because the separator may be the same!
for i := 0 to Length(StrFormat) do
begin
if Result[i] = '.' then
Result[i] := DecimalSeparator
else
if Result[i] = ',' then
Result[i] := ThousandSeparator;
end;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetFormat;
var
IntColumn : Integer;
StrBeginCell, StrEndCell : String;
begin
if FDataPipe = dpDataset then
begin
with Dataset do
begin
for IntColumn := 1 to FFieldNames.Count do
begin
StrBeginCell := GetColumnCharacters(IntColumn+BeginColumnData-1)+IntToStr(FIntBeginRowData);
StrEndCell := GetColumnCharacters(IntColumn+BeginColumnData-1)+IntToStr(FIntRecordNo + FIntBeginRowData - 1);
if FieldByName(FFieldNames[IntColumn-1]).DataType = ftString then
FExcelWorksheet.Range[StrBeginCell,StrEndCell].NumberFormat := '@' //other cases automatic 'general'
else
// Copy the field format from dataset
if FieldByName(FFieldNames[IntColumn-1]) is TNumericField then
if TNumericField(FieldByName(FFieldNames[IntColumn-1])).DisplayFormat <> '' then
FExcelWorksheet.Range[StrBeginCell,StrEndCell].NumberFormat :=
SetNumberSeparator(TNumericField(FieldByName(FFieldNames[IntColumn-1])).DisplayFormat);
end;
end;
end;
end;
//------------------------------------------------------------------------------
function TscExcelExport.CanConvertFieldToCell(const IntFieldIndex: Integer) : Boolean;
begin
Result := FieldDataType[IntFieldIndex] in
[ftString, ftSmallint, ftInteger, ftWord, ftAutoInc,
ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime,
ftLargeInt, ftWideString, ftVariant];
end;
//------------------------------------------------------------------------------
function TscExcelExport.IsValueField(const IntFieldIndex: Integer) : Boolean;
begin
Result := FieldDataType[IntFieldIndex] in
[ftSmallint, ftInteger, ftWord, ftFloat, ftCurrency];
end;
//------------------------------------------------------------------------------
function TscExcelExport.GetFieldDataType(IntIndex : Integer): TFieldType;
begin
Result := ftUnknown;
if DataPipe = dpDataSet then
Result := DataSet.Fields[IntIndex].DataType
else
if Assigned(FOnGetFieldDataType) then
FOnGetFieldDataType(Self, IntIndex, Result);
end;
//------------------------------------------------------------------------------
function TscExcelExport.GetWidthFromDatasize(const IntFieldIndex: Integer) : Integer;
var
IntFieldSize : Integer;
begin
IntFieldSize := 10;
if FDataPipe = dpDataSet then
IntFieldSize := DataSet.Fields[IntFieldIndex].DataSize
else
// Trigger event to get datasize
if Assigned(FOnGetFieldDataSize) then
FOnGetFieldDataSize(Self, IntFieldIndex, IntFieldSize);
// Datasize for datetime is to small when also time is saved
if FieldDataType[IntFieldIndex] = ftDateTime then
Result := 16
else
// For all other fieldtypes, just use the datasize
// Datasize = amount of memory to store value
Result := IntFieldSize;
end;
//------------------------------------------------------------------------------
// Get Column-character for giving index
//------------------------------------------------------------------------------
function TscExcelExport.GetColumnCharacters(IntNumber : Integer) : String;
begin
if IntNumber < 1 then
Result:='A'
else
begin
if IntNumber > 702 then
Result:='ZZ'
else
begin
if IntNumber > 26 then begin
if (IntNumber mod 26)=0 then
Result:=Chr(64 + (IntNumber div 26)-1)
else
Result:=Chr(64 + (IntNumber div 26));
if (IntNumber mod 26)=0 then
result:=result+chr(64+26)
else
result:=Result+Chr(64 + (IntNumber mod 26));
end
else
Result:=Chr(64 + IntNumber);
end;
end;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.Disconnect;
begin
// D6 -> these lines give an error OleSysError (invalid parameters)
// The same properties work in on line 760, 1040 ?!
// if not (FExcelApplication.Visible[LCID]) then
// if not FExcelApplication.ScreenUpdating[LCID] then
if not FBlnExcelVisible then
begin
FExcelApplication.DisplayAlerts[LCID] := False;
FExcelApplication.Quit;
end;
FExcelWorksheet.Disconnect;
FExcelWorkbook.Disconnect;
FExcelApplication.Disconnect;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.ExportDataset;
var
CurPrev : TCursor;
begin
CurPrev := Screen.Cursor;
Screen.Cursor := crHourGlass;
try
if FDataPipe = dpDataSet then
if not FDataset.Active then
Exit;
LCID := LOCALE_USER_DEFAULT; //GetUserDefaultLCID;
// Try to connect to Excel and create new Worksheet
try
if FConnectTo = ctNewExcel then
begin
FExcelApplication.ConnectKind := ckNewInstance;
FExcelApplication.Connect;
FExcelWorkbook.ConnectTo(FExcelApplication.Workbooks.Add(TOleEnum(xlWBATWorksheet), LCID));
FExcelWorksheet.ConnectTo(FExcelWorkbook.Worksheets[1] as _Worksheet);
end
else
begin
if FConnectTo = ctNewWorkbook then
begin
FExcelApplication.ConnectKind := ckRunningOrNew;
FExcelApplication.Connect;
FExcelWorkbook.ConnectTo(FExcelApplication.Workbooks.Add(TOleEnum(xlWBATWorksheet), LCID));
FExcelWorksheet.ConnectTo(FExcelWorkbook.Worksheets[1] as _Worksheet);
end
else
begin
FExcelApplication.ConnectKind := ckRunningOrNew;
FExcelApplication.Connect;
FExcelWorkbook.ConnectTo(FExcelApplication.ActiveWorkbook);
FExcelWorksheet.ConnectTo(FExcelWorkbook.Worksheets.Add(EmptyParam,EmptyParam,1,TOleEnum(xlWBATWorksheet),LCID) as _Worksheet);
end;
end;
except
Exit;
end;
FExcelApplication.ScreenUpdating[LCID] := False;
// If property worksheetname is not filled, worksheet will have name of dataset
if FStrWorksheetName <> '' then
FExcelWorksheet.Name := FStrWorksheetName
else
if FDataPipe = dpDataSet then
FExcelWorksheet.Name := FDataset.Name;
// Export header
ExportHeader;
// Export titels
ExportTitles;
// Export data
ExportFieldData;
// Calculate summary
if FSummarySelection <> ssNone then
ExportSummary;
// Set format (for string fields)
SetFormat;
// Set width of columns
SetColumnWidth;
FExcelWorksheet.Names.Add('naam',EmptyParam,True,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,'a1:b10',EmptyParam);
FExcelApplication.ScreenUpdating[LCID] := FBlnExcelVisible;
FExcelApplication.Visible[LCID]:=FBlnExcelVisible;
finally
Screen.Cursor := CurPrev;
end;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.ExportHeader;
var
i : Integer;
Matrix : Variant;
IntHeaderRows : Integer;
StrBeginColumnChar : String;
begin
IntHeaderRows := FStrHeaderText.Count;
if IntHeaderRows = 0 then
Exit;
Matrix := VarArrayCreate([1, IntHeaderRows, 1, 1], varOleStr);
for i := 1 to IntHeaderRows do
Matrix[i, 1] := FStrHeaderText[i - 1];
// Get character corresponding with column index (A ... ZZZZ)
StrBeginColumnChar := GetColumnCharacters(FIntBeginColumnHeader);
FExcelWorksheet.Range[
StrBeginColumnChar + IntToStr(FIntBeginRowHeader),
StrBeginColumnChar + IntToStr(FIntBeginRowHeader+IntHeaderRows-1)].Value := Matrix;
SetFontAndBorderRange(FFontHeader, FBorderHeader,
StrBeginColumnChar + IntToStr(FIntBeginRowHeader),
StrBeginColumnChar + IntToStr(FIntBeginRowHeader+IntHeaderRows-1));
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.ExportTitles;
var
IntColumn : Integer;
IntFieldIndex : Integer;
StrCell : String;
StrColumn : String;
StrTitle : String;
FltFontSizeFactor : Real;
FltTitleFontSizeFactor : Real;
IntFieldCount : Integer;
StrThisFieldName : String;
StrThisFieldDisplayName : String;
IntThisFieldDisplayWidth : Integer;
IntThisFieldDataSize : Integer;
BlnThisFieldVisible : Boolean;
begin
FStrBeginColumnDataChar := GetColumnCharacters(FIntBeginColumnData);
FFieldNames.Clear;
if FDataPipe = dpDataSet then
IntFieldCount := FDataset.Fields.Count
else
if Assigned(FOnGetFieldCount) then
FOnGetFieldCount(Self, IntFieldCount)
else
Exit;
if FBlnShowTitles then
begin
for IntColumn := FIntBeginColumnData to (IntFieldCount + FIntBeginColumnData -1) do
begin
IntFieldIndex := IntColumn - FIntBeginColumnData;
// Only export fields which are writable in an Excel cell
// Don't export non visible fields if VisibleFieldsOnly is True
// Add these fields to a list, so this list can be used when exporting data
BlnThisFieldVisible := True;
if FDataPipe = dpDataSet then
BlnThisFieldVisible := DataSet.Fields[IntFieldIndex].Visible
else
if Assigned(FOnGetFieldVisible) then
FOnGetFieldVisible(Self, IntFieldIndex, BlnThisFieldVisible);
if CanConvertFieldToCell(IntFieldIndex) and
((not VisibleFieldsOnly) or (VisibleFieldsOnly and
BlnThisFieldVisible)) then
begin
StrColumn := GetColumnCharacters(FFieldNames.Count + FIntBeginColumnData);
StrCell:=StrColumn+IntToStr(FIntBeginRowTitles);
StrThisFieldName := '';
if FDataPipe = dpDataSet then
StrThisFieldName := DataSet.Fields[IntFieldIndex].FieldName
else
if Assigned(FOnGetFieldName) then
FOnGetFieldName(Self, IntFieldIndex, StrThisFieldName);
FFieldNames.AddObject(StrThisFieldName, TObject(IntFieldIndex));
// Use DisplayName of column if this is filled in, otherwise use FieldName
StrThisFieldDisplayName := '';
if FDataPipe = dpDataSet then
StrThisFieldDisplayName := DataSet.Fields[IntFieldIndex].DisplayName
else
if Assigned(FOnGetFieldDisplayName) then
FOnGetFieldDisplayName(Self, IntFieldIndex, StrThisFieldDisplayName);
if StrThisFieldDisplayName <> '' then
StrTitle := StrThisFieldDisplayName
else
StrTitle := StrThisFieldName;
FExcelWorksheet.Range[StrCell,StrCell].Value := StrTitle;
IntThisFieldDisplayWidth := 0;
if FDataPipe = dpDataSet then
IntThisFieldDisplayWidth := DataSet.Fields[IntFieldIndex].DisplayWidth
else
if Assigned(FOnGetFieldDisplayWidth) then
FOnGetFieldDisplayWidth(Self, IntFieldIndex, IntThisFieldDisplayWidth);
// Use DisplayField of each field to set the column width
if FStyleColumnWidth = cwFieldDisplayWidth then
begin
// Value of datasize fits when font size = 10, so calculate factor when font size is larger
FltFontSizeFactor := FFontData.Size / 10;
FExcelWorksheet.Range[StrCell,StrCell].ColumnWidth :=
Integer(Round(IntThisFieldDisplayWidth * FltFontSizeFactor));
end
else
begin
// Use Datasize of each field to set the column width
if FStyleColumnWidth = cwFieldDataSize then
begin
// Value of datasize fits when font size = 10, so calculate factor when font size is larger
FltFontSizeFactor := FFontData.Size / 10;
IntThisFieldDataSize := 0;
if FDataPipe = dpDataSet then
IntThisFieldDataSize := DataSet.Fields[IntFieldIndex].DataSize
else
if Assigned(FOnGetFieldDataSize) then
FOnGetFieldDataSize(Self, IntFieldIndex, IntThisFieldDataSize);
FExcelWorksheet.Range[StrCell,StrCell].ColumnWidth :=
Integer(Round(GetWidthFromDatasize(IntFieldIndex) * FltFontSizeFactor));
end
else
begin
// Style = adaptive -> use DisplayWidth of TField except when title of column is larger
if FStyleColumnWidth = cwEnhAutoFit then
begin
// Value of datasize fits when font size = 10, so calculate factor when font size is larger
FltFontSizeFactor := FFontData.Size / 10;
FltTitleFontSizeFactor := FFontTitles.Size / 10;
if ((Length(StrTitle) + 1) * FltTitleFontSizeFactor) >
(IntThisFieldDisplayWidth * FltFontSizeFactor) then
FExcelWorksheet.Range[StrCell,StrCell].ColumnWidth:=Integer(Round((Length(StrTitle) + 1) * FltTitleFontSizeFactor) + 1)
else
FExcelWorksheet.Range[StrCell,StrCell].ColumnWidth :=
Integer(Round(IntThisFieldDisplayWidth * FltFontSizeFactor));
end
// else cwDefault, cwOwnerWidth, cwAutoFit
// These columns widths are set after exporting all data in the procedure SetColumnWidth
end;
end;
end;
end;
SetFontAndBorderRange(FFontTitles, FBorderTitles, FStrBeginColumnDataChar+IntToStr(FIntBeginRowTitles),
GetColumnCharacters(FFieldNames.Count + FIntBeginColumnData -1)+IntToStr(FIntBeginRowTitles));
end
else
begin
// Titles will not be visible, but run through fields
for IntColumn := FIntBeginColumnData to (IntFieldCount + FIntBeginColumnData -1) do
begin
IntFieldIndex := IntColumn - FIntBeginColumnData;
BlnThisFieldVisible := True;
if FDataPipe = dpDataSet then
BlnThisFieldVisible := DataSet.Fields[IntFieldIndex].Visible
else
if Assigned(FOnGetFieldVisible) then
FOnGetFieldVisible(Self, IntFieldIndex, BlnThisFieldVisible);
StrThisFieldName := '';
if FDataPipe = dpDataSet then
StrThisFieldName := DataSet.Fields[IntFieldIndex].FieldName
else
if Assigned(FOnGetFieldName) then
FOnGetFieldName(Self, IntFieldIndex, StrThisFieldName);
// Only export fields which are writable in an Excel cell
// Don't export non visible fields if VisibleFieldsOnly is True
// Add these fields to a list, so this list can be used when exporting data
if CanConvertFieldToCell(IntFieldIndex) and
((not VisibleFieldsOnly) or (VisibleFieldsOnly and BlnThisFieldVisible))
then
begin
StrColumn := GetColumnCharacters(FFieldNames.Count + FIntBeginColumnData);
FFieldNames.AddObject(StrThisFieldName, TObject(IntFieldIndex));
end;
end;
end;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.ExportFieldData;
var
IntColumn : Integer;
IntBeginRow, IntEndRow : Integer;
IntMatrixRow : Integer;
PtBookmark : TBookmark;
Matrix : Variant;
VarCurrentValue : Variant;
MatrixBackgroundColors : Variant;
ColorBackground : TColor;
function IsEOF : Boolean;
begin
Result := True;
if FDataPipe = dpDataSet then
Result := DataSet.Eof
else
if Assigned(FOnGetEOF) then
FOnGetEOF(Self, Result);
end;
function ExcelRangeStr(r1, c1: integer) : string;
begin
Result := chr(c1 + 64) + IntToStr(r1);
end;
procedure ChangeBackgroundColorCells(IntEndRecord : Integer);
var
i,j : integer;
begin
if Assigned(FOnGetCellBackgroundColorEvent) then
begin
for i:=1 to IntEndRecord do
begin
for j:=1 to FFieldNames.Count do
begin
if (FBorderData.FBackColor <> clWhite) or (MatrixBackgroundColors[i,j] <> clWhite) then
begin
FExcelWorksheet.Range[ExcelRangeStr(IntBeginRow+i-1,FIntBeginColumnData+j-1),
ExcelRangeStr(IntBeginRow+i-1,FIntBeginColumnData+j-1)].Interior.Color:=MatrixBackgroundColors[i,j];
end;
end;
end;
end;
end;
begin
FIntRecordNo := 0;
if DataPipe = dpDataSet then
begin
FDataset.DisableControls;
PtBookmark := FDataset.GetBookmark;
end
else
PtBookmark := nil;
try
// Create a matrix of variants
// - Columns = number of fields
// - Rows = block of records (FIntBlockOfRecords)
Matrix := VarArrayCreate([1,FIntBlockOfRecords,1,FFieldNames.Count],varVariant);
// When event is used, create matrix for the cell background colors
if Assigned(FOnGetCellBackgroundColorEvent) then
MatrixBackgroundColors := VarArrayCreate([1,FIntBlockOfRecords,1,FFieldNames.Count],varVariant);
IntBeginRow := FIntBeginRowData;
IntEndRow := FIntBeginRowData + FIntBlockOfRecords-1 ;
IntMatrixRow := 0;
if DataPipe = dpDataSet then
FDataset.First;
while not IsEOF do
begin
Inc(FIntRecordNo);
Inc(IntMatrixRow);
for IntColumn := 1 to FFieldNames.Count do
begin
VarCurrentValue := Null;
if FDataPipe = dpDataSet then
VarCurrentValue := DataSet.Fields[Integer(FFieldNames.Objects[IntColumn - 1])].AsVariant
else
if Assigned(FOnGetFieldValue) then
FOnGetFieldValue(Self, Integer(FFieldNames.Objects[IntColumn - 1]), VarCurrentValue);
Matrix[IntMatrixRow,IntColumn] := VarCurrentValue;
if FDataPipe = dpDataSet then
begin
// Trigger event GetCellColor and fill matrix with cell background colors
if Assigned(FOnGetCellBackgroundColorEvent) then
begin
if FBorderData.FBackColor <> clWhite then
MatrixBackgroundColors[IntMatrixRow,IntColumn] := FBorderData.FBackColor
else
MatrixBackgroundColors[IntMatrixRow,IntColumn] := clWhite;
ColorBackground := MatrixBackgroundColors[IntMatrixRow,IntColumn];
FOnGetCellBackgroundColorEvent(Self,FDataset.FieldByName(FFieldNames[IntColumn - 1]),ColorBackground);
MatrixBackgroundColors[IntMatrixRow,IntColumn]:=ColorBackground;
end;
end;
end;
// Create a new block of records to export to Excel
// Don't export all data to one variant matrix because memory has it limitations
// Property FIntBlockOfRecords is default 20 records
// Check if matrix is full, and if so, write the block to excel
if (FIntRecordNo mod FIntBlockOfRecords = 0) then
begin
FExcelWorksheet.Range[FStrBeginColumnDataChar+IntToStr(IntBeginRow),GetColumnCharacters(FFieldNames.Count + FIntBeginColumnData - 1)+IntToStr(IntEndRow)].Value := Matrix;
ChangeBackgroundColorCells(FIntBlockOfRecords);
IntBeginRow := IntBeginRow + FIntBlockOfRecords; // next insert starts here
IntEndRow := IntBeginRow + FIntBlockOfRecords -1; // next block ends here
IntMatrixRow := 0; // reset index into matrix
end;
if FDataPipe = dpDataSet then
FDataSet.Next;
if Assigned(FOnExportRecords) then
FOnExportRecords(Self,FIntRecordNo);
end;
// Now that EOF is true, so check if the matrix has remaining data to write
if (IntMatrixRow > 0) then
begin
// recalculate the block's end
IntEndRow := IntBeginRow + IntMatrixRow-1;
// Write remaining block
FExcelWorksheet.Range[FStrBeginColumnDataChar+IntToStr(IntBeginRow),GetColumnCharacters(FFieldNames.Count + FIntBeginColumnData - 1)+IntToStr(IntEndRow)].Value := Matrix;
ChangeBackgroundColorCells(IntEndRow-IntBeginRow+1);
end;
finally
if DataPipe = dpDataSet then
begin
FDataset.GotoBookmark(PtBookmark);
FDataset.FreeBookmark(PtBookmark);
FDataset.EnableControls;
end;
end;
FIntEndRowData := IntEndRow;
SetFontAndBorderRange(FFontData, FBorderData,FStrBeginColumnDataChar+IntToStr(FIntBeginRowData),
GetColumnCharacters(FFieldNames.Count + FIntBeginColumnData -1)+IntToStr(FIntEndRowData));
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.ExportSummary;
const
SUM_ARR: Array[TSummaryCalculation] of String = ('SUM', 'MIN', 'MAX');
var
IntColumn : Integer;
StrCell : String;
StrCalc : String;
StrBeginCell, StrEndCell : String;
function Summarized(aColumn: Integer): Boolean;
begin
case FSummarySelection of
ssValues:
Result := IsValueField(Integer(FFieldNames.Objects[aColumn - 1]));
ssGiven:
Result := FSummaryFields.IndexOf(
UpperCase(FFieldNames[aColumn - 1])) > -1;
else
Result := False;
end;
end;
begin
with FDataset do
begin
for IntColumn := 1 to FFieldNames.Count do
begin
if Summarized(IntColumn) then
begin
StrCell:=GetColumnCharacters(IntColumn)+IntToStr(FIntRecordNo + FIntBeginRowData);
StrCalc := SUM_ARR[FSummaryCalculation];
StrBeginCell := GetColumnCharacters(IntColumn)+IntToStr(FIntBeginRowData);
StrEndCell := GetColumnCharacters(IntColumn)+IntToStr(FIntRecordNo + FIntBeginRowData - 1);
FExcelWorksheet.Range[StrCell,StrCell].Value := Format('=%s(%s:%s)', [StrCalc, StrBeginCell, StrEndCell]);
end;
end;
end;
SetFontAndBorderRange(FFontSummary, FBorderSummary, 'A'+IntToStr(FIntRecordNo + FIntBeginRowData),
GetColumnCharacters(FFieldNames.Count)+IntToStr(FIntRecordNo + FIntBeginRowData));
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SaveAs(const StrFileName : String; const FileFormat : TFileFormat);
begin
FExcelApplication.DisplayAlerts[LCID] := False;
// Export data to a file
case FileFormat of
ffXLS : FExcelWorksheet.SaveAs(StrFileName,TOleEnum(xlWorkbookNormal));
// For 97 and 2000 compatible format
ffXL97: FExcelWorksheet.SaveAs(StrFileName,TOleEnum(xlExcel9795));
ffCSV : FExcelWorksheet.SaveAs(StrFileName,TOleEnum(xlCSV));
// Only works with Excel2000
{$IFDEF VER140}
ffHTM : FExcelWorksheet.SaveAs(StrFileName,TOleEnum(xlHtml));
{$ENDIF}
end;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.PrintPreview(const BlnPrintGridLines : Boolean);
begin
// Show PrintPreview of Excel
FExcelWorksheet.PageSetup.PrintGridlines:=BlnPrintGridLines;
FExcelWorksheet.PageSetup.CenterHeader:=FExcelWorksheet.Name;
FExcelApplication.ScreenUpdating[LCID]:=True;
FExcelApplication.Visible[LCID]:=True;
FBlnExcelVisible:=True;
FExcelWorksheet.PrintPreview;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetVisibleFieldsOnly(const Value: Boolean);
begin
FVisibleFieldsOnly := Value;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetBeginRowHeader(const Value: Integer);
begin
if FStrHeaderText.Count > 0 then
begin
if Value > 0 then
FIntBeginRowHeader := Value
else
FIntBeginRowHeader := 1;
if FIntBeginRowTitles < FIntBeginRowHeader + FStrHeaderText.Count - 1 then
SetBeginRowTitles(FIntBeginRowHeader + FStrHeaderText.Count);
end
else
FIntBeginRowHeader := 1;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetBeginRowTitles(const Value: Integer);
begin
if Value < FIntBeginRowHeader + FStrHeaderText.Count then
FIntBeginRowTitles := FIntBeginRowHeader + FStrHeaderText.Count
else
FIntBeginRowTitles := Value;
if FIntBeginRowTitles >= FIntBeginRowData then
SetBeginRowData(FIntBeginRowTitles + 1);
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetBeginRowData(const Value: Integer);
begin
if Value <= FIntBeginRowTitles then
FIntBeginRowData := FIntBeginRowTitles + 1
else
FIntBeginRowData := Value;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetBeginColumnData(const Value: Integer);
begin
if Value < 1 then
FIntBeginColumnData := 1
else
FIntBeginColumnData := Value;
end;
//------------------------------------------------------------------------------
procedure TscExcelExport.SetBeginColumnHeader(const Value: Integer);
begin
if Value < 1 then
FIntBeginColumnHeader := 1
else
FIntBeginColumnHeader := Value;
end;
end.