package com.appdata.appload.file;

import java.io.PrintStream;
import java.util.ArrayList;

import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

/**
 * The Class MyXSSFSheetHandler.
 */
public final class MyXSSFSheetHandler extends DefaultHandler {

	/** Table with styles. */
	private StylesTable stylesTable;

	/** Table with unique strings. */
	private ReadonlySharedStringsTable sharedStringsTable;

	/** Destination for data. */
	private final PrintStream output;

	/** Number of columns to read starting with leftmost. */
	private final int minColumnCount;

	// Set when V start element is seen
	/** The v is open. */
	private boolean vIsOpen;

	private boolean firstRecord = false;

	// Set when cell start element is seen;
	// used when cell close element is seen.
	/** The next data type. */
	private int nextDataType;

	// Used to format numeric cell values.
	/** The format index. */
	private short formatIndex;

	/** The format string. */
	private String formatString;

	/** The formatter. */
	private final DataFormatter formatter;

	/** The this column. */
	private int thisColumn = -1;

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

	// Gathers characters as they are seen.
	/** The value. */
	private StringBuffer value;

	/** The data. */
	private ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();

	/** The row. */
	private ArrayList<String> row = new ArrayList<String>();

	/**
	 * Gets the data.
	 *
	 * @return the data
	 */
	public ArrayList<ArrayList<String>> getData() {
		return data;
	}

	/**
	 * Sets the data.
	 *
	 * @param data
	 *            the new data
	 */
	public void setData(ArrayList<ArrayList<String>> data) {
		this.data = data;
	}

	/**
	 * Accepts objects needed while parsing.
	 *
	 * @param styles
	 *            Table of styles
	 * @param strings
	 *            Table of shared strings
	 * @param cols
	 *            Minimum number of columns to show
	 * @param target
	 *            Sink for output
	 */
	public MyXSSFSheetHandler(StylesTable styles,
			ReadonlySharedStringsTable strings, int cols, PrintStream target) {
		this.stylesTable = styles;
		this.sharedStringsTable = strings;
		this.minColumnCount = cols;
		this.output = target;
		this.value = new StringBuffer();
		this.nextDataType = XSSFDataType.NUMBER;
		this.formatter = new DataFormatter();
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String,
	 * java.lang.String, java.lang.String, org.xml.sax.Attributes)
	 */
	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();
				if (this.formatString == null)
					this.formatString = BuiltinFormats
							.getBuiltinFormat(this.formatIndex);
			}
		}

	}

	/*
	 * (non-Javadoc)
	 *
	 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	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 XSSFDataType.BOOL:
				char first = value.charAt(0);
				thisStr = first == '0' ? "FALSE" : "TRUE";
				break;

			case XSSFDataType.ERROR:
				thisStr = value.toString();
				break;

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

			case XSSFDataType.INLINESTR:
				XSSFRichTextString rtsi = new XSSFRichTextString(
						value.toString());
				thisStr = rtsi.toString();
				break;

			case XSSFDataType.SSTINDEX:
				String sstIndex = value.toString();
				try {
					int idx = Integer.parseInt(sstIndex);
					XSSFRichTextString rtss = new XSSFRichTextString(
							sharedStringsTable.getEntryAt(idx));
					thisStr = rtss.toString();
				} catch (NumberFormatException ex) {
					output.println("Failed to parse SST index '" + sstIndex
							+ "': " + ex.toString());
				}
				break;

			case XSSFDataType.NUMBER:
				String n = value.toString();
				if (this.formatString != null)
					thisStr = formatter.formatRawCellContents(
							Double.parseDouble(n), this.formatIndex,
							this.formatString);
				else
					thisStr = n;
				break;

			default:
				thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
				break;
			}

			// Output after we've seen the string contents
			// Emit commas for any fields that were missing on this row
			if (lastColumnNumber == -1) {
				lastColumnNumber = 0;
			}

			if(!firstRecord){
			    for (int i = 1 ; i <= thisColumn; ++i) {
	                row.add("");
	            }
			    firstRecord = true;
			}else{
			    for (int i = lastColumnNumber + 1; i < thisColumn; ++i) {
	                row.add("");
	            }
			}

			// Might be the empty string.
			row.add(thisStr);

			// Update column
			if (thisColumn > -1)
				lastColumnNumber = thisColumn;

		} else if ("row".equals(name)) {

			// Print out any missing commas if needed
			if (this.minColumnCount > 0) {
				// Columns are 0 based
				if (lastColumnNumber == -1) {
					lastColumnNumber = 0;
				}
				for (int i = lastColumnNumber + 1; i < (this.minColumnCount); i++) {
					row.add("");
				}
			}

			// We're onto a new row
			lastColumnNumber = -1;
			thisColumn = -1;

			data.add(row);
			// output.println((1 + thisRow++) + " : " + row);
			row = new ArrayList<String>();
			firstRecord = false;
		}

	}

	/**
	 * Captures characters only if a suitable element is open. Originally was
	 * just "v"; extended for inlineStr also.
	 *
	 * @param ch
	 *            the ch
	 * @param start
	 *            the start
	 * @param length
	 *            the length
	 * @throws SAXException
	 *             the sAX exception
	 */
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		if (vIsOpen)
			value.append(ch, start, length);
	}

	/**
	 * Converts an Excel column name like "C" to a zero-based index.
	 *
	 * @param name
	 *            the name
	 * @return Index corresponding to the specified name
	 */
	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;
	}

}