8 个解决方案
#1
你可以导出到不同的 sheet,如 sheet[0],sheet[1],sheet[2],sheet[3] 等
#2
我就是这个意思。但是不知道如何实现。
#3
#4
没有用过,请指点一下。
#5
或是用什么控件能实现也行。
#6
用循環語句來處理多個sheet。。。
#7
eg:
try
if lst_n.Items.Count>0 then
begin
sWhere:=' and CBZX in (';
for iLoop:=0 to lst_n.Items.Count-1 do
sWhere:=sWhere+''''+lst_n.Items[iloop]+''',';
sWhere:=Copy(sWhere,0,Length(sWhere)-1)+')';
end;
Progressbar1.Min:=0;
Progressbar1.Position:=0;
ExcelApp.WorkBooks[1].WorkSheets[1].name:='FPY總表';
ExcelApp.WorkBooks[1].WorkSheets[2].name:='詳細資料';
Screen.Cursor:=crSQLWait;
ExcelApp.cells.Interior.ColorIndex:=2;
ExcelApp.WorkBooks[1].WorkSheets[2].Activate;
ExcelApp.WorkBooks[1].WorkSheets[2].cells.font.name:= '新細明體';
ExcelApp.WorkBooks[1].WorkSheets[2].cells.font.size:=10;
for iloop:=1 to 8 do
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,iloop].font.Bold:=true;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,1]:='料號';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,2]:='機種';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,3]:='工單號';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,4]:='工序';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,5]:='B';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,6]:='產出量';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,7]:='不良數';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,8]:='良率';
//導出基本數據
qry_Temp.Close;
qry_Temp.SQL.Text:='select lh,jz,wo,gx,sum(ccl)as ccl,sum(sxl)as bls from sc_xlrbb where CCL>0 '+ sWhere+' group by lh,jz,wo,gx';
qry_Temp.Open;
Progressbar1.Max:=qry_Temp.RecordCount;
lbl_Warning.Caption:='正在導出直通良率明細資料...';
Application.ProcessMessages;
for iLoop:=2 to qry_Temp.RecordCount+1 do
begin
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,1]:=qry_Temp.FieldByName('lh').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,2]:=qry_Temp.FieldByName('jz').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,3]:=qry_Temp.FieldByName('wo').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,4]:=qry_Temp.FieldByName('gx').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,5]:='=CONCATENATE(LEFT(A'+inttostr(iLoop)+',2),D'+inttostr(iLoop)+')';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,6]:=qry_Temp.FieldByName('ccl').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,7]:=qry_Temp.FieldByName('bls').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,8]:='=1-G'+inttostr(iLoop)+'/F'+inttostr(iLoop);
qry_Temp.Next;
Progressbar1.Position:=Progressbar1.Position+1;
Application.ProcessMessages;
end;
ExcelApp.WorkBooks[1].WorkSheets[2].range['H:H'].numberformatlocal:='0.0%';
ExcelApp.WorkBooks[1].WorkSheets[2].Activate;
ExcelApp.Range['A1:H'+inttostr(ExcelApp.WorkBooks[1].WorkSheets[2].usedrange.rows.count)].HorizontalAlignment:=xlCenter ;
ExcelApp.Range['A1:H'+inttostr(ExcelApp.WorkBooks[1].WorkSheets[2].usedrange.rows.count)].Borders.linestyle:=xlContinuous;
ExcelApp.WorkBooks[1].WorkSheets[2].range['B:B'].HorizontalAlignment:=xlLeft;
//生成分析表
lbl_Warning.Caption:='正在生成樞紐分析表...';
Application.ProcessMessages;
ExcelApp.WorkBooks[1].WorkSheets[3].Activate;
ExcelApp.ActiveWorkbook.PivotCaches.Create(1,'詳細資料!R1C1:R1048576C8').CreatePivotTable('Sheet3!R3C1','樞紐分析');
ExcelApp.ActiveWorkbook.ShowPivotTableFieldList := True;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('機種').Orientation:=xlRowField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('機種').Position:=1;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('B').Orientation:=xlColumnField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('B').Position:=1;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('良率').Orientation:=xlRowField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('良率').Position:=2;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').DisplayImmediateItems :=True;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').AddDataField(ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('良率'), '平均值-良率',xlAverage);
ExcelApp.ActiveSheet.range['A4:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count-2)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)].numberformatlocal:='0%';
ExcelApp.ActiveSheet.range['A4:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count-2)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)].Copy;
ExcelApp.WorkBooks[1].WorkSheets[1].Activate;
ExcelApp.ActiveSheet.Paste;
ExcelApp.ActiveSheet.Columns['A:A'].ColumnWidth:=24;
ExcelApp.ActiveSheet.cells[1,1]:='機種';
ExcelApp.ActiveSheet.cells[1,ExcelApp.ActiveSheet.usedrange.Columns.count+1]:='FPY';
str:='=';
for iloop:=2 to ExcelApp.ActiveSheet.usedrange.Columns.count-1 do
str:=str+'IF('+inttoz(iloop)+'2>0,'+inttoz(iloop)+'2,1)*';
str:=copy(str,0,length(str)-1); //公式
ExcelApp.ActiveSheet.cells[2,ExcelApp.ActiveSheet.usedrange.Columns.count]:=str;
str:='';
ExcelApp.ActiveSheet.range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+'2'].AutoFill(ExcelApp.ActiveSheet.range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+'2:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)],xlFillDefault);
ExcelApp.ActiveSheet.range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+':'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)].numberformatlocal:='0%';
ExcelApp.WorkBooks[1].WorkSheets[1].Cells.Select;
ExcelApp.Selection.Sort(Key1:=ExcelApp.Range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+'2:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)],
Order1:=xlAscending,Header:=xlGuess,OrderCustom:=1,MatchCase:=False, Orientation:=xlTopToBottom);
ExcelApp.Range['B1:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].HorizontalAlignment:=xlCenter ;
ExcelApp.Range['A1:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].Borders.linestyle:=xlContinuous;
finally
Screen.Cursor:=crDefault;
end;
lbl_Warning.Caption:='直通良率導出成功!';
ExcelApp.Visible:=true;
#8
这个方法我也想到只是一下子不知道如何处理,
#1
你可以导出到不同的 sheet,如 sheet[0],sheet[1],sheet[2],sheet[3] 等
#2
我就是这个意思。但是不知道如何实现。
#3
#4
没有用过,请指点一下。
#5
或是用什么控件能实现也行。
#6
用循環語句來處理多個sheet。。。
#7
eg:
try
if lst_n.Items.Count>0 then
begin
sWhere:=' and CBZX in (';
for iLoop:=0 to lst_n.Items.Count-1 do
sWhere:=sWhere+''''+lst_n.Items[iloop]+''',';
sWhere:=Copy(sWhere,0,Length(sWhere)-1)+')';
end;
Progressbar1.Min:=0;
Progressbar1.Position:=0;
ExcelApp.WorkBooks[1].WorkSheets[1].name:='FPY總表';
ExcelApp.WorkBooks[1].WorkSheets[2].name:='詳細資料';
Screen.Cursor:=crSQLWait;
ExcelApp.cells.Interior.ColorIndex:=2;
ExcelApp.WorkBooks[1].WorkSheets[2].Activate;
ExcelApp.WorkBooks[1].WorkSheets[2].cells.font.name:= '新細明體';
ExcelApp.WorkBooks[1].WorkSheets[2].cells.font.size:=10;
for iloop:=1 to 8 do
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,iloop].font.Bold:=true;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,1]:='料號';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,2]:='機種';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,3]:='工單號';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,4]:='工序';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,5]:='B';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,6]:='產出量';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,7]:='不良數';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[1,8]:='良率';
//導出基本數據
qry_Temp.Close;
qry_Temp.SQL.Text:='select lh,jz,wo,gx,sum(ccl)as ccl,sum(sxl)as bls from sc_xlrbb where CCL>0 '+ sWhere+' group by lh,jz,wo,gx';
qry_Temp.Open;
Progressbar1.Max:=qry_Temp.RecordCount;
lbl_Warning.Caption:='正在導出直通良率明細資料...';
Application.ProcessMessages;
for iLoop:=2 to qry_Temp.RecordCount+1 do
begin
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,1]:=qry_Temp.FieldByName('lh').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,2]:=qry_Temp.FieldByName('jz').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,3]:=qry_Temp.FieldByName('wo').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,4]:=qry_Temp.FieldByName('gx').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,5]:='=CONCATENATE(LEFT(A'+inttostr(iLoop)+',2),D'+inttostr(iLoop)+')';
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,6]:=qry_Temp.FieldByName('ccl').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,7]:=qry_Temp.FieldByName('bls').AsString;
ExcelApp.WorkBooks[1].WorkSheets[2].cells[iloop,8]:='=1-G'+inttostr(iLoop)+'/F'+inttostr(iLoop);
qry_Temp.Next;
Progressbar1.Position:=Progressbar1.Position+1;
Application.ProcessMessages;
end;
ExcelApp.WorkBooks[1].WorkSheets[2].range['H:H'].numberformatlocal:='0.0%';
ExcelApp.WorkBooks[1].WorkSheets[2].Activate;
ExcelApp.Range['A1:H'+inttostr(ExcelApp.WorkBooks[1].WorkSheets[2].usedrange.rows.count)].HorizontalAlignment:=xlCenter ;
ExcelApp.Range['A1:H'+inttostr(ExcelApp.WorkBooks[1].WorkSheets[2].usedrange.rows.count)].Borders.linestyle:=xlContinuous;
ExcelApp.WorkBooks[1].WorkSheets[2].range['B:B'].HorizontalAlignment:=xlLeft;
//生成分析表
lbl_Warning.Caption:='正在生成樞紐分析表...';
Application.ProcessMessages;
ExcelApp.WorkBooks[1].WorkSheets[3].Activate;
ExcelApp.ActiveWorkbook.PivotCaches.Create(1,'詳細資料!R1C1:R1048576C8').CreatePivotTable('Sheet3!R3C1','樞紐分析');
ExcelApp.ActiveWorkbook.ShowPivotTableFieldList := True;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('機種').Orientation:=xlRowField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('機種').Position:=1;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('B').Orientation:=xlColumnField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('B').Position:=1;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('良率').Orientation:=xlRowField;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('良率').Position:=2;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').DisplayImmediateItems :=True;
ExcelApp.ActiveSheet.PivotTables('樞紐分析').AddDataField(ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('良率'), '平均值-良率',xlAverage);
ExcelApp.ActiveSheet.range['A4:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count-2)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)].numberformatlocal:='0%';
ExcelApp.ActiveSheet.range['A4:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count-2)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)].Copy;
ExcelApp.WorkBooks[1].WorkSheets[1].Activate;
ExcelApp.ActiveSheet.Paste;
ExcelApp.ActiveSheet.Columns['A:A'].ColumnWidth:=24;
ExcelApp.ActiveSheet.cells[1,1]:='機種';
ExcelApp.ActiveSheet.cells[1,ExcelApp.ActiveSheet.usedrange.Columns.count+1]:='FPY';
str:='=';
for iloop:=2 to ExcelApp.ActiveSheet.usedrange.Columns.count-1 do
str:=str+'IF('+inttoz(iloop)+'2>0,'+inttoz(iloop)+'2,1)*';
str:=copy(str,0,length(str)-1); //公式
ExcelApp.ActiveSheet.cells[2,ExcelApp.ActiveSheet.usedrange.Columns.count]:=str;
str:='';
ExcelApp.ActiveSheet.range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+'2'].AutoFill(ExcelApp.ActiveSheet.range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+'2:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)],xlFillDefault);
ExcelApp.ActiveSheet.range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+':'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)].numberformatlocal:='0%';
ExcelApp.WorkBooks[1].WorkSheets[1].Cells.Select;
ExcelApp.Selection.Sort(Key1:=ExcelApp.Range[inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+'2:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.ActiveSheet.usedrange.rows.count)],
Order1:=xlAscending,Header:=xlGuess,OrderCustom:=1,MatchCase:=False, Orientation:=xlTopToBottom);
ExcelApp.Range['B1:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].HorizontalAlignment:=xlCenter ;
ExcelApp.Range['A1:'+inttoz(ExcelApp.ActiveSheet.usedrange.Columns.count)+inttostr(ExcelApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].Borders.linestyle:=xlContinuous;
finally
Screen.Cursor:=crDefault;
end;
lbl_Warning.Caption:='直通良率導出成功!';
ExcelApp.Visible:=true;
#8
这个方法我也想到只是一下子不知道如何处理,