package com.yeejoin.equipmanage.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 
 * <pre>
 * 导出excel表格工具类
 * </pre>
 *
 * 
 */
public class ExcelUtil
{

	/**
	 * 
	 * <pre>
	 * 根据源数据创建Excel表格
	 * </pre>
	 *
	 * @param dataList
	 * @return
	 */
	public static HSSFWorkbook createExcel(List<List<String>> dataList)
	{
		HSSFWorkbook workbook = new HSSFWorkbook();
		try
		{
			HSSFSheet sheet = workbook.createSheet();
			sheet.setDefaultColumnWidth((short) 15);

			HSSFCellStyle style = workbook.createCellStyle();
			setHSSFCellStyle(style);
			style.setFillForegroundColor((short) 9);
			HSSFFont font = workbook.createFont();
			font.setColor((short) 20);
			font.setFontHeightInPoints((short) 12);
			style.setFont(font);
			HSSFCellStyle style2 = workbook.createCellStyle();
			setHSSFCellStyle(style2);
			style2.setFillForegroundColor((short) 9);
			HSSFFont font2 = workbook.createFont();
			style2.setFont(font2);
			

			for (int i = 0; i < dataList.size(); i++)
			{
				HSSFRow row = sheet.createRow(i);
				List<String> list = (List<String>) dataList.get(i);
				for (short j = 0; j < list.size(); j = (short) (j + 1))
				{
					HSSFCell cell = row.createCell(j);
					// cell.setEncoding((short) 1);
					if (i == 0)
					{
						cell.setCellStyle(style);
					}
					else
					{
						cell.setCellStyle(style2);
					}
					cell.setCellValue(list.get(j));
				}
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return workbook;
	}
	
	public static void createXSSFExcel(String path, String fileName, List<String> headers, List<List<String>> dataList) {
		try {
			File file = new File(path);
			if (!file.isDirectory()) {
				file.mkdirs();
			}
			
			file = new File(path + "\\" + fileName);
			XSSFWorkbook workbook;
			if (!file.exists()) {
				workbook = new XSSFWorkbook();
			} else {
				workbook = new XSSFWorkbook(new FileInputStream(file));
			}
			SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 2000);
			int sheetNum = sxssfWorkbook.getNumberOfSheets();
			if (sheetNum == 0) {
				sxssfWorkbook.createSheet();
			}
			SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			if (lastRowNum > 1) {
				fillExcelContent(sheet, lastRowNum, dataList);
			} else {
				CellStyle style = sxssfWorkbook.createCellStyle();
				setHSSFCellStyle(style);
				style.setFillForegroundColor((short) 9);
				Font font = sxssfWorkbook.createFont();
				font.setColor((short) 20);
				font.setFontHeightInPoints((short) 12);
				style.setFont(font);
				CellStyle style2 = sxssfWorkbook.createCellStyle();
				setHSSFCellStyle(style2);
				style2.setFillForegroundColor((short) 9);
				Font font2 = sxssfWorkbook.createFont();
				style2.setFont(font2);
				
				SXSSFRow row = sheet.createRow(0);
				for (int i = 0; i < headers.size(); i++) {
					SXSSFCell cell = row.createCell(i);
					cell.setCellValue(headers.get(i));
				}
				fillExcelContent(sheet, sheet.getLastRowNum(), dataList);
			}
			FileOutputStream out = new FileOutputStream(file);
			sxssfWorkbook.write(out);
			out.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void fillExcelContent(SXSSFSheet sheet, int lastRowNum, List<List<String>> dataList) {
		for (int i = 0; i < dataList.size(); i++) {
			SXSSFRow row = sheet.createRow(lastRowNum + i + 1);
			List<String> list = (List<String>) dataList.get(i);
			for (short j = 0; j < list.size(); j = (short) (j + 1)) {
				SXSSFCell cell = row.createCell(j);
				cell.setCellValue(list.get(j));
			}
		}
	}
	
	/**
	 * 
	 * <pre>
	 * 输出表格
	 * </pre>
	 *
	 * @param response
	 * @param workbook
	 */
	public static void exportExcel(
			HttpServletResponse response,Workbook workbook, String fileName)
	{
		try
		{
//			String name = new String(fileName.getBytes("UTF-8"), "ISO8859_1");
			OutputStream output = response.getOutputStream();
//			response.setHeader("Content-disposition",
//					"attachment; filename=" + name);
//			response.setContentType("application/vnd.ms-excel;charset=utf-8");


			response.setCharacterEncoding("UTF-8");
			response.setHeader("content-Type", "application/vnd.ms-excel");
			response.setHeader("Content-Disposition",
					"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));


			workbook.write(output);
			output.flush();
			output.close();
//			// 输出Excel文件--保存到本地
//			 OutputStream output = new FileOutputStream(new File("F:/"+fileName));
//			 workbook.write(output); // 输出
//			 output.close();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * <pre>
	 * 输出表格
	 * </pre>
	 *
	 * @param response
	 * @param workbook
	 */
	public static void exportXlSXExcel(
			HttpServletResponse response, File file, String fileName)
	{
		try
		{
//			String name = new String(fileName.getBytes("UTF-8"), "ISO8859_1");
			OutputStream output = response.getOutputStream();
//			response.setHeader("Content-disposition",
//					"attachment; filename=" + name);
//			response.setContentType("application/vnd.ms-excel;charset=utf-8");

			response.setCharacterEncoding("UTF-8");
			response.setHeader("content-Type", "application/vnd.ms-excel");
			response.setHeader("Content-Disposition",
					"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));



			FileInputStream inputStream = new FileInputStream(file); 
			int b = 0;  
            byte[] buffer = new byte[1024*10];  
            while (b != -1){  
                b = inputStream.read(buffer);  
                if (-1 != b) {
                	output.write(buffer, 0, b);  
                }
            }  
            inputStream.close();
			output.flush();
			output.close();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * <pre>
	 * 创建盘点结果Excel表格数据
	 * </pre>
	 *
	 * @param exportDatas
	 * @return
	 */
	public static HSSFWorkbook createInventoryResultExcel(
			List<List<List<String>>> exportDatas)
	{

		HSSFWorkbook workbook = new HSSFWorkbook();
		try
		{
			HSSFSheet sheet = workbook.createSheet();
			sheet.setDefaultColumnWidth((short) 15);

			HSSFCellStyle style = workbook.createCellStyle();
			HSSFFont font = workbook.createFont();
			// font.setColor((short) 16);
			font.setBold(true);
			font.setFontHeightInPoints((short) 10);
			style.setFont(font);
			// style.setFillForegroundColor((short) 9);
			style.setAlignment(HorizontalAlignment.CENTER);
			setBorderStyle(style);

			HSSFCellStyle style2 = workbook.createCellStyle();
			HSSFFont font2 = workbook.createFont();
			style2.setFont(font2);
			style2.setWrapText(true);
			style2.setFillForegroundColor((short) 9);
			setBorderStyle(style2);

			HSSFCellStyle style3 = workbook.createCellStyle();
			HSSFFont font3 = workbook.createFont();
			font3.setColor((short) 18);
			font3.setFontHeightInPoints((short) 12);
			style3.setFont(font3);
			style3.setWrapText(true);
			// style3.setFillForegroundColor((short) 13);
			// style3.setAlignment(HorizontalAlignment.CENTER);
			style3.setVerticalAlignment(VerticalAlignment.CENTER);

			HSSFRow statisticRow = sheet.createRow(0);
			HSSFCell stacell = statisticRow.createCell((short) 0);
			String staValue = "";
			for (int i = 0; i < exportDatas.get(0).get(0).size(); i++)
			{
				if (i == 2)
				{
					staValue += exportDatas.get(0).get(0).get(i) + "："
							+ exportDatas.get(0).get(1).get(i) + "\r\n";
				}
				else
				{
					staValue += exportDatas.get(0).get(0).get(i) + "："
							+ exportDatas.get(0).get(1).get(i) + "    ";
				}

			}
			stacell.setCellValue(new HSSFRichTextString(staValue));
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,
					exportDatas.get(1).get(0).size() - 1));
			stacell.setCellStyle(style3);
			statisticRow.setHeightInPoints(40);
			statisticRow.getCell(0).setCellStyle(style3);

			for (int i = 2; i < exportDatas.get(1).size() + 2; i++)
			{
				HSSFRow row = sheet.createRow(i);
				List<String> list = (List<String>) exportDatas.get(1).get(i - 2);
				for (short j = 0; j < list.size(); j = (short) (j + 1))
				{
					HSSFCell cell = row.createCell(j);
					if (i == 2)
					{
						cell.setCellStyle(style);
					}
					else
					{
						cell.setCellStyle(style2);
					}
					cell.setCellValue(list.get(j));
				}
			}

		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return workbook;

	}
	
	private static void setBorderStyle(CellStyle style){
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
	}
	
	@SuppressWarnings("deprecation")
	private static void setHSSFCellStyle(CellStyle style){
		style.setLocked(true);    
		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);
//		style.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色    
//		style.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色    
//		style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色    
//		style.setFillForegroundColor(HSSFColor.WHITE.index);  
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
	}
	
	
	/**
	 * 创建车辆导出Excl,合并表头
	 * 
	 * @param carLists
	 * @return
	 */
	public static HSSFWorkbook createCarExportExcl(List<List<String>> carLists, String title) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		String head = "序号,所属单位,所属单位,所属单位,车辆类别,车辆名称,车辆号牌,国别,灭火性能,灭火性能,灭火性能,灭火性能,灭火性能,"
				+ "灭火性能,灭火性能,举高性能,举高性能,抢险救援性能,抢险救援性能,供气性能,国产/进口,国产/进口,底盘品牌,装备日期,"
				+ "有效期（年）,保养周期（天）,整车生产厂家,厂牌型号,发动机型号,底盘型号,发动机功率（千瓦）,"
				+ "总质量（千克）,比功率（千瓦/吨）,备注";
		String[] strings = head.split(",");
		List<CellRangeAddress> rangelist = new ArrayList<CellRangeAddress>();// 合并单元格集合
		rangelist.add(new CellRangeAddress(0, 0, 0, 33));//标题栏
		rangelist.add(new CellRangeAddress(1, 1, 8, 14));//灭火性能
		rangelist.add(new CellRangeAddress(1, 1, 15, 16));//举高性能
		rangelist.add(new CellRangeAddress(1, 1, 17, 18));//抢险救援性能
		rangelist.add(new CellRangeAddress(1, 2, 20, 21));//国产/进口
		rangelist.add(new CellRangeAddress(1, 3, 0, 0));//序号
		//rangelist.add(new CellRangeAddress(1, 3, 1, 1));//所属支队
		//rangelist.add(new CellRangeAddress(1, 3, 2, 2));//所属大队
		rangelist.add(new CellRangeAddress(1, 3, 1, 3));//所属单位
		rangelist.add(new CellRangeAddress(1, 3, 4, 4));//车辆类别
		rangelist.add(new CellRangeAddress(1, 3, 5, 5));//车辆名称
		rangelist.add(new CellRangeAddress(1, 3, 6, 6));//车辆号牌
		rangelist.add(new CellRangeAddress(1, 3, 7, 7));//国别
		rangelist.add(new CellRangeAddress(1, 3, 22, 22));//底盘品牌
		rangelist.add(new CellRangeAddress(1, 3, 23, 23));//装备日期
		rangelist.add(new CellRangeAddress(1, 3, 24, 24));//有效期
		rangelist.add(new CellRangeAddress(1, 3, 25, 25));//保养周期
		rangelist.add(new CellRangeAddress(1, 3, 26, 26));//整车生产厂家
		rangelist.add(new CellRangeAddress(1, 3, 27, 27));//厂牌型号
		rangelist.add(new CellRangeAddress(1, 3, 28, 28));//发动机型号
		rangelist.add(new CellRangeAddress(1, 3, 29, 29));//底盘型号
		rangelist.add(new CellRangeAddress(1, 3, 30, 30));//发动机功率
		rangelist.add(new CellRangeAddress(1, 3, 31, 31));//总质量
		rangelist.add(new CellRangeAddress(1, 3, 32, 32));//比功率
		rangelist.add(new CellRangeAddress(1, 3, 33, 33));//备注
		rangelist.add(new CellRangeAddress(2, 2, 11, 14));//载灭火剂量
		rangelist.add(new CellRangeAddress(2, 3, 8, 8));//水泵流量
		rangelist.add(new CellRangeAddress(2, 3, 9, 9));//水泡射程
		rangelist.add(new CellRangeAddress(2, 3, 10, 10));//车载人数
		rangelist.add(new CellRangeAddress(2, 3, 15, 15));//额定工作高度
		rangelist.add(new CellRangeAddress(2, 3, 16, 16));//工作斗载重量
		rangelist.add(new CellRangeAddress(2, 3, 17, 17));//起吊质量
		rangelist.add(new CellRangeAddress(2, 3, 18, 18));//牵引质量
		rangelist.add(new CellRangeAddress(2, 3, 19, 19));//同时充气气瓶数
		try {
			HSSFSheet sheet = workbook.createSheet();
			sheet.setDefaultColumnWidth((short) 15);
			HSSFFont columnTitleFont = workbook.createFont();
			columnTitleFont.setFontName("宋体");
			columnTitleFont.setFontHeightInPoints((short) 15);
			columnTitleFont.setBold(true);
			HSSFFont columnHeadFont = workbook.createFont();
			columnHeadFont.setFontName("宋体");
			columnHeadFont.setFontHeightInPoints((short) 10);
			columnHeadFont.setBold(true);
			HSSFCellStyle columnTitleStyle = workbook.createCellStyle();
			columnTitleStyle.setFont(columnTitleFont);
			setHSSFCellStyle(columnTitleStyle);
			HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
			columnHeadStyle.setFont(columnHeadFont);
			setHSSFCellStyle(columnHeadStyle);
			HSSFCellStyle style2 = workbook.createCellStyle();
			style2.setFillForegroundColor((short) 9);
			setHSSFCellStyle(style2);
			HSSFFont font2 = workbook.createFont();
			style2.setFont(font2);
			HSSFRow r0 = sheet.createRow(0);
			r0.setHeight((short) 500);
			HSSFCell c00 = r0.createCell(0);
			c00.setCellValue(new HSSFRichTextString(title));// 将第一行第一列设为标题
			c00.setCellStyle(columnTitleStyle);
			HSSFRow r1;
			for (int r = 1; r < 4; r++) {
				r1 = sheet.createRow(r);
				for (int i = 0; i < strings.length; i++) {
					HSSFCell cell = r1.createCell(i);
					if (r == 1) {
						cell.setCellValue(strings[i]);
					} else if (r == 2) {
						switch (i) {
						case 8:
							cell.setCellValue("水泵流量（升/分钟）");
							break;
						case 9:
							cell.setCellValue("水炮射程（米）");
							break;
						case 10:
							cell.setCellValue("乘员数量（含驾驶员）");
							break;
						case 11:
							cell.setCellValue("载灭火剂量");
							break;
						case 15:
							cell.setCellValue("额定工作高度（米）");
							break;
						case 16:
							cell.setCellValue("工作斗载重量（公斤）");
							break;
						case 17:
							cell.setCellValue("起吊质量（吨）");
							break;
						case 18:
							cell.setCellValue("牵引质量（吨）");
							break;
						case 19:
							cell.setCellValue("同时充气气瓶数（个）");
							break;
						}
					} else if (r == 3) {
						switch (i) {
						case 20:
							cell.setCellValue("整车");
							break;
						case 21:
							cell.setCellValue("底盘");
							break;
						case 11:
							cell.setCellValue("水（吨）");
							break;
						case 12:
							cell.setCellValue("泡沫（吨）");
							break;
						case 13:
							cell.setCellValue("干粉（吨）");
							break;
						case 14:
							cell.setCellValue("二氧化碳（升）");
							break;
						}
					}
					cell.setCellStyle(columnHeadStyle);
				}
			}

			rangelist.forEach(e -> sheet.addMergedRegion(e));
			
			for (int r = 4; r < carLists.size()+4; r++) {
				r1 = sheet.createRow(r);
				List<String> list = carLists.get(r-4);
				for (int i = 0; i < list.size(); i++) {
					if (1 == i) {
						sheet.addMergedRegion(new CellRangeAddress(r, r, 1, 3));
					}
					HSSFCell cell = r1.createCell(i);
					cell.setCellValue(list.get(i));
					cell.setCellStyle(style2);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return workbook;
	}

	/**
	 * <pre>
	 * 消防人员防护装备Excel导出格式（包含二装备级分类表头的均可通用，装备名称总量超过256的暂不支持导出）
	 * </pre>
	 *
	 * @param headers
	 * @param contents
	 * @param sheetName
	 * @return
	 */
	public static Sheet createProtectiveEquipmentExportExcel(
			Map<String,List<String>> headers,List<List<String>> contents, 
			String sheetName,Workbook workbook)
	{
		Sheet sheet = workbook.createSheet(sheetName);
		try
		{
			sheet.setDefaultColumnWidth((short) 15);

			/*************************表格内容格式*********************/
			CellStyle style = getContentStyle(workbook);

			/*************************表头格式**********************/
			CellStyle style2 = getHeaderStyle(workbook);

			/*************************标题格式**********************/
			CellStyle style3 = getTitleStyle(workbook);

			/********************标题行************************/
			setTitleValue(headers.get("title"), style3, sheet);

			/**********************一级表头**********************/
			List<CellRangeAddress> rangelist = new ArrayList<CellRangeAddress>();// 合并单元格集合
			Row firstHeadRow = sheet.createRow(1);
			firstHeadRow.setHeightInPoints(30);
			int firstHeadSize=headers.get("header1").size();
			int start=4;
			
			for (int i = 0; i < firstHeadSize; i++)
			{
				String[] firstHeadArray=headers.get("header1").get(i).split(":");
				String firstHeadValue =firstHeadArray[0];
				//计算所需合并单元格的坐标
				rangelist.add(new CellRangeAddress(
						1,1,start,start+Integer.valueOf(firstHeadArray[1])-1));
				
				for (int j = start; j <= start+Integer.valueOf(firstHeadArray[1])-1; j++)
				{
					Cell firstHeadCell=firstHeadRow.createCell(j);
					//需要为合并的每个单元格都赋值，否则第二个合并后的单元格值无法显示
					firstHeadCell.setCellValue(firstHeadValue);
					//设置单元格宽度自适应
//					sheet.setColumnWidth(j, firstHeadValue.getBytes().length*256);
					
				}
				
				start=start+Integer.valueOf(firstHeadArray[1]);//计算下一个标题的单元格起始位置
				
			}
			
			rangelist.forEach(e->{
				sheet.addMergedRegion(e);//合并单元格
				setRegionStyle(sheet,e,style2);//添加样式
			});
			
			/**********************二级表头**********************/
			rangelist.clear();// 清空合并单元格集合
			Row secondHeadRow = sheet.createRow(2);
			secondHeadRow.setHeightInPoints(120);
			int secondHeadSize=headers.get("header2").size();
			
			for (int i = 0; i < secondHeadSize; i++)
			{
				String secondHeadValue=headers.get("header2").get(i);
				Cell secondHeadCell=secondHeadRow.createCell(i);
				secondHeadCell.setCellValue(secondHeadValue);
				secondHeadCell.setCellStyle(style2);
				//计算所需合并单元格的坐标
				if(i<4){
					Cell firstHeadCell=firstHeadRow.createCell(i);
					firstHeadCell.setCellValue(secondHeadValue);
//					sheet.setColumnWidth(i, secondHeadValue.getBytes().length*256);//设置单元格宽度自适应
					rangelist.add(new CellRangeAddress(1, 2, i,i));
				}else 
				{
					sheet.setColumnWidth(i, 1536);//设置单元格宽度为两个中文宽度
				}
				
			}
			
			rangelist.forEach(e->{
				sheet.addMergedRegion(e);//合并单元格
				setRegionStyle(sheet,e,style2);//添加样式
			});
			
			/**********************填充表格内容第一行**********************/
			Row startContentRow = sheet.createRow(3);
			setFirstContentValue(headers.get("startContent"), style, startContentRow);
			sheet.addMergedRegion(new CellRangeAddress(3,3,0,1));
			
			/**********************填充表格内容中间**********************/
			setMidContentValue(contents, style, sheet, 4);

			/**********************填充表格内容最后一行**********************/
			int titleSize =Integer.valueOf(headers.get("title").get(0).split(":")[1]);
			Row endContentRow = sheet.createRow(4+contents.size());
			endContentRow.setHeightInPoints(30);
			sheet.addMergedRegion(new CellRangeAddress
					(4+contents.size(),4+contents.size(),0,titleSize-1));
			setContentValue(
					headers.get("endContent"), getEndContentStyle(workbook), endContentRow);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return sheet;
	}
	
	/**
	 * <pre>
	 * 消防车辆Excel导出格式（表头只有装备定义名称的均可通用）
	 * </pre>
	 *
	 * @param datas
	 * @param sheetName
	 * @return
	 */
	public static Sheet createFireVehicleExportExcel(
			Map<String,List<String>> headers,List<List<String>> contents, 
			String sheetName,Workbook workbook)
	{
		Sheet sheet = workbook.createSheet(sheetName);
		try
		{
			sheet.setDefaultColumnWidth((short) 15);
			
			/*************************表格内容格式*********************/
			CellStyle style = getContentStyle(workbook);
			
			/*************************表头格式**********************/
			CellStyle style2 = getHeaderStyle(workbook);
			
			/*************************标题格式**********************/
			CellStyle style3 = getTitleStyle(workbook);
			
			/********************填充标题行************************/
			setTitleValue(headers.get("title"), style3, sheet);
			
			/**********************填充表头**********************/
			Row headRow = sheet.createRow(1);
			headRow.setHeightInPoints(120);
			setContentValue(headers.get("header"),style2,headRow);
			
			/**********************填充表格内容第一行**********************/
			Row startContentRow = sheet.createRow(2);
			setFirstContentValue(headers.get("startContent"), style, startContentRow);
			sheet.addMergedRegion(new CellRangeAddress(2,2,0,1));
			
			/**********************填充表格内容中间**********************/
			setMidContentValue(contents, style, sheet, 3);
			
			/**********************填充表格内容最后一行**********************/
			int titleSize =Integer.valueOf(headers.get("title").get(0).split(":")[1]);
			Row endContentRow = sheet.createRow(3+contents.size());
			endContentRow.setHeightInPoints(30);
			sheet.addMergedRegion(new CellRangeAddress
					(3+contents.size(),3+contents.size(),0,titleSize-1));
			setContentValue(
					headers.get("endContent"), getEndContentStyle(workbook), endContentRow);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return sheet;
	}
	
	/**
	 * 
	 * <pre>
	 * 设置合并后的单元格格式
	 * </pre>
	 *
	 * @param sheet
	 * @param region
	 * @param cs
	 */
	public static void setRegionStyle(Sheet sheet, CellRangeAddress region,CellStyle cs) 
	{
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {

            Row row = sheet.getRow(i);
            if (row == null){
                row = sheet.createRow(i);
            }
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
	}
    
	/**
	 * 
	 * <pre>
	 * 表格内容格式
	 * </pre>
	 *
	 * @param workbook
	 * @return
	 */
	public static CellStyle getContentStyle(Workbook workbook)
	{
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		// font.setColor((short) 16);
		//font.setBold(true);
		font.setFontHeightInPoints((short) 10);
		style.setFont(font);
		// style.setFillForegroundColor((short) 9);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		setBorderStyle(style);
		
		return style;
	}
	
	/**
	 * 
	 * <pre>
	 * 最后一行内容格式
	 * </pre>
	 *
	 * @param workbook
	 * @return
	 */
	public static CellStyle getEndContentStyle(Workbook workbook)
	{
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		style.setFont(font);
		style.setAlignment(HorizontalAlignment.LEFT);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		
		return style;
	}
	
	/**
	 * 
	 * <pre>
	 * 表头格式
	 * </pre>
	 *
	 * @param workbook
	 * @return
	 */
	public static CellStyle getHeaderStyle(Workbook workbook)
	{
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setColor((short) 18);
		font.setFontHeightInPoints((short) 12);
		style.setFont(font);
		style.setWrapText(true);
		style.setFillForegroundColor((short) 9);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		setBorderStyle(style);
		
		return style;
	}
	
	/**
	 * 
	 * <pre>
	 * 标题格式
	 * </pre>
	 *
	 * @param workbook
	 * @return
	 */
	public static CellStyle getTitleStyle(Workbook workbook)
	{
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setBold(true);
		font.setColor((short) 12);
		font.setFontHeightInPoints((short) 20);
		style.setFont(font);
		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		// style3.setFillForegroundColor((short) 13);
		
		return style;
	}
	
	/**
	 * 
	 * <pre>
	 * 填充标题行内容
	 * </pre>
	 *
	 * @param datas
	 * @param style
	 * @param sheet
	 */
	public static void setTitleValue(List<String> datas,CellStyle style,Sheet sheet)
	{
		Row titleRow = sheet.createRow(0);
		Cell titleCell = titleRow.createCell((short) 0);
		String[] titleArray=datas.get(0).split(":");
		String titleValue =titleArray[0];
		int titleSize =Integer.valueOf(titleArray[1]);
		
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,titleSize-1));
		titleCell.setCellValue(titleValue);
		titleCell.setCellStyle(style);
		titleRow.setHeightInPoints(40);
		titleRow.getCell(0).setCellStyle(style);
	}
	
	/**
	 * 
	 * <pre>
	 * 填充单元格内容（无单元格合并,无数据格式转换）
	 * </pre>
	 *
	 * @param datas
	 * @param style
	 * @param row
	 */
	public static void setContentValue(List<String> datas,CellStyle style,Row row)
	{
		if (datas.size()>0)
		{
			for (int i = 0; i < datas.size(); i++)
			{
				Cell cell=row.createCell(i);
				cell.setCellValue(datas.get(i));
				cell.setCellStyle(style);
				if(i>3){
					row.getSheet().setColumnWidth(i, 1536);//设置单元格宽度自适应
				}
			}
		}
	}
	
	/**
	 * 
	 * <pre>
	 * 填充表格内容第一行数据（第4列开始转换为Double数据类型）
	 * </pre>
	 *
	 * @param datas
	 * @param style
	 * @param row
	 */
	public static void setFirstContentValue(List<String> datas,CellStyle style,Row row)
	{
		if (datas.size()>0)
		{
			for (int i = 0; i < datas.size(); i++)
			{
				Cell cell=row.createCell(i);
				if (i>3&&StringUtil.isNumeric(datas.get(i)))
				{
					cell.setCellValue(Double.valueOf(datas.get(i)));
				}else
				{
					cell.setCellValue(datas.get(i));
				}
				cell.setCellStyle(style);
			}
		}
	}
	
	/**
	 * 
	 * <pre>
	 * 填充表格中间内容多行数据（第4列开始转换为Double数据类型）
	 * </pre>
	 *
	 * @param datas
	 * @param style
	 * @param sheet
	 * @param startRow 起始行
	 */
	public static void setMidContentValue(
			List<List<String>> datas,CellStyle style,Sheet sheet,int startRow)
	{
		if (datas.size()>0)
		{
			for (int i = startRow; i < datas.size() + startRow; i++)
			{
				Row row = sheet.createRow(i);
				List<String> list = datas.get(i-startRow);
				for (int j = 0; j < list.size(); j++)
				{
					Cell cell = row.createCell(j);
					if (j>3&&StringUtil.isNumeric(list.get(j)))
					{
						cell.setCellValue(Double.valueOf(list.get(j)));
					}else
					{
						cell.setCellValue(list.get(j));
//						sheet.setColumnWidth(j, list.get(j).getBytes().length*256*2);//设置单元格宽度自适应
					}
					cell.setCellStyle(style);
				}
			}
		}
	}
	
	
	
	
	
}