www.pudn.com > Excel.zip > MainD.pas

unit MainD; 
 
interface 
 
uses 
  SysUtils, Classes, Excel2000, OleServer, Forms, Windows, OleCtrls, Dialogs, 
  DB, DBTables, DBClient; 
 
type 
  TMainDm = class(TDataModule) 
    ExcelApp: TExcelApplication; 
    ExcelBook: TExcelWorkbook; 
    ExcelSheet: TExcelWorksheet; 
    DemoDb: TDatabase; 
    ItemsTable: TTable; 
    ExcelCds: TClientDataSet; 
    ExcelSrc: TDataSource; 
    procedure DataModuleDestroy(Sender: TObject); 
  private 
    { Private declarations } 
  public 
    procedure ConnectToExcel; 
    procedure CopyExcelDataToClientDataSet; 
    procedure CreateExcelWorkbook(FileName: String); 
    procedure DeleteExcelFile(FileName: String); 
    procedure DisconnectFromExcel; 
    procedure LoadWorksheetData; 
    procedure OpenExcelWorkbook(FileName: String); 
    { Public declarations } 
  end; 
 
var 
  MainDm: TMainDm; 
 
implementation 
 
uses Variants, MainF; 
 
{$R *.dfm} 
 
const 
  WORKSHEET_NAME          = 'Items'; 
 
procedure TMainDm.ConnectToExcel; 
{Starts an instance of Excel. To see what is happening in Excel as you run 
 this program uncomment the line that set's the Visible property to True.} 
begin 
  ExcelApp.Connect; 
  //ExcelApp.Visible[GetUserDefaultLCID] := True; 
end; 
 
procedure TMainDm.CopyExcelDataToClientDataSet; 
{Copies the data in the current Excel worksheet to the ExcelCds 
 ClientDataSet so it can be displayed.} 
var 
  Row:            Integer; 
  Col:            Integer; 
  DataRange:      Range; 
  FirstRow, 
  LastRow:        Integer; 
begin 
  {Get the range of cells that contains cell A1 then calculate the number of 
   the first and last rows in the range. Add one to the first row number to 
   skip the first row which contains the column titles, not data.} 
  DataRange := ExcelSheet.Range['A1', 'A1'].CurrentRegion; 
  FirstRow := DataRange.Row + 1; 
  LastRow := FirstRow + DataRange.Rows.Count - 2; 
  {Start with a new empty ClientDataset.} 
  ExcelCds.Close; 
  ExcelCds.CreateDataSet; 
  {Set the ClientDataset's ReadOnly property to False and display a status 
   message.} 
  MainForm.StatusBar.SimpleText := 'Loading Excel data...'; 
  ExcelCds.ReadOnly := False; 
  ExcelCds.DisableControls; 
  {Move down through the rows in the spreadsheet until a row with an empty 
   cell in column 1 is reached. For each row append a new row to the 
   ClientDataSet and copy the values.} 
  try 
    for Row := FirstRow to LastRow do 
    begin 
      ExcelCds.Append; 
      for Col := 1 to 5 do 
        ExcelCds.Fields[Col - 1].AsInteger := ExcelSheet.Cells.Item[Row, Col].Value; 
      ExcelCds.Post; 
    end; //while 
    ExcelCds.First; 
  finally 
    ExcelCds.EnableControls; 
    ExcelCds.ReadOnly := True; 
    MainForm.StatusBar.SimpleText := ''; 
  end; //try 
end; 
 
procedure TMainDm.CreateExcelWorkbook(FileName: String); 
{Creates a new Excel workbook file with one worksheet and saves it in the 
 application directory.} 
var 
  XlWorkbook:          _Workbook; 
begin 
  FileName := ExtractFilePath(Application.ExeName) + FileName; 
  {Delete the file if it exists because the workbook SaveAs method 
   will not overwrite an existing file.} 
  DeleteExcelFile(FileName); 
 
  ExcelApp.SheetsInNewWorkbook[GetUserDefaultLCID] := 1; 
  XlWorkbook := ExcelApp.Workbooks.Add(EmptyParam, GetUserDefaultLCID); 
  (XlWorkbook.ActiveSheet as _Worksheet).Name := WORKSHEET_NAME; 
  XlWorkbook.SaveAs(FileName, 
                     EmptyParam, 
                     EmptyParam, 
                     EmptyParam, 
                     EmptyParam, 
                     False, 
                     xlExclusive, 
                     EmptyParam, 
                     EmptyParam, 
                     EmptyParam, 
                     EmptyParam, 
                     GetUserDefaultLCID); 
  {Connect the ExcelBook component to the new workbook and connect the 
   ExcelSheet component to the worksheet it contains then set the name 
   of the worksheet.} 
  ExcelBook.ConnectTo(XlWorkbook); 
  ExcelSheet.ConnectTo(XlWorkbook.ActiveSheet as _Worksheet); 
end; 
 
procedure TMainDm.DataModuleDestroy(Sender: TObject); 
{Saves and closes the workbook and disconnects from Excel when the 
 data module is destroyed.} 
begin 
  DisconnectFromExcel; 
end; 
 
procedure TMainDm.DeleteExcelFile(FileName: String); 
begin 
  if SysUtils.FileExists(FileName) then 
    if not SysUtils.DeleteFile(FileName) then 
      RaiseLastOsError; 
end; 
 
procedure TMainDm.DisconnectFromExcel; 
{Save and close the workbook and disconnect from all Excel objects.} 
begin 
  try 
    ExcelBook.Close(True); 
  except 
  end; //try 
  ExcelSheet.Disconnect; 
  ExcelBook.Disconnect; 
  ExcelApp.Disconnect; 
end; 
 
procedure TMainDm.LoadWorksheetData; 
{Loads the data from the DBDemos Items table into the empty worksheet.} 
var 
  Row:                Integer; 
  Col:                Integer; 
begin 
  MainForm.StatusBar.SimpleText := 'Loading Excel data...'; 
  {Put the column names in the first row of the worksheet.} 
  ExcelSheet.Cells.Item[1, 1].Value := 'OrderNo'; 
  ExcelSheet.Cells.Item[1, 2].Value := 'ItemNo'; 
  ExcelSheet.Cells.Item[1, 3].Value := 'PartNo'; 
  ExcelSheet.Cells.Item[1, 4].Value := 'Qty'; 
  ExcelSheet.Cells.Item[1, 5].Value := 'Discount'; 
  {Loop through the Items table and copy the fields from each record into 
   the corresponding row of the worksheet.} 
  ItemsTable.Open; 
  Row := 1; 
  while not ItemsTable.Eof do 
  begin 
    Inc(Row); 
    for Col := 1 to 5 do 
      ExcelSheet.Cells.Item[Row, Col].Value := 
        ItemsTable.Fields[Col - 1].Value; 
    ItemsTable.Next; 
  end; //while 
  {Close the table and save the changes to the workbook.} 
  ItemsTable.Close; 
  ExcelBook.Save; 
  MainForm.StatusBar.SimpleText := ''; 
end; 
 
procedure TMainDm.OpenExcelWorkbook(FileName: String); 
{Opens an existing workbook file and connects the ExcelBook and ExcelSheet 
 components to the newly opened workbook and its active worksheet.} 
var 
  XlWorkbook:            _Workbook; 
begin 
  XlWorkbook := ExcelApp.Workbooks.Open( 
                          ExtractFilePath(Application.ExeName) + FileName, 
                          EmptyParam, EmptyParam, 
                          EmptyParam, EmptyParam, EmptyParam, 
                          EmptyParam, EmptyParam, EmptyParam, 
                          EmptyParam, EmptyParam, EmptyParam, 
                          EmptyParam, GetUserDefaultLCID); 
  ExcelBook.ConnectTo(XlWorkbook); 
  ExcelSheet.ConnectTo(ExcelApp.ActiveSheet as _Worksheet); 
end; 
 
end.