Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel, TExcelApplication, Delphi7

Posted on 2004-11-08
10
Medium Priority
?
6,835 Views
Last Modified: 2008-01-09
Hi,

I'm looking for some documents/examples/help on using the TExcelApplication component on the servers tab of delphi7.

0
Comment
Question by:jamesr123456
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 4

Expert Comment

by:Greg Rowland
ID: 12524387
http://www.djpate.freeserve.co.uk/Automation.htm

There is an old message thread I will try to locate for you.
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12524394
0
 
LVL 4

Expert Comment

by:Greg Rowland
ID: 12524460
procedure TGeneralProceduresf.myStartExcelWorkBookSimple;
begin
  Screen.Cursor := crSqlWait;
  { No need to start Excel ourselves, since the ExcelApplication's AutoConnect
  property is True }
  lcid := GetUserDefaultLCID;
  ExcelApplication1.Visible[lcid]:=True;

  { Create a new workbook }
  { The TOleEnum cast in the next line just prevents compiler range warnings }
  ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
// ExcelApplication1.ScreenUpdating[lcid] := False;

end;

procedure TGeneralProceduresf.myEndExcelWorkBook;
begin
 Screen.Cursor := crDefault;
 ExcelApplication1.ScreenUpdating[lcid] := True;

 ExcelWorksheet1.Disconnect;
 ExcelWorkbook1.Disconnect;
 ExcelApplication1.Disconnect;

end;


procedure myExcelData(myDataSource: TwwDataSource);
var
 myArray: Variant;
 c, r, i: integer;
 myCol: String;

begin
// Schedule report --------

 with myDataSource.DataSet do begin
   // myData.DataSet
   myArray := VarArrayCreate([1, RecordCount + 10, 1, FieldCount + 10], varVariant);
   myCol := myColumnAlpha(FieldCount + 10)  + IntToStr(RecordCount + 10);

   for  i := 0 to FieldCount - 1 do
     if not (Fields[i].IsBlob)
        and (Fields[i].FieldName <> 'NOTES')
        and (Fields[i].FieldName <> 'NEWNOTE') then
       myArray[ 1, i + 1] := Fields[i].FieldName;

   First;
   r := 2;
   repeat
     for  i := 0 to FieldCount - 1 do
       if not (Fields[i].IsBlob)
         and (Fields[i].FieldName <> 'NOTES')
         and (Fields[i].FieldName <> 'NEWNOTE') then
           myArray[ r, i + 1] :=  Fields[i].AsString;

     Inc(r);
     Next;
   until (Eof);

   with GeneralProceduresf, ExcelApplication1, ExcelWorksheet1 do begin
     myStartExcelWorkBookSimple;
     Range['A1', myCol].Value := myArray;
     Range['A1', 'A1'].Select;
     Range['A1', myColumnAlpha(FieldCount + 10) + '1'].Font.Bold := True;

     myEndExcelWorkBook;

   end;

 end;


end;


Good luck
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:calinutz
ID: 12526568
For documents on how to use Excel use the Deborah Pate's automation pages as suggested above, they are the best you'll get on the web.
But for specific questions... you are welcome to ask them here.
0
 

Author Comment

by:jamesr123456
ID: 12535829
Deborah's pages are old and dont work with ExcelXP
0
 
LVL 11

Expert Comment

by:calinutz
ID: 12548054
:)
Then I guess you will need to ask a question.... ;)
0
 

Author Comment

by:jamesr123456
ID: 12548103
I did ask a question! I was hoping for some recent examples/links. Word 97 is 5 years old!
0
 
LVL 4

Accepted Solution

by:
Greg Rowland earned 375 total points
ID: 12548889
The code examples run under Delphi 7.0 Office 97, 2000 and 2003, I will try them with Offce XP when I get a chance.

I started working automation in 1998 initially with a lot of frustration.
Ultimately “while not simple” the results have been awesome.
I would highly recommend you subscribe to this newsgroup
      borland.pulic.delphi.oleautomation

Deborah “The Goddess of automation” frequents there and is very generous with knowledge.

Cheers,

G
0
 

Author Comment

by:jamesr123456
ID: 12591948
I seem to be getting somewhere, slowly! damn! was hoping I wouldnt have to get my feet wet, oh well...
0
 

Author Comment

by:jamesr123456
ID: 12592682
For anybody wanting another example:
uses OleServer, ExcelXp, Activex;

var XApp: TExcelApplication;
    LCID: Integer;
    XWorkBook: TExcelWorkBook;
    XWorkSheet: TExcelWorkSheet;
    XDataSet:TDataset;
    XStartCell:array[1..2] of string;
    LoopRows:Integer;
    LoopColumns:Integer;
    MyRange:String;
    temp:integer;
    Format: OleVariant;
    LargestColumn:integer;

begin
LargestColumn:=0;

// Limited to a range between A and Z

// Open Excel
LCID:=GetUserDefaultLCID;

XApp:=TExcelApplication.Create(Self);
XWorkBook:=TExcelWorkBook.Create(Self);
XWorkSheet:=TExcelWorkSheet.Create(Self);

XApp.ConnectKind:=ckNewInstance;
XApp.Connect;
XApp.Visible[LCID]:=True;
XApp.ScreenUpdating[LCID] := False;

XWorkBook.ConnectTo(XApp.Workbooks.Add(TOleEnum(xlWBATWorksheet),lcid));
XWorkSheet.ConnectTo(XApp.Worksheets[1] as _Worksheet);

XDataSet:=Cost.CostTable; //TClientDataset

// Copy dataset column headings to excel
XStartCell[1]:=Chr(Ord('A')-1);
XStartCell[2]:='4';
For LoopColumns:=0 to XDataSet.FieldCount-1 do
  begin
  MyRange:= Chr(Ord(XStartCell[1][1])+LoopColumns+1) + inttostr( strtoint(XStartCell[2])+1);
  XWorkSheet.Range[MyRange, MyRange].Value2:=XDataSet.Fields[LoopColumns].DisplayName;
  XWorkSheet.Range[MyRange, MyRange].EntireColumn.AutoFit;
  end;

// Copy rest of dataSet into excel
  begin
  XStartCell[1]:=Chr(Ord('A')-1);
  XStartCell[2]:='5';

  For LoopRows:=1 to XDataSet.RecordCount do
    begin
    XDataSet.RecNo:=LoopRows;

    For LoopColumns:=0 to XDataSet.FieldCount-1 do
      begin
      MyRange:= Chr(Ord(XStartCell[1][1])+LoopColumns+1) + inttostr( strtoint(XStartCell[2])+LoopRows);

      if LoopColumns=0 then
        begin
        XWorkSheet.Range[MyRange,MyRange].Value2:=XDataSet.Fields[LoopColumns].AsString;
        XWorkSheet.Range[MyRange,MyRange].NumberFormat:='@';
        end
      else
        begin
        Format := '#,##0.00_);[Red](#,##0.00)';
        XWorkSheet.Range[MyRange,MyRange].Value2:=XDataSet.Fields[LoopColumns].AsCurrency;
        XWorkSheet.Range[MyRange,MyRange].NumberFormat:=Format;
        if XWorkSheet.Range[MyRange, MyRange].ColumnWidth>LargestColumn then
          LargestColumn:=XWorkSheet.Range[MyRange, MyRange].ColumnWidth;
        end;


      end;

    end;
  end;

// Headings
XWorkSheet.Range['A1','A1'].Value2:='General Costing 2004';
XWorkSheet.Range['A2','A2'].Value2:='Product Category Blah';
XWorkSheet.Range['A1','A2'].Font.Size:=14;

XApp.ScreenUpdating[LCID] := True;

// Adjust Column Widths

XStartCell[1]:=Chr(Ord('B'));
XStartCell[2]:='5';
XWorkSheet.Range[XStartCell[1]+XStartCell[2], 'Z99'].ColumnWidth:=LargestColumn+3;

// Close excel and free objects

XWorkSheet.Disconnect;
XWorkBook.Disconnect;
XApp.Disconnect;

XWorkSheet.Free;
XWorkBook.Free;
XApp.Free;
end;
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question