package cn.base.excel;

import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import cn.base.util.FTPUtil;

public class ExportExcel {
	private static Logger logger = Logger.getLogger(ExportExcel.class);
	public void exportCommExcel(Map<String,String> fileMap,List<String> headerList,List footerList,List<Map> listData, HttpServletResponse responses){
		OutputStream os = null;
		try {
			os = responses.getOutputStream();
			responses.reset();// 清空输出流
			String fileName = fileMap.get("fileName").toString();
			fileName = toUtf8String(fileName);
			responses.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");// 设定输出文件头
			responses.setContentType("application/msexcel");// 定义输出类型
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFFont font = workbook.createFont();// 创建字体对象
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			font.setFontName("黑体 ");// 设置字体
			// Sheet名称
			HSSFSheet sheet = workbook.createSheet(fileMap.get("sheetName").toString());
			sheet.setDefaultColumnWidth(14);// 默认列宽
			// 合并单元格,表头说明信息
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,headerList.size()-1));
			HSSFCellStyle cellStyle = setHeadRegionStyle(workbook);// 设置合并头部单元格样式
			// 创建第一行
			HSSFRow row = sheet.createRow(0);
			row.setHeight((short) 400);
			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(fileMap.get("header").toString());
			HSSFCellStyle cellStyle2 = setRowsRegionStyle(workbook);// 设置表头单元格样式
			// 创建第二行，生成表头
			HSSFRow row2 = sheet.createRow(1);
			for(int i=0;i<headerList.size();i++){
				cell=row2.createCell(i);
				cell.setCellStyle(cellStyle2);
				cell.setCellValue(headerList.get(i));
			}
			HSSFCellStyle cs = setCommonStyle(workbook);// 设置普通单元格样式
			
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
			
			if(listData!=null&&listData.size()>0){
				for(int i=0;i<listData.size();i++){
					HSSFRow row3=sheet.createRow(i+2);
					for(int j=0;j<headerList.size();j++){
						cell=row3.createCell(j);
						cell.setCellStyle(cs);
						/*if(true){
							  //图片一导出到单元格B2中  
			                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)j, j, (short)(j+1), j+1);  
			                //图片二导出到单元格C3到E5中，且图片的left和top距离边框50  
			                HSSFClientAnchor anchor1 = new HSSFClientAnchor(50, 50, 0, 0,  
			                        (short) 2, 2, (short) 5, 5);  
			      
			                // 插入图片  
			                patriarch.createPicture(anchor, workbook.addPicture(FTPUtil.getFTPPictureBytes("ftp://Lenovo:fanlizhi@10.10.10.62/test/test.jpg"), HSSFWorkbook.PICTURE_TYPE_JPEG));  
//			                patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut1  
//			                        .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
						}*/
						if("null".equals(listData.get(i).get(headerList.get(j))+"")){
							cell.setCellValue("");
						}else{
							cell.setCellValue(listData.get(i).get(headerList.get(j))+"");
						}
					}
				}
				
				int footerrowNum = listData.size()+2;
				if(footerList!=null && footerList.size()>0){
					HSSFRow rowFooter = sheet.createRow(footerrowNum);//行列从0开始
					for(int i=0;i<footerList.size();i++){
						cell=rowFooter.createCell(i);
						cell.setCellStyle(cellStyle2);
						cell.setCellValue(footerList.get(i)+"");
					}
				}
				//动态合并单元格
				if(footerList!=null && footerList.size()>0){
					System.out.println("footerList.size()-1++++"+(footerList.size()-1));
					String[] footer = new String[footerList.size()-1];
					footer[0] = footerrowNum+","+footerrowNum+",0,1";
					for(int i=1;i<footerList.size()-1;i++){
						footer[i] = footerrowNum+","+footerrowNum+","+(i+1)+","+(i+1);
					}
					for(int i=0;i<footer.length;i++){
						String[] temp = footer[i].split(",");
						Integer startrow = Integer.parseInt(temp[0]);
						Integer endrow = Integer.parseInt(temp[1]);
						Integer startcol = Integer.parseInt(temp[2]);
						Integer endcol = Integer.parseInt(temp[3]);
						sheet.addMergedRegion(new CellRangeAddress(startrow,endrow,startcol,endcol));
					}
				}
				/*String[] footer = {
						footerrowNum+","+footerrowNum+",0,1",
						footerrowNum+","+footerrowNum+",2,2",
						footerrowNum+","+footerrowNum+",3,3",
						footerrowNum+","+footerrowNum+",4,4",
						footerrowNum+","+footerrowNum+",5,5",
						footerrowNum+","+footerrowNum+",6,6",
						footerrowNum+","+footerrowNum+",7,7"
						};*/
				
			}
			
			
			workbook.write(os);
			os.flush();    
			logger.info("成功导出Excel!");
		} catch (Exception ex) {
			logger.error("导出Excel出错了", ex);
		} finally {
			try {
				os.close();
			} catch (Exception ex) {
				logger.error("关闭os失败", ex);
			}
		}
	}

	// 把字符串转成utf8编码，保证中文文件名不会乱码
	public static String toUtf8String(String s) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < s.length(); i++) {
			char c = s.charAt(i);
			if (c >= 0 && c <= 255) {
				sb.append(c);
			} else {
				byte[] b;
				try {
					b = Character.toString(c).getBytes("utf-8");
				} catch (Exception ex) {
					b = new byte[0];
				}
				for (int j = 0; j < b.length; j++) {
					int k = b[j];
					if (k < 0)
						k += 256;
					sb.append("%" + Integer.toHexString(k).toUpperCase());
				}
			}
		}
		return sb.toString();
	}
	// 设置行普通单元格样式
			private static HSSFCellStyle setCommonStyle(HSSFWorkbook workbook) {
				HSSFCellStyle cellStyle = workbook.createCellStyle();
				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
				cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平右对齐
				cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
				return cellStyle;
			}
			
		// 设置行合并单元格样式 
		private static HSSFCellStyle setRowsRegionStyle(HSSFWorkbook workbook) {
			HSSFFont font = workbook.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
			font.setFontName("黑体");
			HSSFCellStyle cellStyle = setCommonStyle(workbook);
			cellStyle.setFont(font);
			return cellStyle;
		}

			
		// 设置行普通单元格样式
		private static HSSFCellStyle setHeaderStyle(HSSFWorkbook workbook) {
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
			return cellStyle;
		}
		
		// 设置头部合并单元格样式
		private static HSSFCellStyle setHeadRegionStyle(HSSFWorkbook workbook) {
			HSSFCellStyle cellStyle = setHeaderStyle(workbook);
			HSSFFont font = workbook.createFont();
			font.setFontHeightInPoints((short) 15);// 字体大小
			font.setFontName("宋体");// 字体类型
			cellStyle.setFont(font);
			return cellStyle;
		}
}
