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
| //Exportation du contenu de StringGrid d'une liste de StringGrid vers Excel
function TFprincipal.StringGridToExcelSheet2(ListeGrid: array of TStringGrid; ListeGrid2: array of TStringGrid; SheetName, FileName: string;
ShowExcel: Boolean): Boolean;
const
xlWBATWorksheet = -4167;
var
SheetCount,SheetColCount,SheetColCount2,SheetRowCount,SheetRowCount2,BookCount: Integer;
XLApp, Sheet: OLEVariant;
ListeData,ListeData2: array of OLEVariant;
I, J, N, M, num_grid,num_data,indice_dep_ligne,indice_dep_colonne,total_colonne,total_ligne: Integer;
SaveFileName : String;
begin
num_grid:=1;
//calcule la quantité de feuilles nécessaires
SheetCount := (ListeGrid[num_grid].ColCount div 256) + 1;
if ListeGrid[num_grid].ColCount mod 256 = 0 then
SheetCount := SheetCount - 1;
//calcule la quantité de classeurs nécessaires
BookCount := (ListeGrid[num_grid].RowCount div 65536) + 1;
if ListeGrid[num_grid].RowCount mod 65536 = 0 then
BookCount := BookCount - 1;
//Creation d'Excel-OLE Object
Result := False;
XLApp := CreateOleObject('Excel.Application');
try
//pointe sur la feuille excel
if ShowExcel = false then
XLApp.Visible := False
else
XLApp.Visible := True;
//ajout Workbook
for M := 1 to BookCount do
begin
XLApp.Workbooks.Add(xlWBATWorksheet);
//place les feuilles
for N := 1 to SheetCount - 1 do
begin
XLApp.Worksheets.Add;
end;
end;
total_colonne:=0;
total_ligne:=0;
total_colonne:=total_colonne+ListeGrid[num_grid].ColCount+ListeGrid2[num_grid].ColCount;
if length(ListeGrid)=1 then
total_ligne:=ListeGrid[num_grid].RowCount;
if length(ListeGrid)>1 then
for num_grid:=1 to length(ListeGrid)-1 do
total_ligne:=total_ligne+ListeGrid[num_grid].RowCount;
//ajoute les colonnes
if total_colonne <= 256 then SheetColCount := total_colonne
else SheetColCount := 256;
//ajoute les lignes
if total_ligne <= 65536 then SheetRowCount := total_ligne
else SheetRowCount := 65536;
setlength(ListeData,length(ListeGrid));
setlength (ListeData2,length(ListeGrid2));
num_data:=1;
//remplissage de la feuille
for M := 1 to BookCount do
begin
for N := 1 to SheetCount do
begin
//va chercher les data
for num_grid:=1 to length(ListeGrid)-1 do
begin
ListeData[num_data] := VarArrayCreate([1, ListeGrid[num_grid].RowCount, 1, ListeGrid[num_grid].ColCount], varVariant);
ListeData2[num_data] := VarArrayCreate([1, ListeGrid2[num_grid].RowCount, 1, ListeGrid2[num_grid].ColCount], varVariant);
for I := 0 to ListeGrid[num_grid].ColCount - 1 do
for J := 0 to ListeGrid[num_grid].RowCount - 1 do
if ((I+256*(N-1)) <= ListeGrid[num_grid].ColCount) and ((J+65536*(M-1)) <= ListeGrid[num_grid].RowCount) then
ListeData[num_data][J + 1, I + 1] := ListeGrid[num_grid].Cells[I+256*(N-1), J+65536*(M-1)];
for I := 0 to ListeGrid2[num_grid].ColCount - 1 do
for J := 0 to ListeGrid2[num_grid].RowCount - 1 do
if ((I+256*(N-1)) <= ListeGrid2[num_grid].ColCount) and ((J+65536*(M-1)) <= ListeGrid2[num_grid].RowCount) then
ListeData2[num_data][J + 1, I + 1] := ListeGrid2[num_grid].Cells[I+256*(N-1), J+65536*(M-1)];
num_data:=num_data+1;
end;
XLApp.Worksheets[N].Select;
XLApp.Workbooks[M].Worksheets[N].Name := SheetName + IntToStr(N);
//formate les cellules en string
XLApp.Workbooks[M].Worksheets[N].Range[RefToCell(1, 1), RefToCell(SheetRowCount,
SheetColCount)].Select;
XLApp.Selection.NumberFormat := '@';
XLApp.Workbooks[M].Worksheets[N].Range['A1'].Select;
//remet les données en place
Sheet := XLApp.Workbooks[M].WorkSheets[N];
num_data:=1;
indice_dep_ligne:=2;
indice_dep_colonne:=1;
for num_grid:=1 to length(ListeGrid)-1 do
begin
Sheet.Range[RefToCell(indice_dep_ligne, 1), RefToCell(ListeGrid[num_grid].RowCount+indice_dep_colonne,ListeGrid[num_grid].ColCount)].Value := ListeData[num_data];
Sheet.Range[RefToCell(indice_dep_ligne, 6), RefToCell(ListeGrid2[num_grid].RowCount+indice_dep_colonne,ListeGrid2[num_grid].ColCount+5)].Value := ListeData2[num_data];
indice_dep_ligne:=indice_dep_ligne + ListeGrid[num_grid].RowCount+1;
indice_dep_colonne:=indice_dep_colonne+ListeGrid[num_grid].ColCount;
num_data:=num_data+1;
end;
end;
end;
//Enregistre le classeur excel
try
for M := 1 to BookCount do
begin
//rajoute le numéro du classeur dans le nom de fichier choisi
SaveFileName := Copy(FileName,1,Pos('.',FileName)-1) + IntToStr(M) +
Copy(FileName,Pos('.',FileName),
Length(FileName)-Pos('.',FileName)+1);
XLApp.Workbooks[M].SaveAs(SaveFileName);
end;
Result := True;
except
// Error?
end;
finally
//Excel termine
if (not VarIsEmpty(XLApp)) and (ShowExcel = false) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
XLAPP := Unassigned;
Sheet := Unassigned;
end;
end;
end; |
Partager