buildexcelfile.asp

物业管理和办公自动化系统

<%
'##############################################################################
'#
'#		文 件 名	 : BuildExcelFile.asp
'#
'#
'#		创 建 人	 : yaoxiaohui
'# 　	日　  期	 : 2002-12-2
'#
'#		修改历史  : ****年**月**日 ******
'#      修改内容  : **************************************************
'#
'#		功能描述	 : 进出库统计生成Excel文件
'# 　	版  　本	 : v1.0
'#	
'#	    Copyright(c) 2001-2002 上海阿尔卡特网络支援系统有限公司
'#
'##############################################################################
'------------------------------------------------------------------------------
'----   服务器端设置
'------------------------------------------------------------------------------
option explicit
Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1
%>
<!-- #include file="../include/common.inc" -->
<!-- #include file="../include/recordlist.inc" -->
<!-- #include file="../include/commonpage.inc" -->

<!-- #include file="BuildExcelFile.inc" -->
<%
call CheckSecurity()
'------------------------------------------------------------------------------
'----   用户权限检查
'------------------------------------------------------------------------------
if hasright(ID_STOCK) < RIGHT_READ  then
	Response.Clear
	Response.Redirect "../include/error.asp?error=你没有查询权限！"
	Response.end
end if
'response.write  Request.Form("ContentOfExcelFile")
'response.end
'meta http-equiv="Content-Type" content="text/html;charset=gb2312"

dim filename
dim pOpdate1,pOpdate2,pDepot
filename = Request.Form("filename")
pOpdate1 = Request.Form("opdate1")
pOpdate2 = Request.Form("opdate2")
pDepot	 = Request.Form("depot")

if filename = "" then filename = "统计报表"
Response.AddHeader "Content-Disposition","attachment;filename=" & pOpdate1 & "至" & pOpdate2 & pDepot & filename & ".xls"
Response.ContentType = "APPLICATION/OCTET-STREAM"


'Response.Charset = "UTF-16"
Response.Charset = "gb2312"			' 编码中的“简体中文(HZ)”
'Response.Charset = "hz-gb-2312"		' 编码中的“简体中文(GB2312)”，这两种编码都可以正常显示中文

'******************************************************************************
'----   建立数据库连接
'------------------------------------------------------------------------------
dim conn
set conn = DBConnection
dim sql, rs, j
dim pGoodsType, pGoodsDesc,sGoodsDesc, pCompare1, pCompare2
dim sSQL
sSQL = "select t1.goods_id,t1.goods_name,t1.sheet_id,t1.model,t1.quantity" & _
	",t1.unit,t1.goods_type,t1.opdate,t2.depot_name,t1.remark" & _
	" from t_stock_detail t1" & _
	" left join t_depot t2 on t1.depot = t2.depot_id" & _
	" left join t_stock_master t3 on t1.sheet_id = t3.sheet_id" & _
	" where t1.opdate >= " & ToSQL(pOpdate1,"text") & " and t1.opdate < " & ToSQL(pOpdate2,"text") & _
	" order by t1.goods_id"
'response.write sql  & "<br>" : response.end
set rs = Openrs(conn,sSQL)
if Not rs.EOF then
pCompare2 = GetValue(rs,"goods_type")
end if 
pGoodsDesc = ""
while Not rs.EOF
	pCompare1 = GetValue(rs,"goods_type")
	if pCompare1 = pComPare2 then
		pGoodsType = pCompare1
	else 
		pGoodsDesc = "全部"
	end if
	rs.movenext
wend
rs.Close()
set rs = nothing

if pGoodsDesc = "全部" then 
	sGoodsDesc = pGoodsDesc
else
	sql = "select type_desc from t_goods_type where type_id =" & pGoodsType
	sGoodsDesc = DLookUp(conn,sql)
end if

'******************************************************************************
'----   分页相关的变量定义
'------------------------------------------------------------------------------
Dim sFileName			: sFileName			= "sheet_stock_print.asp"
Dim iTotalRecords		: iTotalRecords		= 0			                ' 分页
Dim iRecordsPerPage		: iRecordsPerPage	= 25                        ' 一页8行
Dim iTotalPages			: iTotalPages		= 0
Dim iCols				: iCols				= 9                         ' 9列
Dim iCurrentPage		: iCurrentPage		= GetParam("Page")
Dim sSorting			: sSorting			= GetParam("Sorting")
Dim sSorted				: sSorted			= GetParam("Sorted")
Dim sSortParams			: sSortParams		= ""
Dim sFormParams			: sFormParams		= ""
Dim sDirection			: sDirection		= ""

sFormParams	= "opdate1=" & pOpdate1 & "&opdate2=" & pOpdate2 & "&"
'******************************************************************************
'----   分页SQL语句构造完毕，调用PrepareListData进行分页前的准备
'------------------------------------------------------------------------------
dim arrayFields(6), sDelimiter
sDelimiter = "*"
arrayFields(0)	=	"goods_id"
arrayFields(1)	=	"goods_name"
arrayFields(2)	=	"model"
arrayFields(3)	=	"quantity"
arrayFields(4)	=	"unit"
arrayFields(5)	=	"unit_price"
arrayFields(6)	=	"remark"

'response.write pSheetID  &"<BR>"
'response.write pSQL & sWhere & sOrderby  &"<BR>"
'response.end

call PrepareListData(conn, sSQL, Join(arrayFields, sDelimiter), sDelimiter)

Response.Write SheetTop
Response.Write SheetMasterUp
Response.Write TableHeader
Response.Write TableRecords( sSQL, iCols, iRecordsPerPage, iTotalRecords, iCurrentPage, iTotalPages, sFileName, sFormParams, sSortParams )
Response.Write SheetMasterDown
Response.Write SheetBottom
Disconnect(conn)
%>