二开-如何快速引出物料等基础资料

目前K3自带的excel引出比较慢，数据量大的时候还容易崩溃；现在提供一种方法，来快速引出EXCEL；

1. 首先确定引出基础资料显示哪些列；

2. select * from t_ItemPropDesc where FItemClassID = 4 order by FPageName,FOrder

3. 

4. 然后构建查询脚本

declare @fname varchar(50)

declare @FSQLColumnName varchar(50)

declare @FDataType int

declare @FSearch int

declare @FSrcTable  varchar(50)

declare @FSrcField varchar(50)

declare @FDisplayField varchar(50)

declare @select varchar(max)

declare @from varchar(max)

declare @order varchar(100)

declare @index int

set @index = 1

set @select = 'select t0.FNumber as [代码],t0.FName as [名称]'

set @from = 'From t_icitem t0 ' + CHAR(10)

set @order = 'order by t0.FNumber'

declare c cursor fast_forward for select FName,FSQLColumnName,FDataType,FSearch,isnull(FSrcTable,'') as FSrcTable,FSrcField,FDisplayField from t_ItemPropDesc where FItemClassID = 4 order by FPageName,FOrder

open c

fetch next from c into @fname,@FSQLColumnName,@FDataType,@FSearch,@FSrcTable,@FSrcField,@FDisplayField

while @@FETCH_STATUS  =0 

begin

if @FDataType= 3 and @FSrcTable <> ''

begin

set @select = @select +',isnull(t' + convert(varchar(10),@index) +'.' + @FDisplayField + ','''') as ['+ @fname +']'

set @from = @from + 'left join ' + @FSrcTable + ' as t'+ convert(varchar(10),@index) + ' on t0.' + @FSQLColumnName + '= t' + convert(varchar(10),@index) + '.' + @FSrcField 

if @FSrcTable = 't_submessage' 

begin

set @from = @from + ' And t0.'+@FSQLColumnName+'>0 And t'+ convert(varchar(10),@index) +'.FTypeID = ' + convert(varchar(10),@FSearch)

end

if @FSrcTable = 't_Item' 

begin

set @from = @from + ' And t0.'+@FSQLColumnName+'>0 And t'+ convert(varchar(10),@index) +'.FItemClassID = ' + convert(varchar(10),@FSearch)

end

set @from = @from + CHAR(10)

set @index = @index + 1

end

else if @FDataType = 200

begin

set @select = @select + ',isnull(t0.' + @FSQLColumnName + ','''') as ['+ @fname +']'

end

else