取Excel选定单元格的值 ( 积分: 100 )

  • 主题发起人 主题发起人 iseek
  • 开始时间 开始时间
I

iseek

Unregistered / Unconfirmed
GUEST, unregistred user!
如题。

说来好笑，用了好长时间的Excel，一直都是读写指定单元格的值，今天突然发现，自己竟然不知道如何读当前选定单元格的值，也就是说，我该如何像stringGrid那样，读Cells[Col,Row]？

在线等，答案一出，即刻放分。
 
如题。

说来好笑，用了好长时间的Excel，一直都是读写指定单元格的值，今天突然发现，自己竟然不知道如何读当前选定单元格的值，也就是说，我该如何像stringGrid那样，读Cells[Col,Row]？

在线等，答案一出，即刻放分。
 
注意：不是“指定”,而是已“选定”的单元格。
 
cells[1,1].value
 
Range("E9").Select
 
不是“指定”的“E9”或者“1,1”,而是已“选定”的单元格。比如说，你刚才用鼠标点击了“F8”单元格，但我并不知道，我要通过程序读，得到“F8”，然后取“F8”的值。
 
Application.ActiveCell.Value
 
谢谢vvyang.
另二位也辛苦了，各给1分。呵呵。

下面是我自用的Excel单元，顺便贴出来，也许对有些人有用。
------------------------------------------------------
unit ExcelUnit;

interface
uses classes,Sysutils,ComObj;


Type TMyExcel = class
private
FTR : integer;
FExcelApp : Variant;
FVisible : boolean;
FModified : boolean;
FLoaded : boolean;

procedure SetTR(Value:integer);
procedure SetVisible(Value:boolean);
procedure MonthSumFormula(aSheet,aCol:integer;sCol:string);
procedure MonthSumFormula2(aSheet,sCol:string;aCol:integer);
function WorkBookName(i:integer):string; //未使用
public
property TR:integer read FTR write SetTR;
property Visible:boolean read FVisible write SetVisible;
property Loaded:boolean read FLoaded;

constructor Create;
destructor Destroy;override;

procedure LoadExcelFile;
function IndexOfExcel(s:string;aSheet,aRow,aCol:integer):integer;
function IndexOfExcel2(s,aSheet:string;aRow,aCol:integer):integer;
function UsedCount(rc:boolean;aSheet:integer):integer;//Excel表某sheet有数据的最后行或列
function UsedCount2(rc:boolean;aSheet:string):integer;//Excel表某sheet有数据的最后行或列
function ACellValue(aSheet,aRow,aCol:integer):string; //返回某单元格数据
function ACellValue2(aSheet:string;aRow,aCol:integer):string;
function ACellValue3(aSheet:string;aRow,aCol:integer):string;//空白格强制返回零值
function ACellValue4(aSheet:string;aRow,aCol:integer):string;//正确返回百分数
function ACellValue5(aSheet:string;aRow,aCol:integer):string;//返回值保留两位小数
procedure InsertACol(str:string;aSheet,aCol:integer); //在表里插入一列
procedure AddACol(s,aSheet:string;aCol:integer); //在表里追加一列
procedure WriteACell(s:string;aSheet,aRow,aCol:integer;iPlus:boolean); //写单元格
procedure WriteACell2(s,aSheet:string;aRow,aCol:integer;iPlus:boolean); //写单元格
procedure DeleteACol(aSheet,aCol:integer); //删除指定列
procedure DeleteACol2(aSheet:string;aCol:integer); //删除指定列
procedure SaveExcel; //保存
function SaveAs(fn:string):boolean; //换名保存
procedure SaveAndClose; //保存并关闭
procedure ShowExcel(f:boolean); //显示/隐藏
procedure SelectAll(st:integer); //全选
procedure SelectUsed(st:integer); //选定已使用区域
procedure SelCopy; //复制选定区域
procedure AutoFilt(aCol:integer;kWord:string); //数据筛选
procedure AutoFilt2(aCol,op:integer;c1,c2:string); //两重条件筛选.op=Operator
procedure UnAutoFilt(aCol:integer); //取消筛选
function WorkBookNum:integer; //返回工作簿数量
procedure RangeSelect(s:string); //选择指定区域
function BlankRows(aSheet,aCol,iStart:integer):integer;//返回某页某列的空行数
//rocedure WriteDate(aRow:integer;dt:string); //写日期
procedure CloseWorkBook(i:integer); //
procedure GiveupSave;
procedure AddAWorkBook(fn:string);
end;

implementation
uses MainUnit,MyUnit,GlobalUnit,{passUnit,}MyConst;

constructor TMyExcel.Create;
begin
inherited Create;
FTR:=-1;
try
FExcelApp := CreateOleObject(sExcelApp);
except
on Exception do raise exception.Create(sNoExcelApp);
end;
FVisible:=false;
FModified:=false;
FLoaded:=false;
end;

destructor TMyExcel.Destroy;
begin
FExcelApp.Quit;
inherited;
end;

procedure TMyExcel.SetTR(Value:integer);
begin
FTR:=Value;
end;

procedure TMyExcel.SetVisible(Value:boolean);
begin
FVisible:=Value;
end;

procedure TMyExcel.LoadExcelFile;
begin
if FExcelApp.WorkBooks.Count>0 then exit;
if FileExists(iRunMgr.ExcelFile) then
begin
FExcelApp.WorkBooks.Open(iRunMgr.ExcelFile);
//FExcelApp.Visible := True;
end;
FTR:=iRunMgr.CurTR;
FLoaded:=true;
end;

function TMyExcel.IndexOfExcel(s:string;aSheet,aRow,aCol:integer):integer;
var i:integer;
tmp:string;
begin
Result:=-1;
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
if aRow>0 then
begin
for i:=1 to 256 do
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[aRow,i];
if tmp=s then
begin
Result:=i;
break;
end;
end;
end else
if aCol>0 then
begin
for i:=3 to 368 do
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[i,aCol];
if tmp=s then
begin
Result:=i;
break;
end;
end;
end;
end;

function TMyExcel.UsedCount(rc:boolean;aSheet:integer):integer;
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
if rc then
begin
if aSheet=0 then Result:=FExcelApp.ActiveSheet.usedRange.Rows.count else
Result:=FExcelApp.WorkSheets[aSheet].usedRange.Rows.count;
end else
begin
if aSheet=0 then Result:=FExcelApp.ActiveSheet.usedRange.Columns.count else
Result:=FExcelApp.WorkSheets[aSheet].usedRange.Columns.count;
end;
end;

function TMyExcel.ACellValue(aSheet,aRow,aCol:integer):string;
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
Result:=FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value;
end;

procedure TMyExcel.MonthSumFormula(aSheet,aCol:integer;sCol:string);
begin
FExcelApp.WorkSheets[aSheet].Cells[370,aCol]:='=SUM('+sCol+'3:'+sCol+'33)';
FExcelApp.WorkSheets[aSheet].Cells[371,aCol]:='=SUM('+sCol+'34:'+sCol+'62)';
FExcelApp.WorkSheets[aSheet].Cells[372,aCol]:='=SUM('+sCol+'63:'+sCol+'93)';
FExcelApp.WorkSheets[aSheet].Cells[373,aCol]:='=SUM('+sCol+'94:'+sCol+'123)';
FExcelApp.WorkSheets[aSheet].Cells[374,aCol]:='=SUM('+sCol+'124:'+sCol+'154)';
FExcelApp.WorkSheets[aSheet].Cells[375,aCol]:='=SUM('+sCol+'155:'+sCol+'184)';
FExcelApp.WorkSheets[aSheet].Cells[376,aCol]:='=SUM('+sCol+'185:'+sCol+'215)';
FExcelApp.WorkSheets[aSheet].Cells[377,aCol]:='=SUM('+sCol+'216:'+sCol+'246)';
FExcelApp.WorkSheets[aSheet].Cells[378,aCol]:='=SUM('+sCol+'247:'+sCol+'276)';
FExcelApp.WorkSheets[aSheet].Cells[379,aCol]:='=SUM('+sCol+'277:'+sCol+'307)';
FExcelApp.WorkSheets[aSheet].Cells[380,aCol]:='=SUM('+sCol+'308:'+sCol+'337)';
FExcelApp.WorkSheets[aSheet].Cells[381,aCol]:='=SUM('+sCol+'338:'+sCol+'368)';
end;

procedure TMyExcel.InsertACol(str:string;aSheet,aCol:integer);
var s:string;
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
s:=iRunMgr.NumberToLetter(aCol);
FExcelApp.WorkSheets[aSheet].Columns[aCol].Insert;
FExcelApp.WorkSheets[aSheet].Cells[1,aCol].Formula:='=SUBTOTAL(9,'+s+'3:'+s+'368)';
FExcelApp.WorkSheets[aSheet].Cells[2,aCol].Value:=str;
MonthSumFormula(aSheet,aCol,s);
FModified:=true;
end;

procedure TMyExcel.WriteACell(s:string;aSheet,aRow,aCol:integer;iPlus:boolean);
var sum:real;
tmp:string;
begin
if not iPlus then FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value:=s else
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value;
if tmp='' then FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value:=s else
begin
sum:=strtofloat(tmp)+strtofloat(s);
FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value:=floattostr(sum);
end;
end;
FModified:=true;
end;

procedure TMyExcel.DeleteACol(aSheet,aCol:integer);
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
FExcelApp.WorkSheets[aSheet].Columns[aCol].Delete;
FModified:=true;
end;

procedure TMyExcel.SaveExcel;
begin
if FExcelApp.WorkBooks.Count=0 then exit;
if not FModified then exit;
if not FExcelApp.ActiveWorkBook.Saved then FExcelApp.ActiveWorkBook.Save;
FModified:=false;
end;

function TMyExcel.SaveAs(fn:string):boolean;
begin
Result:=false;
if FExcelApp.WorkBooks.Count=0 then exit;
FExcelApp.SaveAs(fn);
Result:=true;
end;

procedure TMyExcel.SaveAndClose;
begin
if FExcelApp.WorkBooks.Count=0 then exit;
try
if not FExcelApp.ActiveWorkBook.Saved then FExcelApp.ActiveWorkBook.Save;
FExcelApp.WorkBooks.Close;
FLoaded:=false;
except
end;
end;

procedure TMyExcel.ShowExcel(f:boolean);
begin
if (f) and (FExcelApp.WorkBooks.Count=0) then LoadExcelFile;
FExcelApp.Visible := f;
end;

procedure TMyExcel.SelectAll(st:integer);
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
FExcelApp.WorkSheets[st].Activate;
FExcelApp.Cells.Select;
end;

procedure TMyExcel.SelectUsed(st:integer);
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
FExcelApp.WorkSheets[st].Activate;
FExcelApp.ActiveSheet.UsedRange.Copy;
end;

procedure TMyExcel.SelCopy;
begin
if FExcelApp.WorkBooks.Count=0 then exit;
FExcelApp.Selection.Copy;
end;

procedure TMyExcel.AutoFilt(aCol:integer;kWord:string);
begin
if FExcelApp.WorkBooks.Count=0 then exit;
FExcelApp.Selection.AutoFilter;
FExcelApp.Selection.AutoFilter(Field:=aCol,Criteria1:=kWord);
end;

procedure TMyExcel.AutoFilt2(aCol,op:integer;c1,c2:string);
var tmp:string;
begin
if FExcelApp.WorkBooks.Count=0 then exit;
FExcelApp.Selection.AutoFilter;
if op=1 then tmp:='xlAnd';
FExcelApp.Selection.AutoFilter(Field:=aCol,Criteria1:=c1,Operator:=tmp,Criteria2:=c2);
end;

function TMyExcel.WorkBookNum:integer;
begin
Result:=FExcelApp.WorkBooks.Count;
end;

procedure TMyExcel.RangeSelect(s:string);
begin
FExcelApp.Range.Select;
end;

procedure TMyExcel.UnAutoFilt(aCol:integer);
begin
if FExcelApp.WorkBooks.Count=0 then exit;
FExcelApp.Selection.AutoFilter(Field:=aCol);
end;

function TMyExcel.BlankRows(aSheet,aCol,iStart:integer):integer;
var i,k:integer;
tmp:string;
begin
Result:=-1;
if FExcelApp.WorkBooks.Count=0 then exit;
k:=0;
for i:=iStart to 368 do
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[i,aCol].Value;
if (trim(tmp)='') or (tmp='0') then inc(k);
end;
Result:=k;
end;

procedure TMyExcel.WriteACell2(s, aSheet: string; aRow, aCol: integer;
iPlus: boolean);
var sum:real;
tmp:string;
begin
if not iPlus then FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value:=s else
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value;
if tmp='' then FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value:=s else
begin
sum:=strtofloat(tmp)+strtofloat(s);
FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value:=floattostr(sum);
end;
end;
{if (aSheet<>'库存账') and (aSheet<>'总表') then
begin
if FExcelApp.WorkSheets[aSheet].Cells[aRow,1].Value='' then
FExcelApp.WorkSheets[aSheet].Cells[aRow,1].Value:=DateToStr(iMain.DTPicker.Date);
end;}
FModified:=true;
end;

procedure TMyExcel.AddACol(s, aSheet: string; aCol: integer);
var tmp:string;
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
tmp:=iRunMgr.NumberToLetter(aCol);
FExcelApp.WorkSheets[aSheet].Columns[aCol].Insert;
FExcelApp.WorkSheets[aSheet].Cells[1,aCol].Formula:='=SUBTOTAL(9,'+tmp+'3:'+tmp+'368)';
FExcelApp.WorkSheets[aSheet].Cells[2,aCol].Value:=s;
MonthSumFormula2(aSheet,tmp,aCol);
FModified:=true;
end;

procedure TMyExcel.MonthSumFormula2(aSheet, sCol: string; aCol: integer);
begin
FExcelApp.WorkSheets[aSheet].Cells[370,aCol]:='=SUM('+sCol+'3:'+sCol+'33)';
FExcelApp.WorkSheets[aSheet].Cells[371,aCol]:='=SUM('+sCol+'34:'+sCol+'62)';
FExcelApp.WorkSheets[aSheet].Cells[372,aCol]:='=SUM('+sCol+'63:'+sCol+'93)';
FExcelApp.WorkSheets[aSheet].Cells[373,aCol]:='=SUM('+sCol+'94:'+sCol+'123)';
FExcelApp.WorkSheets[aSheet].Cells[374,aCol]:='=SUM('+sCol+'124:'+sCol+'154)';
FExcelApp.WorkSheets[aSheet].Cells[375,aCol]:='=SUM('+sCol+'155:'+sCol+'184)';
FExcelApp.WorkSheets[aSheet].Cells[376,aCol]:='=SUM('+sCol+'185:'+sCol+'215)';
FExcelApp.WorkSheets[aSheet].Cells[377,aCol]:='=SUM('+sCol+'216:'+sCol+'246)';
FExcelApp.WorkSheets[aSheet].Cells[378,aCol]:='=SUM('+sCol+'247:'+sCol+'276)';
FExcelApp.WorkSheets[aSheet].Cells[379,aCol]:='=SUM('+sCol+'277:'+sCol+'307)';
FExcelApp.WorkSheets[aSheet].Cells[380,aCol]:='=SUM('+sCol+'308:'+sCol+'337)';
FExcelApp.WorkSheets[aSheet].Cells[381,aCol]:='=SUM('+sCol+'338:'+sCol+'368)';
end;

function TMyExcel.UsedCount2(rc: boolean; aSheet: string): integer;
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
if rc then
begin
if aSheet='' then Result:=FExcelApp.ActiveSheet.usedRange.Rows.count else
Result:=FExcelApp.WorkSheets[aSheet].usedRange.Rows.count;
end else
begin
if aSheet='' then Result:=FExcelApp.ActiveSheet.usedRange.Columns.count else
Result:=FExcelApp.WorkSheets[aSheet].usedRange.Columns.count;
end;
end;

function TMyExcel.ACellValue2(aSheet: string; aRow, aCol: integer): string;
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
Result:=FExcelApp.WorkSheets[aSheet].Cells[aRow,aCol].Value;
end;

procedure TMyExcel.DeleteACol2(aSheet: string; aCol: integer);
begin
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
FExcelApp.WorkSheets[aSheet].Columns[aCol].Delete;
FModified:=true;
end;

{procedure TMyExcel.WriteDate(aRow: integer; dt: string);
var i:integer;
begin
for i:=2 to FExcelApp.WorkSheets.Count - 1 do
FExcelApp.WorkSheets.Cells[aRow,1].Value:=dt;
end;}

function TMyExcel.WorkBookName(i: integer): string;
begin
Result:=FExcelApp.WorkBooks.Name;
end;

procedure TMyExcel.CloseWorkBook(i: integer);
begin
FExcelApp.WorkBooks.Close;
end;

procedure TMyExcel.GiveupSave;
begin
FExcelApp.ActiveWorkBook.Saved := True;
end;

procedure TMyExcel.AddAWorkBook(fn: string);
begin
FExcelApp.WorkBooks.Add;
FExcelApp.WorkBooks[FExcelApp.WorkBooks.Count].Open(fn);
end;

function TMyExcel.ACellValue3(aSheet: string; aRow, aCol: integer): string;
var tmp:string;
begin
tmp:=ACellValue2(aSheet,aRow,aCol);
if trim(tmp)='' then tmp:='0';
Result:=tmp;
end;

function TMyExcel.ACellValue4(aSheet: string; aRow, aCol: integer): string;
var tmp:string;
begin
tmp:=ACellValue2(aSheet,aRow,aCol);
if pos('.',tmp)>0 then tmp:=DoubleToPercent(tmp);
Result:=tmp;
end;

function TMyExcel.ACellValue5(aSheet: string; aRow, aCol: integer): string;
var tmp:string;
begin
tmp:=ACellValue2(aSheet,aRow,aCol);
if pos('.',tmp)>0 then tmp:=iRoundTo2(tmp,-2);
Result:=tmp;
end;

function TMyExcel.IndexOfExcel2(s, aSheet: string; aRow,
aCol: integer): integer;
var i:integer;
tmp:string;
begin
Result:=-1;
if FExcelApp.WorkBooks.Count=0 then LoadExcelFile;
if aRow>0 then
begin
for i:=1 to 256 do
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[aRow,i];
if tmp=s then
begin
Result:=i;
break;
end;
end;
end else
if aCol>0 then
begin
for i:=3 to 368 do
begin
tmp:=FExcelApp.WorkSheets[aSheet].Cells[i,aCol];
if tmp=s then
begin
Result:=i;
break;
end;
end;
end;
end;

end.
 
您必须登录或注册才可回复。

Similar threads

X
一步一步实现kbmmw的httpsys使用https功能 - xalion
回复
0
查看
1K
xalion
X
D
[原]Delphi FireMonkey如何翻译AndroidSDK中的Java类为JNI单元
回复
0
查看
930
DelphiTeacher的专栏
D
D
[原]Delphi FireMonkey如何翻译AndroidSDK中的Java类为JNI单元
回复
0
查看
871
DelphiTeacher的专栏
D
D
[原]Delphi FireMonkey如何翻译AndroidSDK中的Java类为JNI单元
回复
0
查看
945
DelphiTeacher的专栏
D
D
[原]Delphi FireMonkey如何翻译AndroidSDK中的Java类为JNI单元
回复
0
查看
777
DelphiTeacher的专栏
D
后退
顶部