import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Random;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.log4j.Logger;

/**
 * jexcelApi工具类
 * 
 * @author Yaozb 2015/7/20
 */
public class JExcelApiUtil {

	final static Logger log = Logger.getLogger(JExcelApiUtil.class);

	/**
	 * 生成文件存储路径
	 * 
	 * @Author Yaozb 2015/7/21
	 * @return
	 */
	public String createFilName(String reportName) {
		return reportName+"_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + (new Random().nextInt(900) + 100);
	}

	/**
	 * 生成excel表格
	 * 
	 * @author Yaozb 2015/7/21
	 * @param response HttpServletResponse  
	 * @param reportName 报表名称
	 * @param titles 表头
	 * @param keys 数据key值
	 * @param list 数据列表
	 * @throws WriteException
	 * @throws IOException
	 */
	public ServletOutputStream createExcel(HttpServletRequest request,HttpServletResponse response,String reportName,String[] titles,List<HashMap<String,Object>> list,int columnWidth)
			throws WriteException,IOException {
//		String fileName = createFilName(reportName);
		String fileName = reportName;

		setFileDownloadHeader(request,response,fileName);
		
		//创建工作薄
		ServletOutputStream os = response.getOutputStream();
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		//创建新的一页
		WritableSheet sheet = workbook.createSheet(reportName,0);
		//创建报表名称
		//用于标题
		sheet.mergeCells(0,titles.length-1,0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
        WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
        WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
        titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
        titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
        titleFormate.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        Label title = new Label(0,reportName,titleFormate);
        sheet.setRowView(0,600,false);//设置第一行的高度
        sheet.addCell(title);
        
    	//创建title
		WritableFont bold2 = new WritableFont(WritableFont.ARIAL,8,WritableFont.BOLD);
        WritableCellFormat titleFormate2 = new WritableCellFormat(bold2);
        titleFormate2.setAlignment(jxl.format.Alignment.CENTRE);
        titleFormate2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
        titleFormate2.setBackground(Colour.GRAY_25);
        titleFormate2.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		for(int i=0;i<titles.length;i++){
			Label cell = new Label(i,1,titles[i],titleFormate2);
			sheet.addCell(cell);
			sheet.setColumnView(i,columnWidth);
		}
		
		sheet.setRowView(1,400,false);//设置第二行的高度
        
		//创建内容
        WritableCellFormat titleFormate3 = new WritableCellFormat();
        titleFormate3.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
		for(int i=0;i<list.size();i++){
			HashMap<String,Object> map = list.get(i);
			for(int j=0;j<titles.length;j++){
				Label label = new Label(j,i+2,map.get(titles[j]).toString(),titleFormate3);//第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
				sheet.addCell(label);
			}
		}
	

		//把创建的内容写入到输出流中，并关闭输出流
		workbook.write();
		workbook.close();
		return os;
	}
	
	public void setFileDownloadHeader(HttpServletRequest request,String fileName) {
		response.setCharacterEncoding("UTF-8");
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
        final String userAgent = request.getHeader("USER-AGENT");
        try {
            String finalFileName = null;
            if(userAgent.indexOf("MSIE")!=-1){
                finalFileName = URLEncoder.encode(fileName,"UTF8");
            }else if(userAgent.indexOf("Mozilla")!=-1){//google,火狐浏览器
                finalFileName = new String(fileName.getBytes(),"ISO8859-1");
            }else{
                finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
            }
            response.setHeader("Content-Disposition","attachment; filename=\"" + finalFileName + ".xls\"");//这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开
        } catch (UnsupportedEncodingException e) {
        }
    }
}