2,498
社区成员
发帖
与我相关
我的任务
分享
procedure TWKO_PD_REPORT_F.cx_tenOKClick(Sender: TObject);
var
ExcelApp:Variant;
iLoop,i:Integer;
begin
if CheckBox1.Checked then
begin
if (cx_kssj.Text='')or(cx_jssj.Text='') then
begin
MessageDlg('導出報表開始日期和結束日期不能為空!',mtWarning,[mbOK],0);
Exit;
end;
end
else
begin
if (cx_planer.Text='')or(cx_kssj.Text='')or(cx_jssj.Text='') then
begin
MessageDlg('導出報表條件不能為空!',mtWarning,[mbOK],0);
Exit;
end;
end;
try
Application.ProcessMessages;
ExcelApp:=CreateOleObject('Excel.Application');
except
MessageDlg('對不起,你電腦上沒有安裝Excel,請確認!',mtWarning,[mbOK],0);
Exit;
end;
try
ExcelApp.Caption:='十大機種總報表';
ExcelApp.Visible:=False;
ExcelApp.WorkBooks.Add;
ExcelApp.WorkBooks[1].WorkSheets[1].name:='十大機種總報表';
ExcelApp.WorkBooks[1].WorkSheets[1].Activate;
Screen.Cursor:=crSQLWait;
Application.ProcessMessages;
ExcelApp.cells.Interior.ColorIndex:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.name:='新細明體';
ExcelApp.WorkBooks[1].WorkSheets[1].cells.font.size:=9;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[4].ColumnWidth:=13.5;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[5].ColumnWidth:=17.38;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[9].ColumnWidth:=11.63;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.name:='標楷體';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8]:='昆盈十大機種總報表';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.size:=14;
ExcelApp.Range['A1:I1'].MergeCells:=True;
ExcelApp.Range['A1:I1'].HorizontalAlignment:=xlCenter;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,1]:='月份';
ExcelApp.Range['A2:A3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,2]:='父階料號';
ExcelApp.Range['B2:B3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,3]:='機種類別';
ExcelApp.Range['C2:C3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,4]:='機種';
ExcelApp.Range['D2:D3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,5]:='日期';
ExcelApp.Range['E2:E3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,6]:='總標準工時';
ExcelApp.Range['F2:F3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,7]:='總投入工時';
ExcelApp.Range['G2:G3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,8]:='總工單量';
ExcelApp.Range['H2:H3'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,9]:='總單位標準工時';
ExcelApp.Range['I2:I3'].MergeCells:=True;
Application.ProcessMessages;
for i:=0 to DaysBetween(cx_kssj.Date,cx_jssj.Date) do
begin
lab_age.Caption:='開始導出"'+ FormatDateTime('yyyy/mm/dd',IncDay(cx_kssj.Date,i))+'"的數據';
Application.ProcessMessages;
if CheckBox1.Checked then
begin
adoq_wko_pdreport.Close;
adoq_wko_pdreport.SQL.Text:='select * from RES_TENMAX_MODEL where dt between '''+ FormatDateTime('yyyy/mm/dd',cx_kssj.Date)+''' and '''+ FormatDateTime('yyyy/mm/dd',cx_jssj.Date)+''' Order by dt';
adoq_wko_pdreport.Open;
end
else
begin
adoq_wko_pdreport.Close;
adoq_wko_pdreport.SQL.Text:='select * from RES_TENMAX_MODEL where JGLB='''+ VarToStr(Trim(cx_planer.Text))+''' and dt between '''+ FormatDateTime('yyyy/mm/dd',cx_kssj.Date)+''' and '''+ FormatDateTime('yyyy/mm/dd',cx_jssj.Date)+''' Order by dt';
adoq_wko_pdreport.Open;
end;
pb1.Min:=0;
pb1.Max:=adoq_wko_pdreport.RecordCount;
Application.ProcessMessages;
adoq_wko_pdreport.First;
for iLoop:=0 to adoq_wko_pdreport.RecordCount -1 do
begin
ExcelApp.cells[4+iLoop,1].RowHeight:=14;
ExcelApp.cells[4+iLoop,1].value:=adoq_wko_pdreport.FieldByName('YF').AsString;
ExcelApp.cells[4+iLoop,2].value:=adoq_wko_pdreport.FieldByName('LH').AsString;
ExcelApp.cells[4+iLoop,3].value:=adoq_wko_pdreport.FieldByName('JGLB').AsString;
ExcelApp.cells[4+iLoop,4].value:=adoq_wko_pdreport.FieldByName('MODEL').AsString;
ExcelApp.cells[4+iLoop,5].value:=adoq_wko_pdreport.FieldByName('DT').AsString;
ExcelApp.cells[4+iLoop,6].value:=adoq_wko_pdreport.FieldByName('SUMBZGS').AsString;
ExcelApp.cells[4+iLoop,7].value:=adoq_wko_pdreport.FieldByName('SUMTRGS').AsString;
ExcelApp.cells[4+iLoop,8].value:=adoq_wko_pdreport.FieldByName('SUMQTY').AsString;
ExcelApp.cells[4+iLoop,9].value:=adoq_wko_pdreport.FieldByName('DWBZGS').AsString;
pb1.Position:=pb1.Position+1;
Application.ProcessMessages;
adoq_wko_pdreport.Next;
end;
end;
Application.ProcessMessages;
ExcelApp.Range['A1:I'+inttostr(ExcelApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].HorizontalAlignment:=xlCenter ;
ExcelApp.Range['A1:I'+inttostr(ExcelApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].Borders.linestyle:=xlContinuous;
Screen.Cursor:=crDefault;
pb1.Position:=0;
ModalResult:=mrOk;
ExcelApp.Visible:=True;
except
ExcelApp.ActiveWorkBook.Saved:=True;
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
var
i,j : integer;
exls,sheet,book: variant;
begin
try
exls:=CreateOleObject('Excel.application');
exls.WorkBooks.Add;
book := exls.workbooks[1];
sheet := exls.worksheets[1];
except
application.MessageBox('请先安装MICROSOFT EXCEL!','提示',mb_ok+mb_iconinformation);
exit;
end;
adoqueryx.SQL.Clear;
adoqueryx.SQL.Add('select * from tests');
adoqueryx.Open;
with adoqueryx do
begin
DisableControls;
for i:=1 to FieldCount do
begin
exls.WorkSheets[1].Cells[1,i].value:=Fields[i-1].DisplayName;
end;
first;
i:=2;
while not eof do
begin
for j:=1 to fieldcount do
begin
exls.WorkSheets[1].Cells[i,j]:=fields[j-1].AsString;
end;
next;
i:=i+1;
end;
EnableControls;
exls.WorkBooks[exls.WorkBooks.Count].SaveAS(ExtractFilePath(ParamStr(0))+'aaaa.xls');
exls.activeWorkBook.saved:=true;
exls.workbooks.close;
exls.quit;
end;
adoqueryx.Close;
end;