| Rolando Glez |
17-02-2011 20:12:00 |
Exportar datos a Exel
Estudie los siguiente procedimientos y adecuelo a su necesidad saludos
Primer procedimiento
Código:
procedure TForm_FICHAEDICIONART.makebookexcel(Sender:Tobject);
var
Excel, Libro: Variant;
i: integer;
Nombre: string;
code_exit:boolean;
hwnd:word;
namef,carpeta,FIELD_PRECIO,FIELD_IMP:STRING;
namefile:Pchar;
begin
i:=2;
with datamodule1.Adotable_articulos do
begin
if not eof then
begin
Excel:=CreateOleObject('Excel.Application');
Excel.Visible := True;
Excel.DisplayAlerts:=false;
Excel.WorkBooks.Add();
namefile:=pchar('"'+namef+'"');
//Comienzo a llenar las celdas
Excel.WorkBooks[1].WorkSheets[1].Name := 'Reporte';
Libro := Excel.WorkBooks[1].WorkSheets['Reporte'];
carpeta:='C:\archivos de programa\fcbc\contratos\EXPORT\XLS';
namef:=carpeta+'\'+'C'+copy(DataModule1.Adotable_CONTR_PROVEDORES.fieldbyname('contrato').asstring,1,7)+'-'+copy(DataModule1.Adotable_CONTR_PROVEDORES.fieldbyname('contrato').asstring,9,2)+'-F'+trim(nOFACT.caption)+'.xls';
if not( Directoryexists(carpeta)) then SYSTEM.mkdir(CARPETA);
if moneda.ItemIndex in[0,1] then
begin
if DBCHECKBOX1.Checked then BEGIN
FIELD_PRECIO:='PRECIO_FOB';
FIELD_IMP:='IMPORTEFOB';
libro.cells[1,1]:='CODIGO';
libro.cells[1,2]:='DESCRIPTOR';
libro.cells[1,3]:='CANTIDAD';
libro.cells[1,4]:=FIELD_PRECIO;
libro.cells[1,5]:=FIELD_IMP;
libro.cells[1,6]:='PRECIO CIF';
libro.cells[1,7]:='IMPORTE CIF';
libro.cells[1,8]:='PRECIO COMP';
libro.cells[1,9]:='PRECIO ADQ.';
libro.cells[1,10]:='PRECIO MIN';
libro.cells[1,11]:='PRECIO MAY';
END
ELSE
BEGIN
FIELD_PRECIO:='PRECIO_CIF';
FIELD_IMP:='IMPORTECIF';
libro.cells[1,1]:='CODIGO';
libro.cells[1,2]:='DESCRIPTOR';
libro.cells[1,3]:='CANTIDAD';
libro.cells[1,4]:=FIELD_PRECIO;
libro.cells[1,5]:=FIELD_IMP;
libro.cells[1,6]:='PRECIO COMP';
libro.cells[1,7]:='PRECIO ADQ.';
libro.cells[1,8]:='PRECIO MIN';
libro.cells[1,9]:='PRECIO MAY';
END;
end
else
begin
FIELD_PRECIO:='PRECIO_COM';
FIELD_IMP:='IMPORTEFOB';
libro.cells[1,1]:='CODIGO';
libro.cells[1,2]:='DESCRIPTOR';
libro.cells[1,3]:='CANTIDAD';
libro.cells[1,4]:=FIELD_PRECIO;
libro.cells[1,5]:='IMPORTE';
libro.cells[1,6]:='PRECI ADQ.';
libro.cells[1,7]:='PRECIO MIN';
libro.cells[1,8]:='PRECIO MAY';
end;
while not eof do
begin
if moneda.ItemIndex in[0,1] then
begin
if DBCHECKBOX1.Checked then begin
libro.cells[i,1]:=FIELDBYNAME('cod_artic').asstring;
libro.cells[i,2]:=fieldbyname('desc_prod').asstring;
libro.cells[i,3]:=fieldbyname('CANTIDAD').ASSTRING;
libro.cells[i,4]:=fieldbyname(FIELD_PRECIO).ASSTRINg;
libro.cells[i,5]:=fieldbyname(FIELD_IMP).ASSTRING;
libro.cells[i,6]:=fieldbyname('precio_cif').ASSTRING;
libro.cells[i,7]:=fieldbyname('importecif').ASSTRING;
libro.cells[i,8]:=fieldbyname('precio_com').ASSTRING;
libro.cells[i,9]:=fieldbyname('precio_ADQ').ASSTRING;
libro.cells[i,10]:=fieldbyname('precio_MIN').ASSTRING;
libro.cells[i,11]:=fieldbyname('precio_MAY').ASSTRING;
end
else begin
libro.cells[i,1]:=FIELDBYNAME('cod_artic').asstring;
libro.cells[i,2]:=fieldbyname('desc_prod').asstring;
libro.cells[i,3]:=fieldbyname('CANTIDAD').ASSTRING;
libro.cells[i,4]:=fieldbyname('PRECIO_CIF').ASSTRINg;
libro.cells[i,5]:=fieldbyname(FIELD_IMP).ASSTRING;
libro.cells[i,6]:=fieldbyname('precio_com').ASSTRING;
libro.cells[i,7]:=fieldbyname('precio_ADQ').ASSTRING;
libro.cells[i,8]:=fieldbyname('precio_MIN').ASSTRING;
libro.cells[i,9]:=fieldbyname('precio_MAY').ASSTRING;
end
end
else
begin
libro.cells[i,1]:=FIELDBYNAME('cod_artic').asstring;
libro.cells[i,2]:=fieldbyname('desc_prod').asstring;
libro.cells[i,3]:=fieldbyname('CANTIDAD').ASSTRING;
libro.cells[i,4]:=fieldbyname(FIELD_PRECIO).ASSTRINg;
libro.cells[i,5]:=fieldbyname(FIELD_IMP).ASSTRING;
libro.cells[i,6]:=fieldbyname('precio_ADQ').ASSTRING;
libro.cells[i,7]:=fieldbyname('precio_MIN').ASSTRING;
libro.cells[i,8]:=fieldbyname('precio_MAY').ASSTRING;
end;
inc(i);
next;
end;
if DBCHECKBOX1.Checked THEN
begin
libro.cells[i+1,2]:='TOTAL';
libro.cells[i+1,3]:='=SUMA(C2:C'+INTTOSTR(I-1)+')';
libro.cells[i+1,4]:='=SUMA(D2:D'+INTTOSTR(I-1)+')';
libro.cells[i+1,5]:='=SUMA(E2:E'+INTTOSTR(I-1)+')';
libro.cells[i+1,6]:='=SUMA(F2:F'+INTTOSTR(I-1)+')';
libro.cells[i+1,7]:='=SUMA(G2:G'+INTTOSTR(I-1)+')';
end
ELSE begin
libro.cells[i+1,2]:='TOTAL';
libro.cells[i+1,3]:='=SUMA(C2:C'+INTTOSTR(I-1)+')';
libro.cells[i+1,4]:='=SUMA(D2:D'+INTTOSTR(i-1)+')';
libro.cells[i+1,5]:='=SUMA(E2:E'+INTTOSTR(i-1)+')';
end;
first;
(*libro.directory:='C:\Archivos de programa\fcbc\export\xls';*)
{system.chdir('C:\Archivos de programa\fcbc\contrato\export\xls');}
libro.saveas(namef);
{Excel.SaveAs('PEPE','C:\Archivos de programa\fcbc\export\xls',Null,Null,false,false,xlNoChange,xlUserResolution,false,Null,Null,0);}
excel.quit;
if FileExists(excelpath+'\excel.exe') then
shellexecute(HWND,'Open',pchar(StrPas('"')+excelpath+ StrPas('\excel.exe"')),pchar(StrPas('"')+namef+StrPas('"')),'C:\Archivos de programa\fcbc\contrato\export\xls',sw_normal)
else
SHOWMESSAGE('No encontra el excel.exe en ruta '+excelpath)
end
else
showmessage('! Up No encontre registros ');
end
end;
segundo procedimiento
Código:
procedure Form_FICHAEDICIONART.execelsample(Sender:Tobject;campo:variant;longitud:integer);
var
RangeE: Excel2000.Range;
Excel,libro:variant;
ch,I, Row,columna: Integer;
Bookmark: TBookmarkStr;
namef,carpeta:string;
{[4,7,8,9,10,11,12,16,17,18]}
function findindexfields(i:integer):boolean;
var j:integer;
foundfield:boolean;
begin
j:=0;
foundfield:=false;
repeat;
if i=campo[j] then foundfield:=true;
inc(j);
until (foundfield) or (j>longitud);
result:=foundfield;
end;
begin
// create and show
Excel:=CreateOleObject('Excel.Application');
Excel.Visible := True;
Excel.DisplayAlerts:=false;
Excel.WorkBooks.Add();
Excel.WorkBooks[1].WorkSheets[1].Name := 'Reporte';
Libro := Excel.WorkBooks[1].WorkSheets['Reporte'];
carpeta:='C:\archivos de programa\fcbc\contratos\EXPORT\XLS';
namef:=carpeta+'\'+'C'+copy(DataModule1.Adotable_CONTR_PROVEDORES.fieldbyname('contrato').asstring,1,7)+'-'+copy(DataModule1.Adotable_CONTR_PROVEDORES.fieldbyname('contrato').asstring,9,2)+'-F'+trim(nOFACT.caption)+'.xls';
if not( Directoryexists(carpeta)) then SYSTEM.mkdir(CARPETA);
ExcelApplication1.Visible [0] := True;
ExcelApplication1.Workbooks.Add (NULL, 0);
// fill is the first row with field titles
RangeE := ExcelApplication1.ActiveCell;
columna:=1;
for I := 0 to datamodule1.Adotable_articulos.Fields.Count - 1 do
begin
if findindexfields(i) then
begin
if TRIM(datamodule1.Adotable_articulos.Fields [i].DisplayLabel)='CUC' then datamodule1.Adotable_articulos.Fields [i].DisplayLabel:='IMPORTE ADQ.';
RangeE.Value := datamodule1.Adotable_articulos.Fields [i].DisplayLabel;
libro.cells[1,columna]:=datamodule1.Adotable_articulos.Fields [i].DisplayLabel;
inc(columna);
RangeE := RangeE.Next;
end
end;
columna:=1;
// add field data in following rows
datamodule1.Adotable_articulos.DisableControls;
try
Bookmark := Datamodule1.ADOTable_articulos.Bookmark;
try
Row := 2;
Datamodule1.ADOTable_articulos.First;
while not datamodule1.Adotable_articulos.EOF do begin
columna:=1;
RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),'A' + IntToStr (Row)];
for I :=0 to datamodule1.Adotable_articulos.Fields.Count - 1 do begin
if findindexfields(i) then begin
RangeE.Value := datamodule1.AdoTable_articulos.Fields [i].AsString;
RangeE := RangeE.Next;
libro.cells[row,columna]:=datamodule1.AdoTable_articulos.Fields [i].AsString;
inc(columna);
end;
end;
datamodule1.Adotable_articulos.Next;
Inc (Row);
end
finally
datamodule1.Adotable_articulos.Bookmark := Bookmark;
end;
finally
datamodule1.Adotable_articulos.EnableControls;
end;
inc(row);
// format the section
RangeE := ExcelApplication1.Range ['B'+IntToStr (Row ), 'B' + IntToStr (Row )];
RangeE.Value :='Total';;
ch:=67;
if MONEDA.ItemIndex IN[0,1] then
BEGIN
if dbcheckbox1.checked then begin
for i:=2 to 11 do
begin
RangeE := ExcelApplication1.Range [CHR(CH)+IntToStr (Row ), CHR(CH) + IntToStr (Row )];
RangeE.Value :='=SUMA('+CHR(CH)+'2:'+CHR(CH)+inttostr(row-2)+')';
RangeE.Calculate;
libro.cells[row,i+1]:='=SUMA('+CHR(CH)+'2:'+CHR(CH)+inttostr(row-2)+')';
inc(ch);
{RangeE := RangeE.Next;}
end;
end
else begin
for i:=2 to 10 do
begin
if CHR(CH)<>'F' then
Begin
RangeE := ExcelApplication1.Range [CHR(CH)+IntToStr (Row ), CHR(CH) + IntToStr (Row )];
RangeE.Value :='=SUMA('+CHR(CH)+'2:'+CHR(CH)+inttostr(row-2)+')';
libro.cells[row,i+1]:='=SUMA('+CHR(CH)+'2:'+CHR(CH)+inttostr(row-2)+')';
end;
inc(ch);
{RangeE := RangeE.Next;}
end;
END;
end
else
begin
for i:=2 to 6 do
begin
RangeE := ExcelApplication1.Range [CHR(CH)+IntToStr (Row ), CHR(CH) + IntToStr (Row )];
RangeE.Value :='=SUMA('+CHR(CH)+'2:'+CHR(CH)+inttostr(row-2)+')';
libro.cells[row,i+1]:='=SUMA('+CHR(CH)+'2:'+CHR(CH)+inttostr(row-2)+')';
inc(ch);
end;
{RangeE := RangeE.Next;}
end;
if MONEDA.ItemIndex IN[0,1] then
BEGIN
if dbcheckbox1.checked then RangeE := ExcelApplication1.Range ['A1', 'L' + IntToStr (Row - 2)]
else RangeE := ExcelApplication1.Range ['A1', 'K' + IntToStr (Row - 2)];
end
else
begin
RangeE := ExcelApplication1.Range ['A1', 'L' + IntToStr (Row - 2)]
end;
RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
libro.saveas(namef);
RangeE.Calculate
{libro:=ExcelApplication1.Range ['A1', 'K' + IntToStr (Row - 1)];
libro.autoformat(3, NULL, NULL, NULL, NULL, NULL, NULL)}
end;
|
