| Blog信息 |
|
blog名称:注册会计师（注会）练习软件
日志总数:398
评论数量:116
留言数量:27
访问次数:3270496
建立时间:2005年6月6日 |
| |
|
 [delpih编程]给亿君表格 ejungrid增加处理excel的sunif函数功能
软件技术
吕向阳 发表于 2010/8/13 11:37:31 |
|
ejungrid亿君表格很强大，还体现自定义函数功能的扩充非常方便。在使用中我发现没有内置 sumif的excel函数，但我又着急用不能等升级，自己动手很方便，下面是sumif的代码
unit test_ejungrid;
interface
uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ZcCalcExpress, ZJGrid, ZcDataGrid, ZcUniClass, ZcSheetControl, ZcFormulas, StdCtrls, ExtCtrls, ZcGridStyle, ZcGridClasses;
type TstrArray = array of string; TDataArray = array of Extended;
type TForm1 = class(TForm) ejnlcns1: TEjunLicense; ejungrid1: TEjunDataGrid; btn1: TButton; btn2: TButton; dlgOpen1: TOpenDialog; btn3: TButton; procedure FormCreate(Sender: TObject); procedure btn1Click(Sender: TObject); procedure btn2Click(Sender: TObject); procedure btn3Click(Sender: TObject); private { Private declarations } function sumif(ACaller: TZcFormulaFunctionCaller; const Args: array of TZcFormulaValue): TZcFormulaValue; public { Public declarations } end;
var Form1: TForm1;
implementation
{$R *.dfm}
function TForm1.sumif(ACaller: TZcFormulaFunctionCaller; const Args: array of TZcFormulaValue): TZcFormulaValue;
procedure ReadDataArray(var Data: TDataArray; Args: TZcFormulaValue); var iCount1: Integer; iCol, iRow: Integer; I: Integer; strtmp: string; begin iCount1 := (Args.VRight - Args.VLeft + 1) * (Args.VBottom - Args.VTop + 1); SetLength(Data, iCount1); I := 0; for iRow := Args.VTop to Args.VBottom do for iCol := Args.VLeft to Args.VRight do begin Data[i] := 0; strtmp := ACaller.GetSheetValue(Args.VSheet, iCol, iRow).AsString; if trim(strtmp) = '' then Data[i] := 0 else begin strtmp := stringreplace(strtmp, '''', '', [rfReplaceAll]); strtmp := stringreplace(strtmp, ',', '', [rfReplaceAll]); try Data[i] := strtofloat(strtmp); except
end; end; Inc(I);
end; end;
procedure ReadstrArray(var Data: TstrArray; Args: TZcFormulaValue); var iCount1: Integer; iCol, iRow: Integer; I: Integer; begin iCount1 := (Args.VRight - Args.VLeft + 1) * (Args.VBottom - Args.VTop + 1); SetLength(Data, iCount1); I := 0; for iRow := Args.VTop to Args.VBottom do for iCol := Args.VLeft to Args.VRight do begin Data[I] := ACaller.GetSheetValue(Args.VSheet, iCol, iRow).asstring; Inc(I); end;
end;var Data1: TstrArray; Data2: TDataArray; I: Integer;begin if (Args[0].ValueType <> fvArea) and (Args[2].ValueType <> fvArea) then raise Exception.Create('参数必须是范围类型'); // 把区域中的值读到动态数组中 ReadstrArray(Data1, Args[0]); ReadDataArray(Data2, Args[2]);
if High(data1) <> High(data2) then raise Exception.Create('第一个参数，第三个参数必须是范围类型，且范围大小相等！');
Result.ValueType := fvNumber; Result.VNumber := 0; for I := 0 to High(Data1) do begin if Trim(Data1[i]) = Trim(Args[1].vstring) then Result.VNumber := Result.VNumber + Data2[I]; end;
end;
procedure TForm1.FormCreate(Sender: TObject);begin // 注册自定义函数// EjunDataGrid1.Formulas.UserFunctions.Add('fc', [ptNumber, ptNumber], ptNumber, 2, 2, FootCell); EjunGrid1.Formulas.UserFunctions.Add(
//'fc', [ptNumber, ptNumber], ptNumber, 2, 2, FootCell);
'sumif', // 函数名称 [ptRef, ptstring, ptRef], // 定义定义函数的参数类型, 表明这个函数有两个引用类型的参数， // 如果输入其他类型的参数将会产生异常 ptNumber, // 定义返回值的类型 3, 3, // 指明函数参数个数, 最小参数个数和最大参数个数 sumif);end;
procedure TForm1.btn1Click(Sender: TObject);begin ejungrid1.SaveToExcel('d:\a000.xls', 'sheet1');end;
procedure TForm1.btn2Click(Sender: TObject);begin ejungrid1.LoadFromExcel('d:\a000.xls', 'sheet1');end;
procedure TForm1.btn3Click(Sender: TObject);var file1: string;begin dlgOpen1.Filter := '*.xls|*.xls'; if dlgOpen1.Execute then begin file1 := dlgOpen1.FileName; ejungrid1.LoadFromExcel(file1);
end;
end;
end. |
|
|