/*
 * Created on Oct 15, 2003
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package uk.mediatel.cms.excelGenerator;

/**
 * @author TimW
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
 
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.Region;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.TimeZone;
import java.util.Iterator;
import java.util.HashMap;
import java.util.Date;
import java.util.StringTokenizer;
import java.util.ArrayList;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

//import org.apache.log4j.Logger;
import org.apache.log4j.Category;
import org.apache.log4j.PropertyConfigurator;

public class ClientPlanGenerator 
{	
	//logger init
	//static Logger logger = Logger.getLogger(ClientPlanGenerator.class.getName());
	static Category logger = Category.getInstance(ExcelDataGrid.class.getName());
		
	private String templateFileName;
	private POIFSFileSystem fs;
	private HSSFWorkbook wb = null;
	private HSSFSheet sheet1 = null;
	private boolean templateFilesHasBeenParsed = false;
	private ExcelDataGrid dataGrid;
	
	private int FooterStartsOnRow = -1;
		
	private int sheetCellWidth = 0;
	
	//construct to hold all the different cell styles
	private HashMap mapCellStyles = new HashMap(15);

	//TODO: define what object to accept for the name value pairs of miscelaniouse data
	private HashMap miscData = null;
	
	//ARRAY OF INTS TO HOLD COLUMN WIDTHS 
	private HashMap columnWidths = new HashMap(); 
	
	//ARRAY OF INTS TO HOLD ROW HEIGHTS 
	private HashMap rowHeights = new HashMap(); 
	private HashMap footerRowHeights = new HashMap(); 
	
	private int lastRowIndex = 0;
	private int totalNoOfRowsWrittenTo = 0;
	
	//used to determin how much the footer was moved up the grid before data was inserted
	private int footerOffset = 0;
	private int lengthOfFooter = 0;
	
	private HashMap mapDataRowPositionToExcelRowNumb = new HashMap(); 
	private HashMap mapDataColumnPositionToExcelColChar = new HashMap();
	private ArrayList cellObjectsContainingFormula = new ArrayList();
	
	public ClientPlanGenerator(String templateFileURL, HashMap miscData) 
	throws ClientPlanGeneratorException
	{
		//Grab log configuration file
		PropertyConfigurator.configure("DARTTrackingFigures.Properties");
		logger.debug("ClientPlanGenerator Constructor Begin....");
		
		this.miscData = miscData;
		
		setTemplateFileName(templateFileURL);
		
		//Get 'Sheet1' worksheet
		try 
		{
			wb = new HSSFWorkbook(fs);
		} 
		catch (IOException e) 
		{
			throw new ClientPlanGeneratorException(10,"Could not open the workbook of the file " + templateFileName + " extended info = " + e);
		}
		
		sheet1 = wb.getSheetAt(0);
		
		parseTemplateFile(sheet1);
		
		logger.debug("... End ClientPlanGenerator Constructor");
	}
	
	public ClientPlanGenerator(String templateFileURL, int tim) 
	//throws ClientPlanGeneratorException
	{
		//Grab log configuration file
		//PropertyConfigurator.configure("DARTTrackingFigures.Properties");
		logger.debug("ClientPlanGenerator Constructor Begin....");
		
		this.miscData = new HashMap();
	
		
		//Get 'Sheet1' worksheet
		try 
		{
			setTemplateFileName(templateFileURL);
			
			wb = new HSSFWorkbook(fs);
			
			sheet1 = wb.getSheetAt(0);
			
			parseTemplateFile(sheet1);
		} 
		catch (IOException e) 
		{
			System.out.println("IOException");
			//throw new ClientPlanGeneratorException(10,"Could not open the workbook of the file " + templateFileName + " extended info = " + e);
		}
		catch (ClientPlanGeneratorException e) 
		{
			System.out.println("ClientPlanGeneratorException");
			//throw new ClientPlanGeneratorException(10,"Could not open the workbook of the file " + templateFileName + " extended info = " + e);
		}
	
		logger.debug("... End ClientPlanGenerator Constructor");
	}
	
	public ClientPlanGenerator(String log4jPropertiesFileLocation) 
	{
		//Grab log configuration file
		PropertyConfigurator.configure(log4jPropertiesFileLocation);
		//BasicConfigurator.configure();
		logger.debug("ClientPlanGenerator Constructor Begin....");
					
		logger.debug("... End ClientPlanGenerator Constructor");
	}
	
	public void ClientPlanGeneratorGo(String templateFileURL, HashMap miscData) 
	throws ClientPlanGeneratorException
	{
		//Grab log configuration file
		//PropertyConfigurator.configure("DARTTrackingFigures.Properties");
		//BasicConfigurator.configure();
		logger.debug("ClientPlanGenerator Constructor Begin....");
		
		this.miscData = miscData;
		
		setTemplateFileName(templateFileURL);
		
		//Get 'Sheet1' worksheet
		try 
		{
			wb = new HSSFWorkbook(fs);
		} 
		catch (IOException e) 
		{
			throw new ClientPlanGeneratorException(10,"Could not open the workbook of the file " + templateFileName + " extended info = " + e);
		}
	
		sheet1 = wb.getSheetAt(0);
	
		parseTemplateFile(sheet1);
	
		logger.debug("... End ClientPlanGenerator Constructor");
	}
	
	/**
	 * Method thats parsed the Excel document constructing the template cell styles to be used on insertion.
	 * 
	 * This method also replaces the miscilaniouse references in the sheet with their corrisponding value. 
	 * 
	 * @param sheet1 the sheet to be parsed
	 * @throws ClientPlanGeneratorException
	 */
	private void parseTemplateFile(HSSFSheet sheet1) throws ClientPlanGeneratorException
	{	
		logger.debug("");
		logger.debug("ParseTemplateFile Begin....");
		
		HSSFWorkbook wb = null;
		
		int firstStyleEncounteredRowNo = -1;
		
		Iterator physicalRows = sheet1.rowIterator();
		
		int noOfMergedRegions = sheet1.getNumMergedRegions();
		logger.debug("- noOfMergedRegions = " + noOfMergedRegions);
		
		int count = 0;
		
		//iterate through rows that contain data
		for ( Iterator i = sheet1.rowIterator(); i.hasNext(); ) 
		{	
			count++;
			
			HSSFRow rowElement = (HSSFRow) i.next();
			//iterate through cells that contain data
			for (int x = rowElement.getFirstCellNum(); x < rowElement.getLastCellNum(); x++)
			{	
				if (rowElement.getLastCellNum() > sheetCellWidth)
				{
					sheetCellWidth = rowElement.getLastCellNum();
				}
				
				HSSFCell cellElement = rowElement.getCell((short)x);
		
				String cellValue = "";
				int cellType = -1;
		
				//get the cell type and its associated value
				try 
				{
					cellType = cellElement.getCellType();
			
					switch (cellType)
					{	
						case 0: //CELL_TYPE_NUMERIC    
							cellValue = String.valueOf(cellElement.getNumericCellValue());
						break;
				
						case 1: //CELL_TYPE_STRING 
							cellValue = cellElement.getStringCellValue();
						break;
				
						case 2: //CELL_TYPE_FORMULA 
							cellValue = "";//cellElement.getCellFormula();
						break;
				
						case 3: //CELL_TYPE_BLANK  
					
						break;
				
						case 4: //CELL_TYPE_BOOLEAN   
							cellValue = String.valueOf(cellElement.getBooleanCellValue());
						break;
				
						case 5: //CELL_TYPE_ERROR    
							cellValue = String.valueOf(cellElement.getErrorCellValue());
						break;
				
						default: throw new java.lang.NullPointerException();
					}
				} 
				catch (java.lang.NullPointerException e) 
				{
					cellValue = "NULL CELL";
				}

				logger.debug("split length " + cellValue.split("¬").length + " value = " + cellValue);
								
				//if a cell value contains the character '¬' then create a cell style				
				if ((cellValue.indexOf(172) != -1) && (cellValue.split("¬").length == 2))
				{	
					if (firstStyleEncounteredRowNo == -1)
					{
						firstStyleEncounteredRowNo = rowElement.getRowNum();
					}
					
					String arryValueSplit[] = cellValue.split("¬");
					logger.debug("- " + arryValueSplit[0] + " - " + arryValueSplit[1]);
					
					//if this column style type does not already exists then create it
					if (mapCellStyles.get(cellValue) == null)
					{	
						TemplateCell newCell = new TemplateCell(cellElement,sheet1,rowElement);
						mapCellStyles.put(cellValue,newCell);
						logger.debug("- Style Added called: " + cellValue + " isCellPartOfAMerge?:" + newCell.isCellPartOfAMerge() + " NumberOfCellOnXAxis:" + newCell.getNumberOfCellOnXAxisInMerge() + " NumberOfRowsOnYAxis:" + newCell.getNumberOfRowsOnYAxisInMerge());
					}
				}
				else if ((cellValue.indexOf(172) != -1) && (cellValue.split("¬").length == 3)) //Cell needs its value replacing with a value from the misc data array
				{	
					String[] cellCurrentValue = cellValue.split("¬");
					//System.out.println(" cellValue " + cellValue); 
					Iterator it = miscData.keySet().iterator();            
					while(it.hasNext())            
					{                
						String tmp = (String)it.next();                
						
	
						if (cellCurrentValue[1].equalsIgnoreCase(tmp))
						{	
							//System.out.println("cellType " + cellElement.getCellType());
							
							//default set the cell type to be of type string
							cellType = 1;
							
							String cellStringValue = cellCurrentValue[0].trim() + (String)miscData.get(tmp) + cellCurrentValue[2].trim();
							
							//Try to parse the cell value as a date
							try 
							{
								DateFormat dateFormatObject;
								dateFormatObject = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
								dateFormatObject.setTimeZone(TimeZone.getTimeZone("GMT"));
								dateFormatObject.parse(cellStringValue);
								cellType = 6;
							} 
							catch (ParseException e) 
							{
								//No need to do anything, just trying to see if it can be parsed
							}
							
							//if the cell value could not be parsed as a date try to parse it as a value
							if (cellType == 1)
							{							
								try
								{
									new Double(cellStringValue);
									cellType = 0;
								}
								catch (NumberFormatException e2)
								{
									//No need to do anything, just trying to see if it can be parsed
								}
							}
							
							writeCellValue(cellElement,cellType,cellStringValue);
							
							//writeValueToSheet(sheet1, rowElement.getRowNum(), cellElement.getCellType(), cellCurrentValue[0].trim() + (String)miscData.get(tmp) + cellCurrentValue[2].trim(), new TemplateCell(cellElement,sheet1,rowElement), cellElement.getCellNum());
						}
					}
				}
				else if ((cellValue.indexOf(172) != -1) && (cellValue.split("¬").length == 4)) //Defining the start of the footer section
				{	
					FooterStartsOnRow = rowElement.getRowNum() -1;
					cellElement.setCellValue("");
				}
			}
			
			if (!i.hasNext())
			{
				lastRowIndex = rowElement.getRowNum();
			}
		}
				
		lengthOfFooter = lastRowIndex - (FooterStartsOnRow + 1);
		
		//TODO: add check that FooterStartsOnRow does not still equal 0;
		
		//record height of footer rows
		for(int r=FooterStartsOnRow; r <= lastRowIndex; r++)
		{	
			if (sheet1.getRow(r) != null)
			{
				footerRowHeights.put(new Integer(r - FooterStartsOnRow),new Short(sheet1.getRow(r).getHeight()));
			}
		}
		
		//delete rows containing style examples
		for(int s=firstStyleEncounteredRowNo; s <= (FooterStartsOnRow + 1); s++)
		{	
			HSSFRow tmp = sheet1.getRow(s);
			
			if (tmp != null)
			{
				sheet1.removeRow(sheet1.getRow(s));
			}
		}
		
		//Move footer of spreadsheet up so the template is ready to be written to
		int toffset = (firstStyleEncounteredRowNo - FooterStartsOnRow) + 1;
		sheet1.shiftRows(FooterStartsOnRow,lastRowIndex,toffset);
		
		footerOffset = FooterStartsOnRow - (firstStyleEncounteredRowNo)  +1 ;
		FooterStartsOnRow = FooterStartsOnRow + (firstStyleEncounteredRowNo - FooterStartsOnRow)  +1;
	
		//Set the new position of the footer
		//FooterStartsOnRow = firstStyleEncounteredRowNo + 1;
		
		logger.debug(".....ParseTemplateFile End");
	}
	
	/**
	 * Method that returns the row number of the first instance a style name was encountered when the template was parsed.
	 * 
	 * Returns 999999999 if style not found
	 * 
	 * @param styleName the name of the style to be searched for
	 * @return the row number the style was first encountered, if not found returns 999999999
	 */
	private int getFirstRowWhereStyleEncountered(String styleName)
	{
		int startRowNumber = 999999999;
		
		//Find the row number to start writing the datagrid out to by searching the mapCellStyles for a match		
		Iterator it = mapCellStyles.keySet().iterator();            
		while(it.hasNext())            
		{                
			String tmp = (String)it.next();                
			
			String arryValueSplit[] = tmp.split("¬");
			//System.out.println("- Key Value:" + tmp + " " + arryValueSplit[0] + " == " + styleName);
			
			if (arryValueSplit[0].equals(styleName))
			{
				//get the row number from the corrisponding template cell 
				TemplateCell cellStyleArray = (TemplateCell) mapCellStyles.get(tmp);
				int thisRow = cellStyleArray.getRowObject().getRowNum();
				if (thisRow < startRowNumber)
				{
					//System.out.println("thisRow " + thisRow + " + totalNoOfRowsWrittenTo " + totalNoOfRowsWrittenTo);
					startRowNumber = thisRow + totalNoOfRowsWrittenTo;
				}
			}
		}
		
		return startRowNumber;
	}
	
	public void writeDataGridToWorkSheet(ExcelDataGrid dataGrid) 
	throws ClientPlanGeneratorException
	{		
		logger.debug("");
		logger.debug("WriteDataGridToWorkSheet Begin....");
				
		String gridName = dataGrid.getGridName();
		
		//Find the row number to start writing the datagrid out to by searching the mapCellStyles for a match		
		int startRowNumber = getFirstRowWhereStyleEncountered(gridName);
		
		logger.debug("- startRowNumber: " + startRowNumber);
		
		//if row number to start writing the datagrid is not found then throw exception
		if (startRowNumber == 999999999)
		{
			throw new ClientPlanGeneratorException(333,"The Data Grid sent to the method does not appear to be appropriate for the template file already specified. No style could be found for the data grid's name.");
		}
				
		int offSetAmount = (dataGrid.getNoOfRows() + 20);

		//Move footer of spreadsheet down out of the way for now
		sheet1.shiftRows(FooterStartsOnRow,lastRowIndex,offSetAmount);
					
		//init variables before write operation comences
		int noOfRowsWrittenTo = 0;
		
		HSSFRow targetRow = null;
		
		//Write values to the worksheet
		for (int i = 0; i < dataGrid.getNoOfRows(); i++)
		{
			//initialise the cell position counter
			int cellPositionCursor = 0;
			
			int extraRowsWrittenTo = 0;
			
			//set row to write to
			targetRow = sheet1.createRow(startRowNumber + noOfRowsWrittenTo);
			
			//set the row's relative position in the excel document
			mapDataRowPositionToExcelRowNumb.put(new Integer(i),new Integer(targetRow.getRowNum() + 1));
			
			//get the row style to employ
			//String rowDataStyle = dataGrid.getRowDataStyle(i);

			logger.debug("- row out " + (startRowNumber + noOfRowsWrittenTo));
			for (int y = 0; y < dataGrid.getNoOfColumns(); y++)
			{	
				ExcelCellValue currentCell = dataGrid.getExcelCellValueByPosition(i,y);
				
				//get the cell data type
				Integer cellType = new Integer(currentCell.getCellDataType());
				
				//get the cell style
				String cellRefValue = currentCell.getDataReference();
				
				//get the cell reference value ignoring the unique delimiter added to allow duplicate column references (use in Visual plans etc..)
				cellRefValue = (cellRefValue.split("~"))[0];
				
				//System.out.println("cellRefValue " +   cellRefValue);
				
				//Define the template cell style to use for outputting current cell
				TemplateCell chosenCellTemplate = null;
				String searchForStyleName = cellRefValue + "¬" + currentCell.getCellStyle();
				
				//logger.debug("- Look for style named: " + searchForStyleName);
				chosenCellTemplate = (TemplateCell) mapCellStyles.get(searchForStyleName);
								
				//If a specific cell style can not be found then use the default style for the cellStyle value
				if (chosenCellTemplate == null)
				{
					searchForStyleName = gridName + "¬" + currentCell.getCellStyle();
					chosenCellTemplate = (TemplateCell) mapCellStyles.get(searchForStyleName);
					
					//If a specific cell style still can not be found then throw error
					if (chosenCellTemplate == null)
					{
						throw new ClientPlanGeneratorException(334,"Can not find an appropriate cell sytle: " + searchForStyleName);
					}
				}
				
				//check of this cell specifies to expand across a number of columns
				int cellExpandValue = currentCell.getSkipNextXCells();
				if (cellExpandValue != 0)
				{
					//copy the TemplateCell so that the modifications do not affect the original
					TemplateCell newCellTemplate = (TemplateCell) chosenCellTemplate.clone();
					
					//Alter the TemplateCell so that is spans (1 + cellExpandValue) columns
					newCellTemplate.expandAcrossCols(cellExpandValue);
					
					chosenCellTemplate = newCellTemplate;
					
					//ensure that the next X rows are not output to the spreadsheet
					y = y + cellExpandValue;
				}
				
				//set the column's relative position in the excel document if this column has not already been added
				if (mapDataColumnPositionToExcelColChar.get(new Integer(cellPositionCursor)) == null)
				{
					// this section is involved in working out what the character column ref should be e.g. A, C, AD, CF, etc....
					int alphaOctive = cellPositionCursor / 25;
					int alphaRemainder =  (cellPositionCursor % 25);
					
					
					String excelColRef = "";
					
					if (alphaOctive >= 1)
					{
						excelColRef = (new Character((char)(64 + alphaOctive))).toString();
						
						//because the index is not starting from 0 if it is over 24
						alphaRemainder--;
					}
					
					excelColRef = excelColRef + (new Character((char)(65 + alphaRemainder))).toString();
					
					//System.out.println("cellPositionCursor:" + y + " alphaOctive:" + alphaOctive + " alphaRemainder:" + alphaRemainder + " excelColRef:" + excelColRef);
					
					mapDataColumnPositionToExcelColChar.put(new Integer(y),excelColRef);
					logger.debug("- excelColRef = " + cellPositionCursor + " = " + excelColRef);
				}
				
				int[] newRowColumnCursorPositions = writeValueToSheet(sheet1, targetRow.getRowNum(), currentCell.getCellDataType(), currentCell.getCellValue(), chosenCellTemplate, cellPositionCursor);
				if (newRowColumnCursorPositions[0] > extraRowsWrittenTo)
				{
					extraRowsWrittenTo = newRowColumnCursorPositions[0];
				}
				cellPositionCursor = newRowColumnCursorPositions[1];
	
				//Decide if a spacer cell needs to be output?
				TemplateCell spacerCellTemplate = (TemplateCell) mapCellStyles.get(searchForStyleName + "SP");
				if (spacerCellTemplate != null)
				{
					newRowColumnCursorPositions = writeValueToSheet(sheet1, targetRow.getRowNum(), 1, "", spacerCellTemplate, cellPositionCursor);
					if (newRowColumnCursorPositions[0] > extraRowsWrittenTo)
					{
						extraRowsWrittenTo = newRowColumnCursorPositions[0];
					}
					cellPositionCursor = newRowColumnCursorPositions[1];
				}
			}
			
			noOfRowsWrittenTo = (noOfRowsWrittenTo + extraRowsWrittenTo) + 1;
		}
		
		//interpret the formula values that were found so that their references match the excel sheet's references
		reinterpretFormulas();
				
		int newFooterStartPosition = (noOfRowsWrittenTo - offSetAmount) -1;
				
		//Move footer of spreadsheet back up to the correct position
		sheet1.shiftRows((FooterStartsOnRow + offSetAmount),(lastRowIndex + offSetAmount),newFooterStartPosition);
		//int lengthOfFooter = lastRowIndex - FooterStartsOnRow;
		FooterStartsOnRow = (FooterStartsOnRow + offSetAmount + newFooterStartPosition + 1);
		
		
		//Move printable area 
		if (wb.getPrintArea(0) != null)
		{
			
			String[] currentPrintArea = wb.getPrintArea(0).split("\\$");
						
			Integer currentEndRow = new Integer(currentPrintArea[4]);
			int newEndRow = FooterStartsOnRow + (lengthOfFooter - (lastRowIndex - currentEndRow.intValue()));
			String newPrintArea = currentPrintArea[1] + "$" + currentPrintArea[2] + "$" +  currentPrintArea[3] + "$" + newEndRow;
			
			System.out.println("print area == " + currentPrintArea[1] + "$" + currentPrintArea[2] + "$" +  currentPrintArea[3] + "$" + newEndRow);
			
			wb.setPrintArea(0,newPrintArea);
		}
				
		totalNoOfRowsWrittenTo = totalNoOfRowsWrittenTo + noOfRowsWrittenTo;
		
		logger.debug("....WriteDataGridToWorkSheet End");
	}

	
	/**
	 * Method used to write a value to a cell and applying a specified style. This method will apply 
	 * merge regions to multiple cells if the specified cell style requires it.
	 * 
	 * @param sheetObject			the sheet object that contains the target cell to write to.
	 * @param rowNumber				specifies the row that the target cell to write to.
	 * @param cellType				the Excel cell type that the target cell is to be set as. See {@link org.apache.poi.hssf.usermodel.HSSFCell HSSFCell} for reference values. 
	 * @param cellValue				the value you want to set the target cell to.
	 * @param cellStyle				the {@link uk.mediatel.cms.excelGenerator#TemplateCell TemplateCell} object you want to apply to the target cell.
	 * @param cellPositionCursor	the column count which specifies the target cell of the row to write the information to.
	 * @return the new cell cursor position
	 */
	private int[] writeValueToSheet(HSSFSheet sheetObject,int rowNumber, int cellType, Object cellValue, TemplateCell cellStyle, int cellPositionCursor)
	{
		logger.debug("writeValueToSheet begin.....");
		
		HSSFRow targetRow = sheetObject.getRow(rowNumber);
		HSSFCell cellObject = targetRow.getCell((short) cellPositionCursor);
		
		int noOfCellsToWrite = cellStyle.getNumberOfCellOnXAxisInMerge();
		int toColNumb = (cellPositionCursor + noOfCellsToWrite) - 1;
		
		//logger.debug("- Cell Position " + cellPositionCursor + " '" + cellValue + "'");
		//System.out.println("- Cell Position " + cellPositionCursor + " row position" + noOfRowsWrittenTo + "");
		
		//get the template cell style array
		HSSFCellStyle[][] arryCellsStyleObjects = cellStyle.getArrayStyleObjects();
		
		//extract the one dimentional array of cell widths and row heights
		short[] arryCellWidths = cellStyle.getArryCellWidths();	
		short[] arryRowHeights = cellStyle.getArryRowHeights();			
		
		int rowNoCount = -1;
		
		//do we need to create a merge region
		if (cellStyle.isCellPartOfAMerge())
		{
			//extract the dimentions of the cell template's merge 
			int noOfRowsToWrite = cellStyle.getNumberOfRowsOnYAxisInMerge();
							
			//set new dimentions
			int fromRowNumb = rowNumber;
			int toRowNumb = (rowNumber + noOfRowsToWrite) - 1;
			int fromColNumb =  cellPositionCursor;
			
			//creat a new merge
			Region newMergeRegion = new Region(fromRowNumb, (short) fromColNumb, toRowNumb, (short) toColNumb); 
			sheetObject.addMergedRegion(newMergeRegion);		
							
			//set the styles for the merge
			for (int r = fromRowNumb; r <= toRowNumb; r++)
			{   
				rowNoCount++;
				
				int cellNoCount = 0;
				HSSFRow tempRowObj = sheetObject.getRow(r);
				
				//If the row is a phantom row then make it a solid row
				if(tempRowObj == null)
				{
					tempRowObj = sheetObject.createRow(r);
				}
				
				rowHeights.put(new Integer(r),new Short(arryRowHeights[rowNoCount]));
				
				for (int c = fromColNumb; c <= toColNumb; c++)
				{	
					logger.debug("- Col Loop " + c);
					HSSFCell tempCellObj = tempRowObj.createCell((short) c);
					
					logger.debug("- Write Style " + r + " " + c + " " + arryCellsStyleObjects[rowNoCount][cellNoCount]);
					
					tempCellObj.setCellStyle((HSSFCellStyle) arryCellsStyleObjects[rowNoCount][cellNoCount]);

					columnWidths.put(new Integer(c),new Short(arryCellWidths[cellNoCount]));

					cellNoCount++;
				}
			}
		}
		else
		{	
			logger.debug("- Row " + rowNumber + " Col " + cellPositionCursor);
			HSSFCell tempCellObj = targetRow.createCell((short) cellPositionCursor);
			tempCellObj.setCellStyle(arryCellsStyleObjects[0][0]);
			
			logger.debug("- Write Style " + rowNumber + " " + cellPositionCursor + " " + arryCellsStyleObjects[0][0]);
			columnWidths.put(new Integer(cellPositionCursor),new Short(arryCellWidths[0]));
			rowHeights.put(new Integer(rowNumber),new Short(arryRowHeights[0]));
			
			rowNoCount++;
		}
		
		cellObject = targetRow.getCell((short) cellPositionCursor);
		//logger.debug("- Cell Style on way out = " + cellObject.getCellStyle() + " " + cellStyle.getStyleObject().equals(arryCellsStyleObjects[0][0]));
		
		//write the specific value to the cell  
		writeCellValue(cellObject, cellType, cellValue);
		
		int[] returnValue = {rowNoCount,toColNumb + 1};
		
		logger.debug("......writeValueToSheet end");
		return returnValue;
	}
	
	private void writeCellValue(HSSFCell cellObject, int cellType, Object cellValue)
	throws java.lang.NullPointerException
	{
		switch (cellType)
		{	
			case 0: //CELL_TYPE_NUMERIC   
				//set the cell type
				if (!((String)cellValue).equals(""))
				{
					Double cellDoubleValue = new Double((String)cellValue);
					cellObject.setCellValue(cellDoubleValue.doubleValue());
					cellObject.setCellType(cellType);
				}
			break;
			
			case 1: //CELL_TYPE_STRING 
				//set the cell type
				cellObject.setCellType(cellType);
				String tmpCellValue = (String)cellValue;
				cellObject.setCellValue(tmpCellValue.trim());
			break;
			
			case 2: //CELL_TYPE_FORMULA 
				//set the cell type only if there is a formula to write, blank formulas causes Excel to crash!!!!
				if (!((String)cellValue).equals(""))
				{	
					//can not interpret the value of the formula untill all the data has been placed on the sheet, so add it to a queue to be done after all data writing has finished
					cellObject.setCellValue((String)cellValue);
					cellObjectsContainingFormula.add(cellObject);
					
					//cellObject.setCellType(cellType);
					//cellObject.setCellFormula((String)cellValue);
				}
			break;
			
			case 3: //CELL_TYPE_BLANK  
				
			break;
			
			case 4: //CELL_TYPE_BOOLEAN   
				//set the cell type
				cellObject.setCellType(cellType);
				
				//derive if the value is true or false
				boolean boolCellValue = false;
				String cellStringValue = (String)cellValue;
				
				if (cellStringValue.equalsIgnoreCase("true"))
				{	//System.out.println(cellStringValue);
					boolCellValue = true;
				}
				
				cellObject.setCellValue(boolCellValue);
			break;
						
			case 6: //extended type of date
	    		
				DateFormat dateFormatObject;
				
				dateFormatObject = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				dateFormatObject.setTimeZone(TimeZone.getTimeZone("GMT"));
				
				String cellStringDateValue = (String) cellValue;
				Date cellDateValue = null;
				try 
				{
					cellDateValue = dateFormatObject.parse(cellStringDateValue);
				} 
				catch (ParseException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}   
								
				cellObject.setCellValue(cellDateValue);
				
			break;
			
			default: throw new java.lang.NullPointerException();
		}
	}
	
	/**
	 * Method to loop over all the cells that were found with formulas, re-interpret the formula string to
	 * update the cell references, and to set the cell values to the cells themselves. 
	 * 
	 */
	private void reinterpretFormulas()
	{	
		logger.debug("reinterpretFormulas begin....");
		
		//Itorate over all the cells found with formulas in them
		Iterator it =  cellObjectsContainingFormula.iterator();
		while(it.hasNext())
		{
			
			HSSFCell cellObject = (HSSFCell) it.next();
			StringTokenizer tokens = new StringTokenizer(cellObject.getStringCellValue(),"[");
			logger.debug("- " + cellObject.getStringCellValue());
			
			String outputString = "";
			
			//Go over the elements of the formula value
			while (tokens.hasMoreTokens()) 
			{
				String stringElement = tokens.nextToken();
				
				//check if the element has a '|' & ']' characters, if not then ignore
				if (stringElement.indexOf(93) != -1 && stringElement.indexOf(124) != -1)
				{
					StringTokenizer item = new StringTokenizer(stringElement,"|");
					
					while (item.hasMoreTokens()) 
					{	
						String subElement = item.nextToken();
						
						if (subElement.indexOf(93) == -1)//first side of X|X reference
						{
							//System.out.println("1st '" + subElement + "' " + mapDataColumnPositionToExcelColChar.get(new Integer(subElement)));
							
							outputString = outputString + mapDataColumnPositionToExcelColChar.get(new Integer(subElement));
						}
						else//second side of X|X reference
						{
							
							String[] splitSubElement = subElement.split("]");
							
							//System.out.println("2nd '" + splitSubElement[0] + "' " + mapDataRowPositionToExcelRowNumb.get(new Integer(splitSubElement[0])));
												
							outputString = outputString + mapDataRowPositionToExcelRowNumb.get(new Integer(splitSubElement[0]));
							outputString = outputString + splitSubElement[1];
						}
					}
				}
				else
				{
					outputString = outputString + stringElement;
				}
			}
			
			//cellObject.setCellType(2);
			cellObject.setCellFormula(outputString);
			
			logger.debug("- outputString " + outputString);
			logger.debug("......reinterpretFormulas end");
		}
	}
	
	private void applyColumnWidths()
	{
		logger.debug("ApplyColumnWidths begin....");
		
		Iterator it = columnWidths.keySet().iterator();            
		while(it.hasNext())            
		{                
			Integer tmp = (Integer)it.next();                
						
			Short columnWidthValue = (Short) columnWidths.get(tmp);
			sheet1.setColumnWidth((short)tmp.intValue(), columnWidthValue.shortValue());   
		}
		
		logger.debug("......ApplyColumnWidths end");
	}
	
	private void applyRowHeights()
	{
		logger.debug("ApplyColumnWidths begin....");
				
		Iterator it = rowHeights.keySet().iterator();            
		while(it.hasNext())            
		{                
			Integer tmp = (Integer)it.next();                
	
			HSSFRow tmpRowObject = sheet1.getRow(tmp.intValue());
			Short rowHeight = (Short) rowHeights.get(tmp);
			tmpRowObject.setHeight(rowHeight.shortValue());           
		}
		
		Iterator it2 = footerRowHeights.keySet().iterator();
		int count = 0;
		while(it2.hasNext())            
		{                
			Integer tmp = (Integer)it2.next();                
			HSSFRow tmpRowObject = sheet1.getRow((FooterStartsOnRow -1) + tmp.intValue());
			Short rowHeight = (Short) footerRowHeights.get(tmp);
			tmpRowObject.setHeight(rowHeight.shortValue());  
			count++;
		}
		
		logger.debug("......ApplyColumnWidths end");
	}
	
	public void writeFileToDisk(String generatedFileName) throws ClientPlanGeneratorException
	{
		
		applyColumnWidths();
		applyRowHeights();
		
		try
		{
			FileOutputStream fileOut = new FileOutputStream(generatedFileName);
			wb.write(fileOut);
			fileOut.close();
		} 
		catch (FileNotFoundException e)
		{
			throw new ClientPlanGeneratorException(10,"Could write to the file " + generatedFileName + " extended info = " + e);
		} 
		catch (IOException e) 
		{
			throw new ClientPlanGeneratorException(10,"Could write to the file " + generatedFileName + " extended info = " + e);
		}
	}
	
	/**
	 * @param string
	 */
	public void setTemplateFileName(String templateFileName) 
	throws ClientPlanGeneratorException
	{
		try 
		{
			fs = new POIFSFileSystem(new FileInputStream(templateFileName));
			this.templateFileName = templateFileName;
			logger.debug("-specified template file found");
		} 
		catch (FileNotFoundException e) 
		{
			throw new ClientPlanGeneratorException(10,"Could not find the file " + templateFileName + " extended info = " + e);
		} 
		catch (IOException e) 
		{
			throw new ClientPlanGeneratorException(10,"Could not open the file " + templateFileName + " extended info = " + e);
		}
	}
		
	public static void main(String[] args)
	throws ClientPlanGeneratorException
	{
		HashMap temp =  new HashMap();
		temp.put("CampaignStartDate","1976-05-01 00:00:00");
		temp.put("Ratecard","12000");
		
		ClientPlanGenerator tim = new ClientPlanGenerator("AllEncompassPlan.xls", temp); //AllEncompassPlan2.xls coi2.xls
		/*
		uk.mediatel.databasetools.QueryingDatabase tmp = new uk.mediatel.databasetools.QueryingDatabase();
		java.sql.ResultSet tmp2 = tmp.ExecuteQuery("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://TARIN:433/UBERPress","sa","xizqst45#","SELECT CIN, Sum(BurstID) AS burst, 'tim' AS name FROM  tblPressBurst GROUP BY CIN");
		
		java.sql.ResultSet tmp3 = tmp.ExecuteQuery("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://TARIN:433/UBERPress","sa","xizqst45#","SELECT CIN, CreatedByCompanyID, ProductID, CampaignName, CampaignFromDate, CampaignToDate, CampaignStatusID, CampaignBudgetAmount, TeamID,   CampaignCurrencyID, CampaignApprovalNbr, AdvertiserCompanyID, VATRate, MediatelBrandID, BurstApprovedAmount, BurstTotalAmount, BurstCompleteAmount, RestrictedAccessInd, ActiveInd, LastUpdateDatetime, LastUpdateUserID FROM tblPressCampaign");
		//java.sql.ResultSet tmp3 = tmp.ExecuteQuery("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://TARIN:433/UBERPress","sa","xizqst45#","SELECT CIN, CreatedByCompanyID, ProductID, CampaignName FROM tblPressCampaign");
		*/
		
		ExcelDataGrid testExcelDataGrid = new ExcelDataGrid("Data");
				
		try 
		{
			testExcelDataGrid.addColumn(0,1,"Col1");
			testExcelDataGrid.addRow("TimsUnique1",0,"Heading1",1);
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique1",0,"CreatedByCompanyID");
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique1",0,"CreatedByCompanyID");
			
			//testExcelDataGrid.insertRecordsetIntoGrid(0,1,tmp3,"0,0,0,1,6,1,0,0,0,0,1,1,1,1,1,1,1,4,4,1,1","Value");
			//testExcelDataGrid.insertRecordsetIntoGrid(0,1,tmp2,"1,1,1","Value");
			
			/*
			testExcelDataGrid.addRow("TimsUnique1",0,"Heading1",1);
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique1",0,"CreatedByCompanyID");
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique1",1,"ProductID");
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique1",13,"Costs",1,"Cost","Heading1",1);
			
			testExcelDataGrid.addRow("TimsUnique10",1,"Heading2",1);
			
			testExcelDataGrid.addRow("TimsUnique",10,"SubHeading",1);
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique",0,"First Sub Header");
			
			testExcelDataGrid.addRow("TimsUnique3",10,"SubTotal",2);
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique3",10,"SUM([12|11] : [12|19])"); //SUM([I|0] : [I|9]) SUM(I15 : I23)
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique3",0,"SUM([0|1]:[0|9])");
			
			testExcelDataGrid.addColumn(9,1,"VisPlan~1");
			testExcelDataGrid.addColumn(9,1,"VisPlan~2");
			
			testExcelDataGrid.addRow("TimsUnique2",testExcelDataGrid.getNoOfRows(),"Total1",1);
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique2",0,"234");
			testExcelDataGrid.addRow("TimsUnique20",testExcelDataGrid.getNoOfRows(),"Total2",1);
			testExcelDataGrid.setRowCellValueByUniqueKey("TimsUnique20",13,"Costs",1,"Cost","Total2",1);
			
			//testExcelDataGrid.updateRowCellStyleByUniqueRef("TimsUnique3","Approved");
			*/
		}
		catch (IllegalArgumentException e)
		{
			e.printStackTrace();
		}
		/* catch (java.sql.SQLException e) 
		{
			e.printStackTrace();
		}
		*/
		
		//System.out.println(tim.getFirstRowWhereStyleEncountered("Data"));
		
				
		tim.writeDataGridToWorkSheet(testExcelDataGrid);
		
		
		
		tim.writeFileToDisk("outputTest.xls");
		
	}
	
}