package com.zhangjun.edu.exportExcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
 * <p>Description:下载生成xls格式的EXCEL</p>
 * @author 张军
 * @version 1.0
 * @date 2012-10-02
 */
@SuppressWarnings("serial")
public class Dom2ExcelServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}

	@SuppressWarnings("static-access")
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		
		response.reset();
		response.setContentType("application/vnd.ms-excel");
		try {
			    String sessionName=(String)request.getSession().getAttribute("sname");
		        Document dom=(Document)request.getSession().getAttribute(sessionName);
		        response.setContentType("application/octet-stream;charset=GB2312"); 
		        String filename = dom2Xls(dom,response.getOutputStream());
		        
				response.setHeader("Content-Disposition","attachment; filename="+filename);
		       
		       if (filename!=null)
				{
					File file1=new File(filename);
					FileInputStream is=new FileInputStream(file1);
					byte []bbb=new byte[(int)file1.length()];
					
					System.out.println("------bbb.length:"+bbb.length);
					is.read(bbb);
					is.close();
					OutputStream out = response.getOutputStream();
					out.write(bbb);
					out.close();
					response.setStatus(response.SC_OK);
					file1.delete();
				}
				else
				{
					System.out.println("----createExcelFile error!");
				}
		       
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	 private String dom2Xls(Document dom,OutputStream os)
    {
    	StringBuffer bf=new StringBuffer();
    	String name = new Date().getTime()+"";
    	String filename=name+".xls";
    	if (filename==null) return null;
//    	filename="data/"+filename;
    	
    	WritableWorkbook workbook = null;
    	try
    	{
    		workbook=Workbook.createWorkbook(new File(filename));
    		WritableSheet sheet = workbook.createSheet("详细数据",0);
    		
    		
    		
			dom.normalize();
			Node root=(Node)dom.getDocumentElement();
			NodeList nodeRowList=root.getChildNodes();
			
			if (nodeRowList.getLength()>0)	
			{
				Node nodeRowTitle=nodeRowList.item(0);
				NodeList nodeItemList=nodeRowTitle.getChildNodes();
				for (int j=0;j<nodeItemList.getLength();j++)
				{
					Node nodeItem=nodeItemList.item(j);
					if (j!=0)
					{
						bf.append(",");
					}
					bf.append("\"");
					bf.append(nodeItem.getChildNodes().item(0).getChildNodes().item(0).getNodeValue());	// /documents/row/item/name
					bf.append("\"");
					writeExecl(sheet,j,0,nodeItem.getChildNodes().item(0).getChildNodes().item(0).getNodeValue());
					
				}
				bf.append("\n");
			}
			
			for (int i=0;i<nodeRowList.getLength();i++)
			{
				Node nodeRow=nodeRowList.item(i);
				NodeList nodeItemList=nodeRow.getChildNodes();
				int k=1;
				for (int j=0;j<nodeItemList.getLength();j++)
				{
					Node nodeItem=nodeItemList.item(j);
					if (j!=0)
					{
						bf.append(",");
					}
					String itemtype=nodeItem.getChildNodes().item(2).getChildNodes().item(0).getNodeValue();	//itemtype
					itemtype=itemtype.trim();
					
					NodeList temp=nodeItem.getChildNodes().item(1).getChildNodes();
					if (temp==null || temp.getLength()==0)
					{
						bf.append("\"");
					}
					else if (itemtype!=null && (itemtype.equals("NUMBER")||itemtype.equals("String")||itemtype.equals("VARCHAR")||itemtype.equals("CHAR")||itemtype.equals("VARCHAR2")))
					{
						bf.append("\"");
						bf.append(nodeItem.getChildNodes().item(1).getChildNodes().item(0).getNodeValue());	// /documents/row/item/value
						bf.append("\t\"");
						if(j==0)
						{
//							sheet.removeColumn();
						}
						if(j==1){
							String url=nodeItem.getChildNodes().item(1).getChildNodes().item(0).getNodeValue();
							System.out.println("---------online返回的URL=="+url);
							int index = url.lastIndexOf("stuId");
							int index1 = url.lastIndexOf("target");
							System.out.println("---------index=="+index);
							String stuId = url.substring(index+7, index1-3);
							System.out.println("---------policyNo=="+stuId);
							writeExecl(sheet,1,i+1,stuId);
						}
						else
						{
							writeExecl(sheet,j,i+1,nodeItem.getChildNodes().item(1).getChildNodes().item(0).getNodeValue());
						}
					}
					else
					{
						bf.append(nodeItem.getChildNodes().item(1).getChildNodes().item(0).getNodeValue());	// /documents/row/item/value
						writeExecl(sheet,j,i+1,nodeItem.getChildNodes().item(1).getChildNodes().item(0).getNodeValue());
					}
				}
				bf.append("\n");
			}
			workbook.write();
			workbook.close();
    	}
    	catch (Exception e)
    	{
    		System.out.println("------exception in dom2Csv:"+e);
    		e.printStackTrace();
    	}
    	System.out.println(filename);
    	return filename;
    }
	
		 public static void writeExecl(WritableSheet sheet,int i,int j,String str) throws WriteException{
				WritableFont wf = new jxl.write.WritableFont(WritableFont.createFont("宋体"),12, WritableFont.NO_BOLD, false,UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
				WritableCellFormat cf = new WritableCellFormat();
				Label voucherno = new Label(i,j,str); 
				voucherno.setCellFormat(cf);
				cf.setAlignment(Alignment.CENTRE);
				cf.setVerticalAlignment(VerticalAlignment.CENTRE);
				cf.setFont(wf);
				sheet.addCell(voucherno);
	    }
	 
		 
	    public String getFilename()
	    {
	    	String retstr=null;
	    	try
	    	{
	    		java.util.Date now=new java.util.Date();
	    		SimpleDateFormat fm=new SimpleDateFormat("yyyyMMdd");
	    		Random rm=new Random(now.getTime());
	    		retstr="com_"+fm.format(now)+rm.nextInt()+".xls";
	    	}
	    	catch (Exception e)
	    	{
	    		System.out.println("----ex in getFileName:"+e);
	    		e.printStackTrace();
	    	}
	    	return retstr;
	    }
}