public class ChartHelper {

	/** instance to parent websheet bean. */
	private TieWebSheetBean parent = null;

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

	/**
	 * Instantiates a new chart helper.
	 *
	 * @param pParent
	 *            parent websheet bean
	 */
	public ChartHelper(final TieWebSheetBean pParent) {
		this.parent = pParent;
		LOG.fine("ChartHelper Constructor");
	}

	/**
	 * initial charts map for current workbook.
	 */
	public final void loadChartsMap() {

		this.initChartsMap(parent.getWb());
	}

	/**
	 * initial chart map for specified workbook.
	 * 
	 * @param wb
	 *            specified workbook.
	 */
	private void initChartsMap(final Workbook wb) {
		try {
			if (wb instanceof XSSFWorkbook) {
				initXSSFChartsMap((XSSFWorkbook) wb, parent.getCharsData());
			}
		} catch (Exception e) {
			LOG.log(Level.SEVERE, "getChartsMap Error Exception = "
					+ e.getLocalizedMessage(), e);
		}
	}

	/**
	 * return cell Value for paredCell. ParsedCell contain sheetName/row/col
	 * which normally is parsed from String like: Sheet1!B2 .
	 * 
	 * @param pCell
	 *            parsed cell.
	 * @return cell string value without format.
	 */
	public final String getParsedCellValue(final ParsedCell pCell) {

		String result = "";
		try {
			Cell poiCell = parent.getWb().getSheet(pCell.getSheetName())
					.getRow(pCell.getRow()).getCell(pCell.getCol());
			result = CellUtility.getCellValueWithoutFormat(poiCell);

		} catch (Exception ex) {
			LOG.log(Level.FINE,
					"error getParsedCellValue :" + ex.getLocalizedMessage(),
					ex);
		}
		return result;

	}

	/**
	 * create default category dataset for JfreeChart with giving chartData.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @return DefaultCategoryDataset for jfreechart.
	 */

	private DefaultPieDataset createPieDataset(final ChartData chartData) {
		DefaultPieDataset dataset = new DefaultPieDataset();
		List<ParsedCell> categoryList = chartData.getCategoryList();
		for (ChartSeries chartSeries : chartData.getSeriesList()) {
			List<ParsedCell> valueList = chartSeries.getValueList();
			for (int i = 0; i < categoryList.size(); i++) {
				try {
					String sCategory = getParsedCellValue(
							categoryList.get(i));
					String sValue = getParsedCellValue(valueList.get(i));
					dataset.setValue(sCategory, Double.parseDouble(sValue));
				} catch (Exception ex) {
					LOG.log(Level.FINE, "error in creatPieDataset : "
							+ ex.getLocalizedMessage(), ex);
				}
			}
		}
		return dataset;

	}

	/**
	 * return pie chart title from chartData.
	 *
	 * @param chartData
	 *            ChartData object.
	 * @return title (String).
	 */
	private String getPieTitle(final ChartData chartData) {
		for (ChartSeries chartSeries : chartData.getSeriesList()) {
			if (chartSeries != null) {
				return getParsedCellValue(chartSeries.getSeriesLabel());
			}	
		}
		return "";

	}

	/**
	 * Create jfree chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @return jfree line chart.
	 */

	private JFreeChart createChart(final ChartData chartData) {

		return chartData.getType().createChart(this, chartData);

	}

	/**
	 * Set color of series.
	 * 
	 * @param chart
	 *            JFreeChart.
	 * @param seriesIndex
	 *            Index of series to set color of (0 = first series)
	 * @param style
	 *            One of STYLE_xxx.
	 */
	public final void setSeriesStyle(final JFreeChart chart,
			final int seriesIndex, final String style) {
		if (chart != null && style != null) {
			BasicStroke stroke = ChartUtility.toStroke(style);

			Plot plot = chart.getPlot();
			if (plot instanceof CategoryPlot) {
				CategoryPlot categoryPlot = chart.getCategoryPlot();
				CategoryItemRenderer cir = categoryPlot.getRenderer();
				try {
					cir.setSeriesStroke(seriesIndex, stroke); // series line
																// style
				} catch (Exception e) {
					LOG.log(Level.SEVERE,
							"Error setting style '" + style
									+ "' for series '"
									+ Integer.toString(seriesIndex)
									+ "' of chart '" + chart.toString()
									+ "': " + e.getLocalizedMessage(),
							e);
				}
			} else if (plot instanceof XYPlot) {
				XYPlot xyPlot = chart.getXYPlot();
				XYItemRenderer xyir = xyPlot.getRenderer();
				try {
					xyir.setSeriesStroke(seriesIndex, stroke); // series line
																// style
				} catch (Exception e) {
					LOG.log(Level.SEVERE,
							"Error setting style '" + style
									+ "' for series '"
									+ Integer.toString(seriesIndex)
									+ "' of chart '" + chart.toString()
									+ "': " + e.getLocalizedMessage(),
							e);
				}
			} else {
				LOG.log(Level.FINE, "setSeriesColor() unsupported plot: {}",
						plot.toString());
			}
		}
	}

	/**
	 * Create jfree line chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @return jfree line chart.
	 */
	public JFreeChart createLineChart(final ChartData chartData) {

		// create the chart...
		final JFreeChart chart = ChartFactory.createLineChart(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				PlotOrientation.VERTICAL, // orientation
				true, // include legend
				false, // tooltips
				false // urls
		);

		setupStyle(chart, chartData);

		return chart;

	}

	/**
	 * Create jfree bar chart.
	 *
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @return jfree line chart.
	 */
	public JFreeChart createAreaChart(final ChartData chartData) {
		PlotOrientation orientation = PlotOrientation.VERTICAL;
		// create the chart...
		final JFreeChart chart = ChartFactory.createAreaChart(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				orientation, true, // include legend
				false, // tooltips
				false // urls
		);

		setupStyle(chart, chartData);

		return chart;

	}

	/**
	 * create stacked area chart.
	 * 
	 * @param chartData
	 *            chart data.
	 * @return jfree chart.
	 */
	public JFreeChart createStackedAreaChart(final ChartData chartData) {
		PlotOrientation orientation = PlotOrientation.VERTICAL;
		// create the chart...
		final JFreeChart chart = ChartFactory.createStackedAreaChart(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				orientation, true, // include legend
				false, // tooltips
				false // urls
		);

		setupStyle(chart, chartData);

		return chart;

	}

	/**
	 * Create jfree bar chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @param vertical
	 *            chart orientation.
	 * @return jfree line chart.
	 */
	public JFreeChart createBarChart(final ChartData chartData,
			final boolean vertical) {

		PlotOrientation orientation = PlotOrientation.VERTICAL;
		if (!vertical) {
			orientation = PlotOrientation.HORIZONTAL;
		}
		// create the chart...
		final JFreeChart chart = ChartFactory.createBarChart(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				orientation, true, // include legend
				false, // tooltips
				false // urls
		);

		setupBarStyle(chart, chartData);

		return chart;

	}

	/**
	 * Create jfree bar chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @param vertical
	 *            chart orientation.
	 * @return jfree line chart.
	 */
	public JFreeChart createStackedBarChart(final ChartData chartData,
			final boolean vertical) {

		PlotOrientation orientation = PlotOrientation.VERTICAL;
		if (!vertical) {
			orientation = PlotOrientation.HORIZONTAL;
		}
		// create the chart...
		final JFreeChart chart = ChartFactory.createStackedBarChart(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				orientation, true, // include legend
				false, // tooltips
				false // urls
		);

		setupBarStyle(chart, chartData);

		return chart;

	}

	/**
	 * Create jfree bar chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @return jfree line chart.
	 */
	public JFreeChart createPieChart(final ChartData chartData) {

		// create the chart...
		final JFreeChart chart = ChartFactory.createPieChart(
				getPieTitle(chartData), // chart title
				createPieDataset(chartData), // data
				true, // include legend
				false, // tooltips
				false // urls
		);

		setupPieStyle(chart, chartData);

		return chart;

	}

	/**
	 * create pie 3d chart.
	 * 
	 * @param chartData
	 *            chart data.
	 * @return jfreechart.
	 */
	public JFreeChart createPie3DChart(final ChartData chartData) {

		// create the chart...
		final JFreeChart chart = ChartFactory.createPieChart3D(
				getPieTitle(chartData), // chart title
				createPieDataset(chartData), // data
				true, // include legend
				false, // tooltips
				false // urls
		);

		setupPieStyle(chart, chartData);

		return chart;

	}

	/**
	 * Create jfree bar chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @param vertical
	 *            chart orientation.
	 * @return jfree line chart.
	 */
	public JFreeChart createBarChart3D(final ChartData chartData,
			final boolean vertical) {

		PlotOrientation orientation = PlotOrientation.VERTICAL;
		if (!vertical) {
			orientation = PlotOrientation.HORIZONTAL;
		}
		// create the chart...
		final JFreeChart chart = ChartFactory.createBarChart3D(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				orientation, true, // include legend
				false, // tooltips
				false // urls
		);

		setupBarStyle(chart, chartData);

		return chart;

	}

	/**
	 * Create jfree bar chart.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @param vertical
	 *            chart orientation.
	 * @return jfree line chart.
	 */
	public JFreeChart createStackedBarChart3D(final ChartData chartData,
			final boolean vertical) {

		PlotOrientation orientation = PlotOrientation.VERTICAL;
		if (!vertical) {
			orientation = PlotOrientation.HORIZONTAL;
		}
		// create the chart...
		final JFreeChart chart = ChartFactory.createStackedBarChart3D(
				chartData.getTitle(), // chart title
				chartData.getCatAx().getTitle(), // x axis label
				chartData.getValAx().getTitle(), // y axis label
				createDataset(chartData), // data
				orientation, true, // include legend
				false, // tooltips
				false // urls
		);

		setupBarStyle(chart, chartData);

		return chart;

	}

	/**
	 * finalize the style for jfreechart. The default setting is different from
	 * jfreechart and Excel. We try to minimize the difference.
	 * 
	 * @param chart
	 *            jfreechart.
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 */

	private void setupStyle(final JFreeChart chart,
			final ChartData chartData) {
		CategoryPlot plot = (CategoryPlot) chart.getPlot();
		List<ChartSeries> seriesList = chartData.getSeriesList();
		BasicStroke bLine = new BasicStroke(2.0f);
		for (int i = 0; i < seriesList.size(); i++) {
			Color cColor = ColorUtility.xssfClrToClr(
					seriesList.get(i).getSeriesColor().getXssfColor());
			plot.getRenderer().setSeriesPaint(i, cColor);
			plot.getRenderer().setSeriesStroke(i, bLine);
		}
		plot.setBackgroundPaint(ColorUtility
				.xssfClrToClr(chartData.getBgColor().getXssfColor()));

		// below are modifications for default setting in excel chart
		// to-do: need read setting from xml in future
		plot.setOutlineVisible(false);
		plot.setRangeGridlinesVisible(true);
		plot.setRangeGridlinePaint(Color.BLACK);
		plot.setRangeGridlineStroke(
				new BasicStroke(TieConstants.DEFAULT_BASIC_STROKE));
		plot.setRangeAxisLocation(AxisLocation.BOTTOM_OR_LEFT);
		chart.setBackgroundPaint(Color.WHITE);
		LegendTitle legend = chart.getLegend();
		legend.setPosition(RectangleEdge.RIGHT);
		legend.setFrame(BlockBorder.NONE);

	}

	/**
	 * finalize the style for jfreechart. The default setting is different from
	 * jfreechart and Excel. We try to minimize the difference.
	 * 
	 * @param chart
	 *            jfreechart.
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 */

	private void setupPieStyle(final JFreeChart chart,
			final ChartData chartData) {
		PiePlot plot = (PiePlot) chart.getPlot();
		List<ChartSeries> seriesList = chartData.getSeriesList();
		List<ParsedCell> categoryList = chartData.getCategoryList();
		BasicStroke bLine = new BasicStroke(2.0f);
		for (int i = 0; i < seriesList.size(); i++) {
			ChartSeries chartSeries = seriesList.get(i);
			List<XColor> valueColorList = chartSeries.getValueColorList();
			for (int index = 0; index < categoryList.size(); index++) {
				try {
					String sCategory = getParsedCellValue(
							categoryList.get(index));
					Color cColor = ColorUtility.xssfClrToClr(
							valueColorList.get(index).getXssfColor());
					plot.setSectionPaint(sCategory, cColor);
					plot.setSectionOutlineStroke(sCategory, bLine);
				} catch (Exception ex) {
					LOG.log(Level.FINE, "SetupPieStyle error = "
							+ ex.getLocalizedMessage(), ex);
				}
			}

		}
		plot.setBackgroundPaint(ColorUtility
				.xssfClrToClr(chartData.getBgColor().getXssfColor()));

		// below are modifications for default setting in excel chart
		// to-do: need read setting from xml in future

		plot.setOutlineVisible(false);
		plot.setLegendItemShape(
				new Rectangle(TieConstants.DEFAULT_LEGENT_ITEM_SHAPE_WIDTH,
						TieConstants.DEFAULT_LEGENT_ITEM_SHAPE_HEIGHT));
		chart.setBackgroundPaint(Color.WHITE);
		LegendTitle legend = chart.getLegend();
		legend.setPosition(RectangleEdge.RIGHT);
		legend.setFrame(BlockBorder.NONE);

	}

	/**
	 * finalize the style for barchart. This will call setupStyle common first.
	 * 
	 * @param chart
	 *            jfreechart.
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 */

	private void setupBarStyle(final JFreeChart chart,
			final ChartData chartData) {
		setupStyle(chart, chartData);
		CategoryPlot plot = (CategoryPlot) chart.getPlot();
		BarRenderer renderer = (BarRenderer) plot.getRenderer();
		renderer.setBarPainter(new StandardBarPainter());
		renderer.setItemMargin(TieConstants.DEFAULT_BAR_STYLE_ITEM_MARGIN);

		plot.setForegroundAlpha(
				TieConstants.DEFAULT_BARSTYLE_FOREGROUND_ALPHA);
	}

	/**
	 * create default category dataset for JfreeChart with giving chartData.
	 * 
	 * @param chartData
	 *            contain information gathered from excel chart object.
	 * @return DefaultCategoryDataset for jfreechart.
	 */

	private DefaultCategoryDataset createDataset(
			final ChartData chartData) {
		DefaultCategoryDataset dataset = new DefaultCategoryDataset();
		List<ParsedCell> categoryList = chartData.getCategoryList();
		for (ChartSeries chartSeries : chartData.getSeriesList()) {
			String seriesLabel = getParsedCellValue(
					chartSeries.getSeriesLabel());
			List<ParsedCell> valueList = chartSeries.getValueList();
			for (int i = 0; i < categoryList.size(); i++) {
				try {
					String sCategory = getParsedCellValue(
							categoryList.get(i));
					String sValue = getParsedCellValue(valueList.get(i));
					dataset.addValue(Double.parseDouble(sValue),
							seriesLabel, sCategory);
				} catch (Exception ex) {
					LOG.log(Level.FINE, "error in creatDataset : "
							+ ex.getLocalizedMessage(), ex);
				}
			}
		}
		return dataset;

	}

	/**
	 * initial chart map for XSSF format file. XSSF file is actually the only
	 * format in POI support chart object.
	 *
	 * @param wb
	 *            xssf workbook.
	 * @param chartsData
	 *            the charts data
	 */
	private void initXSSFChartsMap(final XSSFWorkbook wb,
			final ChartsData chartsData) {

		initAnchorsMap(wb, chartsData);
		Map<String, ClientAnchor> anchorMap = chartsData
				.getChartAnchorsMap();

		Map<String, BufferedImage> chartMap = chartsData.getChartsMap();
		Map<String, ChartData> chartDataMap = chartsData.getChartDataMap();
		chartMap.clear();
		chartDataMap.clear();

		for (int i = 0; i < wb.getNumberOfSheets(); i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			List<XSSFChart> charts = drawing.getCharts();
			if ((charts != null) && (!charts.isEmpty())) {
				for (XSSFChart chart : charts) {
					generateSingleXSSFChart(chart,
							getChartIdFromParent(chart,
									sheet.getSheetName()),
							sheet, anchorMap, chartMap, chartDataMap);
				}
			}
		}

	}

	/**
	 * Gets the chart id from parent.
	 *
	 * @param chart
	 *            the chart
	 * @param sheetName
	 *            the sheet name
	 * @return the chart id from parent
	 */
	private String getChartIdFromParent(final XSSFChart chart,
			final String sheetName) {
		if (chart.getParent() != null) {
			for (RelationPart rp : chart.getParent().getRelationParts()) {
				if (rp.getDocumentPart() == chart) {
					return sheetName + "!" + rp.getRelationship().getId();
				}
			}
		}
		return null;
	}

	/**
	 * initial anchors map for specified workbook. Excel put the chart position
	 * information in draw.xml instead of chart.xml. anchors map contains the
	 * information getting from draw.xml.
	 *
	 * @param wb
	 *            specified workbook.
	 * @param chartsData
	 *            the charts data
	 */
	private void initAnchorsMap(final Workbook wb,
			final ChartsData chartsData) {
		try {
			if (wb instanceof XSSFWorkbook) {
				ChartUtility.initXSSFAnchorsMap((XSSFWorkbook) wb,
						chartsData);
			}
		} catch (Exception e) {
			LOG.log(Level.SEVERE,
					"Web Form getAnchorsMap Error Exception = "
							+ e.getLocalizedMessage(),
					e);
		}
	}

	/**
	 * Generate single XSSF chart.
	 * 
	 * @param chart
	 *            the chart
	 * @param chartId
	 *            the chart id
	 * @param sheet
	 *            the sheet
	 * @param anchorMap
	 *            the anchor map
	 * @param chartMap
	 *            the chart map
	 * @param chartDataMap
	 *            the chart data map
	 */
	private void generateSingleXSSFChart(final XSSFChart chart,
			final String chartId, final XSSFSheet sheet,
			final Map<String, ClientAnchor> anchorMap,
			final Map<String, BufferedImage> chartMap,
			final Map<String, ChartData> chartDataMap) {
		ClientAnchor anchor;
		try {
			anchor = anchorMap.get(chartId);
			if (anchor != null) {
				ChartData chartData = ChartUtility
						.initChartDataFromXSSFChart(chartId, chart,
								(XSSFWorkbook) parent.getWb());
				chartDataMap.put(chartId, chartData);
				JFreeChart jchart = createChart(chartData);
				if (jchart != null) {
					AnchorSize anchorSize = PicturesUtility
							.getAnchorSize(sheet, null, null, anchor);
					BufferedImage img = jchart.createBufferedImage(
							anchorSize.getWidth(), anchorSize.getHeight());
					chartMap.put(chartId, img);
				}
			}
		} catch (Exception ex) {
			LOG.log(Level.SEVERE, "generate chart for " + chartId
					+ " error = " + ex.getLocalizedMessage(), ex);
		}
	}

}
 public class ChartsData  {
	/** hold chart data for current display sheet. */
	private Map<String, ChartData> chartDataMap;
	/**
	 * hold charts for current display sheet. each chart is a image generated by
	 * jfreechart.
	 */
	private Map<String, BufferedImage> chartsMap;
	/** hold chart anchor for each chart in current display sheet. */
	private Map<String, ClientAnchor> chartAnchorsMap;
	/**
	 * Index map for chartAnchorsMap. This map's key is chart top left cell
	 * position. e.g. key = Sheet1!A1 value = chartId of chartAnchorsMap.
	 */
	private Map<String, String> chartPositionMap;


	/**
	 * return chartdatamap.
	 * 
	 * @return chartdatamap.
	 */
	public final Map<String, ChartData> getChartDataMap() {
		if (this.chartDataMap == null) {
			this.chartDataMap = new HashMap<>();
		}
		return chartDataMap;
	}

	/**
	 * return chartsmap.
	 * 
	 * @return chartsmap.
	 */

	public final Map<String, BufferedImage> getChartsMap() {
		if (this.chartsMap == null) {
			this.chartsMap = new HashMap<>();
		}		
		
		return chartsMap;
	}

	/**
	 * return chartanchorsmap.
	 * 
	 * @return chartanchorsmap.
	 */

	public final Map<String, ClientAnchor> getChartAnchorsMap() {
		if (this.chartAnchorsMap == null) {
			this.chartAnchorsMap = new HashMap<>();
		}
		
		return chartAnchorsMap;
	}

	/**
	 * return chartpositionmap.
	 * 
	 * @return chartpositionmap.
	 */
	public final Map<String, String> getChartPositionMap() {
		if (this.chartPositionMap == null) {
			this.chartPositionMap = new HashMap<>();
		}
		
		return chartPositionMap;
	}

	/**
	 * set chartdatamap.
	 * 
	 * @param pchartDataMap
	 *            chartdatamap.
	 */

	public final void setChartDataMap(
			final Map<String, ChartData> pchartDataMap) {
		this.chartDataMap = pchartDataMap;
	}



	/**
	 * set chartanchors map.
	 * 
	 * @param pchartAnchorsMap
	 *            chartanchorsmap.
	 */
	public final void setChartAnchorsMap(
			final Map<String, ClientAnchor> pchartAnchorsMap) {
		this.chartAnchorsMap = pchartAnchorsMap;
	}

	/**
	 * set chartpositionmap.
	 * 
	 * @param pchartPositionMap
	 *            chartpositionmap.
	 */
	public final void setChartPositionMap(
			final Map<String, String> pchartPositionMap) {
		this.chartPositionMap = pchartPositionMap;
	}

} public class ExpressionEngine {
	/** expression. */
	private Expression jExpression;

	/**
	 * context. private JexlContext jContext;
	 */
	/** jexlEngine local map. */
	private static final ThreadLocal<JexlEngine> 
	JEXL_LOCAL = new ThreadLocal<JexlEngine>() {
		@Override
		protected JexlEngine initialValue() {
			return new JexlEngine();
		}
	};
	/** jexpmap local map. */
	private static final ThreadLocal<Map<String, Expression>> 
	JEXL_MAP_LOCAL = new ThreadLocal<Map<String, Expression>>() {
		@Override
		protected Map<String, Expression> initialValue() {
			return new HashMap<>();
		}
	};

	/**
	 * empty context used for evaluate single script.
	 */
	private static final Map<String, Object> 
	EMPTY_CONTEXT = new HashMap<>();

	/**
	 * constructor.
	 * 
	 * @param pExpression
	 *            expression.
	 */
	public ExpressionEngine(final String pExpression) {
		JexlEngine jexl = JEXL_LOCAL.get();
		jExpression = jexl.createExpression(pExpression);
	}



	/**
	 * constructor.
	 */
	public ExpressionEngine() {
		super();
	}

	/**
	 * Evaluate the expression.
	 *
	 * @param expression
	 *            string input expression.
	 * @return evaluated object.
	 */
	public final Object evaluate(final String expression) {
		return evaluate(expression, EMPTY_CONTEXT);
	}

	/**
	 * Evaluate the expression.
	 * 
	 * @param expression
	 *            string input expression.
	 * @param context
	 *            context map.
	 * @return evaluated object.
	 */
	public final Object evaluate(final String expression,
			final Map<String, Object> context) {
		JexlContext jexlContext = new MapContext(context);
		try {
			JexlEngine jexl = JEXL_LOCAL.get();
			Map<String, Expression> expMap = JEXL_MAP_LOCAL.get();
			Expression jexlExpression = expMap.get(expression);
			if (jexlExpression == null) {
				jexlExpression = jexl.createExpression(expression);
				expMap.put(expression, jexlExpression);
			}
			return jexlExpression.evaluate(jexlContext);
		} catch (Exception e) {
			throw new EvaluationException(e);
		}
	}

	/**
	 * evaluate from giving context.
	 * 
	 * @param context
	 *            context.
	 * @return object evaluated.
	 */
	public final Object evaluate(final Map<String, Object> context) {
		JexlContext jexlContext = new MapContext(context);
		try {
			return jExpression.evaluate(jexlContext);
		} catch (Exception e) {
			throw new EvaluationException(
					"An error occurred when evaluating expression "
							+ jExpression.getExpression(), e);
		}
	}

	/**
	 * get jexpression.
	 * 
	 * @return jexlexpression.
	 */

	public final Expression getJexlExpression() {
		return jExpression;
	}

	/**
	 * get local exl engine.
	 * 
	 * @return jexlengine.
	 */
	public final JexlEngine getJexlEngine() {
		return JEXL_LOCAL.get();
	}

}
 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 CachedCells implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = 916959757743324812L;

	/** The cached map. */
	private HashMap<Cell, FormulaMapping> cachedMap = new HashMap<>();

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

	/**
	 * Instantiates a new cached cells.
	 *
	 * @param pparent
	 *            the parent
	 */
	public CachedCells(final TieWebSheetBean pparent) {
		this.parent = pparent;
	}

	/**
	 * Put.
	 *
	 * @param cell
	 *            the cell
	 * @param formula
	 *            the cell type
	 */
	public final void put(final Cell cell, final CellType formula) {
		Map<Cell, FormulaMapping> map = cachedMap;
		// if cellType not null then only specified Type will be put into Cache
		// e.g. only formula cell will be cached then pass in
		// Cell.CELL_TYPE_FORMULA
		if ((cell != null) && ((formula == null)
				|| (cell.getCellTypeEnum() == formula))) {
			String value = CellUtility.getCellValueWithFormat(cell,
					parent.getFormulaEvaluator(),
					parent.getDataFormatter());
			FormulaMapping f = map.get(cell);
			if (f == null) {
				f = new FormulaMapping();
			}
			f.setValue(value);
			map.put(cell, f);
		}
	}

	/**
	 * Put.
	 *
	 * @param cell
	 *            the cell
	 * @param originFormula
	 *            the origin formula
	 */
	public final void put(final Cell cell, final String originFormula) {
		Map<Cell, FormulaMapping> map = cachedMap;
		// if cellType not null then only specified Type will be put into Cache
		// e.g. only formula cell will be cached then pass in
		// Cell.CELL_TYPE_FORMULA
		if ((cell != null) && (originFormula != null)) {
			FormulaMapping f = map.get(cell);
			if (f == null) {
				f = new FormulaMapping();
			}
			f.setOriginFormula(originFormula);
			String value = CellUtility.getCellValueWithFormat(cell,
					parent.getFormulaEvaluator(),
					parent.getDataFormatter());
			f.setValue(value);
			map.put(cell, f);
		}
	}

	/**
	 * Gets the value.
	 *
	 * @param cell
	 *            the cell
	 * @return the value
	 */
	public final String getValue(final Cell cell) {
		return cachedMap.get(cell).getValue();
	}

	/**
	 * Clear.
	 */
	public final void clear() {
		cachedMap.clear();
	}

	/**
	 * Checks if is value changed.
	 * 
	 * @param cell
	 *            the cell
	 *
	 * @return true, if is value changed
	 */
	public final boolean isValueChanged(final Cell cell) {
		String newValue = CellUtility.getCellValueWithFormat(cell,
				parent.getFormulaEvaluator(), parent.getDataFormatter());
		return isValueChanged(cell, newValue);
	}

	/**
	 * Checks if is value changed.
	 * 
	 * @param cell
	 *            the cell
	 * @param pnewValue
	 *            the new value
	 *
	 * @return true, if is value changed
	 */
	public final boolean isValueChanged(final Cell cell,
			final String pnewValue) {
		Map<Cell, FormulaMapping> map = cachedMap;
		String oldValue = map.get(cell).getValue();
		String newValue = pnewValue;
		if (oldValue == null) {
			oldValue = "";
		}
		if (newValue == null) {
			newValue = "";
		}
		return (!oldValue.equals(newValue));
	}

	/**
	 * Gets the cached map.
	 *
	 * @return the cached map
	 */
	public final Map<Cell, FormulaMapping> getCachedMap() {
		return cachedMap;
	}

}
 public class CellAttributesMap implements Serializable {
	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = -1080211494689904695L;

	/**
	 * hold comments for the current sheet config form. key is $$ - for normal
	 * comment $ - empty method comment $xxx -- method comment while xxx is
	 * method name. (now only one method exist: init). i.e. comment could be
	 * $init{department.name} , so here init is the method name. value is the
	 * comments without tie commands.
	 */
	private Map<String, Map<String, String>> templateCommentMap;

	/**
	 * hold input type for input control widget. key is
	 * sheetName!$rowindex$columnindex value is the control type. e.g. dropdown
	 * radiobox calendar
	 */
	private Map<String, String> cellInputType;

	/**
	 * hold attributes for input control widget. key is
	 * sheetName!$rowindex$columnindex value is the list of attributes.
	 */
	private Map<String, List<CellFormAttributes>> cellInputAttributes;

	/**
	 * hold attributes for selectItems of dropdown list or radio box. key is
	 * sheetName!$rowindex$columnindex value is the map of attributes.
	 */
	private Map<String, Map<String, String>> cellSelectItemsAttributes;

	/**
	 * hold date pattern for calendar control widget. key is
	 * sheetName!$rowindex$columnindex value is the date pattern. date pattern
	 * is necessary for covert date to string
	 */

	private Map<String, String> cellDatePattern;

	/**
	 * hold attributes for validate process. key is
	 * sheetName!$rowindex$columnindex value is the list of attributes.
	 * 
	 * which .value = rule .message = error message.
	 */
	private Map<String, List<CellFormAttributes>> cellValidateAttributes;

	/**
	 * constructor.
	 * 
	 * @param pTemplateCommentMap
	 *            tempdate comment map.
	 * @param pCellInputType
	 *            cell input type.
	 * @param pCellInputAttributes
	 *            cell input attributes.
	 * @param pCellSelectItemsAttributes
	 *            cell select items attributes.
	 * @param pCellDatePattern
	 *            cell date pattern.
	 * @param pCellValidateAttributes
	 *            cell validate attributes.
	 */
	public CellAttributesMap(
			final Map<String, Map<String, String>> pTemplateCommentMap,
			final Map<String, String> pCellInputType,
			final Map<String, List<CellFormAttributes>> pCellInputAttributes,
			final Map<String, Map<String, String>> pCellSelectItemsAttributes,
			final Map<String, String> pCellDatePattern,
			final Map<String, List<CellFormAttributes>> pCellValidateAttributes) {
		this.templateCommentMap = pTemplateCommentMap;
		this.cellInputType = pCellInputType;
		this.cellInputAttributes = pCellInputAttributes;
		this.cellSelectItemsAttributes = pCellSelectItemsAttributes;
		this.cellDatePattern = pCellDatePattern;
		this.cellValidateAttributes = pCellValidateAttributes;
	}

	/**
	 * clear all the related maps.
	 */
	public final void clear() {
		if (this.templateCommentMap != null) {
			this.templateCommentMap.clear();
		}
		if (this.cellDatePattern != null) {
			this.cellDatePattern.clear();
		}
		if (this.cellInputAttributes != null) {
			this.cellInputAttributes.clear();
		}
		if (this.cellInputType != null) {
			this.cellInputType.clear();
		}
		if (this.cellSelectItemsAttributes != null) {
			this.cellSelectItemsAttributes.clear();
		}

	}

	/**
	 * return templatecommentmap.
	 * 
	 * @return templatecommentmap.
	 */
	public final Map<String, Map<String, String>> getTemplateCommentMap() {
		return templateCommentMap;
	}

	/**
	 * return cellinputtype.
	 * 
	 * @return cellinputtype.
	 */
	public final Map<String, String> getCellInputType() {
		return cellInputType;
	}

	/**
	 * return cellinputattributes.
	 * 
	 * @return cellinputattributes.
	 */
	public final Map<String, List<CellFormAttributes>> getCellInputAttributes() {
		return cellInputAttributes;
	}

	/**
	 * return cellselectitemattributes.
	 * 
	 * @return cellselectitemattributes.
	 */
	public final Map<String, Map<String, String>> getCellSelectItemsAttributes() {
		return cellSelectItemsAttributes;
	}

	/**
	 * return celldatepattern.
	 * 
	 * @return celldatepattern.
	 */
	public final Map<String, String> getCellDatePattern() {
		return cellDatePattern;
	}

	/**
	 * Gets the cell validate attributes.
	 *
	 * @return the cellValidateAttributes
	 */
	public final Map<String, List<CellFormAttributes>> getCellValidateAttributes() {
		return cellValidateAttributes;
	}

	/**
	 * Sets the cell validate attributes.
	 *
	 * @param pcellValidateAttributes
	 *            the cellValidateAttributes to set
	 */
	public final void setCellValidateAttributes(
			final Map<String, List<CellFormAttributes>> pcellValidateAttributes) {
		this.cellValidateAttributes = pcellValidateAttributes;
	}

} public class CellFormAttributes implements Serializable {

	/**
	 * serialVersionUID.
	 */
	private static final long serialVersionUID = 904076322986778019L;

	/** The type. */
	private String type;
	
	/** The value. */
	private String value;
	
	/** The message. */
	private String message;

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

	/**
	 * Sets the type.
	 *
	 * @param ptype
	 *            the new type
	 */
	public final void setType(final String ptype) {
		this.type = ptype;
	}

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

	/**
	 * Sets the value.
	 *
	 * @param pvalue
	 *            the new value
	 */
	public final void setValue(final String pvalue) {
		this.value = pvalue;
	}

	/**
	 * Gets the message.
	 *
	 * @return the message
	 */
	public final String getMessage() {
		return message;
	}

	/**
	 * Sets the message.
	 *
	 * @param pmessage
	 *            the new message
	 */
	public final void setMessage(final String pmessage) {
		this.message = pmessage;
	}

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

		StringBuilder sb = new StringBuilder();
		sb.append("{");
		sb.append("type = " + type);
		sb.append(",");
		sb.append("value = " + value);
		sb.append(",");
		sb.append("message = " + message);
		sb.append("}");
		return sb.toString();
	}

}
 @SuppressWarnings("rawtypes")
public class CellMap implements Serializable, java.util.Map {

	/** serial instance. */
	private static final long serialVersionUID = 1L;

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

	/** The Constant emptySet. */
	private static final Set emptySet = new HashSet();

	/** instance to parent websheet bean. */
	private TieWebSheetBean parent = null;

	/**
	 * Construtor. Pass in websheet bean, So this helper can access related
	 * instance class.
	 * 
	 * @param pParent
	 *            parent websheet bean
	 */

	public CellMap(final TieWebSheetBean pParent) {
		super();
		this.parent = pParent;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#size()
	 */
	@Override
	public final int size() {
		return 0;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#isEmpty()
	 */
	@Override
	public final boolean isEmpty() {
		return false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#containsKey(java.lang.Object)
	 */
	@Override
	public final boolean containsKey(final Object key) {
		return false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#containsValue(java.lang.Object)
	 */
	@Override
	public final boolean containsValue(final Object value) {
		return false;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#remove(java.lang.Object)
	 */
	@Override
	public final Object remove(final Object key) {
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#putAll(java.util.Map)
	 */
	@Override
	public void putAll(final Map m) {
		emptySet.clear();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#clear()
	 */
	@Override
	public void clear() {
		emptySet.clear();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#keySet()
	 */
	@Override
	public final Set keySet() {
		return emptySet;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#values()
	 */
	@Override
	public final Collection values() {
		return emptySet;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#entrySet()
	 */
	@Override
	public final Set entrySet() {
		return emptySet;
	}

	/**
	 * Put picture image to session map and return the key to web.
	 * 
	 * @param rowIndex
	 *            the row index of the cell which contains picture.
	 * @param colIndex
	 *            the column index of the cell which contains picture.
	 * @return the key of the picture image in the session map.
	 */
	private String loadPicture(final int rowIndex, final int colIndex) {

		FacesCell facesCell = parent.getCellHelper()
				.getFacesCellWithRowColFromCurrentPage(rowIndex, colIndex);
		if (facesCell != null && facesCell.isContainPic()) {
			FacesContext context = FacesContext.getCurrentInstance();
			String pictureId = facesCell.getPictureId();
			String pictureViewId = Integer.toHexString(
					System.identityHashCode(parent.getWb())) + pictureId;
			Map<String, Object> sessionMap = context.getExternalContext()
					.getSessionMap();
			if (sessionMap.get(pictureViewId) == null) {
				sessionMap.put(pictureViewId, parent.getPicturesMap()
						.get(pictureId).getPictureData());
			}
			return pictureViewId;
		} else {
			return null;
		}
	}

	/**
	 * Put chart image to session map and return the key to web.
	 * 
	 * @param rowIndex
	 *            the row index of the cell which contains picture.
	 * @param colIndex
	 *            the column index of the cell which contains picture.
	 * @return the key of the picture image in the session map.
	 */

	private String loadChart(final int rowIndex, final int colIndex) {

		FacesCell facesCell = parent.getCellHelper()
				.getFacesCellWithRowColFromCurrentPage(rowIndex, colIndex);
		if (facesCell != null && facesCell.isContainChart()) {
			FacesContext context = FacesContext.getCurrentInstance();
			String chartId = facesCell.getChartId();
			String chartViewId = Integer.toHexString(
					System.identityHashCode(parent.getWb())) + chartId;
			if (context != null) {
				Map<String, Object> sessionMap = context
						.getExternalContext().getSessionMap();
				if (sessionMap.get(chartViewId) == null) {
					sessionMap.put(chartViewId, parent.getCharsData()
							.getChartsMap().get(chartId));
				}
			}
			return chartViewId;
		} else {
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#get(java.lang.Object)
	 */
	@Override
	public final Object get(final Object key) {
		Object result = "";
		try {
			CellMapKey mkey = new CellMapKey((String) key);
			if (!mkey.isParseSuccess()) {
				return result;
			}
			Cell poiCell = parent.getCellHelper()
					.getPoiCellWithRowColFromCurrentPage(mkey.getRowIndex(),
							mkey.getColIndex());
			if (poiCell == null) {
				return result;
			}
			if (mkey.isCharted()) {
				result = loadChart(mkey.getRowIndex(), mkey.getColIndex());
			} else if (mkey.isPictured()) {
				result = loadPicture(mkey.getRowIndex(),
						mkey.getColIndex());
			} else if (mkey.isFormatted()) {
				result = CellUtility.getCellValueWithFormat(poiCell,
						parent.getFormulaEvaluator(),
						parent.getDataFormatter());
			} else {
				result = CellUtility.getCellValueWithoutFormat(poiCell);
			}

		} catch (Exception ex) {
			LOG.log(Level.SEVERE, "Web Form CellMap get value error="
					+ ex.getLocalizedMessage(), ex);
		}
		// return blank if null
		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.util.Map#put(java.lang.Object, java.lang.Object)
	 */
	@Override
	public final Object put(final Object key, final Object value) {
		try {
			CellMapKey mkey = new CellMapKey((String) key);
			if (!mkey.isParseSuccess()) {
				return null;
			}
			Cell poiCell = parent.getCellHelper()
					.getPoiCellWithRowColFromCurrentPage(mkey.getRowIndex(),
							mkey.getColIndex());
			if (poiCell == null) {
				return null;
			}
			String oldValue = CellUtility
					.getCellValueWithoutFormat(poiCell);
			FacesCell facesCell = parent.getCellHelper()
					.getFacesCellWithRowColFromCurrentPage(
							mkey.getRowIndex(), mkey.getColIndex());
			String newValue = assembleNewValue(value, facesCell);
			if (newValue != null && !newValue.equals(oldValue)) {
				CellUtility.setCellValue(poiCell, newValue);
				if (facesCell.isHasSaveAttr()) {
					parent.getCellHelper().saveDataInContext(poiCell,
							newValue);
				}
				// patch to avoid not updated downloaded file
				CellUtility.copyCell(poiCell.getSheet(), poiCell.getRow(), poiCell.getRow(), poiCell.getColumnIndex(),false);
				parent.getCellHelper().reCalc();
			}

			return value;
		} catch (Exception ex) {
			LOG.log(Level.SEVERE,
					"Save cell data error : " + ex.getLocalizedMessage(),
					ex);
		}
		return null;
	}

	/**
	 * Assemble new value.
	 *
	 * @param value
	 *            the value
	 * @param facesCell
	 *            the faces cell
	 * @return the string
	 */
	private String assembleNewValue(final Object value,
			final FacesCell facesCell) {
		String newValue;
		if (value instanceof java.util.Date) {
			String datePattern = facesCell.getDatePattern();
			if (datePattern == null || datePattern.isEmpty()) {
			    datePattern = parent.getDefaultDatePattern();
			}
			Format formatter = new SimpleDateFormat(datePattern);
			newValue = formatter.format(value);
		} else {
			newValue = (String) value;
		}
		if ("textarea".equalsIgnoreCase(facesCell.getInputType())
				&& (newValue != null)) {
			// remove "\r" because excel issue
			newValue = newValue.replace("\r\n", "\n");
		}
		return newValue;
	}
} 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 SerialDataContext implements Serializable {

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

	/**
	 * serial id.
	 */
	private static final long serialVersionUID = 1L;

	/**
	 * data context map is transient.
	 */
	private transient Map<String, Object> dataContext;

	/** convert map to json string. */
	private String mapToJson;

	/**
	 * save the workbook before serialize.
	 * 
	 * @param out
	 *            outputstream.
	 * @throws IOException
	 *             io exception.
	 */
	private void writeObject(final java.io.ObjectOutputStream out)
			throws IOException {
		Gson objGson = new GsonBuilder().setPrettyPrinting().create();
		this.mapToJson = objGson.toJson(this.dataContext);
		out.defaultWriteObject();
	}

	/**
	 * load the workbook from saving.
	 * 
	 * @param in
	 *            inputstream.
	 * @throws IOException
	 *             io exception.
	 */
	private void readObject(final java.io.ObjectInputStream in)
			throws IOException {
		try {
			in.defaultReadObject();
			Gson objGson = new GsonBuilder().setPrettyPrinting().create();
			Type listType = new TypeToken<Map<String, Object>>() {
			}.getType();
			this.dataContext = objGson.fromJson(mapToJson, listType);
		} catch (EncryptedDocumentException | ClassNotFoundException e) {
			LOG.log(Level.SEVERE,
					" error in readObject of serialWorkbook : "
							+ e.getLocalizedMessage(),
					e);
		}
	}

	/**
	 * Gets the data context.
	 *
	 * @return the dataContext
	 */
	public final Map<String, Object> getDataContext() {
		return dataContext;
	}

	/**
	 * Sets the data context.
	 *
	 * @param pdataContext
	 *            the dataContext to set
	 */
	public final void setDataContext(
			final Map<String, Object> pdataContext) {
		this.dataContext = pdataContext;
	}

}
 public class SerialWorkbook implements Serializable {

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

	/**
	 * serial id.
	 */
	private static final long serialVersionUID = 4711304873642901753L;

	/**
	 * workbook is transient.
	 */
	private transient Workbook wb;

	/** hold configuration for each sheet. */
	private Map<String, SheetConfiguration> sheetConfigMap;



	/**
	 * save the workbook before serialize.
	 * 
	 * @param out
	 *            outputstream.
	 * @throws IOException
	 *             io exception.
	 */
	private void writeObject(final java.io.ObjectOutputStream out)
			throws IOException {
		out.defaultWriteObject();
		if (wb != null) {
			wb.write(out);
		}
	}

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

	/**
	 * Gets the wb.
	 *
	 * @return the wb
	 */
	public final Workbook getWb() {
		return wb;
	}

	/**
	 * set wb.
	 * 
	 * @param pwb
	 *            the wb to set.
	 */
	public final void setWb(final Workbook pwb) {
		this.wb = pwb;
	}

	/**
	 * Gets the sheet config map.
	 *
	 * @return the sheetConfigMap
	 */
	public final Map<String, SheetConfiguration> getSheetConfigMap() {
		return sheetConfigMap;
	}

	/**
	 * Sets the sheet config map.
	 *
	 * @param psheetConfigMap
	 *            the sheetConfigMap to set
	 */
	public void setSheetConfigMap(
			final Map<String, SheetConfiguration> psheetConfigMap) {
		this.sheetConfigMap = psheetConfigMap;
	}

	/**
	 * recover the cell reference to the sheet.
	 */
	public void recover() {
		Map<String, SheetConfiguration> map = this.getSheetConfigMap();
		for (Entry<String, SheetConfiguration> entry : map.entrySet()) {
			entry.getValue().recover(this.getWb());
		}
	}

}
 public class CellHelper {

	/** instance to parent websheet bean. */
	private TieWebSheetBean parent = null;

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

	/**
	 * Instantiates a new cell helper.
	 */
	public CellHelper() {
		super();
	}

	/**
	 * Instantiates a new cell helper.
	 *
	 * @param pparent
	 *            parent bean.
	 */
	public CellHelper(final TieWebSheetBean pparent) {
		super();
		this.parent = pparent;
	}

	/**
	 * Save data in context.
	 *
	 * @param poiCell
	 *            the poi cell
	 * @param strValue
	 *            the str value
	 */
	public final void saveDataInContext(final Cell poiCell,
			final String strValue) {

		String saveAttr = SaveAttrsUtility.prepareContextAndAttrsForCell(poiCell, ConfigurationUtility.getFullNameFromRow(poiCell.getRow()), this);
		if (saveAttr!= null) {
			SaveAttrsUtility.saveDataToObjectInContext(
							parent.getSerialDataContext().getDataContext(),
							saveAttr, strValue, parent.getExpEngine());
			parent.getHelper().getWebSheetLoader().setUnsavedStatus(
							RequestContext.getCurrentInstance(), true);
		}
	}

	/**
	 * recalc whole workbook.
	 */
	public final void reCalc() {

		parent.getFormulaEvaluator().clearAllCachedResultValues();
		try {
			parent.getFormulaEvaluator().evaluateAll();
		} catch (Exception ex) {
			// skip the formula exception when recalc but log it
			LOG.log(Level.SEVERE,
					" recalc formula error : " + ex.getLocalizedMessage(),
					ex);
		}

	}

	/**
	 * evaluate boolean express.
	 *
	 * @param pscript
	 *            express.
	 * @return true (express is true) false ( express is false or invalid).
	 */
	public final boolean evalBoolExpression(final String pscript) {
		return CommandUtility.evalBoolExpression(parent.getExpEngine(),
				pscript);
	}

	/**
	 * Gets the poi cell with row col from current page.
	 *
	 * @param rowIndex
	 *            the row index
	 * @param colIndex
	 *            the col index
	 * @return the poi cell with row col from current page
	 */
	public final Cell getPoiCellWithRowColFromCurrentPage(
			final int rowIndex, final int colIndex) {
		return CellUtility.getPoiCellWithRowColFromCurrentPage(rowIndex,
				colIndex, parent.getWb());
	}

	/**
	 * Gets the poi cell with row col from tab.
	 *
	 * @param rowIndex
	 *            the row index
	 * @param colIndex
	 *            the col index
	 * @param tabName
	 *            the tab name
	 * @return the poi cell with row col from tab
	 */
	public final Cell getPoiCellWithRowColFromTab(final int rowIndex,
			final int colIndex, final String tabName) {
		if (parent.getWb() != null) {

			return CellUtility.getPoiCellFromSheet(rowIndex, colIndex,
					parent.getWb().getSheet(parent.getSheetConfigMap()
							.get(tabName).getSheetName()));
		}
		return null;
	}

	/**
	 * Gets the faces cell with row col from current page.
	 *
	 * @param rowIndex
	 *            the row index
	 * @param colIndex
	 *            the col index
	 * @return the faces cell with row col from current page
	 */
	public final FacesCell getFacesCellWithRowColFromCurrentPage(
			final int rowIndex, final int colIndex) {
		if (parent.getBodyRows() != null) {
			int top = parent.getCurrent().getCurrentTopRow();
			int left = parent.getCurrent().getCurrentLeftColumn();
			return parent.getBodyRows().get(rowIndex - top).getCells()
					.get(colIndex - left);
		}
		return null;
	}

	/**
	 * Restore data context.
	 *
	 * @param fullName
	 *            the full name
	 */
	public final void restoreDataContext(final String fullName) {

		String[] parts = fullName.split(":");

		if (!isNeedRestore(fullName, parts)) {
			return;
		}

		boolean stopSkip = false;
		List<String> list = parent.getCurrent()
				.getCurrentDataContextNameList();
		int listSize = list.size();

		// prepare collection data in context.
		// must loop through the full name which may have multiple
		// layer.
		// i.e. E.department.1:E.employee.0
		// need prepare department.1 and employee.0

		for (int i = 0; i < parts.length; i++) {
			String part = parts[i];
			boolean skip = false;
			if ((!stopSkip) && (i < listSize)) {
				String listPart = list.get(i);
				if (part.equalsIgnoreCase(listPart)) {
					skip = true;
				}
			}
			if (!skip) {
				stopSkip = true;
				startRestoreDataContext(part);
			}
		}
		if (stopSkip) {
			parent.getCurrent().setCurrentDataContextName(fullName);
		}

		return;
	}

	/**
	 * Get last collect object from full name.
	 * 
	 * Last collect contain each command, collection and index.
	 *
	 * @param fullName
	 *            the full name
	 * @return the collection object
	 */
	public final CollectionObject getLastCollect(final String fullName) {
		String[] parts = fullName.split(":");
		String part = parts[parts.length - 1];
		return startRestoreDataContext(part);
	}

	/**
	 * Checks if is need restore.
	 *
	 * @param fullName
	 *            the full name
	 * @param parts
	 *            the parts
	 * @return true, if is need restore
	 */
	private boolean isNeedRestore(final String fullName,
			final String[] parts) {
		if (fullName == null) {
			return false;
		}
		if ((parent.getCurrent().getCurrentDataContextName() != null)
				&& (parent.getCurrent().getCurrentDataContextName()
						.toLowerCase()
						.startsWith(fullName.toLowerCase()))) {
			return false;
		}

		return ((parts != null) && (parts.length > 1)); 
	}

	/**
	 * Start restore data context.
	 *
	 * @param part
	 *            the part
	 * @return the collection object
	 */
	private CollectionObject startRestoreDataContext(final String part) {
		if (part.startsWith(TieConstants.EACH_COMMAND_FULL_NAME_PREFIX)) {
			String[] varparts = part.split("\\.");
			CollectionObject collect = new CollectionObject();

			collect.setEachCommand(
					CommandUtility
							.getEachCommandFromPartsName(
									parent.getCurrentSheetConfig()
											.getCommandIndexMap(),
									varparts));
			collect.setLastCollection(ConfigurationUtility
					.transformToCollectionObject(parent.getExpEngine(),
							collect.getEachCommand().getItems(),
							parent.getSerialDataContext()
									.getDataContext()));
			collect.setLastCollectionIndex(
					CommandUtility.prepareCollectionDataInContext(varparts,
							collect.getLastCollection(),
							parent.getSerialDataContext()
									.getDataContext()));
			return collect;
		}
		return null;
	}

}
 public class PicturesHelper {

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

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

	/**
	 * Instantiates a new pictures helper.
	 *
	 * @param pparent
	 *            the parent
	 */
	public PicturesHelper(final TieWebSheetBean pparent) {
		this.parent = pparent;
	}

	/**
	 * initial pictures map for current workbook.
	 */
	public final void loadPicturesMap() {
		PicturesUtility.getPictruesMap(parent.getWb(),
				parent.getPicturesMap());
	}

	/**
	 * Setup faces cell picture charts.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @param fcell
	 *            the fcell
	 * @param cell
	 *            the cell
	 * @param fId
	 *            the f id
	 */
	public final void setupFacesCellPictureCharts(final Sheet sheet1,
			final FacesCell fcell, final Cell cell, final String fId) {
		if (parent.getPicturesMap() != null) {
			setupFacesCellPicture(sheet1, fcell, cell, fId);
		}
		if (parent.getCharsData().getChartsMap() != null) {
			setupFacesCellCharts(sheet1, fcell, cell, fId);
		}
	}


	/**
	 * Setup faces cell charts.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @param fcell
	 *            the fcell
	 * @param cell
	 *            the cell
	 * @param fId
	 *            the f id
	 */
	private void setupFacesCellCharts(final Sheet sheet1,
			final FacesCell fcell, final Cell cell, final String fId) {
		try {
			String chartId = parent.getCharsData().getChartPositionMap()
					.get(fId);
			if (chartId != null) {
				BufferedImage img = parent.getCharsData().getChartsMap()
						.get(chartId);
				if (img != null) {
					fcell.setContainChart(true);
					fcell.setChartId(chartId);
					fcell.setChartStyle(PicturesUtility.generateChartStyle(
							sheet1, fcell, cell, chartId,
							parent.getCharsData().getChartAnchorsMap()));
				}
			}
		} catch (Exception ex) {
			LOG.log(Level.SEVERE,
					"setupFacesCell Charts error = " + ex.getMessage(), ex);
		}
	}

	/**
	 * Setup faces cell picture.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @param fcell
	 *            the fcell
	 * @param cell
	 *            the cell
	 * @param fId
	 *            the f id
	 */
	private void setupFacesCellPicture(final Sheet sheet1,
			final FacesCell fcell, final Cell cell, final String fId) {
		try {
			Picture pic = parent.getPicturesMap().get(fId);
			if (pic != null) {
				fcell.setContainPic(true);
				fcell.setPictureId(fId);
				fcell.setPictureStyle(PicturesUtility
						.generatePictureStyle(sheet1, fcell, cell, pic));
			}
		} catch (Exception ex) {
			LOG.log(Level.SEVERE,
					"setupFacesCell Picture error = " + ex.getMessage(),
					ex);
		}
	}

}
 public class ValidationHandler {

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

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

	/**
	 * Instantiates a new validation handler.
	 *
	 * @param pparent
	 *            the parent
	 */
	public ValidationHandler(final TieWebSheetBean pparent) {
		super();
		this.parent = pparent;

	}

	/**
	 * Refresh after status changed.
	 *
	 * @param oldStatus
	 *            the old status
	 * @param newStatus
	 *            the new status
	 * @param formRow
	 *            the form row
	 * @param formCol
	 *            the form col
	 * @param cell
	 *            the cell
	 * @param updateGui
	 *            update gui flag.
	 *            if true then update gui.
	 */
	private void refreshAfterStatusChanged(final boolean oldStatus, final boolean newStatus, final int formRow,
			final int formCol, final FacesCell cell, final boolean updateGui) {

		if (!newStatus) {
			cell.setErrormsg("");
		}
		cell.setInvalid(newStatus);
		if (updateGui && (oldStatus != newStatus) && (parent.getWebFormClientId() != null)) {
			RequestContext.getCurrentInstance()
					.update(parent.getWebFormClientId() + ":" + (formRow) + ":group" + (formCol));
		}

	}

	/**
	 * Validate with row col in current page.
	 *
	 * @param row
	 *            the row
	 * @param col
	 *            the col
	 * @param updateGui
	 * 			  if true then update gui. 
	 * @return true, if successful
	 */
	public boolean validateWithRowColInCurrentPage(final int row, final int col, boolean updateGui) {

		//until now passEmptyCheck has one to one relation to submitMode
		//e.g. when passEmptyCheck = false, then submitMode = true.
		
		boolean submitMode = parent.getSubmitMode();
		boolean passEmptyCheck = !submitMode;
		
		int topRow = parent.getCurrent().getCurrentTopRow();
		int leftCol = parent.getCurrent().getCurrentLeftColumn();
		boolean pass = true;

		FacesRow fRow = CellUtility.getFacesRowFromBodyRow(row, parent.getBodyRows(), topRow);
		if (fRow == null) {
			return pass;
		}

		FacesCell cell = CellUtility.getFacesCellFromBodyRow(row, col, parent.getBodyRows(), topRow, leftCol);
		if (cell == null) {
			return pass;
		}

		Cell poiCell = parent.getCellHelper().getPoiCellWithRowColFromCurrentPage(row, col);
		boolean oldStatus = cell.isInvalid();

		String value = CellUtility.getCellValueWithoutFormat(poiCell);
		if (value == null) {
			value = "";
		} else {
			value = value.trim();
		}

		if (passEmptyCheck && value.isEmpty()) {
			refreshAfterStatusChanged(oldStatus, false, row - topRow, col - leftCol, cell, updateGui);
			return pass;
		}

		if (((parent.isOnlyValidateInSubmitMode() && submitMode ) || !parent.isOnlyValidateInSubmitMode())
		&& !validateByTieWebSheetValidationBean(poiCell, topRow, leftCol, cell, value, updateGui)) {
			return false;
		}
		

		SheetConfiguration sheetConfig = parent.getSheetConfigMap().get(parent.getCurrent().getCurrentTabName());
		List<CellFormAttributes> cellAttributes = CellControlsUtility.findCellValidateAttributes(
				parent.getCellAttributesMap().getCellValidateAttributes(), fRow.getOriginRowIndex(), poiCell);

		if (parent.isAdvancedContext() && parent.getConfigAdvancedContext().getErrorSuffix() != null
				&& !checkErrorMessageFromObjectInContext(row - topRow, col - leftCol, cell, poiCell, value,
						sheetConfig, updateGui)) {
			return false;
		}

		if (cellAttributes != null) {
			pass = validateAllRulesForSingleCell(row - topRow, col - leftCol, cell, poiCell, value, sheetConfig,
					cellAttributes, updateGui);
		}

		if (pass) {
			refreshAfterStatusChanged(oldStatus, false, row - topRow, col - leftCol, cell, updateGui);
		}
		return pass;

	}

	/**
	 * Validate by tie web sheet validation bean.
	 *
	 * @param poiCell the poi cell
	 * @param topRow the top row
	 * @param leftCol the left col
	 * @param cell the cell
	 * @param value the value
	 * @param updateGui the update gui
	 * @return true, if successful
	 */
	private boolean validateByTieWebSheetValidationBean(final Cell poiCell, final int topRow, final int leftCol,
			final FacesCell cell, final String value, boolean updateGui) {
		if (parent.getTieWebSheetValidationBean() != null) {
			String errormsg = null;
			String fullName = ConfigurationUtility.getFullNameFromRow(poiCell.getRow());
			String saveAttr = SaveAttrsUtility.prepareContextAndAttrsForCell(poiCell, fullName, parent.getCellHelper());
			if (saveAttr != null) {
				int row = poiCell.getRowIndex();
				int col = poiCell.getColumnIndex();
				errormsg = parent.getTieWebSheetValidationBean().validate(
						parent.getSerialDataContext().getDataContext(), saveAttr, ConfigurationUtility
								.getFullNameFromRow(poiCell.getRow()), poiCell.getSheet().getSheetName(),
						row, col, value);
				if ((errormsg != null) && (!errormsg.isEmpty())) {
					cell.setErrormsg(errormsg);
					refreshAfterStatusChanged(false, true, row - topRow, col - leftCol, cell, updateGui);
					return false;
				}
			}
		}
		return true;
	}

	
	/**
	 * Check error message from object in context.
	 *
	 * @param formRow the form row
	 * @param formCol the form col
	 * @param cell the cell
	 * @param poiCell the poi cell
	 * @param value the value
	 * @param sheetConfig the sheet config
	 * @param updateGui the update gui
	 * @return true, if successful
	 */
	private boolean checkErrorMessageFromObjectInContext(final int formRow, final int formCol, final FacesCell cell,
			final Cell poiCell, final String value, final SheetConfiguration sheetConfig, boolean updateGui) {

		@SuppressWarnings("unchecked")
		HashMap<String, TieCell> tieCells = (HashMap<String, TieCell>) parent.getSerialDataContext().getDataContext()
				.get("tiecells");

		if (tieCells != null) {

			TieCell tieCell = tieCells.get(CellUtility.getSkeyFromPoiCell(poiCell));

			if (tieCell != null && tieCell.getContextObject() != null) {

				String errorMethod = tieCell.getMethodStr() + parent.getConfigAdvancedContext().getErrorSuffix();

				String errorMessage = CellControlsUtility.getObjectPropertyValue(tieCell.getContextObject(),
						errorMethod, true);

				if (errorMessage != null && !errorMessage.isEmpty()) {
					cell.setErrormsg(errorMessage);
					LOG.log(Level.INFO, "Validation failed for sheet {0} row {1} column {2} : {3}",
							new Object[] { poiCell.getSheet().getSheetName(), poiCell.getRowIndex(),
									poiCell.getColumnIndex(), errorMessage });
					refreshAfterStatusChanged(false, true, formRow, formCol, cell, updateGui);
					return false;
				}

			}
		}

		return true;

	}

	/**
	 * Validate all rules for single cell.
	 *
	 * @param formRow            the form row
	 * @param formCol            the form col
	 * @param cell            the cell
	 * @param poiCell            the poi cell
	 * @param value            the value
	 * @param sheetConfig            the sheet config
	 * @param cellAttributes            the cell attributes
	 * @param updateGui the update gui
	 * @return true, if successful
	 */
	private boolean validateAllRulesForSingleCell(final int formRow, final int formCol, final FacesCell cell,
			final Cell poiCell, final String value, final SheetConfiguration sheetConfig,
			final List<CellFormAttributes> cellAttributes, boolean updateGui) {
		Sheet sheet1 = parent.getWb().getSheet(sheetConfig.getSheetName());
		for (CellFormAttributes attr : cellAttributes) {
			boolean pass = doValidation(value, attr, poiCell.getRowIndex(), poiCell.getColumnIndex(), sheet1);
			if (!pass) {
				String errmsg = attr.getMessage();
				if (errmsg == null) {
					errmsg = TieConstants.DEFALT_MSG_INVALID_INPUT;
				}
				cell.setErrormsg(errmsg);
				LOG.log(Level.INFO, "Validation failed for sheet {0} row {1} column {2} : {3}", new Object[] {
						poiCell.getSheet().getSheetName(), poiCell.getRowIndex(), poiCell.getColumnIndex(), errmsg });
				refreshAfterStatusChanged(false, true, formRow, formCol, cell, updateGui);
				return false;
			}

		}
		return true;
	}

	/**
	 * Do validation.
	 *
	 * @param value            the value
	 * @param attr            the attr
	 * @param rowIndex            the row index
	 * @param colIndex the col index
	 * @param sheet            the sheet
	 * @return true, if successful
	 */
	private boolean doValidation(final Object value, final CellFormAttributes attr, final int rowIndex,
			final int colIndex, final Sheet sheet) {
		boolean pass;

		String attrValue = attr.getValue();
		attrValue = attrValue.replace("$value", value.toString() + "").replace("$rowIndex", rowIndex + "")
				.replace("$colIndex", colIndex + "").replace("$sheetName", sheet.getSheetName());
		attrValue = ConfigurationUtility.replaceExpressionWithCellValue(attrValue, rowIndex, sheet);
		if (attrValue.contains(TieConstants.EL_START)) {
			Object returnObj = FacesUtility.evaluateExpression(attrValue, Object.class);
			attrValue = returnObj.toString();
			pass = Boolean.parseBoolean(attrValue);
		} else {
			pass = parent.getCellHelper().evalBoolExpression(attrValue);
		}
		return pass;

	}

	/**
	 * Validate cell.
	 *
	 * @param target
	 *            the target
	 * @return true, if successful
	 */
	public final boolean validateCell(final UIComponent target) {

		int[] rowcol = CellUtility.getRowColFromComponentAttributes(target);
		int row = rowcol[0];
		int col = rowcol[1];
		return validateWithRowColInCurrentPage(row, col, true);
	}

	/**
	 * Validate current page.
	 *
	 * @return true, if successful
	 */
	public final boolean validateCurrentPage() {
		boolean allpass = true;

		int top = parent.getCurrent().getCurrentTopRow();
		for (int irow = 0; irow < parent.getBodyRows().size(); irow++) {
			if (!validateRowInCurrentPage(irow + top, false)) {
				allpass = false;
			}
		}
		return allpass;
	}


	/**
	 * Validate row in current page.
	 *
	 * @param irow the irow
	 * @param updateGui the update gui
	 * @return true, if successful
	 */
	public final boolean validateRowInCurrentPage(final int irow, final boolean updateGui) {
		SheetConfiguration sheetConfig = parent.getSheetConfigMap().get(parent.getCurrent().getCurrentTabName());
		return this.validateRow(irow, sheetConfig, updateGui);
	}



	/**
	 * Validate row.
	 *
	 * @param irow the irow
	 * @param sheetConfig the sheet config
	 * @param updateGui the update gui
	 * @return true, if successful
	 */
	private boolean validateRow(final int irow, final SheetConfiguration sheetConfig, boolean updateGui) {
		boolean pass = true;
		if (sheetConfig == null) {
			return pass;
		}
		int top = sheetConfig.getBodyCellRange().getTopRow();
		List<FacesCell> cellRow = parent.getBodyRows().get(irow - top).getCells();
		for (int index = 0; index < cellRow.size(); index++) {
			FacesCell fcell = cellRow.get(index);
			if ((fcell != null) && (!validateWithRowColInCurrentPage(irow, fcell.getColumnIndex(), updateGui))) {
				pass = false;
			}
		}
		return pass;
	}

	/**
	 * Triggered when value in cells changed. e.g. user edit cell.
	 * 
	 * @param event
	 *            ajax event.
	 */
	public void valueChangeEvent(final AjaxBehaviorEvent event) {

		try {
			FacesContext facesContext = FacesContext.getCurrentInstance();
			String tblName = parent.getWebFormClientId();
			UIComponent target = event.getComponent();
			boolean pass = validateCell(target);
			if (pass) {
				// to improve performance, re-validate current row only
				// page validation take times. will happen when change tab(page)
				// or
				// reload page.
				int[] rowcol = CellUtility.getRowColFromComponentAttributes(target);
				validateRowInCurrentPage(rowcol[0], true);
				refreshCachedCellsInCurrentPage(facesContext, tblName);
			}
		} catch (Exception ex) {
			LOG.log(Level.SEVERE, "Validation error:" + ex.getLocalizedMessage(), ex);
		}
	}

	/**
	 * Refresh cached cells in current page.
	 *
	 * @param facesContext
	 *            the faces context
	 * @param tblName
	 *            the tbl name
	 */
	private void refreshCachedCellsInCurrentPage(final FacesContext facesContext, final String tblName) {
		// refresh current page calculation fields
		UIComponent s = facesContext.getViewRoot().findComponent(tblName);
		if (s == null) {
			return;
		}
		DataTable webDataTable = (DataTable) s;
		int first = webDataTable.getFirst();
		int rowsToRender = webDataTable.getRowsToRender();
		int rowCounts = webDataTable.getRowCount();
		int top = parent.getCurrent().getCurrentTopRow();
		int left = parent.getCurrent().getCurrentLeftColumn();

		for (int i = first; i <= (first + rowsToRender); i++) {
			if (i < rowCounts) {
				refreshCachedCellsInRow(tblName, top, left, i);
			}

		}
	}

	/**
	 * Refresh cached cells in row.
	 *
	 * @param tblName
	 *            the tbl name
	 * @param top
	 *            the top
	 * @param left
	 *            the left
	 * @param i
	 *            the i
	 */
	private void refreshCachedCellsInRow(final String tblName, final int top, final int left, final int i) {
		FacesRow dataRow = parent.getBodyRows().get(i);
		int isize = dataRow.getCells().size();
		for (int index = 0; index < isize; index++) {
			FacesCell fcell = dataRow.getCells().get(index);
			Cell poiCell = parent.getCellHelper().getPoiCellWithRowColFromCurrentPage(i + top, index + left);
			if (poiCell != null) {
				parent.getHelper().getWebSheetLoader().refreshCachedCell(tblName, i, index, poiCell, fcell);
			}
		}
	}

	/**
	 * set submit mode flag with javascript for holding in client side.
	 * 
	 * @param fullflag
	 *            true or false
	 */
	public void setSubmitModeInView(final Boolean fullflag) {

		if (FacesContext.getCurrentInstance() != null) {
			Map<String, Object> viewMap = FacesContext.getCurrentInstance().getViewRoot().getViewMap();
			if (viewMap != null) {
				Boolean flag = (Boolean) viewMap.get(TieConstants.SUBMITMODE);
				if ((flag == null) || (!flag.equals(fullflag))) {
					viewMap.put(TieConstants.SUBMITMODE, fullflag);
				}
			}
		}
	}

	/**
	 * triggered validation process before actions like save or submit.
	 * 
	 * @return true (pass) false (failed)
	 */
	public boolean preValidation() {
				
		String currentTabName = parent.getCurrent().getCurrentTabName();
		String tabName = null;
		String firstInvalidTabName = null;
		boolean reload = false;
		for (Map.Entry<String, SheetConfiguration> entry : parent.getSheetConfigMap().entrySet()) {
			tabName = entry.getKey();
			// if not reload and tabname==current then skip reloading.
			if (reload || (!tabName.equals(currentTabName))) {
				parent.getWebSheetLoader().prepareWorkShee(tabName);
				reload = true;
			}
			if (!parent.getValidationHandler().validateCurrentPage() &&
				(firstInvalidTabName == null)) {
				firstInvalidTabName = tabName; 
			}
		}		
		if (firstInvalidTabName != null)  {
			if (!tabName.equals(firstInvalidTabName)) {
				parent.getHelper().getWebSheetLoader().loadWorkSheet(firstInvalidTabName);
			}
			return false;
		}
		return true;
	}

}
 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,
				WebSheetUtility.pixel2WidthUnits(parent.getLineNumberColumnWidth() + parent.getAddRowColumnWidth()));

		RangeBuildRef rangeBuildRef = new RangeBuildRef(left, right, totalWidth, sheet1);

		if (sheetConfig.isFixedWidthStyle()) {
			parent.setTableWidthStyle(
					"table-layout: fixed; width:" + WebSheetUtility.widthUnits2Pixel(totalWidth) + "px;");
		}

		parent.setLineNumberColumnWidthStyle(
				getWidthStyle(WebSheetUtility.pixel2WidthUnits(parent.getLineNumberColumnWidth()), totalWidth));
		parent.setAddRowColumnWidthStyle("width:" + parent.getAddRowColumnWidth() + "px;");

		parent.getHeaderRows().clear();

		if (top < 0) {
			// this is blank configuration. set column letter as header
			parent.getHeaderRows().add(loadHeaderRowWithoutConfigurationTab(rangeBuildRef, true));
			// set showlinenumber to true as default
			parent.setShowLineNumber(true);
		} else {
			parent.getHeaderRows().add(loadHeaderRowWithoutConfigurationTab(rangeBuildRef, false));
			for (int i = top; i <= bottom; i++) {
				parent.getHeaderRows().add(loadHeaderRowWithConfigurationTab(sheetConfig, rangeBuildRef, i,
						cellRangeMap, skippedRegionCells));

			}
			// set showlinenumber to false as default
			parent.setShowLineNumber(false);

		}

	}

	/**
	 * Load header row without configuration tab.
	 *
	 * @param rangeBuildRef
	 *            the range build ref
	 * @param rendered
	 *            the rendered
	 * @return the list
	 */
	private List<HeaderCell> loadHeaderRowWithoutConfigurationTab(final RangeBuildRef rangeBuildRef,
			final boolean rendered) {

		int firstCol = rangeBuildRef.getLeft();
		int lastCol = rangeBuildRef.getRight();
		double totalWidth = (double) rangeBuildRef.getTotalWidth();
		Sheet sheet1 = rangeBuildRef.getSheet();
		List<HeaderCell> headercells = new ArrayList<>();
		for (int i = firstCol; i <= lastCol; i++) {
			if (!sheet1.isColumnHidden(i)) {
				String style = getHeaderColumnStyle(parent.getWb(), null, sheet1.getColumnWidth(i), totalWidth);
				headercells.add(
						new HeaderCell("1", "1", style, style, WebSheetUtility.getExcelColumnName(i), rendered, true));
			}
		}
		fillToMaxColumns(headercells);
		return headercells;

	}

	/**
	 * Fill to max columns.
	 *
	 * @param headercells
	 *            the headercells
	 */
	private void fillToMaxColumns(final List<HeaderCell> headercells) {
		if (headercells.size() < parent.getMaxColCounts()) {
			int fills = parent.getMaxColCounts() - headercells.size();
			for (int s = 0; s < fills; s++) {
				headercells.add(new HeaderCell("1", "1", "", "", "", false, false));
			}
		}
	}

	/**
	 * Gets the header column style.
	 *
	 * @param wb
	 *            the wb
	 * @param cell
	 *            the cell
	 * @param colWidth
	 *            the col width
	 * @param totalWidth
	 *            the total width
	 * @return the header column style
	 */
	private String getHeaderColumnStyle(final Workbook wb, final Cell cell, final double colWidth,
			final double totalWidth) {

		String columnstyle = "";
		if (cell != null) {
			columnstyle += CellStyleUtility.getCellStyle(wb, cell, "") + CellStyleUtility.getCellFontStyle(wb, cell); // +
		}

		columnstyle = columnstyle + getWidthStyle(colWidth, totalWidth);
		return columnstyle;
	}

	/**
	 * Gets the width style.
	 *
	 * @param colWidth
	 *            the col width
	 * @param totalWidth
	 *            the total width
	 * @return the width style
	 */
	private String getWidthStyle(final double colWidth, final double totalWidth) {
		double percentage = FacesUtility.round(TieConstants.CELL_FORMAT_PERCENTAGE_VALUE * colWidth / totalWidth, 2);
		return "width:" + percentage + TieConstants.CELL_FORMAT_PERCENTAGE_SYMBOL + ";";
	}

	/**
	 * Load header row with configuration tab.
	 *
	 * @param sheetConfig
	 *            the sheet config
	 * @param rangeBuildRef
	 *            the range build ref
	 * @param currentRow
	 *            the current row
	 * @param cellRangeMap
	 *            the cell range map
	 * @param skippedRegionCells
	 *            the skipped region cells
	 * @return the list
	 */
	private List<HeaderCell> loadHeaderRowWithConfigurationTab(final SheetConfiguration sheetConfig,
			final RangeBuildRef rangeBuildRef, final int currentRow, final Map<String, CellRangeAddress> cellRangeMap,
			final List<String> skippedRegionCells) {

		Sheet sheet1 = rangeBuildRef.getSheet();
		int left = rangeBuildRef.getLeft();
		int right = rangeBuildRef.getRight();
		double totalWidth = (double) rangeBuildRef.getTotalWidth();
		Row row = sheet1.getRow(currentRow);
		List<HeaderCell> headercells = new ArrayList<>();
		for (int cindex = left; cindex <= right; cindex++) {
			String cellindex = CellUtility.getCellIndexNumberKey(cindex, currentRow);

			if (!skippedRegionCells.contains(cellindex) && !sheet1.isColumnHidden(cindex)) {
				Cell cell = null;
				if (row != null) {
					cell = row.getCell(cindex, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				}
				int originRowIndex = ConfigurationUtility.getOriginalRowNumInHiddenColumn(row);
				if (cell != null) {
					FacesCell fcell = new FacesCell();

					CellUtility.convertCell(sheetConfig, fcell, cell, cellRangeMap, originRowIndex,
							parent.getCellAttributesMap(), null);
					parent.getPicHelper().setupFacesCellPictureCharts(sheet1, fcell, cell,
							WebSheetUtility.getFullCellRefName(sheet1, cell));
					CellStyleUtility.setupCellStyle(parent.getWb(), fcell, cell, row.getHeightInPoints());
					fcell.setColumnStyle(fcell.getColumnStyle()
							+ getColumnWidthStyle(sheet1, cellRangeMap, cellindex, cindex, totalWidth));
					fcell.setColumnIndex(cindex);

					headercells.add(
							new HeaderCell(Integer.toString(fcell.getRowspan()), Integer.toString(fcell.getColspan()),
									fcell.getStyle(), fcell.getColumnStyle(), CellUtility.getCellValueWithFormat(cell,
											parent.getFormulaEvaluator(), parent.getDataFormatter()),
									true, true));
				}
			}

		}
		fillToMaxColumns(headercells);
		return headercells;
	}

	/**
	 * Gets the column width style.
	 *
	 * @param sheet1
	 *            the sheet 1
	 * @param cellRangeMap
	 *            the cell range map
	 * @param cellindex
	 *            the cellindex
	 * @param cindex
	 *            the cindex
	 * @param totalWidth
	 *            the total width
	 * @return the column width style
	 */
	private String getColumnWidthStyle(final Sheet sheet1, final Map<String, CellRangeAddress> cellRangeMap,
			final String cellindex, final int cindex, final double totalWidth) {

		CellRangeAddress caddress = cellRangeMap.get(cellindex);
		double colWidth;
		// check whether the cell has rowspan or colspan
		if (caddress != null) {
			colWidth = CellStyleUtility.calcTotalWidth(sheet1, caddress.getFirstColumn(), caddress.getLastColumn(), 0);
		} else {
			colWidth = sheet1.getColumnWidth(cindex);
		}

		return getWidthStyle(colWidth, totalWidth);

	}

	// return 0 -- No template
	// return -1 -- error in open form
	// return 1 -- success

	/**
	 * Clear workbook.
	 */
	private void clearWorkbook() {
		parent.setFormulaEvaluator(null);
		parent.setDataFormatter(null);
		parent.setSheetConfigMap(null);
		parent.setTabs(null);
		parent.getSerialDataContext().setDataContext(null);
		parent.setPicturesMap(null);
		parent.setHeaderRows(null);
		parent.setBodyRows(null);
		parent.setWb(null);
		parent.getHeaderRows().clear();
		parent.getBodyRows().clear();
		parent.getCharsData().getChartsMap().clear();
		parent.getCharsData().getChartDataMap().clear();
		parent.getCharsData().getChartAnchorsMap().clear();
		parent.getCharsData().getChartPositionMap().clear();
		parent.getCellAttributesMap().clear();
	}

	/**
	 * Load workbook.
	 *
	 * @param fis
	 *            the fis
	 * @param dataContext
	 *            the data context
	 * @return the int
	 */
	public final int loadWorkbook(final InputStream fis, final Map<String, Object> dataContext) {

		try {
			Workbook wb = WorkbookFactory.create(fis);
			int ireturn = loadWorkbook(wb, dataContext);
			fis.close();
			return ireturn;
		} catch (Exception e) {
			LOG.log(Level.SEVERE, "Web Form loadWorkbook Error Exception = " + e.getLocalizedMessage(), e);
			return -1;
		}

	}

	/**
	 * Load workbook.
	 *
	 * @param wb
	 *            the wb
	 * @param dataContext
	 *            the data context
	 * @return the int
	 */
	public final int loadWorkbook(final Workbook wb, final Map<String, Object> dataContext) {

		try {
			clearWorkbook();
			// only support xssf workbook now since 2016 July
			if (!(wb instanceof XSSFWorkbook)) {
				LOG.fine("Error: WebSheet only support xlsx template.");
				return -1;
			}
			LOG.fine("Begin load work book...");
			parent.setWb(wb);
			parent.getSerialDataContext().setDataContext(dataContext);
			parent.setSheetConfigMap(new ConfigurationHandler(parent).buildConfiguration());
			parent.reCalcMaxColCounts();
			parent.getChartHelper().loadChartsMap();
			parent.getPicHelper().loadPicturesMap();
			initSheet();
			initTabs();
			if (!parent.getTabs().isEmpty()) {
				loadWorkSheet(parent.getTabs().get(0).getTitle());
			}

		} catch (Exception e) {
			LOG.log(Level.FINE, "Web Form loadWorkbook Error Exception = " + e.getLocalizedMessage(), e);
			return -1;
		}
		return 1;

	}

	/**
	 * Inits the tabs.
	 */
	private void initTabs() {
		parent.setTabs(new ArrayList<TabModel>());
		if (parent.getSheetConfigMap() != null) {
			for (String key : parent.getSheetConfigMap().keySet()) {
				parent.getTabs().add(new TabModel("form_" + key, key, "form"));
			}
		}
	}

	/**
	 * Inits the sheet.
	 */
	private void initSheet() {
		loadData();
	}

	/**
	 * load data process. unfinished.
	 */
	private void loadData() {

		if (parent.getSerialDataContext().getDataContext() == null) {
			// no data objects available.
			return;
		}

		if (parent.isAdvancedContext()) {
			parent.getSerialDataContext().getDataContext().put("tiecells", new HashMap<String, TieCell>());
		}

		for (SheetConfiguration sheetConfig : parent.getSheetConfigMap().values()) {
			List<RowsMapping> currentRowsMappingList = null;
			ConfigBuildRef configBuildRef = new ConfigBuildRef(parent.getWbWrapper(),
					parent.getWb().getSheet(sheetConfig.getSheetName()), parent.getExpEngine(), parent.getCellHelper(),
					sheetConfig.getCachedCells(), parent.getCellAttributesMap(), sheetConfig.getFinalCommentMap());
			int length = sheetConfig.getFormCommand().buildAt(null, configBuildRef,
					sheetConfig.getFormCommand().getTopRow(), parent.getSerialDataContext().getDataContext(),
					currentRowsMappingList);
			sheetConfig.setShiftMap(configBuildRef.getShiftMap());
			sheetConfig.setCollectionObjNameMap(configBuildRef.getCollectionObjNameMap());
			sheetConfig.setCommandIndexMap(configBuildRef.getCommandIndexMap());
			sheetConfig.setWatchList(configBuildRef.getWatchList());
			sheetConfig.setBodyAllowAddRows(configBuildRef.isBodyAllowAdd());
			sheetConfig.getBodyCellRange().setBottomRow(sheetConfig.getFormCommand().getTopRow() + length - 1);
			sheetConfig.setBodyPopulated(true);
		}
		parent.getCellHelper().reCalc();

	}

	/**
	 * Refresh data.
	 */
	public void refreshData() {

		if (parent.getSerialDataContext().getDataContext() == null) {
			// no data objects available.
			return;
		}

		for (SheetConfiguration sheetConfig : parent.getSheetConfigMap().values()) {
			for (int irow = sheetConfig.getFormCommand().getTopRow(); irow < sheetConfig.getFormCommand()
					.getLastRow(); irow++) {

				refreshDataForRow(parent.getWb().getSheet(sheetConfig.getSheetName()).getRow(irow));
			}

		}
		parent.getCellHelper().reCalc();

	}

	/**
	 * Refresh data for row.
	 *
	 * @param row the row
	 */
	private void refreshDataForRow(Row row) {
		if (row == null) {
			return;
		}
		String saveAttrList = SaveAttrsUtility.getSaveAttrListFromRow(row);
		if (saveAttrList!= null) {
			String[] saveAttrs = saveAttrList.split(",");
			for (String fullSaveAttr : saveAttrs) {
				refreshDataForCell(row, fullSaveAttr);
			}
		}	
	}
	
	/**
	 * refresh data for single cell.
	 *
	 * @param row row.
	 * @param fullSaveAttr fullSaveAttr.
	 */

	private void refreshDataForCell(Row row, String fullSaveAttr) {
		if (fullSaveAttr != null) {
			try {
				String fullName = ConfigurationUtility.getFullNameFromRow(row);
				if (fullName != null) {
					parent.getCellHelper().restoreDataContext(fullName);
					SaveAttrsUtility.refreshSheetRowFromContext(parent.getSerialDataContext().getDataContext(),
							fullSaveAttr, row, parent.getExpEngine());
				}
			}catch (Exception ex) {
				LOG.log(Level.SEVERE, "refreshDataForCell with fullAaveAttr ="+fullSaveAttr+" error = " + ex.getMessage(), ex);			
			}

		}
	}

	/**
	 * Find tab index with name.
	 *
	 * @param tabname
	 *            the tabname
	 * @return the int
	 */
	public final int findTabIndexWithName(final String tabname) {

		for (int i = 0; i < parent.getTabs().size(); i++) {
			if (parent.getTabs().get(i).getTitle().equalsIgnoreCase(tabname)) {
				return i;
			}
		}
		return -1;

	}

	/**
	 * Load work sheet.
	 *
	 * @param tabName
	 *            the tab name
	 */
	public final void loadWorkSheet(final String tabName) {

		prepareWorkShee(tabName);
		parent.getValidationHandler().validateCurrentPage();
		createDynamicColumns(tabName);
		// reset datatable current page to 1
		setDataTablePage(0);
		parent.getCurrent().setCurrentDataContextName(null);
		saveObjs();
		if ((RequestContext.getCurrentInstance() != null) && (parent.getClientId() != null)) {
			RequestContext.getCurrentInstance().update(parent.getClientId() + ":websheettab");
		}
	}
	
	/**
	 * prepare worksheet for loading.
	 * this only load at backend without refresh gui.
	 *
	 * @param tabName the tab name
	 */
	public final void prepareWorkShee(final String tabName) {

		int tabIndex = findTabIndexWithName(tabName);
		if (parent.getWebFormTabView() != null) {
			parent.getWebFormTabView().setActiveIndex(tabIndex);
		}
		parent.getCurrent().setCurrentTabName(tabName);
		String sheetName = parent.getSheetConfigMap().get(tabName).getSheetName();
		Sheet sheet1 = parent.getWb().getSheet(sheetName);
		parent.getWb().setActiveSheet(parent.getWb().getSheetIndex(sheet1));

		SheetConfiguration sheetConfig = parent.getSheetConfigMap().get(tabName);

		parent.setMaxRowsPerPage(parent.getSheetConfigMap().get(tabName).getMaxRowPerPage());
		parent.setBodyAllowAddRows(parent.getSheetConfigMap().get(tabName).isBodyAllowAddRows());

		// populate repeat rows before setup cell range map

		Map<String, CellRangeAddress> cellRangeMap = ConfigurationUtility.indexMergedRegion(sheet1);
		List<String> skippedRegionCells = ConfigurationUtility.skippedRegionCells(sheet1);
		loadHeaderRows(sheetConfig, cellRangeMap, skippedRegionCells);
		loadBodyRows(sheetConfig, cellRangeMap, skippedRegionCells);
	}	

	/**
	 * Sets the data table page.
	 *
	 * @param first
	 *            the new data table page
	 */
	private void setDataTablePage(final int first) {
		if (parent.getWebFormClientId() != null) {
			final DataTable d = (DataTable) FacesContext.getCurrentInstance().getViewRoot()
					.findComponent(parent.getWebFormClientId());
			if (d != null) {
				d.setFirst(first);
			}
		}
	}

	/**
	 * Save objs.
	 */
	private void saveObjs() {

		try {
			if (FacesContext.getCurrentInstance() != null) {
				Map<String, Object> viewMap = FacesContext.getCurrentInstance().getViewRoot().getViewMap();
				viewMap.put("currentTabName", parent.getCurrent().getCurrentTabName());
				viewMap.put(TieConstants.SUBMITMODE, parent.getSubmitMode());
			}
		} catch (Exception ex) {
			LOG.log(Level.SEVERE, "saveobjs in viewMap error = " + ex.getMessage(), ex);

		}

	}

	/**
	 * Setup row info.
	 *
	 * @param facesRow
	 *            the faces row
	 * @param sheet1
	 *            the sheet 1
	 * @param row
	 *            the row
	 * @param rowIndex
	 *            the row index
	 * @param allowAdd
	 *            the allow add
	 */
	private void setupRowInfo(final FacesRow facesRow, final Sheet sheet1, final Row row, final int rowIndex,
			final boolean allowAdd) {

		facesRow.setAllowAdd(allowAdd);
		if (row != null) {
			facesRow.setRendered(!row.getZeroHeight());
			facesRow.setRowheight(row.getHeight());
			int rowNum = ConfigurationUtility.getOriginalRowNumInHiddenColumn(row);
			facesRow.setOriginRowIndex(rowNum);
		} else {
			facesRow.setRendered(true);
			facesRow.setRowheight(sheet1.getDefaultRowHeight());
			facesRow.setOriginRowIndex(rowIndex);
		}

	}

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

		int top = sheetConfig.getBodyCellRange().getTopRow();
		int bottom = CellUtility.getBodyBottomFromConfig(sheetConfig);
		int left = sheetConfig.getBodyCellRange().getLeftCol();
		int right = sheetConfig.getBodyCellRange().getRightCol();

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

		parent.getBodyRows().clear();
		clearCache();

		for (int i = top; i <= bottom; i++) {

			parent.getBodyRows()
					.add(assembleFacesBodyRow(i, sheet1, left, right, sheetConfig, cellRangeMap, skippedRegionCells));

		}
		sheetConfig.setBodyPopulated(true);
		parent.getCurrent().setCurrentTopRow(top);
		parent.getCurrent().setCurrentLeftColumn(left);
	}

	/**
	 * Assemble faces body row.
	 *
	 * @param rowIndex
	 *            the row index
	 * @param sheet1
	 *            the sheet 1
	 * @param left
	 *            the left
	 * @param right
	 *            the right
	 * @param sheetConfig
	 *            the sheet config
	 * @param cellRangeMap
	 *            the cell range map
	 * @param skippedRegionCells
	 *            the skipped region cells
	 * @return the faces row
	 */
	private FacesRow assembleFacesBodyRow(final int rowIndex, final Sheet sheet1, final int left, final int right,
			final SheetConfiguration sheetConfig, final Map<String, CellRangeAddress> cellRangeMap,
			final List<String> skippedRegionCells) {

		FacesRow facesRow = new FacesRow(rowIndex);
		Row row = sheet1.getRow(rowIndex);
		setupRowInfo(facesRow, sheet1, row, rowIndex, CommandUtility.isRowAllowAdd(row, sheetConfig));
		String saveAttrList = SaveAttrsUtility.getSaveAttrListFromRow(row);
		List<FacesCell> bodycells = new ArrayList<>();
		for (int cindex = left; cindex <= right; cindex++) {
			String cellindex = CellUtility.getCellIndexNumberKey(cindex, rowIndex);
			if (!skippedRegionCells.contains(cellindex) && !sheet1.isColumnHidden(cindex)) {
				Cell cell = null;
				if (row != null) {
					cell = row.getCell(cindex, MissingCellPolicy.CREATE_NULL_AS_BLANK);
				}
				if (cell != null) {
					FacesCell fcell = new FacesCell();

					CellUtility.convertCell(sheetConfig, fcell, cell, cellRangeMap, facesRow.getOriginRowIndex(),
							parent.getCellAttributesMap(), saveAttrList);
					parent.getPicHelper().setupFacesCellPictureCharts(sheet1, fcell, cell,
							WebSheetUtility.getFullCellRefName(sheet1, cell));
					CellStyleUtility.setupCellStyle(parent.getWb(), fcell, cell, row.getHeightInPoints());
					fcell.setColumnIndex(cindex);
					bodycells.add(fcell);
					addCache(cell);
				} else {
					bodycells.add(null);
				}
			} else {
				bodycells.add(null);
			}
		}
		facesRow.setCells(bodycells);
		return facesRow;
	}

	/**
	 * Adds the cache.
	 * 
	 * @param cell
	 *            the cell
	 */
	private void addCache(final Cell cell) {
		parent.getCachedCells().put(cell, CellType.FORMULA);
	}

	/**
	 * Clear cache.
	 */
	private void clearCache() {
		parent.getCachedCells().clear();

	}

	/**
	 * Refresh cached cell.
	 *
	 * @param tblName
	 *            the tbl name
	 * @param i
	 *            the i
	 * @param index
	 *            the index
	 * @param cell
	 *            the cell
	 * @param fcell
	 *            the fcell
	 */
	public final void refreshCachedCell(final String tblName, final int i, final int index, final Cell cell,
			final FacesCell fcell) {

		if ((cell != null) && (cell.getCellTypeEnum() == CellType.FORMULA) && (tblName != null)) {
			try {
				processRefreshCell(tblName, i, index, cell, fcell);
			} catch (Exception ex) {
				LOG.log(Level.SEVERE, "refresh Cached Cell error : " + ex.getLocalizedMessage(), ex);
			}
		}
	}

	/**
	 * Process refresh cell.
	 *
	 * @param tblName
	 *            the tbl name
	 * @param i
	 *            the i
	 * @param index
	 *            the index
	 * @param cell
	 *            the cell
	 * @param fcell
	 *            the fcell
	 */
	private void processRefreshCell(final String tblName, final int i, final int index, final Cell cell,
			final FacesCell fcell) {
		String newValue = CellUtility.getCellValueWithFormat(cell, parent.getFormulaEvaluator(),
				parent.getDataFormatter());
		if (parent.getCachedCells().isValueChanged(cell, newValue)) {
			if (fcell.isHasSaveAttr()) {
				parent.getCellHelper().saveDataInContext(cell, newValue);
			}
			RequestContext.getCurrentInstance().update(tblName + ":" + i + ":cocalc" + index);
			parent.getCachedCells().put(cell, CellType.FORMULA);
		}
	}

	/**
	 * Creates the dynamic columns.
	 *
	 * @param tabName
	 *            the tab name
	 */
	private void createDynamicColumns(final String tabName) {

		SheetConfiguration sheetConfig = parent.getSheetConfigMap().get(tabName);

		int left = sheetConfig.getBodyCellRange().getLeftCol();
		int right = sheetConfig.getBodyCellRange().getRightCol();

		parent.getColumns().clear();

		for (int i = left; i <= right; i++) {
			parent.getColumns().add("column" + (i - left));
		}

	}

	/**
	 * Adds the repeat row.
	 *
	 * @param rowIndex
	 *            the row index
	 */
	public final void addRepeatRow(final int rowIndex) {

		try {
			SheetConfiguration sheetConfig = parent.getSheetConfigMap().get(parent.getCurrent().getCurrentTabName());
			Sheet sheet = parent.getWb().getSheet(sheetConfig.getSheetName());
			ConfigBuildRef configBuildRef = new ConfigBuildRef(parent.getWbWrapper(), sheet, parent.getExpEngine(),
					parent.getCellHelper(), sheetConfig.getCachedCells(), parent.getCellAttributesMap(),
					sheetConfig.getFinalCommentMap());
			// set add mode
			configBuildRef.setAddMode(true);
			configBuildRef.setCollectionObjNameMap(sheetConfig.getCollectionObjNameMap());
			configBuildRef.setCommandIndexMap(sheetConfig.getCommandIndexMap());
			configBuildRef.setShiftMap(sheetConfig.getShiftMap());
			configBuildRef.setWatchList(sheetConfig.getWatchList());
			int length = CommandUtility.addRow(configBuildRef, rowIndex,
					parent.getSerialDataContext().getDataContext());
			refreshBodyRowsInRange(configBuildRef.getInsertPosition(), length, sheet, sheetConfig);
			parent.getCellHelper().reCalc();
		} catch (AddRowException e) {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Add Row Error", e.getMessage()));
			LOG.log(Level.SEVERE, "Add row error = " + e.getLocalizedMessage(), e);

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

		}

	}

	/**
	 * Refresh body rows in range.
	 *
	 * @param insertPosition
	 *            the insert position
	 * @param length
	 *            the length
	 * @param sheet
	 *            the sheet
	 * @param sheetConfig
	 *            the sheet config
	 */
	private void refreshBodyRowsInRange(final int insertPosition, final int length, final Sheet sheet,
			final SheetConfiguration sheetConfig) {
		Map<String, CellRangeAddress> cellRangeMap = ConfigurationUtility.indexMergedRegion(sheet);
		List<String> skippedRegionCells = ConfigurationUtility.skippedRegionCells(sheet);
		int top = sheetConfig.getBodyCellRange().getTopRow();
		int left = sheetConfig.getBodyCellRange().getLeftCol();
		int right = sheetConfig.getBodyCellRange().getRightCol();
		for (int irow = insertPosition; irow < (insertPosition + length); irow++) {
			parent.getBodyRows().add(irow - top,
					assembleFacesBodyRow(irow, sheet, left, right, sheetConfig, cellRangeMap, skippedRegionCells));
		}
		for (int irow = insertPosition + length - top; irow < parent.getBodyRows().size(); irow++) {
			FacesRow facesrow = parent.getBodyRows().get(irow);
			facesrow.setRowIndex(facesrow.getRowIndex() + length);
		}
	}

	/**
	 * Delete repeat row.
	 *
	 * @param rowIndex
	 *            the row index
	 */
	public final void deleteRepeatRow(final int rowIndex) {
		try {
			SheetConfiguration sheetConfig = parent.getSheetConfigMap().get(parent.getCurrent().getCurrentTabName());
			Sheet sheet = parent.getWb().getSheet(sheetConfig.getSheetName());
			ConfigBuildRef configBuildRef = new ConfigBuildRef(parent.getWbWrapper(), sheet, parent.getExpEngine(),
					parent.getCellHelper(), sheetConfig.getCachedCells(), parent.getCellAttributesMap(),
					sheetConfig.getFinalCommentMap());
			// set delete mode
			configBuildRef.setCollectionObjNameMap(sheetConfig.getCollectionObjNameMap());
			configBuildRef.setCommandIndexMap(sheetConfig.getCommandIndexMap());
			configBuildRef.setShiftMap(sheetConfig.getShiftMap());
			configBuildRef.setWatchList(sheetConfig.getWatchList());
			CommandUtility.deleteRow(configBuildRef, rowIndex, parent.getSerialDataContext().getDataContext(),
					sheetConfig, parent.getBodyRows());
			parent.getCellHelper().reCalc();
		} catch (DeleteRowException e) {
			FacesContext.getCurrentInstance().addMessage(null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, "Delete row error", e.getMessage()));
			LOG.log(Level.SEVERE, "Delete row error = " + e.getLocalizedMessage(), e);

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

		}

	}

	/**
	 * Sets the unsaved status.
	 *
	 * @param requestContext
	 *            the request context
	 * @param statusFlag
	 *            the status flag
	 */
	public void setUnsavedStatus(final RequestContext requestContext, final Boolean statusFlag) {

		// in client js should have setUnsavedState method
		if (requestContext != null) {
			LOG.log(Level.FINE, "run setUnsavedState( {} )", statusFlag.toString());
			requestContext.execute("setUnsavedState(" + statusFlag.toString() + ")");
		}

	}

	/**
	 * Checks if is unsaved status.
	 *
	 * @return the boolean
	 */
	public final Boolean isUnsavedStatus() {
		Map<String, Object> viewMap = FacesContext.getCurrentInstance().getViewRoot().getViewMap();
		Boolean flag = (Boolean) viewMap.get(TieConstants.UNSAVEDSTATE);
		if (flag == null) {
			return false;
		}
		return flag;
	}

}
 public final class CellControlsUtility {

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

	/** list of supported components. */
	private static List<String> supportComponents = Arrays.asList(
			"Calendar", "SelectOneMenu", "InputNumber", "InputTextarea");

	/**
	 * array list for possible parameter's type.
	 */

	public enum AttributesType {
		
		/** The string. */
		STRING(String.class) {
			@Override
			public Object parseValue(final String value) {
				return value;
			}
		},
		
		/** The boolean. */
		BOOLEAN(Boolean.class) {
			@Override
			public Object parseValue(final String value) {
				return Boolean.parseBoolean(value);
			}
		},
		
		/** The booleantype. */
		BOOLEANTYPE(boolean.class) {
			@Override
			public Object parseValue(final String value) {
				return Boolean.parseBoolean(value);
			}
		},
		
		/** The integer. */
		INTEGER(Integer.class) {
			@Override
			public Object parseValue(final String value) {
				return Integer.parseInt(value);
			}
		},
		
		/** The integertype. */
		INTEGERTYPE(int.class) {
			@Override
			public Object parseValue(final String value) {
				return Integer.parseInt(value);
			}
		},
		
		/** The long. */
		LONG(Long.class) {
			@Override
			public Object parseValue(final String value) {
				return Long.parseLong(value);
			}
		},
		
		/** The longtype. */
		LONGTYPE(long.class) {
			@Override
			public Object parseValue(final String value) {
				return Long.parseLong(value);
			}
		},
		
		/** The float. */
		FLOAT(Float.class) {
			@Override
			public Object parseValue(final String value) {
				return Float.parseFloat(value);
			}
		},
		
		/** The floattype. */
		FLOATTYPE(float.class) {
			@Override
			public Object parseValue(final String value) {
				return Float.parseFloat(value);
			}
		},
		
		/** The double. */
		DOUBLE(Double.class) {
			@Override
			public Object parseValue(final String value) {
				return Double.parseDouble(value);
			}
		},
		
		/** The doubletype. */
		DOUBLETYPE(double.class) {
			@Override
			public Object parseValue(final String value) {
				return Double.parseDouble(value);
			}
		},
		
		/** The byte. */
		BYTE(Byte.class) {
			@Override
			public Object parseValue(final String value) {
				return Byte.parseByte(value);
			}
		},
		
		/** The bytetype. */
		BYTETYPE(byte.class) {
			@Override
			public Object parseValue(final String value) {
				return Byte.parseByte(value);
			}
		},
		
		/** The short. */
		SHORT(Short.class) {
			@Override
			public Object parseValue(final String value) {
				return Short.parseShort(value);
			}
		},
		
		/** The shorttype. */
		SHORTTYPE(short.class) {
			@Override
			public Object parseValue(final String value) {
				return Short.parseShort(value);
			}
		};

		/** The clazz. */
		private final Class clazz; // class name

		/**
		 * Instantiates a new attributes type.
		 *
		 * @param pclazz
		 *            the pclazz
		 */
		AttributesType(final Class pclazz) {
			this.clazz = pclazz;
		}

		/**
		 * Parses the value.
		 *
		 * @param value
		 *            the value
		 * @return the object
		 */
		public abstract Object parseValue(String value);
	}

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

	/**
	 * find component according it's class.
	 * 
	 * @param component
	 *            component.
	 * @return component name.
	 */
	private static String findComponentNameFromClass(
			final UIComponent component) {

		String cname = component.getClass().getSimpleName();
		if (supportComponents.contains(cname)) {
			return cname;
		}
		return null;

	}

	/**
	 * populate attributes.
	 * 
	 * @param component
	 *            component obj.
	 * @param fcell
	 *            facescell.
	 * @param defaultControlMap
	 *            default control map.
	 */
	public static void populateAttributes(final UIComponent component,
			final FacesCell fcell,
			final Map<String, Map<String, String>> defaultControlMap) {

		List<CellFormAttributes> inputAttrs = fcell.getInputAttrs();
		String cname = findComponentNameFromClass(component);
		if (cname == null) {
			return;
		}

		Map<String, String> defaultMap = defaultControlMap.get(cname);
		if (defaultMap == null) {
			defaultMap = new HashMap<>();
			defaultControlMap.put(cname, defaultMap);
		}
		for (Map.Entry<String, String> entry : defaultMap.entrySet()) {
			setObjectProperty(component, entry.getKey(), entry.getValue(),
					true);
		}
		for (CellFormAttributes attr : inputAttrs) {
			String propertyName = attr.getType();
			String propertyValue = attr.getValue();
			if (!defaultMap.containsKey(propertyName)) {
				String defaultValue = getObjectPropertyValue(component,
						propertyName, true);
				defaultMap.put(propertyName, defaultValue);
			}
			setObjectProperty(component, propertyName, propertyValue, true);
		}

	}

	/**
	 * match parameter of method.
	 * 
	 * @param obj
	 *            object.
	 * @param methodName
	 *            method name.
	 * @return index of paraMatchArray if mached. otherwise return -1.
	 */
	private static AttributesType matchParaMeterOfMethod(final Object obj,
			final String methodName) {

		for (AttributesType attr : AttributesType.values()) {
			try {
				obj.getClass().getMethod(methodName,
						new Class[] { attr.clazz });
				return attr;

			} catch (Exception ex) {
				LOG.log(Level.FINE, " error in matchParaMeterOfMethod = "
						+ ex.getLocalizedMessage(), ex);
			}
		}
		return null;
	}

	/**
	 * convert object.
	 *
	 * @param attr
	 *            the attr
	 * @param value
	 *            value.
	 * @return object according to class.
	 */
	static Object convertToObject(final AttributesType attr, final String value) {

		return attr.parseValue(value);

	}

	/**
	 * set object property.
	 * 
	 * @param obj
	 *            object.
	 * @param propertyName
	 *            property name.
	 * @param propertyValue
	 *            value.
	 * @param ignoreNonExisting
	 *            true if ignore non exist property.
	 */

	public static void setObjectProperty(final Object obj,
			final String propertyName, final String propertyValue,
			final boolean ignoreNonExisting) {
		try {

			String methodName = "set"
					+ Character.toUpperCase(propertyName.charAt(0))
					+ propertyName.substring(1);
			AttributesType parameterType = matchParaMeterOfMethod(obj,
					methodName);
			if (parameterType != null) {
				Method method = obj.getClass().getMethod(methodName,
						new Class[] { parameterType.clazz });
				method.invoke(obj,
						convertToObject(parameterType, propertyValue));
			}
		} catch (Exception e) {
			String msg = "failed to set property '" + propertyName
					+ "' to value '" + propertyValue + "' for object "
					+ obj;
			if (ignoreNonExisting) {
				LOG.log(Level.FINE, msg, e);
			} else {
				LOG.warning(msg);
				throw new IllegalArgumentException(e);
			}
		}
	}

	/**
	 * get object property value.
	 * 
	 * @param obj
	 *            object.
	 * @param propertyName
	 *            property name.
	 * @param ignoreNonExisting
	 *            true ignore no existing.
	 * @return perperty value.
	 */
	public static String getObjectPropertyValue(final Object obj,
			final String propertyName, final boolean ignoreNonExisting) {
		try {
			Method method = obj.getClass()
					.getMethod("get"
							+ Character.toUpperCase(propertyName.charAt(0))
							+ propertyName.substring(1));
			return (String) method.invoke(obj);
		} catch (Exception e) {
			String msg = "failed to get property '" + propertyName
					+ "' for object " + obj;
			if (ignoreNonExisting) {
				LOG.log(Level.FINE, msg, e);
			} else {
				LOG.warning(msg);
				throw new IllegalArgumentException(e);
			}
		}
		return null;
	}

	/**
	 * Setup control attributes.
	 *
	 * @param originRowIndex
	 *            the origin row index
	 * @param fcell
	 *            the fcell
	 * @param poiCell
	 *            the poi cell
	 * @param sheetConfig
	 *            the sheet config
	 * @param cellAttributesMap
	 *            the cell attributes map
	 */
	public static void setupControlAttributes(final int originRowIndex,
			final FacesCell fcell, final Cell poiCell,
			final SheetConfiguration sheetConfig,
			final CellAttributesMap cellAttributesMap) {
		int rowIndex = originRowIndex;
		if (rowIndex < 0) {
			rowIndex = poiCell.getRowIndex();
		}

		String skey = poiCell.getSheet().getSheetName() + "!" + CellUtility
				.getCellIndexNumberKey(poiCell.getColumnIndex(), rowIndex);

		Map<String, String> commentMap = cellAttributesMap
				.getTemplateCommentMap().get("$$");
		if (commentMap != null) {
			String comment = commentMap.get(skey);
			if (comment != null) {
				CommandUtility.createCellComment(poiCell, comment,
						sheetConfig.getFinalCommentMap());
			}
		}

		String widgetType = cellAttributesMap.getCellInputType().get(skey);
		if (widgetType != null) {
			fcell.setControl(widgetType.toLowerCase());

			fcell.setInputAttrs(
					cellAttributesMap.getCellInputAttributes().get(skey));
			fcell.setSelectItemAttrs(cellAttributesMap
					.getCellSelectItemsAttributes().get(skey));
			fcell.setDatePattern(
					cellAttributesMap.getCellDatePattern().get(skey));
		}

	}

	/**
	 * Find cell validate attributes.
	 *
	 * @param validateMaps
	 *            validateMaps.
	 * @param originRowIndex
	 * 			   original Row Index from facesRow.           
	 * @param cell
	 *            cell.
	 * @return list.
	 */
	public static List<CellFormAttributes> findCellValidateAttributes(
			final Map<String, List<CellFormAttributes>> validateMaps,
			final int originRowIndex,
			final Cell cell) {
		String key = cell.getSheet().getSheetName() + "!" + CellUtility
				.getCellIndexNumberKey(cell.getColumnIndex(), originRowIndex);
		
		return validateMaps.get(key);
	}
	
}
 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;
	}

}

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
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.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.event.AjaxBehaviorEvent;
import javax.faces.event.ComponentSystemEvent;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.context.RequestContext;
import org.primefaces.event.TabChangeEvent;
import org.primefaces.model.DefaultStreamedContent;
import org.primefaces.model.StreamedContent;
import org.tiefaces.components.websheet.chart.ChartHelper;
import org.tiefaces.components.websheet.chart.ChartsData;
import org.tiefaces.components.websheet.configuration.ExpressionEngine;
import org.tiefaces.components.websheet.configuration.SheetConfiguration;
import org.tiefaces.components.websheet.dataobjects.CachedCells;
import org.tiefaces.components.websheet.dataobjects.CellAttributesMap;
import org.tiefaces.components.websheet.dataobjects.CellFormAttributes;
import org.tiefaces.components.websheet.dataobjects.CellMap;
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.serializable.SerialDataContext;
import org.tiefaces.components.websheet.serializable.SerialWorkbook;
import org.tiefaces.components.websheet.service.CellHelper;
import org.tiefaces.components.websheet.service.PicturesHelper;
import org.tiefaces.components.websheet.service.ValidationHandler;
import org.tiefaces.components.websheet.service.WebSheetLoader;
import org.tiefaces.components.websheet.utility.CellControlsUtility;
import org.tiefaces.components.websheet.utility.CellUtility;

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

package org.tiefaces.components.websheet;




/**
 * Main class for web sheet.
 * 
 * @author Jason Jiang
 *
 */
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);
