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

unit MainD; 
 
interface 
 
uses 
  SysUtils, Classes, DB, ADODB, DBTables; 
 
type 
  TMainDm = class(TDataModule) 
    ExcelConn: TADOConnection; 
    ExcelDs: TADODataSet; 
    ExcelSrc: TDataSource; 
    ExcelCmd: TADOCommand; 
    DemoDb: TDatabase; 
    ItemsTable: TTable; 
    procedure ExcelDsAfterOpen(DataSet: TDataSet); 
  private 
    { Private declarations } 
  public 
    { Public declarations } 
    procedure CreateExcelFile; 
    procedure LoadData; 
    procedure OpenExcelFile; 
    procedure RefreshExcelData; 
  end; 
 
var 
  MainDm: TMainDm; 
 
implementation 
 
uses Variants, MainF; 
 
{$R *.dfm} 
 
const 
  ConnStr1         = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='; 
  ConnStr2         = ';Extended Properties=excel 8.0;"";'; 
  XlsName          = 'Items'; 
  XlsDir           = 'w:\di\excel\'; 
  XlsPath          = XlsDir + XlsName + '.xls'; 
  XlsTemplatePath  = XlsDir + XlsName + 'Template.xls'; 
  XlsWorksheet     = XlsName + '$'; 
 
procedure TMainDm.CreateExcelFile; 
var 
  TempFs:           TFileStream; 
  ExcelFs:          TFileStream; 
begin 
  ExcelConn.Close; 
  TempFs := TFileStream.Create(XlsTemplatePath, fmOpenRead); 
  try 
    ExcelFs := TFileStream.Create(XlsPath, fmCreate); 
    try 
      ExcelFs.CopyFrom(TempFs, 0); 
    finally 
      ExcelFs.Free; 
    end; //try 
  finally 
    TempFs.Free; 
  end; //try 
end; 
 
procedure TMainDm.OpenExcelFile; 
begin 
  ExcelConn.ConnectionString := ConnStr1 + XlsPath + ConnStr2; 
  ExcelDs.CommandText := XlsWorksheet; 
  ExcelDs.Open; 
end; 
 
procedure TMainDm.LoadData; 
var 
  I:          Integer; 
begin 
  MainForm.StatusBar.SimpleText := 'Loading...'; 
  ExcelDs.DisableControls; 
  try 
    ItemsTable.Open; 
    while not ItemsTable.EOF do 
    begin 
      ExcelDs.Insert; 
      for I := 0 to ItemsTable.Fields.Count - 1 do 
        ExcelDs.Fields[I].Assign(ItemsTable.Fields[I]); 
      ExcelDs.Post; 
      ItemsTable.Next; 
    end; //while 
  finally 
    ExcelDs.EnableControls; 
    MainForm.StatusBar.SimpleText := ''; 
  end; //try 
end; 
 
procedure TMainDm.RefreshExcelData; 
var 
  CurrentRec:          Integer; 
begin 
  CurrentRec := ExcelDs.RecNo; 
  ExcelDs.Close; 
  ExcelDs.Open; 
  ExcelDs.RecNo := CurrentRec; 
end; 
 
procedure TMainDm.ExcelDsAfterOpen(DataSet: TDataSet); 
var 
  I:           Integer; 
begin 
  for I := 0 to ExcelDs.Fields.Count - 1 do 
    ExcelDs.Fields[I].DisplayWidth := 15; 
end; 
 
end.