unit Unit1;

interface
(*

Code for the article:

Accessing and managing MS Excel sheets with Delphi

http://delphi.about.com/library/weekly/aa090903a.htm

How to retrieve, display and edit Microsoft Excel spreadsheets
with ADO (dbGO) and Delphi. This step-by-step article describes
how to connect to Excel, retrieve sheet data, and enable editing
of data (using the DBGrid). You'll also find a list of most common
errors (and how to deal with them) that might pop up in the process.

*)


uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids, DB, ADODB, Buttons,
  ComCtrls, cxStyles, cxCustomData, cxGraphics, cxFilter, cxData, cxDataStorage,
  cxEdit, cxDBData, cxGridLevel, cxClasses, cxControls, cxGridCustomView,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid, cxPC;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    ADOQuery2: TADOQuery;
    cxPageControl1: TcxPageControl;
    cxTabSheet1: TcxTabSheet;
    cxTabSheet2: TcxTabSheet;
    cxGrid1: TcxGrid;
    cxGrid1DBTableView1: TcxGridDBTableView;
    cxGrid1Level1: TcxGridLevel;
    DBNavigator1: TDBNavigator;
    Edit3: TEdit;
    ListBox1: TListBox;
    ADOConnection1: TADOConnection;
    ADOConnection2: TADOConnection;
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    l1: TLabel;
    l2: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    BitBtn1: TBitBtn;
    ComboBox1: TComboBox;
    Edit4: TEdit;
    Panel2: TPanel;
    Button1: TButton;
    Button2: TButton;
    StatusBar1: TStatusBar;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    procedure ConnectToExcel;
    procedure FetchData;
    procedure GetFieldInfo;

    procedure DisplayException(Sender:TObject; E: Exception);
  public
    procedure WMDROPFILES(var msg : TWMDropFiles) ;
     message WM_DROPFILES;
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
{$R *.dfm}
uses typinfo,shellapi;


{ TForm1 }

procedure TForm1.ConnectToExcel;
var strConn :  widestring;
i:integer;
begin

i := Pos('xlsx',edit1.Text);
if i>1 then  strConn:='Provider=Microsoft.ACE.OLEDB.12.0;' +
           'Data Source=' + Edit1.Text + ';' +
           'Extended Properties="Excel 12.0;HDR=YES";'
else



//xlsx
//Provider=Microsoft.ACE.OLEDB.12.0;
//Data Source=c:\myFolder\myExcel2007file.xlsx;
//Extended Properties="Excel 12.0;HDR=YES";



  strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
           'Data Source=' + Edit1.Text + ';' +
           'Extended Properties=Excel 8.0;';

  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  try
    AdoConnection1.Open;
    AdoConnection1.GetTableNames(ComboBox1.Items,True);
  except
    ShowMessage('Unable to connect to Excel, make sure the workbook ' + Edit1.Text + ' exist!');
    raise;
  end;
end;(*ConnectToExcel*)

procedure TForm1.FetchData;
begin
  StatusBar1.SimpleText:='';

  if not AdoConnection1.Connected then ConnectToExcel;

  AdoQuery1.Close;

if combobox1.Text ='' then combobox1.ItemIndex:=0;
ShowMessage(Edit2.Text+l1.Caption+ComboBox1.Text+l2.Caption);
  AdoQuery1.SQL.Text:=Edit2.Text+l1.Caption+ComboBox1.Text+l2.Caption;
  try
    AdoQuery1.Open;
  except
    ShowMessage('Unable to read data from Excel, make sure the query ' + Edit1.Text + ' is meaningful!');
    raise;
  end;
end;

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  FetchData;

  GetFieldInfo;

         cxGrid1DBTableView1.BeginUpdate;
         cxGrid1DBTableView1.ClearItems;
          cxGrid1DBTableView1.DataController.CreateAllItems;
          cxGrid1DBTableView1.EndUpdate;

  cxGrid1DBTableView1.Columns[0].Summary.FooterKind:=skCount;

cxGrid1DBTableView1.ApplyBestFit();



  //grdCevapDBTableView2.BeginUpdate;
//grdCevapDBTableView2.ClearItems;
//fillGridView(grdCevapDBTableView2,command);
//grdCevapDBTableView2.DataController.CreateAllItems;
//grdCevapDBTableView2.EndUpdate;
end;

procedure TForm1.FormCreate(Sender: TObject);

begin
DragAcceptFiles( Handle, True ) ;
  AdoConnection1.LoginPrompt:=False;
  AdoQuery1.Connection:=AdoConnection1;
  DataSource1.DataSet:=AdoQuery1;

  DBNavigator1.DataSource:=DataSource1;

  Application.OnException:= DisplayException;



end;

procedure TForm1.DisplayException(Sender: TObject; E: Exception);
begin
  StatusBar1.SimpleText:=E.Message;

  edit3.Text:=E.Message;
end;

procedure TForm1.GetFieldInfo;
var
  i   : integer;
  ft     : TFieldType;
  sft    : string;
  fname  : string;
begin
  ListBox1.Clear;
  for i := 0 to AdoQuery1.Fields.Count - 1 do
  begin
    ft := AdoQuery1.Fields[i].DataType;
    sft := GetEnumName(TypeInfo(TFieldType), Integer(ft));
    fname:= AdoQuery1.Fields[i].FieldName;

    ListBox1.Items.Add(Format('%d) NAME: %s TYPE: %s',[1+i, fname, sft]));
  end;
end;


procedure TForm1.Button2Click(Sender: TObject);
var sAppend : string;
begin
  sAppend:='INSERT INTO [Sheet2$] IN "' + Edit1.Text + '" "Excel 8.0;" SELECT AuthorEmail, Title, Description FROM Articles';

  AdoQuery2.SQL.Text:=sAppend;
  AdoQuery2.ExecSQL;
end;

procedure TForm1.Button1Click(Sender: TObject);
var sCopy : string;
begin
  sCopy := 'SELECT * INTO ["Excel 8.0;Database=' + Edit1.Text + '"].[SheetAuthors] FROM Authors';

  AdoQuery2.SQL.Text:=sCopy;
  AdoQuery2.ExecSQL;
end;

 procedure TForm1.WMDROPFILES(var msg: TWMDropFiles) ;
 const
   MAXFILENAME = 255;
 var
   cnt, fileCount : integer;
   fileName : array [0..MAXFILENAME] of char;
 begin
   // how many files dropped?
   fileCount := DragQueryFile(msg.Drop, $FFFFFFFF, fileName, MAXFILENAME) ;

   // query for file names
   for cnt := 0 to -1 + fileCount do
   begin
     DragQueryFile(msg.Drop, cnt, fileName, MAXFILENAME) ;

     //do something with the file(s)
     Edit1.Text:=fileName;

   end;

   //release memory
   DragFinish(msg.Drop) ;
 end;

end.