package cn.com.countrygarden.bi.main.util.erp;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * @Author Owen
 * @Description excel解析工具类
 */
public class ImportExcelUtils {
	public int totalRows; // sheet中总行数
//	public static int totalCells; 静态变量 会导致并发问题
	public int totalCells; // 每一行总单元格数

	/**
	 * read the Excel .xlsx,.xls
	 * 
	 * @param file
	 *            jsp中的上传文件
	 * @return
	 * @throws Exception 
	 */
	// 读取excel文件
	public List<ArrayList<String>> readExcel(MultipartFile file , int sheetNum) throws Exception {
		if (file == null || ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())) {
			return null;
		} else {
			String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
			//空判断
			if (!ExcelUtil.EMPTY.equals(postfix)) {
				//.xls判断
				if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
					return readXls(file , sheetNum);
				//.xlsx判断
				} else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
					return readXlsx(file , sheetNum);
				} else {
					return null;
				}
			}
		}
		return null;
	}

	/**
	 * @throws Exception 
	 * @Author Owen
	 * @Description 解析.xlsx版本，存在list
	 */
	@SuppressWarnings("deprecation")
	public List<ArrayList<String>> readXlsx(MultipartFile file , int sheetNum ) throws Exception {
		List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
		// IO流读取文件
		InputStream input = null;
		XSSFWorkbook wb = null;
		ArrayList<String> rowList = null;
		try {
			input = file.getInputStream();
			// 创建文档
			wb = new XSSFWorkbook(input);
			// 读取sheet(页)
			for (int numSheet = 1; numSheet <= wb.getNumberOfSheets(); numSheet++) {
				if(sheetNum <= 0 || sheetNum > wb.getNumberOfSheets()){
					throw new Exception("指定的sheet页，不存在");
				}
				//读取指定的sheet(页)
				if(numSheet == sheetNum){
					XSSFSheet xssfSheet = wb.getSheetAt(numSheet-1);
					if (xssfSheet == null) {
						continue;
					}
					totalRows = xssfSheet.getLastRowNum();
					// 读取Row,从第二行开始
					for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
						XSSFRow xssfRow = xssfSheet.getRow(rowNum);
						if (xssfRow != null) {
							rowList = new ArrayList<String>();
							totalCells = xssfSheet.getRow(0).getLastCellNum()-1; //取列名行的最后一列作为每一行的总列数
							// 读取列，从第一列开始
							for (int c = 0; c <= totalCells ; c++) {
								XSSFCell cell = xssfRow.getCell(c);
								// 去除空行
								if (cell == null || "null".equals(cell.getRawValue()) || cell.getRawValue() == null) {
									rowList.add(ExcelUtil.EMPTY);
								} else {
									//去除换行、回车、缩进符
									Pattern pattern = Pattern.compile("\t|\r|\n");
									String value = ExcelUtil.getXValue(cell).trim(); //去掉首位空格
									value = value.replace(" ", ""); //去掉所有空格
									Matcher matcher = pattern.matcher(value);
									String rowValue = matcher.replaceAll("");
									rowList.add(rowValue);
								}
							}
							if (rowList.size() > 0) {
								list.add(rowList);
							}
						}
					}
				}
			}
			if(list.size() == 0){
				throw new Exception("指定的sheet页，不存在");
			}
			return list;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				input.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;

	}

	/**
	 * @throws Exception 
	 * @Author Owen
	 * @Description 解析.xls版本，存在list
	 */
	public List<ArrayList<String>> readXls(MultipartFile file, int sheetNum) throws Exception {
		List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
		// IO流读取文件
		InputStream input = null;
		HSSFWorkbook wb = null;
		ArrayList<String> rowList = null;
		try {
			input = file.getInputStream();
			// 创建文档
			wb = new HSSFWorkbook(input);
			// 读取sheet(页)
			for (int numSheet = 1; numSheet <= wb.getNumberOfSheets(); numSheet++) {
				if(sheetNum <= 0 || sheetNum > wb.getNumberOfSheets()){
					throw new Exception("指定的sheet页，不存在");
				}
				//读取指定的sheet(页)
				if(numSheet == sheetNum){
					HSSFSheet hssfSheet = wb.getSheetAt(numSheet-1);
					if (hssfSheet == null) {
						continue;
					}
					totalRows = hssfSheet.getLastRowNum();
					// 读取Row,从第二行开始
					for (int rowNum = 1; rowNum <= totalRows; rowNum++) {
						HSSFRow hssfRow = hssfSheet.getRow(rowNum);
						if (hssfRow != null) {
							rowList = new ArrayList<String>();
							totalCells = hssfRow.getLastCellNum();
							// 读取列，从第一列开始
							for (short c = 0; c <= totalCells + 1; c++) {
								HSSFCell cell = hssfRow.getCell(c);
								if (cell == null) {
									rowList.add(ExcelUtil.EMPTY);
									continue;
								}
								rowList.add(ExcelUtil.getHValue(cell).trim());
							}
							list.add(rowList);
						}
					}
				}
			}
			if(list.size() == 0){
				throw new Exception("指定的sheet页，不存在");
			}
			return list;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				input.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
}
