1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
| unit ExportExcel;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, Grids, StrUtils, Registry;
procedure StringGrid_Excel(MonArray: array of TStringGrid);
Function ApplicationInstalled(StrSubKey: string; Cle: HKEY) : Boolean;
Const
xlGeneral = 1;
xlBottom = $FFFFEFF5;
xlContext = $FFFFEC76;
xlCenter = -4108;
xlRight = -4152;
xlDiagonalDown = 5;
xlDiagonalUp = 6;
xlEdgeLeft = 7;
xlEdgeTop = 8;
xlEdgeBottom = 9;
xlEdgeRight = 10;
xlContinuous = 1;
xlThin = 2;
xlMedium = $FFFFEFD6;
xlThick = 4;
xlDouble = $FFFFEFE9;
xlAutomatic = $FFFFEFF7;
xlInsideVertical = 11;
xlInsideHorizontal = 12;
xlNone = $FFFFEFD2;
xlUnderlineStyleNone = $FFFFEFD2;
xlSolid = 1;
xlR1C1 = $FFFFEFCA;
implementation
uses Unit1;
Function ApplicationInstalled(StrSubKey: string; Cle: HKEY) : Boolean;
var
Registre: TRegistry;
Begin
Registre := Nil;
Registre := TRegistry.Create; //Création de l'objet registre
Result := False;
Try
Try
Registre.RootKey := Cle; //Sélection de la clé principale
if Registre.KeyExists(StrSubKey) then Result := True;
Except
MessageDlg('Impossible de vérifier si Excel est installé sur ce poste.', mtError, [mbOk], 0);
end;
Finally
if assigned(Registre) then Registre.Free; //Libération de la base de registre. A faire absolument !
end;
end;
procedure StringGrid_Excel(MonArray: array of TStringGrid);
var
// Pour Excel
vMSExcel : variant; // Ouverture
vXLWorkbook, vXLWorkbooks : variant; // Classeur
aFeuille : AnsiString; // nom de la Feuille
vWorksheet : variant; // Feuille
// Pour le programme
i: Integer;
x,y: Integer;
t: Integer;
LigneDepart: integer;
Begin
// Initialisation
LigneDepart := 0;
// On test si excel est présent
if ApplicationInstalled('Excel.Application',HKEY_CLASSES_ROOT) then
Begin
// Ouverture Excel
vMSExcel := CreateOleObject('Excel.Application');
vMSExcel.Visible := False;
// Création d'un nouveau classeur
vXLWorkbooks := vMSExcel.Workbooks;
vXLWorkbook := vXLWorkbooks.Add;
// On travail sur la 1ère feuille de calcul de ce classeur
aFeuille := 'Feuil1';
vWorksheet := vXLWorkbook.WorkSheets[aFeuille];
For t := 0 to High(MonArray) do
Begin
LigneDepart := (MonArray[t].RowCount + 2) * t;
// Hauteur de la première ligne
vWorksheet.Cells.Item[1,1].EntireRow.RowHeight := MonArray[t].RowHeights[0];
{ Premier ligne, toutes les colonnes }
For i:= 0 to MonArray[t].ColCount - 1 do
Begin
// Taille des colonnes
vWorksheet.Cells.Item[1+ligneDepart,i+1].EntireColumn.ColumnWidth := MonArray[t].ColWidths[i] div 5;
// Valeur
vWorksheet.Cells.Item[1+ligneDepart,i+1].Value := MonArray[t].Cells[i,0];
// Bordures
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlDiagonalDown].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlDiagonalUp].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeLeft].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeLeft].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeLeft].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeTop].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeTop].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeTop].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeBottom].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeBottom].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeBottom].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeRight].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeRight].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[1+ligneDepart,i+1],vWorksheet.Cells[1+ligneDepart,i+1]].Borders[xlEdgeRight].ColorIndex := xlAutomatic;
end;
{ Toutes les lignes, première colonne }
For i:= 1 to MonArray[t].RowCount - 1 do
Begin
// Hauteur des lignes
vWorksheet.Cells.Item[i+1+ligneDepart,1].EntireRow.RowHeight := MonArray[t].RowHeights[i];
if i <> MonArray[t].RowCount - 1 then // Différent de la dernière ligne du StringGrid réservé au total
Begin
// Valeur
vWorksheet.Cells.Item[i+1+ligneDepart,1].Value := MonArray[t].Cells[0,i];
// Alignement des cellules
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].HorizontalAlignment := xlGeneral;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].VerticalAlignment := xlCenter;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].WrapText := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Orientation := 0;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].AddIndent := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].IndentLevel := 0;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].ShrinkToFit := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].ReadingOrder := xlContext;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].MergeCells := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].EntireRow.AutoFit;
end
else
Begin
// Valeur
vWorksheet.Cells.Item[i+1+ligneDepart,1].Value := 'TOTAL : ';
// Style & police de cellule
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Font.Bold := True;
// Alignement des cellules
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].HorizontalAlignment := xlRight;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].VerticalAlignment := xlCenter;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].WrapText := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Orientation := 0;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].AddIndent := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].IndentLevel := 0;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].ShrinkToFit := False;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].ReadingOrder := xlContext;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].MergeCells := False;
end;
// Bordures
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlDiagonalDown].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlDiagonalUp].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeLeft].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeLeft].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeLeft].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeTop].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeTop].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeTop].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeBottom].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeRight].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeRight].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[i+1+ligneDepart,1],vWorksheet.Cells[i+1+ligneDepart,1]].Borders[xlEdgeRight].ColorIndex := xlAutomatic;
end;
{ L'interrieur du tableau }
For x := 1 to MonArray[t].RowCount - 1 do
Begin
For y:= 1 to MonArray[t].ColCount - 1 do
Begin
if x <> MonArray[t].RowCount - 1 then // Si on est pas sur la dernière ligne 'Le TOTAL'
Begin
// Valeur
if MonArray[t].Cells[y,x ] = '' then vWorksheet.Cells.Item[x+1+ligneDepart,y+1].Value := 0
else vWorksheet.Cells.Item[x+1+ligneDepart,y+1].Value := AnsiReplaceStr(MonArray[t].Cells[y,x],',','.');
// Format avec décimal
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].NumberFormat := '0,00;;#';
// ou sans décimal
// vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].NumberFormat := '0;;#';
end
else
Begin
// Valeur
vWorksheet.Cells.Item[x+1+ligneDepart,y+1].Value := '=SUM(R[-'+IntToStr(MonArray[t].RowCount-2)+']C:R[-1]C)';
// Format avec décimal
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].NumberFormat := '0,00;;#';
// ou sans décimal
// vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].NumberFormat := '0;;#';
end;
// Forme et style et alignement
if x = MonArray[t].RowCount -1 then vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].Font.Bold := True;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].HorizontalAlignment := xlCenter;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].VerticalAlignment := xlCenter;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].WrapText := False;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].Orientation := 0;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].AddIndent := False;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].IndentLevel := 0;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].ShrinkToFit := False;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].ReadingOrder := xlContext;
vWorksheet.Range[vWorksheet.Cells[x+1+ligneDepart,y+1],vWorksheet.Cells[x+1+ligneDepart,y+1]].MergeCells := False;
end;
end;
// Bordures de la dernière ligne
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlDiagonalDown].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlDiagonalUp].LineStyle := xlNone;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeLeft].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeLeft].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeLeft].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeTop].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeTop].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeTop].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeBottom].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeBottom].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeBottom].ColorIndex := xlAutomatic;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeRight].LineStyle := xlContinuous;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeRight].Weight := xlMedium;
vWorksheet.Range[vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,1],vWorksheet.Cells[MonArray[t].RowCount+ligneDepart,MonArray[t].ColCount]].Borders[xlEdgeRight].ColorIndex := xlAutomatic;
// Ajustement de la ligne de titre des tableaux
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].HorizontalAlignment := xlCenter;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].VerticalAlignment := xlCenter;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].WrapText := True;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].Orientation := 0;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].AddIndent := False;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].IndentLevel := 0;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].ShrinkToFit := False;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].ReadingOrder := xlContext;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].MergeCells := False;
vWorksheet.Range[IntToStr(1+LigneDepart) + ':' + IntToStr(1+LigneDepart)].EntireRow.AutoFit;
end;
// Ajustement de la largeur de la premier colonne et Taille de la colonne 1
vWorksheet.Columns['A:A'].ColumnWidth := MonArray[0].ColWidths[0] div 5;
// On rend visible Excel maintenant
vMSExcel.Visible := true;
// Fermeture Excel // Activer Si vous le souhaiter
// Try
// vMSExcel.Quit;
// Except
// end;
vMSExcel := unassigned;
end
else
Begin
MessageDlg('Excel n''est pas installé sur ce poste.', mtError, [mbOk], 0);
end;
end;
end. |
Export Multi StringGrid vers Excel
Répondre avec citation
Partager