There's really a lot of examples on the web as well as on usenet (most
easily accessible via groups.google.com.) That said, here's a few
snippets of code as you requested:
Obviously you need this somewhere...:
uses .Excel2000; // or XP or whatever.
Declarations (e.g. perhaps in private section of class somewhere):
FXLA : TExcelApplication;
FXLWB : TExcelWorkbook;
FXLWS : TExcelWorkSheet;
Dynamically create wrappers (if you like, e.g. perhaps in Create):
// nil owner so we're responsible for freeing (the vcl will not.)
FXLA := TExcelApplication.Create(nil);
FXLWB := TExcelWorkbook.Create(nil);
FXLWS := TExcelWorksheet.Create(nil);
//so the first access/use of wrapper will implicitly connect/create an
instance of Excel if neccesary
FXLA.AutoConnect := True;
Finally free the wrappers (e.g. perhaps in Destroy):
FXLWS.Free;
FXLWB.Free;
FXLA.Free;
Prepare to use the wrappers (maybe in the main method for doing the work
with Excel):
XLA.Visible[GetUserDefaultLCID] := False; //Hide Excel while working
try
XLA.DisplayAlerts[GetUserDefaultLCID] := False; //no prompts
XLWB.ConnectTo( XLA.Workbooks.Add(xlWBATWorksheet,
GetUserDefaultLCID) ); // add new workbook and connect WB wrapper
// connect to sheet 1, and populate it
XLWS.ConnectTo( XLWB.Worksheets[1] as _Worksheet );
PopulateWS();
(XLWB.Worksheets[1] as _WorkSheet).Select(EmptyParam,
GetUserDefaultLCID); //select the first worksheet
finally
XLA.Visible[GetUserDefaultLCID] := True; // failsafe always leave
excel visible when we quit/disconnect.
end;
Stuff you might want to do:
Some basic cell manipulations:
var
XLRange : ExcelRange;
FmtConds : FormatCondition;
...
// get a range object to work with, representing one or more cells (in
this case one cell, also shows use of Cells property)
XLRange := XLWS.Range[ XLWS.Cells.Item[Row, Col], XLWS.Cells.Item[Row,
Col] ];
// now use it to set up the cell
XLRange.Value := SomeValue; // variant
if (NumberFormat <> '') then XLRange.NumberFormat := NumberFormat;
set up the number format to use.
XLRange.Borders.Item[xlEdgeRight].LineStyle := xlContinuous; // set
some edges.
XLRange.Borders.Item[xlEdgeRight].Weight := xlHairline; //or xlThin etc
// VB Macro code this was derived from:
// Selection.FormatConditions.Delete
// Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLess, _
// Formula1:="=$F$4"
// Selection.FormatConditions(1).Font.ColorIndex = 3
// Add conditional formatting to cell
XLRange.FormatConditions.Delete;
FmtConds := XLRange.FormatConditions .Add(xlCellValue, xlLess,
'=$F$4', EmptyParam);
FmtConds.Font.ColorIndex := 3; //Red
Merge some cells and format them
with XLWS.Range['A1','L1'] do
begin
MergeCells := True;
Value := 'Some value';
Font.Size := 14;
Font.Bold := True;
end;
Set a formula in a cell
XLWS.Cells.Item[Row, Col].Formula := '=sum(A1:A10)';
Set the number format for cells containing a date to only display year
and month:
XLWS.Cells.Item[Row, Col].NumberFormat := 'mmm-yy';
Set the number format for a cell containing a number to be currency
(british pounds) with a trailing k (since the value is indicative of
thousands) and with every thousand demarcated by a comma as well:
XLWS.Cells.Item[Row, Col].NumberFormat := '£#,##0k'
Select an area that spans all populated cells:
//select entire area
XLRange := XLWS.Range[CellRef1,CellRef1];
XLRange := XLWS.Range[XLRange, XLRange.End_[xlToRight]];
XLRange := XLWS.Range[XLRange, XLRange.End_[xlDown]];
Autofit all rows and columns:
XLWS.Cells.EntireRow.AutoFit;
XLWS.Cells.EntireColumn.AutoFit;
Setup area for single page landscape printing:
var
lcid : Integer;
RangeAddress : String;
begin
lcid := GetUserDefaultLCID;
// Detemine Print range -- shows another way to select all the
populated cells on the worksheet.
RangeAddress := XLWS.Range[XLWS.Cells.Item[1, 1],
XLWS.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam)].Address[1, 1,
1, 1, 1];
// now set page up
with XLWS.PageSetup do
begin
PrintArea := RangeAddress;
Orientation := xlLandscape;
Zoom := False;
FitToPagesWide := 1;
LeftMargin := XLWS.Application.InchesToPoints(0.18, lcid);
RightMargin := XLWS.Application.InchesToPoints(0.18, lcid);
TopMargin := XLWS.Application.InchesToPoints(0.18, lcid);
BottomMargin := XLWS.Application.InchesToPoints(0.18, lcid);
HeaderMargin := 0;
FooterMargin := 0;
PaperSize := xlPaperA4;
FitToPagesTall := 1
end;
end;
Add a graph to a sheet using non-adjacent columns for the data:
var
XLRange : ExcelRange;
XLChartWS : ExcelWorksheet;
begin
// New worksheet for graphs:
XLChartWS := XLWB.Sheets.Add( EmptyParam, XLWS.DefaultInterface,
EmptyParam, EmptyParam, lcid) as _WorkSheet;
XLChartWS.Name := 'Blah graphs';
// Range for the data -- shows how to offset a given cell by an
arbitrary amount in x or y direction (Offset property),
// and how to get the cell reference from the cell/range itself etc.
XLRange := XLWS.Range[
XLWS.Range['L6', XLWS.Range['L6',
EmptyParam].End_[xlDown].Offset[-1,0] ].Address[1, 1, 1, 1, 1] + ',' +
XLWS.Range['M6', XLWS.Range['M6',
EmptyParam].End_[xlDown].Offset[-1,0] ].Address[1, 1, 1, 1, 1] + ',' +
XLWS.Range['O6', XLWS.Range['O6',
EmptyParam].End_[xlDown].Offset[-1,0] ].Address[1, 1, 1, 1, 1],
EmptyParam];
then followed by:
var
XLChart : ExcelChart;
//using ChartObjects early bound interfaces here causes access
violation when using XLChart below???
// But late bound OLEVariant works... <shrug>
WSChartObjects : OLEVariant; //ChartObjects;
WSChartObject : OLEVariant; //ChartObject;
ChartAxis : Axis;
begin
// get reference to collection of chartobjects on worksheet
WSChartObjects := ChartObjects( XLChartWS.ChartObjects(EmptyParam,
lcid) );
// add new chart with specified position and size
WSChartObject := WSChartObjects.Add(Left, Top, Width, Height);
// as these are late bound variant references (slow), now we get an
early bound interface again
// (to the same chart object) to use from now on...
XLChart := IDispatch(WSChartObject.Chart) as ExcelChart;
// set up the chart type
XLChart.ChartType := xlLineMarkers;
// set data range, indicating data is in columns...
XLChart.SetSourceData( XLRange, xlColumns );
// Set the chart title
XLChart.HasTitle[lcid] := True;
XLChart.ChartTitle[lcid].Characters[EmptyParam,EmptyParam].Text :=
ChartTitle;
// x axis title.
ChartAxis := Axis ( XLChart.Axes(xlCategory, xlPrimary, lcid) );
ChartAxis.HasTitle := True;
ChartAxis.AxisTitle.Characters[EmptyParam,EmptyParam].Text := XAxisTitle;
// and the y axis...
ChartAxis := Axis ( XLChart.Axes(xlValue, xlPrimary, lcid) );
ChartAxis.HasTitle := True;
ChartAxis.AxisTitle.Characters[EmptyParam,EmptyParam].Text := YAxisTitle;
// legend? yes. Where? on the right... Data table? no thanks...
XLChart.HasLegend[lcid] := True;
XlChart.Legend[lcid].Position := xlRight;
XLChart.HasDataTable := False;
end;
I learnt most of that using Google (web and newsgroups) and the Excel
VBA macro recorder. Often the best way is to record a few actions in
the macro recorder then see what VBA code it generates, then reverse
engineer it into sensible/proper/usable delphi code using the wrapper.
It's not that hard once with a bit of practice. I would suggest you do
the same, and then when you get stuck come back and ask specific
questions about the problem you're having, stating what you've tried
already and what that resulted in. One final hint though: Try to stick
to using early bound interfaces (i.e. avoid using Excel via Variant
variables.) Not only do early bound proper interface references
support code completion in the IDE, but they're also a lot more
efficient than late bound access to the Excel object. If you don't know
what I'm on about, then google it.
Walter
Bijal Mandviya wrote:
>
> Hi,
>
> If u could send code snippet , tht's the best.
> It will be ur nice help.
> Thanks.
>
[Non-text portions of this message have been removed]
-----------------------------------------------------
Home page: http://groups.yahoo.com/group/delphi-en/
To unsubscribe: [EMAIL PROTECTED]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/delphi-en/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/delphi-en/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
