menu
  Home  ==>  articles  ==>  office_com_automation  ==>  excel_delphi   

Excel et Delphi - John COLIBRI.

  • résumé : manipuler Excel depuis Delphi: créer, sélectionner, remplir et sauvegarder une feuille de calcul, dessiner un graphique
  • mots clé : Excel - tExcelApplication - tExcelWorkBook - tExcelWorkSheet - tExcelChart
  • logiciel utilisé : Windows XP personnel, Delphi 6.0, Excel 97
  • matériel utilisé : Pentium 2.800 Mhz, 512 Meg de mémoire, 250 Giga disque dur
  • champ d'application : Delphi 1 à 2006 sur Windows
  • niveau : développeur Delphi
  • plan :


1 - Introduction

Excel s'étant imposé comme tableur, beaucoup de nos clients nous ont demandés de leur expliquer comment communiquer entre Delphi et Excel.

En tapant "Excel"+"Delphi" dans Google, vous trouverez déjà de nombreux articles. Voici notre présentation sur les communications Delphi Excel.




2 - Architecture Excel

2.1 - Architecture Excel

L'architecture Excel est la suivante:

image

Et:

  • chaque projet est constitué d'un ou plusieurs WorkBook. Chaque Workbook contient
    • les feuilles de calcul WorkSheet contenant
      • les données littérales (nombre, chaînes etc)
      • les formules de calcul (A1+B2)
      • des graphes
    • les feuilles de graphes
    • des paramètres pour
      • les macros (des mini programmes)
      • les paramètres de la page
      • les paramètres de sécurité (droits etc)
  • le projet peut utiliser
    • des templates, correspondant à un modèle de feuille de calcul
    • des Add-In qui contiennent des routines externes pour effectuer des calculs plus élaborés que ne le permettent les formules ou les macros


2.2 - Version Excel

Lorsque nous installons Delphi 6, l'Installateur nous donne le choix entre:
  • office 97
  • office 2000
Nous avons opté pour Office 97, et les exemples seront effectués avec Excel 97. Ils devraient ne pas être affectés de façon majeurs par la version d'Excel, mais n'ayant pas ces nouvelles versions, nous ne pouvons pas le certifier. Les lecteurs rencontrant des problèmes pourront nous les signaler en envoyant un mail à jcolibri@jcolibri.com, et nous mentionnerons ces problèmes dans la prochaine version de l'article.



2.3 - Communication Delphi / Excel

Depuis Delphi, nous pouvons communiquer avec Excel de plusieurs manières:
  • en utilisant un Variant
  • par le biais des composants Excel de l'onglet "serveur" de la Palette
  • en considérant Excel comme une source de données ADO
  • en mettant en oeuvre le protocole DDE (communication de données entre applications)
Nous présenterons surtout les deux premières solutions. Mentionnons aussi que nous avons installé les composants Office (97) lors de notre installation de Delphi 6. Si ce n'est pas le cas pour votre installation, ajoutez les à partir du CD Delphi



2.4 - Un Exemple Excel

Nous allons utiliser, pour afficher le contenu d'une feuille Excel, le petit exemple suivant (les ventes et les coûts du trimestre, avec calcul de marge):

image




Programmation Excel en Delphi

3.1 - Les traitements Delphi

Nous allons tout d'abord présenter les traitement Excel directs (sans utiliser les composants de la Palette), et en particulier, comment:
  • ouvrir et fermer Excel
  • créer un WorkBook, sélectionner une WorkSheet
  • analyser la structure d'un fichier .XSL donné
  • écrire des données dans une feuille
  • créer un graphe
Puis nous parlerons des composants Excel de la Palette Delphi



3.2 - Application, WorkBook, WorkSheet

3.2.1 - Ouvrir et Fermer Excel

Pour accéder à Excel, nous allons utiliser un variant Ole. Nous créons le variant en utilisant:

USES ComObjVariantsExcel97;

VAR my_ole_applicationVariant;

  my_ole_application:= CreateOleObject('Excel.Application');

Pour rendre Excel visible, nous basculons le booléen Visible

my_ole_application.Visible:= True;

Et pour fermer Excel:

my_ole_application.Quit;
my_ole_application:= Unassigned;

Notez que si des données ont été modifiées par Delphi, Excel ouvrira un dialogue demandant s'il faut sauvegarder ou non le résultat



3.2.2 - Créer un WorkBook

Nous pouvons créer un WorkBook en utilisant:

VAR my_ole_workbookVariant;
  my_ole_workbook:= my_ole_application.Workbooks.Add;

et Excel affichera un nouveau WorkBook avec 3 WorkSheets.

Pour accéder aux feuilles, nous pouvons utiliser:

  • soit la variable retournée par le constructeur, my_ole_worksheet
  • soit utiliser my_ole_application.WorkBooks[1].WorkSheet[1]
  • ou encore donner un nom et utiliser ce nom comme index
my_ole_workbook ...

my_ole_application.Workbooks[1] ...

my_ole_work_book.Name:= 'year_2007';
my_ole_application.Workbooks['year_2007'] ...



Et pour fermer les WorkBooks, nous utilisons

my_ole_application.Workbooks.Close;



3.2.3 - Les WorkSheets

Un niveau plus bas, nous accédons aux WorkSheets.

Nous avons vu qu'en créant un WorkBook, Excel nous fournit 3 WorkSheets par défaut. Nous pouvons aussi créer une WorkSheet dans son propre WorkBook en utilisant:

VAR my_worksheetVariant;
  my_worksheet:= my_ole_application.Workbooks.Add(xlWBATWorksheet);
  my_worksheet.Name:= 'accounting';

  my_ole_application.Workbooks[1].WorkSheets[1] ...
  my_ole_application.Workbooks[1].WorkSheets['accounting'] ...

Et pour sélectionner un WorkSheet, nous utilisons le verbe Activate():

my_ole_application.Workbooks[1].Sheets[2].Activate;



3.2.4 - Analyser un WorkBook

Pour examiner le contenu d'un WorkBook, nous parcourons simplement ses WorkSheets:

my_ole_application.ActiveWorkBook.SaveAs('c:\excel\budget.xsl');

Le chargement se fait naturellement par Open:

my_ole_application.Workbooks.open('c:\excel\sales.xsl');



3.2.5 - Sauvegarder le contenu

Pour sauvegarder un

my_ole_application.ActiveWorkBook.SaveAs('c:\excel\budget.xsl');



3.2.6 - Le projet excel_open_create

Nous avons regroupé ces premiers essais dans un projet qui figure dans le .ZIP p_10_excel_open_create et dont voici une image:

image



3.3 - Analyse de fichiers .XSL

Pour pouvoir analyser les fichiers .XSL nous avons créé une application séparée excel_open_analyze, dont voici une image:

image



3.4 - Modification d'une WorkSheet

Nous allons à présent modifier depuis Delphi le contenu d'une WorkSheet



3.4.1 - Accès aux cellules

Nous pouvons modifier la valeur d'une cellule en utilisant le tableau Cells[ligne, colonne] d'un WorkSheet. Soit:

my_ole_application.Workbooks[1].WorkSheets[1].Cells[2, 12].Value:= 123;



Pour accéder à une cellule, nous pouvons utiliser:

  • soit my_worksheet.Cells[ligne, colonne]
  • dans notre cas, si nous créons directement une WorkSheet, nous pouvons même utiliser:

    my_ole_application.Cells[2, 12]:= 123;

  • nous pouvons aussi utiliser my_application.Selection:

    my_ole_application.Cells[3, 7].Select;
    ...
    my_ole_application.Selection:= 912;

    ou my_application.ActiveCell au lieu d'indexer Cells



Au lieu de désigner la cellule par ses coordonnées, nous pouvons indiquer la plage par la propriété Range:

my_ole_application.Range[D18].Value:= 567;



3.4.2 - Ecriture de Formule

A chaque cellule nous pouvons attacher une formule de calcul. Pour cela, nous utilisons la propriété Formula de la cellule. Par exemple

my_ole_application.Cells[2, 15].Formula:= '=5+ 3* B4';
my_ole_application.Cells[5, 9].Formula:= '=R1C2+ $A$B * R[-2]C';



3.4.3 - Lecture de valeurs

Pour lire la valeur stockée dans une cellule, nous utilisons la propriété Value, et pour lire la formule, la propriété Formula

Caption:= my_ole_sheet.Cells[2, 15].Formula;
Caption:= my_ole_sheet.Cells[5, 9].Value;
Caption:= my_ole_sheet.Range['R3C4'].Value;



3.4.4 - Le projet excel_worksheet

Nous avons regroupé les modification / lectures de valeurs et formules dans un projet qui figure dans le .ZIP p_30_excel_worksheet et dont voici une image:

image



3.5 - Création de Graphique

3.5.1 - Création d'une ChartSheet

Pour créer une feuille de graphique, nous utilisons:

my_ole_chart_worksheet:= 
    my_ole_application.Workbooks[1].Sheets.Add(, , 1, xlChart);



3.5.2 - Affichage d'un graphique

Nous allons remplir une feuille de données en utilisant tableau Variant, puis nous afficherons ces données.

Voici le code pour remplir notre feuille de données:

var g_c_ole_applicationvariant;
const k_data_sheet_name'my_data';

procedure TForm1.generate_data_Click(SenderTObject);
  var l_c_ole_sheetsVariant;
      l_c_ole_data_worksheetVariant;
      l_rowInteger;
  begin
    l_c_ole_sheets:= g_c_ole_application.Workbooks[1].Sheets;

    l_c_ole_data_worksheet:= g_c_ole_application.Workbooks[1].Sheets[1];
    l_c_ole_data_worksheet.Name:= k_data_sheet_name;

    // -- create data for the graph in the data sheet
    for l_row:= 1 to 15 do
      l_c_ole_data_worksheet.Cells[l_row, 1]:= 100+ Random(100);
    for l_row:= 1 to 15 do
      l_c_ole_data_worksheet.Cells[l_row, 2]:= 80 + Random(6);
    for l_row:= 1 to 15 do
      l_c_ole_data_worksheet.Cells[l_row, 3]:= 90+ Random(12);
    for l_row:= 1 to 15 do
      l_c_ole_data_worksheet.Cells[l_row, 4]:= 50+ Random(4);
  end// generate_data_Click

Et voici le code qui créé le graphe et l'affiche:

const k_chart_sheet_name'my_chart';

procedure TForm1.chart_Click(SenderTObject);
  var l_c_ole_sheetsVariant;
      l_c_ole_rangeVariant;
      l_c_ole_chart_worksheetVariant;
      l_c_ole_data_worksheetVariant;
  begin
    l_c_ole_sheets:= g_c_ole_application.Workbooks[1].Sheets;

    l_c_ole_range:= l_c_ole_sheets.Item[k_data_sheet_name].Range['A1:D15'];

    // -- create the chart sheet
    l_c_ole_chart_worksheet:= 
        g_c_ole_application.Workbooks[1].Sheets.Add(, , 1, xlChart);
    l_c_ole_chart_worksheet.Name:= k_chart_sheet_name;

    // -- the graph type
    l_c_ole_sheets.Item[k_chart_sheet_name].ChartType:= xl3DAreaStacked;

    l_c_ole_sheets.Item[k_chart_sheet_name].SeriesCollection.Add(l_c_ole_range);
  end// chart_Click

Nous avons choisi ce type de graphe en consultant les styles offerts par le Wizzard de Excel:

image



3.5.3 - Le projet excel_graph

Le projet de dessin figure dans le .ZIP p_40_excel_chart et dont voici une image:

image



3.6 - Utilisation des Serveurs

3.6.1 - Principe

Au lieu d'utiliser Ole, nous pouvons utiliser les serveurs fournis avec Delphi:
  • le composant principal est le server tExcelApplication
  • nous pouvons ensuite "naviguer" à partir de ce composant (WorkBook, WorkSheet etc) ou bien utiliser les composants tExcelWorkBook et tExcelWorkSheet)
Voici ces composants:

image



3.6.2 - Exemple

A titre d'exemple, remplissons quelques cellules.
   posez un tExcelApplication sur la tForm et initialisez AutoConnect à True et ConnectKind à ckNewInstance
   posez un tExcelWorkBook et un tExcelWorkSheet sur la tForm
   posez un tButton, et dans son OnClick reliez les composants:

procedure TForm1.create_application_Click(SenderTObject);
    // -- Connect to the server TExcelApplication
  begin
    ExcelApplication1.Connect;
    g_visible:= Not g_visible;
    ExcelApplication1.Visible[0]:= g_visible;

    // -- add new workbook and connect WB wrapper
    ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks.Add(
        xlWBATWorksheet, 0));

    // -- connect to sheet 1
    ExcelWorkSheet1.ConnectTo(ExcelWorkBook1.Worksheets[1] 
        as _Worksheet);

    // -- select the first worksheet
    (ExcelWorkBook1.Worksheets[1] as _WorkSheet).Select(EmptyParam, 0);
  end// create_application_Click

   posez un tButton et remplissez quelques cellules:

procedure TForm1.add_data_Click(SenderTObject);
  const k_row_count= 6;
        k_column_count= 9;
  var l_row_countl_column_countInteger;
      l_v_data_arrayVariant;

  procedure initialize_variant_array;
      // -- fill a Variant array with some data
    var l_rowl_columnInteger;
    begin
      l_row_count:= k_row_count;
      l_column_count:= k_column_count;

      l_v_data_array := VarArrayCreate([0, l_row_count - 1, 
          0, l_column_count - 1], VarOleStr);

      for l_row:= 0 to l_row_count- 1 do
        for l_column:= 0 to l_column_count- 1 do
          l_v_data_array[l_rowl_column]:= 3+ Random(5);
    end// initialize_variant_array

  begin // add_data_Click
    initialize_variant_array;

    // -- Assign the Delphi Variant Matrix 
    ExcelWorkSheet1.Range['A1',
        ExcelWorkSheet1.Cells.Item[k_row_countk_column_count]].Value := l_v_data_array;

    // -- fiddle some properties
    ExcelWorkSheet1.Name := 'Customers';
    ExcelWorkSheet1.Columns.Font.Bold := True;
    ExcelWorkSheet1.Columns.HorizontalAlignment := xlRight;
    ExcelWorkSheet1.Columns.ColumnWidth := 4;

    // -- Customise the first entire Column
    ExcelWorkSheet1.Range['A1''A6'].Font.Color := clBlue;
    ExcelWorkSheet1.Range['A1''A6'].HorizontalAlignment := xlHAlignLeft;
    ExcelWorkSheet1.Range['A1''A6'].ColumnWidth := 7;

    // -- Unassign the Delphi Variant array
    l_v_data_array:= Unassigned;
  end// add_data_Click

   voici le résultat:

image




4 - Améliorations

Nous ne sommes pas des experts en Excel. Cela doit forcément se voir. Ce que nous savons néanmoins, c'est que
  • vous trouverez via Google de nombreux "Tips" qui vous indiquerons comment tirer un trait, changer le format d'affichage, etc
  • imprimer, insérer des macros, écrire des Add-Ins est un peu plus difficile à trouver, mais pas impossible
  • pour modifier, via Delphi, des propriétés plus cachées, il faut avoir pratiqué Excel. Le plus dur est de connaître le nom des verbes, leurs paramètres, et les constantes littérales à utiliser. Nous avons entendu parler d'un mode trace en Visual Basic qui permettrait d'explorer ce monde assez mal documenté au demeurant.



5 - Télécharger le code source Delphi

Vous pouvez télécharger:

Ce .ZIP qui comprend:

  • le .DPR, la forme principale, les formes annexes eventuelles
  • les fichiers de paramètres (le schéma et le batch de création)
  • dans chaque .ZIP, toutes les librairies nécessaires à chaque projet (chaque .ZIP est autonaume)
Ces .ZIP, pour les projets en Delphi 6, contiennent des chemins RELATIFS. Par conséquent:
  • créez un répertoire n'importe où sur votre machine
  • placez le .ZIP dans ce répertoire
  • dézippez et les sous-répertoires nécessaires seront créés
  • compilez et exécutez
Ces .ZIP ne modifient pas votre PC (pas de changement de la Base de Registre, de DLL ou autre). Pour supprimer le projet, effacez le répertoire.

La notation utilisée est la notation alsacienne qui consiste à préfixer les identificateurs par la zone de compilation: K_onstant, T_ype, G_lobal, L_ocal, P_arametre, F_unction, C_lasse. Elle est présentée plus en détail dans l'article La Notation Alsacienne



Comme d'habitude:

  • nous vous remercions de nous signaler toute erreur, inexactitude ou problème de téléchargement en envoyant un e-mail à jcolibri@jcolibri.com. Les corrections qui en résulteront pourront aider les prochains lecteurs
  • tous vos commentaires, remarques, questions, critiques, suggestion d'article, ou mentions d'autres sources sur le même sujet seront de même les bienvenus à jcolibri@jcolibri.com.
  • plus simplement, vous pouvez taper (anonymement ou en fournissant votre e-mail pour une réponse) vos commentaires ci-dessus et nous les envoyer en cliquant "envoyer" :
    Nom :
    E-mail :
    Commentaires * :
     

  • et si vous avez apprécié cet article, faites connaître notre site, ajoutez un lien dans vos listes de liens ou citez-nous dans vos blogs ou réponses sur les messageries. C'est très simple: plus nous aurons de visiteurs et de références Google, plus nous écrirons d'articles.



6 - Références




7 - L'auteur

John COLIBRI est passionné par le développement Delphi et les applications de Bases de Données. Il a écrit de nombreux livres et articles, et partage son temps entre le développement de projets (nouveaux projets, maintenance, audit, migration BDE, migration Xe_n, refactoring) pour ses clients, le conseil (composants, architecture, test) et la formation. Son site contient des articles avec code source, ainsi que le programme et le calendrier des stages de formation Delphi, base de données, programmation objet, Services Web, Tcp/Ip et UML qu'il anime personellement tous les mois, à Paris, en province ou sur site client.
Created: jan-04. Last updated: mar-2020 - 250 articles, 620 .ZIP sources, 3303 figures
Contact : John COLIBRI - Tel: 01.42.83.69.36 / 06.87.88.23.91 - email:jcolibri@jcolibri.com
Copyright © J.Colibri   http://www.jcolibri.com - 2001 - 2020
Retour:  Home  Articles  Formations  Développement Delphi  Livres  Pascalissime  Liens  Download
l'Institut Pascal

John COLIBRI

+ Home
  + articles_avec_sources
    + bases_de_donnees
    + web_internet_sockets
    + services_web_
    + prog_objet_composants
    + office_com_automation
      – excel_delphi
    + colibri_utilities
    + uml_design_patterns
    + graphique
    + delphi
    + outils
    + firemonkey
    + vcl_rtl
    + colibri_helpers
    + colibri_skelettons
    + admin
  + formations
  + developpement_delphi
  + présentations
  + pascalissime
  + livres
  + entre_nous
  – télécharger

contacts
plan_du_site
– chercher :

RSS feed  
Blog

Migration Delphi migration de versions Delphi, migration Unicode, migration BDE / base de données, migration Internet - Tél 01.42.83.69.36
Formation Delphi complete L'outil de développpement, le langage de programmation, les composants, les bases de données et la programmation Internet - 5 jours