package cn.edu.seu.ise.dca.util;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Locale;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelWriter {

	private WritableWorkbook workbook;
	private WritableSheet currentSheet;
	private WritableCellFormat captionFont;
	private WritableCellFormat commonFont;

	public ExcelWriter(String filePath) {
		File file = new File(filePath);
		WorkbookSettings wbSettings = new WorkbookSettings();
		wbSettings.setLocale(new Locale("en", "EN"));
		try {
			workbook = Workbook.createWorkbook(file, wbSettings);
			workbook.createSheet("default", 0);
			setCurrentSheet(0);
			//Set up the font in excel 
			setUpFont();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} 
	}
	
	/**
	 * Create a data sheet
	 * 
	 * @param sheetName
	 * @param position
	 */
	public void createSheet(String sheetName, int position) {
		workbook.createSheet(sheetName, position);
	}

	/**
	 * Set the current sheet to edit
	 * 
	 * @param position
	 */
	public void setCurrentSheet(int position) {
		currentSheet = workbook.getSheet(position);
	}
	
	/**
	 * Set up the caption font
	 * @param captionFont
	 */
	public void setCaptionFont(WritableCellFormat captionFont) {
		this.captionFont = captionFont;
	}

	/**
	 * Set up the common font
	 * @param commonFont
	 */
	public void setCommonFont(WritableCellFormat commonFont) {
		this.commonFont = commonFont;
	}

	/**
	 * Add a caption into the sheet
	 * @param column
	 * @param row
	 * @param content
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public void addCaption(int column, int row, String content) {
		Label label = new Label(column, row, content, captionFont);
		try {
			currentSheet.addCell(label);
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Add a number into a sheet
	 * @param column
	 * @param row
	 * @param content
	 */
	public void addNumber(int column, int row, long content) {
		Number number = new Number(column, row, content, commonFont);
		try {
			currentSheet.addCell(number);
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Add a string into sheet
	 * @param column
	 * @param row
	 * @param content
	 */
	public void addLabel(int column, int row, String content) {
		Label label = new Label(column, row, content, commonFont);
		try {
			currentSheet.addCell(label);
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Add a list of string into a row
	 * @param row
	 * @param contents
	 */
	public void addLabels(int row, List<String> contents) {
		for(int i = 0; i < contents.size(); i++) {
			Label label = new Label(i, row, contents.get(i), commonFont);
			try {
				currentSheet.addCell(label);
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}	
	}
	
	/**
	 * Add a formula into the sheet
	 * @param column
	 * @param row
	 * @param formula
	 */
	public void addFormula(int column, int row, String formula) {
		Formula f = new Formula(column, row, formula, commonFont);
		try {
			currentSheet.addCell(f);
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Close the writer
	 */
	public void close() {
		try {
			workbook.write();
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Set up the default font used in excel
	 * @throws WriteException
	 */
	private void setUpFont() throws WriteException {
		// Create a ARIAL font
		WritableFont arial10pt = new WritableFont(WritableFont.ARIAL, 10);
		commonFont = new WritableCellFormat(arial10pt);
		commonFont.setAlignment(Alignment.CENTRE);
		commonFont.setWrap(true);
		// Create a bold ARIAL font
		WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
		captionFont = new WritableCellFormat(arial10ptBold);
		captionFont.setAlignment(Alignment.CENTRE);
		captionFont.setWrap(true);
	}
}