我建立了添加了2个Adoquery
希望在将他们中的数据导入到EXCEL中时,1个Adoquery的数据导入到1张sheet里
我对这些一点都不熟,以下是我的代码。当运行到下面有<----标记的地方就提示无效索引,百般无奈
另外前面能正确执行的代码能正确生成Excel报表
if adoquery1.RecordCount <> 0 then
begin
//创建OLE对象Excel Application与 WorkBook
try
XLApp := CreateOleObject('Excel.Application'); //创建OLE对象
except
MessageDlg('您的机器里未安装Microsoft Excel2000。请先安装Microsoft Office2000!',mtwarning,[mbOK],0);
abort;
end;
XlApp.workbooks.add(xlWBatWorkSheet);
XLApp.Visible:=true;
XLApp.Application.Caption := '财务报表';
XLAPP.workbooks[1].sheets[1].name := '应收款列表';
sheet:=XLAPP.workbooks[1].sheets['应收款列表'];
sheet.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).FieldNames := false;
sheet.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).Refresh(False);
sheet.Range['A5:H5'].HorizontalAlignment := xlCenter;
XLAPP.Cells[6,1].value := '1';
sheet.Range['A6'].select;
if adoquery1.RecordCount > 1 then
sheet.Range['A6'].AutoFill(sheet.Range['A6:A'+inttostr(adoquery1.RecordCount + 5)],xlLandscape);
sheet.Columns['F:F'].ColumnWidth := 10;
//XLAPP.workbooks[1].Columns['H:H'].ColumnWidth := 10;
sheet.Columns['A:A'].ColumnWidth := 5;
XLApp.ActiveWindow.DisplayGridlines := False;
sheet.PageSetup.PrintTitleRows := '$1:$5'; //合并单元格
sheet.Range['F2:H2'].MergeCells := true;
sheet.Range['G3:H3'].MergeCells := true;
sheet.Range['G3:H3'].HorizontalAlignment := xlCenter;
sheet.Range['G3:H3'].VerticalAlignment := xlCenter;
sheet.Range['G1:H1'].MergeCells := true;
sheet.Range['G1:H1'].HorizontalAlignment := xlCenter;
sheet.Range['G1:H1'].VerticalAlignment := xlCenter;
sheet.Range['A1:E3'].MergeCells := true;
sheet.Range['A1:E3'].HorizontalAlignment := xlCenter;
sheet.Range['A1:E3'].VerticalAlignment := xlCenter;
sheet.Range['A1:E3'].value := '应收款列表';
sheet.Rows[2].Font.Name := '宋体';
sheet.Range['A1:E3'].Font.Color := clBlue;
sheet.Range['A1:E3'].Font.size := 18;
sheet.Range['A1:E3'].Font.Bold := True;
XLAPP.Cells[1,6].value := '制表日期';
XLAPP.Cells[3,6].value := '制表人';
sheet.Range['G1:H1'].value := p_windows.logtime;
sheet.Range['G3:H3'].value := p_windows.logName; sheet.Rows[5].Font.Bold := True;
sheet.Rows[5].Font.Name := '宋体';
end; if adoquery2.RecordCount <> 0 then
begin XlApp.WorkSheets[2].Activate;<--------报错内容“无效索引”
XLAPP.workbooks[1].sheets[2].name := '应收款历程列表'; sheet2:=XLAPP.workbooks[1].sheets['应收款历程列表'];
sheet2.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).FieldNames := false;
sheet2.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).Refresh(False);
sheet2.Range['A5:H5'].HorizontalAlignment := xlCenter;
XLAPP.Cells[6,1].value := '1';
sheet2.Range['A6'].select;
if adoquery1.RecordCount > 1 then
sheet2.Range['A6'].AutoFill(sheet2.Range['A6:A'+inttostr(adoquery1.RecordCount + 5)],xlLandscape);
sheet2.Columns['F:F'].ColumnWidth := 10;
//XLAPP.workbooks[1].Columns['H:H'].ColumnWidth := 10;
sheet2.Columns['A:A'].ColumnWidth := 5;
XLApp.ActiveWindow.DisplayGridlines := False;
sheet2.PageSetup.PrintTitleRows := '$1:$5'; //合并单元格
sheet2.Range['F2:H2'].MergeCells := true;
sheet2.Range['G3:H3'].MergeCells := true;
sheet2.Range['G3:H3'].HorizontalAlignment := xlCenter;
sheet2.Range['G3:H3'].VerticalAlignment := xlCenter;
sheet2.Range['G1:H1'].MergeCells := true;
sheet2.Range['G1:H1'].HorizontalAlignment := xlCenter;
sheet2.Range['G1:H1'].VerticalAlignment := xlCenter;
sheet2.Range['A1:E3'].MergeCells := true;
sheet2.Range['A1:E3'].HorizontalAlignment := xlCenter;
sheet2.Range['A1:E3'].VerticalAlignment := xlCenter;
sheet2.Range['A1:E3'].value := '应收款列表';
sheet2.Rows[2].Font.Name := '宋体';
sheet2.Range['A1:E3'].Font.Color := clBlue;
sheet2.Range['A1:E3'].Font.size := 18;
sheet2.Range['A1:E3'].Font.Bold := True;
XLAPP.Cells[1,6].value := '制表日期';
XLAPP.Cells[3,6].value := '制表人';
sheet2.Range['G1:H1'].value := p_windows.logtime;
sheet2.Range['G3:H3'].value := p_windows.logName; sheet2.Rows[5].Font.Bold := True;
sheet2.Rows[5].Font.Name := '宋体';
end;
希望在将他们中的数据导入到EXCEL中时,1个Adoquery的数据导入到1张sheet里
我对这些一点都不熟,以下是我的代码。当运行到下面有<----标记的地方就提示无效索引,百般无奈
另外前面能正确执行的代码能正确生成Excel报表
if adoquery1.RecordCount <> 0 then
begin
//创建OLE对象Excel Application与 WorkBook
try
XLApp := CreateOleObject('Excel.Application'); //创建OLE对象
except
MessageDlg('您的机器里未安装Microsoft Excel2000。请先安装Microsoft Office2000!',mtwarning,[mbOK],0);
abort;
end;
XlApp.workbooks.add(xlWBatWorkSheet);
XLApp.Visible:=true;
XLApp.Application.Caption := '财务报表';
XLAPP.workbooks[1].sheets[1].name := '应收款列表';
sheet:=XLAPP.workbooks[1].sheets['应收款列表'];
sheet.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).FieldNames := false;
sheet.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).Refresh(False);
sheet.Range['A5:H5'].HorizontalAlignment := xlCenter;
XLAPP.Cells[6,1].value := '1';
sheet.Range['A6'].select;
if adoquery1.RecordCount > 1 then
sheet.Range['A6'].AutoFill(sheet.Range['A6:A'+inttostr(adoquery1.RecordCount + 5)],xlLandscape);
sheet.Columns['F:F'].ColumnWidth := 10;
//XLAPP.workbooks[1].Columns['H:H'].ColumnWidth := 10;
sheet.Columns['A:A'].ColumnWidth := 5;
XLApp.ActiveWindow.DisplayGridlines := False;
sheet.PageSetup.PrintTitleRows := '$1:$5'; //合并单元格
sheet.Range['F2:H2'].MergeCells := true;
sheet.Range['G3:H3'].MergeCells := true;
sheet.Range['G3:H3'].HorizontalAlignment := xlCenter;
sheet.Range['G3:H3'].VerticalAlignment := xlCenter;
sheet.Range['G1:H1'].MergeCells := true;
sheet.Range['G1:H1'].HorizontalAlignment := xlCenter;
sheet.Range['G1:H1'].VerticalAlignment := xlCenter;
sheet.Range['A1:E3'].MergeCells := true;
sheet.Range['A1:E3'].HorizontalAlignment := xlCenter;
sheet.Range['A1:E3'].VerticalAlignment := xlCenter;
sheet.Range['A1:E3'].value := '应收款列表';
sheet.Rows[2].Font.Name := '宋体';
sheet.Range['A1:E3'].Font.Color := clBlue;
sheet.Range['A1:E3'].Font.size := 18;
sheet.Range['A1:E3'].Font.Bold := True;
XLAPP.Cells[1,6].value := '制表日期';
XLAPP.Cells[3,6].value := '制表人';
sheet.Range['G1:H1'].value := p_windows.logtime;
sheet.Range['G3:H3'].value := p_windows.logName; sheet.Rows[5].Font.Bold := True;
sheet.Rows[5].Font.Name := '宋体';
end; if adoquery2.RecordCount <> 0 then
begin XlApp.WorkSheets[2].Activate;<--------报错内容“无效索引”
XLAPP.workbooks[1].sheets[2].name := '应收款历程列表'; sheet2:=XLAPP.workbooks[1].sheets['应收款历程列表'];
sheet2.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).FieldNames := false;
sheet2.QueryTables.Add(adoquery1.Recordset,XLAPP.Range['A5', EmptyParam], EmptyParam).Refresh(False);
sheet2.Range['A5:H5'].HorizontalAlignment := xlCenter;
XLAPP.Cells[6,1].value := '1';
sheet2.Range['A6'].select;
if adoquery1.RecordCount > 1 then
sheet2.Range['A6'].AutoFill(sheet2.Range['A6:A'+inttostr(adoquery1.RecordCount + 5)],xlLandscape);
sheet2.Columns['F:F'].ColumnWidth := 10;
//XLAPP.workbooks[1].Columns['H:H'].ColumnWidth := 10;
sheet2.Columns['A:A'].ColumnWidth := 5;
XLApp.ActiveWindow.DisplayGridlines := False;
sheet2.PageSetup.PrintTitleRows := '$1:$5'; //合并单元格
sheet2.Range['F2:H2'].MergeCells := true;
sheet2.Range['G3:H3'].MergeCells := true;
sheet2.Range['G3:H3'].HorizontalAlignment := xlCenter;
sheet2.Range['G3:H3'].VerticalAlignment := xlCenter;
sheet2.Range['G1:H1'].MergeCells := true;
sheet2.Range['G1:H1'].HorizontalAlignment := xlCenter;
sheet2.Range['G1:H1'].VerticalAlignment := xlCenter;
sheet2.Range['A1:E3'].MergeCells := true;
sheet2.Range['A1:E3'].HorizontalAlignment := xlCenter;
sheet2.Range['A1:E3'].VerticalAlignment := xlCenter;
sheet2.Range['A1:E3'].value := '应收款列表';
sheet2.Rows[2].Font.Name := '宋体';
sheet2.Range['A1:E3'].Font.Color := clBlue;
sheet2.Range['A1:E3'].Font.size := 18;
sheet2.Range['A1:E3'].Font.Bold := True;
XLAPP.Cells[1,6].value := '制表日期';
XLAPP.Cells[3,6].value := '制表人';
sheet2.Range['G1:H1'].value := p_windows.logtime;
sheet2.Range['G3:H3'].value := p_windows.logName; sheet2.Rows[5].Font.Bold := True;
sheet2.Rows[5].Font.Name := '宋体';
end;
procedure TForm1.DBGrid1DblClick(Sender: TObject);
var
myexcel:variant;
workbook:olevariant;
worksheet:olevariant;
i,j,k:integer;
begin
try
myexcel:=createoleobject('excel.application');
myexcel.application.workbooks.add;
myexcel.caption:='将数据导入到EXCEL表中';
myexcel.application.visible:=true;
workbook:=myexcel.application.workbooks[1];
worksheet:=workbook.worksheets.item[1];
except
showmessage('EXCEL不存在!');
end;
i:=0;
table1.first;
//加表头
for k:=0 to table1.FieldCount-1 do
worksheet.cells[1,1+k]:=table1.fields[k].DisplayName; //加数据
while not table1.eof do
begin
inc(i);
for j:=0 to table1.fieldcount-2 do
worksheet.cells[i+1,j+1]:=table1.fields[j].asstring;
table1.next;
end;end;