package fr.home.mcm.plugin;


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;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.RGB;
import jxl.format.Font;
import jxl.format.BoldStyle;


/**
 *  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_SRCSHEET        = "srcsheet";
	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_KEEPFORMAT      = "keepformat";
	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 srcsheet		= null;
	String height 			= null;
	String width 			= null;
	String border 			= null;
	String cellpadding 		= null;
	String cellspacing 		= null;
	String background   		= null;
	String backgroundcolor 		= null;
	String keepformat 		= "no";
	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) ;
			srcsheet = getCleanParameter(params, PARAM_SRCSHEET, srcsheet) ;
			
			// 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;
			}

			keepformat 	      = getCleanParameter(params, PARAM_KEEPFORMAT, keepformat);
			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);
		}
		
		int nbsheets = workbook.getNumberOfSheets();
		int initsheet = 0;
		
		if( null != srcsheet ) // Specific sheet number to display was asked
		{
			initsheet = Integer.parseInt (srcsheet);
			if( initsheet < 0 || initsheet >= nbsheets ) initsheet = 0; // if sheet asked doesn't exist we go back to normal behaviour start from 0
			else nbsheets = initsheet +1; // otherwise if sheet no is valid, process only this one
		}
		
		for (int sheet = initsheet; sheet < nbsheets; 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( keepformat.indexOf("no") != -1 )
					{
						if(i == 0)
						{
							writeAttribute(bw, "class", headerclass);
							writeAttribute(bw, "style", "background-color:" + headerbackgroundcolor + ";");
						}
						else
						{
							writeAttribute(bw, "class", (isEvenRow ? evenrowclass : oddrowclass));
							writeAttribute(bw, "style", "background-color:" + (isEvenRow ? evenrowbackgroundcolor : oddrowbackgroundcolor) + ";");
						}
					}
					bw.write(">");
					
					boolean isitalic = false;
					boolean isbold = false;
					boolean hasborder = false;
					String celltextcolor = null;

					for (int j = 0; j < row.length; j++)
					{							
						bw.write("<td");
						if( keepformat.indexOf("no") != -1 )
						{
							if(i == 0)
							{
								writeAttribute(bw, "class", headerclass);
								writeAttribute(bw, "style", "background-color:" + headerbackgroundcolor + ";");
							}
							else
							{
								writeAttribute(bw, "class", (isEvenRow ? evenrowclass : oddrowclass));
								writeAttribute(bw, "style", "background-color:" + (isEvenRow ? evenrowbackgroundcolor : oddrowbackgroundcolor) + ";");
							}
						}
						else if( keepformat.indexOf("yes") != -1 )
						{
							CellFormat cformat = (row[j]).getCellFormat();
							if( cformat != null )
							{
								hasborder = cformat.hasBorders();
								Colour cellcol = cformat.getBackgroundColour();
								if( cellcol != null )
								{
									RGB rgbcol = cellcol.getDefaultRGB();
									if( rgbcol != null) writeAttribute(bw, "style", "background-color:rgb("+ rgbcol.getRed() + "," + rgbcol.getGreen() + "," + rgbcol.getBlue() + ");" );
								}
								Font cellfont = cformat.getFont();
								if( cellfont != null )
								{
									isitalic = cellfont.isItalic();
									isbold   = (cellfont.getBoldWeight() == BoldStyle.BOLD.getValue() );
									cellcol  = cellfont.getColour();
									if( cellcol != null )
									{
										celltextcolor = cellcol.getDescription();
									} 
								}
							}
						}
						bw.write(">");
						if( isitalic ) bw.write("<i>");
						if( isbold ) bw.write("<b>");
						bw.write("<font");
						if( keepformat.indexOf("no") != -1 )
						{
							if(i == 0)
							{
								writeAttribute(bw, "color", headercolor);
							}
							else
							{
								writeAttribute(bw, "color", (isEvenRow ? evenrowcolor : oddrowcolor));
							}
						}
						else if( keepformat.indexOf("yes") != -1 )
						{
							writeAttribute(bw, "color", celltextcolor);	
						}
						
						bw.write(">");
						
						bw.write(row[j].getContents());
						if( (row[j].getContents() == "") && hasborder ) bw.write("&nbsp;");

						bw.write("</font>");
						if( isbold ) bw.write("</b>");
						if( isitalic ) bw.write("</i>");
						
						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);
	}
}