public final class FacesUtility {

	/**
	 * hide constructor.
	 */
	private FacesUtility() {
		// not called
	}

	/**
	 * return faces context resource paths.
	 * 
	 * @param path
	 *            path.
	 * @return a set of path.
	 */
	public static Set<String> getResourcePaths(final String path) {
		return getResourcePaths(getContext(), path);
	}

	/**
	 * return faces context resource path.
	 * 
	 * @param context
	 *            face context.
	 * @param path
	 *            path.
	 * @return a set of path.
	 */
	public static Set<String> getResourcePaths(final FacesContext context,
			final String path) {
		return context.getExternalContext().getResourcePaths(path);
	}

	/**
	 * get resource file as stream.
	 * 
	 * @param path
	 *            resource file path.
	 * @return stream.
	 */
	public static InputStream getResourceAsStream(final String path) {
		return getResourceAsStream(getContext(), path);
	}

	/**
	 * get resource file as stream.
	 * 
	 * @param context
	 *            faces context.
	 * @param path
	 *            resource file path.
	 * @return stream.
	 */
	public static InputStream getResourceAsStream(
			final FacesContext context, final String path) {
		return context.getExternalContext().getResourceAsStream(path);
	}

	/**
	 * get metadata attributes.
	 * 
	 * @return map of metadata attributes.
	 */
	public static Map<String, Object> getMetadataAttributes() {
		return getMetadataAttributes(getContext());
	}

	/**
	 * get metadata attributes.
	 * 
	 * @param context
	 *            faces context.
	 * @return map of metadata attributes.
	 */
	public static Map<String, Object> getMetadataAttributes(
			final FacesContext context) {
		return context.getViewRoot().getAttributes();
	}

	/**
	 * evaluate expression.
	 * 
	 * @param <T>
	 *            This is the type parameter.
	 * @param expression
	 *            expression.
	 * @return evaluated object.
	 */
	public static <T> T evaluateExpressionGet(final String expression) {
		return evaluateExpressionGet(getContext(), expression);
	}

	/**
	 * evaluate expression.
	 * 
	 * @param <T>
	 *            This is the type parameter
	 * @param context
	 *            faces context.
	 * @param expression
	 *            expression.
	 * @return evaluated object.
	 */
	@SuppressWarnings("unchecked")
	public static <T> T evaluateExpressionGet(final FacesContext context,
			final String expression) {
		if (expression == null) {
			return null;
		}

		return (T) context.getApplication().evaluateExpressionGet(context,
				expression, Object.class);
	}

	/**
	 * return faces context.
	 * 
	 * @return faces context.
	 */
	public static FacesContext getContext() {
		return FacesContext.getCurrentInstance();
	}

	/**
	 * remove prefix path of the full path.
	 * 
	 * @param prefix
	 *            prefix string.
	 * @param resource
	 *            normal resource.
	 * @return removed path.
	 */
	public static String removePrefixPath(final String prefix,
			final String resource) {
		String normalizedResource = resource;
		if (normalizedResource.startsWith(prefix)) {
			normalizedResource = normalizedResource
					.substring(prefix.length() - 1);
		}

		return normalizedResource;
	}

	/**
	 * evaluate input type.
	 * 
	 * @param input
	 *            input string.
	 * @param type
	 *            input type.
	 * @return true if input meet type, otherwise false.
	 */
	public static boolean evalInputType(final String input,
			final String type) {

		Scanner scanner = new Scanner(input);
		boolean ireturn = false;
		if ("Integer".equalsIgnoreCase(type)) {
			ireturn = scanner.hasNextInt();
		} else if ("Double".equalsIgnoreCase(type)) {
			ireturn = scanner.hasNextDouble();
		} else if ("Boolean".equalsIgnoreCase(type)) {
			ireturn = scanner.hasNextBoolean();
		} else if ("Byte".equalsIgnoreCase(type)) {
			ireturn = scanner.hasNextByte();
		} else if (type.toLowerCase().startsWith("text")) {
			ireturn = true;
		}
		scanner.close();
		return ireturn;
	}

	/**
	 * Find bean in context.
	 * 
	 * @param <T>
	 *            This is the type parameter.
	 * @param beanName
	 *            bean name.
	 * @return bean object.
	 */
	@SuppressWarnings("unchecked")
	public static <T> T findBean(final String beanName) {
		FacesContext context = FacesContext.getCurrentInstance();
		return (T) context.getApplication().evaluateExpressionGet(context,
				TieConstants.EL_START + beanName + TieConstants.EL_END,
				Object.class);
	}

	/**
	 * evaluate expression.
	 * 
	 * @param <T>
	 *            This is the type parameter.
	 * @param expression
	 *            expression.
	 * @param expected
	 *            exprected type.
	 * @return evaluated object.
	 */
	public static <T> T evaluateExpression(final String expression,
			final Class<? extends T> expected) {
		return evaluateExpression(FacesContext.getCurrentInstance(),
				expression, expected);
	}

	/**
	 * evaluate expression.
	 * 
	 * @param <T>
	 *            This is the type parameter.
	 * @param context
	 *            faces context.
	 * @param expression
	 *            expression.
	 * @param expected
	 *            expected type.
	 * @return evaluated object.
	 */

	public static <T> T evaluateExpression(final FacesContext context,
			final String expression, final Class<? extends T> expected) {
		return context.getApplication().evaluateExpressionGet(context,
				expression, expected);
	}

	/**
	 * join string.
	 * 
	 * @param aArr
	 *            attribute list.
	 * @param sSep
	 *            seperator string.
	 * @return joined string.
	 */
	public static String strJoin(final short[] aArr, final String sSep) {
		StringBuilder sbStr = new StringBuilder();
		for (int i = 0, il = aArr.length; i < il; i++) {
			if (i > 0) {
				sbStr.append(sSep);
			}
			sbStr.append(aArr[i]);
		}
		return sbStr.toString();
	}
	/**
	 * join string
	 * @param aArr attr list.
	 * @param sSep seperator string.
	 * @return joined string.
	 */
	public static String strJoin(final String[] aArr, final String sSep) {
		StringBuilder sbStr = new StringBuilder();
		for (int i = 0, il = aArr.length; i < il; i++) {
			if (i > 0) {
				sbStr.append(sSep);
			}
			sbStr.append(aArr[i]);
		}
		return sbStr.toString();
	}

	/**
	 * round number according to decimal places.
	 * 
	 * @param value
	 *            double value.
	 * @param places
	 *            decimal places.
	 * @return rounded number.
	 */
	public static double round(final double value, final int places) {
		if (places < 0) {
			throw new IllegalArgumentException();
		}

		BigDecimal bd = BigDecimal.valueOf(value);
		bd = bd.setScale(places, RoundingMode.HALF_UP);
		return bd.doubleValue();
	}
}
 public final class TieConstants {

	/** The Constant COMPONENT_ID. */
	public static final String COMPONENT_ID = "websheettable";

	/** The Constant CONFIGURATION_SHEET. */
	public static final String CONFIGURATION_SHEET = "Configuration";

	/** The Constant ATTRS_WEBSHEETBEAN. */
	public static final String ATTRS_WEBSHEETBEAN = "webSheetBean";

	/** The Constant EXCEL_2003_TYPE. */
	public static final String EXCEL_2003_TYPE = "xls";

	/** The Constant EXCEL_2007_TYPE. */
	public static final String EXCEL_2007_TYPE = "xlsx";

	/** The Constant CONFIGURATION_SCHEMA_VERSION. */
	public static final String CONFIGURATION_SCHEMA_VERSION = "Version";

	/** The Constant CONFIGURATION_SCHEMA_TAB_NAME. */
	public static final String CONFIGURATION_SCHEMA_TAB_NAME = "Tab Name";

	/** The Constant CONFIGURATION_SCHEMA_SHEET_NAME. */
	public static final String CONFIGURATION_SCHEMA_SHEET_NAME = "Sheet Name";

	/** The Constant CONFIGURATION_SCHEMA_FORM_HEADER_RANGE. */
	public static final String CONFIGURATION_SCHEMA_FORM_HEADER_RANGE = "Form Header Range";

	/** The Constant CONFIGURATION_SCHEMA_FORM_BODY_RANGE. */
	public static final String CONFIGURATION_SCHEMA_FORM_BODY_RANGE = "Form Body Range";

	/** The Constant CONFIGURATION_SCHEMA_FORM_FOOTER_RANGE. */
	public static final String CONFIGURATION_SCHEMA_FORM_FOOTER_RANGE = "Form Footer Range";

	/** The Constant CONFIGURATION_SCHEMA_FORM_BODY_TYPE. */
	public static final String CONFIGURATION_SCHEMA_FORM_BODY_TYPE = "Form Body Type";

	/** The Constant CONFIGURATION_SCHEMA_ALLOW_ADD_ROW. */
	public static final String CONFIGURATION_SCHEMA_ALLOW_ADD_ROW = "Allow ADD/DELETE ROW";

	/** The Constant CONFIGURATION_SCHEMA_INIT_ROWS. */
	public static final String CONFIGURATION_SCHEMA_INIT_ROWS = "Initial Rows";

	/** The Constant CONFIGURATION_SCHEMA_FORM_PAGE_TYPE. */
	public static final String CONFIGURATION_SCHEMA_FORM_PAGE_TYPE = "Form Page Type";

	/** The Constant CONFIGURATION_SCHEMA_FORM_WIDTH. */
	public static final String CONFIGURATION_SCHEMA_FORM_WIDTH = "Form Width";

	/** The Constant CONFIGURATION_SCHEMA_MAX_ROWS_PER_PAGE. */
	public static final String CONFIGURATION_SCHEMA_MAX_ROWS_PER_PAGE = "Max Rows Per Page";

	/** The Constant CONFIGURATION_SCHEMA_SAVED_ROWS_BEFORE. */
	public static final String CONFIGURATION_SCHEMA_SAVED_ROWS_BEFORE = "Saved Rows Before Repeat";

	/** The Constant CONFIGURATION_SCHEMA_SAVED_ROWS_AFTER. */
	public static final String CONFIGURATION_SCHEMA_SAVED_ROWS_AFTER = "Saved Rows After Rpeat";

	/** The Constant CONFIGURATION_SCHEMA_TARGET_COLUMN_CELL. */
	public static final String CONFIGURATION_SCHEMA_TARGET_COLUMN_CELL = "Target Column/Cell";

	/** The Constant CONFIGURATION_SCHEMA_ATTRIBUTE_TYPE. */
	public static final String CONFIGURATION_SCHEMA_ATTRIBUTE_TYPE = "Attributes Type";

	/** The Constant CONFIGURATION_SCHEMA_ATTRIBUTE_VALUE. */
	public static final String CONFIGURATION_SCHEMA_ATTRIBUTE_VALUE = "Attributes Value";

	/** The Constant CONFIGURATION_SCHEMA_VALIDATION_ERROR_MSG. */
	public static final String CONFIGURATION_SCHEMA_VALIDATION_ERROR_MSG = "Validation Error Messages";

	/** The Constant CONFIGURATION_ATTR_CONTROL. */
	public static final String CONFIGURATION_ATTR_CONTROL = "Control";

	/** The Constant FORM_TYPE_FREE. */
	public static final String FORM_TYPE_FREE = "Free";

	/** The Constant FORM_TYPE_REPEAT. */
	public static final String FORM_TYPE_REPEAT = "Repeat";

	/** The Constant CELL_INPUT_TYPE_DOUBLE. */
	public static final String CELL_INPUT_TYPE_DOUBLE = "double";

	/** The Constant CELL_INPUT_TYPE_INTEGER. */
	public static final String CELL_INPUT_TYPE_INTEGER = "integer";

	/** The Constant CELL_INPUT_TYPE_PERCENTAGE. */
	public static final String CELL_INPUT_TYPE_PERCENTAGE = "percentage";

	/** The Constant CELL_INPUT_TYPE_DATE. */
	public static final String CELL_INPUT_TYPE_DATE = "date";

	/** The Constant CELL_INPUT_TYPE_TEXT. */
	public static final String CELL_INPUT_TYPE_TEXT = "text";

	/** The Constant CELL_INPUT_TYPE_TEXTAREA. */
	public static final String CELL_INPUT_TYPE_TEXTAREA = "textarea";

	/** The Constant COPY_SHEET_PREFIX. */
	public static final String COPY_SHEET_PREFIX = "cptieTemp_";

	/** The Constant EXCEL_SHEET_NAME_LIMIT. */
	public static final short EXCEL_SHEET_NAME_LIMIT = 31;
	/** method prefix. */
	/** command's prefix. */
	public static final String COMMAND_PREFIX = "tie:";
	/** form command. */
	public static final String COMMAND_FORM = "form";
	/** each command. */
	public static final String COMMAND_EACH = "each";
	/** true string. */
	public static final String TRUE_STRING = "true";
	/** false string. */
	public static final String FALSE_STRING = "false";
	/** attribute prefix. */
	public static final String ATTR_PREFIX = "(";
	/** attribute suffix. */
	public static final String ATTR_SUFFIX = ")";
	/** attribute's regex. */
	public static final String ATTR_REGEX = "\\s*\\w+\\s*=\\s*([\"|'])(?:(?!\\1).)*\\1";
	/** attribute's regex pattern. */
	public static final Pattern ATTR_REGEX_PATTERN = Pattern
			.compile(ATTR_REGEX);

	/** The Constant CELL_DATA_ROW. */
	public static final String CELL_DATA_ROW = "data-row";

	/** The Constant CELL_DATA_COLUMN. */
	public static final String CELL_DATA_COLUMN = "data-column";

	/** The Constant CELL_DATA_SYMBOL. */
	public static final String CELL_DATA_SYMBOL = "data-symbol";

	/** The Constant TIE_WEB_SHEET_MAX_ROWS. */
	public static final int TIE_WEB_SHEET_MAX_ROWS = 99999;

	/** The Constant EL_START. */
	public static final String EL_START = "#{";

	/** The Constant EL_START_BRACKET. */
	public static final char EL_START_BRACKET = '{';

	/** The Constant EL_END. */
	public static final char EL_END = '}';

	/** The Constant TAB_TYPE_NONE. */
	public static final String TAB_TYPE_NONE = "none";

	/** The Constant TAB_STYLE_VISIBLE. */
	public static final String TAB_STYLE_VISIBLE = "height: 530px;";

	/** The Constant TAB_STYLE_INVISIBLE. */
	public static final String TAB_STYLE_INVISIBLE = "height: 30px;";

	/** The Constant defaultMaxRowsPerPage. */
	public static final int DEFAULT_MAX_ROWS_PER_PAGE = 80;

	/** The Constant defaultTableWidthStyle. */
	public static final String DEFAULT_TABLE_WIDTH_STYLE = "width: 100%;";

	/** The Constant defaultLineNumberColumnWidth. */
	public static final int DEFAULT_LINENUMBER_COLUMN_WIDTH = 26;

	/** The Constant excel letter total numbers. */
	public static final int EXCEL_LETTER_NUMBERS = 26;

	/** The Constant defaultAddRowColumnWidth. */
	public static final int DEFAULT_ADDROW_COLUMN_WIDTH = 64;

	/** default basic stroke width. */
	public static final float DEFAULT_BASIC_STROKE = 0.1f;

	/** The Constant defaultLegentItemShapeWidth. */
	public static final int DEFAULT_LEGENT_ITEM_SHAPE_WIDTH = 8;

	/** The Constant defaultLegentItemShapeHeight. */
	public static final int DEFAULT_LEGENT_ITEM_SHAPE_HEIGHT = 8;

	/** The Constant defaultBarStyleItemMargin. */
	public static final float DEFAULT_BAR_STYLE_ITEM_MARGIN = 0.02f;

	/** The Constant defaultBarStyleForegroundAlpha. */
	public static final int DEFAULT_BARSTYLE_FOREGROUND_ALPHA = 1;

	/** The Constant eachCommandInvalidMsg. */
	public static final String EACH_COMMAND_INVALID_MSG = " is not a list collection, cannot support add/delete function.";

	/** The Constant defaultCommandPartLength. */
	public static final int DEFAULT_COMMAND_PART_LENGTH = 3;

	/** The Constant USER_FORMULA_PREFIX. */
	public static final String USER_FORMULA_PREFIX = "$[";

	/** The Constant USER_FORMULA_SUFFIX. */
	public static final String USER_FORMULA_SUFFIX = "]";

	/** The Constant max columns in sheet. = AML */
	public static final int MAX_COLUMNS_IN_SHEET = 1024;

	/** The Constant hiddenOriginRowNumber. = AML */
	public static final int HIDDEN_ORIGIN_ROW_NUMBER_COLUMN = 1025;

	/** The Constant hiddenFullNameColumn. = AMM */
	public static final int HIDDEN_FULL_NAME_COLUMN = 1026;

	/** The Constant hiddenSaveObjectsColumn. = AMN */
	public static final int HIDDEN_SAVE_OBJECTS_COLUMN = 1027;

	/** The Constant cellMapKeyChart. */
	public static final String CELL_MAP_KEY_CHART = "chart";

	/** The Constant cellMapKeyPicture. */
	public static final String CELL_MAP_KEY_PICTURE = "picture";

	/** The Constant cellMapKeyFormat. */
	public static final String CELL_MAP_KEY_FORMAT = "format";

	/** The Constant cellMapKeyPercent. */
	public static final String CELL_MAP_KEY_PERCENT = "percent";

	/** The Constant cellFormatPercentageSymbol. */
	public static final String CELL_FORMAT_PERCENTAGE_SYMBOL = "%";

	/** The Constant cellFormatPercentageValue. */
	public static final int CELL_FORMAT_PERCENTAGE_VALUE = 100;
	/** The Constant cellAddrPrefix. */
	public static final String CELL_ADDR_PRE_FIX = "$";
	/** The Constant rgb max. */
	public static final short RGB_MAX = 256;
	/** select item labels. */
	public static final String SELECT_ITEM_LABELS = "itemlabels";
	/** select item values. */
	public static final String SELECT_ITEM_VALUES = "itemvalues";
	/** default select item label. */
	public static final String DEFAULT_SELECT_ITEM_LABEL = "defaultlabel";
	/** default select item value. */
	public static final String DEFAULT_SELECT_ITEM_VALUE = "defaultvalue";
	/** widget calendar. */
	public static final String WIDGET_CALENDAR = "calendar";
	/** widget attribute pattern. */
	public static final String WIDGET_ATTR_PATTERN = "pattern";
	/** component attributes locale. */
	public static final String COMPONENT_ATTR_LOCALE = "locale";
	/** method regex. */
	public static final String METHOD_REGEX = "\\$+[^{$]+\\{.*\\}";
	/** method prefix. */
	public static final String METHOD_PREFIX = "${";
	/** The expression notation end. */
	public static final String METHOD_END = "}";
	/** method widget prefix. */
	public static final String METHOD_WIDGET_PREFIX = "$widget.";
	/** method validate prefix. */
	public static final String METHOD_VALIDATE_PREFIX = "$validate";
	/** The expression notation pattern. */
	public static final Pattern EXPRESSION_NOTATION_PATTERN = Pattern
			.compile("\\$\\{[^}]*}");
	/** The DEFAULT_HEADER_ROW_HEIGHT. */
	public static final float DEFAULT_HEADER_ROW_HEIGHT = 12;
	/** The NORMAL_COMMENT_KEY_IN_MAP. */
	public static final String NORMAL_COMMENT_KEY_IN_MAP = "$$";
	/** The SAVE_COMMENT_KEY_IN_MAP. */
	public static final String SAVE_COMMENT_KEY_IN_MAP = "$save";
	/** The VALIDATION_RULE_TAG. */
	public static final String VALIDATION_RULE_TAG = "rule";
	/** The VALIDATION_ERROR_MSG_TAG. */
	public static final String VALIDATION_ERROR_MSG_TAG = "error";
	/** SPLIT_SPACE_SEPERATE_ATTRS_REGX. */
	public static final String SPLIT_SPACE_SEPERATE_ATTRS_REGX = "\" (?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)";
	/** The Constant DEFALT_MSG_INVALID_INPUT. */
	public static final String DEFALT_MSG_INVALID_INPUT = "Invalid input";

	/** The Constant EACH_COMMAND_FULL_NAME_PREFIX. */
	public static final String EACH_COMMAND_FULL_NAME_PREFIX = "E.";

	/** The Constant FORM_COMMAND_FULL_NAME_PREFIX. */
	public static final String FORM_COMMAND_FULL_NAME_PREFIX = "F.";
	/** The Constant CELL_INVALID_STYLE. */
	public static final String CELL_INVALID_STYLE = "border-color: red;";
	/** The Constant TEXT_ALIGN_LEFT. */
	public static final String TEXT_ALIGN_LEFT = "text-align: left;";
	/** The Constant TEXT_ALIGN_RIGHT. */
	public static final String TEXT_ALIGN_RIGHT = "text-align: right;";
	/** The Constant TEXT_ALIGN_CENTER. */
	public static final String TEXT_ALIGN_CENTER = "text-align: center;";

	/** The Constant VERTICAL_ALIGN_TOP. */
	public static final String VERTICAL_ALIGN_TOP = "vertical-align: top;";
	/** The Constant VERTICAL_ALIGN_BOTTOM. */
	public static final String VERTICAL_ALIGN_BOTTOM = "vertical-align: bottom;";
	/** The Constant VERTICAL_ALIGN_CENTER. */
	public static final String VERTICAL_ALIGN_CENTER = "vertical-align: middle;";

	/** The Constant submit mode. */
	public static final String SUBMITMODE = "tieWebSheetSubmitMode";
	/** The Constant unSaved state. */
	public static final String UNSAVEDSTATE = "unSaved";
	/**
	 * hide constructor.
	 */
	private TieConstants() {
		// not called
	}

}
 public class TieWebSheetBean extends TieWebSheetView
		implements Serializable {

	/** The Constant serialVersionUID. */
	private static final long serialVersionUID = 3495468356246589276L;

	/** hold instance for columns in current display sheet. */
	private List<String> columns = new ArrayList<>();
	/** hold instance for each body rows in current display sheet. */
	private List<FacesRow> bodyRows;
	/** hold instance for each header rows in current display sheet. */
	private List<List<HeaderCell>> headerRows;
	/** current workbook. */
	private SerialWorkbook serialWb;
	/** current workbook wrapper for formula parser. */
	private transient XSSFEvaluationWorkbook wbWrapper;
	/** current formula evaluator. */
	private transient FormulaEvaluator formulaEvaluator;
	/** current dataFormatter. */
	private transient DataFormatter dataFormatter;
	/** hold data object context. */
	private SerialDataContext serialDataContext;
	/** hold pictures for current display sheet. */
	private transient Map<String, Picture> picturesMap;
	/**
	 * cell attributes map.
	 */
	private CellAttributesMap cellAttributesMap = new CellAttributesMap(
			new HashMap<String, Map<String, String>>(),
			new HashMap<String, String>(),
			new HashMap<String, List<CellFormAttributes>>(),
			new HashMap<String, Map<String, String>>(),
			new HashMap<String, String>(),
			new HashMap<String, List<CellFormAttributes>>());

	/**
	 * chars data.
	 */
	private transient ChartsData chartsData;

	/** hold cached cells in current display sheet. */
	private CachedCells cachedCells;
	/**
	 * The max column counts across sheets of this workbook. e.g. sheet1 has 3
	 * columns, sheet2 has 5 columns. maxColCounts = 5;
	 */
	private int maxColCounts = 0;

	/** hold expressionEngine instance. */
	private transient ExpressionEngine expEngine = null;
	/** hold current objects. */
	private TieWebSheetBeanCurrent current;

	/** submit mode may affect validation process. e.g. validation interface. */
	private Boolean submitMode = false;
	/** create bean's this.getHelper(). */
	private transient TieWebSheetBeanHelper helper = null;

	/**
	 * cell default control.
	 */
	private Map<String, Map<String, String>> cellDefaultControl = new HashMap<>();

	/** for download file. */
	private transient StreamedContent exportFile;

	/**
	 * cells map for current display sheet.
	 */

	private CellMap cellsMap = new CellMap(this);
	

	
	/** logger. */
	private static final Logger LOG = Logger
			.getLogger(TieWebSheetBean.class.getName());

	/** constructor. Allow for extension. */
	public TieWebSheetBean() {
		LOG.fine("TieWebSheetBean Constructor");
	}

	/** initialize. */
	@PostConstruct
	public void init() {
		initialLoad();
	}


	/**
	 * get body rows.
	 * 
	 * @return body rows.
	 */
	public List<FacesRow> getBodyRows() {
		if (this.bodyRows == null) {
			this.bodyRows = new ArrayList<>();
		}
		return bodyRows;
	}

	/**
	 * set body rows.
	 * 
	 * @param pBodyRows
	 *            body rows list.
	 */
	public void setBodyRows(final List<FacesRow> pBodyRows) {
		this.bodyRows = pBodyRows;
	}

	/**
	 * Gets the header rows.
	 *
	 * @return return header row list.
	 */
	public List<List<HeaderCell>> getHeaderRows() {
		if (this.headerRows == null) {
			this.headerRows = new ArrayList<>();
		}
		return headerRows;
	}

	/**
	 * set header rows.
	 * 
	 * @param pHeaderRows
	 *            header rows list.
	 */
	public void setHeaderRows(final List<List<HeaderCell>> pHeaderRows) {
		this.headerRows = pHeaderRows;
	}

	/**
	 * set columns.
	 * 
	 * @param pColumns
	 *            column list.
	 */
	public void setColumns(final List<String> pColumns) {
		this.columns = pColumns;
	}

	/**
	 * Gets the serial wb.
	 *
	 * @return the serial_wb
	 */
	public SerialWorkbook getSerialWb() {
		if (serialWb == null) {
			serialWb = new SerialWorkbook();
		}
		return serialWb;
	}

	/**
	 * Sets the serial wb.
	 *
	 * @param pserialWb
	 *            the serial_wb to set
	 */
	public void setSerialWb(final SerialWorkbook pserialWb) {
		this.serialWb = pserialWb;
	}

	/**
	 * Gets the serial data context.
	 *
	 * @return the serialDataContext
	 */
	public SerialDataContext getSerialDataContext() {
		if (serialDataContext == null) {
			serialDataContext = new SerialDataContext();
		}
		return serialDataContext;
	}

	/**
	 * Sets the serial data context.
	 *
	 * @param pserialDataContext
	 *            the serialDataContext to set
	 */
	public void setSerialDataContext(
			final SerialDataContext pserialDataContext) {
		this.serialDataContext = pserialDataContext;
	}

	/**
	 * get workbook.
	 * 
	 * @return workbook.
	 */
	public Workbook getWb() {
		return this.getSerialWb().getWb();
	}

	/**
	 * Set up workbook. Also create evaluation wrapper.
	 * 
	 * @param pWb
	 *            workbook.
	 */

	public void setWb(final Workbook pWb) {

		this.getSerialWb().setWb(pWb);
		this.wbWrapper = XSSFEvaluationWorkbook.create((XSSFWorkbook) pWb);
	}

	/**
	 * Return evaluation wrapper if needed.
	 * 
	 * @return wbwrapper.
	 */
	public XSSFEvaluationWorkbook getWbWrapper() {
		if ((this.wbWrapper == null) && (this.getWb() != null)) {
			this.wbWrapper = XSSFEvaluationWorkbook
					.create((XSSFWorkbook) this.getWb());
		}
		return wbWrapper;
	}

	/**
	 * get formulaevaluator.
	 * 
	 * @return formulaevaluator.
	 */
	public FormulaEvaluator getFormulaEvaluator() {
		if ((this.formulaEvaluator == null) && (this.getWb() != null)) {
			this.formulaEvaluator = this.getWb().getCreationHelper()
					.createFormulaEvaluator();
		}
		return formulaEvaluator;
	}

	/**
	 * set formulaevaluator.
	 * 
	 * @param pFormulaEvaluator
	 *            formulaevaluator.
	 */
	public void setFormulaEvaluator(
			final FormulaEvaluator pFormulaEvaluator) {
		this.formulaEvaluator = pFormulaEvaluator;
	}

	/**
	 * get data formatter.
	 * 
	 * @return dataformatter.
	 */
	public DataFormatter getDataFormatter() {
		if (this.dataFormatter == null) {
			this.dataFormatter = new DataFormatter(this.getDefaultLocale());
		}
		return dataFormatter;
	}

	/**
	 * set dataformatter.
	 * 
	 * @param pDataFormatter
	 *            dataformatter.
	 */
	public void setDataFormatter(final DataFormatter pDataFormatter) {
		this.dataFormatter = pDataFormatter;
	}

	/**
	 * get columns.
	 * 
	 * @return list of columns.
	 */
	public List<String> getColumns() {
		return columns;
	}

	/**
	 * Gets the current.
	 *
	 * @return the current
	 */
	public TieWebSheetBeanCurrent getCurrent() {
		if (current == null) {
			current = new TieWebSheetBeanCurrent();
		}
		return current;
	}

	/**
	 * get submit mode.
	 * 
	 * @return true if it's in submit mode.
	 */
	public Boolean getSubmitMode() {
		return this.submitMode;
	}

	/**
	 * set submit mode.
	 * 
	 * @param pSubmitMode
	 *            submit mode flag.
	 */
	public void setSubmitMde(final Boolean pSubmitMode) {
		this.submitMode = pSubmitMode;
		this.getHelper().getValidationHandler()
		.setSubmitModeInView(this.submitMode);		
	}

	/**
	 * get bean helper.
	 * 
	 * @return helper.
	 */

	public TieWebSheetBeanHelper getHelper() {
		if (this.helper == null) {
			this.helper = new TieWebSheetBeanHelper(this);
		}
		return this.helper;
	}

	/**
	 * get cell this.getHelper().
	 * 
	 * @return cell this.getHelper().
	 */
	public CellHelper getCellHelper() {
		return this.getHelper().getCellHelper();
	}

	/**
	 * get expression engine.
	 * 
	 * @return exp engine.
	 */
	public ExpressionEngine getExpEngine() {
		if (this.expEngine == null) {
			this.expEngine = new ExpressionEngine();
		}
		return expEngine;
	}

	/**
	 * get websheet loader.
	 * 
	 * @return websheetloader.
	 */
	public WebSheetLoader getWebSheetLoader() {
		return this.getHelper().getWebSheetLoader();
	}

	/**
	 * get validationhandler.
	 * 
	 * @return validation handler.
	 */
	public ValidationHandler getValidationHandler() {
		return this.getHelper().getValidationHandler();
	}

	/**
	 * get picthis.getHelper().
	 * 
	 * @return picHelper.
	 */
	public PicturesHelper getPicHelper() {
		return this.getHelper().getPicHelper();
	}

	/**
	 * get chartthis.getHelper().
	 * 
	 * @return chartthis.getHelper().
	 */
	public ChartHelper getChartHelper() {
		return this.getHelper().getChartHelper();
	}

	/**
	 * get pictures map.
	 * 
	 * @return pictures map.
	 */
	public Map<String, Picture> getPicturesMap() {
		if (this.picturesMap == null) {
			this.picturesMap = new HashMap<>();
		}
		return picturesMap;
	}

	/**
	 * set pictures map.
	 * 
	 * @param pPicturesMap
	 *            pictures map.
	 */
	public void setPicturesMap(final Map<String, Picture> pPicturesMap) {
		this.picturesMap = pPicturesMap;
	}

	/**
	 * charts data.
	 * 
	 * @return charts data
	 */
	public ChartsData getCharsData() {
		if (this.chartsData == null) {
			this.chartsData = new ChartsData();
		}
		return this.chartsData;
	}

	/**
	 * Gets the cached cells.
	 *
	 * @return cached cells.
	 */
	public CachedCells getCachedCells() {
		if (cachedCells == null) {
			cachedCells = new CachedCells(this);
		}
		return cachedCells;
	}

	/**
	 * set cached cells.
	 * 
	 * @param pCachedCells
	 *            cached cells.
	 */
	public void setCachedCells(final CachedCells pCachedCells) {
		this.cachedCells = pCachedCells;
	}

	/**
	 * Gets the max col counts.
	 *
	 * @return max column counts.
	 */
	public int getMaxColCounts() {
		if (this.maxColCounts == 0) {
			reCalcMaxColCounts();
		}
		return maxColCounts;
	}


	/**
	 * recalculate max coulumn count across sheets in the workbook.
	 */
	public void reCalcMaxColCounts() {
		if ((this.getSheetConfigMap() == null)
				|| (this.getSheetConfigMap().isEmpty())) {
			this.maxColCounts = 0;
			return;
		}
		int maxColumns = 0;
		for (SheetConfiguration sheetConfig : this.getSheetConfigMap()
				.values()) {
			int counts = sheetConfig.getHeaderCellRange().getRightCol()
					- sheetConfig.getHeaderCellRange().getLeftCol() + 1;
			if (maxColumns < counts) {
				maxColumns = counts;
			}
		}
		this.maxColCounts = maxColumns;
	}

	/**
	 * load web sheet from inputStream file.
	 * 
	 * @param inputStream
	 *            input stream file.
	 * @return 1 (success) -1 (failed)
	 */
	public int loadWebSheet(final InputStream inputStream) {
		return loadWebSheet(inputStream, null);
	}

	/**
	 * load web sheet from inputStream file with data object.
	 * 
	 * @param inputStream
	 *            input stream file.
	 * @param pDataContext
	 *            data object.
	 * @return 1 (success) -1 (failed)
	 */
	public int loadWebSheet(final InputStream inputStream,
			final Map<String, Object> pDataContext) {
		return this.getHelper().getWebSheetLoader()
				.loadWorkbook(inputStream, pDataContext);
	}

	/**
	 * load web sheet from giving workbook.
	 * 
	 * @param pWb
	 *            workbook.
	 * @return 1 (success) -1 (failed)
	 */

	public int loadWebSheet(final Workbook pWb) {
		return loadWebSheet(pWb, null);
	}

	/**
	 * load web sheet from giving workbook with data object.
	 * 
	 * @param pWb
	 *            workbook.
	 * @param pDataContext
	 *            data object.
	 * @return 1 (success) -1 (failed)
	 */

	public int loadWebSheet(final Workbook pWb,
			final Map<String, Object> pDataContext) {
		return this.getHelper().getWebSheetLoader().loadWorkbook(pWb,
				pDataContext);
	}

	/**
	 * Triggered when user switch the tab. This will load different tab(sheet)
	 * as the current sheet.
	 * 
	 * @param event
	 *            tabchange event.
	 */
	public void onTabChange(final TabChangeEvent event) {
		String tabName = event.getTab().getTitle();
		loadWorkSheetByTabName(tabName);
	}

	/**
	 * load worksheet by tab name.
	 * 
	 * @param tabName
	 *            tab name.
	 * @return 1 success. -1 failed.
	 */

	public int loadWorkSheetByTabName(final String tabName) {

		try {
			int sheetId = this.getHelper().getWebSheetLoader()
					.findTabIndexWithName(tabName);
			if ((getSheetConfigMap() != null)
					&& (sheetId < getSheetConfigMap().size())) {
				this.getHelper().getWebSheetLoader().loadWorkSheet(tabName);
				setActiveTabIndex(sheetId);
			}
			return 1;
		} catch (Exception ex) {
			LOG.log(Level.SEVERE, "loadWorkSheetByTabName failed. error = "
					+ ex.getMessage(), ex);

		}
		return -1;
	}

	/**
	 * get export file.
	 * 
	 * @return exportfile.
	 */

	public StreamedContent getExportFile() {
		return exportFile;
	}

	/** download current workbook. */
	public void doExport() {
		try {

			String fileName = this.getExportFileName();
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			this.getWb().write(out);
			InputStream stream = new BufferedInputStream(
					new ByteArrayInputStream(out.toByteArray()));
			exportFile = new DefaultStreamedContent(stream,
					"application/force-download", fileName);

		} catch (Exception e) {
			LOG.log(Level.SEVERE,
					"Error in export file : " + e.getLocalizedMessage(), e);
		}
		return;
	}

	/**
	 * Save the current workbooks.
	 */
	public void doSave() {
		
		
		this.setSubmitMde(false);
		if (!this.getHelper().getValidationHandler().preValidation()) {
			LOG.fine("Validation failded before saving");
			return;
		}
		processSave();
		this.getHelper().getWebSheetLoader().setUnsavedStatus(
				RequestContext.getCurrentInstance(), false);
	}

	/**
	 * save process. User need override this method to save into db.
	 * 
	 */
	public void processSave() {
		return;
	}

	
	/**
	 * Submit the current workbooks.
	 */
	public void doSubmit() {
				
		this.setSubmitMde(true);
		// validation may behavior differently depend on the submit mode.
		// e.g. when submit mode = false, empty fields or value not changed cells
		// don't need to pass the validation rule. This allow partial save the form. 
		// when submit mode = true, all cells need to pass the validation.
		if (!this.getHelper().getValidationHandler().preValidation()) {
			LOG.fine("Validation failed before saving");
			return;
		}
		processSubmit();
		this.getHelper().getWebSheetLoader().setUnsavedStatus(
				RequestContext.getCurrentInstance(), false);
		this.setSubmitMde(false);
	}

	/**
	 * submit process. User need override this method to submit the form.
	 * 
	 */
	public void processSubmit() {
		return;
	}
	
	
	/**
	 * Triggered when value in cells changed. e.g. user edit cell.
	 * 
	 * @param event
	 *            ajax event.
	 */
	public void valueChangeEvent(final AjaxBehaviorEvent event) {
		this.getHelper().getValidationHandler().valueChangeEvent(event);
	}

	/**
	 * check whether current workbook contain multiple pages.
	 * 
	 * @return true (multiple pages) false ( single page).
	 */
	public boolean isMultiplePage() {
		return ((bodyRows != null)
				&& (bodyRows.size() > this.getMaxRowsPerPage()));
	}

	/**
	 * called before bean gone.
	 */
	@PreDestroy
	public void finish() {
		if (FacesContext.getCurrentInstance() == null) {
			LOG.info("session has gone");
		}

	}

	/**
	 * get sheet config map.
	 * 
	 * @return sheet config map.
	 */
	public Map<String, SheetConfiguration> getSheetConfigMap() {
		return this.getSerialWb().getSheetConfigMap();
	}

	/**
	 * set sheet config map.
	 * 
	 * @param pSheetConfigMap
	 *            sheet config map.
	 */
	public void setSheetConfigMap(
			final Map<String, SheetConfiguration> pSheetConfigMap) {
		this.getSerialWb().setSheetConfigMap(pSheetConfigMap);
	}

	/**
	 * Gets the cells map.
	 *
	 * @return the cells map
	 */
	@SuppressWarnings("rawtypes")
	public Map getCellsMap() {
		return cellsMap;
	}

	/**
	 * initial load process. designed for extension.
	 */
	public void initialLoad() {
		// designed for extension.
	}

	/**
	 * Triggered when user click add row button.
	 * 
	 * @param rowIndex
	 *            row index.
	 */
	public void addRepeatRow(final int rowIndex) {
		this.getHelper().getWebSheetLoader().addRepeatRow(rowIndex);
	}

	/**
	 * Triggered when user click delete row button.
	 * 
	 * @param rowIndex
	 *            row index.
	 */
	public void deleteRepeatRow(final int rowIndex) {
		this.getHelper().getWebSheetLoader().deleteRepeatRow(rowIndex);
	}

	/**
	 * get cell attributes map.
	 * 
	 * @return cell attributes map.
	 */
	public CellAttributesMap getCellAttributesMap() {
		return cellAttributesMap;
	}

	/**
	 * get cell default control.
	 * 
	 * @return cell default control.
	 */
	public Map<String, Map<String, String>> getCellDefaultControl() {
		return cellDefaultControl;
	}

	/**
	 * populate component.
	 * 
	 * @param event
	 *            component system event.
	 */
	public void populateComponent(final ComponentSystemEvent event) {
		UIComponent component = event.getComponent();
		int[] rowcol = CellUtility
				.getRowColFromComponentAttributes(component);
		int row = rowcol[0];
		int col = rowcol[1];
		FacesCell fcell = CellUtility.getFacesCellFromBodyRow(row, col,
				this.getBodyRows(), this.getCurrent().getCurrentTopRow(),
				this.getCurrent().getCurrentLeftColumn());
		CellControlsUtility.populateAttributes(component, fcell,
				this.getCellDefaultControl());
	}

	/**
	 * Gets the current sheet config.
	 *
	 * @return the currentSheetConfig
	 */
	public SheetConfiguration getCurrentSheetConfig() {
		String currentTabName = this.getCurrent().getCurrentTabName();
		if (currentTabName == null) {
			return null;
		}
		return this.getSheetConfigMap().get(currentTabName);
	}

	/**
	 * load the bean from saving.
	 * 
	 * @param in
	 *            inputstream.
	 * @throws IOException
	 *             io exception.
	 */
	private void readObject(final java.io.ObjectInputStream in)
			throws IOException {
		try {
			in.defaultReadObject();
			recover();
		} catch (EncryptedDocumentException | ClassNotFoundException e) {
			LOG.log(Level.SEVERE,
					" error in readObject of serialWorkbook : "
							+ e.getLocalizedMessage(),
					e);
		}
	}

	/**
	 * recover objects after deserilize.
	 */
	private void recover() {
		if (this.getWb() != null) {
			this.getChartHelper().loadChartsMap();
			this.getPicHelper().loadPicturesMap();
		}
	}


	/**
	 * Refresh data.
	 */
	public void refreshData() {
		this.getWebSheetLoader().refreshData();
	}


}
 public static class TabModel implements Serializable {

	/** serialid. */
	private static final long serialVersionUID = 1L;
	/** tab id. */
	private String id;
	/** tab title. */
	private String title;
	/** tab type. */
	private String type;

	/**
	 * constructor.
	 * 
	 * @param pid
	 *            id.
	 * @param ptitle
	 *            title.
	 * @param ptype
	 *            type.
	 */
	public TabModel(final String pid, final String ptitle, final String ptype) {
		this.id = pid;
		if (this.id != null) {
			this.id = this.id.replaceAll(" ", "_");
		}
		this.title = ptitle;
		this.type = ptype;
	}

	/**
	 * get id.
	 * 
	 * @return id.
	 */
	public String getId() {
		return id;
	}

	/**
	 * set id.
	 * 
	 * @param pid
	 *            id.
	 */
	public void setId(final String pid) {
		this.id = pid;
	}

	/**
	 * get title.
	 * 
	 * @return title.
	 */
	public String getTitle() {
		return title;
	}

	/**
	 * get type.
	 * 
	 * @return type.
	 */
	public String getType() {
		return type;
	}
}
 public class ConfigBuildRef {

	/** workbook wrapper. */
	private XSSFEvaluationWorkbook wbWrapper;
	/** sheet. */
	private Sheet sheet;
	/** list of row need watched. */
	private List<Integer> watchList;
	/** expression engine. */
	private ExpressionEngine engine;
	/** cell helper. */
	private CellHelper cellHelper;
	/** cell attributes map. */
	private CellAttributesMap cellAttributesMap;
	/** final comment map. */
	private Map<Cell, String> finalCommentMap;
	/** body allow add. */
	private boolean bodyAllowAdd = false;
	/** true if in addMOde. */
	private boolean addMode = false;
	/** insert position. */
	private int insertPosition = -1;

	/**
	 * Saved configRange attributes for each full name. String : full name.
	 * ConfigRangeAttrs : include range (top, bottom) and rows mapping.
	 */
	private NavigableMap<String, ConfigRangeAttrs> shiftMap;

	/**
	 * Saved formula cells. include original formula and rows mapping for this
	 * cell.
	 */
	private Map<Cell, String> cachedCells;

	/**
	 * used for cache origin config range tree.
	 */
	private ConfigRange originConfigRange;

	/**
	 * used for originConfigRange to finding command through full name. key
	 * (String) - command name. i.e. F.deparments or E.department value
	 * (Command) - configuration command.
	 */
	private Map<String, Command> commandIndexMap = new HashMap<>();
	/**
	 * used for save the object for create new one. key (String) - var name.
	 * value (String) - object class name.
	 */
	private Map<String, String> collectionObjNameMap = new HashMap<>();

	/**
	 * constructor.
	 * 
	 * @param pWbWrapper
	 *            workbook wrapper.
	 * @param pSheet
	 *            sheet.
	 * @param pEngine
	 *            expression engine.
	 * @param pCellHelper
	 *            cellhelper.
	 * @param pCachedCells
	 *            cached cells.
	 * @param pCellAttributesMap
	 *            cell attributes map.
	 * @param pFinalCommentMap
	 *            final comment map.
	 */
	public ConfigBuildRef(final XSSFEvaluationWorkbook pWbWrapper,
			final Sheet pSheet, final ExpressionEngine pEngine,
			final CellHelper pCellHelper,
			final Map<Cell, String> pCachedCells,
			final CellAttributesMap pCellAttributesMap,
			final Map<Cell, String> pFinalCommentMap) {
		super();
		this.wbWrapper = pWbWrapper;
		this.sheet = pSheet;
		this.engine = pEngine;
		this.cellHelper = pCellHelper;
		this.cachedCells = pCachedCells;
		this.cellAttributesMap = pCellAttributesMap;
		this.finalCommentMap = pFinalCommentMap;
		this.shiftMap = new TreeMap<>();
	}

	/**
	 * Gets the watch list.
	 *
	 * @return the watch list
	 */
	public final List<Integer> getWatchList() {
		return watchList;
	}

	/**
	 * Sets the watch list.
	 *
	 * @param pwatchList
	 *            the new watch list
	 */
	public final void setWatchList(final List<Integer> pwatchList) {
		this.watchList = pwatchList;
	}

	/**
	 * Gets the wb wrapper.
	 *
	 * @return the wb wrapper
	 */
	public final XSSFEvaluationWorkbook getWbWrapper() {
		return wbWrapper;
	}

	/**
	 * Gets the sheet.
	 *
	 * @return the sheet
	 */
	public final Sheet getSheet() {
		return sheet;
	}

	/**
	 * Gets the engine.
	 *
	 * @return the engine
	 */
	public final ExpressionEngine getEngine() {
		return engine;
	}

	/**
	 * Gets the cell helper.
	 *
	 * @return the cell helper
	 */
	public final CellHelper getCellHelper() {
		return cellHelper;
	}

	/**
	 * Gets the cached cells.
	 *
	 * @return the cached cells
	 */
	public final Map<Cell, String> getCachedCells() {
		return cachedCells;
	}

	/**
	 * Put shift attrs.
	 *
	 * @param fullName
	 *            the full name
	 * @param attrs
	 *            the attrs
	 * @param unitRowsMapping
	 *            the unit rows mapping
	 */
	public final void putShiftAttrs(final String fullName,
			final ConfigRangeAttrs attrs,
			final RowsMapping unitRowsMapping) {
		attrs.setUnitRowsMapping(unitRowsMapping);
		this.shiftMap.put(fullName, attrs);
	}

	/**
	 * Gets the shift map.
	 *
	 * @return the shift map
	 */
	public final NavigableMap<String, ConfigRangeAttrs> getShiftMap() {
		return shiftMap;
	}

	/**
	 * Sets the shift map.
	 *
	 * @param pshiftMap
	 *            the shift map
	 */
	public final void setShiftMap(
			final NavigableMap<String, ConfigRangeAttrs> pshiftMap) {
		this.shiftMap = pshiftMap;
	}

	/**
	 * Checks if is body allow add.
	 *
	 * @return true, if is body allow add
	 */
	public final boolean isBodyAllowAdd() {
		return bodyAllowAdd;
	}

	/**
	 * Sets the body allow add.
	 *
	 * @param pbodyAllowAdd
	 *            the new body allow add
	 */
	public final void setBodyAllowAdd(final boolean pbodyAllowAdd) {
		this.bodyAllowAdd = pbodyAllowAdd;
	}

	/**
	 * Gets the origin config range.
	 *
	 * @return the origin config range
	 */
	public final ConfigRange getOriginConfigRange() {
		return originConfigRange;
	}

	/**
	 * Sets the origin config range.
	 *
	 * @param poriginConfigRange
	 *            the new origin config range
	 */
	public final void setOriginConfigRange(
			final ConfigRange poriginConfigRange) {
		this.originConfigRange = poriginConfigRange;
	}

	/**
	 * Gets the command index map.
	 *
	 * @return the command index map
	 */
	public final Map<String, Command> getCommandIndexMap() {
		return commandIndexMap;
	}

	/**
	 * Sets the command index map.
	 *
	 * @param pcommandIndexMap
	 *            the command index map
	 */
	public final void setCommandIndexMap(
			final Map<String, Command> pcommandIndexMap) {
		this.commandIndexMap = pcommandIndexMap;
	}

	/**
	 * Checks if is adds the mode.
	 *
	 * @return true, if is adds the mode
	 */
	public final boolean isAddMode() {
		return addMode;
	}

	/**
	 * Sets the adds the mode.
	 *
	 * @param paddMode
	 *            the new adds the mode
	 */
	public final void setAddMode(final boolean paddMode) {
		this.addMode = paddMode;
	}

	/**
	 * Gets the collection obj name map.
	 *
	 * @return the collection obj name map
	 */
	public final Map<String, String> getCollectionObjNameMap() {
		return collectionObjNameMap;
	}

	/**
	 * Sets the collection obj name map.
	 *
	 * @param pcollectionObjNameMap
	 *            the collection obj name map
	 */
	public final void setCollectionObjNameMap(
			final Map<String, String> pcollectionObjNameMap) {
		this.collectionObjNameMap = pcollectionObjNameMap;
	}

	/**
	 * Gets the insert position.
	 *
	 * @return the insert position
	 */
	public final int getInsertPosition() {
		return insertPosition;
	}

	/**
	 * Sets the insert position.
	 *
	 * @param pinsertPosition
	 *            the new insert position
	 */
	public final void setInsertPosition(final int pinsertPosition) {
		this.insertPosition = pinsertPosition;
	}

	/**
	 * Gets the cell attributes map.
	 *
	 * @return the cell attributes map
	 */
	public final CellAttributesMap getCellAttributesMap() {
		return cellAttributesMap;
	}

	/**
	 * Gets the final comment map.
	 *
	 * @return the final comment map
	 */
	public final Map<Cell, String> getFinalCommentMap() {
		return finalCommentMap;
	}

}
 public class ConfigurationHandler {

	/** command map. */
	@SuppressWarnings("rawtypes")
	private static Map<String, Class> commandMap = new HashMap<>();

	static {
		commandMap.put(TieConstants.COMMAND_FORM, FormCommand.class);
		commandMap.put(TieConstants.COMMAND_EACH, EachCommand.class);
	}

	/** refer to parent bean class. */
	private TieWebSheetBean parent = null;

	/** logger. */
	private static final Logger LOG = Logger.getLogger(ConfigurationHandler.class.getName());

	/**
	 * constructor.
	 *
	 * @param pparent
	 *            the parent
	 */
	public ConfigurationHandler(final TieWebSheetBean pparent) {
		super();
		this.parent = pparent;
	}

	/**
	 * Builds the configuration.
	 *
	 * @return the map
	 */
	public final Map<String, SheetConfiguration> buildConfiguration() {

		Map<String, SheetConfiguration> sheetConfigMap = new LinkedHashMap<>();
		// in buildsheet, it's possible to add sheets in workbook.
		// so cache the sheetname first here.
		List<String> sheetNames = new ArrayList<>();
		String sname;
		for (int i = 0; i < parent.getWb().getNumberOfSheets(); i++) {
			sname = parent.getWb().getSheetName(i);
			if (!sname.startsWith(org.tiefaces.common.TieConstants.COPY_SHEET_PREFIX)) {
				sheetNames.add(sname);
			}
		}

		for (String sheetName : sheetNames) {
			Sheet sheet = parent.getWb().getSheet(sheetName);
			ConfigurationUtility.buildSheetCommentFromAlias(sheet, parent.getTieCommandAliasList());
			buildSheet(sheet, sheetConfigMap, parent.getCellAttributesMap());
		}
		return sheetConfigMap;

	}

	/**
	 * Gets the sheet configuration.
	 *
	 * @param sheet
	 *            the sheet
	 * @param formName
	 *            the form name
	 * @param sheetRightCol
	 *            the sheet right col
	 * @return the sheet configuration
	 */
	private SheetConfiguration getSheetConfiguration(final Sheet sheet, final String formName,
			final int sheetRightCol) {

		SheetConfiguration sheetConfig = new SheetConfiguration();
		sheetConfig.setFormName(formName);
		sheetConfig.setSheetName(sheet.getSheetName());
		int leftCol = sheet.getLeftCol();
		int lastRow = sheet.getLastRowNum();
		int firstRow = sheet.getFirstRowNum();
		int rightCol = 0;
		int maxRow = 0;
		for (Row row : sheet) {
			if (row.getRowNum() > TieConstants.TIE_WEB_SHEET_MAX_ROWS) {
				break;
			}
			maxRow = row.getRowNum();
			int firstCellNum = row.getFirstCellNum();
			if (firstCellNum >= 0 && firstCellNum < leftCol) {
				leftCol = firstCellNum;
			}
			if ((row.getLastCellNum() - 1) > rightCol) {
				int verifiedcol = verifyLastCell(row, rightCol, sheetRightCol);
				if (verifiedcol > rightCol) {
					rightCol = verifiedcol;
				}
			}
		}
		if (maxRow < lastRow) {
			lastRow = maxRow;
		}
		// header range row set to 0 while column set to first column to
		// max
		// column (FF) e.g. $A$0 : $FF$0
		String tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol)
				+ TieConstants.CELL_ADDR_PRE_FIX + "0 : " + TieConstants.CELL_ADDR_PRE_FIX
				+ WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + "0";
		sheetConfig.setFormHeaderRange(tempStr);
		sheetConfig.setHeaderCellRange(new CellRange(tempStr));
		// body range row set to first row to last row while column set
		// to
		// first column to max column (FF) e.g. $A$1 : $FF$1000
		tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol)
				+ TieConstants.CELL_ADDR_PRE_FIX + (firstRow + 1) + " : " + TieConstants.CELL_ADDR_PRE_FIX
				+ WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + (lastRow + 1);
		sheetConfig.setFormBodyRange(tempStr);
		sheetConfig.setBodyCellRange(new CellRange(tempStr));
		sheetConfig.setFormBodyType(org.tiefaces.common.TieConstants.FORM_TYPE_FREE);
		sheetConfig.setCellFormAttributes(new HashMap<String, List<CellFormAttributes>>());

		// check it's a hidden sheet
		int sheetIndex = parent.getWb().getSheetIndex(sheet);
		if (parent.getWb().isSheetHidden(sheetIndex) || parent.getWb().isSheetVeryHidden(sheetIndex)) {
			sheetConfig.setHidden(true);
		}

		return sheetConfig;

	}

	/**
	 * Builds the form command from sheet config.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @param sheet
	 *            the sheet
	 * @return the form command
	 */
	private FormCommand buildFormCommandFromSheetConfig(final SheetConfiguration sheetConfig, final Sheet sheet) {

		int firstRow = sheetConfig.getBodyCellRange().getTopRow();
		int leftCol = sheetConfig.getBodyCellRange().getLeftCol();
		int rightCol = sheetConfig.getBodyCellRange().getRightCol();
		int lastRow = sheetConfig.getBodyCellRange().getBottomRow();

		Cell firstCell = sheet.getRow(firstRow).getCell(leftCol, MissingCellPolicy.CREATE_NULL_AS_BLANK);

		FormCommand fcommand = new FormCommand();
		fcommand.setCommandTypeName(TieConstants.COMMAND_FORM);
		if (sheetConfig.isHidden()) {
			fcommand.setHidden(TieConstants.TRUE_STRING);
		} else {
			fcommand.setHidden(TieConstants.FALSE_STRING);
		}
		fcommand.setName(sheetConfig.getFormName());
		fcommand.getConfigRange().setFirstRowRef(firstCell, true);
		fcommand.getConfigRange().setLastRowPlusRef(sheet, rightCol, lastRow, true);
		fcommand.setHeaderLength("0");
		fcommand.setFooterLength("0");
		fcommand.setLength(Integer.toString(lastRow - firstRow + 1));
		return fcommand;
	}

	/**
	 * check last column. if it's blank then treat it as null cell.
	 *
	 * @param row
	 *            row object.
	 * @param stoppoint
	 *            the left cell we want to stop check.
	 * @param sheetRightCol
	 *            the sheet right col
	 * @return integer. the last column without blank cell.
	 */
	private int verifyLastCell(final Row row, final int stoppoint, final int sheetRightCol) {

		int lastCol = sheetRightCol;
		int col;
		for (col = lastCol; col >= stoppoint; col--) {

			Cell cell = row.getCell(col);
			if ((cell != null) && (cell.getCellTypeEnum() != CellType.BLANK)) {
				break;
			}
		}
		return col;
	}

	/**
	 * new implement of configuration with setting in comments.
	 *
	 * @param sheet
	 *            the sheet
	 * @param sheetConfigMap
	 *            the sheet config map
	 * @param cellAttributesMap
	 *            the cell attributes map
	 */

	/**
	 * build a sheet for configuration map.
	 *
	 * @param sheet
	 *            sheet.
	 * @param sheetConfigMap
	 *            sheetConfiguration map.
	 * @param cellAttributesMap
	 *            the cell attributes map
	 */
	public final void buildSheet(final Sheet sheet, final Map<String, SheetConfiguration> sheetConfigMap,
			final CellAttributesMap cellAttributesMap) {

		if ((sheet.getLastRowNum() <= 0) && (sheet.getRow(0) == null)) {
			// this is a empty sheet. skip it.
			return;
		}
		checkAndRepairLastRow(sheet);

		int sheetRightCol = WebSheetUtility.getSheetRightCol(sheet);

		List<ConfigCommand> commandList = buildCommandListFromSheetComment((XSSFSheet) sheet, sheetRightCol,
				cellAttributesMap);

		boolean hasEachCommand = hasEachCommandInTheList(commandList);

		List<String> formList = new ArrayList<>();

		buildSheetConfigMapFromFormCommand(sheet, sheetConfigMap, commandList, formList, sheetRightCol);
		// match parent command
		matchParentCommand(commandList);
		// setup save attrs in hidden column in the sheet.
		// loop command list again to assemble other command list into sheet
		// configuration
		matchSheetConfigForm(sheetConfigMap, commandList, formList);
		initTemplateForCommand(sheet, sheetConfigMap, formList, hasEachCommand);
	}

	/**
	 * check and repair the sheet's lastrow. If the row is blank then remove it.
	 *
	 * @param sheet
	 *            the sheet
	 */
	private final void checkAndRepairLastRow(final Sheet sheet) {
		// repair last row if it's inserted in the configuration generation
		Row lastrow = sheet.getRow(sheet.getLastRowNum());
		// if it's lastrow and all the cells are blank. then remove the lastrow.
		if (lastrow != null) {
			for (Cell cell : lastrow) {
				if ((cell.getCellTypeEnum() != CellType._NONE) && (cell.getCellTypeEnum() != CellType.BLANK)) {
					return;
				}
			}
			sheet.removeRow(lastrow);
		}

	}

	/**
	 * Initialize template for command to use. e.g. set origin row number and copy
	 * template if there's each command. create missing row as row require
	 * sequenced.
	 * 
	 * @param sheet
	 *            sheet.
	 * @param sheetConfigMap
	 *            sheet config map.
	 * @param formList
	 *            list of form.
	 * @param hasEachCommand
	 *            whether has each command.
	 */
	private void initTemplateForCommand(final Sheet sheet, final Map<String, SheetConfiguration> sheetConfigMap,
			final List<String> formList, final boolean hasEachCommand) {

		for (String formname : formList) {
			SheetConfiguration sheetConfig = sheetConfigMap.get(formname);
			CellRange range = sheetConfig.getBodyCellRange();
			for (int index = range.getTopRow(); index <= range.getBottomRow(); index++) {
				Row row = sheet.getRow(index);
				if (row == null) {
					row = sheet.createRow(index);
				}
				if (hasEachCommand) {
					ConfigurationUtility.setOriginalRowNumInHiddenColumn(row, index);
				}

			}
		}

		if (hasEachCommand) {
			copyTemplateForTieCommands(sheet);
		}

	}

	/**
	 * build command list from comments. after transfer the comment to command,
	 * remove it from comments.
	 *
	 * @param sheet
	 *            sheet.
	 * @param sheetRightCol
	 *            the sheet right col
	 * @param cellAttributesMap
	 *            the cell attributes map
	 * @return command list.
	 */
	private List<ConfigCommand> buildCommandListFromSheetComment(final XSSFSheet sheet, final int sheetRightCol,
			final CellAttributesMap cellAttributesMap) {
		List<ConfigCommand> commandList = new ArrayList<>();
		// if skip then return empty list.
		if (parent.isSkipConfiguration()) {
			return commandList;
		}

		Map<CellAddress, ? extends Comment> comments = null;

		try {
			// due to a poi bug. null exception throwed if no comments in the
			// sheet.
			comments = sheet.getCellComments();
		} catch (Exception ex) {
			LOG.log(Level.FINE, "Null exception throwed when no comment exists: " + ex.getLocalizedMessage(), ex);
		}
		if (comments == null) {
			return commandList;
		}

		// not sure the map is sorted. So use tree map to sort it.
		SortedSet<CellAddress> keys = new TreeSet<>(comments.keySet());
		// go through each comments
		// if found tie command then transfer it to list also remove from
		// comments.
		for (CellAddress key : keys) {
			Cell cell = sheet.getRow(key.getRow()).getCell(key.getColumn(), MissingCellPolicy.CREATE_NULL_AS_BLANK);
			buildCommandList(sheet, sheetRightCol, cell, commandList, cellAttributesMap);
		}
		return commandList;

	}

	/**
	 * build top level configuration map from command list. User can either put
	 * tie:form command in the comments (which will transfer to sheetConfig), Or
	 * just ignore it, then use whole sheet as one form.
	 *
	 * @param sheet
	 *            sheet.
	 * @param sheetConfigMap
	 *            sheetConfigMap.
	 * @param commandList
	 *            command list.
	 * @param formList
	 *            form list.
	 * @param sheetRightCol
	 *            the sheet right col
	 */
	private void buildSheetConfigMapFromFormCommand(final Sheet sheet,
			final Map<String, SheetConfiguration> sheetConfigMap, final List<ConfigCommand> commandList,
			final List<String> formList, final int sheetRightCol) {
		boolean foundForm = false;
		int minRowNum = sheet.getLastRowNum();
		int maxRowNum = sheet.getFirstRowNum();
		for (Command command : commandList) {
			// check whether is form command
			if (command.getCommandTypeName().equalsIgnoreCase(TieConstants.COMMAND_FORM)) {
				foundForm = true;
				FormCommand fcommand = (FormCommand) command;
				sheetConfigMap.put(fcommand.getName(),
						getSheetConfigurationFromConfigCommand(sheet, fcommand, sheetRightCol));
				formList.add(fcommand.getName());
				if (fcommand.getTopRow() < minRowNum) {
					minRowNum = fcommand.getTopRow();
				}
				if (fcommand.getLastRow() > maxRowNum) {
					maxRowNum = fcommand.getLastRow();
				}
			}
		}
		// if no form found, then use the whole sheet as form
		if (!foundForm) {
			WebSheetUtility.clearHiddenColumns(sheet);
			String formName = sheet.getSheetName();
			SheetConfiguration sheetConfig = getSheetConfiguration(sheet, formName, sheetRightCol);
			FormCommand fcommand = buildFormCommandFromSheetConfig(sheetConfig, sheet);
			commandList.add(fcommand);
			sheetConfig.setFormCommand(fcommand);
			sheetConfigMap.put(formName, sheetConfig);
			formList.add(formName);
			minRowNum = sheet.getFirstRowNum();
			maxRowNum = sheet.getLastRowNum();
		}

		// if skip config then return.
		if (parent.isSkipConfiguration()) {
			return;
		}
		SaveAttrsUtility.setSaveAttrsForSheet(sheet, minRowNum, maxRowNum,
				parent.getCellAttributesMap().getTemplateCommentMap().get(TieConstants.SAVE_COMMENT_KEY_IN_MAP));
	}

	/**
	 * Set up parent attribute for each command (exclude form command). The top
	 * level commands have no parent.
	 *
	 * @param commandList
	 *            the command list
	 */
	private void matchParentCommand(final List<ConfigCommand> commandList) {

		if (commandList == null) {
			return;
		}
		for (int i = 0; i < commandList.size(); i++) {
			ConfigCommand child = commandList.get(i);
			if (!child.getCommandTypeName().equalsIgnoreCase(TieConstants.COMMAND_FORM)) {
				setParentForChildCommand(commandList, i, child);
			}
		}
	}

	/**
	 * Sets the parent for child command.
	 *
	 * @param commandList
	 *            the command list
	 * @param i
	 *            the i
	 * @param child
	 *            the child
	 */
	private void setParentForChildCommand(final List<ConfigCommand> commandList, final int i,
			final ConfigCommand child) {
		int matchIndex = -1;
		ConfigRange matchRange = null;
		for (int j = 0; j < commandList.size(); j++) {
			if (j != i) {
				Command commandParent = commandList.get(j);
				if (!commandParent.getCommandTypeName().equalsIgnoreCase(TieConstants.COMMAND_FORM)
						&& WebSheetUtility.insideRange(child.getConfigRange(), commandParent.getConfigRange())
						&& ((matchRange == null)
								|| (WebSheetUtility.insideRange(commandParent.getConfigRange(), matchRange)))) {
					matchRange = commandParent.getConfigRange();
					matchIndex = j;
				}
			}

		}
		if (matchIndex >= 0) {
			commandList.get(matchIndex).getConfigRange().addCommand(child);
			child.setParentFound(true);
		}
	}

	/**
	 * check whether contain each command in the list.
	 * 
	 * @param commandList
	 *            command list.
	 * @return true if contain each command.
	 */
	private boolean hasEachCommandInTheList(final List<ConfigCommand> commandList) {

		if (commandList != null) {
			for (ConfigCommand command : commandList) {
				if (command.getCommandTypeName().equalsIgnoreCase(TieConstants.COMMAND_EACH)) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * Assemble top level command to sheetConfiguration (form). top level commands
	 * are those haven't matched from matchParentCommand function.
	 * 
	 * @param sheetConfigMap
	 *            sheet configuration map.
	 * @param commandList
	 *            command list.
	 * @param formList
	 *            form list.
	 */
	private void matchSheetConfigForm(final Map<String, SheetConfiguration> sheetConfigMap,
			final List<ConfigCommand> commandList, final List<String> formList) {
		for (ConfigCommand command : commandList) {
			// check weather it's form command
			if (!command.getCommandTypeName().equalsIgnoreCase(TieConstants.COMMAND_FORM)
					&& (!command.isParentFound())) {
				matchCommandToSheetConfigForm(sheetConfigMap, formList, command);
			}
		}
	}

	/**
	 * Match command to sheet config form.
	 *
	 * @param sheetConfigMap
	 *            the sheet config map
	 * @param formList
	 *            the form list
	 * @param command
	 *            the command
	 */
	private void matchCommandToSheetConfigForm(final Map<String, SheetConfiguration> sheetConfigMap,
			final List<String> formList, final ConfigCommand command) {
		for (String formname : formList) {
			SheetConfiguration sheetConfig = sheetConfigMap.get(formname);
			if (WebSheetUtility.insideRange(command.getConfigRange(), sheetConfig.getFormCommand().getConfigRange())) {
				sheetConfig.getFormCommand().getConfigRange().addCommand(command);
				break;
			}
		}
	}

	/**
	 * Copy the each command area to seperated sheet. As it will be used for
	 * iteration.
	 * 
	 * @param sheet
	 *            sheet.
	 */
	private void copyTemplateForTieCommands(final Sheet sheet) {
		// if skip configuration. then return.
		if (parent.isSkipConfiguration()) {
			return;
		}
		Workbook wb = sheet.getWorkbook();
		String copyName = TieConstants.COPY_SHEET_PREFIX + sheet.getSheetName();
		if (wb.getSheet(copyName) == null) {
			Sheet newSheet = wb.cloneSheet(wb.getSheetIndex(sheet));
			int sheetIndex = wb.getSheetIndex(newSheet);
			wb.setSheetName(sheetIndex, copyName);
			wb.setSheetHidden(sheetIndex, Workbook.SHEET_STATE_VERY_HIDDEN);
		}
	}

	/**
	 * build command list from comment.
	 *
	 * @param sheet
	 *            sheet.
	 * @param sheetRightCol
	 *            sheet right column.
	 * @param cell
	 *            the cell
	 * @param cList
	 *            command list.
	 * @param cellAttributesMap
	 *            the cell attributes map
	 * @return command list.
	 */
	private List<ConfigCommand> buildCommandList(final Sheet sheet, final int sheetRightCol, final Cell cell,
			final List<ConfigCommand> cList, final CellAttributesMap cellAttributesMap) {

		Comment comment = cell.getCellComment();
		String text = comment.getString().getString();
		String[] commentLines = text.split("\\n");
		StringBuilder newComment = new StringBuilder();
		boolean changed = false;
		for (String commentLine : commentLines) {
			String line = commentLine.trim();
			if (ParserUtility.isCommandString(line)) {
				processCommandLine(sheet, cell, line, cList, sheetRightCol);
				changed = true;
			} else if (ParserUtility.isEmptyMethodString(line) || ParserUtility.isMethodString(line)) {
				processMethodLine(cell, line, cellAttributesMap);
				changed = true;
			} else {
				if (newComment.length() > 0) {
					newComment.append("\\n" + commentLine);
				} else {
					newComment.append(commentLine);
				}
			}
		}
		if (!changed) {
			moveCommentToMap(cell, text, cellAttributesMap.getTemplateCommentMap(), true);
		} else {
			// reset comment string if changed
			if (newComment.length() > 0) {
				moveCommentToMap(cell, newComment.toString(), cellAttributesMap.getTemplateCommentMap(), true);
				CreationHelper factory = sheet.getWorkbook().getCreationHelper();
				RichTextString str = factory.createRichTextString(newComment.toString());
				comment.setString(str);
			} else {
				// remove cell comment if new comment become empty.
				cell.removeCellComment();
			}
		}

		return cList;
	}

	/**
	 * Process method line.
	 *
	 * @param cell
	 *            the cell
	 * @param line
	 *            the line
	 * @param cellAttributesMap
	 *            the cell attributes map
	 */
	private void processMethodLine(final Cell cell, final String line, final CellAttributesMap cellAttributesMap) {
		if (ParserUtility.isWidgetMethodString(line)) {
			ParserUtility.parseWidgetAttributes(cell, line, cellAttributesMap);
		} else if (ParserUtility.isValidateMethodString(line)) {
			ParserUtility.parseValidateAttributes(cell, line, cellAttributesMap);
		} else {
			moveCommentToMap(cell, line, cellAttributesMap.getTemplateCommentMap(), false);
		}
	}

	/**
	 * Process command line.
	 *
	 * @param sheet
	 *            the sheet
	 * @param cell
	 *            the cell
	 * @param line
	 *            the line
	 * @param cList
	 *            the c list
	 * @param sheetRightCol
	 *            the sheet right col
	 */
	private void processCommandLine(final Sheet sheet, final Cell cell, final String line,
			final List<ConfigCommand> cList, final int sheetRightCol) {
		int nameEndIndex = line.indexOf(TieConstants.ATTR_PREFIX, TieConstants.COMMAND_PREFIX.length());
		if (nameEndIndex < 0) {
			String errMsg = "Failed to parse command line [" + line + "]. Expected '" + TieConstants.ATTR_PREFIX
					+ "' symbol.";
			LOG.severe(errMsg);
			throw new IllegalStateException(errMsg);
		}
		String commandName = line.substring(TieConstants.COMMAND_PREFIX.length(), nameEndIndex).trim();
		Map<String, String> attrMap = buildAttrMap(line, nameEndIndex);
		ConfigCommand configCommand = createConfigCommand(sheet, cell, sheetRightCol, commandName, attrMap);
		if (configCommand != null) {
			cList.add(configCommand);
		}
	}

	/**
	 * change the comment.
	 *
	 * @param cell
	 *            the cell
	 * @param newComment
	 *            updated comment.
	 * @param sheetCommentMap
	 *            the sheet comment map
	 * @param normalComment
	 *            the normal comment
	 */
	private void moveCommentToMap(final Cell cell, final String newComment,
			final Map<String, Map<String, String>> sheetCommentMap, final boolean normalComment) {

		String cellKey = cell.getSheet().getSheetName() + "!$" + cell.getColumnIndex() + "$" + cell.getRowIndex();

		ParserUtility.parseCommentToMap(cellKey, newComment, sheetCommentMap, normalComment);

	}

	/**
	 * create configuration command.
	 * 
	 * @param sheet
	 *            sheet.
	 * @param firstCell
	 *            first cell.
	 * @param sheetRightCol
	 *            sheet right col.
	 * @param commandName
	 *            command name.
	 * @param attrMap
	 *            attibutes map.
	 * @return configuration command.
	 */

	private ConfigCommand createConfigCommand(final Sheet sheet, final Cell firstCell, final int sheetRightCol,
			final String commandName, final Map<String, String> attrMap) {
		@SuppressWarnings("rawtypes")
		Class clas = commandMap.get(commandName);
		if (clas == null) {
			LOG.log(Level.WARNING, "Cannot find command class for {} ", commandName);
			return null;
		}
		try {
			ConfigCommand command = (ConfigCommand) clas.newInstance();
			command.setCommandTypeName(commandName);
			for (Map.Entry<String, String> attr : attrMap.entrySet()) {
				WebSheetUtility.setObjectProperty(command, attr.getKey(), attr.getValue(), true);
			}
			command.getConfigRange().setFirstRowRef(firstCell, true);
			command.getConfigRange().setLastRowPlusRef(sheet, sheetRightCol, command.getLastRow(), true);
			return command;
		} catch (Exception e) {
			LOG.log(Level.WARNING,
					"Failed to initialize command class " + clas.getName() + " for command" + commandName, e);
			return null;
		}
	}

	/**
	 * Build the attributes map.
	 * 
	 * @param commandLine
	 *            command line from comment.
	 * @param nameEndIndex
	 *            index of command name's end.
	 * @return attributes map.
	 */
	private Map<String, String> buildAttrMap(final String commandLine, final int nameEndIndex) {
		int paramsEndIndex = commandLine.lastIndexOf(TieConstants.ATTR_SUFFIX);
		if (paramsEndIndex < 0) {
			String errMsg = "Failed to parse command line [" + commandLine + "]. Expected '" + TieConstants.ATTR_SUFFIX
					+ "' symbol.";
			throw new IllegalArgumentException(errMsg);
		}
		String attrString = commandLine.substring(nameEndIndex + 1, paramsEndIndex).trim();
		return ParserUtility.parseCommandAttributes(attrString);
	}

	/**
	 * Create sheet configuration from form command.
	 *
	 * @param sheet
	 *            sheet.
	 * @param fcommand
	 *            form command.
	 * @param sheetRightCol
	 *            the sheet right col
	 * @return sheet configuration.
	 */
	private SheetConfiguration getSheetConfigurationFromConfigCommand(final Sheet sheet, final FormCommand fcommand,
			final int sheetRightCol) {

		SheetConfiguration sheetConfig = new SheetConfiguration();
		sheetConfig.setFormName(fcommand.getName());
		sheetConfig.setSheetName(sheet.getSheetName());
		int leftCol = fcommand.getLeftCol();
		int lastRow = fcommand.getLastRow();
		int rightCol = 0;
		int maxRow = 0;
		for (Row row : sheet) {
			if (row.getRowNum() > TieConstants.TIE_WEB_SHEET_MAX_ROWS) {
				break;
			}
			maxRow = row.getRowNum();
			if ((row.getLastCellNum() - 1) > rightCol) {
				int verifiedcol = verifyLastCell(row, rightCol, sheetRightCol);
				if (verifiedcol > rightCol) {
					rightCol = verifiedcol;
				}
			}
		}
		if (maxRow < lastRow) {
			lastRow = maxRow;
		}
		// header range row set to 0 while column set to first column to
		// max
		// column (FF) e.g. $A$0 : $FF$0
		setHeaderOfSheetConfiguration(fcommand, sheetConfig, leftCol, rightCol);
		// body range row set to first row to last row while column set
		// to
		// first column to max column (FF) e.g. $A$1 : $FF$1000
		setBodyOfSheetConfiguration(fcommand, sheetConfig, leftCol, lastRow, rightCol);

		// footer range row set to 0 while column set to first column to
		// max
		// column (FF) e.g. $A$0 : $FF$0
		setFooterOfSheetConfiguration(fcommand, sheetConfig, leftCol, rightCol);

		String hidden = fcommand.getHidden();
		if ((hidden != null) && (Boolean.parseBoolean(hidden))) {
			sheetConfig.setHidden(true);
		}
		String fixedWidthStyle = fcommand.getFixedWidthStyle();
		if ((fixedWidthStyle != null) && (Boolean.parseBoolean(fixedWidthStyle))) {
			sheetConfig.setFixedWidthStyle(true);
		}
		sheetConfig.setFormCommand(fcommand);
		return sheetConfig;

	}

	/**
	 * Sets the footer of sheet configuration.
	 *
	 * @param fcommand
	 *            the fcommand
	 * @param sheetConfig
	 *            the sheet config
	 * @param leftCol
	 *            the left col
	 * @param rightCol
	 *            the right col
	 */
	private void setFooterOfSheetConfiguration(final FormCommand fcommand, final SheetConfiguration sheetConfig,
			final int leftCol, final int rightCol) {
		String tempStr;
		if (fcommand.calcFooterLength() == 0) {
			tempStr = CellUtility.getCellIndexLetterKey(leftCol, 0) + " : "
					+ CellUtility.getCellIndexLetterKey(rightCol, 0);
		} else {
			tempStr = CellUtility.getCellIndexLetterKey(leftCol,
					fcommand.getTopRow() + fcommand.calcHeaderLength() + fcommand.calcBodyLength()) + " : "
					+ CellUtility.getCellIndexLetterKey(rightCol, fcommand.getTopRow() + fcommand.calcHeaderLength());
		}
		sheetConfig.setFormFooterRange(tempStr);
		sheetConfig.setFooterCellRange(new CellRange(tempStr));
	}

	/**
	 * Sets the body of sheet configuration.
	 *
	 * @param fcommand
	 *            the fcommand
	 * @param sheetConfig
	 *            the sheet config
	 * @param leftCol
	 *            the left col
	 * @param lastRow
	 *            the last row
	 * @param rightCol
	 *            the right col
	 */
	private void setBodyOfSheetConfiguration(final FormCommand fcommand, final SheetConfiguration sheetConfig,
			final int leftCol, final int lastRow, final int rightCol) {
		String tempStr;
		tempStr = CellUtility.getCellIndexLetterKey(leftCol, fcommand.getTopRow() + fcommand.calcHeaderLength() + 1)
				+ " : " + CellUtility.getCellIndexLetterKey(rightCol, lastRow + 1);

		sheetConfig.setFormBodyRange(tempStr);
		sheetConfig.setBodyCellRange(new CellRange(tempStr));
		sheetConfig.setFormBodyType(TieConstants.FORM_TYPE_FREE);
		sheetConfig.setCellFormAttributes(new HashMap<String, List<CellFormAttributes>>());
	}

	/**
	 * Sets the header of sheet configuration.
	 *
	 * @param fcommand
	 *            the fcommand
	 * @param sheetConfig
	 *            the sheet config
	 * @param leftCol
	 *            the left col
	 * @param rightCol
	 *            the right col
	 */
	private void setHeaderOfSheetConfiguration(final FormCommand fcommand, final SheetConfiguration sheetConfig,
			final int leftCol, final int rightCol) {
		String tempStr;
		if (fcommand.calcHeaderLength() == 0) {
			tempStr = CellUtility.getCellIndexLetterKey(leftCol, 0) + " : "
					+ CellUtility.getCellIndexLetterKey(rightCol, 0);
		} else {
			tempStr = CellUtility.getCellIndexLetterKey(leftCol, fcommand.getTopRow() + 1) + " : "
					+ CellUtility.getCellIndexLetterKey(rightCol, fcommand.getTopRow() + fcommand.calcHeaderLength());
		}
		sheetConfig.setFormHeaderRange(tempStr);
		sheetConfig.setHeaderCellRange(new CellRange(tempStr));
	}

}
 public class RangeBuildRef {

	/** left. */
	private int left;
	/** right. */
	private int right;
	/** total width. */
	private int totalWidth;
	/** sheet. */
	private Sheet sheet;

	/**
	 * Instantiates a new range build ref.
	 *
	 * @param pleft
	 *            the left
	 * @param pright
	 *            the right
	 * @param ptotalWidth
	 *            the total width
	 * @param psheet
	 *            the sheet
	 */
	public RangeBuildRef(final int pleft, final int pright,
			final int ptotalWidth, final Sheet psheet) {
		super();
		this.left = pleft;
		this.right = pright;
		this.totalWidth = ptotalWidth;
		this.sheet = psheet;
	}

	/**
	 * Gets the left.
	 *
	 * @return the left
	 */
	public final int getLeft() {
		return left;
	}

	/**
	 * Sets the left.
	 *
	 * @param pleft
	 *            the left to set
	 */
	public final void setLeft(final int pleft) {
		this.left = pleft;
	}

	/**
	 * Gets the right.
	 *
	 * @return the right
	 */
	public final int getRight() {
		return right;
	}

	/**
	 * Sets the right.
	 *
	 * @param pright
	 *            the right to set
	 */
	public final void setRight(final int pright) {
		this.right = pright;
	}

	/**
	 * Gets the total width.
	 *
	 * @return the totalWidth
	 */
	public final int getTotalWidth() {
		return totalWidth;
	}

	/**
	 * Sets the total width.
	 *
	 * @param ptotalWidth
	 *            the totalWidth to set
	 */
	public final void setTotalWidth(final int ptotalWidth) {
		this.totalWidth = ptotalWidth;
	}

	/**
	 * Gets the sheet.
	 *
	 * @return the sheet1
	 */
	public final Sheet getSheet() {
		return sheet;
	}

	/**
	 * Sets the sheet.
	 *
	 * @param psheet1
	 *            the sheet1 to set
	 */
	public final void setSheet(final Sheet psheet1) {
		this.sheet = psheet1;
	}

}
 public class RowsMapping implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = 1L;
	/** The rows map. */
	private Map<Integer, List<SerialRow>> rowsMap = new HashMap<>();

	/**
	 * Instantiates a new rows mapping.
	 */
	public RowsMapping() {
		super();
	}

	/**
	 * Copy constructor.
	 *
	 * @param source
	 *            the source
	 */
	public RowsMapping(final RowsMapping source) {
		this.rowsMap.putAll(source.getRowsMap());
	}

	/**
	 * Gets the rows map.
	 *
	 * @return the rows map
	 */
	public final Map<Integer, List<SerialRow>> getRowsMap() {
		return rowsMap;
	}

	/**
	 * Adds the row.
	 *
	 * @param sourceRowNum
	 *            the source row num
	 * @param targetRow
	 *            the target row
	 */
	public final void addRow(final Integer sourceRowNum,
			final Row targetRow) {
		List<SerialRow> mapRowList = rowsMap.get(sourceRowNum);
		if (mapRowList == null) {
			mapRowList = new ArrayList<>();
		}
		SerialRow serialTarget = new SerialRow(targetRow, -1);
		if (!mapRowList.contains(serialTarget)) {
			mapRowList.add(serialTarget);
			rowsMap.put(sourceRowNum, mapRowList);
		}
	}

	/**
	 * Removes the row.
	 *
	 * @param sourceRowNum
	 *            the source row num
	 * @param targetRow
	 *            the target row
	 */
	public final void removeRow(final Integer sourceRowNum,
			final Row targetRow) {
		List<SerialRow> mapRowList = rowsMap.get(sourceRowNum);
		if (mapRowList != null) {
			mapRowList.remove(new SerialRow(targetRow, -1));
			rowsMap.put(sourceRowNum, mapRowList);
		}
	}

	/**
	 * Gets the.
	 *
	 * @param sourceRowNum
	 *            the source row num
	 * @return the list
	 */
	public final List<SerialRow> get(final Integer sourceRowNum) {
		return rowsMap.get(sourceRowNum);
	}

	/**
	 * Merge map.
	 *
	 * @param addMap
	 *            the add map
	 */
	public final void mergeMap(final RowsMapping addMap) {
		Map<Integer, List<SerialRow>> map = addMap.getRowsMap();
		for (Map.Entry<Integer, List<SerialRow>> entry : map.entrySet()) {
			List<SerialRow> entryRowList = entry.getValue();
			if ((entryRowList != null) && (!entryRowList.isEmpty())) {
				for (SerialRow row : entryRowList) {
					this.addRow(entry.getKey(), row.getRow());
				}
			}
		}
	}

	
	/**
	 * recover rows mapping by using it's address.
	 * 
	 * @param sheet
	 *            sheet.
	 */
	public final void recover(final Sheet sheet) {

		for (Map.Entry<Integer, List<SerialRow>> entry : this.getRowsMap()
				.entrySet()) {
			List<SerialRow> listRow = entry.getValue();
			for (SerialRow serialRow : listRow) {
				serialRow.recover(sheet);
			}
		}	

	}
	
	
	/**
	 * Obtain a human readable representation.
	 * 
	 * @return String Human readable label
	 */
	@Override
	public final String toString() {

		StringBuilder sb = new StringBuilder();
		sb.append("{");
		for (Map.Entry<Integer, List<SerialRow>> entry : this.getRowsMap()
				.entrySet()) {
			sb.append(entry.getKey() + "=[");
			for (SerialRow row : entry.getValue()) {
				sb.append(row.getRow().getRowNum() + ",");
			}
			sb.append("], ");
		}
		sb.append("}");
		return sb.toString();
	}

}
 public class SheetConfiguration implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = -108522960628560962L;

	/** The form name. */
	private String formName; // corresponds to tab name of configuration tab

	/** The sheet name. */
	private String sheetName; // corresponds to sheet name of configuration tab

	/** The form header range. */
	private String formHeaderRange; // corresponds to formHeaderRange of

	/** The form body range. */
	// configuration tab
	private String formBodyRange; // corresponds to formBodyRange of

	/** The form footer range. */
	// configuration tab
	private String formFooterRange; // corresponds to formFooterRange of

	/** The header cell range. */
	private CellRange headerCellRange; // transfer formHeaderRange to CellRange

	/** The body cell range. */
	// object
	private CellRange bodyCellRange; // transfer formBodyRange to CellRange

	/** The footer cell range. */
	// object
	private CellRange footerCellRange; // transfer formFooterRange to CellRange

	/** The form body type. */
	// object
	private String formBodyType; // formBodyType: Repeat or Free

	/** The body allow add rows. */
	private boolean bodyAllowAddRows; // whether allow dynamic insert row in

	/** The body initial rows. */
	// form body
	private int bodyInitialRows; // initial rows number for form body. support

	/** The body populated. */
	// EL.
	private boolean bodyPopulated; // runtime holder

	/** The max row per page. */
	private int maxRowPerPage; // max rows per page

	/** The cell form attributes. */
	private Map<String, List<CellFormAttributes>> cellFormAttributes;
	/**
	 * The form command.
	 */
	private FormCommand formCommand;

	/** The shift map. */
	private NavigableMap<String, ConfigRangeAttrs> shiftMap;

	/** The command index map. */
	private HashMap<String, Command> commandIndexMap;

	/** The collection obj name map. */
	private Map<String, String> collectionObjNameMap;

	/** The watch list. */
	private List<Integer> watchList;

	/** The hidden. */
	private boolean hidden = false; // in some case e.g. prepop, we choose to
									// hide the sheet.
	/** fixedWidthStyle. */
	private boolean fixedWidthStyle = false;

	/** The cached origin formulas. */
	private SerialCellMap serialCachedCells = new SerialCellMap();

	/**
	 * due to poi bug. cannot set comment during evaluate cell time. have to
	 * output comments following sequence. i.e. row by row. hold comments for
	 * the final sheet config form.
	 * 
	 * key is cell. value is the comments.
	 */
	private SerialCellMap serialFinalCommentMap = new SerialCellMap();

	/** logger. */
	private static final Logger LOG = Logger
			.getLogger(SerialWorkbook.class.getName());

	/**
	 * Instantiates a new sheet configuration.
	 */
	public SheetConfiguration() {
		super();
		LOG.fine("Sheet Configuration constructor");
	}

	/**
	 * Gets the sheet name.
	 *
	 * @return the sheet name
	 */
	public final String getSheetName() {
		return sheetName;
	}

	/**
	 * Sets the sheet name.
	 *
	 * @param psheetName
	 *            the new sheet name
	 */
	public final void setSheetName(final String psheetName) {
		this.sheetName = psheetName;
	}

	/**
	 * Gets the form name.
	 *
	 * @return the form name
	 */
	public final String getFormName() {
		return formName;
	}

	/**
	 * Sets the form name.
	 *
	 * @param pformName
	 *            the new form name
	 */
	public final void setFormName(final String pformName) {
		this.formName = pformName;
	}

	/**
	 * Gets the form header range.
	 *
	 * @return the form header range
	 */
	public final String getFormHeaderRange() {
		return formHeaderRange;
	}

	/**
	 * Sets the form header range.
	 *
	 * @param pformHeaderRange
	 *            the new form header range
	 */
	public final void setFormHeaderRange(final String pformHeaderRange) {
		this.formHeaderRange = pformHeaderRange;
	}

	/**
	 * Gets the form body range.
	 *
	 * @return the form body range
	 */
	public final String getFormBodyRange() {
		return formBodyRange;
	}

	/**
	 * Sets the form body range.
	 *
	 * @param pformBodyRange
	 *            the new form body range
	 */
	public final void setFormBodyRange(final String pformBodyRange) {
		this.formBodyRange = pformBodyRange;
	}

	/**
	 * Gets the form footer range.
	 *
	 * @return the form footer range
	 */
	public final String getFormFooterRange() {
		return formFooterRange;
	}

	/**
	 * Sets the form footer range.
	 *
	 * @param pformFooterRange
	 *            the new form footer range
	 */
	public final void setFormFooterRange(final String pformFooterRange) {
		this.formFooterRange = pformFooterRange;
	}

	/**
	 * Gets the cell form attributes.
	 *
	 * @return the cell form attributes
	 */
	public final Map<String, List<CellFormAttributes>> getCellFormAttributes() {
		return cellFormAttributes;
	}

	/**
	 * Sets the cell form attributes.
	 *
	 * @param pcellFormAttributes
	 *            the cell form attributes
	 */
	public final void setCellFormAttributes(
			final Map<String, List<CellFormAttributes>> pcellFormAttributes) {
		this.cellFormAttributes = pcellFormAttributes;
	}

	/**
	 * Gets the header cell range.
	 *
	 * @return the header cell range
	 */
	public final CellRange getHeaderCellRange() {
		return headerCellRange;
	}

	/**
	 * Sets the header cell range.
	 *
	 * @param pheaderCellRange
	 *            the new header cell range
	 */
	public final void setHeaderCellRange(final CellRange pheaderCellRange) {
		this.headerCellRange = pheaderCellRange;
	}

	/**
	 * Gets the body cell range.
	 *
	 * @return the body cell range
	 */
	public final CellRange getBodyCellRange() {
		return bodyCellRange;
	}

	/**
	 * Sets the body cell range.
	 *
	 * @param pbodyCellRange
	 *            the new body cell range
	 */
	public final void setBodyCellRange(final CellRange pbodyCellRange) {
		this.bodyCellRange = pbodyCellRange;
	}

	/**
	 * Gets the footer cell range.
	 *
	 * @return the footer cell range
	 */
	public final CellRange getFooterCellRange() {
		return footerCellRange;
	}

	/**
	 * Sets the footer cell range.
	 *
	 * @param pfooterCellRange
	 *            the new footer cell range
	 */
	public final void setFooterCellRange(final CellRange pfooterCellRange) {
		this.footerCellRange = pfooterCellRange;
	}

	/**
	 * Gets the form body type.
	 *
	 * @return the form body type
	 */
	public final String getFormBodyType() {
		return formBodyType;
	}

	/**
	 * Sets the form body type.
	 *
	 * @param pformBodyType
	 *            the new form body type
	 */
	public final void setFormBodyType(final String pformBodyType) {
		this.formBodyType = pformBodyType;
	}

	/**
	 * Checks if is body allow add rows.
	 *
	 * @return true, if is body allow add rows
	 */
	public final boolean isBodyAllowAddRows() {
		return bodyAllowAddRows;
	}

	/**
	 * Sets the body allow add rows.
	 *
	 * @param pbodyAllowAddRows
	 *            the new body allow add rows
	 */
	public final void setBodyAllowAddRows(final boolean pbodyAllowAddRows) {
		this.bodyAllowAddRows = pbodyAllowAddRows;
	}

	/**
	 * Gets the body initial rows.
	 *
	 * @return the body initial rows
	 */
	public final int getBodyInitialRows() {
		return bodyInitialRows;
	}

	/**
	 * Sets the body initial rows.
	 *
	 * @param pbodyInitialRows
	 *            the new body initial rows
	 */
	public final void setBodyInitialRows(final int pbodyInitialRows) {
		this.bodyInitialRows = pbodyInitialRows;
	}

	/**
	 * Checks if is body populated.
	 *
	 * @return true, if is body populated
	 */
	public final boolean isBodyPopulated() {
		return bodyPopulated;
	}

	/**
	 * Sets the body populated.
	 *
	 * @param pbodyPopulated
	 *            the new body populated
	 */
	public final void setBodyPopulated(final boolean pbodyPopulated) {
		this.bodyPopulated = pbodyPopulated;
	}


	/**
	 * Gets the max row per page.
	 *
	 * @return the max row per page
	 */
	public final int getMaxRowPerPage() {
		return maxRowPerPage;
	}

	/**
	 * Sets the max row per page.
	 *
	 * @param pmaxRowPerPage
	 *            the new max row per page
	 */
	public final void setMaxRowPerPage(final int pmaxRowPerPage) {
		this.maxRowPerPage = pmaxRowPerPage;
	}

	/**
	 * Gets the watch list.
	 *
	 * @return the watch list
	 */
	public final List<Integer> getWatchList() {
		return watchList;
	}

	/**
	 * Sets the watch list.
	 *
	 * @param pwatchList
	 *            the new watch list
	 */
	public final void setWatchList(final List<Integer> pwatchList) {
		this.watchList = pwatchList;
	}

	/**
	 * Gets the shift map.
	 *
	 * @return the shift map
	 */
	public final NavigableMap<String, ConfigRangeAttrs> getShiftMap() {
		return shiftMap;
	}

	/**
	 * Sets the shift map.
	 *
	 * @param pshiftMap
	 *            the shift map
	 */
	public final void setShiftMap(
			final NavigableMap<String, ConfigRangeAttrs> pshiftMap) {
		this.shiftMap = pshiftMap;
	}

	/**
	 * Gets the command index map.
	 *
	 * @return the command index map
	 */
	public final Map<String, Command> getCommandIndexMap() {
		return commandIndexMap;
	}

	/**
	 * Sets the command index map.
	 *
	 * @param pcommandIndexMap
	 *            the command index map
	 */
	public final void setCommandIndexMap(
			final Map<String, Command> pcommandIndexMap) {
		if (pcommandIndexMap instanceof HashMap) {
			this.commandIndexMap = (HashMap<String, Command>) pcommandIndexMap;
		} else {
			this.commandIndexMap = new HashMap<>();
			this.commandIndexMap.putAll(pcommandIndexMap);
		}
	}

	/**
	 * Gets the collection obj name map.
	 *
	 * @return the collection obj name map
	 */
	public final Map<String, String> getCollectionObjNameMap() {
		return collectionObjNameMap;
	}

	/**
	 * Sets the collection obj name map.
	 *
	 * @param pcollectionObjNameMap
	 *            the collection obj name map
	 */
	public final void setCollectionObjNameMap(
			final Map<String, String> pcollectionObjNameMap) {
		this.collectionObjNameMap = pcollectionObjNameMap;
	}

	/**
	 * Gets the form command.
	 *
	 * @return the form command
	 */
	public final FormCommand getFormCommand() {
		if (this.formCommand == null) {
			this.formCommand = new FormCommand();
		}
		return formCommand;
	}

	/**
	 * Sets the form command.
	 *
	 * @param pformCommand
	 *            the new form command
	 */
	public final void setFormCommand(final FormCommand pformCommand) {
		this.formCommand = pformCommand;
	}

	/**
	 * Checks if is hidden.
	 *
	 * @return true, if is hidden
	 */
	public final boolean isHidden() {
		return hidden;
	}

	/**
	 * Sets the hidden.
	 *
	 * @param phidden
	 *            the new hidden
	 */
	public final void setHidden(final boolean phidden) {
		this.hidden = phidden;
	}

	/**
	 * Gets the cached origin formulas.
	 *
	 * @return the cached origin formulas
	 */
	public final Map<Cell, String> getCachedCells() {
		return serialCachedCells.getMap();
	}

	/**
	 * Gets the serial cached cells.
	 *
	 * @return the serialCachedCells
	 */
	public final SerialCellMap getSerialCachedCells() {
		return serialCachedCells;
	}

	/**
	 * Gets the final comment map.
	 *
	 * @return the final comment map
	 */
	public final Map<Cell, String> getFinalCommentMap() {
		return serialFinalCommentMap.getMap();
	}

	/**
	 * Gets the serial final comment map.
	 *
	 * @return the serialFinalCommentMap
	 */
	public final SerialCellMap getSerialFinalCommentMap() {
		return serialFinalCommentMap;
	}

	/**
	 * recover the cell reference to the sheet.
	 * 
	 * @param wb
	 *            workbook.
	 */
	public void recover(final Workbook wb) {
		Sheet sheet = wb.getSheet(this.sheetName);
		this.getSerialCachedCells().recover(sheet);
		this.getSerialFinalCommentMap().recover(sheet);
		this.getFormCommand().recover(sheet);
		if (this.getShiftMap() != null) {
			for (Map.Entry<String, ConfigRangeAttrs> entry : this
					.getShiftMap().entrySet()) {
				entry.getValue().recover(sheet);
			}
		}
		if (this.getCommandIndexMap() != null) {
			for (Map.Entry<String, Command> entry : this
					.getCommandIndexMap().entrySet()) {
				entry.getValue().recover(sheet);
			}
		}

	}

	
	
	/**
     * @return the fixedWidthStyle
     */
    public final boolean isFixedWidthStyle() {
        return fixedWidthStyle;
    }

    /**
     * @param pfixedWidthStyle the fixedWidthStyle to set
     */
    public final void setFixedWidthStyle(final boolean pfixedWidthStyle) {
        this.fixedWidthStyle = pfixedWidthStyle;
    }

    /**
	 * Obtain a human readable representation.
	 * 
	 * @return String Human readable label
	 */
	@Override
	public final String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append("{");
		sb.append("formName = " + formName);
		sb.append(",");
		sb.append("sheetName = " + sheetName);
		sb.append(",");
		sb.append("formHeaderRange = " + formHeaderRange);
		sb.append(",");
		sb.append("formBodyRange = " + formBodyRange);
		sb.append(",");
		sb.append("formFooterRange = " + formFooterRange);
		sb.append(",");
		sb.append("formBodyType = " + formBodyType);
		sb.append(",");
		sb.append("bodyAllowAddRows = " + bodyAllowAddRows);
		sb.append(",");
		sb.append("bodyInitialRows = " + bodyInitialRows);
		sb.append(",");
		sb.append("HeaderCellRange = " + headerCellRange);
		sb.append(",");
		sb.append("BodyCellRange = " + bodyCellRange);
		sb.append(",");
		sb.append("BodyPopulated = " + bodyPopulated);
		sb.append(",");
		sb.append("FooterCellRange = " + footerCellRange);
		sb.append(",");
		sb.append("cellFormAttributes = " + cellFormAttributes);
		sb.append(",");
		sb.append("cachedOriginFormulas = " + serialCachedCells);
		sb.append(",");
		sb.append("finalCommentMap = " + serialFinalCommentMap);
		sb.append(",");
		sb.append("formCommand = " + formCommand);
		sb.append(",");
		sb.append("shiftMap = " + shiftMap);
		sb.append(",");
		sb.append("commandIndexMap = " + commandIndexMap);
		sb.append(",");
		sb.append("collectionObjNameMap = " + collectionObjNameMap);
		sb.append(",");
		sb.append("watchList = " + watchList);
		sb.append("}");
		return sb.toString();
	}

}
 public class FacesCell implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = -6811957210518221928L;
	/** cell web css style. */
	private String style = "";
	/** column css style. */
	private String columnStyle = "";
	/** cell column span default set to 1. */
	private int colspan = 1; //
	/** row span default set to 1. */
	private int rowspan = 1; //
	/** column index in the datatable. */
	private int columnIndex; //
	/** indicate the cell hold invalid data when. */
	private boolean invalid = false; //
	/** hold error message when the cell is invalid. */
	private String errormsg;
	/** data type for input cell. could be text/text area/number etc. */
	private String inputType = "";
	/** indicate the cell hold picture when set to true. */
	private boolean containPic = false;
	/** picture Id for retrieve picture when containPic = true. */
	private String pictureId;
	/** cell web css style. */
	private String pictureStyle = "";
	/** decimalPlaces for number. default is 2. */
	private short decimalPlaces = 2;
	/** symbol. default is null. */
	private String symbol;
	/** symbolPosition. default is prefix */
	private String symbolPosition = "p";
	/** indicate the cell hold chart when set to true. */
	private boolean containChart = false;
	/** chart Id for retrieve picture when containPic = true. */
	private String chartId;
	/** cell web css style. */
	private String chartStyle = "";

	/** The control. */
	private String control = "";

	/** The input attrs. */
	private List<CellFormAttributes> inputAttrs;

	/** The select item attrs. */
	private Map<String, String> selectItemAttrs;

	/** The date pattern. */
	private String datePattern = "";

	/** The has save attr. */
	private boolean hasSaveAttr = false;

	/**
	 * Gets the style.
	 *
	 * @return the style
	 */
	public final String getStyle() {
		return style;
	}

	/**
	 * Sets the style.
	 *
	 * @param pstyle
	 *            the new style
	 */
	public final void setStyle(final String pstyle) {
		this.style = pstyle;
	}

	/**
	 * Gets the valid style.
	 *
	 * @return the valid style
	 */
	public final String getValidStyle() {
		if (invalid) {
			return style + TieConstants.CELL_INVALID_STYLE;
		} else {
			return style;
		}
	}

	/**
	 * Gets the input type.
	 *
	 * @return the input type
	 */
	public final String getInputType() {
		return inputType;
	}

	/**
	 * Sets the input type.
	 *
	 * @param pinputType
	 *            the new input type
	 */
	public final void setInputType(final String pinputType) {
		this.inputType = pinputType;
	}

	/**
	 * Gets the column index.
	 *
	 * @return the column index
	 */
	public final int getColumnIndex() {
		return columnIndex;
	}

	/**
	 * Sets the column index.
	 *
	 * @param pcolumnIndex
	 *            the new column index
	 */
	public final void setColumnIndex(final int pcolumnIndex) {
		this.columnIndex = pcolumnIndex;
	}

	/**
	 * Checks if is invalid.
	 *
	 * @return true, if is invalid
	 */
	public final boolean isInvalid() {
		return invalid;
	}

	/**
	 * Sets the invalid.
	 *
	 * @param pinvalid
	 *            the new invalid
	 */
	public final void setInvalid(final boolean pinvalid) {
		this.invalid = pinvalid;
	}

	/**
	 * Checks if is contain pic.
	 *
	 * @return true, if is contain pic
	 */
	public final boolean isContainPic() {
		return containPic;
	}

	/**
	 * Sets the contain pic.
	 *
	 * @param pcontainPic
	 *            the new contain pic
	 */
	public final void setContainPic(final boolean pcontainPic) {
		this.containPic = pcontainPic;
	}

	/**
	 * Gets the picture style.
	 *
	 * @return the picture style
	 */
	public final String getPictureStyle() {
		return pictureStyle;
	}

	/**
	 * Sets the picture style.
	 *
	 * @param ppictureStyle
	 *            the new picture style
	 */
	public final void setPictureStyle(final String ppictureStyle) {
		this.pictureStyle = ppictureStyle;
	}

	/**
	 * Gets the picture id.
	 *
	 * @return the picture id
	 */
	public final String getPictureId() {
		return pictureId;
	}

	/**
	 * Sets the picture id.
	 *
	 * @param ppictureId
	 *            the new picture id
	 */
	public final void setPictureId(final String ppictureId) {
		this.pictureId = ppictureId;
	}

	/**
	 * Gets the errormsg.
	 *
	 * @return the errormsg
	 */
	public final String getErrormsg() {
		return errormsg;
	}

	/**
	 * Sets the errormsg.
	 *
	 * @param perrormsg
	 *            the new errormsg
	 */
	public final void setErrormsg(final String perrormsg) {
		this.errormsg = perrormsg;
	}

	/**
	 * Gets the colspan.
	 *
	 * @return the colspan
	 */
	public final int getColspan() {
		return colspan;
	}

	/**
	 * Sets the colspan.
	 *
	 * @param pcolspan
	 *            the new colspan
	 */
	public final void setColspan(final int pcolspan) {
		this.colspan = pcolspan;
	}

	/**
	 * Gets the rowspan.
	 *
	 * @return the rowspan
	 */
	public final int getRowspan() {
		return rowspan;
	}

	/**
	 * Sets the rowspan.
	 *
	 * @param prowspan
	 *            the new rowspan
	 */
	public final void setRowspan(final int prowspan) {
		this.rowspan = prowspan;
	}

	/**
	 * Gets the column style.
	 *
	 * @return the column style
	 */
	public final String getColumnStyle() {
		return columnStyle;
	}

	/**
	 * Sets the column style.
	 *
	 * @param pcolumnStyle
	 *            the new column style
	 */
	public final void setColumnStyle(final String pcolumnStyle) {
		this.columnStyle = pcolumnStyle;
	}

	/**
	 * Gets the decimal places.
	 *
	 * @return the decimal places
	 */
	public final short getDecimalPlaces() {
		return decimalPlaces;
	}

	/**
	 * Sets the decimal places.
	 *
	 * @param pdecimalPlaces
	 *            the new decimal places
	 */
	public final void setDecimalPlaces(final short pdecimalPlaces) {
		this.decimalPlaces = pdecimalPlaces;
	}

	/**
	 * Gets the symbol.
	 *
	 * @return the symbol
	 */
	public final String getSymbol() {
		return symbol;
	}

	/**
	 * Sets the symbol.
	 *
	 * @param ppsymbol
	 *            the new symbol
	 */
	public final void setSymbol(final String ppsymbol) {
		this.symbol = ppsymbol;
	}

	/**
	 * Gets the symbol position.
	 *
	 * @return the symbol position
	 */
	public final String getSymbolPosition() {
		return symbolPosition;
	}

	/**
	 * Sets the symbol position.
	 *
	 * @param psymbolPosition
	 *            the new symbol position
	 */
	public final void setSymbolPosition(final String psymbolPosition) {
		this.symbolPosition = psymbolPosition;
	}

	/**
	 * Checks if is contain chart.
	 *
	 * @return true, if is contain chart
	 */
	public final boolean isContainChart() {
		return containChart;
	}

	/**
	 * Sets the contain chart.
	 *
	 * @param pcontainChart
	 *            the new contain chart
	 */
	public final void setContainChart(final boolean pcontainChart) {
		this.containChart = pcontainChart;
	}

	/**
	 * Gets the chart id.
	 *
	 * @return the chart id
	 */
	public final String getChartId() {
		return chartId;
	}

	/**
	 * Sets the chart id.
	 *
	 * @param pchartId
	 *            the new chart id
	 */
	public final void setChartId(final String pchartId) {
		this.chartId = pchartId;
	}

	/**
	 * Gets the chart style.
	 *
	 * @return the chart style
	 */
	public final String getChartStyle() {
		return chartStyle;
	}

	/**
	 * Sets the chart style.
	 *
	 * @param pchartStyle
	 *            the new chart style
	 */
	public final void setChartStyle(final String pchartStyle) {
		this.chartStyle = pchartStyle;
	}

	/**
	 * Gets the input attrs.
	 *
	 * @return the input attrs
	 */
	public final List<CellFormAttributes> getInputAttrs() {
		return inputAttrs;
	}

	/**
	 * Sets the input attrs.
	 *
	 * @param pinputAttrs
	 *            the new input attrs
	 */
	public final void setInputAttrs(
			final List<CellFormAttributes> pinputAttrs) {
		this.inputAttrs = pinputAttrs;
	}

	/**
	 * Gets the select item attrs.
	 *
	 * @return the select item attrs
	 */
	public final Map<String, String> getSelectItemAttrs() {
		return selectItemAttrs;
	}

	/**
	 * Sets the select item attrs.
	 *
	 * @param pselectItemAttrs
	 *            the select item attrs
	 */
	public final void setSelectItemAttrs(
			final Map<String, String> pselectItemAttrs) {
		this.selectItemAttrs = pselectItemAttrs;
	}

	/**
	 * Gets the control.
	 *
	 * @return the control
	 */
	public final String getControl() {
		return control;
	}

	/**
	 * Sets the control.
	 *
	 * @param pcontrol
	 *            the new control
	 */
	public final void setControl(final String pcontrol) {
		this.control = pcontrol;
	}

	/**
	 * Gets the date pattern.
	 *
	 * @return the date pattern
	 */
	public final String getDatePattern() {
		return datePattern;
	}

	/**
	 * Sets the date pattern.
	 *
	 * @param pdatePattern
	 *            the new date pattern
	 */
	public final void setDatePattern(final String pdatePattern) {
		this.datePattern = pdatePattern;

	}

	/**
	 * Checks if is checks for save attr.
	 *
	 * @return true, if is checks for save attr
	 */
	public final boolean isHasSaveAttr() {
		return hasSaveAttr;
	}

	/**
	 * Sets the checks for save attr.
	 *
	 * @param phasSaveAttr
	 *            the new checks for save attr
	 */
	public final void setHasSaveAttr(final boolean phasSaveAttr) {
		this.hasSaveAttr = phasSaveAttr;
	}

}
 public class FacesRow implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = -1576241602026457797L;
	/** row number. */
	private int rowIndex;
	/** whether the row is visible. */
	private boolean rendered;
	/** the row height. */
	private float rowheight;
	/** cells in the row. */
	private List<FacesCell> cells;
	
	/** whether the row allow add/delete. */
	private boolean allowAdd;
	
	/** The origin row index. */
	private int originRowIndex = -1;
	/**
	 * constructor.
	 * @param pRowIndex the row index.
	 */
	public FacesRow(final int pRowIndex) {
		super();
		this.rowIndex = pRowIndex;
	}
	
	/**
	 * Gets the row index.
	 *
	 * @return the row index
	 */
	public final int getRowIndex() {
		return rowIndex;
	}
	
	/**
	 * Sets the row index.
	 *
	 * @param pRowIndex
	 *            the new row index
	 */
	public final void setRowIndex(final int pRowIndex) {
		this.rowIndex = pRowIndex;
	}
	
	/**
	 * Checks if is rendered.
	 *
	 * @return true, if is rendered
	 */
	public final boolean isRendered() {
		return rendered;
	}
	
	/**
	 * Sets the rendered.
	 *
	 * @param pRendered
	 *            the new rendered
	 */
	public final void setRendered(final boolean pRendered) {
		this.rendered = pRendered;
	}
	
	/**
	 * Gets the rowheight.
	 *
	 * @return the rowheight
	 */
	public final float getRowheight() {
		return rowheight;
	}
	
	/**
	 * Sets the rowheight.
	 *
	 * @param pRowheight
	 *            the new rowheight
	 */
	public final void setRowheight(final float pRowheight) {
		this.rowheight = pRowheight;
	}
	
	
	/**
	 * Checks if is allow add.
	 *
	 * @return true, if is allow add
	 */
	public final boolean isAllowAdd() {
		return allowAdd;
	}

	/**
	 * Sets the allow add.
	 *
	 * @param pallowAdd
	 *            the new allow add
	 */
	public final void setAllowAdd(final boolean pallowAdd) {
		this.allowAdd = pallowAdd;
	}

	/**
	 * Gets the cells.
	 *
	 * @return the cells
	 */
	public final List<FacesCell> getCells() {
		return cells;
	}

	/**
	 * Sets the cells.
	 *
	 * @param pCells
	 *            the new cells
	 */
	public final void setCells(final List<FacesCell> pCells) {
		this.cells = pCells;
	}

	/**
	 * Gets the origin row index.
	 *
	 * @return the origin row index
	 */
	public final int getOriginRowIndex() {
		return originRowIndex;
	}

	/**
	 * Sets the origin row index.
	 *
	 * @param poriginRowIndex
	 *            the new origin row index
	 */
	public final void setOriginRowIndex(final int poriginRowIndex) {
		this.originRowIndex = poriginRowIndex;
	}
	
}
 public class HeaderCell implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = 1669739622422183590L;
	
	/** The rowspan. */
	private String rowspan; // cell row span attribute

	/** The colspan. */
	private String colspan; // cell column span attribute

	/** The cell value. */
	private String cellValue; // header text label

	/** The style. */
	private String style; // cell web css style attriubte

	/** The column style. */
	private String columnStyle; // cell web css style attriubte

	/** The rendered. */
	private boolean rendered = true;

	/** The column rendered. */
	private boolean columnRendered = true;

	/**
	 * Constructor.
	 *
	 * @param prowspan
	 *            cell row span attribute
	 * @param pcolspan
	 *            cell column span attribute
	 * @param pstyle
	 *            cell web css style attriubte
	 * @param pcolumnStyle
	 *            the column style
	 * @param pcellValue
	 *            the cell value
	 * @param prendered
	 *            the rendered
	 * @param pcolumnRendered
	 *            the column rendered
	 */
	public HeaderCell(final String prowspan, final String pcolspan,
			final String pstyle, final String pcolumnStyle,
			final String pcellValue, final boolean prendered,
			final boolean pcolumnRendered) {
		super();
		this.rowspan = prowspan;
		this.colspan = pcolspan;
		this.style = pstyle;
		this.columnStyle = pcolumnStyle;
		this.cellValue = pcellValue;
		this.rendered = prendered;
		this.columnRendered = pcolumnRendered;
	}

	/**
	 * Gets the rowspan.
	 *
	 * @return the rowspan
	 */
	public final String getRowspan() {
		return rowspan;
	}

	/**
	 * Sets the rowspan.
	 *
	 * @param prowspan
	 *            the new rowspan
	 */
	public final void setRowspan(final String prowspan) {
		this.rowspan = prowspan;
	}

	/**
	 * Gets the colspan.
	 *
	 * @return the colspan
	 */
	public final String getColspan() {
		return colspan;
	}

	/**
	 * Sets the colspan.
	 *
	 * @param pcolspan
	 *            the new colspan
	 */
	public final void setColspan(final String pcolspan) {
		this.colspan = pcolspan;
	}

	/**
	 * Gets the cell value.
	 *
	 * @return the cell value
	 */
	public final String getCellValue() {
		return cellValue;
	}

	/**
	 * Sets the cell value.
	 *
	 * @param pcellValue
	 *            the new cell value
	 */
	public final void setCellValue(final String pcellValue) {
		this.cellValue = pcellValue;
	}

	/**
	 * Gets the column style.
	 *
	 * @return the column style
	 */
	public final String getColumnStyle() {
		return columnStyle;
	}

	/**
	 * Sets the column style.
	 *
	 * @param pcolumnStyle
	 *            the new column style
	 */
	public final void setColumnStyle(final String pcolumnStyle) {
		this.columnStyle = pcolumnStyle;
	}

	/**
	 * Gets the style.
	 *
	 * @return the style
	 */
	public final String getStyle() {
		return style;
	}

	/**
	 * Sets the style.
	 *
	 * @param pstyle
	 *            the new style
	 */
	public final void setStyle(final String pstyle) {
		this.style = pstyle;
	}

	/**
	 * Checks if is rendered.
	 *
	 * @return true, if is rendered
	 */
	public final boolean isRendered() {
		return rendered;
	}

	/**
	 * Sets the rendered.
	 *
	 * @param prendered
	 *            the new rendered
	 */
	public final void setRendered(final boolean prendered) {
		this.rendered = prendered;
	}

	/**
	 * Checks if is column rendered.
	 *
	 * @return true, if is column rendered
	 */
	public final boolean isColumnRendered() {
		return columnRendered;
	}

	/**
	 * Sets the column rendered.
	 *
	 * @param pcolumnRendered
	 *            the new column rendered
	 */
	public final void setColumnRendered(final boolean pcolumnRendered) {
		this.columnRendered = pcolumnRendered;
	}

	/**
	 * Obtain a human readable representation.
	 * 
	 * @return String Human readable label
	 */
	@Override
	public final String toString() {

		StringBuilder sb = new StringBuilder();
		sb.append("{");
		sb.append("rowspan = " + rowspan);
		sb.append(",");
		sb.append("colspan = " + colspan);
		sb.append(",");
		sb.append("style = " + style);
		sb.append(",");
		sb.append("columnStyle = " + columnStyle);
		sb.append(",");
		sb.append("cellValue = " + cellValue);
		sb.append(",");
		sb.append("rendered = " + rendered);
		sb.append(",");
		sb.append("columnRendered = " + columnRendered);
		sb.append("}");
		return sb.toString();
	}

}
 public class TieCell implements Serializable {

    private static final long serialVersionUID = -6811957210518221929L;

    private String skey;

    private String objectStr;

    private String methodStr;

    private Object contextObject;

    public String getSkey() {
	return skey;
    }

    public void setSkey(String skey) {
	this.skey = skey;
    }

    public String getObjectStr() {
	return objectStr;
    }

    public void setObjectStr(String objectStr) {
	this.objectStr = objectStr;
    }

    public String getMethodStr() {
	return methodStr;
    }

    public void setMethodStr(String methodStr) {
	this.methodStr = methodStr;
    }

    public Object getContextObject() {
	return contextObject;
    }

    public void setContextObject(Object contextObject) {
	this.contextObject = contextObject;
    }

} public final class CellStyleUtility {

	/**
	 * hide constructor.
	 */
	private CellStyleUtility() {
		// not called
	}


	/**
	 * Gets the row style.
	 *
	 * @param wb
	 *            the wb
	 * @param poiCell
	 *            the poi cell
	 * @param inputType
	 *            the input type
	 * @param rowHeight
	 *            the row height
	 * @param rowspan
	 *            the rowspan
	 * @return the row style
	 */
	public static String getRowStyle(final Workbook wb, final Cell poiCell,
			final String inputType, final float rowHeight,
			final int rowspan) {

		CellStyle cellStyle = poiCell.getCellStyle();
		if ((cellStyle != null) && (rowspan == 1)) {
			short fontIdx = cellStyle.getFontIndex();
			Font font = wb.getFontAt(fontIdx);
			float maxHeight = rowHeight;
			if (!inputType.isEmpty()) {
				maxHeight = Math.min(font.getFontHeightInPoints() + 8f,
						rowHeight);
			}
			return "height:" + WebSheetUtility.pointsToPixels(maxHeight)
					+ "px;";
		}
		return "";
	}

	/**
	 * Gets the cell font style.
	 *
	 * @param wb
	 *            the wb
	 * @param poiCell
	 *            the poi cell
	 * @return the cell font style
	 */
	public static String getCellFontStyle(final Workbook wb,
			final Cell poiCell) {

		CellStyle cellStyle = poiCell.getCellStyle();
		StringBuilder webStyle = new StringBuilder();
		if (cellStyle != null) {
			short fontIdx = cellStyle.getFontIndex();
			Font font = wb.getFontAt(fontIdx);
			if (font.getItalic()) {
				webStyle.append("font-style: italic;");
			}
			if (font.getBold()) {
				webStyle.append("font-weight: bold;");
			}
			webStyle.append(
					"font-size: " + font.getFontHeightInPoints() + "pt;");
			String decoration = getCellFontDecoration(font);
			if (decoration.length() > 0) {
				webStyle.append("text-decoration:" + decoration + ";");
			}
			webStyle.append(getCellFontColor(font));

		}
		return webStyle.toString();

	}

	/**
	 * get cell font color.
	 * 
	 * @param font
	 *            font.
	 * @return String font color.
	 */
	private static String getCellFontColor(final Font font) {
		short[] rgbfix = { TieConstants.RGB_MAX, TieConstants.RGB_MAX,
				TieConstants.RGB_MAX };
		if (font instanceof XSSFFont) {
			XSSFColor color = ((XSSFFont) font).getXSSFColor();
			if (color != null) {
				rgbfix = ColorUtility.getTripletFromXSSFColor(color);
			}
		}
		if (rgbfix[0] != TieConstants.RGB_MAX) {
			return "color:rgb(" + FacesUtility.strJoin(rgbfix, ",") + ");";
		}
		return "";
	}

	/**
	 * Get font decoration.
	 * 
	 * @param font
	 *            font.
	 * @return font decoration.
	 */
	private static String getCellFontDecoration(final Font font) {
		StringBuilder decoration = new StringBuilder();
		if (font.getUnderline() != 0) {
			decoration.append(" underline");
		}
		if (font.getStrikeout()) {
			decoration.append(" line-through");
		}
		return decoration.toString();
	}

	/**
	 * Gets the cell style.
	 *
	 * @param wb
	 *            the wb
	 * @param poiCell
	 *            the poi cell
	 * @param inputType
	 *            the input type
	 * @return the cell style
	 */
	public static String getCellStyle(final Workbook wb, final Cell poiCell,
			final String inputType) {

		CellStyle cellStyle = poiCell.getCellStyle();
		StringBuilder webStyle = new StringBuilder();
		if (cellStyle != null) {
			if (!inputType.isEmpty()) {
				webStyle.append(getAlignmentFromCell(poiCell, cellStyle));
				webStyle.append(getVerticalAlignmentFromCell(cellStyle));
			}

			webStyle.append(ColorUtility.getBgColorFromCell(wb, poiCell,
					cellStyle));
		}
		return webStyle.toString();

	}

	/**
	 * Gets the column style.
	 *
	 * @param wb
	 *            the wb
	 * @param fcell
	 *            the fcell
	 * @param poiCell
	 *            the poi cell
	 * @param rowHeight
	 *            the row height
	 * @return the column style
	 */
	public static String getColumnStyle(final Workbook wb,
			final FacesCell fcell, final Cell poiCell,
			final float rowHeight) {

		String inputType = fcell.getInputType();
		CellStyle cellStyle = poiCell.getCellStyle();
		StringBuilder webStyle = new StringBuilder();
		if (cellStyle != null) {
			if (fcell.isContainPic() || fcell.isContainChart()) {
				webStyle.append("vertical-align: top;");
			} else {
				webStyle.append(getAlignmentFromCell(poiCell, cellStyle));
				webStyle.append(getVerticalAlignmentFromCell(cellStyle));
			}
			webStyle.append(ColorUtility.getBgColorFromCell(wb, poiCell,
					cellStyle));
			webStyle.append(getRowStyle(wb, poiCell, inputType, rowHeight,
					fcell.getRowspan()));
		} else {
			webStyle.append(getAlignmentFromCellType(poiCell));
		}
		return webStyle.toString();

	}

	/**
	 * Gets the alignment from cell.
	 *
	 * @param poiCell
	 *            the poi cell
	 * @param cellStyle
	 *            the cell style
	 * @return the alignment from cell
	 */
	private static String getAlignmentFromCell(final Cell poiCell,
			final CellStyle cellStyle) {

		String style = "";
		switch (cellStyle.getAlignmentEnum()) {
		case LEFT:
			style = TieConstants.TEXT_ALIGN_LEFT;
			break;
		case RIGHT:
			style = TieConstants.TEXT_ALIGN_RIGHT;
			break;
		case CENTER:
			style = TieConstants.TEXT_ALIGN_CENTER;
			break;
		case GENERAL:
			style = getAlignmentFromCellType(poiCell);
			break;
		default:
			break;
		}
		return style;
	}

	/**
	 * Gets the vertical alignment from cell.
	 * 
	 * @param cellStyle
	 *            the cell style
	 *
	 * @return the vertical alignment from cell
	 */
	private static String getVerticalAlignmentFromCell(
			final CellStyle cellStyle) {

		String style = "";
		switch (cellStyle.getVerticalAlignmentEnum()) {
		case TOP:
			style = TieConstants.VERTICAL_ALIGN_TOP;
			break;
		case CENTER:
			style = TieConstants.VERTICAL_ALIGN_CENTER;
			break;
		case BOTTOM:
			style = TieConstants.VERTICAL_ALIGN_BOTTOM;
			break;
		default:
			break;
		}
		return style;
	}

	// additionalWidth is to calculate extra width outside spreadsheet for
	// layout purpose
	/**
	 * Calc total width.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @param firstCol
	 *            the first col
	 * @param lastCol
	 *            the last col
	 * @param additionalWidth
	 *            the additional width
	 * @return the int
	 */
	// e.g. lineNumberColumnWidth and addRowColumnWidth
	public static int calcTotalWidth(final Sheet sheet1, final int firstCol,
			final int lastCol, final int additionalWidth) {

		int totalWidth = additionalWidth;
		for (int i = firstCol; i <= lastCol; i++) {
			totalWidth += sheet1.getColumnWidth(i);
		}
		return totalWidth;
	}

	/**
	 * Calc total height.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @param firstRow
	 *            the first row
	 * @param lastRow
	 *            the last row
	 * @param additionalHeight
	 *            the additional height
	 * @return the int
	 */
	public static int calcTotalHeight(final Sheet sheet1,
			final int firstRow, final int lastRow,
			final int additionalHeight) {

		int totalHeight = additionalHeight;
		for (int i = firstRow; i <= lastRow; i++) {

			totalHeight += sheet1.getRow(i).getHeight();
		}
		return totalHeight;
	}

	/**
	 * Setup cell style.
	 *
	 * @param wb
	 *            the wb
	 * @param fcell
	 *            the fcell
	 * @param poiCell
	 *            the poi cell
	 * @param rowHeight
	 *            the row height
	 */
	public static void setupCellStyle(final Workbook wb,
			final FacesCell fcell, final Cell poiCell,
			final float rowHeight) {

		CellStyle cellStyle = poiCell.getCellStyle();
		if ((cellStyle != null) && (!cellStyle.getLocked())) {
			// not locked
			if (fcell.getInputType().isEmpty()) {
				fcell.setInputType(
						CellStyleUtility.getInputTypeFromCellType(poiCell));
			}
			if (fcell.getControl().isEmpty()
					&& (!fcell.getInputType().isEmpty())) {
				fcell.setControl("text");
			}
			setInputStyleBaseOnInputType(fcell, poiCell);

		}
		String webStyle = getCellStyle(wb, poiCell, fcell.getInputType())
				+ getCellFontStyle(wb, poiCell)
				+ getRowStyle(wb, poiCell, fcell.getInputType(), rowHeight,
						fcell.getRowspan());
		fcell.setStyle(webStyle);
		fcell.setColumnStyle(getColumnStyle(wb, fcell, poiCell, rowHeight));
	}

	/**
	 * set up Input Style parameter for input number component which need those
	 * parameters to make it work. e.g. symbol, symbol position, decimal places.
	 * 
	 * @param fcell
	 *            faces cell
	 * @param poiCell
	 *            poi cell
	 */
	static void setInputStyleBaseOnInputType(final FacesCell fcell,
			final Cell poiCell) {

		if ((fcell == null) || fcell.getInputType().isEmpty()) {
			return;
		}

		switch (fcell.getInputType()) {
		case TieConstants.CELL_INPUT_TYPE_PERCENTAGE:
			fcell.setSymbol("%");
			fcell.setSymbolPosition("p");
			fcell.setDecimalPlaces(
					CellStyleUtility.getDecimalPlacesFromFormat(poiCell));
			break;

		case TieConstants.CELL_INPUT_TYPE_INTEGER:
			fcell.setDecimalPlaces((short) 0);
			break;

		case TieConstants.CELL_INPUT_TYPE_DOUBLE:
			fcell.setDecimalPlaces(
					CellStyleUtility.getDecimalPlacesFromFormat(poiCell));
			fcell.setSymbol(CellStyleUtility.getSymbolFromFormat(poiCell));
			fcell.setSymbolPosition(
					CellStyleUtility.getSymbolPositionFromFormat(poiCell));
			break;
		default:
			break;
		}

	}

	/**
	 * Gets the alignment from cell type.
	 *
	 * @param poiCell
	 *            the poi cell
	 * @return the alignment from cell type
	 */
	@SuppressWarnings("deprecation")
	private static String getAlignmentFromCellType(final Cell poiCell) {

		switch (poiCell.getCellTypeEnum()) {
		case FORMULA:
		case NUMERIC:
			return "text-align: right;";
		default:
			return "";
		}

	}

	/**
	 * Gets the input type from cell type.
	 *
	 * @param cell
	 *            the cell
	 * @return the input type from cell type
	 */
	@SuppressWarnings("deprecation")
	private static String getInputTypeFromCellType(final Cell cell) {

		String inputType = TieConstants.CELL_INPUT_TYPE_TEXT;
		if (cell.getCellTypeEnum() == CellType.NUMERIC) {
			inputType = TieConstants.CELL_INPUT_TYPE_DOUBLE;
		}
		CellStyle style = cell.getCellStyle();
		if (style != null) {
			int formatIndex = style.getDataFormat();
			String formatString = style.getDataFormatString();
			if (DateUtil.isADateFormat(formatIndex, formatString)) {
				inputType = TieConstants.CELL_INPUT_TYPE_DATE;
			} else {
				if (isAPercentageCell(formatString)) {
					inputType = TieConstants.CELL_INPUT_TYPE_PERCENTAGE;
				}
			}
		}
		return inputType;
	}

	/**
	 * get decimal places from format string e.g. 0.00 will return 2
	 *
	 * @param cell
	 *            the cell
	 * @return decimal places of the formatted string
	 */
	private static short getDecimalPlacesFromFormat(final Cell cell) {
		CellStyle style = cell.getCellStyle();
		if (style == null) {
			return 0;
		}
		String formatString = style.getDataFormatString();
		if (formatString == null) {
			return 0;
		}
		int ipos = formatString.indexOf('.');
		if (ipos < 0) {
			return 0;
		}
		short counter = 0;
		for (int i = ipos + 1; i < formatString.length(); i++) {
			if (formatString.charAt(i) == '0') {
				counter++;
			} else {
				break;
			}
		}
		return counter;
	}

	/**
	 * get symbol from format string e.g. [$CAD] #,##0.00 will return CAD. While
	 * $#,##0.00 will return $
	 *
	 * @param cell
	 *            the cell
	 * @return symbol of the formatted string
	 */
	private static String getSymbolFromFormat(final Cell cell) {
		CellStyle style = cell.getCellStyle();
		if (style == null) {
			return null;
		}
		String formatString = style.getDataFormatString();
		if (formatString == null) {
			return null;
		}
		if (formatString.indexOf(TieConstants.CELL_ADDR_PRE_FIX) < 0) {
			return null;
		}
		int ipos = formatString.indexOf("[$");
		if (ipos < 0) {
			// only $ found, then return default dollar symbol
			return "$";
		}
		// return specified dollar symbol
		return formatString.substring(ipos + 2,
				formatString.indexOf(']', ipos));
	}

	/**
	 * get symbol position from format string e.g. [$CAD] #,##0.00 will return
	 * p. While #,##0.00 $ will return s
	 *
	 * @param cell
	 *            the cell
	 * @return symbol position of the formatted string
	 */
	private static String getSymbolPositionFromFormat(final Cell cell) {
		CellStyle style = cell.getCellStyle();
		if (style == null) {
			return "p";
		}
		String formatString = style.getDataFormatString();
		if (formatString == null) {
			return "p";
		}
		int symbolpos = formatString.indexOf('$');
		int numberpos = formatString.indexOf('#');
		if (numberpos < 0) {
			numberpos = formatString.indexOf('0');
		}
		if (symbolpos < numberpos) {
			return "p";
		} else {
			return "s";
		}
	}

	/**
	 * Check weather the cell is percentage formatted.
	 *
	 * @param formatString
	 *            the format string
	 * @return true if it's percentage formatted
	 */
	private static boolean isAPercentageCell(final String formatString) {

		if (formatString == null) {
			return false;
		}
		return formatString.indexOf('%') >= 0;

	}

}
 public final class CellUtility {

	/** logger. */
	static final Logger LOG = Logger.getLogger(CellUtility.class.getName());

	/**
	 * Instantiates a new cell helper.
	 */
	private CellUtility() {
		super();
	}

	/**
	 * return cell value with format.
	 * 
	 * @param poiCell
	 *            cell.
	 * @param formulaEvaluator
	 *            formula evaluator.
	 * @param dataFormatter
	 *            data formatter.
	 * @return cell string value with format.
	 */
	@SuppressWarnings("deprecation")
	public static String getCellValueWithFormat(final Cell poiCell, final FormulaEvaluator formulaEvaluator,
			final DataFormatter dataFormatter) {

		if (poiCell == null) {
			return null;
		}

		String result;
		try {
			CellType cellType = poiCell.getCellTypeEnum();
			if (cellType == CellType.FORMULA) {
				cellType = formulaEvaluator.evaluate(poiCell).getCellTypeEnum();
			}
			if (cellType == CellType.ERROR) {
				result = "";
			} else {
				result = dataFormatter.formatCellValue(poiCell, formulaEvaluator);
			}
		} catch (Exception e) {
			LOG.log(Level.SEVERE,
					"Web Form WebFormHelper getCellValue Error row = " + poiCell.getRowIndex() + " column = "
							+ poiCell.getColumnIndex() + " error = " + e.getLocalizedMessage()
							+ "; Change return result to blank",
					e);
			result = "";
		}

		return result;
	}

	/**
	 * get input cell value. none input return blank
	 * 
	 * @param poiCell
	 *            cell.
	 * @return String cell value.
	 */
	@SuppressWarnings("deprecation")
	public static String getCellValueWithoutFormat(final Cell poiCell) {

		if (poiCell == null) {
			return null;
		}

		if (poiCell.getCellTypeEnum() == CellType.FORMULA) {
			return getCellStringValueWithType(poiCell, poiCell.getCachedFormulaResultTypeEnum());
		} else {
			return getCellStringValueWithType(poiCell, poiCell.getCellTypeEnum());
		}
	}

	/**
	 * Get cell value as string but with giving type.
	 * 
	 * @param poiCell
	 *            cell.
	 * @param cellType
	 *            cell type.
	 * @return Sting cell value.
	 */
	private static String getCellStringValueWithType(final Cell poiCell, final CellType cellType) {

		switch (cellType) {
		case BOOLEAN:
			return getCellStringValueWithBooleanType(poiCell);
		case NUMERIC:
			return getCellStringValueWithNumberType(poiCell);
		case STRING:
			return poiCell.getStringCellValue();
		default:
			return "";
		} // switch

	}

	/**
	 * Gets the cell string value with boolean type.
	 *
	 * @param poiCell
	 *            the poi cell
	 * @return the cell string value with boolean type
	 */
	private static String getCellStringValueWithBooleanType(final Cell poiCell) {
		if (poiCell.getBooleanCellValue()) {
			return "Y";
		} else {
			return "N";
		}
	}

	/**
	 * Gets the cell string value with number type.
	 *
	 * @param poiCell
	 *            the poi cell
	 * @return the cell string value with number type
	 */
	private static String getCellStringValueWithNumberType(final Cell poiCell) {
		String result;
		if (DateUtil.isCellDateFormatted(poiCell)) {
			result = poiCell.getDateCellValue().toString();
		} else {
			result = BigDecimal.valueOf(poiCell.getNumericCellValue()).toPlainString();
			// remove .0 from end for int
			if (result.endsWith(".0")) {
				result = result.substring(0, result.length() - 2);
			}
		}
		return result;
	}

	/**
	 * Set cell value with giving String value.
	 * 
	 * @param c
	 *            cell.
	 * @param value
	 *            giving value.
	 * @return cell.
	 */
	@SuppressWarnings("deprecation")
	public static Cell setCellValue(final Cell c, final String value) {

		try {
			if (value.length() == 0) {
				c.setCellType(CellType.BLANK);
			} else if (WebSheetUtility.isNumeric(value)) {
				setCellValueNumber(c, value);
			} else if (WebSheetUtility.isDate(value)) {
				setCellValueDate(c, value);
			} else if (c.getCellTypeEnum() == CellType.BOOLEAN) {
				setCellValueBoolean(c, value);
			} else {
				setCellValueString(c, value);
			}
		} catch (Exception e) {
			LOG.log(Level.SEVERE, " error in setCellValue of CellUtility = " + e.getLocalizedMessage(), e);
			setCellValueString(c, value);
		}
		return c;
	}

	/**
	 * Sets the cell value string.
	 *
	 * @param c
	 *            the c
	 * @param value
	 *            the value
	 */
	private static void setCellValueString(final Cell c, final String value) {
		c.setCellType(CellType.STRING);
		c.setCellValue(value);
	}

	/**
	 * Sets the cell value boolean.
	 *
	 * @param c
	 *            the c
	 * @param value
	 *            the value
	 */
	private static void setCellValueBoolean(final Cell c, final String value) {
		if ("Y".equalsIgnoreCase(value) || "Yes".equalsIgnoreCase(value) || "True".equalsIgnoreCase(value)) {
			c.setCellValue(true);
		} else {
			c.setCellValue(false);
		}
	}

	/**
	 * Sets the cell value date.
	 *
	 * @param c
	 *            the c
	 * @param value
	 *            the value
	 */
	private static void setCellValueDate(final Cell c, final String value) {
		String date = WebSheetUtility.parseDate(value);
		setCellValueString(c, date);
	}

	/**
	 * Sets the cell value number.
	 *
	 * @param c
	 *            the c
	 * @param value
	 *            the value
	 */
	private static void setCellValueNumber(final Cell c, final String value) {
		double val = Double.parseDouble(value.replace(Character.toString(','), ""));
		c.setCellType(CellType.NUMERIC);
		c.setCellValue(val);
	}

	/**
	 * Copy rows.
	 *
	 * @param srcSheet
	 *            the src sheet
	 * @param destSheet
	 *            the dest sheet
	 * @param srcRowStart
	 *            the src row start
	 * @param srcRowEnd
	 *            the src row end
	 * @param destRow
	 *            the dest row
	 * @param checkLock
	 *            the check lock
	 * @param setHiddenColumn
	 *            the set hidden column
	 */
	public static void copyRows(final Sheet srcSheet, final Sheet destSheet, final int srcRowStart, final int srcRowEnd,
			final int destRow, final boolean checkLock, final boolean setHiddenColumn) {

		int length = srcRowEnd - srcRowStart + 1;
		if (length <= 0) {
			return;
		}
		destSheet.shiftRows(destRow, destSheet.getLastRowNum(), length, true, false);
		for (int i = 0; i < length; i++) {
			copySingleRow(srcSheet, destSheet, srcRowStart + i, destRow + i, checkLock, setHiddenColumn);
		}
		// If there are are any merged regions in the source row, copy to new
		// row
		for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) {
			CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i);
			if ((cellRangeAddress.getFirstRow() >= srcRowStart) && (cellRangeAddress.getLastRow() <= srcRowEnd)) {
				int targetRowFrom = cellRangeAddress.getFirstRow() - srcRowStart + destRow;
				int targetRowTo = cellRangeAddress.getLastRow() - srcRowStart + destRow;

				CellRangeAddress newCellRangeAddress = new CellRangeAddress(targetRowFrom, targetRowTo,
						cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
				destSheet.addMergedRegion(newCellRangeAddress);
			}
		}
	}

	/**
	 * Copy single row.
	 *
	 * @param srcSheet
	 *            the src sheet
	 * @param destSheet
	 *            the dest sheet
	 * @param sourceRowNum
	 *            the source row num
	 * @param destinationRowNum
	 *            the destination row num
	 * @param checkLock
	 *            the check lock
	 * @param setHiddenColumn
	 *            the set hidden column
	 */
	private static void copySingleRow(final Sheet srcSheet, final Sheet destSheet, final int sourceRowNum,
			final int destinationRowNum, final boolean checkLock, final boolean setHiddenColumn) {
		// Get the source / new row
		Row newRow = destSheet.getRow(destinationRowNum);
		Row sourceRow = srcSheet.getRow(sourceRowNum);

		if (newRow == null) {
			newRow = destSheet.createRow(destinationRowNum);
		}
		newRow.setHeight(sourceRow.getHeight());
		// Loop through source columns to add to new row
		for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
			// Grab a copy of the old/new cell
			copyCell(destSheet, sourceRow, newRow, i, checkLock);
		}
		if (setHiddenColumn) {
			ConfigurationUtility.setOriginalRowNumInHiddenColumn(newRow, sourceRow.getRowNum());
		}
		return;

	}

	/**
	 * Copy cell.
	 *
	 * @param destSheet
	 *            the dest sheet
	 * @param sourceRow
	 *            the source row
	 * @param newRow
	 *            the new row
	 * @param cellIndex
	 *            the cell index
	 * @param checkLock
	 *            the check lock
	 * @return the int
	 */
	public static Cell copyCell(final Sheet destSheet, final Row sourceRow, final Row newRow, final int cellIndex,
			final boolean checkLock) {
		// If the old cell is null jump to next cell
		Cell sourceCell = sourceRow.getCell(cellIndex);
		if (sourceCell == null) {
			return null;
		}
		// If source cell is dest cell refresh it
		boolean refreshCell = false;		
		if (sourceRow.equals(newRow)&&(sourceCell.getColumnIndex()==cellIndex)) {
			sourceRow.removeCell(sourceCell);
			refreshCell = true;
		}
		Cell newCell = newRow.createCell(cellIndex);
		try {
			if (!refreshCell && (sourceCell.getCellComment() != null)) {
				// If there is a cell comment, copy
				cloneComment(sourceCell, newCell);
			}
			copyCellSetStyle(destSheet, sourceCell, newCell);
			copyCellSetValue(sourceCell, newCell, checkLock);
		} catch (Exception ex) {
			LOG.log(Level.SEVERE, "copy cell set error = " + ex.getLocalizedMessage(), ex);
		}

		return newCell;
	}

	/**
	 * set cell value.
	 * 
	 * @param sourceCell
	 *            source cell.
	 * @param newCell
	 *            new cell.
	 * @param checkLock
	 *            check lock flag.
	 */
	@SuppressWarnings("deprecation")
	private static void copyCellSetValue(final Cell sourceCell, final Cell newCell, final boolean checkLock) {

		CellStyle newCellStyle = newCell.getCellStyle();
		String name = sourceCell.getCellTypeEnum().toString();
		CellValueType e = Enum.valueOf(CellValueType.class, name);
		e.setCellValue(newCell, sourceCell, checkLock, newCellStyle);
	}

	/**
	 * The Enum CellValueType.
	 */
	public enum CellValueType {

		/** The string. */
		STRING {
			@Override
			public void setCellValue(final Cell newCell, final Cell sourceCell, final boolean checkLock,
					final CellStyle newCellStyle) {
				if ((!checkLock) || newCellStyle.getLocked()) {
					newCell.setCellValue(sourceCell.getRichStringCellValue());
				}
			}
		},

		/** The boolean. */
		BOOLEAN {
			@Override
			public void setCellValue(final Cell newCell, final Cell sourceCell, final boolean checkLock,
					final CellStyle newCellStyle) {
				if ((!checkLock) || newCellStyle.getLocked()) {
					newCell.setCellValue(sourceCell.getBooleanCellValue());
				}
			}
		},

		/** The numeric. */
		NUMERIC {
			@Override
			public void setCellValue(final Cell newCell, final Cell sourceCell, final boolean checkLock,
					final CellStyle newCellStyle) {
				if ((!checkLock) || newCellStyle.getLocked()) {
					newCell.setCellValue(sourceCell.getNumericCellValue());
				}
			}
		},

		/** The formula. */
		FORMULA {
			@Override
			public void setCellValue(final Cell newCell, final Cell sourceCell, final boolean checkLock,
					final CellStyle newCellStyle) {
				newCell.setCellFormula(sourceCell.getCellFormula());
			}
		},

		/** The error. */
		ERROR {
			@Override
			public void setCellValue(final Cell newCell, final Cell sourceCell, final boolean checkLock,
					final CellStyle newCellStyle) {
				if ((!checkLock) || newCellStyle.getLocked()) {
					newCell.setCellErrorValue(sourceCell.getErrorCellValue());
				}
			}
		},

		/** The blank. */
		BLANK {
			@Override
			public void setCellValue(final Cell newCell, final Cell sourceCell, final boolean checkLock,
					final CellStyle newCellStyle) {
				newCell.setCellType(CellType.BLANK);
			}
		};
		/**
		 * Sets the cell value.
		 *
		 * @param newCell
		 *            the new cell
		 * @param sourceCell
		 *            the source cell
		 * @param checkLock
		 *            the check lock
		 * @param newCellStyle
		 *            the new cell style
		 */
		public abstract void setCellValue(Cell newCell, Cell sourceCell, boolean checkLock, CellStyle newCellStyle);

	}

	/**
	 * set up cell style.
	 * 
	 * @param destSheet
	 *            dest sheet.
	 * @param sourceCell
	 *            source cell.
	 * @param newCell
	 *            new cell.
	 */
	@SuppressWarnings("deprecation")
	private static void copyCellSetStyle(final Sheet destSheet, final Cell sourceCell, final Cell newCell) {
		CellStyle newCellStyle = getCellStyleFromSourceCell(destSheet, sourceCell);
		newCell.setCellStyle(newCellStyle);

		// If there is a cell hyperlink, copy
		if (sourceCell.getHyperlink() != null) {
			newCell.setHyperlink(sourceCell.getHyperlink());
		}

		// Set the cell data type
		newCell.setCellType(sourceCell.getCellTypeEnum());
	}

	/**
	 * clone existing comments into new cell comment.
	 * 
	 * @param sourceCell
	 *            source cell.
	 * @param newCell
	 *            target cell.
	 */

	public static void cloneComment(final Cell sourceCell, final Cell newCell) {

		XSSFSheet sheet = (XSSFSheet) newCell.getSheet();
		CreationHelper factory = sheet.getWorkbook().getCreationHelper();
		Drawing drawing = sheet.createDrawingPatriarch();
		XSSFComment sourceComment = (XSSFComment) sourceCell.getCellComment();
		// Below code are from POI busy manual.
		// When the comment box is visible, have it show in a 1x3 space
		ClientAnchor anchor = createCommentAnchor(newCell, factory);
		// Create the comment and set the text+author
		Comment comment = drawing.createCellComment(anchor);
		RichTextString str = factory.createRichTextString(sourceComment.getString().toString());
		comment.setString(str);
		comment.setAuthor(sourceComment.getAuthor());
		// Assign the comment to the cell
		newCell.setCellComment(comment);
		comment.setColumn(newCell.getColumnIndex());
		comment.setRow(newCell.getRowIndex());
		// As POI doesn't has well support for comments,
		// So we have to use low level api to match the comments.
		matchCommentSettings(newCell, sourceCell);
	}

	/**
	 * Creates the comment anchor.
	 *
	 * @param newCell the new cell
	 * @param factory the factory
	 * @return the client anchor
	 */
	private static ClientAnchor createCommentAnchor(final Cell newCell, CreationHelper factory) {
		ClientAnchor anchor = factory.createClientAnchor();
		anchor.setCol1(newCell.getColumnIndex());
		anchor.setCol2(newCell.getColumnIndex() + 1);
		anchor.setRow1(newCell.getRowIndex());
		anchor.setRow2(newCell.getRowIndex() + 3);
		return anchor;
	}

	/**
	 * Creates the or insert comment.
	 *
	 * @param cell the cell
	 * @param commentStr the comment str
	 */
	public static void createOrInsertComment(final Cell cell, final String commentStr) {

		XSSFSheet sheet = (XSSFSheet) cell.getSheet();
		CreationHelper factory = sheet.getWorkbook().getCreationHelper();
		Drawing drawing = sheet.createDrawingPatriarch();
		Comment comment = cell.getCellComment();
		String originStr = "";
		if (comment == null) { 
			// Below code are from POI busy manual.
			// When the comment box is visible, have it show in a 1x3 space
			ClientAnchor anchor = createCommentAnchor(cell, factory);
			// Create the comment and set the text+author
			comment = drawing.createCellComment(anchor);
		} else {
			originStr = comment.getString().getString()+"\n";
		}
		originStr +=  commentStr;
		RichTextString str = factory.createRichTextString(originStr);
		comment.setString(str);
		comment.setAuthor("");
		// Assign the comment to the cell
		cell.setCellComment(comment);
		comment.setColumn(cell.getColumnIndex());
		comment.setRow(cell.getRowIndex());
	}	
	
	/**
	 * Use low level API to match the comments setting.
	 * 
	 * @param newCell
	 *            target cell.
	 * @param sourceCell
	 *            source cell.
	 */
	private static void matchCommentSettings(final Cell newCell, final Cell sourceCell) {
		try {
			XSSFVMLDrawing sourceVml = getVmlDrawingFromCell(sourceCell);
			XSSFVMLDrawing targetVml = getVmlDrawingFromCell(newCell);
			CTShape sourceCtShape = getCtShapeFromVml(sourceCell, sourceVml);
			CTShape targetCtShape = getCtShapeFromVml(newCell, targetVml);
			targetCtShape.setType(sourceCtShape.getType());
			CTClientData sourceClientData = sourceCtShape.getClientDataArray(0);
			CTClientData targetClientData = targetCtShape.getClientDataArray(0);
			String[] anchorArray = sourceClientData.getAnchorList().get(0).split(",");
			int shiftRows = newCell.getRowIndex() - sourceCell.getRowIndex();
			/*
			 * AchorArray mappings: 0->col1 1->dx1 2->row1 3->dy1 4->col2 5->dx2 6-> row2
			 * 7->dy2
			 */
			anchorArray[2] = Integer.toString(Integer.parseInt(anchorArray[2].trim()) + shiftRows);
			anchorArray[6] = Integer.toString(Integer.parseInt(anchorArray[6].trim()) + shiftRows);
			targetClientData.getAnchorList().set(0, FacesUtility.strJoin(anchorArray, ","));
		} catch (Exception e) {
			LOG.log(Level.SEVERE, "matchCommentSettings error = " + e.getLocalizedMessage(), e);
		}
	}

	/**
	 * Find vmldrawing part according to cell.
	 * 
	 * @param cell
	 *            cell.
	 * @return vmldrawing.
	 */
	private static XSSFVMLDrawing getVmlDrawingFromCell(final Cell cell) {
		XSSFSheet sourceSheet = (XSSFSheet) cell.getSheet();
		for (POIXMLDocumentPart sourcePart : sourceSheet.getRelations()) {
			if ((sourcePart != null) && (sourcePart instanceof XSSFVMLDrawing)) {
				return (XSSFVMLDrawing) sourcePart;
			}
		}
		return null;
	}

	/**
	 * Find CtShape from vml object. This class use reflection to invoke the
	 * protected method in POI.
	 *
	 * @param sourceCell            cell.
	 * @param sourceVml            vml.
	 * @return ctShape.
	 * @throws ReflectiveOperationException the reflective operation exception
	 * @throws SecurityException the security exception
	 */
	@SuppressWarnings("rawtypes")
	private static CTShape getCtShapeFromVml(final Cell sourceCell, XSSFVMLDrawing sourceVml)
			throws ReflectiveOperationException {
		Method findshape;
		// int parameter
		Class[] paramInt = new Class[2];
		paramInt[0] = Integer.TYPE;
		paramInt[1] = Integer.TYPE;
		findshape = sourceVml.getClass().getDeclaredMethod("findCommentShape", paramInt);
		findshape.setAccessible(true);
		return (CTShape) findshape.invoke(sourceVml, sourceCell.getRowIndex(), sourceCell.getColumnIndex());
	}

	/**
	 * create cell style from source cell.
	 * 
	 * @param destSheet
	 *            dest sheet.
	 * @param sourceCell
	 *            source cell.
	 * @return cell style.
	 */
	private static CellStyle getCellStyleFromSourceCell(final Sheet destSheet, final Cell sourceCell) {
		Workbook wb = destSheet.getWorkbook();
		// Copy style from old cell and apply to new cell
		CellStyle newCellStyle = wb.createCellStyle();
		newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
		return newCellStyle;
	}

	/**
	 * return cell index number key. e.g. $0$0 for A1 cell.
	 * 
	 * @param cell
	 *            input cell.
	 * @return string.
	 */
	public static String getCellIndexNumberKey(final Cell cell) {
		if (cell != null) {
			return TieConstants.CELL_ADDR_PRE_FIX + cell.getColumnIndex() + TieConstants.CELL_ADDR_PRE_FIX
					+ cell.getRowIndex();
		}
		return null;
	}

	/**
	 * return cell index number key. e.g. $0$0 for A1 cell.
	 * 
	 * @param columnIndex
	 *            column index.
	 * @param rowIndex
	 *            row index.
	 * @return string.
	 */
	public static String getCellIndexNumberKey(final int columnIndex, final int rowIndex) {
		return TieConstants.CELL_ADDR_PRE_FIX + columnIndex + TieConstants.CELL_ADDR_PRE_FIX + rowIndex;
	}

	/**
	 * return cell index key with column letter and row index. e.g. $A$0 for A1
	 * cell.
	 * 
	 * @param columnLetter
	 *            column letter.
	 * @param rowIndex
	 *            row index.
	 * @return String.
	 */
	public static String getCellIndexLetterKey(final String columnLetter, final int rowIndex) {
		return TieConstants.CELL_ADDR_PRE_FIX + columnLetter + TieConstants.CELL_ADDR_PRE_FIX + rowIndex;
	}

	/**
	 * return cell index key with column and row index. e.g. $A$0 for A1 cell.
	 * 
	 * @param columnIndex
	 *            column index.
	 * @param rowIndex
	 *            row index.
	 * @return key.
	 */

	public static String getCellIndexLetterKey(final int columnIndex, final int rowIndex) {
		return TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(columnIndex)
				+ TieConstants.CELL_ADDR_PRE_FIX + rowIndex;
	}

	/**
	 * Convert cell.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @param fcell
	 *            the fcell
	 * @param poiCell
	 *            the poi cell
	 * @param cellRangeMap
	 *            the cell range map
	 * @param originRowIndex
	 *            the origin row index
	 * @param cellAttributesMap
	 *            the cell attributes map
	 * @param saveAttrs
	 *            the save attrs
	 */
	// set up facesCell's attribute from poiCell and others.
	public static void convertCell(final SheetConfiguration sheetConfig, final FacesCell fcell, final Cell poiCell,
			final Map<String, CellRangeAddress> cellRangeMap, final int originRowIndex,
			final CellAttributesMap cellAttributesMap, final String saveAttrs) {
		CellRangeAddress caddress;
		String key = getCellIndexNumberKey(poiCell);
		caddress = cellRangeMap.get(key);
		if (caddress != null) {
			// has col or row span
			fcell.setColspan(caddress.getLastColumn() - caddress.getFirstColumn() + 1);
			fcell.setRowspan(caddress.getLastRow() - caddress.getFirstRow() + 1);
		}

		CellControlsUtility.setupControlAttributes(originRowIndex, fcell, poiCell, sheetConfig, cellAttributesMap);
		fcell.setHasSaveAttr(SaveAttrsUtility.isHasSaveAttr(poiCell, saveAttrs));

	}

	/**
	 * Gets the row col from component attributes.
	 *
	 * @param target
	 *            the target
	 * @return the row col from component attributes
	 */
	public static int[] getRowColFromComponentAttributes(final UIComponent target) {

		int rowIndex = (Integer) target.getAttributes().get("data-row");
		int colIndex = (Integer) target.getAttributes().get("data-column");
		int[] list = new int[2];
		list[0] = rowIndex;
		list[1] = colIndex;
		return list;
	}

	/**
	 * Gets the inits the rows from config.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @return the inits the rows from config
	 */
	public static int getInitRowsFromConfig(final SheetConfiguration sheetConfig) {
		int initRows = 1;
		if ("Repeat".equalsIgnoreCase(sheetConfig.getFormBodyType())) {
			initRows = sheetConfig.getBodyInitialRows();
			if (initRows < 1) {
				initRows = 1;
			}
		}
		return initRows;
	}

	/**
	 * Gets the body bottom from config.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @return the body bottom from config
	 */
	public static int getBodyBottomFromConfig(final SheetConfiguration sheetConfig) {

		return sheetConfig.getBodyCellRange().getBottomRow();

	}

	/**
	 * Gets the faces row from body row.
	 * 
	 * @param row
	 *            the row
	 * @param bodyRows
	 *            the body rows
	 * @param topRow
	 *            the top row
	 * @return faces row
	 */
	public static FacesRow getFacesRowFromBodyRow(final int row, final List<FacesRow> bodyRows, final int topRow) {
		FacesRow frow = null;

		try {
			frow = bodyRows.get(row - topRow);

		} catch (Exception e) {
			LOG.log(Level.SEVERE, "getFacesRowFromBodyRow Error row = " + row + "top row = " + topRow + " ; error = "
					+ e.getLocalizedMessage(), e);
		}
		return frow;
	}

	/**
	 * Gets the faces cell from body row.
	 *
	 * @param row
	 *            the row
	 * @param col
	 *            the col
	 * @param bodyRows
	 *            the body rows
	 * @param topRow
	 *            the top row
	 * @param leftCol
	 *            the left col
	 * @return the faces cell from body row
	 */
	public static FacesCell getFacesCellFromBodyRow(final int row, final int col, final List<FacesRow> bodyRows,
			final int topRow, final int leftCol) {
		FacesCell cell = null;

		try {
			cell = bodyRows.get(row - topRow).getCells().get(col - leftCol);

		} catch (Exception e) {
			LOG.log(Level.SEVERE, "getFacesCellFromBodyRow Error row = " + row + " col = " + col + "top row = " + topRow
					+ " leftCol = " + leftCol + " ; error = " + e.getLocalizedMessage(), e);
		}
		return cell;
	}

	/**
	 * Gets the poi cell with row col from current page.
	 *
	 * @param rowIndex
	 *            the row index
	 * @param colIndex
	 *            the col index
	 * @param wb
	 *            workbook.
	 * @return the poi cell with row col from current page
	 */
	public static Cell getPoiCellWithRowColFromCurrentPage(final int rowIndex, final int colIndex, final Workbook wb) {
		if (wb != null) {
			return getPoiCellFromSheet(rowIndex, colIndex, wb.getSheetAt(wb.getActiveSheetIndex()));
		}
		return null;
	}

	/**
	 * Gets the poi cell from sheet.
	 *
	 * @param rowIndex
	 *            the row index
	 * @param colIndex
	 *            the col index
	 * @param sheet1
	 *            the sheet 1
	 * @return the poi cell from sheet
	 */
	public static Cell getPoiCellFromSheet(final int rowIndex, final int colIndex, final Sheet sheet1) {
		if ((sheet1 != null) && (sheet1.getRow(rowIndex) != null)) {
			return sheet1.getRow(rowIndex).getCell(colIndex);
		}
		return null;
	}

	/**
	 * Gets the skey from poi cell.
	 *
	 * @param poiCell the poi cell
	 * @return the skey from poi cell
	 */
	public static String getSkeyFromPoiCell(final Cell poiCell) {
		return poiCell.getSheet().getSheetName() + "!"
				+ CellUtility.getCellIndexNumberKey(poiCell.getColumnIndex(), poiCell.getRowIndex());
	}

	/**
	 * Gets the or add tie cell in map.
	 *
	 * @param poiCell the poi cell
	 * @param tieCells the tie cells
	 * @return the or add tie cell in map
	 */
	public static TieCell getOrAddTieCellInMap(final Cell poiCell, HashMap<String, TieCell> tieCells) {
		String skey = CellUtility.getSkeyFromPoiCell(poiCell);
		TieCell tieCell = tieCells.get(skey);
		if (tieCell == null) {
			tieCell = new TieCell();
			tieCell.setSkey(skey);
			tieCells.put(skey, tieCell);
		}
		return tieCell;
	}

}
 public final class CommandUtility {

	/** logger. */
	private static final Logger LOG = Logger
			.getLogger(CommandUtility.class.getName());

	/**
	 * hide constructor.
	 */
	private CommandUtility() {
		// not called
	}

	/**
	 * Adds the row.
	 *
	 * @param configBuildRef
	 *            the config build ref
	 * @param rowIndex
	 *            the row index
	 * @param dataContext
	 *            the data context
	 * @return the int
	 */
	@SuppressWarnings({ "rawtypes" })
	public static int addRow(final ConfigBuildRef configBuildRef,
			final int rowIndex, final Map<String, Object> dataContext) {

		// replace the lastCollection.
		// since here's add one row.
		// Then we should insert one empty object in the list.
		// The collection must be a list to support add/delete function.
		// and the object must support empty constructor.

		String fullName = ConfigurationUtility.getFullNameFromRow(
				configBuildRef.getSheet().getRow(rowIndex));
		String[] parts = fullName.split(":");
		configBuildRef.getCellHelper().restoreDataContext(fullName);
		CollectionObject collect = configBuildRef.getCellHelper()
				.getLastCollect(fullName);

		Collection lastCollection = collect.getLastCollection();
		int lastCollectionIndex = collect.getLastCollectionIndex();
		EachCommand eachCommand = collect.getEachCommand();
		if (lastCollectionIndex < 0) {
			// no each command in the loop.
			throw new AddRowException("No each command found.");
		}
		String unitFullName = CommandUtility.insertEmptyObjectInContext(
				fullName, lastCollection, eachCommand, lastCollectionIndex,
				dataContext);
		RowsMapping unitRowsMapping = new RowsMapping();
		ConfigRangeAttrs savedRangeAttrs = configBuildRef.getShiftMap()
				.get(fullName);
		int insertPosition = savedRangeAttrs.getFirstRowRef().getRowIndex()
				+ savedRangeAttrs.getFinalLength();
		configBuildRef.setInsertPosition(insertPosition);
		CommandUtility.insertEachTemplate(eachCommand.getConfigRange(),
				configBuildRef, lastCollectionIndex + 1, insertPosition,
				unitRowsMapping);
		ConfigRange currentRange = ConfigurationUtility.buildCurrentRange(
				eachCommand.getConfigRange(), configBuildRef.getSheet(),
				insertPosition);
		List<RowsMapping> currentRowsMappingList = ConfigurationUtility
				.findParentRowsMappingFromShiftMap(parts,
						configBuildRef.getShiftMap());
		currentRowsMappingList.add(unitRowsMapping);
		currentRange.getAttrs().setAllowAdd(true);
		configBuildRef.setBodyAllowAdd(true);
		// reverse order of changeMap.
		Map<String, String> changeMap = new TreeMap<>(
				Collections.reverseOrder());
		ConfigurationUtility.changeIndexNumberInHiddenColumn(configBuildRef,
				currentRange.getAttrs().getLastRowPlusRef().getRowIndex(),
				fullName, changeMap, 1);
		ConfigurationUtility.changeIndexNumberInShiftMap(
				configBuildRef.getShiftMap(), changeMap);
		configBuildRef.putShiftAttrs(unitFullName, currentRange.getAttrs(),
				unitRowsMapping);
		int length = currentRange.buildAt(unitFullName, configBuildRef,
				insertPosition, dataContext, currentRowsMappingList);
		currentRange.getAttrs().setFinalLength(length);

		ConfigurationUtility.reBuildUpperLevelFormula(configBuildRef,
				fullName);
		ConfigurationUtility.changeUpperLevelFinalLength(
				configBuildRef.getShiftMap(), fullName, length);
		currentRowsMappingList.remove(unitRowsMapping);
		dataContext.remove(eachCommand.getVar());

		return length;

	}

	/**
	 * Delete row.
	 *
	 * @param configBuildRef
	 *            the config build ref
	 * @param rowIndex
	 *            the row index
	 * @param dataContext
	 *            the data context
	 * @param sheetConfig
	 *            the sheet config
	 * @param bodyRows
	 *            the body rows
	 * @return the int
	 * @throws DeleteRowException
	 *             the delete row exception
	 */
	@SuppressWarnings({ "rawtypes" })
	public static int deleteRow(final ConfigBuildRef configBuildRef,
			final int rowIndex, final Map<String, Object> dataContext,
			final SheetConfiguration sheetConfig,
			final List<FacesRow> bodyRows) {

		String fullName = ConfigurationUtility.getFullNameFromRow(
				configBuildRef.getSheet().getRow(rowIndex));

		configBuildRef.getCellHelper().restoreDataContext(fullName);
		CollectionObject collect = configBuildRef.getCellHelper()
				.getLastCollect(fullName);

		Collection lastCollection = collect.getLastCollection();
		int lastCollectionIndex = collect.getLastCollectionIndex();
		EachCommand eachCommand = collect.getEachCommand();
		if (lastCollectionIndex < 0) {
			// no each command in the loop.
			throw new DeleteRowException("No each command found.");
		}
		if (lastCollection.size() <= 1) {
			// this is the last record and no parent left.
			throw new DeleteRowException(
					"Cannot delete the last record in the group.");
		}

		CommandUtility.deleteObjectInContext(lastCollection, eachCommand,
				lastCollectionIndex, dataContext);

		// find range from shiftmap.
		ConfigRangeAttrs currentRangeAttrs = configBuildRef.getShiftMap()
				.get(fullName);
		if (currentRangeAttrs == null) {
			throw new DeleteRowException("Cannot find delete range.");

		}

		// The lastRowRef is wrong in rangeAttrs. So use length to recalc it.
		int startRow = currentRangeAttrs.getFirstRowIndex();
		int length = currentRangeAttrs.getFinalLength();
		int endRow = startRow + length - 1;

		List<String> removeFullNameList = findRemoveFullNameList(
				configBuildRef.getSheet(), startRow, endRow);
		// remove range from shiftmap.
		removeRangesFromShiftMap(configBuildRef.getShiftMap(),
				removeFullNameList);
		// 1. remove ranged rows from sheet
		String var = eachCommand.getVar();
		CommandUtility.removeRowsInSheet(configBuildRef.getSheet(),
				startRow, endRow, configBuildRef.getCachedCells());
		// 2. reset FacesRow row index.
		CommandUtility.removeRowsInBody(sheetConfig, bodyRows, startRow,
				endRow);
		// 3. decrease index number in hidden column
		Map<String, String> changeMap = new TreeMap<>();
		ConfigurationUtility.changeIndexNumberInHiddenColumn(configBuildRef,
				startRow, fullName, changeMap, -1);
		// 4. decrease index number in shift map
		ConfigurationUtility.changeIndexNumberInShiftMap(
				configBuildRef.getShiftMap(), changeMap);
		// 5. rebuild upper level formula
		ConfigurationUtility.reBuildUpperLevelFormula(configBuildRef,
				fullName);
		// 6. decrease upper level final length
		ConfigurationUtility.changeUpperLevelFinalLength(
				configBuildRef.getShiftMap(), fullName, -length);

		dataContext.remove(var);

		return length;

	}

	/**
	 * Removes the ranges from shift map.
	 *
	 * @param shiftMap
	 *            the shift map
	 * @param removeFullNameList
	 *            the remove full name list
	 */
	private static void removeRangesFromShiftMap(
			final NavigableMap<String, ConfigRangeAttrs> shiftMap,
			final List<String> removeFullNameList) {
		for (String fname : removeFullNameList) {
			shiftMap.remove(fname);
		}

	}

	/**
	 * Find remove full name list.
	 *
	 * @param sheet
	 *            the sheet
	 * @param startRow
	 *            the start row
	 * @param endRow
	 *            the end row
	 * @return the list
	 */
	private static List<String> findRemoveFullNameList(final Sheet sheet,
			final int startRow, final int endRow) {

		List<String> list = new ArrayList<>();

		for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++) {
			String fullName = ConfigurationUtility
					.getFullNameFromRow(sheet.getRow(rowIndex));
			if (!list.contains(fullName)) {
				list.add(fullName);
			}
		}

		return list;
	}

	/**
	 * Gets the each command from parts name.
	 *
	 * @param commandIndexMap
	 *            the command index map
	 * @param varparts
	 *            the varparts
	 * @return the each command from parts name
	 */
	public static EachCommand getEachCommandFromPartsName(
			final Map<String, Command> commandIndexMap,
			final String[] varparts) {
		if (varparts.length == TieConstants.DEFAULT_COMMAND_PART_LENGTH) {
			return (EachCommand) commandIndexMap
					.get(TieConstants.EACH_COMMAND_FULL_NAME_PREFIX
							+ varparts[1]);
		}
		return null;

	}

	/**
	 * Insert empty object in context.
	 *
	 * @param fullName
	 *            the full name
	 * @param lastCollection
	 *            the last collection
	 * @param eachCommand
	 *            the each command
	 * @param lastCollectionIndex
	 *            the last collection index
	 * @param dataContext
	 *            the data context
	 * @return the string
	 * @throws EvaluationException
	 *             the evaluation exception
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private static String insertEmptyObjectInContext(final String fullName,
			final Collection lastCollection, final EachCommand eachCommand,
			final int lastCollectionIndex,
			final Map<String, Object> dataContext) {
		if (!(lastCollection instanceof List)) {
			throw new EvaluationException(
					"Collection must be list in order to insert/delete.");
		}
		List collectionList = (List) lastCollection;
		// the object must support empty constructor.
		Object currentObj = collectionList.get(lastCollectionIndex);
		Object insertObj;
		try {
			insertObj = currentObj.getClass().newInstance();
			collectionList.add(lastCollectionIndex + 1, insertObj);
			dataContext.put(eachCommand.getVar(), insertObj);
			return fullName.substring(0, fullName.lastIndexOf('.') + 1)
					+ (lastCollectionIndex + 1);
		} catch (InstantiationException | IllegalAccessException e) {
			throw new EvaluationException(e);

		}

	}

	/**
	 * Delete object in context.
	 *
	 * @param lastCollection
	 *            the last collection
	 * @param eachCommand
	 *            the each command
	 * @param lastCollectionIndex
	 *            the last collection index
	 * @param dataContext
	 *            the data context
	 */
	@SuppressWarnings({ "rawtypes" })
	private static void deleteObjectInContext(
			final Collection lastCollection, final EachCommand eachCommand,
			final int lastCollectionIndex,
			final Map<String, Object> dataContext) {
		if (!(lastCollection instanceof List)) {
			throw new EvaluationException(eachCommand.getVar()
					+ TieConstants.EACH_COMMAND_INVALID_MSG);
		}
		List collectionList = (List) lastCollection;
		// the object must support empty constructor.

		collectionList.remove(lastCollectionIndex);
		dataContext.remove(eachCommand.getVar());

	}

	/**
	 * Prepare collection data in context.
	 *
	 * @param varparts
	 *            the varparts
	 * @param collection
	 *            the collection
	 * @param dataContext
	 *            the data context
	 * @return the int
	 */
	@SuppressWarnings("rawtypes")
	public static int prepareCollectionDataInContext(
			final String[] varparts, final Collection collection,
			final Map<String, Object> dataContext) {
		if (varparts.length == TieConstants.DEFAULT_COMMAND_PART_LENGTH) {
			int collectionIndex = Integer.parseInt(varparts[2]);
			Object obj = ConfigurationUtility
					.findItemInCollection(collection, collectionIndex);
			if (obj != null) {
				dataContext.put(varparts[1], obj);
				return collectionIndex;
			}
		}
		return -1;
	}

	/**
	 * Index command range.
	 *
	 * @param sourceConfigRange
	 *            the source config range
	 * @param indexMap
	 *            the index map
	 */
	public static void indexCommandRange(
			final ConfigRange sourceConfigRange,
			final Map<String, Command> indexMap) {
		if (sourceConfigRange.getCommandList() != null) {
			for (int i = 0; i < sourceConfigRange.getCommandList()
					.size(); i++) {
				Command command = sourceConfigRange.getCommandList().get(i);
				indexMap.put(command.getCommandName(), command);
				command.getConfigRange().indexCommandRange(indexMap);
			}
		}

	}

	/**
	 * Checks if is row allow add.
	 *
	 * @param row
	 *            the row
	 * @param sheetConfig
	 *            the sheet config
	 * @return true, if is row allow add
	 */
	public static boolean isRowAllowAdd(final Row row,
			final SheetConfiguration sheetConfig) {
		String fullName = ConfigurationUtility.getFullNameFromRow(row);
		if (fullName != null) {
			ConfigRangeAttrs attrs = sheetConfig.getShiftMap()
					.get(fullName);
			if ((attrs != null) && (attrs.isAllowAdd()) && (row
					.getRowNum() == attrs.getFirstRowRef().getRowIndex())) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Insert each template.
	 *
	 * @param sourceConfigRange
	 *            the source config range
	 * @param configBuildRef
	 *            the config build ref
	 * @param index
	 *            the index
	 * @param insertPosition
	 *            the insert position
	 * @param unitRowsMapping
	 *            the unit rows mapping
	 */
	public static void insertEachTemplate(
			final ConfigRange sourceConfigRange,
			final ConfigBuildRef configBuildRef, final int index,
			final int insertPosition, final RowsMapping unitRowsMapping) {
		int srcStartRow = sourceConfigRange.getFirstRowAddr().getRow();
		int srcEndRow = sourceConfigRange.getLastRowPlusAddr().getRow() - 1;

		Sheet sheet = configBuildRef.getSheet();
		Workbook wb = sheet.getWorkbook();
		// excel sheet name has limit 31 chars
		String copyName = TieConstants.COPY_SHEET_PREFIX
				+ sheet.getSheetName();
		if (copyName.length() > TieConstants.EXCEL_SHEET_NAME_LIMIT) {
			copyName = copyName.substring(0,
					TieConstants.EXCEL_SHEET_NAME_LIMIT);
		}
		Sheet srcSheet = wb.getSheet(copyName);
		if (index > 0) {
			CellUtility.copyRows(srcSheet, sheet, srcStartRow, srcEndRow,
					insertPosition, false, true);
		}

		for (int rowIndex = srcStartRow; rowIndex <= srcEndRow; rowIndex++) {
			if (configBuildRef.getWatchList().contains(rowIndex)
					&& (ConfigurationUtility.isStaticRow(sourceConfigRange,
							rowIndex))) {
				unitRowsMapping.addRow(rowIndex, sheet
						.getRow(insertPosition + rowIndex - srcStartRow));
			}
		}
	}

	/**
	 * Evaluate.
	 *
	 * @param context
	 *            the context
	 * @param cell
	 *            the cell
	 * @param engine
	 *            the engine
	 */
	@SuppressWarnings("deprecation")
	public static void evaluate(final Map<String, Object> context,
			final Cell cell, final ExpressionEngine engine) {
		if ((cell != null) && (cell.getCellTypeEnum() == CellType.STRING)) {
			String strValue = cell.getStringCellValue();
			if (isUserFormula(strValue)) {
				evaluateUserFormula(cell, strValue);
			} else {
				evaluateNormalCells(cell, strValue, context, engine);
			}
		}
	}

	/**
	 * Evaluate normal cells.
	 *
	 * @param cell
	 *            cell.
	 * @param strValue
	 *            string value.
	 * @param context
	 *            context.
	 * @param engine
	 *            engine.
	 */
	public static void evaluateNormalCells(final Cell cell,
			final String strValue, final Map<String, Object> context,
			final ExpressionEngine engine) {
		if (strValue.contains(TieConstants.METHOD_PREFIX)) {

			Object evaluationResult = evaluate(strValue, context, engine);
			if (evaluationResult == null) {
				evaluationResult = "";
			}
			CellUtility.setCellValue(cell, evaluationResult.toString());

			createTieCell(cell, context, engine);

		}
	}

	private static void createTieCell(final Cell cell, final Map<String, Object> context,
	    final ExpressionEngine engine) {

        	@SuppressWarnings("unchecked")
        	HashMap<String, TieCell> tieCells = (HashMap<String, TieCell>) context.get("tiecells");
        
        	// if tiecells exists is because tieWebSheetBean.isAdvancedContext() is
        	// true
        	if (tieCells != null) {
        
        	    if (SaveAttrsUtility.isHasSaveAttr(cell)) {
        
        		String saveAttrList = SaveAttrsUtility.getSaveAttrListFromRow(cell.getRow());
        
        		if (saveAttrList != null) {
        		    String saveAttr = SaveAttrsUtility.getSaveAttrFromList(cell.getColumnIndex(), saveAttrList);
        		    if (saveAttr != null) {
        
        			int index = saveAttr.lastIndexOf('.');
        			if (index > 0) {
        			    String strObject = saveAttr.substring(0, index);
        			    String strMethod = saveAttr.substring(index + 1);
        			    strObject = "${" + strObject + "}";
        
        			    Object object = CommandUtility.evaluate(strObject, context, engine);
        
        			    if (object != null) {
        				TieCell tieCell = CellUtility.getOrAddTieCellInMap(cell, tieCells);
        				tieCell.setContextObject(object);
        				tieCell.setObjectStr(strObject);
        				tieCell.setMethodStr(strMethod);
        			    }
        
        			}
        
        		    }
        		}
        
        	    }
        
        	}
	}


	/**
	 * Evaluate user formula.
	 *
	 * @param cell
	 *            the cell
	 * @param strValue
	 *            the str value
	 */
	private static void evaluateUserFormula(final Cell cell,
			final String strValue) {
		String formulaStr = strValue.substring(2, strValue.length() - 1);
		if ((formulaStr != null) && (!formulaStr.isEmpty())) {
			cell.setCellFormula(formulaStr);
		}
	}

	/**
	 * Checks if is user formula.
	 *
	 * @param str
	 *            the str
	 * @return true, if is user formula
	 */
	private static boolean isUserFormula(final String str) {
		return str.startsWith(TieConstants.USER_FORMULA_PREFIX)
				&& str.endsWith(TieConstants.USER_FORMULA_SUFFIX);
	}

	/**
	 * Evaluate.
	 *
	 * @param strValue
	 *            the str value
	 * @param context
	 *            the context
	 * @param engine
	 *            the engine
	 * @return the object
	 */
	public static Object evaluate(final String strValue,
			final Map<String, Object> context,
			final ExpressionEngine engine) {
		StringBuffer sb = new StringBuffer();
		int beginExpressionLength = TieConstants.METHOD_PREFIX.length();
		int endExpressionLength = TieConstants.METHOD_END.length();
		Matcher exprMatcher = TieConstants.EXPRESSION_NOTATION_PATTERN
				.matcher(strValue);
		String matchedString;
		String expression;
		Object lastMatchEvalResult = null;
		int matchCount = 0;
		int endOffset = 0;
		while (exprMatcher.find()) {
			endOffset = exprMatcher.end();
			matchCount++;
			matchedString = exprMatcher.group();
			expression = matchedString.substring(beginExpressionLength,
					matchedString.length() - endExpressionLength);
			lastMatchEvalResult = engine.evaluate(expression, context);
			exprMatcher.appendReplacement(sb,
					Matcher.quoteReplacement(lastMatchEvalResult != null
							? lastMatchEvalResult.toString() : ""));
		}
		String lastStringResult = lastMatchEvalResult != null
				? lastMatchEvalResult.toString() : "";
		boolean isAppendTail = matchCount == 1
				&& endOffset < strValue.length();
		Object evaluationResult = null;
		if (matchCount > 1 || isAppendTail) {
			exprMatcher.appendTail(sb);
			evaluationResult = sb.toString();
		} else if (matchCount == 1) {
			if (sb.length() > lastStringResult.length()) {
				evaluationResult = sb.toString();
			} else {
				evaluationResult = lastMatchEvalResult;
			}
		} else if (matchCount == 0) {
			evaluationResult = strValue;
		}
		return evaluationResult;
	}

	/**
	 * Creates the cell comment.
	 *
	 * @param cell
	 *            the cell
	 * @param newComment
	 *            the new comment
	 * @param finalCommentMap
	 *            the final comment map
	 */
	public static void createCellComment(final Cell cell,
			final String newComment,
			final Map<Cell, String> finalCommentMap) {
		// due to poi's bug. the comment must be set in sorted order ( row first
		// then column),
		// otherwise poi will mess up.
		// workaround solution is to save all comments into a map,
		// and output them together when download workbook.

		if (newComment != null) {
			finalCommentMap.put(cell, newComment);

		}
	}

	/**
	 * evaluate boolean express.
	 *
	 * @param expEngine
	 *            expression engine.
	 * @param pscript
	 *            script.
	 * @return true (express is true) false ( express is false or invalid).
	 */
	public static boolean evalBoolExpression(
			final ExpressionEngine expEngine, final String pscript) {
		Object result = null;
		String script = "( " + pscript + " )";
		script = script.toUpperCase().replace("AND", "&&");
		script = script.toUpperCase().replace("OR", "||");
		try {
			result = expEngine.evaluate(script);
		} catch (Exception e) {
			LOG.log(Level.SEVERE,
					"WebForm WebFormHelper evalBoolExpression script = "
							+ script + "; error = "
							+ e.getLocalizedMessage(),
					e);
		}
		if (result != null) {
			return ((Boolean) result).booleanValue();
		} else {
			return false;
		}
	}

	/**
	 * Remove the rows.
	 *
	 * @param sheet
	 *            the sheet
	 * @param rowIndexStart
	 *            start row index.
	 * @param rowIndexEnd
	 *            end row index.
	 * @param cachedMap
	 *            the cached map
	 */
	public static void removeRowsInSheet(final Sheet sheet,
			final int rowIndexStart, final int rowIndexEnd,
			final Map<Cell, String> cachedMap) {

		for (int irow = rowIndexStart; irow <= rowIndexEnd; irow++) {
			removeCachedCellForRow(sheet, irow, cachedMap);
		}
		int irows = rowIndexEnd - rowIndexStart + 1;
		if ((irows < 1) || (rowIndexStart < 0)) {
			return;
		}
		int lastRowNum = sheet.getLastRowNum();
		if (rowIndexEnd < lastRowNum) {
			sheet.shiftRows(rowIndexEnd + 1, lastRowNum, -irows);
		}
		if (rowIndexEnd == lastRowNum) {
			// reverse order to delete rows.
			for (int i = rowIndexEnd; i >= rowIndexStart; i--) {
				removeSingleRowInSheet(sheet, rowIndexStart);
			}
		}
	}

	/**
	 * Removes the single row in sheet.
	 *
	 * @param sheet
	 *            the sheet
	 * @param rowIndexStart
	 *            the row index start
	 */
	private static void removeSingleRowInSheet(final Sheet sheet,
			final int rowIndexStart) {
		Row removingRow = sheet.getRow(rowIndexStart);
		if (removingRow != null) {
			sheet.removeRow(removingRow);
		}
	}

	/**
	 * Removes the cached cell for row.
	 *
	 * @param sheet
	 *            the sheet
	 * @param rowIndexStart
	 *            the row index start
	 * @param cachedMap
	 *            the cached map
	 */
	private static void removeCachedCellForRow(final Sheet sheet,
			final int rowIndexStart, final Map<Cell, String> cachedMap) {
		Row removingRow = sheet.getRow(rowIndexStart);
		if (removingRow != null) {
			// remove cached cell.
			for (Cell cell : removingRow) {
				cachedMap.remove(cell);
			}
		}
	}

	/**
	 * Removes the rows in body.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @param bodyRows
	 *            the body rows
	 * @param rowIndexStart
	 *            the row index start
	 * @param rowIndexEnd
	 *            the row index end
	 */
	public static void removeRowsInBody(
			final SheetConfiguration sheetConfig,
			final List<FacesRow> bodyRows, final int rowIndexStart,
			final int rowIndexEnd) {
		int top = sheetConfig.getBodyCellRange().getTopRow();
		if ((rowIndexEnd < rowIndexStart) || (rowIndexStart < top)) {
			return;
		}

		int irows = rowIndexEnd - rowIndexStart + 1;
		for (int rowIndex = rowIndexEnd; rowIndex >= rowIndexStart; rowIndex--) {
			bodyRows.remove(rowIndex - top);
		}
		for (int irow = rowIndexStart - top; irow < bodyRows
				.size(); irow++) {
			FacesRow facesrow = bodyRows.get(irow);
			facesrow.setRowIndex(facesrow.getRowIndex() - irows);
		}
	}
}
 public final class ConfigurationUtility {

	/** logger. */
	static final Logger LOG = Logger
			.getLogger(ConfigurationUtility.class.getName());

	/**
	 * hide constructor.
	 */
	private ConfigurationUtility() {
		// not called
	}

	/**
	 * Transform to collection object.
	 *
	 * @param engine
	 *            the engine
	 * @param collectionName
	 *            the collection name
	 * @param context
	 *            the context
	 * @return the collection
	 */
	@SuppressWarnings("rawtypes")
	public static Collection transformToCollectionObject(
			final ExpressionEngine engine, final String collectionName,
			final Map<String, Object> context) {
		Object collectionObject = engine.evaluate(collectionName, context);
		if (!(collectionObject instanceof Collection)) {
			throw new EvaluationException(
					collectionName + " expression is not a collection");
		}
		return (Collection) collectionObject;
	}

	/**
	 * Gets the full name from row.
	 *
	 * @param row
	 *            the row
	 * @return the full name from row
	 */
	public static String getFullNameFromRow(final Row row) {
		if (row != null) {
			Cell cell = row.getCell(TieConstants.HIDDEN_FULL_NAME_COLUMN);
			if (cell != null) {
				return cell.getStringCellValue();
			}
		}
		return null;
	}

	/**
	 * Re build upper level formula.
	 *
	 * @param configBuildRef
	 *            the config build ref
	 * @param actionFullName
	 *            the action full name
	 */
	public static void reBuildUpperLevelFormula(
			final ConfigBuildRef configBuildRef,
			final String actionFullName) {
		Map<Cell, String> cachedMap = configBuildRef.getCachedCells();
		Map<String, List<RowsMapping>> rowsMap = new HashMap<>();
		for (Map.Entry<Cell, String> entry : cachedMap.entrySet()) {
			Cell cell = entry.getKey();
			String originFormula = entry.getValue();
			if (originFormula != null) {
				setupUpperLevelFormula(cell, originFormula, actionFullName,
						rowsMap, configBuildRef);
			}
		}

	}

	/**
	 * Setup upper level formula.
	 *
	 * @param cell
	 *            cell.
	 * @param originFormula
	 *            originFormula.
	 * @param actionFullName
	 *            add full name.
	 * @param rowsMap
	 *            rowsmap.
	 * @param configBuildRef
	 *            config build ref.
	 */
	private static void setupUpperLevelFormula(final Cell cell,
			final String originFormula, final String actionFullName,
			final Map<String, List<RowsMapping>> rowsMap,
			final ConfigBuildRef configBuildRef) {
		String fullName = getFullNameFromRow(cell.getRow());
		// check wither it's upper level
		if (actionFullName.startsWith(fullName + ":")) {
			// get rows mapping for upper level row
			List<RowsMapping> currentRowsMappingList = rowsMap
					.get(fullName);
			if (currentRowsMappingList == null) {
				currentRowsMappingList = gatherRowsMappingByFullName(
						configBuildRef, fullName);
				rowsMap.put(fullName, currentRowsMappingList);
			}
			ShiftFormulaRef shiftFormulaRef = new ShiftFormulaRef(
					configBuildRef.getWatchList(), currentRowsMappingList);
			shiftFormulaRef.setFormulaChanged(0);
			buildCellFormulaForShiftedRows(configBuildRef.getSheet(),
					configBuildRef.getWbWrapper(), shiftFormulaRef, cell,
					originFormula);
			if (shiftFormulaRef.getFormulaChanged() > 0) {
				configBuildRef.getCachedCells().put(cell, originFormula);
			}

		}
	}

	/**
	 * Builds the cell formula for shifted rows.
	 *
	 * @param sheet
	 *            the sheet
	 * @param wbWrapper
	 *            the wb wrapper
	 * @param shiftFormulaRef
	 *            the shift formula ref
	 * @param cell
	 *            the cell
	 * @param originFormula
	 *            the origin formula
	 */
	public static void buildCellFormulaForShiftedRows(final Sheet sheet,
			final XSSFEvaluationWorkbook wbWrapper,
			final ShiftFormulaRef shiftFormulaRef, final Cell cell,
			final String originFormula) {
		// only shift when there's watchlist exist.
		if ((shiftFormulaRef.getWatchList() != null)
				&& (!shiftFormulaRef.getWatchList().isEmpty())) {
			Ptg[] ptgs = FormulaParser.parse(originFormula, wbWrapper,
					FormulaType.CELL,
					sheet.getWorkbook().getSheetIndex(sheet));
			Ptg[] convertedFormulaPtg = ShiftFormulaUtility
					.convertSharedFormulas(ptgs, shiftFormulaRef);
			if (shiftFormulaRef.getFormulaChanged() > 0) {
				// only change formula when indicator is true
				cell.setCellFormula(FormulaRenderer
						.toFormulaString(wbWrapper, convertedFormulaPtg));

			}
		}
	}

	/**
	 * Gather rows mapping by full name.
	 *
	 * @param configBuildRef
	 *            the config build ref
	 * @param fullName
	 *            the full name
	 * @return the list
	 */
	public static List<RowsMapping> gatherRowsMappingByFullName(
			final ConfigBuildRef configBuildRef, final String fullName) {
		List<RowsMapping> list = new ArrayList<>();
		Map<String, ConfigRangeAttrs> shiftMap = configBuildRef
				.getShiftMap();
		for (Map.Entry<String, ConfigRangeAttrs> entry : shiftMap
				.entrySet()) {
			String fname = entry.getKey();
			if (fname.startsWith(fullName + ":")
					|| fname.equals(fullName)) {
				ConfigRangeAttrs attrs = entry.getValue();
				list.add(attrs.getUnitRowsMapping());
			}
		}
		return list;
	}

	/**
	 * Increase index number in shift map.
	 *
	 * @param shiftMap
	 *            the shift map
	 * @param changeMap
	 *            the change map
	 */
	public static void changeIndexNumberInShiftMap(
			final Map<String, ConfigRangeAttrs> shiftMap,
			final Map<String, String> changeMap) {
		for (Map.Entry<String, String> entry : changeMap.entrySet()) {
			String key = entry.getKey();
			String newKey = entry.getValue();
			ConfigRangeAttrs attrs = shiftMap.get(key);
			if (attrs != null) {
				shiftMap.remove(key);
				shiftMap.put(newKey, attrs);
			}
		}
	}

	/**
	 * Increase upper level final length.
	 *
	 * @param shiftMap
	 *            the shift map
	 * @param addedFullName
	 *            the added full name
	 * @param increasedLength
	 *            the increased length
	 */
	public static void changeUpperLevelFinalLength(
			final Map<String, ConfigRangeAttrs> shiftMap,
			final String addedFullName, final int increasedLength) {
		String[] parts = addedFullName.split(":");
		StringBuilder fname = new StringBuilder();
		for (int i = 0; i < (parts.length - 1); i++) {
			if (i == 0) {
				fname.append(parts[i]);
			} else {
				fname.append(":").append(parts[i]);
			}
			String sname = fname.toString();
			shiftMap.get(sname).setFinalLength(
					shiftMap.get(sname).getFinalLength() + increasedLength);
		}
	}

	/**
	 * Change index number in hidden column.
	 *
	 * @param configBuildRef
	 *            the config build ref
	 * @param startRowIndex
	 *            the start row index
	 * @param fullName
	 *            the full name
	 * @param changeMap
	 *            the change map
	 * @param steps
	 *            the steps ( 1 add -1 delete ).
	 */
	public static void changeIndexNumberInHiddenColumn(
			final ConfigBuildRef configBuildRef, final int startRowIndex,
			final String fullName, final Map<String, String> changeMap,
			final int steps) {
		String searchName = fullName.substring(0,
				fullName.lastIndexOf('.') + 1);
		Sheet sheet = configBuildRef.getSheet();
		for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String fname = getFullNameFromRow(row);
			if ((fname != null) && (fname.indexOf(searchName) >= 0)) {
				int sindex = fname.indexOf(searchName);
				String snum = fname.substring(sindex + searchName.length());
				int sufindex = snum.indexOf(':');
				String suffix = "";
				if (sufindex > 0) {
					snum = snum.substring(0, sufindex);
					suffix = ":";
				}
				int increaseNum = Integer.parseInt(snum) + steps;
				String realFullName = fname.substring(sindex);
				String changeName = fname.replace(
						searchName + snum + suffix,
						searchName + increaseNum + suffix);
				if (changeMap.get(realFullName) == null) {
					changeMap.put(realFullName,
							changeName.substring(sindex));
				}
				setFullNameInHiddenColumn(row, changeName);
			} else {
				return;
			}
		}
	}

	/**
	 * Decrease index number in hidden column.
	 *
	 * @param configBuildRef
	 *            the config build ref
	 * @param startRowIndex
	 *            the start row index
	 * @param fullName
	 *            the full name
	 * @param changeMap
	 *            the change map
	 */
	public static void decreaseIndexNumberInHiddenColumn(
			final ConfigBuildRef configBuildRef, final int startRowIndex,
			final String fullName, final Map<String, String> changeMap) {
		String searchName = fullName.substring(0,
				fullName.lastIndexOf('.') + 1);
		Sheet sheet = configBuildRef.getSheet();
		for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String fname = getFullNameFromRow(row);
			if ((fname != null) && (fname.indexOf(searchName) >= 0)) {
				int sindex = fname.indexOf(searchName);
				String snum = fname.substring(sindex + searchName.length());
				int sufindex = snum.indexOf(':');
				String suffix = "";
				if (sufindex > 0) {
					snum = snum.substring(0, sufindex);
					suffix = ":";
				}
				int increaseNum = Integer.parseInt(snum) - 1;
				String realFullName = fname.substring(sindex);
				String changeName = fname.replace(
						searchName + snum + suffix,
						searchName + increaseNum + suffix);
				if (changeMap.get(realFullName) == null) {
					changeMap.put(realFullName,
							changeName.substring(sindex));
				}
				setFullNameInHiddenColumn(row, changeName);
			}
		}
	}

	/**
	 * Sets the full name in hidden column.
	 *
	 * @param row
	 *            the row
	 * @param fullName
	 *            the full name
	 */
	public static void setFullNameInHiddenColumn(final Row row,
			final String fullName) {
		Cell cell = row.getCell(TieConstants.HIDDEN_FULL_NAME_COLUMN,
				MissingCellPolicy.CREATE_NULL_AS_BLANK);

		cell.setCellValue(fullName);
	}

	/**
	 * Gets the original row num in hidden column.
	 *
	 * @param row
	 *            the row
	 * @return the original row num in hidden column
	 */
	public static int getOriginalRowNumInHiddenColumn(final Row row) {
		if (row != null) {
			Cell cell = row.getCell(
					TieConstants.HIDDEN_ORIGIN_ROW_NUMBER_COLUMN,
					MissingCellPolicy.CREATE_NULL_AS_BLANK);
			String rowNum = cell.getStringCellValue();
			try {
				if ((rowNum != null) && (!rowNum.isEmpty())
						&& (WebSheetUtility.isNumeric(rowNum))) {
					return Integer.parseInt(rowNum);
				}

			} catch (Exception ex) {
				LOG.log(Level.SEVERE,
						"getOriginalRowNumInHiddenColumn rowNum = " + rowNum
								+ " error = " + ex.getLocalizedMessage(),
						ex);

			}
		}
		return -1;
	}

	/**
	 * Sets the original row num in hidden column.
	 *
	 * @param row
	 *            the row.
	 * @param rowNum
	 *            row number.
	 */
	public static void setOriginalRowNumInHiddenColumn(final Row row,
			final int rowNum) {
		Cell cell = row.getCell(
				TieConstants.HIDDEN_ORIGIN_ROW_NUMBER_COLUMN,
				MissingCellPolicy.CREATE_NULL_AS_BLANK);
		cell.setCellValue(Integer.toString(rowNum));
		cell.setCellType(CellType.STRING);
	}

	/**
	 * Find parent rows mapping from shift map.
	 *
	 * @param parts
	 *            the parts
	 * @param shiftMap
	 *            the shift map
	 * @return the list
	 */
	public static List<RowsMapping> findParentRowsMappingFromShiftMap(
			final String[] parts,
			final Map<String, ConfigRangeAttrs> shiftMap) {

		StringBuilder fullName = new StringBuilder();
		List<RowsMapping> rowsMappingList = new ArrayList<>();
		/**
		 * skip first one and last one. first one is line no. last one is it's
		 * self.
		 */
		for (int i = 1; i < parts.length - 1; i++) {
			String part = parts[i];
			if (fullName.length() == 0) {
				fullName.append(part);
			} else {
				fullName.append(":" + part);
			}
			if (fullName.length() > 0) {
				ConfigRangeAttrs rangeAttrs = shiftMap
						.get(fullName.toString());
				if (rangeAttrs != null) {
					rowsMappingList.add(rangeAttrs.getUnitRowsMapping());
				}
			}
		}
		return rowsMappingList;
	}

	/**
	 * Find child rows mapping from shift map.
	 *
	 * @param fullName
	 *            the full name
	 * @param shiftMap
	 *            the shift map
	 * @return the list
	 */
	public static List<RowsMapping> findChildRowsMappingFromShiftMap(
			final String fullName,
			final NavigableMap<String, ConfigRangeAttrs> shiftMap) {

		List<RowsMapping> rowsMappingList = new ArrayList<>();
		NavigableMap<String, ConfigRangeAttrs> tailmap = shiftMap
				.tailMap(fullName, false);
		for (Map.Entry<String, ConfigRangeAttrs> entry : tailmap
				.entrySet()) {
			String key = entry.getKey();
			// check it's children
			if (key.startsWith(fullName)) {
				rowsMappingList.add(entry.getValue().getUnitRowsMapping());
			} else {
				break;
			}
		}
		return rowsMappingList;
	}

	/**
	 * Find item in collection.
	 *
	 * @param collection
	 *            the collection
	 * @param index
	 *            the index
	 * @return the object
	 */
	@SuppressWarnings("rawtypes")
	public static Object findItemInCollection(final Collection collection,
			final int index) {
		if (index >= 0) {
			if (collection instanceof List) {
				List list = (List) collection;
				return list.get(index);
			}
			int i = 0;
			for (Object object : collection) {
				if (i == index) {
					return object;
				}
				i++;
			}

		}
		return null;
	}

	/**
	 * Builds the current range.
	 *
	 * @param sourceConfigRange
	 *            the source config range
	 * @param sheet
	 *            the sheet
	 * @param insertPosition
	 *            the insert position
	 * @return the config range
	 */
	public static ConfigRange buildCurrentRange(
			final ConfigRange sourceConfigRange, final Sheet sheet,
			final int insertPosition) {
		ConfigRange current = new ConfigRange(sourceConfigRange);
		int shiftNum = insertPosition
				- sourceConfigRange.getFirstRowAddr().getRow();
		current.shiftRowRef(sheet, shiftNum);
		return current;
	}

	/**
	 * Whether the row is static. This only check rowIndex against original
	 * template.
	 *
	 * @param sourceConfigRange
	 *            the source config range
	 * @param rowIndex
	 *            the row index
	 * @return true is static false is not.
	 */
	public static boolean isStaticRow(final ConfigRange sourceConfigRange,
			final int rowIndex) {
		if (sourceConfigRange.getCommandList() != null) {
			for (int i = 0; i < sourceConfigRange.getCommandList()
					.size(); i++) {
				Command command = sourceConfigRange.getCommandList().get(i);
				if ((rowIndex >= command.getConfigRange().getFirstRowAddr()
						.getRow())
						&& (rowIndex < (command.getConfigRange()
								.getLastRowPlusAddr().getRow()))) {
					return false;
				}
			}
		}
		return true;
	}

	/**
	 * Whether the row is static.This check row after shifted.
	 *
	 * @param sourceConfigRange
	 *            the source config range
	 * @param row
	 *            the row for check.
	 * @return true is static false is not.
	 */
	public static boolean isStaticRowRef(
			final ConfigRange sourceConfigRange, final Row row) {
		if (sourceConfigRange.getCommandList() != null) {
			for (int i = 0; i < sourceConfigRange.getCommandList()
					.size(); i++) {
				Command command = sourceConfigRange.getCommandList().get(i);
				int rowIndex = row.getRowNum();
				if ((rowIndex >= command.getTopRow())
						&& (rowIndex < (command.getTopRow()
								+ command.getFinalLength()))) {
					return false;
				}
			}
		}
		return true;
	}

	/**
	 * Remove last each command index from full name. e.g. for
	 * F.departments:E.department.1:E.employee.2 will return
	 * F.departments:E.department.1:E.employee
	 * 
	 * Reason for this is for last layer collection. e.g. E.employee.1 and
	 * E.employee.2 The E.emloyee is the same. *
	 *
	 * @param fullName
	 *            the full name
	 * @return the full data collect name from full name
	 */
	public static String getFullDataCollectNameFromFullName(
			final String fullName) {
		if (fullName == null) {
			return "";
		}

		int lastEachCommandPos = fullName
				.lastIndexOf(TieConstants.EACH_COMMAND_FULL_NAME_PREFIX);
		if (lastEachCommandPos < 0) {
			return "";
		}
		int lastEachCommandIndexPos = fullName.indexOf('.',
				lastEachCommandPos
						+ TieConstants.EACH_COMMAND_FULL_NAME_PREFIX
								.length());
		if (lastEachCommandIndexPos < 0) {
			return fullName;
		}
		return fullName.substring(0, lastEachCommandIndexPos);
	}

	// This method mainly doing 2 things
	// 1. covert $A to $A$rowIndex
	// 2. Get value of $A$rowIndex and replace it in the string
	// i.e. $value >= $E
	// first $value has been taken cared before to actual value like 100
	// Here change $E to $E$8, Then get $E$8 value. Replace it in string like
	/**
	 * Replace expression with cell value.
	 *
	 * @param attrValue
	 *            the attr value
	 * @param rowIndex
	 *            the row index
	 * @param sheet
	 *            the sheet
	 * @return the string
	 */
	// 100 >= 80
	public static String replaceExpressionWithCellValue(
			final String attrValue, final int rowIndex, final Sheet sheet) {

		int ibegin = 0;
		int ifind;
		int inameEnd;
		String tempStr;
		String findStr;
		String replaceStr;
		String returnStr = attrValue;
		while ((ifind = attrValue.indexOf(TieConstants.CELL_ADDR_PRE_FIX,
				ibegin)) > 0) {
			inameEnd = ParserUtility.findFirstNonCellNamePosition(attrValue, ifind);
			if (inameEnd > 0) {
				findStr = attrValue.substring(ifind, inameEnd);
			} else {
				findStr = attrValue.substring(ifind);
			}
			if (findStr.indexOf(TieConstants.CELL_ADDR_PRE_FIX, 1) < 0) {
				// only $A
				tempStr = findStr + TieConstants.CELL_ADDR_PRE_FIX
						+ (rowIndex + 1);
			} else {
				tempStr = findStr;
			}
			replaceStr = CellUtility.getCellValueWithoutFormat(
					WebSheetUtility.getCellByReference(tempStr, sheet));
			if (replaceStr == null) {
				replaceStr = "";
			}
			returnStr = attrValue.replace(findStr, replaceStr);

			ibegin = ifind + 1;

		}
		return returnStr;
	}

	/**
	 * Index merged region.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @return the map
	 */
	public static Map<String, CellRangeAddress> indexMergedRegion(
			final Sheet sheet1) {

		int numRegions = sheet1.getNumMergedRegions();
		Map<String, CellRangeAddress> cellRangeMap = new HashMap<>();
		for (int i = 0; i < numRegions; i++) {

			CellRangeAddress caddress = sheet1.getMergedRegion(i);
			if (caddress != null) {
				cellRangeMap.put(CellUtility.getCellIndexNumberKey(
						caddress.getFirstColumn(), caddress.getFirstRow()),
						caddress);
			}
		}
		return cellRangeMap;
	}

	/**
	 * Skipped region cells.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @return the list
	 */
	public static List<String> skippedRegionCells(final Sheet sheet1) {
		int numRegions = sheet1.getNumMergedRegions();
		List<String> skipCellList = new ArrayList<>();
		for (int i = 0; i < numRegions; i++) {

			CellRangeAddress caddress = sheet1.getMergedRegion(i);
			if (caddress != null) {
				addSkipCellToListInTheRegion(skipCellList, caddress);
			}
		}
		return skipCellList;
	}

	/**
	 * Add skipped cell into the list of a region.
	 * 
	 * @param skipCellList
	 *            list.
	 * @param caddress
	 *            region.
	 */
	private static void addSkipCellToListInTheRegion(
			final List<String> skipCellList,
			final CellRangeAddress caddress) {
		for (int col = caddress.getFirstColumn(); col <= caddress
				.getLastColumn(); col++) {
			for (int row = caddress.getFirstRow(); row <= caddress
					.getLastRow(); row++) {
				if ((col == caddress.getFirstColumn())
						&& (row == caddress.getFirstRow())) {
					continue;
				}
				skipCellList
						.add(CellUtility.getCellIndexNumberKey(col, row));
			}
		}
	}

	
	/**
	 * Build Sheet Comment From command alias.
	 *
	 * @param sheet sheet.
	 * @param tieCommandAliasList 			list of ommand alias
	 */
	public static void buildSheetCommentFromAlias(Sheet sheet, List<TieCommandAlias> tieCommandAliasList) {

		if ((tieCommandAliasList == null)||(tieCommandAliasList.isEmpty())) {
			return;
		}
        for (Row row : sheet) {
            for (Cell cell : row) {
                buildCellCommentFromalias(tieCommandAliasList, cell);
            }
        }		
		
	}

	/**
	 * Builds the cell comment fromalias.
	 *
	 * @param tieCommandAliasList the tie command alias list
	 * @param cell the cell
	 */
	private static void buildCellCommentFromalias(List<TieCommandAlias> tieCommandAliasList, Cell cell) {
		String value = CellUtility.getCellValueWithoutFormat(cell);
		if ((value!=null)&&(!value.isEmpty())) {
			for (TieCommandAlias alias : tieCommandAliasList) {
				Matcher matcher = alias.getPattern().matcher(value);
				if (matcher.find()) {
					CellUtility.createOrInsertComment(cell, alias.getCommand());
					if (alias.isRemove()) {
						CellUtility.setCellValue(cell, ParserUtility.removeCharsFromString(value, matcher.start(), matcher.end()));
					}
				}	
			}
		}
	}
	
	
}
 public final class SaveAttrsUtility {

	/**
	 * hide constructor.
	 */
	private SaveAttrsUtility() {
		// not called
	}

	/**
	 * Parses the save attr.
	 *
	 * @param cell
	 *            the cell
	 * @param saveCommentsMap
	 *            the save comments map
	 * @return the string
	 */
	public static String parseSaveAttr(final Cell cell, final Map<String, String> saveCommentsMap) {
		if (cell != null) {
			String key = cell.getSheet().getSheetName() + "!"
					+ CellUtility.getCellIndexNumberKey(cell.getColumnIndex(), cell.getRowIndex());
			String saveAttr = null;
			if (saveCommentsMap != null) {
				saveAttr = ParserUtility.getStringBetweenBracket(saveCommentsMap.get(key));
			}
			if ((saveAttr == null) && (cell.getCellTypeEnum() == CellType.STRING)) {
				saveAttr = SaveAttrsUtility.parseSaveAttrString(cell.getStringCellValue());
			}
			if ((saveAttr != null) && (!saveAttr.isEmpty())) {
				return TieConstants.CELL_ADDR_PRE_FIX + cell.getColumnIndex() + "=" + saveAttr + ",";
			}
		}
		return "";
	}

	/**
	 * Save data to object in context.
	 *
	 * @param context
	 *            the context
	 * @param saveAttr
	 *            the save attr
	 * @param strValue
	 *            the str value
	 * @param engine
	 *            the engine
	 */
	public static void saveDataToObjectInContext(final Map<String, Object> context, final String saveAttr,
			final String strValue, final ExpressionEngine engine) {

		int index = saveAttr.lastIndexOf('.');
		if (index > 0) {
			String strObject = saveAttr.substring(0, index);
			String strMethod = saveAttr.substring(index + 1);
			strObject = TieConstants.METHOD_PREFIX + strObject + TieConstants.METHOD_END;
			Object object = CommandUtility.evaluate(strObject, context, engine);
			CellControlsUtility.setObjectProperty(object, strMethod, strValue, true);
		}
	}

	/**
	 * reload the data from context to websheet row.
	 * 
	 * @param context
	 *            context.
	 * @param fullSaveAttr
	 *            full saveattr.
	 * @param row
	 *            row.
	 * @param engine
	 *            engine.
	 */

	public static void refreshSheetRowFromContext(final Map<String, Object> context, final String fullSaveAttr,
			final Row row, final ExpressionEngine engine) {
		if (!fullSaveAttr.startsWith(TieConstants.CELL_ADDR_PRE_FIX)) {
			return;
		}
		int ipos = fullSaveAttr.indexOf('=');
		if (ipos > 0) {
			String columnIndex = fullSaveAttr.substring(1, ipos);
			String saveAttr = fullSaveAttr.substring(ipos + 1);
			Cell cell = row.getCell(Integer.parseInt(columnIndex));
			if (cell.getCellTypeEnum() != CellType.FORMULA) {
				CommandUtility.evaluateNormalCells(cell,
						TieConstants.METHOD_PREFIX + saveAttr + TieConstants.METHOD_END, context, engine);
			}
		}
	}

	/**
	 * Parses the save attr string.
	 *
	 * @param strValue
	 *            the str value
	 * @return the string
	 */
	public static String parseSaveAttrString(final String strValue) {
		if (strValue != null) {
			int first = strValue.indexOf(TieConstants.METHOD_PREFIX);
			int last = strValue.lastIndexOf(TieConstants.METHOD_PREFIX);
			int end = strValue.lastIndexOf(TieConstants.METHOD_END);
			if ((first >= 0) && (first == last) && (end > 1)) {
				return strValue.substring(first + 2, end);
			}
		}
		return "";
	}

	/**
	 * Gets the save attr list from row.
	 *
	 * @param row
	 *            the row
	 * @return the save attr list from row
	 */
	public static String getSaveAttrListFromRow(final Row row) {
		if (row != null) {
			Cell cell = row.getCell(TieConstants.HIDDEN_SAVE_OBJECTS_COLUMN);
			if (cell != null) {
				String str = cell.getStringCellValue();
				if ((str != null) && (!str.isEmpty())) {
					return str;
				}
			}
		}
		return null;
	}

	/**
	 * Gets the save attr from list.
	 *
	 * @param columnIndex
	 *            the column index
	 * @param saveAttrs
	 *            the save attrs
	 * @return the save attr from list
	 */
	public static String getSaveAttrFromList(final int columnIndex, final String saveAttrs) {
		if ((saveAttrs != null) && (!saveAttrs.isEmpty())) {
			String str = TieConstants.CELL_ADDR_PRE_FIX + columnIndex + "=";
			int istart = saveAttrs.indexOf(str);
			if (istart >= 0) {
				int iend = saveAttrs.indexOf(',', istart);
				if (iend > istart) {
					return saveAttrs.substring(istart + str.length(), iend);

				}
			}
		}
		return null;
	}

	/**
	 * get the columnIndex from saveAttr. saveAttr format as: $columnIndex=xxxxxxx
	 * 
	 * @param saveAttr
	 *            saveAttr
	 * @return columnIndex String
	 */
	public static String getColumnIndexFromSaveAttr(final String saveAttr) {
		if ((saveAttr != null) && (!saveAttr.isEmpty())) {
			int iend = saveAttr.indexOf('=');
			if (iend > 0) {
				int istart = saveAttr.indexOf('$');
				if (iend > istart) {
					return saveAttr.substring(istart + 1, iend);

				}
			}
		}
		return null;
	}

	/**
	 * Checks if is checks for save attr.
	 *
	 * @param cell
	 *            the cell
	 * @return true, if is checks for save attr
	 */
	public static boolean isHasSaveAttr(final Cell cell) {
		Cell saveAttrCell = cell.getRow().getCell(TieConstants.HIDDEN_SAVE_OBJECTS_COLUMN);
		if (saveAttrCell != null) {
			return isHasSaveAttr(cell, saveAttrCell.getStringCellValue());
		}
		return false;
	}

	/**
	 * Checks if is checks for save attr.
	 *
	 * @param cell
	 *            the cell
	 * @param saveAttrs
	 *            the save attrs
	 * @return true, if is checks for save attr
	 */
	public static boolean isHasSaveAttr(final Cell cell, final String saveAttrs) {

		if (cell != null) {
			int columnIndex = cell.getColumnIndex();
			String str = TieConstants.CELL_ADDR_PRE_FIX + columnIndex + "=";
			if ((saveAttrs != null) && (saveAttrs.indexOf(str) >= 0)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Sets the save objects in hidden column.
	 *
	 * @param row
	 *            the row
	 * @param saveAttr
	 *            the save attr
	 */
	public static void setSaveObjectsInHiddenColumn(final Row row, final String saveAttr) {
		Cell cell = row.getCell(TieConstants.HIDDEN_SAVE_OBJECTS_COLUMN, MissingCellPolicy.CREATE_NULL_AS_BLANK);

		cell.setCellValue(saveAttr);
	}

	/**
	 * Sets the save attrs for sheet.
	 *
	 * @param sheet
	 *            the sheet
	 * @param minRowNum
	 *            the min row num
	 * @param maxRowNum
	 *            the max row num
	 * @param saveCommentsMap
	 *            the save comments map
	 */
	public static void setSaveAttrsForSheet(final Sheet sheet, final int minRowNum, final int maxRowNum,
			final Map<String, String> saveCommentsMap) {

		for (Row row : sheet) {
			int rowIndex = row.getRowNum();
			if ((rowIndex >= minRowNum) && (rowIndex <= maxRowNum)) {
				setSaveAttrsForRow(row, saveCommentsMap);
			}
		}
	}

	/**
	 * Sets the save attrs for row.
	 *
	 * @param row
	 *            the row
	 * @param saveCommentsMap
	 *            the save comments map
	 */
	public static void setSaveAttrsForRow(final Row row, final Map<String, String> saveCommentsMap) {
		StringBuilder saveAttr = new StringBuilder();
		for (Cell cell : row) {
			String sAttr = parseSaveAttr(cell, saveCommentsMap);
			if (!sAttr.isEmpty()) {
				saveAttr.append(sAttr);
			}
		}
		if (saveAttr.length() > 0) {
			SaveAttrsUtility.setSaveObjectsInHiddenColumn(row, saveAttr.toString());
		}
	}

	/**
	 * Prepare context and attrs for cell.
	 *
	 * @param poiCell
	 *            the poi cell
	 * @param fullName
	 *            the full name
	 * @param cellHelper
	 *            the cell helper
	 * @return the string
	 */
	public static String prepareContextAndAttrsForCell(Cell poiCell, String fullName, CellHelper cellHelper) {

		if (fullName == null) {
			return null;
		}
		String saveAttrList = SaveAttrsUtility.getSaveAttrListFromRow(poiCell.getRow());
		if (saveAttrList != null) {
			String saveAttr = SaveAttrsUtility.getSaveAttrFromList(poiCell.getColumnIndex(), saveAttrList);
			if (saveAttr != null) {
				cellHelper.restoreDataContext(fullName);
				return saveAttr;
			}

		}
		return null;
	}

}
 public final class WebSheetUtility {

	/** logger. */
	private static final Logger LOG = Logger
			.getLogger(WebSheetUtility.class.getName());

	// Each cell conatins a fixed number of co-ordinate points; this number
	// does not vary with row height or column width or with font. These two
	/** The Constant TOTAL_COLUMN_COORDINATE_POSITIONS. */
	// constants are defined below.
	public static final int TOTAL_COLUMN_COORDINATE_POSITIONS = 1023; // MB

	/** The Constant TOTAL_ROW_COORDINATE_POSITIONS. */
	public static final int TOTAL_ROW_COORDINATE_POSITIONS = 255; // MB
	// The resoultion of an image can be expressed as a specific number
	// of pixels per inch. Displays and printers differ but 96 pixels per
	/** The Constant PIXELS_PER_INCH. */
	// inch is an acceptable standard to beging with.
	public static final int PIXELS_PER_INCH = 96; // MB
	/** The Constant MILLIMETERS_PER_INCH. */
	public static final double MILLIMETERS_PER_INCH = 25.4;
	/** The Constant POINTS_PER_INCH. */
	public static final double POINTS_PER_INCH = 72D;
	// Cnstants that defines how many pixels and points there are in a
	/** The Constant PIXELS_PER_MILLIMETRES. */
	// millimetre. These values are required for the conversion algorithm.
	public static final double PIXELS_PER_MILLIMETRES = 3.78; // MB

	/** The Constant PICTURE_HEIGHT_ADJUST. */
	// These values are required for the conversion algorithm.
	public static final double PICTURE_HEIGHT_ADJUST = 3.03125; // MB

	/** The Constant POINTS_PER_MILLIMETRE. */
	public static final double POINTS_PER_MILLIMETRE = 2.83; // MB
	// The column width returned by HSSF and the width of a picture when
	// positioned to exactly cover one cell are different by almost exactly
	// 2mm - give or take rounding errors. This constant allows that
	// additional amount to be accounted for when calculating how many
	/** The Constant CELL_BORDER_WIDTH_MILLIMETRES. */
	// celles the image ought to overlie.
	public static final double CELL_BORDER_WIDTH_MILLIMETRES = 2.0D; // MB

	/** The Constant EXCEL_COLUMN_WIDTH_FACTOR. */
	public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;

	/** The Constant UNIT_OFFSET_LENGTH. */
	public static final int UNIT_OFFSET_LENGTH = 7;

	/** The Constant UNIT_OFFSET_MAP. */
	static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146,
			182, 219 };

	/** The Constant EXCEL_ROW_HEIGHT_FACTOR. */
	public static final short EXCEL_ROW_HEIGHT_FACTOR = 20;

	/** The Constant EMU_PER_MM. */
	public static final int EMU_PER_MM = 36000;

	/** The Constant EMU_PER_POINTS. */
	public static final int EMU_PER_POINTS = 12700;

	/** The Constant DATE_REGEX_YEAR_COMM_1. */
	private static final String DATE_REGEX_YEAR_COMM_1 = "([-/.\\\\]{1})";

	/** The Constant DATE_REGEX_YEAR_COMM_2. */
	private static final String DATE_REGEX_YEAR_COMM_2 = "[0?[1-9]|[1-9]|1[012]]{1,2}";

	/** The Constant DATE_REGEX_YEAR_COMM_3. */
	private static final String DATE_REGEX_YEAR_COMM_3 = "([0?[1-9]|[1-9]|1[0-9]|2[0-9]|3[01]]{1,2})";

	/** The Constant DATE_REGEX_4_DIGIT_YEAR. */
	private static final String DATE_REGEX_4_DIGIT_YEAR = "("
			+ "(19|20)[0-9]{2}" + DATE_REGEX_YEAR_COMM_1
			+ DATE_REGEX_YEAR_COMM_2 + "\\3" + DATE_REGEX_YEAR_COMM_3 + ")"
			+ "|" + "(" + DATE_REGEX_YEAR_COMM_2 + DATE_REGEX_YEAR_COMM_1
			+ DATE_REGEX_YEAR_COMM_3 + "\\6" + "(19|20)[0-9]{2}" + ")";

	/** The Constant DATE_REGEX_2_DIGIT_YEAR. */
	private static final String DATE_REGEX_2_DIGIT_YEAR = "(" + "[0-9]{2}"
			+ DATE_REGEX_YEAR_COMM_1 + DATE_REGEX_YEAR_COMM_2 + "\\3"
			+ DATE_REGEX_YEAR_COMM_3 + ")" + "|" + "("
			+ DATE_REGEX_YEAR_COMM_2 + DATE_REGEX_YEAR_COMM_1
			+ DATE_REGEX_YEAR_COMM_3 + "\\6" + "[0-9]{2}" + ")";

	/**
	 * PIXEL_HEIGHT_ASPC_ADJUST.
	 */
	private static final double PIXEL_HEIGHT_ASPC_ADJUST = 14;

	/**
	 * hide constructor.
	 */
	private WebSheetUtility() {
		// not called
	}

	/**
	 * Gets the excel column name.
	 *
	 * @param pnumber
	 *            the number
	 * @return the string
	 */
	public static String getExcelColumnName(final int pnumber) {
		StringBuilder converted = new StringBuilder();
		// Repeatedly divide the number by 26 and convert the
		// remainder into the appropriate letter.
		int number = pnumber;
		while (number >= 0) {
			int remainder = number % TieConstants.EXCEL_LETTER_NUMBERS;
			converted.insert(0, (char) (remainder + 'A'));
			number = (number / TieConstants.EXCEL_LETTER_NUMBERS) - 1;
		}

		return converted.toString();
	}

	/**
	 * return full name for cell with sheet name and $ format e.g. Sheet1$A$1
	 * 
	 * @param sheet1
	 *            sheet
	 * @param cell
	 *            cell
	 * @return String full cell reference name
	 */

	public static String getFullCellRefName(final Sheet sheet1,
			final Cell cell) {
		if ((sheet1 != null) && (cell != null)) {
			return sheet1.getSheetName() + "!$"
					+ getExcelColumnName(cell.getColumnIndex()) + "$"
					+ (cell.getRowIndex() + 1);
		}
		return null;
	}

	/**
	 * return full name for cell with sheet name and $ format e.g. Sheet1$A$1
	 *
	 * @param sheetName
	 *            the sheet name
	 * @param rowIndex
	 *            the row index
	 * @param colIndex
	 *            the col index
	 * @return String full cell reference name
	 */

	public static String getFullCellRefName(final String sheetName,
			final int rowIndex, final int colIndex) {
		if (sheetName != null) {
			return sheetName + "!$" + getExcelColumnName(colIndex) + "$"
					+ (rowIndex + 1);
		}
		return null;
	}

	/**
	 * return sheet name from cell full name e.g. return Sheet1 from Sheet1$A$1
	 *
	 * @param fullName
	 *            the full name
	 * @return String Sheet Name
	 */

	public static String getSheetNameFromFullCellRefName(
			final String fullName) {
		if ((fullName != null) && (fullName.contains("!"))) {
			return fullName.substring(0, fullName.indexOf('!'));
		}
		return null;
	}

	/**
	 * remove sheet name from cell full name e.g. return $A$1 from Sheet1$A$1
	 *
	 * @param fullName
	 *            the full name
	 * @return remove Sheet Name from full name
	 */

	public static String removeSheetNameFromFullCellRefName(
			final String fullName) {
		if ((fullName != null) && (fullName.contains("!"))) {
			return fullName.substring(fullName.indexOf('!') + 1);
		}
		return fullName;
	}

	/**
	 * Convert col to int.
	 *
	 * @param col
	 *            the col
	 * @return the int
	 */
	public static int convertColToInt(final String col) {
		String name = col.toUpperCase();
		int number = 0;
		int pow = 1;
		for (int i = name.length() - 1; i >= 0; i--) {
			number += (name.charAt(i) - 'A' + 1) * pow;
			pow *= TieConstants.EXCEL_LETTER_NUMBERS;
		}

		return number - 1;
	}

	/**
	 * Gets the cell by reference.
	 *
	 * @param cellRef
	 *            the cell ref
	 * @param sheet
	 *            the sheet
	 * @return the cell by reference
	 */
	public static Cell getCellByReference(final String cellRef,
			final Sheet sheet) {

		Cell c = null;
		try {
			CellReference ref = new CellReference(cellRef);
			Row r = sheet.getRow(ref.getRow());
			if (r != null) {
				c = r.getCell(ref.getCol(),
						MissingCellPolicy.CREATE_NULL_AS_BLANK);
			}
		} catch (Exception ex) {
			// use log.debug because mostly it's expected
			LOG.log(Level.SEVERE,
					"WebForm WebFormHelper getCellByReference cellRef = "
							+ cellRef + "; error = "
							+ ex.getLocalizedMessage(),
					ex);
		}
		return c;
	}

	/**
	 * pixel units to excel width units(units of 1/256th of a character width).
	 *
	 * @param pxs
	 *            the pxs
	 * @return the short
	 */
	public static short pixel2WidthUnits(final int pxs) {
		short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR
				* (pxs / UNIT_OFFSET_LENGTH));
		widthUnits += UNIT_OFFSET_MAP[pxs % UNIT_OFFSET_LENGTH];
		return widthUnits;
	}

	/**
	 * excel width units(units of 1/256th of a character width) to pixel units.
	 *
	 * @param widthUnits
	 *            the width units
	 * @return the int
	 */
	public static int widthUnits2Pixel(final int widthUnits) {
		int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR)
				* UNIT_OFFSET_LENGTH;
		int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
		pixels += Math.round(offsetWidthUnits
				/ ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));
		return pixels;
	}

	/**
	 * Height units 2 pixel.
	 *
	 * @param heightUnits
	 *            the height units
	 * @return the int
	 */
	public static int heightUnits2Pixel(final short heightUnits) {
		int pixels = heightUnits / EXCEL_ROW_HEIGHT_FACTOR;
		int offsetHeightUnits = heightUnits % EXCEL_ROW_HEIGHT_FACTOR;
		pixels += Math.round((float) offsetHeightUnits
				/ ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH
						/ 2));
		pixels += (Math.floor(pixels / PIXEL_HEIGHT_ASPC_ADJUST) + 1) * 4;

		return pixels;
	}

	/**
	 * Convert Excels width units into millimetres.
	 *
	 * @param widthUnits
	 *            The width of the column or the height of the row in Excels
	 *            units.
	 * @return A primitive double that contains the columns width or rows height
	 *         in millimetres.
	 */
	public static double widthUnits2Millimetres(final short widthUnits) {
		return widthUnits2Pixel(widthUnits) / PIXELS_PER_MILLIMETRES;
	}

	/**
	 * Convert into millimetres Excels width units..
	 *
	 * @param millimetres
	 *            A primitive double that contains the columns width or rows
	 *            height in millimetres.
	 * @return A primitive int that contains the columns width or rows height in
	 *         Excels units.
	 */
	public static int millimetres2WidthUnits(final double millimetres) {
		return pixel2WidthUnits(
				(int) (millimetres * PIXELS_PER_MILLIMETRES));
	}

	/**
	 * Points to pixels.
	 *
	 * @param points
	 *            the points
	 * @return the int
	 */
	public static int pointsToPixels(final double points) {
		return (int) Math.round(points / POINTS_PER_INCH * PIXELS_PER_INCH);
	}

	/**
	 * Points to millimeters.
	 *
	 * @param points
	 *            the points
	 * @return the double
	 */
	public static double pointsToMillimeters(final double points) {
		return points / POINTS_PER_INCH * MILLIMETERS_PER_INCH;
	}

	/**
	 * Checks if is date.
	 *
	 * @param s
	 *            the s
	 * @return true, if is date
	 */
	public static boolean isDate(final String s) {
		Pattern pattern = Pattern.compile(DATE_REGEX_4_DIGIT_YEAR);
		String[] terms = s.split(" ");
		Matcher matcher;
		for (String term : terms) {
			matcher = pattern.matcher(term);
			if (matcher.matches()) {
				return true;
			}
		}
		pattern = Pattern.compile(DATE_REGEX_2_DIGIT_YEAR);
		terms = s.split(" ");
		for (String term : terms) {
			matcher = pattern.matcher(term);
			if (matcher.matches()) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Parses the date.
	 *
	 * @param entry
	 *            the entry
	 * @return the string
	 */
	public static String parseDate(final String entry) {
		Pattern pattern = Pattern.compile(DATE_REGEX_4_DIGIT_YEAR);
		String[] terms = entry.split(" ");
		Matcher matcher;
		for (String term : terms) {
			matcher = pattern.matcher(term);
			if (matcher.matches()) {
				return matcher.group();
			}
		}
		pattern = Pattern.compile(DATE_REGEX_2_DIGIT_YEAR);
		terms = entry.split(" ");
		for (String term : terms) {
			matcher = pattern.matcher(term);
			if (matcher.matches()) {
				return matcher.group();
			}
		}
		return "";
	}

	/**
	 * Checks if is numeric.
	 *
	 * @param str
	 *            the str
	 * @return true, if is numeric
	 */
	public static boolean isNumeric(final String str) {

		String s = str;
		if (s.startsWith("-")) {
			s = s.substring(1);
		}
		char c;
		int i;
		int sLen = s.length();
		ShouldContinueParameter sPara = new ShouldContinueParameter(false,
				false, 0);

		for (i = 0; i < sLen; i++) {
			c = s.charAt(i);
			if (c < '0' || c > '9') {
				if (!shouldContinue(c, sPara)) {
					return false;
				}
			} else {
				if (sPara.isCommaHit()) {
					sPara.setSinceLastComma(sPara.getSinceLastComma() + 1);
				}
			}
		}
		return true;
	}

	/**
	 * The Class ShouldContinueParameter.
	 */
	private static class ShouldContinueParameter {

		/** The decimal hit. */
		private Boolean decimalHit;

		/** The comma hit. */
		private Boolean commaHit;

		/** The since last comma. */
		private Integer sinceLastComma;

		/**
		 * Instantiates a new should continue parameter.
		 *
		 * @param pdecimalHit
		 *            the decimal hit
		 * @param pcommaHit
		 *            the comma hit
		 * @param psinceLastComma
		 *            the since last comma
		 */
		ShouldContinueParameter(final Boolean pdecimalHit,
				final Boolean pcommaHit, final Integer psinceLastComma) {
			this.decimalHit = pdecimalHit;
			this.commaHit = pcommaHit;
			this.sinceLastComma = psinceLastComma;
		}

		/**
		 * Checks if is decimal hit.
		 *
		 * @return the boolean
		 */
		public Boolean isDecimalHit() {
			return decimalHit;
		}

		/**
		 * Sets the decimal hit.
		 *
		 * @param pdecimalHit
		 *            the new decimal hit
		 */
		public void setDecimalHit(final Boolean pdecimalHit) {
			this.decimalHit = pdecimalHit;
		}

		/**
		 * Checks if is comma hit.
		 *
		 * @return the boolean
		 */
		public Boolean isCommaHit() {
			return commaHit;
		}

		/**
		 * Sets the comma hit.
		 *
		 * @param pcommaHit
		 *            the new comma hit
		 */
		public void setCommaHit(final Boolean pcommaHit) {
			this.commaHit = pcommaHit;
		}

		/**
		 * Gets the since last comma.
		 *
		 * @return the since last comma
		 */
		public Integer getSinceLastComma() {
			return sinceLastComma;
		}

		/**
		 * Sets the since last comma.
		 *
		 * @param psinceLastComma
		 *            the new since last comma
		 */
		public void setSinceLastComma(final Integer psinceLastComma) {
			this.sinceLastComma = psinceLastComma;
		}
	}

	/**
	 * Should continue.
	 *
	 * @param c
	 *            the c
	 * @param para
	 *            the para
	 * @return true, if successful
	 */
	private static boolean shouldContinue(final char c,
			final ShouldContinueParameter para) {
		if (c == '.' && !para.isDecimalHit()) {
			para.setDecimalHit(true);
			if (para.isCommaHit() && para.getSinceLastComma() != 3) {
				return false;
			}
			return true;
		} else if (c == ',' && !para.isDecimalHit()) {
			if (para.isCommaHit()) {
				if (para.getSinceLastComma() != 3) {
					return false;
				}
				para.setSinceLastComma(0);
			}
			para.setCommaHit(true);
			return true;
		}
		return false;
	}

	/**
	 * Sets the object property.
	 *
	 * @param obj
	 *            the obj
	 * @param propertyName
	 *            the property name
	 * @param propertyValue
	 *            the property value
	 * @param ignoreNonExisting
	 *            the ignore non existing
	 */
	public static void setObjectProperty(final Object obj,
			final String propertyName, final String propertyValue,
			final boolean ignoreNonExisting) {
		try {
			Method method = obj.getClass().getMethod(
					"set" + Character.toUpperCase(propertyName.charAt(0))
							+ propertyName.substring(1),
					new Class[] { String.class });
			method.invoke(obj, propertyValue);
		} catch (Exception e) {
			String msg = "failed to set property '" + propertyName
					+ "' to value '" + propertyValue + "' for object "
					+ obj;
			if (ignoreNonExisting) {
				LOG.info(msg);
			} else {
				LOG.warning(msg);
				throw new IllegalArgumentException(e);
			}
		}
	}

	/**
	 * Cell compare to.
	 *
	 * @param thisCell
	 *            the this cell
	 * @param otherCell
	 *            the other cell
	 * @return the int
	 */
	public static int cellCompareTo(final Cell thisCell,
			final Cell otherCell) {
		int r = thisCell.getRowIndex() - otherCell.getRowIndex();
		if (r != 0) {
			return r;
		}

		r = thisCell.getColumnIndex() - otherCell.getColumnIndex();
		if (r != 0) {
			return r;
		}

		return 0;
	}

	/**
	 * Inside range.
	 *
	 * @param child
	 *            the child
	 * @param parent
	 *            the parent
	 * @return true, if successful
	 */
	public static boolean insideRange(final ConfigRange child,
			final ConfigRange parent) {

		return ((cellCompareTo(child.getFirstRowRef(),
				parent.getFirstRowRef()) >= 0)
				&& (cellCompareTo(child.getLastRowPlusRef(),
						parent.getLastRowPlusRef()) <= 0));
	}

	/**
	 * return the last column of the sheet.
	 * 
	 * @param sheet
	 *            sheet.
	 * @return last column number (A column will return 0).
	 */
	public static int getSheetRightCol(final Sheet sheet) {

		try {
			if (sheet instanceof XSSFSheet) {
				XSSFSheet xsheet = (XSSFSheet) sheet;
				int rightCol = getSheetRightColFromDimension(xsheet);
				if (rightCol > TieConstants.MAX_COLUMNS_IN_SHEET) {
					clearHiddenColumns(sheet);
					rightCol = getSheetRightColFromDimension(xsheet);
				}
				return rightCol;

			}
		} catch (Exception e) {
			LOG.log(Level.SEVERE, "error in getSheetRightCol : "
					+ e.getLocalizedMessage(), e);
		}
		return -1;
	}

	/**
	 * Clear hidden columns.
	 *
	 * @param sheet
	 *            the sheet
	 */
	public static void clearHiddenColumns(final Sheet sheet) {

		for (Row row : sheet) {
			if (row.getLastCellNum() > TieConstants.MAX_COLUMNS_IN_SHEET) {
				deleteHiddenColumnsInRow(row);
			}
		}

	}

	/**
	 * Delete hidden columns in row.
	 *
	 * @param row
	 *            the row
	 */
	private static void deleteHiddenColumnsInRow(final Row row) {
		deleteCellFromRow(row,
				TieConstants.HIDDEN_SAVE_OBJECTS_COLUMN);
		deleteCellFromRow(row,
				TieConstants.HIDDEN_ORIGIN_ROW_NUMBER_COLUMN);
		deleteCellFromRow(row,
				TieConstants.HIDDEN_FULL_NAME_COLUMN);
	}

	
	
	
	/**
	 * Delete cell from row.
	 *
	 * @param row
	 *            the row
	 * @param cellNum
	 *            the cell num
	 */
	private static void deleteCellFromRow(final Row row,
			final int cellNum) {
		Cell cell = row.getCell(cellNum);
		if (cell != null) {
			row.removeCell(cell);
		}
	}

	/**
	 * return the last column of the sheet.
	 *
	 * @param xsheet
	 *            the xsheet
	 * @return last column number (A column will return 0).
	 */
	private static int getSheetRightColFromDimension(
			final XSSFSheet xsheet) {
		CTSheetDimension dimension = xsheet.getCTWorksheet().getDimension();
		String sheetDimensions = dimension.getRef();
		if (sheetDimensions.indexOf(':') < 0) {
			return -1;
		} else {
			return CellRangeAddress.valueOf(sheetDimensions)
					.getLastColumn();
		}
	}
}
 public class AddRowException extends RuntimeException {

	/** The Constant serialVersionUID. */
	private static final long serialVersionUID = 1L;

	/**
	 * Instantiates a new evaluation exception.
	 */
	public AddRowException() {
		super();
	}

	/**
	 * Instantiates a new evaluation exception.
	 *
	 * @param message
	 *            the message
	 * @param cause
	 *            the cause
	 */
	public AddRowException(final String message, final Throwable cause) {
		super(message, cause);
	}

    /**
     * Instantiates a new evaluation exception.
     *
     * @param message
     *            the message
     */
    public AddRowException(final String message) {
        super(message);
    }

 
}
 public class DeleteRowException extends RuntimeException {

	/** The Constant serialVersionUID. */
	private static final long serialVersionUID = 1L;

	/**
	 * Instantiates a new evaluation exception.
	 */
	public DeleteRowException() {
		super();
	}

	/**
	 * Instantiates a new evaluation exception.
	 *
	 * @param message
	 *            the message
	 * @param cause
	 *            the cause
	 */
	public DeleteRowException(final String message, final Throwable cause) {
		super(message, cause);
	}

    /**
     * Instantiates a new evaluation exception.
     *
     * @param message
     *            the message
     */
    public DeleteRowException(final String message) {
        super(message);
    }

 
}

import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.component.datatable.DataTable;
import org.primefaces.context.RequestContext;
import org.tiefaces.common.FacesUtility;
import org.tiefaces.common.TieConstants;
import org.tiefaces.components.websheet.TieWebSheetBean;
import org.tiefaces.components.websheet.TieWebSheetView.TabModel;
import org.tiefaces.components.websheet.configuration.ConfigBuildRef;
import org.tiefaces.components.websheet.configuration.ConfigurationHandler;
import org.tiefaces.components.websheet.configuration.RangeBuildRef;
import org.tiefaces.components.websheet.configuration.RowsMapping;
import org.tiefaces.components.websheet.configuration.SheetConfiguration;
import org.tiefaces.components.websheet.dataobjects.FacesCell;
import org.tiefaces.components.websheet.dataobjects.FacesRow;
import org.tiefaces.components.websheet.dataobjects.HeaderCell;
import org.tiefaces.components.websheet.dataobjects.TieCell;
import org.tiefaces.components.websheet.utility.CellStyleUtility;
import org.tiefaces.components.websheet.utility.CellUtility;
import org.tiefaces.components.websheet.utility.CommandUtility;
import org.tiefaces.components.websheet.utility.ConfigurationUtility;
import org.tiefaces.components.websheet.utility.SaveAttrsUtility;
import org.tiefaces.components.websheet.utility.WebSheetUtility;
import org.tiefaces.exception.AddRowException;
import org.tiefaces.exception.DeleteRowException;

/*
 * Copyright 2017 TieFaces.
 * Licensed under MIT
 */

package org.tiefaces.components.websheet.service;




/**
 * The Class WebSheetLoader.
 */
public class WebSheetLoader implements Serializable {

	/** The parent. */
	private TieWebSheetBean parent = null;

	/** logger. */
	private static final Logger LOG = Logger.getLogger(WebSheetLoader.class.getName());

	/** The Constant serialVersionUID. */
	private static final long serialVersionUID = 1L;

	/**
	 * Instantiates a new web sheet loader.
	 *
	 * @param pParent
	 *            the parent
	 */
	public WebSheetLoader(final TieWebSheetBean pParent) {
		this.parent = pParent;
		LOG.fine("TieWebSheetLoader Constructor");
	}

	/**
	 * Load header rows.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @param cellRangeMap
	 *            the cell range map
	 * @param skippedRegionCells
	 *            the skipped region cells
	 */
	private void loadHeaderRows(final SheetConfiguration sheetConfig, final Map<String, CellRangeAddress> cellRangeMap,
			final List<String> skippedRegionCells) {

		int top = sheetConfig.getHeaderCellRange().getTopRow();
		int bottom = sheetConfig.getHeaderCellRange().getBottomRow();
		int left = sheetConfig.getHeaderCellRange().getLeftCol();
		int right = sheetConfig.getHeaderCellRange().getRightCol();

		String sheetName = sheetConfig.getSheetName();
		Sheet sheet1 = parent.getWb().getSheet(sheetName);


		int totalWidth = CellStyleUtility.calcTotalWidth(sheet1, left, right,
