Export sql database to Excel Delphi tutorial (Tip for 10 )

An example of exporting sql database table ( in this case sqlite table ) to MS Excel.

Link to full source : Source code

Here we use previous example which we upgrade with new function used for exporting data.

 As always , here is video tutorial to watch, following with full source of this example.

 

(code style formatted by http://hilite.me/ )

unit MainForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, ZAbstractRODataset, ZAbstractDataset,
  ZDataset, ZAbstractConnection, ZConnection, ComObj, ExcelXP, ComCtrls;

type
  TfrmMain = class(TForm)
    ZConn: TZConnection;
    ZQuery1: TZQuery;
    ZQuery2: TZQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    ComboBox1: TComboBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Edit1: TEdit;
    Label4: TLabel;
    Edit2: TEdit;
    Button3: TButton;
    Label5: TLabel;
    Edit3: TEdit;
    Label6: TLabel;
    Edit4: TEdit;
    Button4: TButton;
    Button5: TButton;
    Label7: TLabel;
    Label8: TLabel;
    Button6: TButton;
    ProgressBar1: TProgressBar;
    SaveDialog1: TSaveDialog;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);

    procedure ExportTableToExcel(Tablex:TZQuery;sFile:string);
    procedure FormCreate(Sender: TObject);
    procedure Button6Click(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmMain: TfrmMain;

implementation

{$R *.dfm}

procedure TfrmMain.ExportTableToExcel(Tablex:TZQuery;sFile:string);
var
//Declarations
 ExcelApplication : variant;
 Sheet : variant;
 column, row ,rowsno: integer;
 FormatFloatY:WideString;
 ValXX:WideString;
begin
Cursor:=crHourGlass;
 try   //Try to create Excel application
  begin
   ExcelApplication := CreateOleObject('Excel.Application');
   ExcelApplication.Visible := true;  //let's make visible
  end;
 except
 //If failed then show warning
  Showmessage('Cannot create an Excel file,'
  +'make sure that MS Excel is installed on your system');
  Application.Terminate;
 end;

 rowsno:=Tablex.RecordCount;   //records number in a table
 Tablex.RecNo:=1; //set table to first record
 ExcelApplication.WorkBooks.Add(-4167);   //Add excel workbook
 ExcelApplication.WorkBooks[1].WorkSheets[1].Name := 'my data';
 Sheet := ExcelApplication.WorkBooks[1].WorkSheets['my data'];

 //Format cells in excel sheet
 Sheet.Range['A1:C'+IntToStr(rowsno+1)].Borders.LineStyle := 7;
 Sheet.Range['A1:C'+IntToStr(rowsno+1)].Borders.color := clblue;

Sheet.Range['B2:B'+IntToStr(rowsno+1)].HorizontalAlignment :=xlLeft;
      //colors of cells in first line
Sheet.Cells[1,1].Interior.Color := clMoneyGreen;
Sheet.Cells[1,2].Interior.Color := clMoneyGreen;
Sheet.Cells[1,3].Interior.Color := clMoneyGreen;
                          //widths of columns
Sheet.Columns[1].ColumnWidth := 30;
Sheet.Columns[2].ColumnWidth := 30;
Sheet.Columns[3].ColumnWidth := 30;
            //captions/text of cells
Sheet.Cells[1,1] := 'ID';
Sheet.Cells[1,2] := 'NAME';
Sheet.Cells[1,3] := 'SURNAME';

   //set progressbar max value = teble records count
ProgressBar1.max:=Tablex.RecordCount;
ProgressBar1.position:=0;  //set position to 0=start
//now copy from table to excel cells
for row := 1 to Tablex.RecordCount do
begin
Sheet.Cells[row+1,1] := Tablex.Fields[0].AsString; //row+1 = move to the next line
//in column one
Sheet.Cells[row+1,2] :=Tablex.Fields[1].AsString;
//row+1 = move to the next line
//in column two
Sheet.Cells[row+1,3] := Tablex.Fields[2].AsString;
//row+1 = move to the next line
//in column three
ProgressBar1.position:=row;//set progress bar position
Tablex.Next;  //go to the next record in a table
end;
Screen.Cursor:=crDefault;  //set default cursor
ProgressBar1.position:=0; //it is done , set PB to the begining
DeleteFile(sFile);  //
Sheet.SaveAs(sFile);   //Save excel file

{
  ExcelApplication.Quit; //Quit excel
  ExcelApplication := Unassigned;  //to be sure that no hidden excel in memory
  Sheet := Unassigned;
}
    //let's close manually
Screen.Cursor:=crDefault;  //set default cursor

//let's test

//a bit slow while screen capturing
end;


procedure TfrmMain.FormCreate(Sender: TObject);
begin
SaveDialog1.InitialDir:=ExtractFilePath(Application.ExeName);
end;

procedure TfrmMain.Button1Click(Sender: TObject);
begin
ZConn.Protocol:='sqlite-3';
ZConn.LibraryLocation:=ExtractFilePath(Application.ExeName)+'sqlite3.dll';
if not FileExists(ZConn.LibraryLocation) then Exit;
ZConn.Database:=ExtractFilePath(Application.ExeName)+'testdb.s3db';
//if not FileExists(ZConn.Database) then Exit;
ZConn.Connect;
Label2.Caption:='testdb.s3db';
ComboBox1.Items.Clear;
ZConn.GetTableNames('',ComboBox1.Items);
ComboBox1.ItemIndex:=0;
ComboBox1.OnChange(Self);
end;

procedure TfrmMain.Button2Click(Sender: TObject);
begin
ZQuery1.Close;
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('CREATE TABLE if not exists testtbl(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,'
+'name VARCHAR(255),surname VARCHAR(255), UNIQUE(id))') ;
ZQuery1.ExecSQL;

ComboBox1.Items.Clear;
ZConn.GetTableNames('',ComboBox1.Items);
ComboBox1.ItemIndex:=0;
ComboBox1.OnChange(Self);
end;

procedure TfrmMain.Button3Click(Sender: TObject);
begin
if Edit1.Text<>'' then
begin
 ZQuery1.SQL.Clear;
 ZQuery1.SQL.Add('Insert into testtbl(name,surname) values('+QuotedStr(Edit1.Text)+','+QuotedStr(Edit2.Text)+')');
 ZQuery1.ExecSQL;
 ComboBox1.OnChange(Self);
end;
end;

procedure TfrmMain.Button4Click(Sender: TObject);
begin
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('Update testtbl set name='+QuotedStr(Edit3.Text)+',surname='+QuotedStr(Edit4.Text)
+' where id='+QuotedStr(Label8.Caption));
 ZQuery1.ExecSQL;
 ComboBox1.OnChange(Self);
end;

procedure TfrmMain.Button5Click(Sender: TObject);
begin
ZQuery2.Delete;
end;

procedure TfrmMain.Button6Click(Sender: TObject);
begin
if SaveDialog1.Execute() then
begin
ExportTableToExcel(ZQuery2,SaveDialog1.FileName);
end;
end;

procedure TfrmMain.ComboBox1Change(Sender: TObject);
var i :Integer;
begin
ZQuery2.Close;
ZQuery2.SQL.Clear;
ZQuery2.SQL.Add('Select * from '+ComboBox1.Text);
ZQuery2.Open;

for I := 0 to DBGrid1.Columns.Count-1 do
begin
 DBGrid1.Columns[i].Width:=100;
end;
end;

procedure TfrmMain.DBGrid1CellClick(Column: TColumn);
begin
Edit3.Text:=ZQuery2.FieldByName('name').AsString;
Edit4.Text:=ZQuery2.FieldByName('surname').AsString;
label8.Caption:=ZQuery2.FieldByName('id').AsString;
end;

end.