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

时间:2022-03-19 19:22:02
如题,我想将几个表:如供应商表,客户表,订单表,送货表,导出到同一个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


你可以导出到不同的 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


这个方法我也想到只是一下子不知道如何处理,