essayer ce petit code.. implementation uses comobj,math; {$R *.dfm} //.... procedure TForm1.Button1Click(Sender: TObject); var NbRecords,NbChamps : Integer; OleApplication : Variant; OleWorkBook : Variant; I,Ex ,J ,colnbr : Integer; XlsFile,NomFichier: ShortString; begin OleApplication := CreateOleObject('Excel.Application'); try If OpenDialog1.Execute then begin XlsFile:= (OpenDialog1.FileName); OleWorkBook := OleApplication.Workbooks.Open( XlsFile); OleApplication.Visible := False; NbChamps := 0; While not(OleApplication.Cells[2,NbChamps+1].Formula = '') do Inc(NbChamps); colnbr:= NbChamps ; Ex := 0; For I:=1 to 1 do begin NbRecords := 0; While not(OleApplication.Cells[NbRecords+3,I].Formula = '') do begin Inc(NbRecords); end; NbRecords := Max(Ex,NbRecords); Ex := NbRecords; end; end; NbChamps:=0; For I:=6 to NbRecords+2 do for j:= 1 to colnbr do begin OleApplication.Cells[I,j].select; OleApplication.ActiveCell.Borders[7].LineStyle :=-4119; OleApplication.ActiveCell.Borders[8].LineStyle :=-4119; OleApplication.ActiveCell.Borders[9].LineStyle :=-4119; OleApplication.ActiveCell.Borders[10].LineStyle :=-4119; end; finally if savedialog1.Execute then begin NomFichier := ExtractFileName(SaveDialog1.FileName); OleApplication.ActiveWorkBook.SaveAs(ExtractFilePath(SaveDialog1.FileName)+NomFichier ); end; OleApplication.Quit; end; // end; end;
var NbRecords,NbChamps : Integer; OleApplication : Variant; OleWorkBook : Variant; I : Integer; XlsFile,NomFichier: ShortString; function ColExcel(Col : Integer) : ShortString; // fonction qui Permet de retrouver les colonnes AA .. ZZ sous excel begin If Col > 26 then Result := Chr(64 + Trunc(Col/26)) + Chr(64 + (Col - (26 * Trunc(Col/26)))) else Result := Chr(64 + Col); end; begin OleApplication := CreateOleObject('Excel.Application'); try If OpenDialog1.Execute then begin XlsFile:= (OpenDialog1.FileName); OleWorkBook := OleApplication.Workbooks.Open( XlsFile); OleApplication.Visible := False; NbChamps := 0; While not(OleApplication.Cells[7,NbChamps+1].Formula = '') do Inc(NbChamps); For I:=6 to 6 do begin NbRecords := 0; While not(OleApplication.Cells[NbRecords+9,I].Formula = '') do Inc(NbRecords);// end; end; OleApplication.Range['A6:'+ColExcel(NbChamps)+IntToStr(NbRecords+8)].Select; //pour OleApplication.selection.Borders.LineStyle:=-4119; //tu peut changer le style... for i:= 2 to NbChamps do begin // i caracter {2=B,3=c ..ect} OleApplication.cells[NbRecords+9,i].NumberFormat := '# ##0' + DECIMALSEPARATOR + '00'; // NumberFormat=format Cellule OleApplication.cells[NbRecords+9,i].formula:= '=sum('+ColExcel(i)+inttostr(6)+':'+ColExcel(i)+inttostr(NbRecords+8)+')'; end; //inc le NbRecords OleApplication.cells[NbRecords+10,2].formula:='WALIDLAM'; //pour ecrire sur celulle OleApplication.cells[NbRecords+10,2].select; //pour selectionner la celulle if savedialog1.Execute then begin NomFichier := ExtractFileName(SaveDialog1.FileName); try OleApplication.ActiveWorkBook.SaveAs(ExtractFilePath(SaveDialog1.FileName)+NomFichier ); except OleApplication.Quit; raise; end; end; OleApplication.Visible:=true; end; except OleApplication.Quit; raise; end;
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionvar NbRecords,NbChamps : Integer; OleApplication : Variant; OleWorkBook : Variant; I : Integer; XlsFile,NomFichier: ShortString; function ColExcel(Col : Integer) : ShortString; // fonction qui Permet de retrouver les colonnes AA .. ZZ sous excel begin If Col > 26 then Result := Chr(64 + Trunc(Col/26)) + Chr(64 + (Col - (26 * Trunc(Col/26)))) else Result := Chr(64 + Col); end; begin OleApplication := CreateOleObject('Excel.Application'); try If OpenDialog1.Execute then begin XlsFile:= (OpenDialog1.FileName); OleWorkBook := OleApplication.Workbooks.Open( XlsFile); OleApplication.Visible := False; NbChamps := 0; While not(OleApplication.Cells[2,NbChamps+1].Formula = '') do Inc(NbChamps); For I:=1 to 1 do begin NbRecords := 0; While not(OleApplication.Cells[NbRecords+3,I].Formula = '') do Inc(NbRecords); end; end; OleApplication.Range['A6:'+ColExcel(NbChamps+1-1)+IntToStr(NbRecords+2)].Select; OleApplication.selection.Borders.LineStyle:=-4119; //tu peut changer le style... for i:= 2 to NbChamps do begin // i caracter {2=B,3=c ..ect} OleApplication.cells[NbRecords+3,i].NumberFormat := '# ##0' + DECIMALSEPARATOR + '00'; // NumberFormat=format Cellule OleApplication.cells[NbRecords+3,i].formula:='=somme('+ColExcel(i+1-1)+inttostr(6)+':'+ColExcel(i+1-1)+inttostr(NbRecords+2)+')'; end; finally if savedialog1.Execute then begin NomFichier := ExtractFileName(SaveDialog1.FileName); OleApplication.ActiveWorkBook.SaveAs(ExtractFilePath(SaveDialog1.FileName)+NomFichier ); end; OleApplication.Quit; end;
OleApplication.cells[NbRecords+3,i].formula:='=somme('+ColExcel(i+1-1)+inttostr(6)+':'+ColExcel(i+1-1)+inttostr(NbRecords+2)+')';
OleApplication.cells[NbRecords+3,i].formula:= '= sum('+ColExcel(i)+inttostr(6)+':'+ColExcel(i)+inttostr(NbRecords+2)+')';
var NbRecords,NbChamps : Integer; OleApplication : Variant; OleWorkBook : Variant; I : Integer; XlsFile,NomFichier: ShortString; function ColExcel(Col : Integer) : ShortString; // fonction qui Permet de retrouver les colonnes AA .. ZZ sous excel begin If Col > 26 then Result := Chr(64 + Trunc(Col/26)) + Chr(64 + (Col - (26 * Trunc(Col/26)))) else Result := Chr(64 + Col); end; begin OleApplication := CreateOleObject('Excel.Application'); try If OpenDialog1.Execute then begin XlsFile:= (OpenDialog1.FileName); OleWorkBook := OleApplication.Workbooks.Open( XlsFile); OleApplication.Visible := False; NbChamps := 0; While not(OleApplication.Cells[2,NbChamps+1].Formula = '') do Inc(NbChamps); For I:=1 to 1 do begin NbRecords := 0; While not(OleApplication.Cells[NbRecords+3,I].Formula = '') do Inc(NbRecords);// end; end; OleApplication.Range['A6:'+ColExcel(NbChamps)+IntToStr(NbRecords+2)].Select; //pour sélectionner une plage de celulles OleApplication.selection.Borders.LineStyle:=-4119; //tu peut changer le style... for i:= 2 to NbChamps do begin // i caracter {2=B,3=c ..ect} OleApplication.cells[NbRecords+3,i].NumberFormat := '# ##0' + DECIMALSEPARATOR + '00'; // NumberFormat=format Cellule OleApplication.cells[NbRecords+3,i].formula:= '=sum('+ColExcel(i)+inttostr(6)+':'+ColExcel(i)+inttostr(NbRecords+2)+')'; end; if savedialog1.Execute then begin NomFichier := ExtractFileName(SaveDialog1.FileName); try OleApplication.ActiveWorkBook.SaveAs(ExtractFilePath(SaveDialog1.FileName)+NomFichier ); except OleApplication.Quit; raise; end; end; OleApplication.Visible:=true; end; except OleApplication.Quit; raise; end;