EXCEL导出

/**

* 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)