Solved

Creating a spreadsheet from Delphi Program

Posted on 2006-11-08
6
426 Views
Last Modified: 2010-05-18
I need to pass some data from my Delophi program into an Excel sheet. The steps I need:
1) create a new spreadsheet
2) Give the spreadsheet a title
3) give names to the first five columns
4) insert data into the columns

some examples of how to do th above would be welcomed.

I use Delphi 7. My program runs under windows XP
0
Comment
Question by:YousefEisa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 250 total points
ID: 17903467
I have put together a sample/demo which can export any TDataset descendant to a spreadsheet. It illustrates the workings of all the topics you requested and more.

Kind regards
Pierre




PAS File:
===========================================================================
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, Grids, DBGrids, DBClient, StdCtrls, ExtCtrls, ComObj, XPMan;

const
  xlEdgeBottom = 9;
  xlEdgeLeft = 7;
  xlEdgeRight = 10;
  xlEdgeTop = 8;

  xlSolid = 1;

  xlThin = 2;
  xlThick = 4;

type
  TForm1 = class(TForm)
    ClientDataSet1: TClientDataSet;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    ClientDataSet1Col1: TStringField;
    ClientDataSet1Col2: TCurrencyField;
    ClientDataSet1col3: TDateField;
    Panel1: TPanel;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure ExportDatasetToExcel(ADataset: TDataset);
var XLApp, XLWorkbook, XLSht: Variant;
    i,r,c: integer;
    FieldColMap: TStringList;

  procedure ExpHeadings(ADataset: TDataset; var ARow, ACol: integer);
  var a, fi: integer;
  begin
    for fi:= 0 to ADataset.FieldCount-1 do
    begin
      XLSht.Cells.Item[ARow, ACol].Font.Bold:= True;
      XLSht.Cells.Item[ARow, ACol].value:= ADataset.Fields[fi].DisplayLabel;
      XLSht.Cells.Item[ARow, ACol].BorderAround(xlSolid, xlThin);
      FieldColMap.Add(ADataset.Fields[fi].DisplayName);
      Inc(ACol,1);
    end;
  end;

  procedure ExpData(ADataset: TDataset; var ARow, ACol: integer);
  var a, fi: integer;
  begin
    ADataset.First;
    while not ADataset.Eof do
    begin
      for fi:= 0 to ADataset.FieldCount-1 do
      begin
        ACol:= FieldColMap.IndexOf(ADataset.Fields[fi].FieldName)+1;
        if ACol > 0 then
        begin
          XLSht.Cells.Item[ARow, ACol].value:= ADataset.Fields[fi].DisplayText;
          XLSht.Cells.Item[ARow, ACol].Borders[xlEdgeLeft].LineStyle:= xlSolid;
          XLSht.Cells.Item[ARow, ACol].Borders[xlEdgeRight].LineStyle:= xlSolid;
        end;
      end;
      ADataset.Next;
      If (NOT ADataset.EOF)
        then Inc(ARow, 1);
    end;
  end;

begin
  XLApp:= CreateOleObject('excel.application');
  XLApp.visible:= true;
  XLWorkbook:= XLApp.Workbooks.Add;
  XLSht:= XLWorkBook.Sheets.Add;
  try //delete the default existing worksheets
    XLWorkBook.Sheets[2].Delete;
    XLWorkBook.Sheets[2].Delete;
    XLWorkBook.Sheets[2].Delete;
  except
  end;
  XLSht.Name:= 'ExportDemo';
  XLApp.ActiveWindow.DisplayGridlines := False;

  FieldColMap:= TStringList.Create;
  try
    XLSht.Cells.Item[1, 1].value:= 'Excel export example';
    XLSht.Cells.Item[2, 1].value:= 'Dataset: '+ADataset.Name;
    XLSht.Range['A1:A2'].Font.Bold:= True;

    c:= 1;
    r:= 4;
    ExpHeadings(ADataset, r, c);
    Inc(r,1);
    ExpData(ADataset, r, c);
    for c:= 1 to FieldColMap.Count do
    begin
      XLSht.Cells.Item[1, c].EntireColumn.AutoFit;
      XLSht.Cells.Item[r, c].Borders[xlEdgeBottom].LineStyle:= xlSolid;
    end;

  finally
    FieldColMap.Free;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ExportDatasetToExcel(ClientDataset1);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  ClientDataSet1.AppendRecord(['Test 1', 599.95, Date]);
  ClientDataSet1.AppendRecord(['Test 2', 200, Date]);
  ClientDataSet1.AppendRecord(['Test 3', 1000, Date]);
end;

end.



DFM File:
=======================================================================
object Form1: TForm1
  Left = 557
  Top = 124
  Width = 370
  Height = 285
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 0
    Top = 0
    Width = 362
    Height = 210
    Align = alClient
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Panel1: TPanel
    Left = 0
    Top = 210
    Width = 362
    Height = 41
    Align = alBottom
    TabOrder = 1
    object Button1: TButton
      Left = 8
      Top = 8
      Width = 97
      Height = 25
      Caption = '&Export to excel'
      TabOrder = 0
      OnClick = Button1Click
    end
  end
  object ClientDataSet1: TClientDataSet
    Active = True
    Aggregates = <>
    Params = <>
    Left = 112
    Top = 72
    Data = {
      610000009619E0BD010000001800000003000000000003000000610004436F6C
      310100490000000100055749445448020002001E0004436F6C32080004000000
      010007535542545950450200490006004D6F6E65790004636F6C330400060000
      0000000000}
    object ClientDataSet1Col1: TStringField
      FieldName = 'Col1'
      Size = 30
    end
    object ClientDataSet1Col2: TCurrencyField
      FieldName = 'Col2'
    end
    object ClientDataSet1col3: TDateField
      FieldName = 'col3'
    end
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 144
    Top = 72
  end
end

0
 
LVL 11

Expert Comment

by:calinutz
ID: 17916714
About automating Excel you will find a lot of usefull information here:

http://www.djpate.freeserve.co.uk/AutoExcl.htm


regards :)
0
 
LVL 11

Assisted Solution

by:calinutz
calinutz earned 250 total points
ID: 17916763
As for what I use when I am lazy ... this is it:


well... as it is obvious... q1 is a TQuery or TADOQuery containing the data we will send to Excel
and DBGrid1 is the grid holding the result of the q1 query. It could have been done in more ways but as I already said... it's for lazy people.
Of course do not forget to add ComObj to the Uses list


//Place this in the body of a buttonclick event:
var
 xls, wb, Range: OLEVariant;
 arrData: Variant;
 i,maxi:integer;
begin
maxi:=q1.RecordCount;
{create variant array where we'll copy our data}
 arrData := VarArrayCreate([1, q1.RecordCount+3, 1,5], varVariant);
     arrData[1, 1] :='First column';
     arrData[1, 2] :='Second column';
     arrData[1, 3] :='Third column';
     arrData[1, 4] :='Forth column';
     arrData[1, 5] :='Fifth column';

q1.FindFirst;
 for i := 1 to q1.RecordCount do
 begin
     arrData[i+1, 1] :=DBGrid1.Fields[0].AsString;
     arrData[i+1, 2] :=DBGrid1.Fields[1].AsString;
     arrData[i+1, 3] :=DBGrid1.Fields[2].AsString;
     arrData[i+1, 4] :=DBGrid1.Fields[3].AsString;
     arrData[i+1, 5] :=DBGrid1.Fields[4].AsString;

     Application.ProcessMessages;
     if i<>q1.RecordCount then q1.FindNext;
 end;

 xls := CreateOLEObject('Excel.Application');
 wb := xls.Workbooks.Add;
 Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],wb.WorkSheets[1].Cells[q1.RecordCount+1, 17]];
Range.Value := arrData;
 {show Excel with our data}
 xls.Visible := True;
end;




Regards
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: 17916804
As for giving a name to a Sheet just do this:
 wb.WorkSheets[1].Name:='Sheet Name';  // of course this would be after the line containing: "wb := xls.Workbooks.Add;"
0
 
LVL 11

Expert Comment

by:calinutz
ID: 17916851
About giving the excel file a name... why bother? On close of the excel file the user will be asked to save the file...

Anyway... check out this nice tutorial too about dealing with excel from delphi.


Cheers :-)
 
0
 

Author Comment

by:YousefEisa
ID: 17921363
Thanks guys for excellent solutions. difficult to choose between them. hope splitting equal points will be acceptable.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

688 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