于Excel对象的属性！ - 达人(在海水中游泳)

2005/08/12 | 于Excel对象的属性！
类别(语言类学习笔记) | 评论(0) | 阅读(1110) | 发表于 00:28: {*******************************************************************************

Author： WangLe
Build Date：2004.12.30

*******************************************************************************}
unit uTpe_CustFunction; //uTpe_NzjjDouMon

interface

uses Windows, Messages, Controls, Classes, SysUtils, Dialogs, Forms, ADODB,Grids,
StrUtils,DBGridEH,ComObj, StdCtrls,DAO2000,db,Variants,Graphics,DBGrids;
type
pnode=^lnode;
lnode=record
Fcaption,Fieldname,Fwidth,Fread,Ffcolor,Fbcolor,Fime,Fali,Flay,Fklist,Fplist,Fstyle : string;
end;

Tobj =Class // Tobject 节点对象
private
Fivalue: integer;
FsS : string;
FiYear: integer;
FsID : string;
FsStrID: string;
FbFlag: Boolean;
Fsc :string;
public
property iv: integer Read Fivalue Write Fivalue;
property sf: string Read FsS Write FsS;
property iYear: integer Read FiYear Write FiYear;
property sID : string Read FsID Write FsID;
property strID: string Read FsStrID write FsStrID;
property sc:string read Fsc write Fsc;
property flag : Boolean Read FbFlag write FbFlag;
end;

//这里的DBGrid 是从Excel中导入的数据(一定要注意),导入到调用表单的DBGrid的目标数据集中
TCustprocess = procedure (sourceData: Tdataset;var Resultstr: string) of object ;

//统计类似字段的个数类型已确定
function GetCount(aqrData: TadoQuery; Str: string): integer;

//导出 Excel 多层标头带数据
procedure ExportDataToExcelMTitle(sWprCode :string;MainForm: TForm; paraDBGrid: TDBGridEh; MyCaption,lefttop,middletop,righttop,leftbottom,middlebottom,rightbottom,TaxCode: string);

//导出Excel 多层标头样式表没有数据
procedure ExportDataToExcelMTitleStyle(MainForm: TForm; paraDBGrid: TDBGridEh; MyCaption,lefttop,middletop,rightTop: string);

//导入Excel 多层标头及数据
procedure ImportDatafromExcelMTitle(sWprCode,sTaxCode :string;paraDBGrid: TDBGridEh;CustCLdata: TcustProcess);
function CheckColCount(ntype: integer): string; //专用子函数3 根据列数转化为 A1 AB1 AZ1
function CountChar(cStr: string;cChar: Char): Word; // 专用子函数1 含有一个 | 就是两层
function GetTitleNCaption(sCaption: string; SubStr :Char; CapLine: Word): string;//专用子函数 2：取第几层表头的字符比如A|B|C 3 2 ==> B
function GetTitleLastCaption(str: string;substr: char): string; // 把 CountChar 和 GetCap 结合起来使用

//统计substr的个数
function SubStrCount(sub: string;str: string): integer;

//填充数据
procedure FillCells(DBGridEh1: TDBGridEh);
procedure Datachange(DBGridEh: TDBGridEh;value: variant;flag: integer;fname: string);

//不能直接确定下来的税目
function Tpe_GetSlKcsCMN( JsYj: Currency; TaxCode,
ItmCode: string; var Sl: Currency; var Kcs: Integer; Yf: integer): Boolean;

//直接超额累进直接确定下来了税目个人工薪所得 060100 供工薪所得和年终奖金专用
function Tpe_GetSlKcsGX( JsYj: Currency; TaxCode,
ItmCode: string; var Sl: Currency; var Kcs: Integer; Yf: integer; Flag: Boolean=False): Boolean;

//获取纳税人年度参数
function Tpe_GetNsrNdCsInt(NsrID: String; DataYear,ID:integer):integer;

//*****************
//判断基础数据是否有重复
function CheckDataWYX(sWprCode,sNsrID,sTaxCode:string; iWC,iDataYear:integer; YF:integer=0):Boolean;

//监测月份数据是否合法
function Tpe_CheckYF(YF:integer):Boolean;overload;
function Tpe_CheckYF(YF:String):Boolean;overload;

//年终奖金调取数据的实施部分
function Tpe_addNzjjXM(sTaxCode,sWprCode,sNsrID,sqlGxsd,sqlself:string;iDataYear,iWprTblID:integer;Dou:Boolean) :Boolean;

//双薪调取数据的实施部分
function Tpe_addDXXM(sTaxCode,sWprCode,lWprCode,sNsrID,sqlGxsd,sqlself:string;iDataYear,iWprTblID:integer) :Boolean;

//为数据定义byNsrYear 工作底稿或计算附表添加新tableID 赋值小计个人合计
function C_SetDataGridStr(sNsrID,SqlStr,GrdStr,Getstr:string;iDataYear,iTblID:Integer):Boolean;
//为数据定义byNsrYear 工作底稿或计算附表添加新tableID 赋值
function WC_CheckAddDataGridStr(sNsrID:string;iDataYear,ITblID,nType:Integer):Boolean;
function WC_GetDataGridStr(sNsrID:string;iDataYear,iTblID:Integer;var SqlStr,GridStr,SetText,GetText :string):Boolean;
function WC_SetDataGridStr(sNsrID:string;iDataYear,iTblID:Integer;var SqlStr,GridStr,SetText,GetText :string):Boolean;

//取得最大的tableID
function GetMaxTableID(sWprCode,sNsrID :string; iDataYear:Integer):integer;
//为工作底稿参数表设置默认值
function Wpr_SetDefaultvalue(OldTableID,NewTbleID:Integer;str:string):Boolean;
//删除工作底稿和计算附表、工作底稿参数
function WprCtbCS_Delete(sNsrID:string;iDataYear,ITblID:Integer):Boolean;

function Tpe_SetJSMonth (sWprCode,sNsrID,sTaxCode :string; IDataYear:Integer) :Boolean;

//用stringlist 生成 [];[];
function MakeGridStyleStr (ts: Tstrings) : string;
//有串生成 Tstrings
procedure MakeGridStyleStrLists(sGridStr: string; ts: TStrings);

//显示小月合计个人合计排序用表单
procedure TpeSubForm(sqlStr,Grdstr,Cap,sOrder,Tax,Gtxt:String;Flag:Boolean);

function SetColV(i,j:integer; DbGrid:TDBgridEH):Boolean;

// 5---6
function changeID(liTblID:integer) :Integer;
//有tableID 取得 WprCode
function GetWprCode(iTblID:Integer):string;

const

Tpe_sGxsdItem = '0601'; //工薪所得
Sds_Fy = '0460'; //普通费用
Sds_Fy3 = '0450'; //三项福利费
Char_ND = ' 年度' ; //年度列表的串管理
Char_ND2 = '所有年度';
sWhereWprYearNsr = ' Where WprCode="%s" and DataYear=%d and NsrID="%s" ';

sAndTaxWtbSta = ' and TaxCode="%s" and WprTblID=%d and State=%d '; //sJsStr //计算用附加条件
sAndTaxCtbSta = ' and TaxCode="%s" and CtbTblID=%d and State=%d '; //sJsStr //计算用附加条件

sAndTaxWtb = ' and TaxCode="%s" and WprTblID=%d '; //sJsStr //计算用附加条件
sAndTaxCtb = ' and TaxCode="%s" and CtbTblID=%d '; //sJsStr //计算用附加条件

sAndWprLineSign = ' and Sign_Wpr=%d '; //工作底稿行标示 = %d
sAndCtbLineSign = ' and Sign_Ctb=%d '; //工作底稿行标示 = %d

Tpe_DouMonthNum =62;
Tpe_NzjjMonthNum =63;
Tpe_Data_SQL_Wpr = 'Select * from %s where &[WhereTaxWpr] and &[WhereNsrYear] and WprTblID=1 and (Sign_Wpr=&[Sign_Sj] or Sign_Wpr=&[Sign_Xj]) order by NsrID, DataYear,序号,Sign_Wpr Desc ';
Tpe_Data_SQL_Ctb = 'Select * from %s where &[WhereTaxWpr] and &[WhereNsrYear] and CtbTblID=1 and (Sign_Ctb=&[Sign_Sj] or Sign_Ctb=&[Sign_Hj]) order by NsrID, DataYear,序号,Sign_Ctb Desc ';
Tpe_Gxsd_SrCount =30; //30个收入
Tpe_Gxsd_ZcCount =10; //10个支出其中Cur1 放起征点
Tpe_CtbSign_PersonalTotal=100;//工薪的个人合计放在了工薪模块中
Tpe_CtbSign_PersonalMonth=200;//特种行业年薪的计税月数

Last_Length =1; // 将来是 2 5 0601 1 - 5 0601 01
First_Length =4; // 将来是 5 5 0601 1 - 5 06001 01
Tpe_WprMb0 =1111; // 保存工作底稿语句
Tpe_CtbMb0 =4444; // 保存计算附表语句
Tpe_WprMb1 =2222; // 保存分项底稿的语句
Tpe_WprMb2 =3333; // 暂不需要保存新政策的语句的其他收入

Tpe_CtbMonth =55555; // 小月合计
Tpe_CtbPer =66666; // 个人合计

//还有那个分类统计也是合计 Pc_Sign_Hj

implementation

uses uCmn_Common, uCmn_FuncProc, uCmn_DM_Dsr,uTpe_ChooseExcelFile,uTpe_Fill,
uTpe_ImpExcelSetCol,uTpe_ChooseImportData,uCmn_RegInfo,uTpe_GxsdSub;

function GetWprCode(iTblID:Integer):string;
begin
result:=Copy(IntToStr(iTblID),2,First_Length);
end;

function Tpe_SetJSMonth (sWprCode,sNsrID,sTaxCode :string; IDataYear:Integer) :Boolean;
var
qry,tmp:TADOQuery;
sSQL:string;
begin
qry:=TADOQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

tmp:=TADOQuery.Create(nil);
tmp.Connection:=dmCmn_DM_Dsr.DsrConnect;
result:=True;
try
dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
sSQL:='Select TaxCode,WprCode,NsrID,DataYear,Sign_Ctb,Txt10_1,Int2 From %s'; // 标示计税月份
sSql:=Format(sSQL+sWhereWprYearNsr+' and TaxCode="%s" and Sign_Ctb=%d ',
[GetTablename(112),sWprCode,iDataYear,sNsrID,sTaxCode,Tpe_CtbSign_PersonalMonth]);
if not MyOpenExecSQL(qry,sSQL,0) then Exit;

sSQL:='Select Txt10_1,Max(Int2) as MaxYF From %s'; // 标示计税月份
sSql:=Format(sSQL+sWhereWprYearNsr+' and TaxCode="%s" and Sign_Wpr=%d and state=%d Group by Txt10_1 order by first(月份) ',
[GetTablename(112),sWprCode,iDataYear,sNsrID,sTaxCode,Pc_Sign_Sj,Pc_State_Qd]);
if not MyOpenExecSQL(tmp,sSQL,0) then Exit;

qry.First;
while not qry.Eof do
begin
if not tmp.Locate('Txt10_1',qry.fieldbyname('Txt10_1').AsString,[])
then qry.Delete
else qry.Next;
end;

tmp.First;
while not tmp.Eof do
begin
if not qry.Locate('Txt10_1',tmp.fieldbyname('Txt10_1').AsString,[]) then
begin
qry.Append;
qry.Fieldvalues['TaxCode']:=sTaxCode;
qry.Fieldvalues['WprCode']:=sWprCode;
qry.Fieldvalues['NsrID']:=sNsrID;
qry.Fieldvalues['DataYear']:=IDataYear;
qry.Fieldvalues['Sign_Ctb']:=Tpe_CtbSign_PersonalMonth;
qry.Fieldvalues['Txt10_1']:=Tmp.FieldByName('Txt10_1').AsString;
qry.Fieldvalues['Int2']:=Tmp.FieldByName('MaxYF').AsString;
qry.Post;
end;
tmp.Next;
end;
dmCmn_DM_Dsr.DsrConnect.CommitTrans;
except
result:=False;
dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
finally
qry.Free;
tmp.Free;
end;
end;

//为工作底稿参数表设置默认值
function Wpr_SetDefaultvalue(OldTableID,NewTbleID:Integer;str:string):Boolean;
var
qry ,qryTmp: TadoQuery;
sql : String;
i:integer;
begin
qry:=TadoQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

qryTmp:=TadoQuery.Create(nil);
qryTmp.Connection:=dmCmn_DM_Dsr.DsrConnect;

try
sql:=Format('Select * from %s Where TableID=%d ',
[GetTableName(101),OldTableID]);
MyOpenExecSql(qry,sql,0);

if qry.RecordCount=0 then
begin
Result := False;
Exit;
end else
begin
qryTmp.Clone(qry);
sql:=Format('Select * from %s Where TableID=%d ',
[GetTableName(101),NewTbleID]);
MyOpenExecSql(qry,sql,0);
//dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
if qry.recordCount=0
then qry.Append
else qry.Edit;
for i:=0 to qryTmp.FieldCount-1 do
qry.Fieldvalues[qryTmp.Fields.Fields[i].FieldName]:=qryTmp.Fields.Fields[i].value;
qry.fieldvalues['PageCaption']:=str;
qry.Fieldvalues['TableID']:=NewTbleID;
// 将来要改成2
qry.Fieldvalues['WprTblID']:=StrToInt(rightstr(inttostr(NewTbleID),1));
qry.Post;
//dmCmn_DM_Dsr.DsrConnect.CommitTrans;
Result := True;
except
Result:= False;
//dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
end;
finally
qry.Free;
qryTmp.Free;
end;
end;

//为数据定义byNsrYear 工作底稿或计算附表添加新tableID 赋值
function WC_CheckAddDataGridStr(sNsrID:string;iDataYear,iTblID,nType:Integer):Boolean;
var
qry ,qryTmp: TadoQuery;
sql : String;
i:integer;
begin
qry:=TadoQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

qryTmp:=TadoQuery.Create(nil);
qryTmp.Connection:=dmCmn_DM_Dsr.DsrConnect;

try
//dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
sql:=Format('Select * from %s Where NsrID="%s" and DataYear=%d and TableID=%d ',
[GetTableName(63), sNsrID, iDataYear,iTblID]);
MyOpenExecSql(qry,sql,0);

if qry.RecordCount=0 then
begin
if leftstr(inttostr(itblid),1)='5' then
begin
case ntype of
0:begin
// 只能是第一个底稿自动添加
if StrToInt(rightstr(IntToStr(ITblID),Last_Length))=1
then i:=Tpe_WprMb0
else Exit;
end;
1: i:=Tpe_WprMb1;
2: i:=Tpe_WprMb2;
end;
end else
case ntype of
0:begin
// 只能是第一个附表自动添加
if StrToInt(rightstr(IntToStr(ITblID),Last_Length))=1
then i:=Tpe_CtbMb0
else Exit;
end;
//其他附表不出附表
1: Exit;
2: Exit;
end;

sql:=Format('Select * from %s Where TableID=%d ',
[GetTableName(61),i]);

MyOpenExecSql(qryTmp,sql,0);
if qryTmp.RecordCount=0 then
begin
Result := False;
Exit;
end else
begin
qry.Append;
qry.fieldvalues['DataSql']:= QryTmp.FieldByname('DataSql').AsString;
qry.fieldvalues['GridStr']:=QryTmp.FieldByname('GridStr').AsString;
qry.fieldvalues['FieldSetTexts']:=QryTmp.FieldByname('FieldSetTexts').AsString;
qry.fieldvalues['FieldGetTexts']:=QryTmp.FieldByname('FieldGetTexts').AsString;
qry.Fieldvalues['NsrID']:= sNsrID;
qry.Fieldvalues['DataYear']:= iDataYear;
qry.Fieldvalues['TableID']:= iTblID;
qry.Fieldvalues['author']:= QryTmp.FieldByname('author').AsString;
qry.Post;
end;
end;
//dmCmn_DM_Dsr.DsrConnect.CommitTrans;
Result := True;
except
Result:= False;
//dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
finally
qry.Free;
qryTmp.Free;
end;
end;

//为数据定义byNsrYear 工作底稿或计算附表添加新tableID 赋值小计个人合计
function C_SetDataGridStr(sNsrID,SqlStr,GrdStr,Getstr:string;iDataYear,iTblID:Integer):Boolean;
var
qry ,qryTmp: TadoQuery;
sql : String;
i:integer;
begin
qry:=TadoQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

qryTmp:=TadoQuery.Create(nil);
qryTmp.Connection:=dmCmn_DM_Dsr.DsrConnect;

try
//dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
sql:=Format('Select * from %s Where NsrID="%s" and DataYear=%d and TableID=%d ',
[GetTableName(63), sNsrID, iDataYear,iTblID]);
MyOpenExecSql(qry,sql,0);
if qry.RecordCount=0 then
begin
sql:=Format('Select * from %s Where TableID=%d ',[GetTableName(61),Tpe_CtbMb0]);
MyOpenExecSql(qryTmp,sql,0);
if qryTmp.RecordCount=0 then
begin
Result := False;
Exit;
end else
begin
qry.Append;
if SqlStr<>''
then qry.fieldvalues['DataSql']:=SqlStr
else qry.fieldvalues['DataSql']:=QryTmp.FieldByname('DataSql').AsString;
if GrdStr<>''
then qry.fieldvalues['GridStr']:=GrdStr
else qry.fieldvalues['GridStr']:=QryTmp.FieldByname('GridStr').AsString;

qry.fieldvalues['FieldSetTexts']:=QryTmp.FieldByname('FieldSetTexts').AsString;

if Getstr <> ''
then qry.fieldvalues['FieldGetTexts']:=Getstr
else qry.fieldvalues['FieldGetTexts']:=QryTmp.FieldByname('FieldGetTexts').AsString;

qry.Fieldvalues['NsrID']:= sNsrID;
qry.Fieldvalues['DataYear']:= iDataYear;
qry.Fieldvalues['TableID']:= iTblID;
qry.Post;
end;
end else
begin
qry.Edit;
if SqlStr<>'' then qry.fieldvalues['DataSql']:=SqlStr;
if GrdStr<>'' then qry.fieldvalues['GridStr']:=GrdStr;
if GetStr<>'' then qry.fieldvalues['FieldGetTexts']:=Getstr;
qry.Post;
end;
//dmCmn_DM_Dsr.DsrConnect.CommitTrans;
Result := True;
except
Result:= False;
//dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
finally
qry.Free;
qryTmp.Free;
end;
end;

{取得计算附表的语句
function C_GetDataGridStr(sNsrID:string;iDataYear,iTblID:Integer; var SqlStr,GrdStr,Stxt,Gtxt:string):Boolean;
var
qry : TadoQuery;
sql : String;
i:integer;
begin
qry:=TadoQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

SqlStr:='';
GrdStr:='';
Stxt:='';
Gtxt:='';

try
//dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
sql:=Format('Select * from %s Where NsrID="%s" and DataYear=%d and TableID=%d ',
[GetTableName(63), sNsrID, iDataYear,iTblID]);
MyOpenExecSql(qry,sql,0);
if qry.RecordCount=0 then
begin
Result := False;
Exit;
end else
begin
SqlStr := Qry.FieldByname('DataSql').AsString;
GrdStr := Qry.FieldByname('GridStr').AsString;
Stxt := Qry.FieldByname('FieldSetTexts').AsString;
Gtxt := Qry.FieldByname('FieldGetTexts').AsString;
end;
//dmCmn_DM_Dsr.DsrConnect.CommitTrans;
Result := True;
except
Result:= False;
//dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
finally
qry.Free;
end;
end;
}

//取出数据定义byNsrYear 工作底稿或计算附表的值
function WC_GetDataGridStr(sNsrID:string;iDataYear,iTblID:Integer;var SqlStr,GridStr,SetText,GetText :string):Boolean;
var
qry : TadoQuery;
sql : String;
begin

qry:=TadoQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

try
try

SqlStr := '';
GridStr := '';
SetText := '';
GetText := '';

sql:=Format('Select * from %s Where NsrID="%s" and DataYear=%d and TableiD=%d ',
[GetTableName(63), sNsrID, iDataYear,iTblID]);
MyOpenExecSql(qry,sql,0);

if qry.RecordCount=0 then
begin
Result := False;
Exit;
end;

SqlStr := Qry.FieldByname('DataSql').AsString;
GridStr := Qry.FieldByname('GridStr').AsString;
SetText := Qry.FieldByname('FieldSetTexts').AsString;
GetText := Qry.FieldByname('FieldGetTexts').AsString;

Result := True;
except
Result:= False;
end;
finally
qry.Free;
end;

end;

// 取得最大的TableID
function GetMaxTableID(sWprCode,sNsrID :string; iDataYear:Integer):integer;
var
qry:TADOQuery;
sql:string;
i:integer;
begin
qry:=TADOQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;
try
sql:=Format('Select TableID from %s Where Mid(TableID,2,%d)="%s" and NsrID="%s" and DataYear=%d and left(tableID,1)="5" order by TableID ',
[GetTableName(63),First_length,sWprCode,sNsrid,iDataYear]);
MyOpenExecSQL(qry,sql,0);
i:=qry.fieldbyname('TableID').AsInteger;
while qry.Locate('TableID',i,[]) do Inc(i);
case Last_Length of
1: if RightStr( IntToStr(i),Last_Length)='9' then i:=0 ;
2: if RightStr( IntToStr(i),Last_Length)='99' then i:=0 ;
else
i:=0;
end;
Result:=i;
finally
qry.Free;
end;
end;

// 更新串
function WC_SetDataGridStr(sNsrID:string;iDataYear,iTblID:Integer;var SqlStr,GridStr,SetText,GetText :string):Boolean;
var
qry ,qryTmp: TadoQuery;
sql : String;
begin
qry:=TadoQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;

qryTmp:=TadoQuery.Create(nil);
qryTmp.Connection:=dmCmn_DM_Dsr.DsrConnect;

try
try
sql:=Format('Select * from %s Where NsrID="%s" and DataYear=%d and TableiD=%d ',
[GetTableName(63), sNsrID, iDataYear,iTblID]);
MyOpenExecSql(qry,sql,0);

if qry.RecordCount=0 then
begin
result:=False;
Exit;
end;

//dmCmn_DM_Dsr.DsrConnect.BeginTrans;
qry.Edit;
if SqlStr <>'' then Qry.Fieldvalues['DataSql']:=SqlStr;
if GridStr<>'' then Qry.Fieldvalues['GridStr']:=GridStr;
if SetText<>'' then Qry.Fieldvalues['FieldSetTexts']:=SetText;
if GetText<>'' then Qry.Fieldvalues['FieldGetTexts']:=GetText;
qry.Post;
Result := True;
//dmCmn_DM_Dsr.DsrConnect.CommitTrans;
except
Result:= False;
//dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
finally
qry.Free;
qryTmp.Free;
end;
end;

//删除工作底稿和计算附表、工作底稿参数
function WprCtbCS_Delete(sNsrID:string;iDataYear,ITblID:Integer):Boolean;
var
sql:string;
qry:TADOQuery;
begin
result:=False;
qry:=TADOQuery.Create(nil);
qry.Connection:=dmCmn_DM_Dsr.DsrConnect;
try
dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
sql:=Format('delete from %s Where TableID=%d ',
[GetTableName(101),ITblID]);
MyOpenExecSql(qry,sql,0);

sql:=Format('delete from %s Where NsrID="%s" and DataYear=%d and TableID=%d ',
[GetTableName(63),sNsrID,iDatayear,ITblID]);
MyOpenExecSql(qry,sql,0);

sql:=Format('delete from %s Where NsrID="%s" and DataYear=%d and TableID=%d ',
[GetTableName(63),sNsrID,iDatayear,changeID(ITblID)]);
MyOpenExecSql(qry,sql,0);

dmCmn_DM_Dsr.DsrConnect.CommitTrans;
result:=True;
except
dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
result:=False;
end;
finally
qry.Free;
end;
end;

//监测月份数据是否合法
function Tpe_CheckYF(YF:integer):Boolean;overload;
begin
Result:= (YF>0) and (YF<13);
end;

function Tpe_CheckYF(YF:String):Boolean;overload;
begin
Result:=(
(Yf='01') or
(Yf='02') or
(Yf='03') or
(Yf='04') or
(Yf='05') or
(Yf='06') or
(Yf='07') or
(Yf='08') or
(Yf='09') or
(Yf='10') or
(Yf='11') or
(Yf='12')
);
end;

//统计类似字段的个数类型已确定
function GetCount(aqrData: TadoQuery;Str: string): integer;
var
i,j,k: integer;
begin
Str:=UpperCase(trim(Str));
i:=Length(Str);
k:=0;
for j:=0 to aqrData.FieldCount-1 do
if Leftstr(Uppercase(aqrData.Fields.Fields[j].FieldName),i)=Str then k:=k+1;
result:=k;
end;

//导出 Excel 多层标头
procedure ExportDataToExcelMTitle(sWprCode :string; MainForm: TForm; paraDBGrid: TDBGridEh; MyCaption,lefttop,middletop,righttop,leftbottom,middlebottom,rightbottom,TaxCode: string);
// 所属表单所属grid 表格标题左上文本上中文本右上文本左下文本中下文本右下文本
var
odSaveFile : TSaveDialog;
sFileName : string; //Excel文件名
i,j,k : Integer;
temp : Widestring;
tmp : Widestring;
tmp2 : Widestring;
ExcelApp : Variant;
TitleCapLines: integer; //表头层数
titledetail : array of array of array of string;
//sFiledvalue : String;

//07-18
qrysm,qrywt : TADOQuery;
begin
odSavefile:= TSaveDialog.Create(MainForm);
try
odSaveFile.Filter:= 'EXCEL表格文件格式(*.XLS)|*.xls';
odSaveFile.DefaultExt:= '.xls';
odSaveFile.InitialDir:= ExtractFilePath(ParamStr(0));
odSaveFile.FileName:= MyCaption + '.xls';
if odSaveFile.Execute
then sFileName:= odSaveFile.FileName
else Exit;
finally
odSaveFile.Free;
end;
Application.ProcessMessages;

try
ExcelApp := CreateOleObject('Excel.Application');
except
MyShowMsg('调用Excel程序失败，请确认本机是否安装Excel系统！',0 ,'调用失败',0);
Exit;
end;

Screen.Cursor:=crHourGlass;

try
ExcelApp.WorkBooks.Add;
ExcelApp.DisplayAlerts := False ;
ExcelApp.WorkBooks[1].WorkSheets[1].Name := '导出数据';
TitleCapLines:=1; // 确定最大的title的行数此处为默认为一行

tmp:=CheckColCount(paraDBgrid.VisibleColumns.count);
temp:='A1'+': '+tmp+'1'; //

ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,1].value:=MyCaption;
ExcelApp.WorkBooks[1].workSheets[1].range[temp].merge;

ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=14 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.Bold:=True ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do // 确定最大的title的行数
begin
j:= CountChar(paraDBGrid.VisibleColumns[i].Title.Caption,'|');
if TitleCapLines<j then TitleCapLines:=j;
end;

setLength(titledetail,paraDBGrid.VisibleColumns.Count,TitleCapLines,3);

k:=3; //从第三行开始数据行默认从第三行开始
for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
for j:=0 to TitleCapLines-1 do
begin
//取标题单元格的值以及是否需要合并的标志这里暂且设置为空
titledetail[i,j,0]:=GetTitleNCaption(paraDBGrid.VisibleColumns[i].title.Caption,'|',j+1);
titledetail[i,j,1]:='';
titledetail[i,j,2]:='';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[k+j,i+1].value:=titledetail[i,j,0];
end;
end;

for j:=0 to TitleCapLines-1 do //设置是否需要向右合并
for i:=0 to paraDBGrid.VisibleColumns.Count-1-1 do
if (titledetail[i,j,0]=titledetail[i+1,j,0]) and (trim(titledetail[i,j,0])<>'') then titledetail[i,j,1]:='right'; //right 代表向右合并一列

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do //设置是否需要向下合并
for j:=0 to TitleCapLines-1-1 do
if (titledetail[i,j,0]=titledetail[i,j+1,0]) and (trim(titledetail[i,j,0])<>'') then titledetail[i,j,2]:='down'; //down 代表向下合并一列

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do //向下合并单元格
begin
j:=3;
for k:=0 to TitleCapLines-1 do
begin
tmp:=CheckColCount(i+1);
tmp2:=CheckColCount(i+1);
temp:=tmp+intTostr(j)+': '+tmp2+intTostr(j+1);
if titledetail[i,k,2]='down' then ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge; //merge;
inc(j)
end;
end;

j:=3;
for i:=0 to TitleCapLines-1 do //向右合并单元格
begin
for k:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
tmp:=CheckColCount(k+1);
tmp2:=CheckColCount(k+2);
temp:=tmp+intTostr(j)+': '+tmp2+intTostr(j);
if titledetail[k,i,1]='right' then ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge; //merge;
end;
inc(j);
end;

paraDBGrid.DataSource.DataSet.DisableControls;

//导出数据部分

Qrysm:=TADOQuery.Create(nil);
qrywt:=TADOQuery.Create(nil);
qrysm.Connection:=dmCmn_DM_Dsr.DsrConnect;
qrywt.Connection:=dmCmn_DM_Dsr.DsrConnect;
MyOpenExecSQL(qrysm,format('select * from %s ',[Gettablename(31)]),0);
MyOpenExecSQL(qrywt,format('select * from %s ',[Gettablename(104)]),0);
try
i:=0;
k:=3+TitleCapLines;
paraDBGrid.DataSource.DataSet.First;
while not paraDBGrid.DataSource.DataSet.eof do //读取表格数据并写入 Excel
begin
for j := 0 to paraDBGrid.VisibleColumns.Count-1 do
begin
if UpperCase(Trim(paraDbGrid.VisibleColumns.Items[j].FieldName))='STATE' then
begin
if paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).AsInteger=Pc_State_Qd
then ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='确定'
else ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='取消';
end else
if UpperCase(Trim(paraDbGrid.VisibleColumns.Items[j].FieldName))='计税依据' then
begin //个人所得税
if (TaxCode=Pc_TaxCode_Tpe) then //and (sWprCode=Pc_TpeWpr_AA )
if paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).AsCurrency >0
then ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).Asstring
else ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=0;
end else //还要考虑将来变为不是lookUp字段的时候怎么办-？itemCode->itemName
if (sWprCode=PC_TPEWPR_AA) and ( UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='INT3') then
begin //写死 0601 0/1/2#一般工资、薪金/特种行业工资、薪金/年薪制工资、薪金
case paraDBGrid.VisibleColumns.Items[j].Field.value of
0:ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='一般工资、薪金';
1:ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='特种行业工资、薪金';
2:ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:='年薪制工资、薪金';
end;
end else //其他所得
if (sWprCode=PC_TPEWPR_AB) and (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='DT_PROCODE') then
begin //违法事项
if qrywt.Locate('事项代码;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,Pc_TaxCode_Tpe]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrywt.FieldByName('违法事项').AsString;
end else //其他所得
if (sWprCode=PC_TPEWPR_AB) and (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='DT_ITEMCODE') then
begin
if qrysm.Locate('itemCode;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,Pc_TaxCode_Tpe]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrysm.FieldByName('itemName').AsString;
end else // 违规费用
if (sWprCode=PC_SdsWPR_AM) and (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='DT_PROCODE') then
begin
if qrywt.Locate('事项代码;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,Pc_TaxCode_Sds]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrywt.FieldByName('违法事项').AsString;
end else //三项福利费
if (sWprCode=PC_SdsWPR_AK) and (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='DT_ITEMCODE') then
begin
if qrysm.Locate('itemCode;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,Pc_TaxCode_Sds]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrysm.FieldByName('itemName').AsString;
end else //政策性费用
if (sWprCode=PC_SdsWPR_AG) and (UpperCase(paraDbGrid.VisibleColumns.Items[j].FieldName)='DT_ITEMCODE') then
begin
if qrysm.Locate('itemCode;TaxCode',VarArrayOf([paraDBGrid.VisibleColumns.Items[j].Field.value,Pc_TaxCode_Sds]),[]) then
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=qrysm.FieldByName('itemName').AsString;
end else
ExcelApp.WorkBooks[1].WorkSheets[1].Cells[k+i,j+1].value:=paraDbgrid.DataSource.DataSet.FieldByName(paradbgrid.VisibleColumns[j].FieldName).Asstring;
end;
Inc(i);
paraDBGrid.DataSource.DataSet.Next;
end;
finally
qrysm.Free;
qrywt.Free;
end;

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
case paraDBGrid.VisibleColumns[i].Footer.valueType of
fvtNon: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value;
fvtSum: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue;
fvtAvg: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue;
fvtCount: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue;
fvtFieldvalue: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value;
fvtStaticText: ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value;
end;
{
if (paraDBGrid.visibleColumns[i].Footer.valueType=fvtNon) or
(paraDBGrid.visibleColumns[i].Footer.valueType=fvtStaticText) then
ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.value
else
ExcelApp.WorkBooks[1].WorkSheets[1].cells[3+titleCapLines+paraDBgrid.DataSource.DataSet.RecordCount,i+1].value:=paraDBGrid.VisibleColumns[i].Footer.Sumvalue ;
}
tmp:=CheckColCount(paraDBGrid.VisibleColumns.Count);
temp:=chr(ord('A'))+intTostr(3)+': '+tmp+intTostr(3+TitleCapLines-1+paraDBGrid.DataSource.DataSet.recordcount+1); //增加了一行合计行
for i:=1 to 4 do
begin
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].Weight:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].LineStyle:=1;
end;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=9 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;

tmp:=checkColCount(paraDBGrid.VisibleColumns.Count);
temp:=chr(ord('A'))+intTostr(3)+': '+tmp+intTostr(3+TitleCapLines-1);
for i:=1 to 4 do
begin
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].Weight:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[i].LineStyle:=1;
end;

ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=12 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,1]:=lefttop;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,paraDBGrid.VisibleColumns.Count]:=righttop;
if paraDBGrid.VisibleColumns.Count>=7 then //>=6 也可以 //合并右边单元格
begin
tmp:=CheckColCount(paraDBGrid.VisibleColumns.Count-1);
tmp2:=CheckColCount(paraDBGrid.VisibleColumns.Count);
temp:=tmp+intTostr(2)+': '+tmp2+intTostr(2);
ExcelApp.WorkBooks[1].WorkSheets[1].Range[temp].merge;
end;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4,1]:=leftbottom;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4,paraDBGrid.VisibleColumns.Count]:=rightbottom;

if paraDBGrid.VisibleColumns.Count>=7 then //>=6 也可以 //合并右边单元格
begin
tmp:=CheckColCount(paraDBGrid.VisibleColumns.Count-1);
tmp2:=CheckColCount(paraDBGrid.VisibleColumns.Count);
temp:=tmp+intTostr(paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4)+': '+tmp2+intTostr(paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4);
ExcelApp.WorkBooks[1].WorkSheets[1].Range[temp].merge;
end;

if paraDBGrid.VisibleColumns.Count>=3 then
begin
if (paraDBGrid.VisibleColumns.Count mod 2)=0 then
begin

i:=paraDBGrid.VisibleColumns.Count;
i:=Trunc(i/2);

ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i]:=middletop;
tmp :=checkColCount(i);
tmp2 :=CheckColCount(i+1);
temp :=tmp+intTostr(2)+': '+tmp2+intTostr(2);
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+TitleCapLines+paraDBGrid.datasource.DataSet.RecordCount,i]:=middlebottom;
tmp :=CheckColCount(i);
tmp2 :=CheckColCount(i+1);
temp:=tmp+intTostr(4+TitleCapLines+paraDBGrid.datasource.DataSet.RecordCount)+': '+tmp2+intTostr(paraDBGrid.datasource.DataSet.RecordCount+TitleCapLines+4);
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

end
else
begin
i:=paraDBGrid.VisibleColumns.Count;
i:=Trunc(i/2)+1;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i]:=middletop;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,i].horizontalalignment:=3;

ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+titleCapLines+paraDBGrid.DataSource.DataSet.RecordCount,i]:=middlebottom;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+titleCapLines+paraDBGrid.DataSource.DataSet.RecordCount,i].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[4+titleCapLines+paraDBGrid.DataSource.DataSet.RecordCount,i].horizontalalignment:=3;

end;
end;

ExcelApp.Workbooks[1].SaveAs(sFileName); //保存

paraDBGrid.DataSource.DataSet.EnableControls;

myShowMsg('数据导出已成功完成！', 0,'',0);

finally
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
Screen.Cursor:=crDefault;
end;
end;

//导出Excel多层标头的空白样表
procedure ExportDataToExcelMTitleStyle(MainForm: TForm; paraDBGrid: TDBGridEh; MyCaption,lefttop,middletop,righttop: string);
var
odSavefile: TSaveDialog;
sFileName: string; // Excel文件名
i,j,k: Integer;
temp : Widestring;
tmp : string;
tmp2 : string;
ExcelApp: Variant; //Excel对象
TitleCapLines: integer; //表头层数
titledetail: array of array of array of string;
begin
odSavefile:= TSaveDialog.Create(MainForm);
try
odSaveFile.Filter:= 'EXCEL表格文件格式(*.XLS)|*.xls';
odSaveFile.DefaultExt:= '.xls';
odSaveFile.InitialDir:= ExtractFilePath(ParamStr(0));
odSaveFile.FileName:= MyCaption + '.xls';
if odSaveFile.Execute
then sFileName:= odSaveFile.FileName
else Exit;
finally
odSaveFile.Free;
end;
Application.ProcessMessages;

try
ExcelApp := CreateOleObject('Excel.Application');
except
MyShowMsg('调用Excel程序失败，请确认本机是否安装Excel系统！',0 ,'调用失败',0);
Exit;
end;

Screen.Cursor:=crHourGlass;

try
ExcelApp.WorkBooks.Add;
ExcelApp.DisplayAlerts := False ;
ExcelApp.WorkBooks[1].WorkSheets[1].Name := '导出样表';
TitleCapLines:=1; // 确定最大的title的行次

tmp:=CheckColCOunt(paraDBgrid.VisibleColumns.count);
temp:='A1'+': '+tmp+'1';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,1].value:=MyCaption;
ExcelApp.WorkBooks[1].workSheets[1].range[temp].merge;

ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.size:=14 ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].font.Bold:=True ;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].wraptext:=True;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].horizontalalignment:=3;

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
j:= CountChar(paraDBGrid.VisibleColumns[i].Title.Caption,'|');
if TitleCapLines<j then TitleCapLines:=j;
end;

setLength(titledetail,paraDBGrid.VisibleColumns.Count,TitleCapLines,3);

k:=3; //从第三行开始
for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
for j:=0 to TitleCapLines-1 do
begin
titledetail[i,j,0]:=GetTitleNCaption(paraDBGrid.VisibleColumns[i].title.Caption,'|',j+1);
titledetail[i,j,1]:='';
titledetail[i,j,2]:='';
ExcelApp.WorkBooks[1].WorkSheets[1].cells[k+j,i+1].value:=titledetail[i,j,0];
end;
end;

for j:=0 to TitleCapLines-1 do //如果和右边的相等，就加合并标志
for i:=0 to paraDBGrid.VisibleColumns.Count-1-1 do
if (titledetail[i,j,0]=titledetail[i+1,j,0]) and (trim(titledetail[i,j,0])<>'') then titledetail[i,j,1]:='right'; //right 代表向右合并一列

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do //如果和下边的相等，就加合并标志
for j:=0 to TitleCapLines-1-1 do
if (titledetail[i,j,0]=titledetail[i,j+1,0]) and (trim(titledetail[i,j,0])<>'') then titledetail[i,j,2]:='down'; //down 代表向下合并一列

for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
j:=3;
for k:=0 to TitleCapLines-1 do
begin
tmp:=CheckColCount(i+1);
temp:=tmp+intTostr(j)+': '+tmp+intTostr(j+1);
if titledetail[i,k,2]='down' then ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge; //merge;
inc(j)
end;
end;

j:=3;
for i:=0 to TitleCapLines-1 do
begin
for k:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
tmp:=CheckColCount(k+1);
tmp2:=CheckColCount(k+2);
temp:=tmp+intTostr(j)+': '+tmp2+intTostr(j);
if titledetail[k,i,1]='right' then ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].merge; //merge;
end;
inc(j);
end;

for i:=3 to ExcelApp.WorkBooks[1].WorkSheets[1].usedRange.rows.count do
for j:=1 to ExcelApp.WorkBooks[1].WorkSheets[1].usedRange.Columns.count do
begin
ExcelApp.WorkBooks[1].WorkSheets[1].cells[i,j].verticalalignment:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[i,j].horizontalalignment:=3;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[i,j].font.size:=9 ;
ExcelApp.WorkBooks[1].WorkSheets[1].cells[i,j].wraptext:=True;
end;

j:=ExcelApp.WorkBooks[1].WorkSheets[1].usedRange.Columns.count;
tmp:=CheckColCount(j-1) ;
temp:=chr(ord('A'))+intTostr(3)+': '+tmp+intTostr(3+TitleCapLines);
for k:=1 to 4 do
begin
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[k].Weight:=2;
ExcelApp.WorkBooks[1].WorkSheets[1].range[temp].Borders[k].LineStyle:=1;
end;

ExcelApp.Workbooks[1].SaveAs(sFileName); //保存
myShowMsg('数据表格已经生成！', 0,'',0);
finally
ExcelApp.WorkBooks.Close;
ExcelApp.Quit;
Screen.Cursor:=crDefault;
end;
end;

procedure ImportDatafromExcelMTitle(sWprCode,sTaxCode :string;paraDBGrid: TDBGridEh;CustCLdata: TcustProcess); //导入 Excel 多层标头
var
ExlOle: variant;
filename: string; //Excel文件名

sheetsIndex : integer; // 选择的Excel的sheets
titlerow : integer; // 多层表头的表头层数
startrow : integer; // Excel开始行

ExlTitle : Tstrings; // Excel 标题列列表
ExlCellTitle: string; // Excel 单元格的标题
GridTitle: Tstrings; // GRid 单元格的标题
GrdTle: string;
K : integer;

TitleCheckError: Boolean; //检测表题列是否一至的标志
arrayResult: array of array of integer; //传回来的序号列数组
arrayCount : integer; //传回来的序号列的最大下标
i,j: integer;

tmpdata : TAdoDataSet; //临时数据集
ftype : TFieldType ;
fname : string; //字段名
frequired: Boolean;
teststr : string; //
//06-06 加上的
ErrStr : Tstrings; //导入出现错误的提示字符串
ErrsStr : String;
//07-18
qrysm,qrywt : TADOQuery;
begin
with TfmTpe_ChooseExcelFile.Create(nil) do
try
if ShowModal<>mroK then Exit;
//取得文件名
filename:=sfilename;
//多层表头的表头层数
titlerow:=nRow;
//返回第几个工作表
sheetsIndex:=nSheets;
finally
release;
end;

Application.ProcessMessages;

startrow:=titlerow+1 ;
GridTitle:=Tstringlist.create;
ExlTitle:=Tstringlist.Create;
ExlTitle.Clear;
GridTitle.clear;

ExlOle:=CreateOleObject('Excel.Application');
try
for i:=0 to paraDBGrid.VisibleColumns.Count-1 do
begin
GrdTle:=GetTitleLastCaption(paraDBGrid.VisibleColumns[i].Title.Caption,'|');
GridTitle.Add(Uppercase(trim(GrdTle)));
end;

ExlOle.workbooks.open(filename);
//开始理顺 Excel 列与 DBGridVisibleColumns的关系
for i:=1 to ExlOle.workSheets[sheetsIndex].usedRange.Columns.count do
begin
if ExlOle.workSheets[sheetsIndex].cells[titlerow,i].mergecells then
ExlCellTitle:= ExlOle.workSheets[sheetsIndex].cells[titlerow,i].MergeArea.cells[1,1].value
else
ExlCellTitle:= ExlOle.workSheets[sheetsIndex].cells[titlerow,i].value;
ExlTitle.Add(Uppercase(trim(ExlCellTitle)));
end;
//showmessage(ExlTitle.Text+#13+intTostr(ExlTitle.Count));

TitleCheckError:=False;

if ExlTitle.Count>GridTitle.Count then k:=GridTitle.Count else k:=ExlTitle.Count;

for i:=0 to k-1 do //前面一模一样就不用管了
begin
if ExlTitle.strings[i]<>GridTitle.strings[i] then
begin
TitleCheckError:=True;
break;
end;
end;

//||||||||||||||||||||||||||||||||||||||||||||||||||
if TitleCheckError then //如果标题列不一致，就要重新采集DBGrid的title.caption //放到下一出了
begin
with TfmTpe_ImpExcelSetCol.Create(nil) do
try
setlength(DBGridColCap,GridTitle.count);
setlength(ExlCellvalues,ExlTitle.Count);

for j:=0 to GridTitle.Count-1 do
DBGridColcap[j]:=GridTitle.strings[j];

for j:=0 to ExlTitle.Count-1 do
ExlCellvalues[j]:=ExlTitle.strings[j];

if showmodal<>mrOK then Exit;

Application.ProcessMessages;

arrayCount:=ColCount;
setlength(arrayResult,ColCount,2);

//GridTitle.Clear; // 重新勾造captionlist
for j:=0 to arrayCount-1 do
begin
arrayResult[j,0]:=ResultOrder[j,0]; // Excel 标题列
arrayResult[j,1]:=ResultOrder[j,1]; // Grid 标题列
//GridTitle.Add(paraDBgrid.VisibleColumns.Items[ResultOrder[j,1]].Title.Caption) //grid 的title
//为了能显示对应关系的错误之处，决定在显示导入界面之前再修改标题
end;
finally
release;
end;
end else
begin
//如果标题列一致，就不需要重新采集DBGrid的title.caption
//arrayCount:=paraDBGrid.VisibleColumns.Count;
arrayCount:=k; //Excel标题列个数与Grid标题列的个数想比较最小列的数量
setlength(arrayResult,paraDBGrid.VisibleColumns.Count,2);
for j:=0 to arrayCount-1 do
begin
arrayResult[j,0]:=j+1; // Excel 标题列
arrayResult[j,1]:=j; // Grid 标题列
teststr:=teststr+' '+inttostr(arrayResult[j,0])+' '+inttostr(arrayResult[j,1])+#13;
end;
end;

{teststr:='';
for j:=0 to arrayCount-1 do
teststr:=teststr+' '+inttostr(arrayResult[j,0])+' '+inttostr(arrayResult[j,1])+#13;
showmessage(teststr+#13+'order'); }
// arrayResult 存放的是最终 Excel标题列的顺序与 Grid可见标题列的顺序

// |||判断导入顺序
//arrayResult arrayCount
teststr:='';
tmpdata:=TAdoDataSet.Create(nil); //创建临时数据集
tmpdata.FieldDefs.Clear;
ErrStr:=TstringList.Create;
try
try
for i:=0 to arrayCount-1 do
begin
teststr:=teststr+ExlTitle.strings[arrayResult[i,0]-1]+' '+Gridtitle.strings[arrayResult[i,1]]+#13; //测试用对应关系列
//
if arrayResult[i,0]<1 then //Excel的列数 arrayResult[i,0] Grid的列数arrayResult[i,1]
begin
teststr:='对应关系出现错误，不能完成数据导入！'+#13+'对应关系如下：'#13+teststr;
MyshowMsg(teststr,0,'',0);
Exit;
end;
fname:=paraDBGrid.VisibleColumns.Items[arrayResult[i,1]].FieldName;
ftype:=paraDBGrid.VisibleColumns.Items[arrayResult[i,1]].Field.DataType;
frequired:= paraDBGrid.VisibleColumns.items[arrayResult[i,1]].Field.Required;
case ftype of
ftstring,ftWidestring: tmpdata.FieldDefs.Add(fname,ftype,100,frequired)
else
tmpdata.FieldDefs.Add(fname,ftype,0,frequired)
end;
end;
except
on E: exception do
MyShowMsg(E.message+#13+' ',0,'',0);
end;
tmpData.CreateDataSet;

qrysm:=TADOQuery.Create(nil);
qrysm.Connection:=dmCmn_DM_Dsr.DsrConnect;

qrywt:=TADOQuery.Create(nil);
qrywt.Connection:=dmCmn_DM_Dsr.DsrConnect;

MyOpenExecSQL(qrysm,format('select * from %s ',[Gettablename(31)]),0);
MyOpenExecSQL(qrywt,format('select * from %s ',[Gettablename(104)]),0);

try
//问题事项表/104 税目税率表/31
//读取单元格数据
for i:=startrow to ExlOle.worksheets[sheetsIndex].usedRange.rows.count do
begin
try
tmpData.Append;
for j:=0 to ArrayCount-1 do
begin
if UpperCase(Trim(tmpdata.Fields.fields[j].FieldName))='STATE' then
begin
if ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value='确定' then
tmpdata.Fields.fields[j].value:=Pc_State_Qd
else if ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value='取消' then
tmpdata.Fields.fields[j].value:=Pc_State_Qx;
end else
if (sWprCode=PC_TPEWPR_AA) and (UpperCase(tmpdata.Fields.fields[j].FieldName)='INT3') then
begin //写死 0601 0/1/2#一般工资、薪金/特种行业工资、薪金/年薪制工资、薪金
if Trim(ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value) ='一般工资、薪金' then
tmpdata.Fields.fields[j].value:=0 else
if Trim(ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value) ='特种行业工资、薪金' then
tmpdata.Fields.fields[j].value:=1 else
if Trim(ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value) ='年薪制工资、薪金' then
tmpdata.Fields.fields[j].value:=2 ;
end else //其他所得
if (sWprCode=PC_TPEWPR_AB) and ( UpperCase(tmpdata.Fields.fields[j].FieldName)='DT_PROCODE') then
begin
if qrywt.Locate('违法事项;TaxCode',VarArrayOf([ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value,Pc_TaxCode_Tpe]),[]) then
tmpdata.Fields.fields[j].value:=qrywt.fieldbyname('事项代码').AsString ;
end else //其他所得
if (sWprCode=PC_TPEWPR_AB) and ( UpperCase(tmpdata.Fields.fields[j].FieldName)='DT_ITEMCODE') then
begin
if qrysm.Locate('itemname;TaxCode',VarArrayOf([ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value,Pc_TaxCode_Tpe]),[]) then
tmpdata.Fields.fields[j].value:=qrysm.fieldbyname('itemCode').AsString ;
end else // 违规费用
if (sWprCode=PC_SdsWPR_AM) and ( UpperCase(tmpdata.Fields.fields[j].FieldName)='DT_PROCODE') then
begin
if qrywt.Locate('违法事项;TaxCode',VarArrayOf([ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value,Pc_TaxCode_Sds]),[]) then
tmpdata.Fields.fields[j].value:=qrywt.fieldbyname('事项代码').AsString ;
end else //三项福利费
if (sWprCode=PC_SdsWPR_AK) and ( UpperCase(tmpdata.Fields.fields[j].FieldName)='DT_ITEMCODE') then
begin
if qrysm.Locate('itemname;TaxCode',VarArrayOf([ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value,Pc_TaxCode_Sds]),[]) then
tmpdata.Fields.fields[j].value:=qrysm.fieldbyname('itemCode').AsString ;
end else //政策性费用
if (sWprCode=PC_SdsWPR_AG) and ( UpperCase(tmpdata.Fields.fields[j].FieldName)='DT_ITEMCODE') then
begin
if qrysm.Locate('itemname;TaxCode',VarArrayOf([ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value,Pc_TaxCode_Sds]),[]) then
tmpdata.Fields.fields[j].value:=qrysm.fieldbyname('itemCode').AsString ;
end else
tmpdata.Fields.fields[j].value:=ExlOle.worksheets[sheetsIndex].cells[i,arrayResult[j,0]].value;
end;
tmpData.Post;
except
on E: exception do
begin
if tmpData.State<>dsBrowse then tmpdata.Post;
ErrsStr:='第'+inttostr(i)+'行标题为'+
GetTitleLastCaption(paraDBgrid.VisibleColumns.Items[arrayResult[j,1]].Title.Caption,'|')+
'的列数据导入出现错误（'+
tmpdata.Fields.fields[j].FieldName+'）';
ErrStr.Add(ErrsStr);
ErrsStr:='读取Excel文件数据过程中,发现类型不能转换为系统可以接收的数据,还要继续导入数据吗?'
+#13+'提示:'+ErrsStr;
if not MyDiaLogMsg(ErrsStr,0,'',0) then Exit;
end;
end;
end;
finally
qrysm.Free;
qrywt.Free;
end;

tmpData.Open;
if TitleCheckError then
begin
GridTitle.Clear;
for j:=0 to arrayCount-1 do
begin
GrdTle:= Uppercase(trim(GetTitleLastCaption(paraDBgrid.VisibleColumns.Items[arrayResult[j,1]].Title.Caption,'|')));
GridTitle.Add(GrdTle) //grid 的title
end;
end;

with TfmTpe_ChooseImportData.Create(Nil) do //大开导入单元表单
try
try
DataSourceDataset:=tmpData;
CustCL:=CustCLdata;
lWprCode:=sWprCode; //传递 WprCode;
lTaxCode:=sTaxCode;
//06.06 加上一个错误提示的处理
if ErrStr.Count>0 then
begin
Memo1.Lines:=ErrStr;
Memo1.Lines.Add('以上所列数据行在导入过程中发现错误，请查看！');
Memo1.Lines.Add('另:鼠标双击将提示信息保存到文件，以供打印或校对！');
Memo1.Visible:=True;
Spl1.Visible:=True;
end else
begin
Memo1.Visible:=False;
Spl1.Visible:=False;
end;
setGridCaption(GridTitle); // 复制 grid 的样式如果全部复制，正常，不是全部复制，已经改变了标题的captionList
showmodal;
except
on E: exception do
Myshowmsg(pchar('导入数据出现错误：'+#13+E.message),0,'',0);
end;
finally
release;
end;
finally
tmpData.Close;
tmpdata.Free;
ErrStr.Free; //错误提示字符串去掉
end;
finally
GridTitle.Free;
ExlTitle.Free;
//ExlOle.ActiveWorkbook.saved:= True;
ExlOle.ActiveWorkbook.Close();
Exlole.quit;
end;

end;

//含有一个 | 就是两层
function CountChar(cStr: string;cChar: Char): Word;
var
i: Word;
begin
Result:=1;
for i:=1 to length(cStr) do
if cStr[i]=cChar then
Result:=Result+1;
end;

//子函数2：取第几层表头的字符比如 A|B|C 3 2 ==> B
function GetTitleNCaption(sCaption: string; SubStr :Char; CapLine: Word): string;
var
i, iPos: integer;
begin
i:=CountChar(sCaption, SubStr);
if i =0 then
begin
Result:=sCaption;
Exit;
end;

if CapLine>i then Capline:=i;
//删除前导
for i:= 1 to CapLine-1 do
begin
iPos:= Pos('|', sCaption);
Delete(sCaption, 1, IPos);
end;
//取串值
iPos:= Pos('|', sCaption);
if iPos<>0
then Result:= Copy(sCaption, 1, iPos-1)
else Result:= sCaption;
end;

// 注意传过来的 ntype 需要的时候自己提前+1;
function CheckColCount(ntype: integer): string; //专用子函数3 根据列数转化为 A1 AB1 AZ1
begin
if ntype<=26 then
begin
result:=chr(ord('A')+ntype -1);
exit;
end;
if ntype<=52 then
begin
Result:='A'+chr(ord('A')+ntype-26 -1);
Exit;
end;
if ntype<=78 then
begin
Result:='B'+chr(ord('A')+ntype-52 -1);
Exit;
end;
//依次类推多少列
//// if nType<= then begin end;
end;

//获得表格标题
function GetTitleLastCaption(str: string;substr: char): string;
var
i,j,k,n: integer;
begin
k:=CountChar(str,substr);
if k=1
then Result:=Str
else
for i:=1 to k-1 do //注意K-1;
begin
j:=POS(substr,str);
n:=Length(str);
if n<>0 then str:=RightStr(str,n-j)
end;
Result:=str;
end;

//统计substr的个数
function SubStrCount(Sub: string;Str: string): integer;
var
TempStr: string;
i,j: integer;
begin
j:=0;
i:=pos(sub,str);
if i>0 then
begin
j:=j+1;
tempstr:=rightstr(str,length(str)-i);
j:=j+SubStrcount(sub,tempStr);
end;
result:=j;
end;

procedure FillCells(DBGridEh1: TDBGridEh);
var
QryGrsd: Tdataset;
i: integer;
begin
if (DBGRidEh1.Columns[DBGridEh1.selectedIndex].ReadOnly) or
(DbgridEh1.ReadOnly) or
(not (alopUpdateEh in DBGRidEh1.AllowedOperations)) then
begin
MyShowMsg('表格数据在只读状态不适用自动填充功能! ');
Exit;
end else
if DBGridEh1.SelectedField.FieldKind<>fkdata then
begin
MyShowMsg('该栏目栏目不适用自动填充功能!');
Exit;
end;

QryGrsd:=DBGridEh1.DataSource.DataSet; //把数据集传过来
QryGrsd.DisableControls;
try
screen.Cursor:=crHourGlass;
with TfmTpe_Fill.create(nil) do
try //
caption:='自动填充栏目';
label3.Caption:='请输入要填充的值：';
Edit3.MaxLength:=0;
if not QryGrsd.Active then Exit;
case DBGridEh1.SelectedField.DataType of
ftUnknown: Flag:=-1;
ftstring, ftWidestring: Flag:=0;
ftSmallint, ftInteger, ftWord: begin
Flag:=1;
end;
ftBoolean: Flag:=2;
ftFloat, ftCurrency, ftBCD: Flag:=3;
ftDate: Flag:=4;
ftTime: Flag:=5;
ftDateTime: Flag:=6;
ftBytes,ftVarBytes: begin
Flag:=7;
Edit3.MaxLength:=1;
end ;
ftAutoInc: Flag:=8;
ftBlob: Flag:=9;
ftMemo, ftFmtMemo: Flag:=10;
ftGraphic: Flag:=11;
ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar,
ftLargeint, ftADT, ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob,
ftInterface, ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd: Flag:=12;
else
Exit;
end;
label2.Caption :=GetTitleLastCaption(Trim(DBGRidEh1.Columns[DBGridEh1.selectedIndex].Title.Caption),'|');
if ShowModal<>mrok then Exit;
if not MyDialogMsg('真的要将栏目：'+label2.Caption+' 中的内容替换为"'+Edit3.Text+'"？',0,'',0) then Exit;
if (Flag=0) and (Trim(edit3.Text)='') then Edit3.Text:=' ';
if (Flag<>0) and (Trim(edit3.Text)='') then Exit;

try //|||||||
case DBGridEh1.Selection.SelectionType of
gstNon: begin
Datachange(DBGridEh1,Edit3.text,flag,DBGridEh1.SelectedField.FieldName);
end;

gstRecordBookmarks: begin
for i:=0 to DBGridEh1.SelectedRows.Count-1 do
begin
QryGrsd.GotoBookmark(pointer(DBGridEH1.SelectedRows[i]));
Datachange(DBGridEh1,Edit3.text,flag,DBGridEh1.SelectedField.FieldName);
end;
end;

//看看矩形为什么只复制屏幕页
gstRectangle: begin
//注意确定巨型块的的行数
//这个是相对于屏幕
//j:=DBGridEh1.Selection.SelectionToGridRect.Bottom - DBGridEh1.Selection.selectionToGridRect.Top;
QryGrsd.GotoBookmark(pointer(DBGridEh1.Selection.Rect.TopRow));
while True do
begin
DataChange(dBGRIDEH1,eDIT3.Text,FLAG,dBgRIDeh1.SelectedField.FieldName);
if Qrygrsd.CompareBookmarks(pointer(dBGRIDeh1.Selection.Rect.BottomRow),QryGrsd.GetBookmark)=0 then Break;
qryGrsd.Next;
if QryGrsd.eof then break;
end;
QryGrsd.GotoBookmark(pointer(DBGridEh1.Selection.Rect.TopRow));
end;

gstColumns,gstAll: begin
QryGrsd.First ;
while not QryGrsd.Eof do
begin
Datachange(DBGridEh1,Edit3.text,flag,DBGridEh1.SelectedField.FieldName);
QryGrsd.Next;
end;
end;
end;
Except//||||||||
on E: exception do
if QryGrsd.State<>dsbrowse then QryGrsd.Cancel;
end; //||||||||
finally //
release;
end; //
finally
QryGrsd.EnableControls;
screen.Cursor:=crDefault;
end;
end;

procedure Datachange(DBGridEh: TDBGridEh;value: variant;flag: integer;fname: string);
var
QryGrsd: TdataSet;
begin
qryGrsd:=DBGridEh.DataSource.DataSet;
try
if (QryGrsd.State<>dsEdit) or (QryGrsd.State<>dsinsert) then QryGrsd.Edit;
case flag of
0: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=value;
QryGrsd.Post;
end;

1: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=StrToInt(value);
QryGrsd.Post;
end;

3: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=StrTOfloat(value);
QryGrsd.Post;
end;

4: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=StrTOdate(value);
QryGrsd.Post;
end;

5: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=strToTime(value);
QryGrsd.Post;
end;

6: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=StrTODateTime(value);
QryGrsd.Post;
end;

7: begin
QryGrsd.Fieldvalues[DBGridEh.SelectedField.FieldName]:=StrTOint(value);
QryGrsd.Post;
end;

else
QryGrsd.Cancel;
end;
except
On E: exception do
begin
if (QryGrsd.State<>dsEdit) or (QryGrsd.State<>dsinsert) then QryGrsd.Cancel;
MyshowMsg(pchar('填充数据出错！'+#13+E.message),0,'',0);
Abort;
end;
end;
end;

//不能直接确定下来的税目
function Tpe_GetSlKcsCMN( JsYj: Currency; TaxCode,
ItmCode: string; var Sl: Currency; var Kcs: Integer; Yf: integer): Boolean;
var
QrySm: TadoQuery;
Str : string;
begin
QrySm:=TadoQuery.Create(nil);
QrySm.Connection:=dmCmn_DM_Dsr.DsrConnect;
Result:=False;
Sl:=0;
Kcs:=0;
if JsYj<=0 then
begin
Result:=True;
Exit;
end;

try
//应该加上确定起始日期的部分,最好第一行都加上一个生效日期
Str:=Format('Select * From %s Where Trim(TaxCode)="%s" '+
'and Trim(ItemCode)="%s" order by ItemCode ',
[GetTableName(31),TaxCode,ItmCode]);
if not MyOpenExecSql(QrySm,Str,0) then Exit;

Str:=Trim(QrySm.FieldByName('税率类型').Asstring);
if Str='超额累进' then Result:=Tpe_GetSlKcsGX(JsYj,TaxCode,ItmCode,Sl,Kcs,Yf)
else
begin //非超额累进税率开始
if str='比例税率' then sl :=QrySm.FieldByName('税率或单位税额').AsFloat //比例税率
else sl :=QrySm.FieldByName('税率或单位税额').AsFloat; //计数计件
end; //非超额累进税率结束
finally
QrySm.Free;
end;
end;

//直接超额累进直接确定下来了税目个人工薪所得 0601 供工薪所得和年终奖金专用
function Tpe_GetSlKcsGX( JsYj: Currency; TaxCode,
ItmCode: string; var Sl: Currency; var Kcs: Integer; Yf: integer; Flag: Boolean=False): Boolean;
var
sSQL : string;
QrySl : TadoQuery;
CurrQD :Currency;
begin
QrySl:=TadoQuery.create(nil);
QrySl.connection:=dmCmn_DM_Dsr.DsrConnect;
Result:=False;
try
//应该加上确定起始日期的部分 //最好第一行都加上一个生效日期

//首先将生效日期的有效的数据罗列出来，然后再在数据集再次过滤

sSQL:=Format('Select * From %s Where TaxCode="%s" and Trim(pItemCode)="%s" ',
[GetTableName(31),TaxCode,ItmCode]);
if Flag //不含税倒推的第一步
then sSQL:=sSQL+' order by 不含税级距起点 Desc'
else sSQL:=sSQL+' order by 级距起点 Desc';

if not MyOpenExecSql(QrySl,sSql,0) then Exit;

Sl:=0;
Kcs:=0;

if QrySl.RecordCount=0 then
begin
MyshowMsg('加载税率失败,不能进行计算!',0,'',0);
Exit;
end;
if JsYj<=0 then
begin
Result:=True;
Exit;
end;
QrySl.First;
While not QrySl.Eof do
begin
if Flag //不含税
then CurrQD:= QrySl.FieldByName('不含税级距起点').AsCurrency
else CurrQD:= QrySl.FieldByName('级距起点').AsCurrency ;
//是大于还是等于起点
if JsYj >=CurrQD then
begin
Sl:=QrySl.FieldByName('税率或单位税额').AsCurrency;
Kcs:=QrySl.FieldByName('扣除数').AsInteger;
Result:=True;
break;
end;
QrySl.Next;
end;
finally
QrySl.Free;
end;
end;

// 获取纳税人年度参数例如：双月工资年度奖金
function Tpe_GetNsrNdCsInt(NsrID: String; DataYear,ID:integer):integer;
var
vI:variant;
begin
vI:=dmCmn_RegInfo.ReadvalueByNsrYear(ID, NsrID, DataYear);
if vI =NULL
then result:=0
else result:=StrToInt(VarToStr(vI));
end;

//监测是否有重复记录这里是所有月份，修改为可以指定月份重复返加真
function CheckDataWYX(sWprCode,sNsrID,sTaxCode:string; iWC,iDataYear:integer; YF:integer=0):Boolean;
var
Qry:TadoQuery;
sSql:String;
str:Tstrings;
begin
Qry:=TadoQuery.Create(nil);
Qry.Connection:=dmCmn_DM_Dsr.DsrConnect;
Str:=TstringList.Create;
try
if YF=0 then
sSql:=Format('Select Txt10_1,月份 from %s '+sWhereWprYearNsr
+sAndTaxWtbSta+sAndWprLineSign,
[GetTableName(112),sWprCode,iDataYear,sNsrID,sTaxCode,iWC,Pc_State_Qd,Pc_Sign_Sj])
else
sSql:=Format('Select Txt10_1,月份 from %s '+sWhereWprYearNsr
+sAndTaxWtbSta+sAndWprLineSign+ ' and 月份=%d',
[GetTableName(112),sWprCode,iDataYear,sNsrID,sTaxCode,iWC,Pc_State_Qd,Pc_Sign_Sj,YF]);

sSql:=format('Select Count(*) as aa ,Txt10_1,月份 from (%s) group by Txt10_1,月份',[sSql]);

sSql:=Format('Select * from (%s) where aa>1',[sSql]);
MyOpenExecSql(Qry,sSql,0);
if Qry.RecordCount>0 then
begin
while not Qry.Eof do
begin
str.Add(intTostr(qry.fieldbyname('月份').AsInteger)+'月份,有'
+intTostr(qry.fieldbyname('aa').asinteger)+'条同名记录： '
+Qry.Fieldbyname('Txt10_1').AsString);
Qry.Next;
if str.Count>5 then
begin
str.Add('......');
sSql:= extractfilePath(application.ExeName)+'工薪所得检查重复记录.txt';
str.SaveToFile(sSQl);
str.Add('详情请查看文件：'+sSql);
str.Add('');
break;
end;
end;
MyShowMsg('工作底稿中发现如下重复记录，请注意：'+#13+str.Text+#13+'计算过程终止！请在工作底稿中对以上所列重复数据进行修改并重新进行计算！',0,'',0);
Result:=True;
end else Result:=False;
finally
Qry.Free;
Str.Free;
end;
end;

//年终奖金调取数据的实施部分
function Tpe_addNzjjXM(sTaxCode,sWprCode,sNsrID,sqlGxsd,sqlself:string;iDataYear,iWprTblID:integer;Dou:Boolean) :Boolean;
function maxXH(sql:String):integer;
var
Qry:TadoQuery;
begin
Qry:=TadoQuery.Create(nil);
Qry.Connection:=dmCmn_DM_Dsr.DsrConnect;
try
MyopenExecSql(Qry,format('Select max(序号) as xh from (%s)',[sql]),0);
if qry.RecordCount>0 then
result:=qry.fieldbyname('xh').AsInteger+1
else
result:=1;
finally
Qry.Free;
end;
end;
var
QryGx,aqrData,QrySx:Tadoquery;
SqlStr:string;
begin
QryGx:=TadoQuery.Create(nil);
QryGx.Connection:=dmCmn_DM_Dsr.DsrConnect;

QrySx:=TadoQuery.Create(nil);
QrySx.Connection:=dmCmn_DM_Dsr.DsrConnect;

aqrData:=TadoQuery.Create(nil);
aqrData.Connection:=dmCmn_DM_Dsr.DsrConnect;

dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
try

if Dou then
begin //doumonth jin
SqlStr:=Format('Select ID,txt10_1,月份,Cur2 from %s '+sWhereWprYearNsr+sAndTaxWtb+sAndWprLineSign,
[GetTablename(112),Pc_TpeWpr_AQ,iDatayear,sNsrID,sTaxCode,iWprTblID,Pc_Sign_SJ]);
MyOpenExecSQL(QrySx,SqlStr,0);
QrySx.Filtered:=False;
QrySx.Filter:=Format('月份=%d',[Tpe_GetNsrNdcsInt(sNsrID,iDataYear,Tpe_NzjjMonthNum)]);
QrySx.Filtered:=True;
end;

MyOpenExecSQL(QryGx,sqlGxsd,0);
if QryGx.RecordCount=0 then
begin
Result:=False;
Exit;
end;
QryGx.Filtered:=False;
QryGx.Filter:=Format('月份=%d',[Tpe_GetNsrNdcsInt(sNsrID,iDataYear,Tpe_NzjjMonthNum)]);
QryGx.Filtered:=True;

MyOpenExecSql(aqrData,sqlself,0);

//[序号];[Txt10_1,姓名];[月份];[税款日期,滞纳金起始日];[Cur3,月工资收入];[Cur1,费用扣除标准];
//[Cur4,双薪];[Cur2,年终奖金];[计税依据,应税所得额];[税率];[Int1,速算扣除数];
//[应税额,应缴税额];[已税额,已申报税额];[查税额,应补税额];

QryGx.First;
while not QryGx.Eof do
begin
aqrData.Append;
aqrData.Fieldvalues['WprCode']:= sWprCode;
aqrData.Fieldvalues['NsrID']:= sNsrID;
aqrData.Fieldvalues['DataYear']:= iDataYear;
aqrData.Fieldvalues['TaxCode']:= sTaxCode;
aqrData.Fieldvalues['State']:= Pc_State_Qd;//确定
aqrData.Fieldvalues['税率']:= 0 ;
aqrData.Fieldvalues['Int1']:= 0 ;
aqrData.Fieldvalues['WprTblID']:=iWprTblID;
aqrData.Fieldvalues['Sign_Wpr']:=Pc_Sign_sj;
aqrData.Fieldvalues['序号']:=maxXH(sqlself);
aqrData.Fieldvalues['Txt10_1']:=QryGx.Fieldbyname('Txt10_1').AsString;
aqrData.Fieldvalues['月份']:=Tpe_GetNsrNdcsInt(sNsrID,iDataYear,Tpe_NzjjMonthNum);

aqrData.Fieldvalues['Cur1']:=QryGx.Fieldbyname('Cur1').AsCurrency; //||月平均工资||
aqrData.Fieldvalues['Cur3']:=QryGx.Fieldbyname('Cur1').AsCurrency+QryGx.fieldbyname('计税依据').AsCurrency; //
if Dou then
if qrysx.Locate('txt10_1',QryGx.Fieldbyname('Txt10_1').AsString,[]) then
aqrData.Fieldvalues['Cur4']:=Qrysx.Fieldbyname('Cur2').AsCurrency;
aqrData.Post;
QryGx.Next;
end;
dmCmn_DM_Dsr.DsrConnect.CommitTrans;
result:=True;
finally
QryGx.Free;
QrySx.Free;
aqrData.Free;
end;
except
result:=False;
dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
end;

//调取双薪数据的过程
function Tpe_addDXXM(sTaxCode,sWprCode,lWprCode,sNsrID,sqlGxsd,sqlself:string;iDataYear,iWprTblID:integer) :Boolean;
function maxXH(sql:String):integer;
var
Qry:TadoQuery;
begin
Qry:=TadoQuery.Create(nil);
Qry.Connection:=dmCmn_DM_Dsr.DsrConnect;
try
MyopenExecSql(Qry,format('Select max(序号) as xh from (%s)',[sql]),0);
if qry.RecordCount>0 then
result:=qry.fieldbyname('xh').AsInteger+1
else
result:=1;
finally
Qry.Free;
end;
end;
var
QryGx,aqrData:Tadoquery;
begin
QryGx:=TadoQuery.Create(nil);
QryGx.Connection:=dmCmn_DM_Dsr.DsrConnect;

aqrData:=TadoQuery.Create(nil);
aqrData.Connection:=dmCmn_DM_Dsr.DsrConnect;
dmCmn_DM_Dsr.DsrConnect.BeginTrans;
try
try
// [序号]; [Txt10_1,姓名]; [月份]; [税款日期,滞纳金起始日];
// [Cur3,月工资收入]; [Cur1,费用扣除标准]; [Cur2,双薪金额];
// [计税依据,应税所得额]; [税率];[Int1,速算扣除数]; [应税额,应缴税额];
// [已税额,已申报税额]; [查税额,应补税额];

MyOpenExecSQL(QryGx,sqlGxsd,0);
if QryGx.RecordCount=0 then
begin
Result:=False;
Exit;
end;

QryGx.Filtered:=False;
QryGx.Filter:=Format('月份=%d',[Tpe_GetNsrNdcsInt(sNsrID,iDataYear,Tpe_DouMonthNum)]);
QryGx.Filtered:=True;
MyOpenExecSql(aqrData,sqlself,0);
QryGx.First;
while not QryGx.Eof do
begin
aqrData.Append;
aqrData.Fieldvalues['WprCode']:= sWprCode;
aqrData.Fieldvalues['NsrID']:= sNsrID;
aqrData.Fieldvalues['DataYear']:= iDataYear;
aqrData.Fieldvalues['TaxCode']:= sTaxCode;
aqrData.Fieldvalues['State']:= Pc_State_Qd;//确定
aqrData.Fieldvalues['税率']:= 0 ;
aqrData.Fieldvalues['Int1']:= 0 ;
aqrData.Fieldvalues['WprTblID']:=iWprTblID;
aqrData.Fieldvalues['Sign_Wpr']:=Pc_Sign_sj;
aqrData.Fieldvalues['序号']:=maxXH(sqlself);
aqrData.Fieldvalues['Txt10_1']:=QryGx.Fieldbyname('Txt10_1').AsString;
aqrData.Fieldvalues['月份']:=Tpe_GetNsrNdcsInt(sNsrID,iDataYear,Tpe_DouMonthNum);
aqrData.Fieldvalues['Cur1']:=QryGx.Fieldbyname('Cur1').AsCurrency; //||月平均工资||
aqrData.Fieldvalues['Cur3']:=QryGx.Fieldbyname('Cur1').AsCurrency+QryGx.fieldbyname('计税依据').AsCurrency; //
aqrData.Post;
QryGx.Next;
end;
dmCmn_DM_Dsr.DsrConnect.CommitTrans;
result:=True;
finally
QryGx.Free;
aqrData.Free;
end;
except
result:=False;
dmCmn_DM_Dsr.DsrConnect.RollbackTrans;
end;
end;

//
procedure TpeSubForm(sqlStr,Grdstr,Cap,sOrder,Tax,Gtxt:String;Flag:Boolean);
var
str:string;
begin
str:=sqlstr+' '+sOrder;
with TfmTpe_GxsdSub.Create(nil) do
try
ReadyAction(Cap,str,Grdstr,Tax,Gtxt,Flag);
showmodal;
finally
release;
end;
end;

function SetColV(i,j:integer; DbGrid:TDBgridEH):Boolean;
begin
result:=False;
if i=j then
if (i>0) and (i<13) then
Result:=True;
for i:=0 to DBGrid.Columns.Count-1 do
if UpperCase(DBGrid.Columns[i].FieldName)='CUR4' then
begin
DBGrid.Columns[i].Visible:=Result;
break;
end;
end;

// 用stringlist 生成 [];[];
function MakeGridStyleStr (ts: Tstrings) : string;
var
tmp:pnode;
zcstr:string;
i:integer;
begin
zcstr:='';
for i:=0 to Ts.count-1 do
begin
tmp:=pnode(ts.objects[i]);
if tmp.Fieldname<>'' then
begin
zcstr:=zcstr+'['+ tmp.Fieldname;
if tmp.Fcaption<>'' then
begin
zcstr:=zcstr +','+tmp.Fcaption;
if tmp.Fwidth<>'' then
begin
zcstr:=zcstr+','+ tmp.Fwidth;
if tmp.Fread<>'' then
begin
zcstr:=zcstr+','+tmp.Fread;
if tmp.Ffcolor<>'' then
begin
zcstr:=zcstr+tmp.Ffcolor;
if tmp.Fbcolor<>'' then
begin
zcstr:=zcstr+tmp.Fbcolor;
if tmp.Fime<>'' then
begin
zcstr:=zcstr+tmp.Fime;
if tmp.Fali<>'' then
begin
zcstr:=zcstr+','+tmp.Fali;
if tmp.Flay<>'' then
begin
zcstr:=zcstr+','+tmp.Flay;
if tmp.Fklist<>'' then
begin
zcstr:=zcstr+','+tmp.Fklist;
if tmp.Fplist<>'' then
zcstr:=zcstr+'#'+tmp.Fplist;
end;
end;
end;
end;
end;
end;
end;
end;
end;
zcstr:=zcstr+'];';
end;
end;
result:=zcstr;
end;
// 有串生成 Tstrings
procedure MakeGridStyleStrLists(sGridStr: string; ts: TStrings);
var
iPos, iIndex, iWidth: integer;
sSubStr, sCellStr: string;
tmp:Pnode;
begin

ts.Clear;
if Trim(sGridStr) = '' then Exit;
//化成标准的格式，必须以;为结束符
if RightStr(sGridStr,1)<>';' then sGridStr:= sGridStr+';';

//先解析表格属性设置 (1,0,1)#
iPos:= Pos(')#', sGridStr);
if iPos <> 0 then
try
//取得表格的定义
sSubStr:= LeftStr(sGridStr, iPos-1);
Delete(sGridStr, 1, iPos+1);
Delete(sSubStr, 1, 1);
//开始循环解析表格的定义
iIndex:= 1;
while sSubStr <> '' do
begin
iPos:= Pos(',', sSubStr);
if iPos <> 0 then
begin
sCellStr:= LeftStr(sSubStr, iPos-1);
Delete(sSubStr, 1, iPos);
end else
begin
sCellStr:= sSubStr;
sSubStr:= '';
end;

{
case iIndex of
1: fGrid.ReadOnly:= sCellStr='1';
2: iFrozCols:= StrToInt(sCellStr);
3: fGrid.UseMultiTitle:= sCellStr='1';
4: fGrid.RowSizingAllowed:= sCellStr='1';
end;
Inc(iIndex);
}
end;
except
MyShowMsg('表格属性设置出现异常错误！', 2, '', 0);
end;
//开始循环解析表格列定义
while Length(sGridStr) > 0 do
try
iPos:= Pos('];', sGridStr);
if iPos <> 0 then
begin
sSubStr:= LeftStr(sGridStr, iPos-1);
Delete(sGridStr, 1, iPos+1);
Delete(sSubStr, 1, 1); //此时sSubStr形式如：字段名,标题,宽度,只读,...
end else
begin //此举可以防止出现死循环
MyShowMsg('表格数据列定义格式错误！', 2, '', 0);
Exit;
end;

New(tmp);
//开始循环解析一个字段列的定义
iIndex:= 1;
while sSubStr <> '' do
begin
iPos:= Pos(',', sSubStr);
if iPos <> 0 then
begin
sCellStr:= LeftStr(sSubStr, iPos-1);
Delete(sSubStr, 1, iPos);
end else
begin
sCellStr:= sSubStr;
sSubStr:= '';
end;

if Trim(sCellStr) <> '' then //如果取得的定义为空，则跳过这一属性设置
case iIndex of
//字段名
1: tmp.Fieldname:= sCellStr;
//标题名
2: begin
if sCellStr = '=' then sCellStr:= tmp.FieldName;
tmp.Fcaption:= sCellStr;
end;
//宽度
3: begin
if TryStrToInt(sCellStr, iWidth) then
tmp.Fwidth:= IntToStr(iWidth);
end;
//只读及显示和输入法等属性定义
4: begin
if Length(sCellStr) > 0 then
begin
//第1位为ReadOnly的解释
//0 为 False，即为可写状态
//1 为 True，即为只读状态
tmp.Fread:= sCellStr[1] ;
//字体颜色
if Length(sCellStr) > 1 then
begin
tmp.Ffcolor:= sCellStr[2];
//背景
if Length(sCellStr) > 2 then
begin
tmp.Fbcolor:= sCellStr[3];
if Length(sCellStr) > 3 then tmp.Fime :=sCellStr[4];
end;
end;
end;
end;
//左中右
5: tmp.Fali:= sCellStr;
//上中下
6: tmp.Flay:= sCellStr;
//KeyList和PickList设置
7: begin //格式为：1/2/3/4/5 或 1/2/3/4/5#01/02/03/04/05
//先分解为sKey和sPick
iPos:= Pos('#', sCellStr);
if iPos <> 0 then
begin
tmp.Fklist:=LeftStr(sCellStr, iPos-1);
tmp.Fplist:=sCellStr;
end else
begin
tmp.Fklist:=sCellStr;
tmp.Fplist:=sCellStr;
end;
end;
//表格列的DisplayFormat
//8: NewColumn.DisplayFormat:= sCellStr;
end;
Inc(iIndex);
end;
ts.AddObject(tmp.Fcaption,TObject(tmp));
except
MyShowMsg('表格数据列定义出现异常错误！', 2, '', 0);
Exit;
end;
end;

// 5--6
function changeID(liTblID:integer): Integer;
var
str:string;
begin
str:=IntToStr(liTblID);
str:='6'+rightstr(str,length(str)-1);
result:=StrToInt(str);
end;

initialization

end.; 0

http://wwle.5d.cn/

我有一对翅膀，不是用来飞翔，而是用来煮汤。

评论Comments