package cn.hme.framework.helper.excel;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.web.multipart.MultipartFile;

import cn.hme.framework.helper.lang.CollectionHelper;
import cn.hme.framework.web.exception.BusinessException;

/**************************************
 * Excel通用工具,使用POI接口
 * 
 * @author: 张小庆
 * @date: 2016-02-22 21:55:50
 * @version: 2.0.0
 * @email: revisit@126.com
 * @Company: www.hmsoft.cn
 */
public abstract class ExcelReaderHelper {
	// excel2003扩展名
	public static final String EXCEL03_EXTENSION = ".xls";
	// excel2007扩展名
	public static final String EXCEL07_EXTENSION = ".xlsx";

	/***********************************************
	 * 将Excel的多张表读到Map数组里
	 * 
	 * @param file_name    Excel文件名 *
	 * @param title_rows   表头
	 * @param column_count 最大列数
	 * @return
	 */
	public static Map<String, List<List<String>>> readExcel(String file_name, int title_rows, int column_count) {
		return readExcel(file_name, null, title_rows, column_count);
	}

	/**********************
	 * 将Excel的多张表读到Map数组里
	 * 
	 * @param file_name    Excel文件名 *
	 * @param title_rows   表头
	 * @param column_count 最大列数
	 * @param is           数据流
	 * @return
	 */
	public static Map<String, List<List<String>>> readExcel(String file_name, InputStream is, int title_rows, int column_count) {
		ExcelRowReader reader = new ExcelRowReader();
		readAllSheet(reader, file_name, is);
		for (List<List<String>> array : reader.getValuesMap().values()) {
			if (array.size() >= title_rows) {
				for (int i = 1; i <= title_rows; i++)
					array.remove(0);
			}
			for (List<String> list : array) {
				CollectionHelper.padSize(list, column_count);
			}
		}
		return reader.getValuesMap();
	}

	/***********************************
	 * 将Excel的多张表读到Map数组里，没有表头信息
	 */
	public static Map<String, List<List<String>>> readExcel(String file_name) {
		return readExcel(file_name, null);
	}

	/***********************************
	 * 将Excel的多张表读到Map数组里，没有表头信息
	 */
	public static Map<String, List<List<String>>> readExcel(String file_name, InputStream is) {

		ExcelRowReader reader = new ExcelRowReader();
		readAllSheet(reader, file_name, is);
		return reader.getValuesMap();
	}

	public static List<List<String>> readSheet(MultipartFile file) {
		try {
			return readSheet(file.getOriginalFilename(), file.getInputStream());
		} catch (Exception e) {
			return null;
		}
	}

	public static List<List<String>> readSheet(MultipartFile file, int title_rows) {
		try {
			return readSheet(file.getOriginalFilename(), file.getInputStream(), title_rows);
		} catch (Exception e) {
			return null;
		}
	}

	public static List<List<String>> readSheet(MultipartFile file, int title_rows, int column_count) {
		try {
			return readSheet(file.getOriginalFilename(), file.getInputStream(), title_rows, column_count);
		} catch (Exception e) {
			return null;
		}
	}

	public static List<List<String>> readSheet(String file_name) {
		return readSheet(file_name, null, null, null, null);
	}

	public static List<List<String>> readSheet(String file_name, InputStream is) {
		return readSheet(file_name, is, null, null, null);
	}

	public static List<List<String>> readSheet(String file_name, int title_rows, int column_count) {
		return readSheet(file_name, null, null, title_rows, column_count);
	}

	public static List<List<String>> readSheet(String file_name, InputStream is, int title_rows, int column_count) {
		return readSheet(file_name, is, null, title_rows, column_count);
	}

	public static List<List<String>> readSheet(String file_name, InputStream is, int title_rows) {
		return readSheet(file_name, is, null, title_rows, null);
	}

	public static List<List<String>> readSheet(String file_name, InputStream is, String sheet_name, Integer title_rows, Integer column_count) {
		ExcelRowReader reader = new ExcelRowReader();
		readOneSheet(reader, file_name, is, sheet_name);
		List<List<String>> list = reader.getValues();
		if (title_rows != null) {
			if (list.size() >= title_rows) {
				for (int i = 1; i <= title_rows; i++)
					list.remove(0);
			}
		}
		if (column_count != null) {
			for (List<String> array : list)
				CollectionHelper.padSize(array, column_count);
		}
		return list;
	}

	public static List<List<String>> readSheet(String file_name, String sheet_name) {
		return readSheet(file_name, null, sheet_name, null, null);
	}

	private static void readOneSheet(ExcelRowReader reader, String file_name, InputStream is, String sheet_name) {
		try {
			// 处理excel2003文件
			if (file_name.endsWith(EXCEL03_EXTENSION)) {
				Excel2003Reader excel03 = new Excel2003Reader();
				excel03.setRowReader(reader);
				excel03.process(is == null ? new FileInputStream(file_name) : is);
				// 处理excel2007文件
			} else if (file_name.endsWith(EXCEL07_EXTENSION)) {
				// excel07.processOneSheet(fileName, 1);
				new Excel2007Reader(reader).processOneSheet(is == null ? new FileInputStream(file_name) : is, sheet_name);
			} else {
				throw new BusinessException("文件格式错误,fileName的扩展名只能是xls或xlsx。");
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new BusinessException("解析Excel文件出现错误，请检查是否存在或者文件格式是否正确。");

		}
	}

	private static void readAllSheet(ExcelRowReader reader, String file_name, InputStream is) {
		try {
			// 处理excel2003文件
			if (file_name.endsWith(EXCEL03_EXTENSION)) {
				Excel2003Reader excel03 = new Excel2003Reader();
				excel03.setRowReader(reader);
				excel03.process(is == null ? new FileInputStream(file_name) : is);

				// 处理excel2007文件
			} else if (file_name.endsWith(EXCEL07_EXTENSION)) {

				new Excel2007Reader(reader).process(is == null ? new FileInputStream(file_name) : is);
			} else {
				throw new BusinessException("文件格式错误,fileName的扩展名只能是xls或xlsx。");
			}
		} catch (Exception e) {
			// e.printStackTrace();
			throw new BusinessException("解析Excel文件出现错误，请检查是否存在或者文件格式是否正确。");

		}
	}

	public static Map<String, Integer> readColumnMap(List<String> array) {
		// List<String> array = list.get(0);
		Map<String, Integer> colMap = new HashMap<String, Integer>();
		for (int k = 0; k < array.size(); k++) {
			if (colMap.containsKey(array.get(k)))
				throw new BusinessException("Excel表第[" + k + "]列，列名[" + array.get(k) + "]已经存在");
			colMap.put(array.get(k), k);
		}
		return colMap;
	}
}
