看看这几个地方的文章,很详细的:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=11954158
http://www.djpate.freeserve.co.uk/AutoExcl.htm
拷贝可以用这样的:sheet1.Range("A1:F4").Copy(sheet2.Range("A1"))
把表一的A1:F4 copy区域 到表二的 A1开始的地方
http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=11954158
http://www.djpate.freeserve.co.uk/AutoExcl.htm
拷贝可以用这样的:sheet1.Range("A1:F4").Copy(sheet2.Range("A1"))
把表一的A1:F4 copy区域 到表二的 A1开始的地方
procedure TOfficeform.Button2Click(Sender: TObject);
var MSExcel:Variant;
i,j:Integer;
begin
SaveDialog1.Filter:='*.XLS|*.XLS';
SaveDialog1.DefaultExt:='XLS';
if SaveDialog1.Execute then
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Add;
MSExcel.Visible:=False;
Table1.Open;
j:=Table1.RecordCount;
Table1.First;
for i:=1 to j do
begin
MSExcel.Cells[i,1].NumberFormat:='@';
MSExcel.Cells[i,1].Value:=Table1.FieldByName('CODE').AsString;
MSExcel.Cells[i,2].Value:=Table1.FieldByName('COLOR').AsString;
Table1.Next;
end;
MSExcel.ActiveWorkBook.SaveAs(SaveDialog1.FileName);
MSExcel.ActiveWorkBook.Saved:=True;
MSExcel.Quit;
end;
我刚学习delphi,这几天也在看关于TexcelApplication
希望我做的工作能对你有点启发(delphi 6/Windows 2000/excel 2000)相关定义:
ExcelApplication: TExcelApplication;
ExcelWorkbook_Dest TExcelWorkbook;
ExcelWorksheet_Dest TExcelWorksheet;
ExcelWorkbook_Sour: TExcelWorkbook;
ExcelWorksheet_Sour: TExcelWorksheet;
处理
procedure TForm1.Button3Click(Sender: TObject);
Var
WorkBook_Dest,Workbook_Sour:_Workbook;
WorkSheet_Dest,WorkSheet_Sour:_Worksheet;
strTempFile:string;
Begin
strTempFile:=OpenDialog1.FileName;
strSaveAsFile:=SaveDialog1.FileName;
ExcelApplication.Connection();
ExcelApplication.Visible[0]:=true;
Workbook_Dest:=ExcelApplication.Workbooks.Add(EmptyParam,0) as _WorkBook;
ExcelWorkbook_Dest.ConnectTo(Workbook_Dest);
WorkSheet_Dest:=ExcelWorkbook_Dest.Worksheets.Add(EmptyParam,EmptyParam,emptyParam,emptyparam,0) as _WorkSheet;
ExcelWorkSheet_Dest.ConnectTo(WorkSheet_Dest);
Workbook_Sour:=ExcelApplication.Workbooks.Open(strTempFile,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkBook;
ExcelWorkbook_Sour.ConnectTo(WorkBook_Sour);
WorkSheet_Sour:=WorkBook_Sour.Worksheets.Item[1] as _WorkSheet;
ExcelWorkSheet_Sour.ConnectTo(WorkSheet_Sour);
ExcelWorkSheet_Sour.Select();
ExcelWorkSheet_Sour.Copy(ExcelWorkBook_Desc.Worksheets.Item[1]);
ExcelWorksheet_Sour.Disconnect();
ExcelWorkBook_Sour.Close();
ExcelWorkBook_Sour.Disconnect();
ExcelWorkSheet_Dest.Disconnect();
ExcelWorkBook_Dest.SaveAs(strSaveAsFile,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
ExcelWorkBool_Dest.Close();
ExcelWorkBook_Dest.Disconnect();
ExcelApplication.Quit();
ExcelApplication.DisConnect();
End;没有测试,如有错误就不好意思了
你这段代码是数据库写入Excel,不是excel写入excel,而且,你这段程序不用试都知道有错误,编译都通不过ActiveWorkBook有SaveAs的方法吗??
begin
Result := SrcArray;
end;procedure TForm1.Button1Click(Sender: TObject);
var
SrcBook, DestBook: _WorkBook;
SrcSheet, DestSheet: _WorkSheet;
i_LastRow, i_LastCol: Integer;
SrcArray, DestArray: Variant;
tempRange:Range;
begin
if SourceOpenDialog.Execute then begin
if DestOpenDialog.Execute then begin
ExcelApplication1.Connect;
SrcBook := ExcelApplication1.Workbooks.Open(SourceOpenDialog.FileName, null, null, null, null, null, null, null, null, null, null, null, null, 0);
SrcSheet := SrcBook.Worksheets.Item[1] as _WorkSheet; //假设该工作薄只有此表 DestBook := ExcelApplication1.Workbooks.Open(DestOpenDialog.FileName, null, null, null, null, null, null, null, null, null, null, null, null, 0);
DestSheet := DestBook.Worksheets.Item[1] as _WorkSheet; //假设该工作薄只有此表
with SrcSheet do begin
i_LastRow := Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Row;
i_LastCol := Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Column;
tempRange:= Range[Cells.Item[1, 1], Cells.Item[i_LastRow, i_LastCol]];
SrcArray := tempRange.Value; //返回载有源表所有数据的数组
DestArray := formatxm(SrcArray);
end;
with DestSheet do begin
Range[Cells.Item[1, 1], Cells.Item[i_LastRow, i_LastCol]].Value := DestArray;
end;
DestBook.Save(0);
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;
end;
end;
var
SrcSheet, DestSheet: _WorkSheet;
....
SrcSheet := SrcBook.Worksheets.Item[1] as _WorkSheet;
在我这里老是编译通不过,是不是前面要加点什么??
operator not applicable to this operand type
代码如下:
procedure TForm1.Button2Click(Sender: TObject);
Var
Excel: Variant;
iColumn, iRow:integer;
iColCount,iRowCount:Integer;
WS: _Worksheet;begin
try
Excel := GetActiveOleObject('Excel.Application');
except
Excel := CreateOleObject('Excel.Application');
end;
WS := Excel.Activesheet as _Worksheet;
...
end;
能加你QQ吗我的是63070398
老兄还有什么高见,请指点小弟一下!!
确实如你所说,按我的方法循环还是没有避免,但请你注意:你的循环每次都要调用
ExcelAppdestination.Cells(iColumn,iRow).value=Formatxm(ExcelAppSource.Cells(iColumn,iRow))
,也就是说每一个循环都要从源Excel中读一格,再往目标Excel写一格,那么源Excel有多少单元格就要进行多少次读写。而如我的方式,则只是在运算时循环,整个操作只需读写Excel各一次。如果这样你还不相信我的更有效率的话,那么请做个实验试一试。这是我的测试数据:
(循环操作为直接等于)
源表: 行-1000 列-9
我的方式:用时-0.3秒
你的方式:用时-73.5秒不信你可以试试!
总算能功成身退了,至于FormatXM()可以见下例:
function TForm1.formatxm(SrcArray: Variant): Variant;//可视为二维数组操作
var
i,j:Integer;
begin
Result:=SrcArray;//将返回值初始化为与SrcArray一样的形式
for i:=VarArrayLowBound(SrcArray,1) to VarArrayHighBound(SrcArray,1) do begin
for j:=VarArrayLowBound(SrcArray,2) to VarArrayHighBound(SrcArray,2) do
Result[i,j]:=SrcArray[i,j];
end;
end;至于"选择性粘贴",可以使用ranger的pastespecial方法。
嘿嘿!!该给我加分了吧。