import java.io.*;
import java.util.*;
import java.sql.*;
import java.math.*;
import javax.servlet.*;
import javax.servlet.http.*;

import javax.naming.*;
import javax.sql.*;

import com.oroinc.text.perl.Perl5Util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.hssf.usermodel.*;

public class loadMarySheet extends HttpServlet {

	PrintWriter 		out 			= null;
	Connection 		  	con 			= null;
	Statement 	      		stmt 			= null;
		Statement 	      		stmt2 			= null;
			StringBuffer				sbResponse = null;
			StringBuffer				debugTrace = null;
	String			whichfolder = null;
	String			whichfile = null;	
	String			whichsheet = null;	
	String			startrow		= null;
	String			endrow 			= null;
	String			startcol 		= null;
	String			endcol 			= null;
	
	String			startrow_lab = null;
	String			endrow_lab = null;
	String			startcol_lab= null;
	String			endcol_lab = null;
	boolean			bError = false;
	String 			update			= null;
	String 			datatracker     = null;
		String 			contexttracker     = null;
		String testlab = null;
	private DataSource datasource = null;
	
	Perl5Util 			util 			= new Perl5Util();
	
  public void init() throws ServletException {
      // (DataSource) envCtx.lookup("jdbc/eSites");
 	 
	/*
    try {   Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
    	    datasource = (DataSource) envCtx.lookup("jdbc/medscapesurvey");
		
		

   }
    catch (Exception e) {  throw new ServletException(e.getMessage()); }
*/

  }
  


  
   public void doGet(HttpServletRequest req, HttpServletResponse res)    // DEBUG ONLY
                                throws ServletException, IOException {
                    doPost(req, res);
     }

public boolean nomoreData(int r,int c,int numcols, HSSFWorkbook wb, HSSFSheet sh){
	boolean isnull = true;

	for(int i=c; i<(c+ (numcols+1));i++){
		
		try{
			Cell currcell = sh.getRow(r).getCell(i,wb.getMissingCellPolicy());
			if(currcell == null){
				;
			}
			else{
				try{
					String title = currcell.getStringCellValue();
					if(title.length()==0){
						;
					}
					else{
						isnull = false;
						break;
					}
				}
				catch(Exception e){
					;
				}

			
			}
		
		}
		catch(Exception e){
			;
		}
		
	}
	return isnull;
	
}
                              
 public void doPost(HttpServletRequest req, HttpServletResponse res)
                                throws ServletException, IOException {
									String					sErrorMessage	 = "";
								 String					sErrorCode		= "";
									 	debugTrace = new StringBuffer("<trace>\n");
										whichfolder = getStringParameter(req, "folder", "0");
										whichfile = getStringParameter(req, "filename", "0");
								
										
									
										
										
										
										int whichsheet_int = 2;
									
										
										
										
										sbResponse		= new StringBuffer("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n");
										sbResponse.append("		<display>\n");
										
										int rowstart = 0;
										int rowend = 0;
										int colstart = 0;
										int colend = 0;
										
										int currcol = 0;
										int currrow = 0;
										
										int sheetcount = 0;
									
										try {
											String testpath = req.getSession().getServletContext().getRealPath("/");
										
									    	FileInputStream file = new FileInputStream(new File(testpath+"spreadsheets/"+whichfolder,whichfile+".xls"));

									    	HSSFWorkbook workbook = new HSSFWorkbook(file);
											
											sheetcount = workbook.getNumberOfSheets();
											int i,ii;
											
											
											
											
											for(i=1; i<sheetcount; i++){
										
									    	HSSFSheet sheet = workbook.getSheetAt(i);
											sbResponse.append("		  <sheet nm=\""+i+"\">\n");
									    	Cell currcell;
										//	Cell testcell = sheet.getRow(3).getCell(0,workbook.getMissingCellPolicy());
									    	//Update the value of cell
										
											//find the location of the meta block
											
										
											String testStr = "";
											double testVal = 0.0;
											
											//GET TITLE
											
											rowend = 2;
											colend = 2;
											rowstart = 0;
											colstart = 0;
											currrow = rowstart;
											currcol = colstart;
											
											int currloop = 0;
											int loopend = (rowend * colend) + 10;
											
											
											debugTrace.append("");
											debugTrace.append("starting loop\n");
											
											
										
											
											sbResponse.append("		    <title>");
											boolean foundTitle = false;
											String title = "";
											
											while( (!(foundTitle) && (currloop < loopend))){
													//debugTrace.append("in title loop\n");
												try{
													currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
													if(currcell == null){
														//debugTrace.append("currcell is null\n");	
													}	
													try{
														//title = currcell.getStringCellValue();
														title = escapeXML(currcell.getStringCellValue(),util);
														foundTitle = true;
														sbResponse.append(title+"</title>\n");
														//debugTrace.append(testStr+"\n");
													}
													catch(Exception e){
														//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
													}
													
												}
												catch(Exception e){
													//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
												}
											//	debugTrace.append(testStr+"\n");
												currrow++;
												if(currrow == rowend){
													currrow = 0;
													currcol++;
													
												}
												currloop++;
											}
											
											
											
											
												//GET LABELS

											
												rowstart = 0;
												colstart = 0;
												
												rowend = 100;
												colend = 10;
												
												currrow = rowstart;
												currcol = colstart;

												currloop = 0;
												 loopend = (rowend * colend) + 10;


												debugTrace.append("");
												debugTrace.append("starting loop\n");




												sbResponse.append("		    <labels>\n");
												boolean foundLabels = false;
												String labtit = "";

												while( (!(foundLabels) && (currloop < loopend))){
														
													try{
														currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
														if(currcell == null){
															//debugTrace.append("currcell is null\n");	
														}	
														try{
															labtit = currcell.getStringCellValue();
															if(labtit.equals("LABELS")){foundLabels=true;}
															
														}
														catch(Exception e){
															//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
														}

													}
													catch(Exception e){
														//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
													}
												//	debugTrace.append(testStr+"\n");
													currrow++;
													if(currrow == rowend){
														currrow = 0;
														currcol++;

													}
													currloop++;
												}
												
												String currlab="";
												int numlabs = 0;
												currloop = 0;
												 loopend = (rowend * colend) + 10;
													
												boolean foundLabelEnd = false;
												
												while( (!(foundLabelEnd) && (currloop < loopend))){
														
														try{
															currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
															if(currcell==null){
																foundLabelEnd = true;
																
															}
															else{
																try{
																currlab = escapeXML(currcell.getStringCellValue(),util);
																if(currlab.length()>0 && (!currlab.equals(" ")) && (!currlab.equals("  "))){
																sbResponse.append("		      <label>"+currlab+"</label>\n");
																//temp
																String currlabcode = attachAllCodes(currcell.getStringCellValue());
																//sbResponse.append("		      <labelcode>"+currlabcode+"</labelcode>\n");
																numlabs++;
																}
																if(currlab.length()==0){
																	foundLabelEnd=true;
																}
																
																//debugTrace.append(currlab+"\n");
																}
																catch(Exception e){
																	currlab = String.valueOf((int)currcell.getNumericCellValue());
																	if(currlab.length()>0 && (!currlab.equals(" ")) && (!currlab.equals("  "))){
																	sbResponse.append("		      <label>"+currlab+"</label>\n");
																	numlabs++;
																	}
																	if(currlab.length()==0){
																		foundLabelEnd=true;
																	}
																	//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
																}
															}

														}
														catch(Exception e){
															foundLabelEnd = true;
															//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
														}
													//	debugTrace.append(testStr+"\n");
														currrow++;
														if(currrow == rowend){
															currrow = 0;
															currcol++;

														}
														currloop++;
													}
													
												
													
												sbResponse.append("		    </labels>\n");
													
													
													
													
												//GET LEGEND
											
												rowstart = 0;
												colstart = 0;
												
												rowend = 100;
												colend = 50;
												
												currrow = rowstart;
												currcol = colstart;

												currloop = 0;
												 loopend = (rowend * colend) + 10;


												debugTrace.append("");
											




												
												boolean foundLegend = false;
											

												while( (!(foundLegend) && (currloop < loopend))){
														
													try{
														currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
														if(currcell == null){
															//debugTrace.append("currcell is null\n");	
														}	
														try{
															labtit = currcell.getStringCellValue();
															if(labtit.equals("LEGEND")){foundLegend=true;}
															
														}
														catch(Exception e){
															//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
														}

													}
													catch(Exception e){
														//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
													}
												//	debugTrace.append(testStr+"\n");
													currrow++;
													if(currrow == rowend){
														currrow = 0;
														currcol++;

													}
													currloop++;
												}
												
												int legcount = 0;
												if(!foundLegend){
													sbResponse.append("		    <legend>NA</legend>\n");
												}
												else{
												sbResponse.append("		    <legend>\n");
												String currleg;
											
												currloop = 0;
												 loopend = (rowend * colend) + 10;
													
												boolean foundLegendEnd = false;
												
												while((!foundLegendEnd) && (currloop < loopend)){
														
														try{
															currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
															if(currcell==null){
																foundLegendEnd = true;
															}
															else{
															try{
																//currleg = currcell.getStringCellValue();
																currleg = escapeXML(currcell.getStringCellValue(),util);
																if(currleg.length()>0){
																sbResponse.append("		      <leg>"+currleg+"</leg>\n");
																legcount++;
																}
																else{
																	foundLegendEnd= true;
																}
																
																//debugTrace.append(currlab+"\n");
															}
															catch(Exception e){
																
																//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
															}
															}

														}
														catch(Exception e){
															foundLegendEnd = true;
															//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
														}
													//	debugTrace.append(testStr+"\n");
														currcol++;
														if(currcol == colend){
															currrow ++;

														}
														currloop++;
														
														
														
												}	
													
												
													
												sbResponse.append("		    </legend>\n");
												}
												
												
												
												
												//GET METADATA
											
												rowstart = 0;
												colstart = 0;
												
												rowend = 100;
												colend = 50;
												
												currrow = rowstart;
												currcol = colstart;

												currloop = 0;
												 loopend = (rowend * colend) + 10;


												debugTrace.append("");
											




												
												boolean foundmeta = false;
											

												while( (!(foundmeta) && (currloop < loopend))){
														
													try{
														currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
														if(currcell == null){
															//debugTrace.append("currcell is null\n");	
														}	
														try{
															labtit = currcell.getStringCellValue();
															if(labtit.equals("METADATA")){foundmeta=true;}
															
														}
														catch(Exception e){
															//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
														}

													}
													catch(Exception e){
														//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
													}
												//	debugTrace.append(testStr+"\n");
													currrow++;
													if(currrow == rowend){
														currrow = 0;
														currcol++;

													}
													currloop++;
												}
											
												if(!foundmeta){
													sbResponse.append("		    <metadata>NA</metadata>\n");
												}
												else{
												sbResponse.append("		    <metadata>\n");
												String currm;
											
												currloop = 0;
												 loopend = (rowend * colend) + 10;
													
												boolean foudmetaEnd = false;
												
												while((!foudmetaEnd) && (currloop < loopend)){
														
														try{
															currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
															if(currcell==null){
																foudmetaEnd = true;
															}
															else{
															try{
																currm = currcell.getStringCellValue();
																if(currm.length() >0){
																sbResponse.append("		      <meta>"+currm+"</meta>\n");
																}
																else{
																	foudmetaEnd = true;
																}	
																
																
																//debugTrace.append(currlab+"\n");
															}
															catch(Exception e){
																
																//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
															}
															}

														}
														catch(Exception e){
															foudmetaEnd = true;
															//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
														}
													//	debugTrace.append(testStr+"\n");
														currrow++;
														if(currrow == rowend){
															currcol ++;

														}
														currloop++;
														
														
														
												}	
													
												
													
												sbResponse.append("		    </metadata>\n");
												}
												
												
													
														//GET DATA


														rowstart = 0;
														colstart = 0;

														rowend = 100;
														colend = 100;

														currrow = rowstart;
														currcol = colstart;

														currloop = 0;
														 loopend = (rowend * colend) + 10;


														debugTrace.append("");
														debugTrace.append("starting loop\n");




														sbResponse.append("		    <data>\n");
														boolean foundData = false;
														String datatit = "";
														boolean isoverviewdata = false;

														while( (!(foundData) && (currloop < loopend))){
															
															try{
																currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
																if(currcell == null){
																	//debugTrace.append("currcell is null\n");	
																}	
																try{
																	datatit = currcell.getStringCellValue();
																	if(datatit.equals("DATA")){
																		isoverviewdata = false;
																		//debugTrace.append("FOUNDDATA\n");
																		foundData=true;
																	}
																	else if(datatit.equals("OVERVIEW DATA")){
																		isoverviewdata = true;
																		//debugTrace.append("FOUNDDATA\n");
																		foundData=true;
																	}
																	//debugTrace.append(testStr+"\n");
																}
																catch(Exception e){
																	//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
																}

															}
															catch(Exception e){
																//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
															}
														//	debugTrace.append(testStr+"\n");
															currrow++;
															if(currrow == rowend){
																currrow = 0;
																currcol++;

															}
															currloop++;
														}
														
														if(!(foundData)){
															sbResponse.append("		    </data>\n");
														}
														
														boolean gotnumdatacols = false;
														boolean dataend = false;
														boolean gotfirstdatacol = false;
														int firstcol = currcol;
														int numdatacols = 1;
														int specialtyrow = currrow;
														int currspeccol = 1;
														int currlegcount = 1;
														int datarow = 0;
														if(!isoverviewdata){
															if(foundLegend){
														 		datarow = specialtyrow+2;
															}
															else{
																datarow = specialtyrow+2;
															}
														}
														else{
															datarow = currrow;
														}
													
														String currspec = "";
														String currcat = "";
														
														if(!isoverviewdata){
															while( (!(dataend) && (currloop < loopend))){
																	
																try{
																	currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
																	if(currcell == null){
																		if(currrow==specialtyrow && nomoreData(currrow,currcol,legcount+2,workbook,sheet)){
																			dataend=true;
																			sbResponse.append("		    </data>\n");	
																		}
																		//debugTrace.append("currcell is null\n");	
																	}	
																	else{
																		try{
																			if(currrow==specialtyrow){
																				//debugTrace.append("currrow = specialtyrow\n");
																				currspec = escapeXML(currcell.getStringCellValue(),util);
																				if(currspec.length()>0){
																					sbResponse.append("		    <dataspecialty spec=\""+currspec+"\">\n");
																					sbResponse.append("		       <dataseries>\n");
																				}
																				else{
																					if(nomoreData(currrow,currcol,legcount+2,workbook,sheet)){
																						dataend=true;
																						sbResponse.append("		    </data>\n");	
																					}
																				}
																			}
																			
																			else if(currrow >= (datarow)){
																			
																				//debugTrace.append("currrow = cellrow\n");
																				sbResponse.append("                                         <cell>"+currcell.getNumericCellValue()+"</cell>\n");
																			}
																		
																	
																		
																	
																		//debugTrace.append(testStr+"\n");
																		}
																		catch(Exception e){
																			
																			//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
																		}
																	}

																}
																catch(Exception e){
																	//public boolean nomoreData(int r,int c,int numcols, HSSFWorkbook wb, HSSFSheet sh){
																		sbResponse.append("		<cellexc>"+currcol+" "+currrow+"</cellexc>\n");
																	if(currrow==specialtyrow && nomoreData(currrow,currcol,legcount+2,workbook,sheet)){
																		dataend=true;
																		sbResponse.append("		    </data>\n");	
																	}
																	//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
																}
															//	debugTrace.append(testStr+"\n");
																currrow++;
																if(currrow == ((datarow+numlabs))){
																	
																	if(foundLegend){
																		currlegcount++;
																		if(currlegcount>legcount){
																			currrow = specialtyrow;
															
																			sbResponse.append("		       </dataseries>\n");
																			sbResponse.append("		    </dataspecialty>\n");
																			currlegcount = 1;
																			currcol++;
																		}
																		else{
																			currrow = datarow;
																			sbResponse.append("		       </dataseries>\n");
																			sbResponse.append("		       <dataseries>\n");
																		}
																	}
																	else{
																		currrow = specialtyrow;
																		sbResponse.append("		       </dataseries>\n");
																		sbResponse.append("		    </dataspecialty>\n");
																	}
																	currcol++;

																}
																currloop++;
															}
														}
														else{
															while( (!(dataend) && (currloop < loopend))){
																	
																try{
																	currcell = sheet.getRow(currrow).getCell(currcol,workbook.getMissingCellPolicy());
																	if(currcell == null){
																		
																		//debugTrace.append("currcell is null\n");	
																	}	
																	else{
																		try{
																			if(currrow == (datarow)){
																				sbResponse.append("		    <dataspecialty spec=\"NA\">\n");
																				sbResponse.append("		       <dataseries>\n");
																			
																				//debugTrace.append("currrow = cellrow\n");
																				sbResponse.append("                <cell>"+currcell.getNumericCellValue()+"</cell>\n");
																			}
																			else if(currrow > datarow){
																				
																				sbResponse.append("                <cell>"+currcell.getNumericCellValue()+"</cell>\n");
																			}
																		
																	
																		
																	
																		//debugTrace.append(testStr+"\n");
																		}
																		catch(Exception e){
																			//debugTrace.append("error getting string value: "+e.getMessage()+"\n");
																		}
																	}

																}
																catch(Exception e){
																	//public boolean nomoreData(int r,int c,int numcols, HSSFWorkbook wb, HSSFSheet sh){
																		
																	if(currrow==specialtyrow && nomoreData(currrow,currcol,legcount+2,workbook,sheet)){
																		sbResponse.append("		    </data>\n");	
																	}
																	//debugTrace.append("error getting cell with missing content: "+e.getMessage()+"\n");
																}
															//	debugTrace.append(testStr+"\n");
																currrow++;
																if(currrow == ((datarow+numlabs))){
																	dataend=true;
																	sbResponse.append("		       </dataseries>\n");
																	sbResponse.append("		    </dataspecialty>\n");
																	sbResponse.append("		  </data>\n");	
																	currcol++;

																}
																currloop++;
															}
															
														}
												
											sbResponse.append("  </sheet>\n");	
											}	
											
											
										
										
											
											debugTrace.append("\n");
											
											file.close();
											debugTrace.append("reached end of trace\n");


										} catch (Exception e) {
												bError = true;
												sErrorMessage = "Exception " + e.getMessage();
												sErrorCode = "102";
										
										} 
										finally{
											if (bError) {

												sbResponse.append("    ");appendTag("status","ERROR",sbResponse);sbResponse.append("\n");
												sbResponse.append("    ");appendTag("errorCode",sErrorCode,sbResponse);sbResponse.append("\n");
												sbResponse.append("    ");appendTag("message",sErrorMessage,sbResponse);sbResponse.append("\n");
												debugTrace.append("</trace>\n");
												sbResponse.append("    ");appendTag("debug",debugTrace.toString(),sbResponse);sbResponse.append("\n");
												
													sbResponse.append("\n");
													sbResponse.append("</display>\n");

											} else {
												sbResponse.append("    ");
												appendTag("status","OK",sbResponse);
												debugTrace.append("</trace>\n");
												sbResponse.append("    ");appendTag("debug",debugTrace.toString(),sbResponse);sbResponse.append("\n");
												
												sbResponse.append("\n");
												sbResponse.append("</display>\n");
										
											}
										}
									
										res.setContentType("text/plain");   // CHANGE BACK TO text/xml
								        out	= res.getWriter();
									
										
										//sbResponse.append(debugTrace.toString());
										sbResponse.append("</response>");
										out.println( sbResponse.toString() );
  
	
	 
 } // end doPost




/**	
	* appendTag  utility func. - makes <tagName> strValue </tagName>  XML tags	
	*	
	* @param tagName
	* @param strValue 	
	* @param stringBuf  - string buffer to append to
	* @return void - we use stringBuf instead to string-related speed
	*/	


public String charConv(String str){
	int ln = str.length();
	StringBuffer b = new StringBuffer();
	int i;
	for(i=ln-1;i>=0; i--){
		char c = str.charAt(i);
		b.append(c);
		
	}
	return b.toString();
	
	
	
}

public String attachAllCodes(String str){
	StringBuffer attstr = new StringBuffer("");
	int i;
	for(i=0; i<str.length(); i++){
		Character c= str.charAt(i);
		char ch = c.charValue();
		attstr.append(Integer.toHexString(ch)+" ");
		
	}
	return attstr.toString();
	
	
}

public String attachSpecCode(String str){
	String attstr = "";
	int i;
	for(i=0; i<str.length(); i++){
		Character c= str.charAt(i);
		char ch = c.charValue();
		if(Integer.toHexString(ch).equals("2264") || Integer.toHexString(ch).equals("2265") || Integer.toHexString(ch).equals("2013") || Integer.toHexString(ch).equals("2019") || Integer.toHexString(ch).equals("2014")){
			attstr += "~"+Integer.toHexString(ch)+","+i;
			//return attstr;
		}
	}
	return attstr;
	
	
}

public String getColFromCell(String cell){
		String[] alphaArray = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"};
		int i;
		StringBuffer colbuffer = new StringBuffer("");
		for(i=0;i<cell.length(); i++){
			if(!Character.isDigit(cell.charAt(i))){
				Character c = cell.charAt(i);
				colbuffer.append(c.toString());
			}
		}
		return colbuffer.toString();
	
	
}



public int getExcelColNum(String cell){
		String col = getColFromCell(cell);
		String[] alphaArray = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"};
		
	int i;
	debugTrace.append("cell to get col from is "+cell+"\n");
	debugTrace.append("col to translate is "+col+"\n");
	
	for(i=0; i<alphaArray.length; i++){
		//debugTrace.append(" testing against: "+alphaArray[i]);
		if(col.equals(alphaArray[i])){
			//debugTrace.append(" did pass test\n\n");
		//	debugTrace.append("found the colnum: "+i+"\n");
			return i;
		}
	}
	//debugTrace.append("\n");
	debugTrace.append("oops fell out\n");
	return -1;
	
	
}

public int getExcelRowNum(String cell){
		int i;
		StringBuffer colbuffer = new StringBuffer("");
		for(i=0;i<cell.length(); i++){
			if(Character.isDigit(cell.charAt(i))){
				Character c = cell.charAt(i);
				colbuffer.append(c.toString());
			}
		}
	//	debugTrace.append("found the rownum: "+colbuffer.toString()+"\n");
		return (Integer.parseInt(colbuffer.toString())-1);
	
	
}
public void appendTag(String tagName, String strValue, StringBuffer stringBuf) {

	stringBuf.append("<");
	stringBuf.append(tagName);
	stringBuf.append(">");								//  make XML safe wrapper function
	stringBuf.append(strValue);
	stringBuf.append("</");
	stringBuf.append(tagName);
	stringBuf.append(">");	

}

	
 public String escapeXML(String rawText, Perl5Util util) {

	String	returnString = "";
	
	returnString = util.substitute("s|&|&amp;|g",rawText);
		returnString = util.substitute("s|\\$|&#36;|g",returnString);
					// param goes here... rest is iterative
	returnString = util.substitute("s|<|&lt;|g",returnString);
		
	// returnString = util.substitute("s|<=|&lt;=|g",returnString);
	returnString = util.substitute("s|>|&gt;|g",returnString);
	// returnString = util.substitute("s|>|&gt;=|g",returnString);	
	
	returnString = util.substitute("s|\"|&quot;|g",returnString);
	//returnString = util.substitute("s|\"|&leq;|g",returnString);
	//returnString = util.substitute("s|—|&mdash;|g",returnString);
	//returnString = util.substitute("s|–|&mdash;|g",returnString);

	String finalretstr = "";
	for(int i=0; i<returnString.length(); i++){
		Character c= returnString.charAt(i);
		char ch = c.charValue();
		if(Integer.toHexString(ch).equals("2014")){
			finalretstr += "&#8212;";
			//finalretstr += "—";
		}
		else if(Integer.toHexString(ch).equals("2013")){
			finalretstr += "&#8211;";
			//finalretstr += "—";
		}
		else if(Integer.toHexString(ch).equals("2264")){
			finalretstr += "&#8211;";
		}
		else{
			finalretstr += c;
		}
		
	}
	return finalretstr.trim();

	// returnString = util.substitute("s|'|&apos;|g",rawText);	
	
	//return returnString.trim();
 }
 
 
	
	/**	
	* getStringParameter with extra default parameter	
	*	
	* @param req from doPost	
	* @param name parameter name to look for	
	* @param def  default value if nam not presnt	
	* @return a new String with value or default if name not present	
	*/	
	
	public String getStringParameter(HttpServletRequest req, String name)	
	 {	
	
		String[] values = req.getParameterValues(name);	
		if (values == null)	
			//throw new ParameterNotFoundException(name + " not found");
			return "not found";	
		else if (values[0].length() == 0)	
			//throw new ParameterNotFoundException(name + " was empty");
			return "was empty";	
		else	
			return values[0];	// ignore multiple field values	
	
	 } // end getStringParameter	
	
	
	
	/**	
	* getStringParameter with extra default parameter	
	*	
	* @param req from doPost	
	* @param name parameter name to look for	
	* @param def  default value if nam not presnt	
	* @return a new String with value or default if name not present	
	*/	
	
	public String getStringParameter(HttpServletRequest req, String name, String def) {	
		try { return getStringParameter(req,name); }	
		catch (Exception e) { return def; }	
	}	
	
} // end class