package UIB.UIB03;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.springframework.jdbc.support.rowset.SqlRowSet;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import UIB.COM.XmlSqlParsersFactory;
import UIB.UIB03.ZBS.ExcelStyleSeter;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;

public class TestPrint	extends CoreIComponent
{
	Connection			con			= 	this.getDao("KgDao").getConnection();
	
	public	CoreReturnObject	print(String strExcel , String strCoilNo)throws Exception
	{
		CoreReturnObject	cro		=	null;
		
		PreparedStatement		pstm	=	con.prepareStatement(XmlSqlParsersFactory.getSql("UIB030310_03.SELECT"));
		pstm.setString(1, strExcel);
		ResultSet				rs		=	pstm.executeQuery();
		if(rs.next())
		{
			
			SqlRowSet	srs			=	null;
			cro	=	this.getDao("KgDao").ExecutequeryForRowSet(XmlSqlParsersFactory.getSql("UIL010020_04.SELECT") , new Object[]{ strCoilNo});
			srs							=	(SqlRowSet)cro.getResult();
			if(!srs.next())
			{
				return null;
			}
			
			InputStream		is		=	new ByteArrayInputStream(this.dbproxy.getLobHandler().getBlobAsBytes(rs , "TMPL_EXCEL"));
			
			OutputStream		os		=	new ByteArrayOutputStream();//建立输出流，填入数据的EXCEL形成该输出流
			Workbook			wBook	=	Workbook.getWorkbook(is);//获取EXCEL对象，生成工作薄		
			WritableWorkbook	wwBook	=	Workbook.createWorkbook(os, wBook);//创建可操作副本
			WritableSheet 		wSheet	=	wwBook.getSheet(0);//获取第0个工作表
			
			/////////填充
			wSheet.addCell(new Label(1 , 5  , srs.getString("COIL_NO")     ));
			wSheet.addCell(new Label(1 , 7  , srs.getString("CHARGE_NO")     ));
			wwBook.write();
			ByteArrayOutputStream		baos	=	(ByteArrayOutputStream)os;
			baos.close();
			os.close();
			wwBook.close();
			wBook.close();
			
			cro		=	new CoreReturnObject();
			cro.setResult(baos.toByteArray());
			
		}
		return cro;
	}
}