package cn.hme.framework.helper.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
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.VerticalAlignment;
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;
import org.springframework.util.StringUtils;

public final class Excel2007Writer {
	/**********************
	 * 标题字体
	 */
	public final static String TitleFontFamilyName = "TitleFontFamilyName";
	/**********************
	 * 标题字体拉伸
	 */
	public final static String TitleBoldWeightName = "TitleBoldWeightName";
	/**********************
	 * 标题字体高度
	 */
	public final static String TitleFontHeightName = "TitleFontHeightName";
	/***********************
	 * 标题字体是否加粗
	 */
	public final static String TitleFontBoldName = "TitleFontBoldName";
	/***********************
	 * 标题高度
	 */
	public final static String TitleHeightName = "TitleHeightName";
	/**********************
	 * 行字体
	 */
	public final static String RowFontFamilyName = "RowFontFamilyName";
	/**********************
	 * 行字体高度
	 */
	public final static String RowFontHeightName = "RowFontHeightName";
	/***********************
	 * 行字体是否加粗
	 */
	public final static String RowFontBoldName = "RowFontBoldName";
	/***********************
	 * 行高度
	 */
	public final static String RowHeightName = "RowHeightName";
	/***********************
	 * 行高度默认值
	 */
	public static short RowHeightValue = 500;

	/********************
	 * 单次写入最大行，防止内存溢出
	 */
	public static int ExcelRowLimit = 10000;

	/**********************************
	 * 创建Excel标题Map
	 */
	public final static Map<String, Integer> createTitleMap(String value, int width) {
		Map<String, Integer> map = new LinkedHashMap<String, Integer>();
		map.put(value, width);
		return map;
	}

	/*********************************************
	 * 根据行数组生成Excel
	 */
	public static SXSSFWorkbook createExcel(List<List<Object>> values) {
		return createExcel(values, new HashMap<String, Object>());
	}

	/******************************
	 * 根据行数组生成Excel
	 * 
	 * @param paramMap 格式化参数
	 * @param values
	 */
	public static SXSSFWorkbook createExcel(List<List<Object>> values, Map<String, Object> paramMap) {
		return createExcel(values, "sheet1", paramMap);
	}

	/*************************************
	 * 根据行数组生成Exce
	 * 
	 * @param values
	 * @param sheet_name 表名
	 * @param paramMap   格式化参数
	 * @return
	 */
	public static SXSSFWorkbook createExcel(List<List<Object>> values, String sheet_name, Map<String, Object> paramMap) {
		return createExcel(values, null, sheet_name, paramMap);
	}

	public static SXSSFWorkbook createExcel(List<List<Object>> values, Map<String, Integer> titleMap, String sheet_name,
			Map<String, Object> paramMap) {
		//
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, ExcelRowLimit);
		// XSSFWorkbook wb = new XSSFWorkbook();
		createSheet(workbook, values, titleMap, sheet_name, paramMap);
		return workbook;
	}

	public static SXSSFWorkbook createExcel(Map<String, List<List<Object>>> values, Map<String, Integer> titleMap,
			Map<String, Object> paramMap) {
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, ExcelRowLimit);
		for (String sheet_name : values.keySet())
			createSheet(workbook, values.get(sheet_name), titleMap, sheet_name, paramMap);
		return workbook;
	}

	public static SXSSFWorkbook createExcel(Map<String, List<List<Object>>> values, Map<String, Integer> titleMap) {
		return createExcel(values, titleMap, new HashMap<String, Object>());
	}

	public static SXSSFWorkbook createExcel(Map<String, List<List<Object>>> values) {
		return createExcel(values, null);
	}

	/*************************
	 * 根据行数组生成Exce
	 * 
	 * @param values
	 * @param sheet_name 表名
	 */
	public static SXSSFWorkbook createExcel(List<List<Object>> values, String sheet_name) {
		return createExcel(values, sheet_name, new HashMap<String, Object>());
	}

	public static void createSheet(SXSSFWorkbook wb, Map<String, List<List<Object>>> values,
			Map<String, Integer> titleMap, Map<String, Object> paramMap) {
		for (String sheet_name : values.keySet())
			createSheet(wb, values.get(sheet_name), titleMap, sheet_name, paramMap);
	}

	public static void createSheet(SXSSFWorkbook wb, List<List<Object>> values, Map<String, Integer> titleMap,
			String sheet_name) {
		createSheet(wb, values, titleMap, sheet_name, new HashMap<String, Object>());
	}

	public static void createSheet(SXSSFWorkbook wb, List<List<Object>> values, String sheet_name) {
		createSheet(wb, values, null, sheet_name, new HashMap<String, Object>());
	}

	/**************************
	 * 
	 * @param wb         Excel表
	 * @param values
	 * @param titleMap   标题信息
	 * @param sheet_name 表名
	 * @param paramMap   格式化参数
	 */

	public static void createSheet(SXSSFWorkbook wb, List<List<Object>> values, Map<String, Integer> titleMap,
			String sheet_name, Map<String, Object> paramMap) {
		// 创建Excel的工作书册 Workbook,对应到一个excel文档
		// = new XSSFWorkbook();
		// 创建Excel的工作sheet,对应到一个excel文档的tab
		if (!StringUtils.hasText(sheet_name))
			sheet_name = "sheet1";
		SXSSFSheet sheet = wb.createSheet(sheet_name);

		// 设置excel每列宽度
		// sheet.setColumnWidth(0, 4000);
		// sheet.setColumnWidth(1, 3500);

		// 创建字体样式
		Font font = wb.createFont();
		// 字符集
		if (paramMap.containsKey(RowFontFamilyName)) {
			try {
				font.setFontName(paramMap.get(RowFontFamilyName).toString());
			} catch (Exception e) {

			}
		}
		if (paramMap.containsKey(RowFontHeightName)) {
			try {
				font.setFontHeight(Short.parseShort(paramMap.get(RowFontHeightName).toString()));
			} catch (Exception e) {

			}
		}
		if (paramMap.containsKey(RowFontBoldName))
			font.setBold(true);

		// font.setColor(XSSFColor.);

		// 创建单元格样式
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.LEFT);
		style.setVerticalAlignment(VerticalAlignment.CENTER);

		// 设置边框
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setFont(font);// 设置字体
		// 不自动换行
		style.setWrapText(false);

		int startIndex = 0;
		// 标题
		if (titleMap != null && titleMap.values().size() > 0) {
			// 创建字体样式
			Font vfont = wb.createFont();
			// 字符集
			if (paramMap.containsKey(TitleFontFamilyName)) {
				try {
					vfont.setFontName(paramMap.get(TitleFontFamilyName).toString());
				} catch (Exception e) {

				}
			} else {
				if (paramMap.containsKey(RowFontFamilyName)) {
					try {
						vfont.setFontName(paramMap.get(RowFontFamilyName).toString());
					} catch (Exception e) {

					}
				}
			}
			if (paramMap.containsKey(TitleFontHeightName)) {
				try {
					vfont.setFontHeight(Short.parseShort(paramMap.get(TitleFontHeightName).toString()));
				} catch (Exception e) {

				}
			} else {
				if (paramMap.containsKey(RowFontHeightName)) {
					try {
						vfont.setFontHeight(Short.parseShort(paramMap.get(RowFontHeightName).toString()));
					} catch (Exception e) {

					}
				}
			}
			if (paramMap.containsKey(TitleFontBoldName))
				vfont.setBold(true);
			else {
				if (paramMap.containsKey(RowFontBoldName))
					vfont.setBold(true);

			}
			SXSSFRow row = sheet.createRow(0);
			// 设定行的高度
			if (paramMap.containsKey(TitleHeightName)) {
				try {
					row.setHeight(Short.parseShort(paramMap.get(TitleHeightName).toString()));
				} catch (Exception e) {
					row.setHeight(RowHeightValue);
				}
			} else
				row.setHeight(RowHeightValue);
			int index = 0;

			for (String key : titleMap.keySet()) {
				SXSSFCell cell = row.createCell(index);
				cell.setCellStyle(style);
				cell.setCellValue(key);
				sheet.setColumnWidth(index, titleMap.get(key) * 256);
				index++;
			}

			startIndex = 1;

		}

		// 创建Excel的sheet的一行
		for (int rowNum = 0; rowNum < values.size(); rowNum++) {
			SXSSFRow row = sheet.createRow(rowNum + startIndex);
			// 设定行的高度
			if (paramMap.containsKey(RowHeightName)) {
				try {
					row.setHeight(Short.parseShort(paramMap.get(RowHeightName).toString()));
				} catch (Exception e) {
					row.setHeight(RowHeightValue);
				}
			} else
				row.setHeight(RowHeightValue);
			// 创建一个Excel的单元格
			for (int colNum = 0; colNum < values.get(rowNum).size(); colNum++) {
				SXSSFCell cell = row.createCell(colNum);
				cell.setCellStyle(style);
				Object value = values.get(rowNum).get(colNum);
				if (value == null)
					continue;
				// 给Excel的单元格设置样式和赋值
				cell.setCellValue(value.toString());

			}

			// 合并单元格(startRow，endRow，startColumn，endColumn)
			// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

		}
	}

	public static void downloadAsExcel(HttpServletResponse response, String fileName,
			Map<String, List<List<Object>>> values) {
		downloadAsExcel(response, fileName, values, null, new HashMap<String, Object>());

	}

	public static void downloadAsExcel(HttpServletResponse response, String fileName,
			Map<String, List<List<Object>>> values, Map<String, Integer> titleMap) {
		downloadAsExcel(response, fileName, values, titleMap, new HashMap<String, Object>());

	}

	public static void downloadAsExcel(HttpServletResponse response, String fileName,
			Map<String, List<List<Object>>> values, Map<String, Integer> titleMap, Map<String, Object> paramMap) {
		SXSSFWorkbook wb = createExcel(values, titleMap, paramMap);
		// 输出文档
		responseAsFile(response, fileName, wb);

	}

	public static void downloadAsExcel(HttpServletResponse response, String fileName, List<List<Object>> values) {
		downloadAsExcel(response, fileName, values, null, new HashMap<String, Object>());

	}

	public static void downloadAsExcel(HttpServletResponse response, String fileName, List<List<Object>> values,
			Map<String, Integer> titleMap) {
		downloadAsExcel(response, fileName, values, titleMap, new HashMap<String, Object>());

	}

	public static void downloadAsExcel(HttpServletResponse response, String fileName, List<List<Object>> values,
			Map<String, Integer> titleMap, Map<String, Object> paramMap) {
		SXSSFWorkbook wb = createExcel(values, titleMap, null, paramMap);
		// 输出文档
		responseAsFile(response, fileName, wb);

	}

	private static void responseAsFile(HttpServletResponse response, String fileName, SXSSFWorkbook wb) {
		response.setCharacterEncoding("utf8");
		// response.setContentType("application/vnd.ms-excel");
		response.setContentType("application/x-download");
		fileName = fileName + ".xlsx";
		OutputStream out = null;
		try {
			response.addHeader("Content-Disposition",
					"attachment;filename=\"" + new String(fileName.getBytes("utf8"), "ISO_8859_1") + "\"");
			out = response.getOutputStream();
			wb.write(out);
			out.close();
		} catch (Exception e) {

		} finally {
			try {
				wb.close();
			} catch (Exception ex) {

			}
		}
	}

	public static void saveAsExcel(File file, Map<String, List<List<Object>>> values) {
		saveAsExcel(file, values, null, new HashMap<String, Object>());

	}

	public static void saveAsExcel(File file, Map<String, List<List<Object>>> values, Map<String, Integer> titleMap) {
		saveAsExcel(file, values, titleMap, new HashMap<String, Object>());

	}

	public static void saveAsExcel(File file, Map<String, List<List<Object>>> values, Map<String, Integer> titleMap,
			Map<String, Object> paramMap) {
		SXSSFWorkbook wb = createExcel(values, titleMap, paramMap);
		// 输出文档
		saveAsFile(file, wb);

	}

	public static void saveAsExcel(File file, List<List<Object>> values) {
		saveAsExcel(file, values, null, new HashMap<String, Object>());

	}

	public static void saveAsExcel(File file, List<List<Object>> values, Map<String, Integer> titleMap) {
		saveAsExcel(file, values, titleMap, new HashMap<String, Object>());

	}

	public static void saveAsExcel(File file, List<List<Object>> values, Map<String, Integer> titleMap,
			Map<String, Object> paramMap) {
		SXSSFWorkbook wb = createExcel(values, titleMap, null, paramMap);
		// 输出文档
		saveAsFile(file, wb);

	}

	private static void saveAsFile(File file, SXSSFWorkbook wb) {
		OutputStream out = null;
		try {
			out = new FileOutputStream(file);
			wb.write(out);
		} catch (Exception e) {

		} finally {
			try {
				out.close();
			} catch (Exception ex) {

			}
			try {
				wb.close();
			} catch (Exception ex) {

			}
		}
	}

}
