在form上添加了ExcelApplication,ExcelWorkBook,ExcelWorkSheet之后,代码:
excelapplication1.connect;
excelapplication1.visible[0]:=true;
ExcelApplication1.Workbooks.Open (c:\a.xls, //打开工作簿
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
excelapplication1.worksheets[2].active; //这句出错;
excelapplication1.cells[1,2].value; //也是错误;在网上看到的差不多都是这样说的.
但是输入"excelapplication1.worksheets[2]."之后,在可选的方法中没有出现"active";
输入"excelapplication1.cells[1,2]."之后,没有可选的方法,属性
excelapplication1.connect;
excelapplication1.visible[0]:=true;
ExcelApplication1.Workbooks.Open (c:\a.xls, //打开工作簿
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
excelapplication1.worksheets[2].active; //这句出错;
excelapplication1.cells[1,2].value; //也是错误;在网上看到的差不多都是这样说的.
但是输入"excelapplication1.worksheets[2]."之后,在可选的方法中没有出现"active";
输入"excelapplication1.cells[1,2]."之后,没有可选的方法,属性
.....
ea1:= TExcelApplication.Create(nil);
ews1:=TExcelWorksheet.Create(nil);
ewb1:= TExcelWorkbook.Create(nil);
try
ea1.Connect; //打开excel。
try
ea1.Visible[0] := True; //可见
ea1.Caption :='测试excel操作';
ewb1.ConnectTo(ea1.Workbooks.Add(emptyparam,0)); // 新建xls文件
//ewb1.ConnectTo(ea1.Workbooks[1]); //连接第1个工作薄 (在已经有1个或多个工作薄打开的情况下)
ews1.ConnectTo(ewb1.Worksheets[2] as _worksheet); //连接工作页-第2个工作页
//ews1.ConnectTo(ewb1.Worksheets.Add(emptyparam,emptyparam,emptyparam,emptyparam,0) as _worksheet);//新建工作页 ews1.Activate ; //将这个工作页设为当前工作页.
ews1.Range['E5','E5'].Select ; //选中
except
ea1.Disconnect; //断开连接
ea1.Quit; //退出excel
end;
finally
ews1.Free;
ewb1.Free;
ea1.Free;
end;
C_Default_Font_Size = 12;
C_Default_Font_Name = 'Times New Roman';
C_Default_Row_Height = 25;
C_Default_Margin = 40;type
TxlBorder = set of (xlbTop, xlbBottom, xlbLeft, xlbRight, xlbBox,
xlbInsideVertical, xlbInsideHorizontal); TExcel = class
private
FExcel: Variant;
FSaveAs: string;
function CNoToCol(aCol_n: integer): string;
function NoToRange(aR1, aC1: Word; aR2: Word = 0; aC2: Word = 0): string;
procedure CreateExcelObj(aTemplateFileName: string);
procedure ReleaseExcelObj;
procedure SetCellsFormat(aFontName: string; aFontSize, aRowHeight: integer);
public
constructor Create(aTemplateFileName: string = ''; aSaveAs: string = '');
destructor Destroy; override;
property Excel: Variant read FExcel;
procedure SetValue(aRow, aCol: integer; aValue: variant); overload;
procedure SetValue(aValue: variant); overload;
procedure SetPageOrientation(aOrientation: Cardinal = xlPortrait);
procedure SetColFormat(aCol: integer; aField: TField);
procedure SetFontSize(aFontSize: integer);
procedure MergeCells(aStartRow, aStartCol, aEndRow, aEndCol: integer);
procedure SetHAlignment(aAlignment: Cardinal);
procedure RangeAutoFit(aStartRow, aStartCol, aEndRow, aEndCol: integer);
procedure SetSelection(aStartRow, aStartCol, aEndRow, aEndCol: integer);
procedure SetRowsHeight(aStartRow, aEndRow, aRowHeight: integer);
procedure DrawBox(xlb: TxlBorder);
end;implementation{ TExcel }
begin
if (xlbLeft in xlb) or (xlbBox in xlb) then
begin
FExcel.Selection.Borders[xlEdgeLeft].LineStyle := xlContinuous;
FExcel.Selection.Borders[xlEdgeLeft].Weight := xlThin;
end; //if
if (xlbTop in xlb) or (xlbBox in xlb) then
begin
FExcel.Selection.Borders[xlEdgeTop].LineStyle := xlContinuous;
FExcel.Selection.Borders[xlEdgeTop].Weight := xlThin;
end; //if
if (xlbBottom in xlb) or (xlbBox in xlb) then
begin
FExcel.Selection.Borders[xlEdgeBottom].LineStyle := xlContinuous;
FExcel.Selection.Borders[xlEdgeBottom].Weight := xlThin;
end; //if
if (xlbRight in xlb) or (xlbBox in xlb) then
begin
FExcel.Selection.Borders[xlEdgeRight].LineStyle := xlContinuous;
FExcel.Selection.Borders[xlEdgeRight].Weight := xlThin;
end; //if
if (xlbInsideVertical in xlb) or (xlbBox in xlb) then
begin
FExcel.Selection.Borders[xlInsideVertical].LineStyle := xlContinuous;
FExcel.Selection.Borders[xlInsideVertical].Weight := xlThin;
end;
if (xlbInsideHorizontal in xlb) or (xlbBox in xlb) then
begin
FExcel.Selection.Borders[xlInsideHorizontal].LineStyle := xlContinuous;
FExcel.Selection.Borders[xlInsideHorizontal].Weight := xlThin;
end;
end;function TExcel.CNoToCol(aCol_n: integer): string;
begin
if aCol_n > 256 then
raise Exception.Create('Invalid column number (' + IntToStr(aCol_n)
+ '), the maximum column number is 256.');
if aCol_n > 26 then
begin
Result := CHR((aCol_n div 26) + ORD('A') - 1);
aCol_n := (aCol_n mod 26);
if aCol_n = 0 then
begin
Result := CHR(ORD(Result[1]) - 1) + 'Z';
Exit;
end; //if
end
else
Result := '';
Result := Result + CHR(aCol_n + ORD('A') - 1);
end;constructor TExcel.Create(aTemplateFileName, aSaveAs: string);
begin
FSaveAs := aSaveAs;
CreateExcelObj(aTemplateFileName);
end;procedure TExcel.CreateExcelObj(aTemplateFileName: string);
var
lErrMsg: string;
begin
try
lErrMsg := '初始化Excel失敗可能沒有安裝Excel或其它錯誤﹐請重試﹗';
FExcel := CreateOLEObject('Excel.Application');
FExcel.DisplayAlerts := False;
FExcel.Visible := False;
if (aTemplateFileName <> '')
and FileExists(aTemplateFileName) then
begin
FExcel.WorkBooks.Open(aTemplateFileName);
FExcel.ActiveSheet.Unprotect(Password := '123');
end else
begin
FExcel.WorkBooks.Add;
SetCellsFormat(C_Default_Font_Name, C_Default_Font_Size, C_Default_Row_Height);
end;
FExcel.ActiveWindow.DisplayGridlines := True;
except
raise Exception.Create(lErrMsg);
if VarIsEmpty(FExcel) then
FExcel := unAssigned;
end;
end;destructor TExcel.Destroy;
begin
ReleaseExcelObj;
inherited;
end;procedure TExcel.MergeCells(aStartRow, aStartCol, aEndRow,
aEndCol: integer);
begin
FExcel.Range[NotoRange(aStartRow, aStartCol, aEndRow, aEndCol)].Select;
FExcel.Selection.Merge;
end;function TExcel.NoToRange(aR1, aC1, aR2, aC2: Word): string;
begin
if (aR2 = 0) or (aC2 = 0) then
Result := CNoToCol(aC1) + IntToStr(aR1)
else
Result := CNoToCol(aC1) + IntToStr(aR1) + ':' + CNoToCol(aC2) + IntToStr(aR2);
end;procedure TExcel.ReleaseExcelObj;
begin
if not VarIsEmpty(FExcel) then
begin
FExcel.Cells[1, 1].Select;
//FExcel.ActiveSheet.Protect(Password := '123');
if FSaveAs <> '' then
FExcel.ActiveWorkbook.SaveAs(FileName := FSaveAs);
FExcel.DisplayAlerts := True;
FExcel.Visible := True;
FExcel := UnAssigned;
end;
end;procedure TExcel.SetCellsFormat(aFontName: string; aFontSize,
aRowHeight: integer);
begin
FExcel.Cells.Select;
FExcel.Selection.Font.Name := aFontName;
FExcel.Selection.Font.Size := aFontSize;
FExcel.Selection.WrapText := True;
FExcel.Selection.HorizontalAlignment := xlLeft;
FExcel.Selection.VerticalAlignment := xlCenter;
FExcel.Selection.Rows.RowHeight := aRowHeight; FExcel.ActiveSheet.PageSetup.Orientation := xlPortrait;
FExcel.ActiveSheet.PageSetup.CenterHorizontally := True;
FExcel.ActiveSheet.PageSetup.LeftMargin := C_Default_Margin;
FExcel.ActiveSheet.PageSetup.RightMargin := C_Default_Margin;
FExcel.ActiveSheet.PageSetup.TopMargin := C_Default_Margin;
FExcel.ActiveSheet.PageSetup.BottomMargin := C_Default_Margin;
end;procedure TExcel.SetColFormat(aCol: integer; aField: TField);
begin
if UpperCase(aField.FieldName) = 'SEQUENCE' then
begin
FExcel.Columns[aCol].NumberFormatLocal := TFloatField(aField).DisplayFormat;
FExcel.Columns[aCol].HorizontalAlignment := xlCenter;
exit;
end else if aField.DataType in [ftinteger, ftFloat, ftBcd, ftCurrency] then
begin
FExcel.Columns[aCol].NumberFormatLocal := TFloatField(aField).DisplayFormat;
FExcel.Columns[aCol].HorizontalAlignment := xlRight;
end
else if aField.DataType in [ftDate, ftDatetime] then
FExcel.Columns[aCol].NumberFormatLocal := TFloatField(aField).DisplayFormat
else if aField.DataType in [ftString, ftWideString] then
FExcel.Columns[aCol].NumberFormatLocal := '@';
end;procedure TExcel.SetValue(aRow, aCol: integer; aValue: variant);
begin
FExcel.Cells[aRow, aCol].Value := aValue;
end;procedure TExcel.SetHAlignment(aAlignment: Cardinal);
begin
FExcel.Selection.HorizontalAlignment := aAlignment;
end;procedure TExcel.SetValue(aValue: variant);
begin
FExcel.Selection := aValue;
end;procedure TExcel.RangeAutoFit(aStartRow, aStartCol, aEndRow,
aEndCol: integer);
begin
FExcel.Range[NotoRange(aStartRow, aStartCol, aEndRow, aEndCol)].Select;
FExcel.Selection.Columns.Autofit;
end;procedure TExcel.SetFontSize(aFontSize: integer);
begin
FExcel.Selection.Font.Size := aFontSize;
end;procedure TExcel.SetPageOrientation(aOrientation: Cardinal);
begin
FExcel.ActiveSheet.PageSetup.Orientation := aOrientation;
FExcel.ActiveSheet.PageSetup.CenterHorizontally := True
end;procedure TExcel.SetRowsHeight(aStartRow, aEndRow, aRowHeight: integer);
begin
FExcel.Range[NotoRange(aStartRow, 1, aEndRow, 1)].Select;
FExcel.Selection.Rows.RowHeight := aRowHeight;
end;procedure TExcel.SetSelection(aStartRow, aStartCol, aEndRow,
aEndCol: integer);
begin
FExcel.Range[NotoRange(aStartRow, aStartCol, aEndRow, aEndCol)].Select;
end;end.