我现在想将查询出来的数据保存到设计好的Excel模板中,我在程序中打开了Excel模板,但是模板的数据明细部分需要动态插入一行数据,怎么处理?
Excel模板: 标题字段1 字段2 字段3
(明细数据显示,格式已经设置好)
总计 SUM(字段3) 日期:我的处理程序:
procedure TForm1.Button5Click(Sender: TObject);
var
WorkBook,Sheet,ExcelObj,Excel: OleVariant;
RecordCounts,iPos : Integer;
I,J: Integer;
begin
ExcelObj := CreateOleObject('Excel.sheet');
Excel := ExcelObj.Application; Excel.DisplayAlerts := False; WorkBook := Excel.WorkBooks.Open('D:\分配明细报表模板.xls');
WorkBook.Sheets[1].Name := '基金名称';
Sheet := WorkBook.Sheets[1];
Excel.ActiveWindow.WindowState := -4137; //
Sheet.Cells[2,1].Value := '基金名称';
Sheet.Cells[3,1].Value := '日期'+FormatDateTime('YYYY.MM.DD',Date); RecordCounts := qry1.RecordCount;
//首先在模板中循环复制数据明细部分,但是这部分是错误的,怎么处理???
Sheet.Cells.Rows['5:5'].Select;
for iPos := 1 to RecordCounts do
begin Sheet.Cells.Selection.Copy;
Sheet.Cells.Rows.Selection.Insert(-4121);
Excel.CutCopyMode := False;
end;//将数据输入到模板中
qry1.First;
J:= 5;
while not qry1.Eof do
begin
for i := 1 to qry1.FieldCount do
Sheet.cells[j,i] := Trim(qry1.Fields[i-1].AsString);
qry1.Next;
inc(j);
end;
qry1.First; //保存
Excel.ActiveWorkBook.SaveAs('D:\Test.xls');
WorkBook.Close;
Excel.Quit;
end;
Excel模板: 标题字段1 字段2 字段3
(明细数据显示,格式已经设置好)
总计 SUM(字段3) 日期:我的处理程序:
procedure TForm1.Button5Click(Sender: TObject);
var
WorkBook,Sheet,ExcelObj,Excel: OleVariant;
RecordCounts,iPos : Integer;
I,J: Integer;
begin
ExcelObj := CreateOleObject('Excel.sheet');
Excel := ExcelObj.Application; Excel.DisplayAlerts := False; WorkBook := Excel.WorkBooks.Open('D:\分配明细报表模板.xls');
WorkBook.Sheets[1].Name := '基金名称';
Sheet := WorkBook.Sheets[1];
Excel.ActiveWindow.WindowState := -4137; //
Sheet.Cells[2,1].Value := '基金名称';
Sheet.Cells[3,1].Value := '日期'+FormatDateTime('YYYY.MM.DD',Date); RecordCounts := qry1.RecordCount;
//首先在模板中循环复制数据明细部分,但是这部分是错误的,怎么处理???
Sheet.Cells.Rows['5:5'].Select;
for iPos := 1 to RecordCounts do
begin Sheet.Cells.Selection.Copy;
Sheet.Cells.Rows.Selection.Insert(-4121);
Excel.CutCopyMode := False;
end;//将数据输入到模板中
qry1.First;
J:= 5;
while not qry1.Eof do
begin
for i := 1 to qry1.FieldCount do
Sheet.cells[j,i] := Trim(qry1.Fields[i-1].AsString);
qry1.Next;
inc(j);
end;
qry1.First; //保存
Excel.ActiveWorkBook.SaveAs('D:\Test.xls');
WorkBook.Close;
Excel.Quit;
end;
procedure TForm1.Button5Click(Sender: TObject);
var
WorkBook,Sheet,ExcelObj,Excel: OleVariant;
RecordCounts,iPos : Integer;
I,J: Integer;
begin
ExcelObj := CreateOleObject('Excel.sheet');
Excel := ExcelObj.Application; Excel.DisplayAlerts := False; WorkBook := Excel.WorkBooks.Open('D:\分配明细报表模板.xls');
WorkBook.Sheets[1].Name := '基金名称';
Sheet := WorkBook.Sheets[1];
Excel.ActiveWindow.WindowState := -4137; //
Sheet.Cells[2,1].Value := '基金名称';
Sheet.Cells[3,1].Value := '日期'+FormatDateTime('YYYY.MM.DD',Date); RecordCounts := qry1.RecordCount;
//首先在模板中循环复制数据明细部分
Sheet.Cells.Rows['5:5'].Select;
for iPos := 1 to RecordCounts do
begin Sheet.Cells.Rows['5:5'].Copy;
Sheet.Cells.Rows['5:5'].Insert(-4121); //在第5行之前插入
Excel.CutCopyMode := False;
end;//将数据输入到模板中
qry1.First;
J:= 5;
while not qry1.Eof do
begin
for i := 1 to qry1.FieldCount do
Sheet.cells[j,i] := Trim(qry1.Fields[i-1].AsString);
qry1.Next;
inc(j);
end;
qry1.First; //保存
Excel.ActiveWorkBook.SaveAs('D:\Test.xls');
WorkBook.Close;
Excel.Quit;
end;