|
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: 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: 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):
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 ComObj, Variants, Excel97;
VAR my_ole_application: Variant;
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_workbook: Variant;
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_worksheet: Variant;
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:
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: 
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:
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:
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_application: variant;
const k_data_sheet_name= 'my_data';
procedure TForm1.generate_data_Click(Sender: TObject);
var l_c_ole_sheets: Variant;
l_c_ole_data_worksheet: Variant;
l_row: Integer;
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(Sender: TObject);
var l_c_ole_sheets: Variant;
l_c_ole_range: Variant;
l_c_ole_chart_worksheet: Variant;
l_c_ole_data_worksheet: Variant;
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:
3.5.3 - Le projet excel_graph Le projet de dessin figure dans le .ZIP p_40_excel_chart et dont voici une 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:
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(Sender: TObject);
// -- 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(Sender: TObject);
const k_row_count= 6;
k_column_count= 9;
var l_row_count, l_column_count: Integer;
l_v_data_array: Variant;
procedure initialize_variant_array;
// -- fill a Variant array with some data
var l_row, l_column: Integer;
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_row, l_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_count, k_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: 
|
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" :
- 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.
|
