/**
* Created by lijun on 16/1/19.
*/
// Groovy标准模版导出实例
import
java
.
sql
.
DriverManager
import
java
.
text
.
SimpleDateFormat
import
org
.
apache
.
poi
.
hssf
.
usermodel
.
HSSFWorkbook
import
net
.
sf
.
jxls
.
transformer
.
XLSTransformer
import
net
.
sf
.
jxls
.
report
.
ReportManagerImpl
import
net
.
sf
.
jxls
.
report
.
ReportManager
import
org
.
apache
.
commons
.
beanutils
.
RowSetDynaClass
//import com.isomorphic.rpc.RPCManager
import
java
.
util
.
Map
import
java
.
util
.
List
import
java
.
util
.
ArrayList
import
java
.
io
.
*
// 定义RPC获取客户端传入参数
//def rpc = new RPCManager(request, response, out)
//def data = (Map)rpc.getData()
def subId
=request
.getParameter
(
"
subId
"
)
System
.out
.
println(subId)
// 定义时间戳
def date
=
new
Date()
def df
=
new
SimpleDateFormat(
"
yyyy-MM-dd_hh_mm_ss
")
def nowTime
= df
.format
(date
)
// 读取sql配置文件连接数据库
def properties
=
new
Properties()
def fileStr
=
Thread
.currentThread
().getContextClassLoader
().getResource
(
""
).file
System
.out
.
println(fileStr);
properties
.load
(
new
FileInputStream(fileStr
+
"
/server.properties
")
)
def con
=
DriverManager
.getConnection
(properties
.getProperty
(
"
sql.SQLServer.driver.url
"
))
// 定义模版路径和写入路径
def srcFilePath
= request
.getSession
().getServletContext
().getRealPath
(
"
/
"
)+
"
template
\\
productAudit.xls
"
def destFileName
= (
new
StringBuilder(
"
productExcel_
"))
.append
(nowTime
).append
(
"
.xls
"
).toString
()
System
.out
.
println(destFileName)
//设置响应
response
.setHeader
(
"
Content-Disposition
"
,
"
attachment;filename=
"
+ destFileName
);
response
.setContentType
(
"
application/vnd.ms-excel
"
);
def beans
=
new
HashMap<String,Object>()
// 定义存储过程获取数据并写入Map
def sql
=
"
select * from t_pa_itemInfoDetail where active = 1 and subId = ?
"
def ps
=con
.prepareStatement
(sql
)
ps
.setString
(
1
,subId
)
def rs
=ps
.executeQuery
()
def rsdc
=
new
RowSetDynaClass(rs)
beans
.put
(
"
rm
"
, rsdc
.getRows
())
sql
=
"
select ROW_NUMBER() OVER (ORDER BY a.subId ASC) AS xuhao,a.* from t_pa_itemInfoList a where active = 1 and itemInfoDetailSubId = ? order by subId
"
ps
=con
.prepareStatement
(sql
)
ps
.setString
(
1
,subId
)
rs
=ps
.executeQuery
()
rsdc
=
new
RowSetDynaClass(rs)
beans
.put
(
"
list
"
, rsdc
.getRows
())
sql
=
"
select a.name as defectTypeName,ISNULL(defectSumNum,0)as defectSumNum,ISNULL(score,0)as score from t_pa_defectTypeCfg as a left join (select defectTypeName,ISNULL(SUM(defectSumNum),0) as defectSumNum,ISNULL(SUM(score),0) as score from t_pa_itemInfoList where active = 1 and itemInfoDetailSubId = ? and ISNULL(defectSumNum,'') <> '' group by defectTypeSubId,defectTypeName ) as b on a.name = b.defectTypeName order by a.subId
"
ps
=con
.prepareStatement
(sql
)
ps
.setString
(
1
,subId
)
rs
=ps
.executeQuery
()
rsdc
=
new
RowSetDynaClass(rs)
List rows
= rsdc
.getRows
()
for(
int i
=
1;i
<=rows
.size;i
++){
List list
=
new
ArrayList()
list
.push
(rows
.get
(i
-1
))
beans
.put
(
"
num
"
+i
.toString
(),list
)
}
sql
=
"
EXEC SP_PP_V_getProductDetailExcel @subId = ?
"
ps
=con
.prepareStatement
(sql
)
ps
.setString
(
1
,subId
)
rs
=ps
.executeQuery
()
System
.out
.
println(rs);
rsdc
=
new
RowSetDynaClass(rs)
beans
.put
(
"
problem
"
, rsdc
.getRows
())
sql
=
"
select * from t_pa_testItemDetail where active =1 and itemInfoSubId = 13
"
ps
=con
.prepareStatement
(sql
)
// ps.setString(1,subId)
rs
=ps
.executeQuery
()
// System.out.println(rs);
rsdc
=
new
RowSetDynaClass(rs)
List rowsTest
= rsdc
.getRows
()
List testList
=
new
ArrayList()
// List list = new ArrayList()
def map
=
new
HashMap<String,Object>()
for(
int i
=
1;i
<=rowsTest
.size;i
++){
testList
.push
(
"
testList
"
+i
.toString
())
map
.put
(
"
testList
"
+i
.toString
(),rowsTest
.get
(i
-1
))
// beans.put("testList"+i.toString(),list)
}
// testList.push(map)
beans
.put
(
"
testList
"
,testList
)
beans
.put
(
"
map
"
,map
)
// ReportManager reportManager = new ReportManagerImpl(con, beans );
// beans.put("test", reportManager);
// beans.put("subId",13);
// 使用XLSTransformer导出文件
def transformer
=
new
XLSTransformer()
System
.out
.
println(srcFilePath)
def infile
=
new
FileInputStream(srcFilePath)
def workbook
= (
HSSFWorkbook)transformer
.transformXLS
(infile
, beans
)
def os
= response
.getOutputStream
()
System
.out
.
println(response
.getOutputStream
())
workbook
.write
(os
)
// 关闭所有连接池释放内存
os
.flush
();
infile
.close
()
os
.close
();
// con.close()
// 定义下载路径返回客户端
// def saveAsFileName = (new StringBuilder("/xls/productAction_")).append(nowTime).append(".xls").toString()
// def path = request.getContextPath() + saveAsFileName
// System.out.println(request.getContextPath())
// rpc.send(path)