Delphi关于StringGrid的公用模块

unit SGridFunction;
{*******************************************
模块名称:StringGrid操作函数模块
编写者:Tony
开始日期:2006年12月05日
版本号:v1.0.7
********************************************}
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,SConnect,
Dialogs, Global, Grids, DBGrids, DB, DBClient, WinSkinData, StdCtrls,
Buttons, ExtCtrls, ComCtrls, ComObj, Clipbrd, ADODB;
//成功提示框
//AMsg: 成功提示信息
procedure SuccessMsgbox(AMsg: String );
//错误提示框
//AMsg: 错误提示信息
procedure ErrorMsgbox(AMsg: String );
//询问提示框
//strMsg:询问信息
function AskMsgbox(AMsg: String ): Boolean ;
//信息提示框
//AMsg: 提示信息
procedure InfoMsgbox(AMsg: String );
//导出数据到Excel
//ASGrid:需要导出的StringGrid
//ExcelModalPath: 报表模版全路径,为空则新建工作簿
//ExcelFileName: Excel文件的默认文件名
//AGridStartCol, AGridStartRow: StringGrid的起始列和行,从0开始
//AExcelStartCol, AExcelStartRow: Excel的起始列和行,从1开始
procedure ExportToExcel(ASGrid: TStringGrid; ExcelModalPath, ExcelFileName: String ;
AGridStartCol, AGridStartRow, AExcelStartCol, AExcelStartRow: Integer );
//取得某一列数据的最大长度
//ASgrid: 目标StringGrid
//ACol: 目标列,从0开始
//AStartRow: 起始行,从0开始
function GetColMaxDataLength(ASGrid: TStringGrid; ACol, AStartRow: Integer ): Integer ;
//根据数据长度自动设置指定列的列宽
//ASgrid: 目标StringGrid
//ACol: 目标列,从0开始
procedure SetOneColWidth(ASGrid: TStringGrid; ACol: Integer );
//根据数据长度自动设置全部列的列宽
//ASgrid: 目标StringGrid
procedure SetAllColWidth(ASGrid: TStringGrid);
//显示ClientDataSet中的数据
//ASgrid: 目标StringGrid
//ACDSet: 包含数据的ClientDataSet
//AGridStartCol, AGridStartRow: StringGrid的起始列和行,从0开始
procedure ShowClientDataSetData(ASGrid: TStringGrid; ACDSet: TClientDataSet;
AGridStartCol, AGridStartRow: Integer );
//显示ADOQuery中的数据
//ASgrid: 目标StringGrid
//AQuery: 包含数据的ADOQuery
//AGridStartCol, AGridStartRow: StringGrid的起始列和行,从0开始
procedure ShowQueryData(ASGrid: TStringGrid; AQuery: TADOQuery;
AGridStartCol, AGridStartRow: Integer );
//判断是否含有数据
//true: 包含数据
//false: 不包含数据
//ASgrid: 目标StringGrid
//AStartCol, AStartRow: 起始列和行,从0开始
function HaveData(ASGrid: TStringGrid; AStartCol, AStartRow: Integer ): Boolean ;
//取得9位以内整数位数
//>=1: 该整型数的长度
//0: 空值
//-1: 长度超过9位
//ANumber: 需要判断位数的整型数
function GetIntegerNumberLength(ANumber: Integer ): Integer ;
//为指定的序号列赋值
//序号编号从1开始
//ASGrid: 目标StringGrid
//ACol: 目标列,从0开始
//AStartRow: 起始行,从0开始
procedure SetNumberFields(ASGrid: TStringGrid; ACol, AStartRow: Integer );
//设置指定的列的对齐方式为右对齐
//实现方式为在数据左边补空格,所以取数据时应注意去掉空格
//ASGrid: 目标StringGrid
//ACol: 目标列,从0开始
//AStartRow: 起始行,从0开始
procedure SetColAlignRight(ASGrid: TStringGrid; ACol, AStartRow: Integer );
//设置指定行的左边距
//ASGrid: 目标StringGrid
//ARow: 目标行,从0开始
procedure SetRowLeftSpace(ASGrid: TStringGrid; ARow, SpaceLength: Integer );
//设置指定行的最小右边距
//ASGrid: 目标StringGrid
//ARow: 目标行,从0开始
procedure SetRowMinRightSpace(ASGrid: TStringGrid; ARow, SpaceLength: Integer );
//设置指定行的最小边距
//ASGrid: 目标StringGrid
//ARow: 目标行,从0开始
procedure SetRowMinSpaceWidth(ASGrid: TStringGrid; ARow, SpaceLength: Integer );
//获得当前X坐标所在的列
//ASGrid: 目标StringGrid
//AX: 坐标的X值
function GetColByCX(ASGrid: TStringGrid; AX: Integer ): Integer ;
//获得当前Y坐标所在的行
//ASGrid: 目标StringGrid
//AY: 坐标的Y值
function GetRowByCY(ASGrid: TStringGrid; AY: Integer ): Integer ;
//获得当前坐标所处的单元格的行列值
//ASGrid: 目标StringGrid
//AX, AY: 坐标的X,Y值
//ACol, ARow: 单元格的列,行索引,从0开始
procedure GetCellByCoordinate(ASGrid: TStringGrid; AX, AY: Integer ;
out ACol, ARow: Integer );
//填充空的单元格为指定值
//ASGrid: 目标StringGrid
//AStartCol, AStartRow: 开始列和行,从0开始
//AEndCol, AEndRow: 结束列和行
//AValue: 填充值
procedure SetSpaceCells(ASGrid: TStringGrid; AStartCol, AStartRow,
AEndCol, AEndRow: Integer ; AValue: String );
implementation
//----------------------------------------------------------------------------//
//成功提示框
//----------------------------------------------------------------------------//
procedure SuccessMsgbox(AMsg: String );
begin
Application . MessageBox( Pchar (AMsg), '完成' , MB_ICONINFORMATION + MB_OK);
end ;
//----------------------------------------------------------------------------//
//错误提示框
//----------------------------------------------------------------------------//
procedure ErrorMsgbox(AMsg: String );
begin
Application . MessageBox( Pchar (AMsg), '错误' , MB_ICONSTOP + MB_OK);
end ;
//----------------------------------------------------------------------------//
//询问提示框
//----------------------------------------------------------------------------//
function AskMsgbox(AMsg: String ): Boolean ;
begin
if Application . MessageBox( Pchar (AMsg), '确认' ,
MB_ICONQUESTION + MB_YESNO) = IDYES then
begin
result := true ;
end
else begin
result := false ;
end ;
end ;
//----------------------------------------------------------------------------//
//消息提示框
//----------------------------------------------------------------------------//
procedure InfoMsgbox(AMsg: String );
begin
Application . MessageBox( Pchar (AMsg), '提示' , MB_ICONINFORMATION + MB_OK);
end ;
//----------------------------------------------------------------------------//
//导出数据到Excel
//----------------------------------------------------------------------------//
procedure ExportToExcel(ASGrid: TStringGrid; ExcelModalPath, ExcelFileName: String ;
AGridStartCol, AGridStartRow, AExcelStartCol, AExcelStartRow: Integer );
var
ExcelApp: Variant;
ColIndex, RowIndex: Integer ;
OneRowData: String ; //单行数据
DataList: TStringList; //所有数据
SaveDlg: TSaveDialog; //保存对话框
SaveExcelFilePath: String ; //Excel文件的保存路径
begin
try
//没有数据时直接退出
if not HaveData(ASGrid, AGridStartCol, AGridStartRow) then
begin
InfoMsgBox( '没有数据需要导出。' );
exit;
end ;
//选择保存路径
try
SaveDlg := TSaveDialog . Create(ASGrid); //创建保存窗口对象
SaveDlg . InitialDir := ExtractFilePath(Application . ExeName); //文件保存在当前目录
SaveDlg . Filter := 'Excel Files(*.xls)| *.xls' ; //文件类型过滤
SaveDlg . FileName := ExcelFileName + VarToStr(date); //定义默认文件名
if SaveDlg . Execute then
begin
SaveExcelFilePath := SaveDlg . FileName; //保存文件路径
end
else begin
exit; //放弃导出
end ;
finally
SaveDlg . Free; //释放对象
end ;
//创建Excel对象
try
ExcelApp := CreateOleObject( 'Excel.Application' ); //创建新Excel对象
except
ErrorMsgBox( '请确认您的机器已经安装 Microsoft Excel 。' );
Exit;
end ;
try
//打开Excel工作簿
try
//打开报表模版
if (excelModalPath <> null) and (excelModalPath <> '' ) then
begin
ExcelApp . WorkBooks . Open(ExcelModalPath);
end
//添加新工作簿
else begin
ExcelApp . WorkBooks . Add;
//设置列宽
for ColIndex := 0 to ASGrid . ColCount - AGridStartCol - 1 do
begin
//此处不能使用ASGrid.ColWidths[AGridStartCol + ColIndex];
ExcelApp . ActiveSheet . Columns[AExcelStartCol + ColIndex].ColumnWidth
:= GetColMaxDataLength(ASGrid, AGridStartCol + ColIndex, AGridStartRow);
end ;
//数字 NumberFormatLocal = "0.00_ "
//日期 NumberFormatLocal = "yyyy-m-d"
//时间 NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
//文本 NumberFormatLocal = "@"
ExcelApp . Cells . NumberFormatLocal := '@' ; //设置单元格为文本格式
end ;
ExcelApp . WorkSheets[ 1 ].Activate; //设置第1个工作表为活动工作表
ExcelApp . Cells . Item[AExcelStartRow, AExcelStartCol].Select; //设定Excel起始单元格
except
ErrorMsgBox( '无法打开报表模版:' + # 13 + ExcelModalPath); //#13为回车换行
exit;
end ;
try
//通过剪切板导出数据
try
//初始化DataList
DataList := TStringList . Create;
DataList . Clear;
//将数据保存到DataList
with ASGrid do
begin
//行
for RowIndex := AGridStartRow to RowCount - 1 do
begin
OneRowData := '' ;
//列
for ColIndex := AGridStartCol to ColCount - 1 do
begin
OneRowData := OneRowData + Trim(Cells[ColIndex, RowIndex]) + # 9 ; //#9为制表符
end ;
DataList . Add(OneRowData); //将该行数据添加到DataList
end ;
end ;
ClipBoard . AsText := DataList . Text; //将DataList中的数据拷贝到剪切板
ExcelApp . ActiveSheet . Paste; //将剪切板中的数据拷贝到Excel
finally
DataList . Free; //释放DataList
ClipBoard . Clear; //清空剪切板
end ;
//保存Excel文件
ExcelApp . ActiveWorkbook . SaveAs(SaveExcelFilePath); //另存为到指定目录
SuccessMsgBox( '成功将文件保存到:' + # 13 + SaveExcelFilePath); //保存成功
finally
ExcelApp . DisplayAlerts := false ; //不弹出保存提示对话框
ExcelApp . WorkBooks . Close; //关闭工作簿
end ;
finally
ExcelApp . Quit; //关闭Excel进程
ExcelApp:= Unassigned; //释放ExcelApp
end ;
except
On e: Exception do
begin
ErrorMsgbox(e . Message);
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//取得某一列数据的最大长度
//----------------------------------------------------------------------------//
function GetColMaxDataLength(ASGrid: TStringGrid; ACol, AStartRow: Integer ): Integer ;
var
ColIndex, RowIndex: Integer ;
MaxColLength: Integer ; //列数据的最大长度
begin
MaxColLength := 0 ;
with ASGrid do
begin
//取得列数据的最大长度
for RowIndex := AStartRow to RowCount - 1 do
begin
if length(Cells[ACol, RowIndex]) > MaxColLength then
begin
MaxColLength:= length(Cells[ACol, RowIndex]);
end ;
end ;
end ;
result := MaxColLength;
end ;
//----------------------------------------------------------------------------//
//根据数据长度自动设置指定列的列宽
//----------------------------------------------------------------------------//
procedure SetOneColWidth(ASGrid: TStringGrid; ACol: Integer );
var
OneCharPixel: Integer ; //一个字符所占的像素数
RightSpaceWidth: Integer ; //右边距空隙
begin
RightSpaceWidth := 3 ; //设置为3达到和左边距一致的效果
OneCharPixel := 6 ; //6对应9号字[*此处最好写成一个根据字号获得像素值的函数*]
ASGrid . ColWidths[ACol] := GetColMaxDataLength(ASGrid, ACol, 0 ) * OneCharPixel
+ RightSpaceWidth;
end ;
//----------------------------------------------------------------------------//
//根据数据长度自动设置全部列的列宽
//----------------------------------------------------------------------------//
procedure SetAllColWidth(ASGrid: TStringGrid);
var
ColIndex: Integer ; //需要设置的列
begin
for ColIndex := 0 to ASGrid . ColCount - 1 do
begin
SetOneColWidth(ASGrid, ColIndex);
end ;
end ;
//----------------------------------------------------------------------------//
//显示ClientDataSet中的数据
//----------------------------------------------------------------------------//
procedure ShowClientDataSetData(ASGrid: TStringGrid; ACDSet: TClientDataSet;
AGridStartCol, AGridStartRow: Integer );
var
ColIndex: Integer ;
RowIndex: Integer ;
begin
try
with ASGrid do
begin
//没有记录时,清空StringGrid并返回
if ACDSet . RecordCount <= 0 then
begin
RowCount := 2 ;
for ColIndex := 0 to ColCount - 1 do
begin
Cells[ColIndex, 1 ] := '' ;
end ;
exit;
end ;
RowCount := AGridStartRow + ACDSet . RecordCount; //StringGrid行数
ColCount := AGridStartCol + ACDSet . FieldCount; //StringGrid列数
RowIndex := AGridStartRow; //当前行为起始行
while not ACDSet . Eof do
begin
//显示数据
for ColIndex := AGridStartCol to ColCount - 1 do
begin
Cells[ColIndex, RowIndex]
:= ACDSet . Fields . Fields[ColIndex - AGridStartCol].AsString
end ;
//转到下一行
RowIndex := RowIndex + 1 ;
ACDSet . Next;
end ;
end ;
except
On e: Exception do
begin
ErrorMsgBox(e . Message);
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//显示ADOQuery中的数据
//----------------------------------------------------------------------------//
procedure ShowQueryData(ASGrid: TStringGrid; AQuery: TADOQuery;
AGridStartCol, AGridStartRow: Integer );
var
ColIndex: Integer ;
RowIndex: Integer ;
begin
try
with ASGrid do
begin
//没有记录时,清空StringGrid并返回
if AQuery . RecordCount <= 0 then
begin
RowCount := 2 ;
for ColIndex := 0 to ColCount - 1 do
begin
Cells[ColIndex, 1 ] := '' ;
end ;
exit;
end ;
RowCount := AGridStartRow + AQuery . RecordCount; //StringGrid行数
ColCount := AGridStartCol + AQuery . FieldCount; //StringGrid列数
RowIndex := AGridStartRow; //当前行为起始行
while not AQuery . Eof do
begin
//显示数据
for ColIndex := AGridStartCol to ColCount - 1 do
begin
Cells[ColIndex, RowIndex]
:= AQuery . Fields . Fields[ColIndex - AGridStartCol].AsString
end ;
//转到下一行
RowIndex := RowIndex + 1 ;
AQuery . Next;
end ;
end ;
except
On e: Exception do
begin
ErrorMsgBox(e . Message);
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//判断是否含有数据
//----------------------------------------------------------------------------//
function HaveData(ASGrid: TStringGrid; AStartCol, AStartRow: Integer ): Boolean ;
var
ColIndex, RowIndex: Integer ;
begin
with ASgrid do
begin
for ColIndex := AStartCol to ColCount - 1 do
begin
for RowIndex := AStartRow to RowCount - 1 do
begin
//包含数据,返回true
if Cells[ColIndex, RowIndex] <> '' then
begin
result := true ;
exit;
end ;
end ;
end ;
end ;
//没有数据,返回false
result := false ;
end ;
//----------------------------------------------------------------------------//
//取得9位以内整数位数
//----------------------------------------------------------------------------//
function GetIntegerNumberLength(ANumber: Integer ): Integer ;
var
IsNegativeNumber: Boolean ; //参数的正负,负数为true
LoopIndex: Integer ; //循环变量
ComporeNumber: Integer ; //用于比较的数
NumberLength: Integer ; //返回值,长度大于10返回-1
begin
if ANumber = null then
begin
result := 0 ; //空值返回0
exit;
end ;
//判断参数的正负
if ANumber < 0 then
begin
ANumber := 0 - ANumber; //转换成正数用于计算长度
IsNegativeNumber := true ; //是负数
end
else begin
if ANumber = 0 then
begin
result := 1 ; //是0,直接返回1
exit;
end ;
IsNegativeNumber := false ; //是正数
end ;
//开始比较
ComporeNumber:= 10 ;
for LoopIndex:= 1 to 9 do
begin
//长度符合要求
if (ComporeNumber div ANumber) > 0 then
begin
//得到长度
if ComporeNumber = ANumber then NumberLength := LoopIndex + 1
else NumberLength := LoopIndex;
//如果是负数,则长度加1,即包含负号
if IsNegativeNumber then result:= NumberLength + 1
else result := NumberLength;
exit;
end ;
//增大1位继续比较
ComporeNumber := ComporeNumber * 10 ;
continue;
end ;
result := - 1 ; //长度大于9,返回-1
end ;
//----------------------------------------------------------------------------//
//为指定的序号列赋值
//----------------------------------------------------------------------------//
procedure SetNumberFields(ASGrid: TStringGrid; ACol, AStartRow: Integer );
var
RowIndex: Integer ; //当前序号
begin
with ASGrid do
begin
for RowIndex := 1 to RowCount - AStartRow do
begin
//添加序号
Cells[ACol, AStartRow + RowIndex - 1 ] := VarToStr(RowIndex);
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//设置指定的列的对齐方式为右对齐
//----------------------------------------------------------------------------//
procedure SetColAlignRight(ASGrid: TStringGrid; ACol, AStartRow: Integer );
var
RowIndex: Integer ;
MaxDataLength: Integer ; //该列最大的数据长度
begin
MaxDataLength := GetColMaxDataLength(ASGrid, ACol, 0 ); //取得该列最大的数据长度
with ASGrid do
begin
for RowIndex := AStartRow to RowCount - 1 do
begin
while length(Cells[ACol, RowIndex]) < MaxDataLength do
begin
Cells[ACol, RowIndex] := ' ' + Cells[ACol, RowIndex]; //在前面补空格
end ;
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//设置指定行的左边距
//----------------------------------------------------------------------------//
procedure SetRowLeftSpace(ASGrid: TStringGrid; ARow, SpaceLength: Integer );
var
ColIndex, LoopIndex: Integer ;
begin
with ASGrid do
begin
for ColIndex := 0 to ColCount - 1 do
begin
Cells[ColIndex, ARow] := TrimLeft(Cells[ColIndex, ARow]); //去掉左边空格
for LoopIndex := 1 to SpaceLength do
begin
Cells[ColIndex, ARow] := ' ' + Cells[ColIndex, ARow]; //在左边补空格
end ;
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//设置指定行的最小右边距
//----------------------------------------------------------------------------//
procedure SetRowMinRightSpace(ASGrid: TStringGrid; ARow, SpaceLength: Integer );
var
ColIndex, LoopIndex: Integer ;
begin
with ASGrid do
begin
for ColIndex := 0 to ColCount - 1 do
begin
Cells[ColIndex, ARow] := TrimRight(Cells[ColIndex, ARow]); //去掉右边空格
for LoopIndex := 1 to SpaceLength do
begin
Cells[ColIndex, ARow] := Cells[ColIndex, ARow] + ' ' ; //在右边补空格
end ;
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//设置指定行的最小边距
//----------------------------------------------------------------------------//
procedure SetRowMinSpaceWidth(ASGrid: TStringGrid; ARow, SpaceLength: Integer );
var
ColIndex, LoopIndex: Integer ;
begin
with ASGrid do
begin
for ColIndex := 0 to ColCount - 1 do
begin
Cells[ColIndex, ARow] := Trim(Cells[ColIndex, ARow]); //去掉两边空格
for LoopIndex := 1 to SpaceLength do
begin
Cells[ColIndex, ARow] := ' ' + Cells[ColIndex, ARow] + ' ' ; //在两边补空格
end ;
end ;
end ;
end ;
//----------------------------------------------------------------------------//
//获得当前X坐标所在的列
//----------------------------------------------------------------------------//
function GetColByCX(ASGrid: TStringGrid; AX: Integer ): Integer ;
var
ColIndex: Integer ;
CurCellRect: TRect; //当前列的矩形区域
begin
with ASGrid do
begin
for ColIndex := 0 to ColCount - 2 do
begin
CurCellRect := CellRect(ColIndex, 0 );
//当前列被隐藏,继续判断下一列
if CurCellRect . Left = CurCellRect . Right then continue;
//X坐标在当前列的范围内
if (AX >= CurCellRect . Left) and (AX < CurCellRect . Right) then
begin
result := ColIndex;
exit;
end ;
end ;
result := ColCount - 1 ; //返回最后一列的索引
end ;
end ;
//----------------------------------------------------------------------------//
//获得当前Y坐标所在的行
//----------------------------------------------------------------------------//
function GetRowByCY(ASGrid: TStringGrid; AY: Integer ): Integer ;
var
RowIndex: Integer ;
CurCellRect: TRect; //当前行的矩形区域
begin
with ASGrid do
begin
for RowIndex := 0 to RowCount - 2 do
begin
CurCellRect := CellRect( 0 , RowIndex);
//当前行被隐藏,继续判断下一行
if CurCellRect . Top = CurCellRect . Bottom then continue;
//Y坐标在当前行的范围内
if (AY > CurCellRect . Top) and (AY < CurCellRect . Bottom) then
begin
result := RowIndex;
exit;
end ;
end ;
result := RowCount - 1 ; //返回最后一行的索引
end ;
end ;
//----------------------------------------------------------------------------//
//获得当前坐标所处的单元格的行列值
//----------------------------------------------------------------------------//
procedure GetCellByCoordinate(ASGrid: TStringGrid; AX, AY: Integer ;
out ACol, ARow: Integer );
begin
ACol := GetColByCX(ASGrid, AX); //取得列索引
ARow := GetRowByCY(ASGrid, AY); //取得行索引
end ;
//----------------------------------------------------------------------------//
//填充空的单元格为指定值
//----------------------------------------------------------------------------//
procedure SetSpaceCells(ASGrid: TStringGrid; AStartCol, AStartRow,
AEndCol, AEndRow: Integer ; AValue: String );
var
ColIndex, RowIndex: Integer ;
begin
with ASGrid do
begin
for ColIndex := AStartCol to AEndCol do
begin
for RowIndex := AStartRow to AEndRow do
begin
//单元格为空(不含空格)时填充
if Trim(Cells[ColIndex, RowIndex]) = '' then
begin
Cells[ColIndex, RowIndex] := AValue;
end ;
end ;
end ;
end ;
end ;
end .
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值