/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */
package org.zkoss.zss.model.impl.html;

import java.awt.Transparency;
import java.awt.image.BufferedImage;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.Closeable;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.Formatter;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;

import org.jfree.chart.ChartRenderingInfo;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.encoders.EncoderUtil;
import org.jfree.chart.encoders.ImageFormat;
import org.zkoss.poi.ss.usermodel.ZssContext;
import org.zkoss.util.Locales;
import org.zkoss.zk.ui.UiException;
import org.zkoss.zss.model.CellRegion;
import org.zkoss.zss.model.SBook;
import org.zkoss.zss.model.SCell;
import org.zkoss.zss.model.SCellStyle;
import org.zkoss.zss.model.SChart;
import org.zkoss.zss.model.SFont;
import org.zkoss.zss.model.SPicture;
import org.zkoss.zss.model.SRow;
import org.zkoss.zss.model.SSheet;
import org.zkoss.zss.model.ViewAnchor;
import org.zkoss.zss.model.SCellStyle.Alignment;
import org.zkoss.zss.model.SBorder.BorderType;
import org.zkoss.zss.model.SCellStyle.VerticalAlignment;
import org.zkoss.zss.model.sys.EngineFactory;
import org.zkoss.zss.model.sys.format.FormatContext;
import org.zkoss.zss.model.sys.format.FormatEngine;
import org.zkoss.zss.model.sys.format.FormatResult;
import org.zkoss.zssex.util.JFreeChartHelper;


/**
 * This example shows how to display a spreadsheet in HTML using the classes for
 * spreadsheet display.
 *
 * @author Ken Arnold, Industrious Media LLC
 */
public class ToHtml implements Serializable{
	private static final long serialVersionUID = 4118266926051968245L;
	
	private final SBook wb;
    private final OutputStream output;
    private boolean completeHTML;
    private Formatter out;
    private int firstColumn;
    private int endColumn;
    private HtmlHelper helper;
    
    // TODO ZSS 3.5 - We don't have style ID, so I need create a map for myself
    private Map<SCellStyle, Integer> styleTable = new HashMap<SCellStyle, Integer>();
    
    private boolean exportOneSheet = false;
    private String exportOneSheetName;
    
    private boolean exportSelectionArea = false;
    private CellRegion area;
    
    private boolean printHeader = true;
    
    private static final String DEFAULTS_CLASS = "excelDefaults";
    private static final String COL_HEAD_CLASS = "colHeader";
    private static final String ROW_HEAD_CLASS = "rowHeader";

    private static final Map<Alignment, String> ALIGN = mapFor(Alignment.LEFT, "left",
    		Alignment.CENTER, "center", Alignment.RIGHT, "right", Alignment.FILL, "left",
    		Alignment.JUSTIFY, "left", Alignment.CENTER_SELECTION, "center");

    private static final Map<VerticalAlignment, String> VERTICAL_ALIGN = mapFor(
            VerticalAlignment.BOTTOM, "bottom", VerticalAlignment.CENTER, "middle", VerticalAlignment.TOP,
            "top");

    private static final Map<BorderType, String> BORDER = mapFor(BorderType.DASH_DOT,
            "dashed 1pt", BorderType.DASH_DOT_DOT, "dashed 1pt", BorderType.DASHED,
            "dashed 1pt", BorderType.DOTTED, "dotted 1pt", BorderType.DOUBLE,
            "double 3pt", BorderType.HAIR, "solid 1px", BorderType.MEDIUM, "solid 2pt",
            BorderType.MEDIUM_DASH_DOT, "dashed 2pt", BorderType.MEDIUM_DASH_DOT_DOT,
            "dashed 2pt", BorderType.MEDIUM_DASHED, "dashed 2pt", BorderType.NONE,
            "none", BorderType.SLANTED_DASH_DOT, "dashed 2pt", BorderType.THICK,
            "solid 3pt", BorderType.THIN, "dashed 1pt");

    @SuppressWarnings({"unchecked"})
    private static <K, V> Map<K, V> mapFor(Object... mapping) {
        Map<K, V> map = new HashMap<K, V>();
        for (int i = 0; i < mapping.length; i += 2) {
            map.put((K) mapping[i], (V) mapping[i + 1]);
        }
        return map;
    }

    /**
     * Creates a new converter to HTML for the given workbook.
     *
     * @param wb     The workbook.
     * @param output Where the HTML output will be written.
     *
     * @return An object for converting the workbook to HTML.
     */
    public static ToHtml create(SBook wb, OutputStream output) {
    	ToHtml toHtml = new ToHtml(wb, output);
        return toHtml;
    }

    public static ToHtml create(SSheet ws, OutputStream output) {
        ToHtml toHtml = new ToHtml(ws.getBook(), output);
        toHtml.exportOneSheet = true;
        toHtml.exportOneSheetName = ws.getSheetName();       
        return toHtml;
    }
    
    private static ToHtml create(SSheet ws, CellRegion area,
			OutputStream output) {
        ToHtml toHtml = new ToHtml(ws.getBook(), output);
        
        toHtml.exportOneSheet = true;
        toHtml.exportOneSheetName = ws.getSheetName();
        
        toHtml.exportSelectionArea = true;
        toHtml.area = area;      
        return toHtml;
	}
    
    /**
     * Creates a new converter to HTML for the given workbook.  If the path ends
     * with "<tt>.xlsx</tt>" an {@link XSSFWorkbook} will be used; otherwise
     * this will use an {@link HSSFWorkbook}.
     *
     * @param path   The file that has the workbook.
     * @param output Where the HTML output will be written.
     *
     * @return An object for converting the workbook to HTML.
     */
    // TODO 3.5 The purpose of this method is to Import excel from inputstream and output to HTML
    // Should I keep this method after 3.5?
//    public static ToHtml create(String path, OutputStream output)
//            throws IOException {
//        return create(new FileInputStream(path), output);
//    }

    /**
     * Creates a new converter to HTML for the given workbook.  This attempts to
     * detect whether the input is XML (so it should create an {@link
     * XSSFWorkbook} or not (so it should create an {@link HSSFWorkbook}).
     *
     * @param in     The input stream that has the workbook.
     * @param output Where the HTML output will be written.
     *
     * @return An object for converting the workbook to HTML.
     */
    // TODO 3.5 The purpose of this method is to Import excel from inputstream and output to HTML
    // Should I keep this method after 3.5?
//    public static ToHtml create(InputStream in, OutputStream output)
//            throws IOException {
//        try {
//            Workbook wb = WorkbookFactory.create(in);
//            return create((XBook) wb, output);
//        } catch (InvalidFormatException e){
//            throw new IllegalArgumentException("Cannot create workbook from stream", e);
//        }
//    }

    private ToHtml(SBook wb, OutputStream output) {
        if (wb == null)
            throw new NullPointerException("wb");
        if (output == null)
            throw new NullPointerException("output");
        this.wb = wb;
        this.output = output;
//        setupColorMap();
    }
    private void setPrintHeader(boolean printHeader) {
    	this.printHeader = printHeader;
    }
    // TODO ZSS 3.5 - Remove this method
//	private void setupColorMap() {
//        if (wb instanceof HSSFWorkbook)
//            helper = new HSSFHtmlHelper((HSSFWorkbook) wb);
//        else if (wb instanceof XSSFWorkbook)
//            helper = new XSSFHtmlHelper((XSSFWorkbook) wb);
//        else
//            throw new IllegalArgumentException(
//                    "unknown workbook type: " + wb.getClass().getSimpleName());
//    }

    /**
     * Run this class as a program
     *
     * @param args The command line arguments.
     *
     * @throws Exception Exception we don't recover from.
     */
    public static void main(String[] args) throws Exception {
    	//test
    	/*
    	ByteArrayOutputStream baos = new ByteArrayOutputStream();
    	Formatter myOut = new Formatter(baos);    	
    	myOut.format("    <td class=%s %s>%s</td>%n", "中�?","中�?","中�?");
    	myOut.close();
    	System.out.println(">>>>"+baos.toString());
    	
    	String enc = System.getProperty("file.encoding");  
    	  
    	System.out.println(enc);
    	*/
    	
//        if(args.length < 2){
//            System.err.println("usage: ToHtml inputWorkbook outputHtmlFile");
//            return;
//        }
//
//        ToHtml toHtml = create(args[0], new FileOutputStream(new File(args[1])));
//        toHtml.setCompleteHTML(true);
//        toHtml.printPage();
//        System.out.println(">>>>>>> completed");
    }

    public static void export(SBook wb, OutputStream outputStream, boolean noHeader) throws IOException {   
        ToHtml toHtml = create(wb, outputStream);
        toHtml.setCompleteHTML(true);
        toHtml.setPrintHeader(!noHeader);
        toHtml.printPage();
    }

    public static void export(SSheet ws, OutputStream outputStream, boolean noHeader) throws IOException {   
        ToHtml toHtml = create(ws, outputStream);
        toHtml.setCompleteHTML(true);
        toHtml.setPrintHeader(!noHeader);
        toHtml.printPage();
    }

	public static void export(SSheet ws, CellRegion area,
			OutputStream outputStream, boolean noHeader) throws IOException {
        ToHtml toHtml = create(ws, area , outputStream);
        toHtml.setCompleteHTML(true);
        toHtml.setPrintHeader(!noHeader);
        toHtml.printPage();
	}
    public static void export(SBook wb, OutputStream outputStream) throws IOException {
    	export(wb, outputStream, false);
    }

    public static void export(SSheet ws, OutputStream outputStream) throws IOException {
    	export(ws, outputStream, false);
    }

	public static void export(SSheet ws, CellRegion area,
			OutputStream outputStream) throws IOException {
		export(ws, area, outputStream, false);
	}
	
	public void setCompleteHTML(boolean completeHTML) {
        this.completeHTML = completeHTML;
    }

    public void printPage() throws IOException {
        try {
            ensureOut();
            if (completeHTML) {
                out.format(
                        "<?xml version=\"1.0\" ?>%n");
                out.format("<html>%n");
                out.format("<head>%n");
                out.format("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
                out.format("</head>%n");
                out.format("<body>%n");
            }

            print();

            if (completeHTML) {
                out.format("</body>%n");
                out.format("</html>%n");
            }
        } finally {
            if (out != null)
                out.close();
            if (output instanceof Closeable) {
                Closeable closeable = (Closeable) output;
                closeable.close();
            }
        }
    }

    public void print() {
        printInlineStyle();
        printSheets();
    }

    private void printInlineStyle() {
        //out.format("<link href=\"excelStyle.css\" rel=\"stylesheet\" type=\"text/css\">%n");
        out.format("<style type=\"text/css\">%n");
        printStyles();
        out.format("</style>%n");
    }

    private void ensureOut() {
        if (out == null)
            out = new Formatter(output);
    }

    public void printStyles() {
        ensureOut();

        // First, copy the base css
        BufferedReader in = null;
        try {
            in = new BufferedReader(new InputStreamReader(
                    getClass().getResourceAsStream("excelStyle.css")));
            String line;
            while ((line = in.readLine()) != null) {
                out.format("%s%n", line);
            }
        } catch (IOException e) {
            throw new IllegalStateException("Reading standard css", e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    //noinspection ThrowFromFinallyBlock
                    throw new IllegalStateException("Reading standard css", e);
                }
            }
        }

        // now add css for each used style
        Set<Integer> seen = new HashSet<Integer>();
        if (exportOneSheet) {
			SSheet sheet = wb.getSheetByName(exportOneSheetName);
            Iterator<SRow> rows = sheet.getRowIterator();
            while (rows.hasNext()) {
                SRow row = rows.next();
                Iterator<SCell> cells = sheet.getCellIterator(row.getIndex());
                while (cells.hasNext()) {
                	SCell cell = cells.next();
                    SCellStyle style = cell.getCellStyle();
                    if(styleTable.get(style) == null)
                    	styleTable.put(style, styleTable.size());
                    final Integer index = Integer.valueOf(styleTable.get(style));
                    if (!seen.contains(index)) {
                        printStyle(style);
                        seen.add(index);
                    }
                }
            }
        } else {
	        for (int i = 0; i < wb.getNumOfSheet(); i++) {
	            SSheet sheet = wb.getSheet(i);
	            Iterator<SRow> rows = sheet.getRowIterator();
	            while (rows.hasNext()) {
	            	SRow row = rows.next();
	                Iterator<SCell> cells = sheet.getCellIterator(row.getIndex());
	                while (cells.hasNext()) {
	                	SCell cell = cells.next();
	                    SCellStyle style = cell.getCellStyle();
	                    if(styleTable.get(style) == null)
	                    	styleTable.put(style, styleTable.size());
	                    final Integer index = Integer.valueOf(styleTable.get(style));
	                    if (!seen.contains(index)) {
	                        printStyle(style);
	                        seen.add(index);
	                    }
	                    
	                }
	            }
	        }
        }
    }

    private void printStyle(SCellStyle style) {
        out.format(".%s .%s {%n", DEFAULTS_CLASS, styleName(style));
        styleContents(style);
        out.format("}%n");
    }

    private void styleContents(SCellStyle style) {
        styleOut("text-align", style.getAlignment(), ALIGN);
        styleOut("vertical-align", style.getVerticalAlignment(), VERTICAL_ALIGN);
        fontStyle(style);
        borderStyles(style);
        
        //helper.colorStyles(style, out);
        // background-color
        out.format(" fill-color: %s;%n", style.getFillColor().getHtmlColor());
        out.format(" background-color: %s;%n", style.getBackColor().getHtmlColor());
        out.format(" color: %s;%n", style.getFont().getColor().getHtmlColor());
        
        // font-color
    }

    private void borderStyles(SCellStyle style) {
        styleOut("border-left", style.getBorderLeft(), BORDER);
        styleOut("border-right", style.getBorderRight(), BORDER);
        styleOut("border-top", style.getBorderTop(), BORDER);
        styleOut("border-bottom", style.getBorderBottom(), BORDER);
    }

    private void fontStyle(SCellStyle style) {
        SFont font = style.getFont();

        if (font.getBoldweight() == SFont.Boldweight.BOLD)
            out.format("  font-weight: bold;%n");
        if (font.isItalic())
            out.format("  font-style: italic;%n");
        if (font.getUnderline() == SFont.Underline.SINGLE)
        	out.format("  text-decoration: underline;%n");
        if (font.isStrikeout())
        	out.format("  text-decoration: line-through;%n");
        if (font.getName() != null)
        	out.format(" font-family:%s;%n",font.getName());
        //ZSS-748
        if (font.getTypeOffset() == SFont.TypeOffset.SUB) {
        	out.format(" vertical-align: sub;%n");
        } else if (font.getTypeOffset() == SFont.TypeOffset.SUPER) {
        	out.format(" vertical-align: super;%n");
        }

//        System.out.println(">>>>> font color:"+font.getColor());
        
        
        int fontheight = font.getHeightPoints();
        if (font.getTypeOffset() != SFont.TypeOffset.NONE)
        	fontheight = (int) (0.7 * fontheight + 0.5);
        if (fontheight == 9) {
            //fix for stupid ol Windows
            fontheight = 10;
        }
        out.format("  font-size: %dpt;%n", fontheight);

        // Font color is handled with the other colors
    }

    private String styleName(SCellStyle style) {
    	// TODO ZSS 3.5 - Format cell with style
//        if (style == null)
//            style = wb.getCellStyleAt((short) 0);
        StringBuilder sb = new StringBuilder();
        Formatter fmt = new Formatter(sb, ZssContext.getCurrent().getLocale()); //ZSS-68
        fmt.format("style_%02x", styleTable.get(style));
        return fmt.toString();
    }

    private <K> void styleOut(String attr, K key, Map<K, String> mapping) {
        String value = mapping.get(key);
        if (value != null) {
            out.format("  %s: %s;%n", attr, value);
        }
    }

//    private static int ultimateCellType(NCell c) {
//    	CellType type = c.getType();
//    	if(type == NCell.CellType.FORMULA) {
//    		
//    	}
//    	return type;
//        if (type == Cell.CELL_TYPE_FORMULA)
//            type = c.getCachedFormulaResultType();
//        return type;
//    }

	private void printSheets() {
		ensureOut();
		if (exportOneSheet) {
			SSheet sheet = wb.getSheetByName(exportOneSheetName);
			
			printSheet(sheet);
		} else {
			int sheetsNumber = wb.getNumOfSheet();
			for (int i = 0; i < sheetsNumber; i++) {
				SSheet sheet = wb.getSheet(i);
				printSheet(sheet);
				out.format("<br/>");
			}
		}
	}

    public void printSheet(SSheet sheet) {
        ensureOut();
        out.format("<div>%s%n<table class=%s>%n", sheet.getSheetName() ,DEFAULTS_CLASS);
        printCols(sheet);
        printSheetContent(sheet);
        out.format("</table>%n</div>");
    }

    private void printCols(SSheet sheet) {
        out.format("<col/>%n");
        ensureColumnBounds(sheet);
        for (int i = firstColumn; i < endColumn; i++) {
            out.format("<col/>%n");
        }
    }

	private void ensureColumnBounds(SSheet sheet) {
		if (exportSelectionArea) {
			firstColumn = area.getColumn();
			//endColumn is 1-based, therefore, must plus one
			endColumn = area.getLastColumn()+1;
		} else {
			Iterator<SRow> iter = sheet.getRowIterator();
			firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
			endColumn = 0;
			while (iter.hasNext()) {
				SRow row = iter.next();
				//short firstCell = row.getFirstCellNum();
				//if (firstCell >= 0) {
					firstColumn = Math.min(firstColumn, sheet.getStartCellIndex(row.getIndex()));
					endColumn = Math.max(endColumn, sheet.getEndCellIndex(row.getIndex()));
				//}
			}
			endColumn += 1;
			firstColumn = 0;
		}
	}

	//TODO consider printHeader option
    private void printColumnHeads(SSheet sheet) {
        out.format("<thead>%n");
        out.format("  <tr class=%s>%n", COL_HEAD_CLASS);
        out.format("    <th class=%s>&#x25CA;</th>%n", COL_HEAD_CLASS);
        //noinspection UnusedDeclaration
        StringBuilder colName = new StringBuilder();
        
        for (int i = firstColumn; i < endColumn; i++) {
            colName.setLength(0);
            int cnum = i;
            boolean lastChar=true;
            do {
            	if(lastChar){
            		colName.insert(0, (char) ('A' + cnum % 26));
            		lastChar = false;
            	}else{
            		colName.insert(0, (char) ('A' + cnum % 26 - 1));
            	}                
                cnum /= 26;
            } while (cnum > 0);
            int width = sheet.getColumn(i).getWidth();
            out.format("    <th class=%s width=%s>%s</th>%n", COL_HEAD_CLASS, width, colName);
        }
        out.format("  </tr>%n");
        out.format("</thead>%n");
    }


	private void printSheetContent(SSheet sheet) {
		printColumnHeads(sheet);

		// TODO ZSS 3.5 - Picture and Charts
        List<SPicture> pictures = sheet.getPictures();
        List<SChart> charts = sheet.getCharts();
        
        out.format("<tbody>%n");
        Iterator<SRow> rows = sheet.getRowIterator();
        while (rows.hasNext()) {
            SRow row = rows.next();
            if(exportSelectionArea){
            	if(row.getIndex() < area.getRow() 
            			|| row.getIndex() > area.getLastRow()){
            		continue;
            	}
            }
            
            int rowHeight = LayoutUnitConversionHelper.convertHeightToPixel(row.getHeight());
            out.format("  <tr>%n");
            out.format("    <td class=%s height=%s>%d</td>%n", ROW_HEAD_CLASS, rowHeight,
                    row.getIndex() + 1);
//            final Locale locale = ZssContext.getCurrent().getLocale();
            for (int i = firstColumn; i < endColumn; i++) {
                String content = "&nbsp;";
                String attrs = "";
                SCellStyle style = null;
                if (i >= sheet.getStartCellIndex(row.getIndex()) && i <= sheet.getEndCellIndex(row.getIndex())) {
                    SCell cell = sheet.getCell(row.getIndex(), i);
                    if (!cell.isNull()) {
                        style = cell.getCellStyle();
                        // TODO ZSS 3.5 Alignment for format?
//                        attrs = tagStyle(cell, style);
                        //Set the value that is rendered for the cell
                        //also applies the format
            			FormatEngine fe = EngineFactory.getInstance().createFormatEngine();
            			FormatResult result = fe.format(cell, new FormatContext(ZssContext.getCurrent().getLocale()));
                        content = result.getText();
                        
                        // TODO ZSS 3.5 Picture and Chart
                        if (hasPictureStartInCell(pictures,cell)){
                        	content = genContentForPicture(pictures,cell);
                        }
                        if (hasChartStartInCell(charts, cell)){
                        	content = genContentForChart(sheet, charts, cell);
                        }
                        if (content.equals(""))
                            content = "&nbsp;";
                        if (cell.getHyperlink() != null){
                        	content = "<a href=\"hyperlinkurl\" style=\"color:blue;\">origin_content</a>"
                        		.replace("hyperlinkurl", cell.getHyperlink().getAddress())
                        		.replace("origin_content", content);
                        }
                    }
                }
                out.format("    <td class=%s %s>%s</td>%n", styleName(style),
                        attrs, content);
            }
            out.format("  </tr>%n");            
        }
        out.format("</tbody>%n");
    }

	// TODO ZSS 3.5 - Alignment of different type of cell
//	private String tagStyle(Cell cell, CellStyle style) {
//        if (style.getAlignment() == ALIGN_GENERAL) {
//            switch (ultimateCellType(cell)) {
//            case HSSFCell.CELL_TYPE_STRING:
//                return "style=\"text-align: left;\"";
//            case HSSFCell.CELL_TYPE_BOOLEAN:
//            case HSSFCell.CELL_TYPE_ERROR:
//                return "style=\"text-align: center;\"";
//            case HSSFCell.CELL_TYPE_NUMERIC:
//            default:
//                // "right" is the default
//                break;
//            }
//        }
//        return "";
//    }

    private String genContentForChart(SSheet sheet, List<SChart> charts, SCell cell) {
    	String result = "<span style=\"position: absolute; z-index: 1; " +
    			"margin-left: 27px; margin-top: 15px; " +
    			"width: contentWidth; height: contentHeight;\">" +
    			"<img alt=\"Embedded Image\" " +
    			"width=\"contentWidth\" height=\"contentHeight\" " +
    			"src=\"data:image/png;base64,realcontent\"></span>";
    	
    	//TODO: how do I know the margin left or top?
    	//TODO: the size is wrong.... and must have the size to show
    	SChart chart = getMappingChart(charts,cell);
    	ViewAnchor anchor = chart.getAnchor();
    	
    	int width = anchor.getWidth();
    	int height = anchor.getHeight();
    	
    	JFreeChart jfchart = JFreeChartHelper.drawJFreeChart(chart);
    	ChartRenderingInfo jfinfo = new ChartRenderingInfo();
		BufferedImage bi = jfchart.createBufferedImage(chart.getAnchor().getWidth(), chart.getAnchor().getHeight(), Transparency.TRANSLUCENT, jfinfo);
		
		byte[] rawData = null;
		try {
			//encode into png image format byte array
			rawData = EncoderUtil.encode(bi, ImageFormat.PNG, true);
		} catch(java.io.IOException ex) {
			throw UiException.Aide.wrap(ex);
		}
		
    	String encodedPic = String.valueOf(Base64Coder.encode(rawData));
    	result = result.replaceAll("contentWidth", String.valueOf(width));
    	result = result.replaceAll("contentHeight", String.valueOf(height));
    	return result.replace("realcontent", encodedPic);
	}

    private String genContentForPicture(List<SPicture> pictures, SCell cell) {
    	String result = "<span style=\"position: absolute; z-index: 1; " +
    			"margin-left: 27px; margin-top: 15px; " +
    			"width: contentWidthpx; height: contentHeightpx;\">" +
    			"<img alt=\"Embedded Image\" width=\"contentWidth\" height=\"contentHeight\" " +
    			"src=\"data:image/png;base64,realcontent\"></span>";
    	
    	//TODO: how do I know the margin left or top?
    	SPicture picture = getMappingPicture(pictures,cell);
    	ViewAnchor anchor = picture.getAnchor();
    	int width = anchor.getWidth();
    	int height = anchor.getHeight();
    	byte[] data = picture.getData();
    	String encodedPic = String.valueOf(Base64Coder.encode(data));
    	result = result.replaceAll("contentWidth", String.valueOf(width));
    	result = result.replaceAll("contentHeight", String.valueOf(height));
    	return result.replace("realcontent", encodedPic);
	}

    private boolean hasChartStartInCell(List<SChart> charts, SCell cell){
		for(SChart chart: charts){
			ViewAnchor anchor = chart.getAnchor();
			if(anchor.getColumnIndex() == cell.getColumnIndex() && anchor.getRowIndex() == cell.getRowIndex()){
				return true;
			}
		}
		return false;
    }
    
    private boolean hasPictureStartInCell(List<SPicture> pictures, SCell cell){
		for(SPicture picture: pictures){
			ViewAnchor anchor = picture.getAnchor();
			if(anchor.getColumnIndex() == cell.getColumnIndex() && anchor.getRowIndex() == cell.getRowIndex()){
				return true;
			}
		}
		return false;
    }
    
    private SChart getMappingChart(List<SChart> charts, SCell cell) {
		for(SChart chart: charts){
			ViewAnchor anchor = chart.getAnchor();
			if(anchor.getColumnIndex() == cell.getColumnIndex() && anchor.getRowIndex() == cell.getRowIndex()){
				return chart;
			}
		}
		return null;
	}	
    
	private SPicture getMappingPicture(List<SPicture> pictures, SCell cell) {
		for(SPicture picture: pictures){
			ViewAnchor anchor = picture.getAnchor();
			if(anchor.getColumnIndex() == cell.getColumnIndex() && anchor.getRowIndex() == cell.getRowIndex()){
				return picture;
			}
		}
		return null;
	}
}