DBGridEh1: TDBGridEh;
ExcelBtn: TSpeedButton;
ExcelApplication1: TExcelApplication;
ExcelWorksheet1: TExcelWorksheet;
ExcelWorkbook1: TExcelWorkbook;
CAcc: TADOConnection;
qMain: TADOQuery;
procedure TTfrmBomCX.ExcelBtnClick(Sender: TObject);
var sCol,sRow:string;
i,j,k:integer;
WB:_WorkBook;
begin
try
with frmDm do
begin
ExcelApplication1.Connect;
ExcelApplication1.Visible[0]:=True;
WB:=ExcelApplication1.Workbooks.Add(NULL,0);
ExcelWorkbook1.ConnectTo(WB);
ExcelWorksheet1.ConnectTo(WB.Sheets[1] as _WorkSheet);
ExcelWorksheet1.Columns.VerticalAlignment:=xlcenter;
ExcelWorksheet1.Columns.HorizontalAlignment:=xlcenter;
i:=3;
ExcelWorksheet1.Cells.Font.Size:=‘12‘;
ExcelWorksheet1.Cells.ITEM[1,1].Font.fontstyle:=‘bold‘;
ExcelWorksheet1.Cells.ITEM[1,1].Font.Size:=‘16‘;
ExcelWorksheet1.Columns.Range[‘A1‘,‘M1‘].Merge(True);
ExcelWorksheet1.Cells.Item[1,1]:=qMain.fieldbyname(‘FModel‘).asstring+‘物料清单(K3 ERP)‘;
ExcelWorksheet1.Columns.Range[‘A2‘,‘M2‘].Merge(True);
ExcelWorksheet1.Cells.Item[2,1]:=
‘物料编码:‘+qMain.fieldbyname(‘FNumber‘).asstring+‘ ‘+
‘产品规格:‘+qMain.fieldbyname(‘Fmodel‘).asstring+‘ ‘+
‘名称:‘+qMain.fieldbyname(‘FName‘).asstring+‘ ‘+
‘版本:‘+qMain.fieldbyname(‘FVersion‘).asstring+ ‘ ‘+
‘打印日期:‘+formatdatetime(‘dddddd‘,now);
ExcelWorksheet1.cells.Item[1,1].HorizontalAlignment:=xlCenter;
ExcelWorksheet1.cells.Item[2,1].HorizontalAlignment:=xlCenter;
ExcelWorksheet1.Columns.Range[‘A3‘,‘M3‘].FONT.FONTSTYLE:=‘Bold‘;
ExcelWorksheet1.Columns.Range[‘A4‘,‘M4‘].FONT.FONTSTYLE:=‘Normal‘;
ExcelWorksheet1.Columns.Range[‘A3‘,‘M3‘].ColumnWidth:=20;
k:=65;
scol:=chr(K)+inttostr(1);
SRow:=chr(K)+inttostr(2);
ExcelWorksheet1.Columns.Range[scol,sRow].ColumnWidth:=4;
for j:=0 to dbgrideh1.Columns.Count-1 do
begin
k:=65;
scol:=chr(K+j+1)+inttostr(1);
SRow:=chr(K+j+1)+inttostr(2);
case j of
1:
begin
ExcelWorksheet1.Columns.Range[scol,sRow].ColumnWidth:=4;
end;
2,3,7,10,11:
begin
ExcelWorksheet1.Columns.Range[scol,sRow].ColumnWidth:=25;
end;
9:
begin
ExcelWorksheet1.Columns.Range[scol,sRow].ColumnWidth:=15;
end;
else
ExcelWorksheet1.Columns.Range[scol,sRow].ColumnWidth:=10;
end;
ExcelWorksheet1.Cells.Item[i,j+2]:=dbgrideh1.Columns.Items[j].Title.Caption;
end;
Try
qChd.First;
while not qChd.Eof do
begin
i:=i+1;
for j:=0 to dbgrideh1.Columns.Count-1 do
begin
ExcelWorksheet1.Cells.Item[i,1]:=i-3;
if (j=0) or (j=5) then
ExcelWorksheet1.Cells.Item[i,j+2]:=‘‘‘‘+dbgrideh1.Fields[j].asString
//{cxdbtv.Columns[j].DataBinding.Field.AsString};
else ExcelWorksheet1.Cells.Item[i,j+2]:=dbgrideh1.Fields[j].asString;
end;
qChd.next;
end;
i:=i+1;
ExcelWorksheet1.Columns.Range[‘A‘+inttostr(i),‘M‘+inttostr(i)].Merge(True);
ExcelWorksheet1.Cells.Item[i,1]:=‘测试物料‘;
qTst.First;
while not qTst.Eof do
begin
i:=i+1;
for j:=0 to dbgrideh2.Columns.Count-1 do
begin
ExcelWorksheet1.Cells.Item[i,1]:=i-3;
if (j=0) or (j=5) then
ExcelWorksheet1.Cells.Item[i,j+2]:=‘‘‘‘+dbgrideh2.Fields[j].asString
else ExcelWorksheet1.Cells.Item[i,j+2]:=dbgrideh2.Fields[j].asString;
end;
qTst.next;
end;
k:=dbgrideh1.Columns.Count;
//加边框架
ExcelWorksheet1.Columns.Range[‘A3‘,chr(65+k)+inttostr(i)].borders.linestyle:=1;
ExcelWorksheet1.Columns.Range[‘A2‘,‘A2‘].HorizontalAlignment:=XlLeft;
ExcelWorksheet1.Columns.Range[‘A4‘,‘M‘+inttostr(i)].HorizontalAlignment:=Xlleft;
except
end;
end;
finally
ExcelApplication1.Disconnect;
application.BringToFront;
showmessage(‘数据导出完成,请手工存储Excel数据‘);
end;
end;