package com.yc.action.grid;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.yc.utils.DateUtil;
import com.yc.utils.Page;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * Jxl 的 Excel写数据器.
 * @author chenlb 2007-10-29 上午10:39:31
 */
public class JxlExcelWriter {
    
    /**
     * @param datas 封装着Object[]的列表, 一般是String内容.
     * @param title 每个sheet里的标题. filed是需要导出的字段
     * @throws ParseException 
     */
    public static void writeExcel(File file, Page page, String[] title,String[] filed,String[] panel_title,String[] panel_filed,List panel,String titles,String local) throws ParseException {
        List datas=page.getData();
	if(datas == null) {
            throw new IllegalArgumentException("没有数据导出到excel!");
        }
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(file);
            WritableSheet ws = workbook.createSheet("sheet 1", 0);
            int rowNum = 0;    //要写的行
            int tempNum=0;
            //写标题
            Label cell = new Label(5, 0, titles);
            ws.addCell(cell);
            
            //--end
            rowNum++;
            if(panel!=null){
            //1,写面板
            if(panel_title != null) {
        	
        	String[] s=local.split(";");
        	int samRow=0;
        	int temcol=0;
                for(int i=0; i<panel_title.length; i++) {//按照面板位置写到excel
                    String[] st=s[i].split("#")[1].split(",");//位置
                    int col=0;
                    int row=rowNum+Integer.parseInt(st[0]);//行
                    if(samRow!=row) 
                	temcol=0;
                    if(i>0)
                	col=temcol+Integer.parseInt(st[1]);//列
                    else
                	col=Integer.parseInt(st[1])-1;//列
                    Label lab = new Label(col,row , panel_title[i]);
                    ws.addCell(lab);//写入标题
                    //跟着要写入内容: 位置是col+1,row,
//                    int r = Integer.parseInt(st[2]);//TODO 处理跨行跨列
//                    int c = Integer.parseInt(st[3]) ;
//                    if( r != 0 || c != 0){ //合并单元格
//                       ws.mergeCells(cop,i,cop+c,r+i);
//                        cop += c;
//                    }
                    putRowC(ws, row, panel.get(0),new String[]{panel_filed[i]},col+1);    //压一行到sheet
                    tempNum=row;
                    temcol=col+1;
                    samRow=row;
                }
                
                
            }
//            for(int i=0; i<panel.size(); i++, rowNum++) {//写sheet
//                putRowC(ws, rowNum, panel.get(i),panel_filed);    //压一行到sheet
//            }
            
            //
            if(panel_title != null)
                 rowNum=tempNum+2;
            }
            
            
            
            //---------格线内容
            if(title != null) {
                putRow(ws, rowNum, title);//压入标题
                rowNum += 1;
            }
            for(int i=0; i<datas.size(); i++, rowNum++) {//写sheet
                putRowC(ws, rowNum, datas.get(i),filed,0);    //压一行到sheet
            }
            
            workbook.write();
            workbook.close();    //一定要关闭, 否则没有保存Excel
        } catch (RowsExceededException e) {
            System.out.println("jxl write RowsExceededException: "+e.getMessage());
        } catch (WriteException e) {
            System.out.println("jxl write WriteException: "+e.getMessage());
        } catch (IOException e) {
            System.out.println("jxl write file i/o exception!, cause by: "+e.getMessage());
        }
    }

    private static void putRow(WritableSheet ws, int rowNum, Object[] cells) throws RowsExceededException, WriteException {
        for(int j=0; j<cells.length; j++) {//写一行
            Label cell = new Label(j, rowNum, ""+cells[j]);
            ws.addCell(cell);
        }
    }
    private static void putRowC(WritableSheet ws, int rowNum, Object cells,String[] filed,int j) throws RowsExceededException, WriteException, ParseException {
        HashMap<String,Object> map=(HashMap<String,Object>)cells;
       boolean flg=false;
       if(j>0) flg=true;
        for(String s:filed){
            String[] temp=s.split(";");

            if(map.get(temp[0])!=null&&temp.length>1){//表示有格式
        	SimpleDateFormat formatDate = new SimpleDateFormat(temp[1].replaceAll("~", "-"));
        	if(DateUtil.isDate(formatDate.format(map.get(temp[0])))){//格式化日期 
        	    String st=formatDate.format(map.get(temp[0]));
        	    Label cell = new Label(j, rowNum,st);
                 ws.addCell(cell);
        	}
        	else{
            //格式化数字 
        	 
        	    int indx=temp[1].indexOf(".");
        	    String format="#.0";
        	    if(indx>0){//有小数位
        		//有千位
        		if(temp[1].startsWith(","))
        		    format=temp[1].replaceAll(",0.*\\.", "#,###."); 
        		else
        		    format=temp[1].replaceAll("0.*\\.", "#."); 
        	    }else{//整数
        		   format=temp[1].replaceAll("0", "#"); //是由于格线与java对于格式化的不同，需要在这里做转换
        	    } 
        	    
        	    if(map.get(temp[0]) instanceof Double){
           	     Double d=(Double)map.get(temp[0]);
           	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format); 
            	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
            	    jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN); 
                       ws.addCell(cell); 
           	 }else if(map.get(temp[0]) instanceof BigDecimal){
           	     BigDecimal d=(BigDecimal)map.get(temp[0]);
           	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format); 
             	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
             	    jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN); 
                        ws.addCell(cell); 
           	 }
           	 else if(map.get(temp[0]) instanceof Float){
           	     Float d=(Float)map.get(temp[0]);
           	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format); 
             	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
             	    jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN); 
                        ws.addCell(cell); 
           	 }else if(map.get(temp[0]) instanceof Integer){
           	     Integer d=(Integer)map.get(temp[0]);
           	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#"); 
            	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
            	   jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN); 
                      ws.addCell(cell); 
           	 }
           }
            }else{
          //普通字符 
        	 
        	 if(map.get(temp[0]) instanceof Double){
        	     Double d=(Double)map.get(temp[0]);
        	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.00"); 
         	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
         	    jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN); 
                    ws.addCell(cell); 
        	 }else if(map.get(temp[0]) instanceof BigDecimal){
        	     BigDecimal d=(BigDecimal)map.get(temp[0]);
        	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.00"); 
          	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
          	    jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN); 
                     ws.addCell(cell); 
        	 }
        	 else if(map.get(temp[0]) instanceof Float){
        	     Float d=(Float)map.get(temp[0]);
        	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.00"); 
          	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
          	    jxl.write.Number cell = new jxl.write.Number(j, rowNum,d.doubleValue(),wcfN); 
                     ws.addCell(cell); 
        	 }else if(map.get(temp[0]) instanceof Integer){
        	     Integer d=(Integer)map.get(temp[0]);
        	     jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#"); 
         	    jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
         	   jxl.write.Number cell = new jxl.write.Number(j, rowNum,d,wcfN); 
                   ws.addCell(cell); 
        	 }else{
            Label cell = new Label(j, rowNum, ""+((map.get(temp[0])==null||"null".equals(map.get(temp[0])))?"":map.get(temp[0])));
            ws.addCell(cell);
			}
            }
            if(!flg)  j++; 
        }
    }
}