package net.sourceforge.fenixedu.util.report;

import java.util.Date;
import java.util.ResourceBundle;

import net.sourceforge.fenixedu.util.projectsManagement.ExcelStyle;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.contrib.HSSFRegionUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

import pt.iscte.ci.metadata.ISCTE;

/**
 * 
 * @author rjmvo
 * 
 */
public class ExcelStyledSheet {
    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private ExcelStyle style;

    public ExcelStyledSheet(final HSSFWorkbook workbook, final String name) {
	this.workbook = workbook;
	sheet = workbook.createSheet(name);
	style = new ExcelStyle(workbook);

	sheet.setMargin(HSSFSheet.LeftMargin, .15d);
	sheet.setMargin(HSSFSheet.RightMargin, .15d);
	sheet.setMargin(HSSFSheet.BottomMargin, .40d);
    }

    public ExcelStyle getExcelStyle() {
	return style;
    }

    private HSSFRow getCurrentRow() {
	return sheet.getRow(sheet.getLastRowNum());
    }

    public HSSFRow getRow(short rowNumber) {
	return sheet.getRow(rowNumber);
    }

    public void createRow() {
	int rowNumber = sheet.getLastRowNum();
	if (rowNumber != 0 || sheet.getRow(rowNumber) != null) {
	    rowNumber += 1;
	}
	sheet.createRow(rowNumber);
    }

    // ISCTE
    public void createRows(final int number) {
	for (int i = 0; i < number; i++) {
	    this.createRow();
	}
    }

    public HSSFCell getCell(final short cellNumber) {
	return getCurrentRow().getCell(cellNumber);
    }

    private HSSFCell createCell(final short column) {
	return getCurrentRow().createCell(column);
    }

    // ISCTE
    public HSSFCell createCell(final Object value) {
	return createCell((short) 0, value);
    }

    public HSSFCell createCell(final short column, final Object value) {
	final HSSFCell cell = createCell(column);

	// ISCTE start
	if (value != null) {
	    if (Number.class.isAssignableFrom(value.getClass())) {
		cell.setCellValue(((Number) value).doubleValue());
		return cell;
	    }
	    if (value instanceof Date) {
		cell.setCellValue((Date) value);
		return cell;
	    }
	}
	// ISCTE end

	cell.setCellValue(String.valueOf(value));
	return cell;
    }

    public HSSFCell createCell(final short column, final Object value, final HSSFCellStyle style) {
	final HSSFCell cell = createCell(column, value);
	cell.setCellStyle(style);
	return cell;
    }

    public HSSFCell createCellFormula(final short column, final String formula) {
	final HSSFCell cell = createCell(column);
	cell.setCellFormula(formula);
	return cell;
    }

    public HSSFCell createCellFormula(final short column, final String formula, final HSSFCellStyle style) {
	final HSSFCell cell = this.createCellFormula(column, formula);
	cell.setCellStyle(style);
	return cell;
    }

    public HSSFCell createEmptyCell(final short column) {
	final HSSFCell cell = createCell(column);
	cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
	return cell;
    }

    public HSSFCell createEmptyCell(final short column, final HSSFCellStyle style) {
	final HSSFCell cell = createEmptyCell(column);
	cell.setCellStyle(style);
	return cell;
    }

    public HSSFCell createHeader(final short column, final String value) {
	final HSSFCell cell = createCell(column, value);
	cell.setCellStyle(style.getHeaderStyle());
	return cell;
    }

    public HSSFCell createEmptyHeader(final short column) {
	final HSSFCell cell = createEmptyCell(column);
	cell.setCellStyle(style.getHeaderStyle());
	return cell;
    }

    public HSSFCellStyle getCellValueBorder() {
	final HSSFCellStyle style = workbook.createCellStyle();
	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	style.setLeftBorderColor(HSSFColor.GREY_25_PERCENT.index);
	style.setRightBorderColor(HSSFColor.GREY_25_PERCENT.index);
	style.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index);
	return style;
    }

    public void setColumnWidth(final short column, final short width) {
	sheet.setColumnWidth(column, width);
    }

    @ISCTE(author = "saidh")
    public void setColumnAutoWidth(final short column) {
	sheet.autoSizeColumn(column);
    }

    @ISCTE(author = "saidh")
    public void setCurrentRowHeight(final short height) {
	getCurrentRow().setHeight(height);
    }

    public void addBorderRegion(int firstRow, short firstColumn, int lastRow, short lastColumn) {
	final Region region = new Region(firstRow, firstColumn, lastRow, lastColumn);
	HSSFRegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
	HSSFRegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
	HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
	HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
	HSSFRegionUtil.setTopBorderColor(HSSFColor.BLACK.index, region, sheet, workbook);
	HSSFRegionUtil.setLeftBorderColor(HSSFColor.BLACK.index, region, sheet, workbook);
	HSSFRegionUtil.setRightBorderColor(HSSFColor.BLACK.index, region, sheet, workbook);
	HSSFRegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region, sheet, workbook);
    }

    public void addMergedRegion(int firstRow, short firstColumn, int lastRow, short lastColumn, boolean border) {
	final Region region = new Region(firstRow, firstColumn, lastRow, lastColumn);
	sheet.addMergedRegion(region);

	if (border) {
	    addBorderRegion(firstRow, firstColumn, lastRow, lastColumn);
	}
    }

    // ISCTE
    public String getColumnPosition(int column) {
	String position = "";
	int iDiv = column;
	int iMod;
	while (iDiv > 26) {
	    iMod = iDiv % 26;
	    if (iMod == 0) {
		iMod = 26;
		iDiv -= 1;
	    }
	    position = (char) ('A' + iMod - 1) + position;
	    iDiv = iDiv / 26;
	}
	return (char) ('A' + iDiv - 1) + position;
    }

    // ISCTE
    public String getCellPosition(HSSFCell cell) {
	return getColumnPosition(cell.getColumnIndex() + 1) + (1 + cell.getRowIndex());
    }

    public static ExcelStyledSheet generateWorksheet(final HSSFWorkbook workbook, final String sheetName,
	    final ResourceBundle resourceBundle, final String... headerColumnsBundleKeys) {
	final ExcelStyledSheet sheet = new ExcelStyledSheet(workbook, sheetName);
	sheet.createRow();
	for (int i = 0; i < headerColumnsBundleKeys.length; i++) {
	    sheet.createHeader((short) i, resourceBundle.getString(headerColumnsBundleKeys[i]));
	}
	return sheet;
    }

    public HSSFPatriarch createDrawingPatriarch() {
	return sheet.createDrawingPatriarch();
    }

}