package cn.hme.framework.helper.excel;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.util.StringUtils;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import cn.hme.framework.helper.date.LocalDateHelper;
import cn.hme.framework.helper.lang.StringHelper;
import cn.hme.framework.helper.log.LogHelper;

/**
 * 抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析 xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低 内存的耗费，特别使用于大数据量的文件。
 * 
 */
public class Excel2007Reader extends DefaultHandler {
	enum xssfDataType {
		BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
	}

	// 共享字符串表
	// Table with styles
	private StylesTable stylesTable;
	private xssfDataType nextDataType;
	// Table with unique strings
	private ReadOnlySharedStringsTable sharedStringsTable;

	// Used to format numeric cell values.
	private short formatIndex;
	private String formatString;

	private int thisColumn = -1;
	// The last column printed to the output stream
	private int lastColumnNumber = -1;

	// Gathers characters as they are seen.
	private StringBuffer value = new StringBuffer();
	// Set when V start element is seen
	private boolean vIsOpen;

	private List<String> rowlist = new ArrayList<String>();
	private ExcelRowReader rowReader;

	private String sheetName;

	private int curRow = 0;

	public Excel2007Reader(ExcelRowReader rowReader) {
		this.rowReader = rowReader;
	}

	/**
	 * 只遍历一个电子表格，
	 */

	public void processOneSheet(InputStream is, String sheet_name) throws Exception {
		OPCPackage pkg = OPCPackage.open(is);
		processOneSheet(pkg, sheet_name);
	}

	public void processOneSheet(OPCPackage pkg, String sheet_name) throws Exception {
		this.sharedStringsTable = new ReadOnlySharedStringsTable(pkg);
		XSSFReader xssfReader = new XSSFReader(pkg);
		this.stylesTable = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
		if (!iter.hasNext()) {
			return;
		}
		InputStream stream = null;
		if (!StringUtils.hasText(sheet_name)) {
			stream = iter.next();
		} else {
			while (iter.hasNext()) {
				if (sheetName.equals(sheet_name)) {
					stream = iter.next();
					break;

				}
			}
		}

		// 没找到表
		if (stream == null)
			return;
		this.sheetName = iter.getSheetName();
		this.rowReader.getValuesMap().put(sheetName, new ArrayList<List<String>>());
		InputSource sheetSource = new InputSource(stream);
		SAXParserFactory saxFactory = SAXParserFactory.newInstance();
		SAXParser saxParser = saxFactory.newSAXParser();
		XMLReader sheetParser = saxParser.getXMLReader();
		sheetParser.setContentHandler(this);
		sheetParser.parse(sheetSource);

		stream.close();

	}

	/**
	 * 遍历工作簿中所有的电子表格
	 * 
	 * @param filename
	 * @throws Exception
	 */
	public void process(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		process(pkg);
	}

	public void process(InputStream is) throws Exception {
		OPCPackage pkg = OPCPackage.open(is);
		process(pkg);
	}

	private void process(OPCPackage pkg) throws Exception {
		this.sharedStringsTable = new ReadOnlySharedStringsTable(pkg);
		XSSFReader xssfReader = new XSSFReader(pkg);
		this.stylesTable = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			sheetName = iter.getSheetName();
			this.rowReader.getValuesMap().put(sheetName, new ArrayList<List<String>>());
			InputSource sheetSource = new InputSource(stream);
			SAXParserFactory saxFactory = SAXParserFactory.newInstance();
			SAXParser saxParser = saxFactory.newSAXParser();
			XMLReader sheetParser = saxParser.getXMLReader();
			sheetParser.setContentHandler(this);
			sheetParser.parse(sheetSource);
			stream.close();
		}
	}

	public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

		if ("inlineStr".equals(name) || "v".equals(name)) {
			vIsOpen = true;
			// Clear contents cache
			value.setLength(0);
		}
		// c => cell
		else if ("c".equals(name)) {
			// Get the cell reference
			String r = attributes.getValue("r");
			int firstDigit = -1;
			for (int c = 0; c < r.length(); ++c) {
				if (Character.isDigit(r.charAt(c))) {
					firstDigit = c;
					break;
				}
			}
			thisColumn = nameToColumn(r.substring(0, firstDigit));

			// Set up defaults.
			this.nextDataType = xssfDataType.NUMBER;
			this.formatIndex = -1;
			this.formatString = null;
			String cellType = attributes.getValue("t");
			String cellStyleStr = attributes.getValue("s");
			if ("b".equals(cellType))
				nextDataType = xssfDataType.BOOL;
			else if ("e".equals(cellType))
				nextDataType = xssfDataType.ERROR;
			else if ("inlineStr".equals(cellType))
				nextDataType = xssfDataType.INLINESTR;
			else if ("s".equals(cellType))
				nextDataType = xssfDataType.SSTINDEX;
			else if ("str".equals(cellType))
				nextDataType = xssfDataType.FORMULA;
			else if (cellStyleStr != null) {
				// It's a number, but almost certainly one
				// with a special style or format
				int styleIndex = Integer.parseInt(cellStyleStr);
				XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
				this.formatIndex = style.getDataFormat();
				this.formatString = style.getDataFormatString();
				// LogHelper.info(formatIndex+":"+formatString);
				if (this.formatString == null)
					this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
			}
		}

	}

	public void endElement(String uri, String localName, String name) throws SAXException {
		String thisStr = null;

		// v => contents of a cell
		if ("v".equals(name)) {
			// Process the value contents as required.
			// Do now, as characters() may be called more than once
			switch (nextDataType) {

			case BOOL:
				char first = value.charAt(0);
				thisStr = first == '0' ? "FALSE" : "TRUE";
				break;

			case ERROR:
				thisStr = "\"ERROR:" + value.toString() + '"';
				break;

			case FORMULA:
				// A formula could result in a string value,
				// so always add double-quote characters.
				thisStr = value.toString();
				break;
			case INLINESTR:
				XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
				thisStr = rtsi.toString();
				break;

			case SSTINDEX:
				String sstIndex = value.toString();
				try {
					int idx = Integer.parseInt(sstIndex);
					// @SuppressWarnings("deprecation")
					// XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getItemAt(idx));
					thisStr = sharedStringsTable.getItemAt(idx).toString();
				} catch (NumberFormatException ex) {
				}
				break;

			case NUMBER:
				String n = value.toString();
				// 判断如果是日期 yyyy
				if (StringHelper.hasValue(n)) {
					if (this.formatString.indexOf("yyyy") > -1) {
						// 日期格式
						thisStr = LocalDateHelper.format(LocalDateHelper.toLocalDateTime(DateUtil.getJavaDate(Double.parseDouble(n))));
					} else
						thisStr = n;
				} else
					thisStr = n;
				// LogHelper.info(n + ":" + thisStr);
				break;

			default:
				thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
				break;
			}
			if (lastColumnNumber == -1) {
				lastColumnNumber = 0;
			}
			for (int i = lastColumnNumber + 1; i < thisColumn; ++i) {
				rowlist.add(null);
			}

			// Update column
			if (thisColumn > -1) {
				lastColumnNumber = thisColumn;
			}
			rowlist.add(StringUtils.hasText(thisStr) ? thisStr : null);

		} else if ("row".equals(name)) {
			this.rowReader.getRows(curRow, sheetName, this.rowlist);
			curRow++;
			this.rowlist = new ArrayList<String>();
		}

	}

	@Override
	public void characters(char[] ch, int start, int length) throws SAXException {
		if (vIsOpen)
			value.append(ch, start, length);
	}

	private int nameToColumn(String name) {
		int column = -1;
		for (int i = 0; i < name.length(); ++i) {
			int c = name.charAt(i);
			column = (column + 1) * 26 + c - 'A';
		}
		return column;
	}
}