package com.xcurrent.wiki;


import java.io.*;
import java.util.Map;

import com.ecyrd.jspwiki.*;
import com.ecyrd.jspwiki.plugin.*;
import com.ecyrd.jspwiki.attachment.Attachment;
import com.ecyrd.jspwiki.attachment.AttachmentManager;
import com.ecyrd.jspwiki.providers.ProviderException;

/*
* imports for the jexcelapi library, jxl.jar from http://www.andykhan.com/jexcelapi/
* which supplies methods for reading MS Excel files
*/
import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;

/**
 *  A JSPWiki Plugin that reads a Microsoft Excel file and presents it as an HTML Table
 *
 *  Note: The 'stylesheet' parameter will override the individual style parameters
 *
 *  @author <a href="mailto:rfreedman@xcurrent.com">Rich Freedman</a> 
 */
public class ExcelToHTMLPlugin implements WikiPlugin
{
	private static final String encoding = "UTF8";
	
	private static final String PARAM_SRC             = "src";
	private static final String PARAM_HEIGHT          = "height";
	private static final String PARAM_WIDTH           = "width";
	private static final String PARAM_BORDER   	  = "border";
	private static final String PARAM_CELLPADDING     = "cellpadding";
	private static final String PARAM_CELLSPACING     = "cellspacing";
	
	private static final String PARAM_BACKGROUND	  = "background";
	private static final String PARAM_BACKGROUNDCOLOR = "backgroundcolor";
	
	private static final String PARAM_HEADERCOLOR     = "headercolor";
	private static final String PARAM_HEADERBGCOLOR   = "headerbackgroundcolor";
	
	private static final String PARAM_EVENROWCOLOR    = "evenrowcolor";
	private static final String PARAM_EVENROWBGCOLOR  = "evenrowbackgroundcolor";
	
	private static final String PARAM_ODDROWCOLOR     = "oddrowcolor";
	private static final String PARAM_ODDROWBGCOLOR   = "oddrowbackgroundcolor";
	
	private static final String PARAM_TABLECLASS      = "tableclass";
	private static final String PARAM_HEADERCLASS     = "headerclass";
	private static final String PARAM_EVENROWCLASS    = "evenrowclass";
	private static final String PARAM_ODDROWCLASS     = "oddrowclass";
	
	private static final String PARAM_STYLESHEET      = "stylesheet";


	/* set default values */
	String src			= null;
	String height 			= null;
	String width 			= null;
	String border 			= null;
	String cellpadding 		= null;
	String cellspacing 		= null;
	String background   		= null;
	String backgroundcolor 		= null;
	String headercolor 		= "#000000";
	String headerbackgroundcolor 	= null;
	String evenrowcolor 		= "#000000";
	String evenrowbackgroundcolor 	= null;
	String oddrowcolor 		= "#000000";
	String oddrowbackgroundcolor 	= null;
	String tableclass 		= "exceltable";
	String headerclass 		= "exceltableheader";
	String evenrowclass 		= "exceltableevenrow";
	String oddrowclass 		= "exceltableoddrow";
	String stylesheet 		= null;
	
	public String execute(WikiContext wcontext, Map parms) throws PluginException
	{
		initializeParameters(wcontext, parms);
		
		try
		{
			java.io.InputStream excelStream = getInputStream(wcontext, src);		
			Workbook wb = Workbook.getWorkbook(excelStream);
			OutputStream out = new ByteArrayOutputStream();
			ExcelToHtml(wb, out);
			return out.toString();
		}
		catch(Exception ex)
		{
			throw new PluginException("Failed to convert Excel to HTML: " + ex.getMessage(), ex);
		}
	}
	
	
	private final void initializeParameters(WikiContext wcontext, Map params)  throws PluginException
	{
		try
		{
			src = getCleanParameter(params, PARAM_SRC, src) ;
			
			// check required 'src' parameter
			if(null == src)
			{
				throw new PluginException( "Parameter 'src' is required" );
			}
			
			tableclass 		= getCleanParameter(params, PARAM_TABLECLASS, tableclass);
			headerclass 		= getCleanParameter(params, PARAM_HEADERCLASS, headerclass);
			evenrowclass 		= getCleanParameter(params, PARAM_EVENROWCLASS, evenrowclass);
			oddrowclass 		= getCleanParameter(params, PARAM_ODDROWCLASS, oddrowclass);
			stylesheet 		= getCleanParameter(params, PARAM_STYLESHEET, stylesheet);
			

			if(stylesheet != null && stylesheet.length() > 0)
			{

				try
				{
					String attachmentURL = resolveWikiAttachmentURL(wcontext, stylesheet);
					if(attachmentURL != null)
					{
						stylesheet = attachmentURL;
					}
				}
				catch( ProviderException e )
				{
				    throw new PluginException( "Attachment info failed: " + e.getMessage() );
				}
			}
			else
			{
				stylesheet = null;
			}

			height 			= getCleanParameter(params, PARAM_HEIGHT, height);
			width 			= getCleanParameter(params, PARAM_WIDTH, width);
			border 			= getCleanParameter(params, PARAM_BORDER, border);
			cellpadding 		= getCleanParameter(params, PARAM_CELLPADDING, cellpadding);
			cellspacing 		= getCleanParameter(params, PARAM_CELLSPACING, cellspacing);
			
			background = getCleanParameter(params, PARAM_BACKGROUND, background);
			if(background != null && background.length() > 0)
			{

				try
				{
					String attachmentURL = resolveWikiAttachmentURL(wcontext, background);
					if(attachmentURL != null)
					{
						background = attachmentURL;
					}
				}
				catch( ProviderException e )
				{
				    throw new PluginException( "Attachment info failed: " + e.getMessage() );
				}
			}
			else
			{
				background = null;
			}


			backgroundcolor 	= getCleanParameter(params, PARAM_BACKGROUNDCOLOR, backgroundcolor);
			headercolor 		= getCleanParameter(params, PARAM_HEADERCOLOR, headercolor);
			headerbackgroundcolor 	= getCleanParameter(params, PARAM_HEADERBGCOLOR, headerbackgroundcolor);
			evenrowcolor 		= getCleanParameter(params, PARAM_EVENROWCOLOR, evenrowcolor);
			evenrowbackgroundcolor 	= getCleanParameter(params, PARAM_EVENROWBGCOLOR, evenrowbackgroundcolor);
			oddrowcolor 		= getCleanParameter(params, PARAM_ODDROWCOLOR, oddrowcolor);
			oddrowbackgroundcolor 	= getCleanParameter(params, PARAM_ODDROWBGCOLOR, oddrowbackgroundcolor);

		}
		catch(Exception ex)
		{
			throw new PluginException("Failed to initialize: " + ex.getMessage(), ex);
		}
	}
	

	private final String resolveWikiAttachmentURL(WikiContext wcontext, String name) throws ProviderException
	{
		String retval = null;
		
		WikiEngine engine = wcontext.getEngine();
		
		AttachmentManager mgr = engine.getAttachmentManager();
		Attachment        att = mgr.getAttachmentInfo( wcontext, name );
		
		if( att != null )
		{
			String baseURL = engine.getBaseURL();
			if(baseURL == null || baseURL.trim().equals(""))
			{
				throw new ProviderException(
					"The property jspwiki.baseURL must be set "+
					"for an attachment reference to work");
			}
			retval = baseURL+engine.getAttachmentURL(att.getName());
		}
		return retval;
	}
	
	
	/**
	*  This method is used to clean away things like quotation marks which
	*  a malicious user could use to stop processing and insert javascript.
	*/
	private static final String getCleanParameter( Map params, String paramId, String defaultvalue )
	{
		String retval = defaultvalue;
		
		String parm = (String) params.get( paramId );
		
		if(parm != null && parm.trim().length() > 0)
		{
			retval = TextUtil.replaceEntities( parm.trim() );
		}
		return retval;
	}

	private final java.io.InputStream getInputStream(WikiContext wcontext, String src) throws Exception
	{
		// If it is an attachment do not open url stream
		WikiEngine engine = wcontext.getEngine();
		AttachmentManager mgr = engine.getAttachmentManager();
		Attachment att = mgr.getAttachmentInfo(wcontext, src);

		if (att != null)
		{
			return mgr.getAttachmentStream(att);
		}

		java.net.URL url = null;
		java.io.InputStream retval = null;
		
		// first, assume that src is a URL 
		try
		{
			url = new java.net.URL(src);
			retval = url.openStream();
		}
		catch(java.net.MalformedURLException urlex)
		{
			// eat the exception
			url = null;
		}
		
		// if it wasn't a valid URL, try it as a file reference
		// this would work, for instance for "C:\foo.xls" or "\\myserver\share\foo.xls"
		if(url == null)
		{
			retval = new java.io.FileInputStream(src);
		}
		
		return retval;
	}
	
	private void ExcelToHtml(Workbook workbook, OutputStream out) throws Exception
	{
		OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
		BufferedWriter bw = new BufferedWriter(osw);

		if(stylesheet != null)
		{
			embedStylesheetRef(bw, stylesheet);
		}
		
		for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++)
		{
			Sheet s = workbook.getSheet(sheet);
			
			bw.write("<br><br>");
			
			bw.write("<table");
			writeAttribute(bw, "class", tableclass);
			writeAttribute(bw, "border", border);
			writeAttribute(bw, "height", height);
			writeAttribute(bw, "width",  width);
			writeAttribute(bw, "cellpadding", cellpadding);
			writeAttribute(bw, "cellspacing", cellspacing);
			writeAttribute(bw, "background", background);
			writeAttribute(bw, "bgcolor", backgroundcolor);			
			bw.write(">");
			
			Cell[] row = null;
			boolean isEvenRow = true;
			int numrows = s.getRows();
			

			//if(headerbackgroundcolor != null || headercolor != null)			
			
			for (int i = 0; i < numrows; i++)
			{
				row = s.getRow(i);
				
				if (row.length > 0)
				{
					bw.write("<tr");
					if(i == 0)
					{
						writeAttribute(bw, "class", headerclass);
						writeAttribute(bw, "bgcolor", headerbackgroundcolor);
					}
					else
					{
						writeAttribute(bw, "class", (isEvenRow ? evenrowclass : oddrowclass));
						writeAttribute(bw, "bgcolor", (isEvenRow ? evenrowbackgroundcolor : oddrowbackgroundcolor));
					}
					bw.write(">");
					
					for (int j = 0; j < row.length; j++)
					{
						bw.write("<td");
						
						if(i == 0)
						{
							writeAttribute(bw, "class", headerclass);
							writeAttribute(bw, "bgcolor", headerbackgroundcolor);
						}
						else
						{
							writeAttribute(bw, "class", (isEvenRow ? evenrowclass : oddrowclass));
							writeAttribute(bw, "bgcolor", (isEvenRow ? evenrowbackgroundcolor : oddrowbackgroundcolor));
						}
						
						bw.write(">");

						bw.write("<font");
						if(i == 0)
						{
							writeAttribute(bw, "color", headercolor);
						}
						else
						{
							writeAttribute(bw, "color", (isEvenRow ? evenrowcolor : oddrowcolor));
						}
						bw.write(">");
						
						bw.write(row[j].getContents());

						bw.write("</font>");

						bw.write("</td>");
					}
					
					bw.write("</tr>");
					isEvenRow = !isEvenRow;
				}
				bw.newLine();
			} // for
		
		    	bw.write("</table><br><br>");
		
		} // for
		
		bw.flush();
		bw.close();
	}

	private void writeAttribute(java.io.Writer writer, String attname, String attvalue) throws Exception
	{
		if(attvalue != null) 
		{
			writer.write(" " +  attname + "='" + attvalue + "'");
		}
	}

	private void embedStylesheetRef(java.io.Writer writer, String cssref) throws Exception
	{
		String newline = System.getProperty("line.separator"); 

		writer.write("<STYLE TYPE='text/css'>" + newline);
		writer.write("<!--" + newline);
		writer.write("@import url" + cssref + ");" + newline);
		writer.write("-->" + newline);
		writer.write("</STYLE>" + newline);
	}
}