2,507
社区成员




procedure TForm1.teminate_excel;
var
lppe: TProcessEntry32;
found : boolean;
Hand : THandle;
hh:hwnd;
s:string;
begin
Hand := CreateToolhelp32Snapshot(TH32CS_SNAPALL,0);
found := Process32First(Hand,lppe);
while found do
begin
s:=strpas(lppe.szExeFile);
if uppercase(s)='EXCEL.EXE' then
begin
hh:=openprocess(PROCESS_ALL_ACCESS,true,lppe.th32ProcessID);
terminateprocess(hh,0); //中止进程
exit;
end;
found := Process32Next(Hand,lppe);
end;
end;
if self.OpenDialog1.Execute then
filename:=self.OpenDialog1.FileName;
if filename='' then
Exit;
//打开Excel报表
form1.teminate_excel;
try
Self.ExcelApplication1:=TExcelApplication.Create(Self);
Self.ExcelApplication1.Connect;
except
messagebox(application.Handle,'无法生成Excel报表,请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);
exit;
end;
Self.ExcelApplication1.Visible[0]:=False;
Self.ExcelApplication1.DisplayAlerts[0]:=False;//这句屏蔽提示对话框,可以实现保存时不出现是否覆盖的提示
self.ExcelApplication1.Workbooks.Open(filename,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,0);
self.ExcelWorkbook1.ConnectTo(Self.ExcelApplication1.Workbooks[1]);
self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);
self.ExcelWorksheet1.ConnectTo(Self.ExcelWorkbook1.Worksheets[1] as _worksheet);
self.ExcelWorksheet1.SaveAs('f:\统计报表.xls');//这句实现保存
procedure TCQ_PG_REPORT_F.ExportToExcel;
var
iLoop:Integer;
ExcelApp:Variant;
objServiceManager,objDesktop,oDocument,osheets,osheet:OleVariant;
isMsOffice:Boolean;
begin
if (Trim(cx_startdt.Text)='') and (Trim(cx_enddt.Text)='') then
begin
MessageDlg('提示:生成報表的開始日期和結束日期不能為空!',mtWarning,[mbOK],0);
Exit;
end;
if (Trim(cx_startdt.Text)<>'') and (Trim(cx_enddt.Text)='') then
begin
MessageDlg('提示:生成報表的結束日期不能為空!',mtWarning,[mbOK],0);
Exit;
end;
if (Trim(cx_startdt.Text)='') and (Trim(cx_enddt.Text)<>'') then
begin
MessageDlg('提示:生成報表的開始日期不能為空!',mtWarning,[mbOK],0);
Exit;
end;
try
Application.ProcessMessages;
ExcelApp:=CreateOleObject('Excel.Application');
isMsOffice:=True;
except
try
objServiceManager:=CreateOLEObject('com.sun.star.ServiceManager');
objDesktop:=objServiceManager.createInstance('com.sun.star.frame.Desktop');
Application.ProcessMessages;
oDocument:=objDesktop.loadComponentFromURL('private:factory/scalc','_blank',0,VarArrayOf([]));
oSheets:=oDocument.Sheets;
isMsOffice:=False;
except
MessageDlg('提示:對不起,你電腦上沒有安裝MicroSoftOffice Excel或OpenOffice Calc,所以不能導出報表!',mtWarning,[mbOK],0);
Exit;
end;
end;
if (Trim(cx_startdt.Text)<>'') and (Trim(cx_enddt.Text)<>'') then
begin
if isMsOffice then //MSOffices
begin
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].cells[1,8]:=cx_startdt.Text +'---'+cx_enddt.Text+'東莞昆盈電腦制品有限公司電子派工單效率報表';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.bold:=true;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.size:=14;
ExcelApp.Range['A1:J1'].MergeCells:=True;
ExcelApp.Range['A1:J1'].HorizontalAlignment:=xlCenter;
Application.ProcessMessages;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,1]:='序號';
ExcelApp.Range['A2:A2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,2]:='編製';
ExcelApp.Range['B2:B2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,3]:='人員';
ExcelApp.Range['C2:C2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,4]:='直接區分';
ExcelApp.Range['D2:D2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,5]:='姓名';
ExcelApp.Range['E2:E2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,6]:='工作地';
ExcelApp.Range['F2:F2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,7]:='時數(HR)';
ExcelApp.Range['G2:G2'].MergeCells:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,8]:='備註';
ExcelApp.Range['H2:H2'].MergeCells:=True;
Application.ProcessMessages;
qry_export.Close;
qry_export.SQL.Clear;
qry_export.SQL.Add('select *,(C.XB+C.XM)as WHO from(select max(a.CB)as CB,max(a.BM)as BM,max(a.BH)as BH,max(a.XM)as XM,max(a.DZHM)as DZHM,max(a.XB)as XB,max(a.CBZX)as CBZX,max(a.GZDD)as GZDD,b.XB as XBDD,max(a.ZJQF)as ZJQF,');
qry_export.SQL.Add('round(sum(datediff(mi,''00:00:00'',convert(varchar(20),a.GZSS))/60.0),1) as GZHR,a.REMARK from CQ_PG a left join RES_XB b on a.GZDD=b.DZHM where a.RQ between '''+ formatdatetime('yyyy/mm/dd',cx_startdt.Date)+''' and '''+ formatdatetime('yyyy/mm/dd',cx_enddt.Date)+''' ');
qry_export.SQL.Add('group by a.CB,a.BM,a.BH,a.XM,a.DZHM,a.XB,a.CBZX,a.GZDD,b.XB,a.ZJQF,a.REMARK )C Order by C.BM,C.XM asc ');
qry_export.Open;
ProgressBar1.Min:=0;
ProgressBar1.Max:=qry_export.RecordCount;
qry_export.First;
for iLoop:=0 to qry_export.RecordCount -1 do
begin
ExcelApp.cells[3+iLoop,1].value:=IntToStr(iLoop+1);
ExcelApp.cells[3+iLoop,2].value:=Trim(qry_export.FieldByName('XB').AsString);
ExcelApp.cells[3+iLoop,3].value:=Trim(qry_export.FieldByName('WHO').AsString);
ExcelApp.cells[3+iLoop,4].value:=Trim(qry_export.FieldByName('ZJQF').AsString);
ExcelApp.cells[3+iLoop,5].value:=Trim(qry_export.FieldByName('XM').AsString);
ExcelApp.cells[3+iLoop,6].value:=Trim(qry_export.FieldByName('XBDD').AsString);
ExcelApp.cells[3+iLoop,7].value:=Trim(qry_export.FieldByName('GZHR').AsString);
ExcelApp.cells[3+iLoop,8].value:=Trim(qry_export.FieldByName('REMARK').AsString);
ProgressBar1.Position:=ProgressBar1.Position+1;
Application.ProcessMessages;
qry_export.Next;
end;
//格式
ExcelApp.Range['A2:J'+inttostr(4+iLoop)].HorizontalAlignment:=xlCenter;
ExcelApp.Range['A2:J'+inttostr(4+iLoop)].Borders.linestyle:=xlContinuous;
//樞紐分析
ExcelApp.WorkBooks[1].WorkSheets[3].Activate;
ExcelApp.ActiveWorkbook.PivotCaches.Create(1,'電子派工單時數報表!R2C1:R1048576C7').CreatePivotTable('Sheet3!R3C1','樞紐分析');
ExcelApp.ActiveWorkbook.ShowPivotTableFieldList:=True;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('人員').Orientation:=xlRowField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('人員').Position:=1;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('工作地').Orientation:=xlColumnField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('工作地').Position:=1;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('時數(HR)').Orientation:=xlRowField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('時數(HR)').Position:=2;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').DisplayImmediateItems:=True;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').AddDataField(ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('時數(HR)'), '加總 - 時數(HR)',xlSum);
//在樞紐分析中插入列
ExcelApp.Columns['A:D'].Select;
ExcelApp.Selection.Insert(xlToRight);
//寫單元格
ExcelApp.WorkBooks[1].WorkSheets[3].name:='電子派工單樞紐分析匯總報表';
ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,1]:='編製';
ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,2]:='姓名';
ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,3]:='崗位時數';
ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,4]:='支援時數';
//套用公式
ExcelApp.cells[5,1].value:='=IF(CODE(E5)=46753,LEFT(E5,2),IF(CODE(E5)>46753,"",LEFT(E5,3)))';
ExcelApp.cells[5,2].value:='=IF(A5="",E5,IF(CODE(A5)=46753,MID(E5,3,4),MID(E5,4,4)))';
ExcelApp.CellDragAndDrop:=True;
//自動填充"編製"
ExcelApp.Range['A5'].Select;
ExcelApp.Selection.AutoFill(ExcelApp.Range['A5:A20000'],xlFillDefault);
//自動填充"姓名"
ExcelApp.Range['B5'].Select;
ExcelApp.Selection.AutoFill(ExcelApp.Range['B5:B20000'],xlFillDefault);
ProgressBar1.Position:=0;
ExcelApp.Visible:=True;
Screen.Cursor:=crDefault;
except
ExcelApp.ActiveWorkBook.Saved:=True;
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
end;
end;
end;