如何将多个表导出到EXCEL，里不同的工作表?

如题，我想将几个表：如供应商表，客户表，订单表，送货表，导出到同一个EXCEL里，但是一个表对应一个工作表,谢谢。

8 个解决方案

#1

你可以导出到不同的 sheet,如 sheet[0],sheet[1],sheet[2],sheet[3] 等

#2

我就是这个意思。但是不知道如何实现。

#3

该回复于2010-10-21 16:46:30被版主删除

#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