package com.rc.qa.base.utils;

import java.io.FileOutputStream;

import java.util.Calendar;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CreationHelper;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.Hyperlink;

/**

 * @author jennifer.huang

 * @date 2014/10/23

 *

 */

public class ExcelOperation {

    private Workbook wb;

    private CreationHelper createHelper;

    private Sheet sheet;

    private CellStyle titleCellStyle,dataCellStyle,linkCellStyle;

    private int rowHeight;

    public ExcelOperation() {

        init();

    }

    public ExcelOperation(String []columns){

        init();

        this.createTitleRow(columns);

    }

    public ExcelOperation(String []columns,CellStyle titleCellStyle){

        init();

        this.createTitleRow(columns, titleCellStyle);

    }

    private void init(){

        wb = new XSSFWorkbook();

        createHelper = wb.getCreationHelper();

        sheet = wb.createSheet();

        this.createTitleStyle();

        this.createDataStyle();

        this.createLinkStyle();

        rowHeight = 400;

    }

    /**

     * createTitleRow

     * row: excel first rowId=0. createRow((short)0)

     * @param columns

     */

    public void createTitleRow(String []columns){

        createTitleRow(columns, titleCellStyle);

    }

    public void createTitleRow(String []columns,CellStyle titleCellStyle){

        sheet.createFreezePane( 0, 1, 0, 1 );

        setColumnWidth(columns);

        Row row = sheet.createRow((short)0);

        setRowHeight(row,rowHeight);

        for(int i=0;i<columns.length;i++){

            createCell(row, i, columns[i], titleCellStyle);

        }

    }

    /**

     * createDataRow

     * @param rowId (can start from 1)

     * @param columns

     */

    public void createDataRow(int rowId,String []columns){

        createDataRow(rowId,columns,dataCellStyle);

    }

    public void createDataRow(int rowId, String []columns,CellStyle dataCellStyle){

        setColumnWidth(columns);

        Row tmpRow = sheet.createRow((short)rowId);

        setRowHeight(tmpRow,rowHeight);

        for(int i=0;i<columns.length;i++){

            createCell(tmpRow, i, columns[i], dataCellStyle);

        }

    }

    /**

     * setHylinkForCell

     * @param rowId

     * @param columnId

     * @param link

     */

    public void setHylinkForCell(int rowId, int columnId,Hyperlink link){

        setHylinkForCell(rowId, columnId, link, linkCellStyle);

    }

    public void setHylinkForCell(int rowId, int columnId,Hyperlink link,CellStyle linkCellStyle){

        Row row = sheet.getRow(rowId);

        Cell cell = row.getCell(columnId);

        cell.setHyperlink(link);

        cell.setCellStyle(linkCellStyle);

    }

    /**

     * saveToExcel

     * @param savePath

     */

    public void saveToExcel(String savePath){

          Calendar c = Calendar.getInstance();

          FileOutputStream fileOut;

          try {

              fileOut = new FileOutputStream(String.format(savePath+"\\Tasks_%d-%d-%d-%d-%d.xlsx", c.get(Calendar.YEAR), c.get(Calendar.MONTH)+1, c.get(Calendar.DATE), c.get(Calendar.HOUR_OF_DAY), c.get(Calendar.MINUTE)));

              wb.write(fileOut);

              fileOut.close();

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    private void setColumnWidth(String []columns){

        for(int i=0;i<columns.length;i++){

            sheet.autoSizeColumn((short)i);

        }

    }

    private void setRowHeight(Row row, int height){

        row.setHeight((short)height);

    }

    private CellStyle createTitleStyle(){

        titleCellStyle = wb.createCellStyle();

        Font titleFont = wb.createFont();

        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        titleFont.setFontHeightInPoints((short)12);

        titleCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());

        titleCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        titleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

        titleCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

        titleCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        titleCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setBorderTop(CellStyle.BORDER_THIN);

        titleCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        titleCellStyle.setAlignment(CellStyle.VERTICAL_CENTER);

        titleCellStyle.setFont(titleFont);

        return titleCellStyle;

    }

    private CellStyle createDataStyle(){

        dataCellStyle = wb.createCellStyle();

        dataCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

        dataCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

        dataCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setBorderRight(CellStyle.BORDER_THIN);

        dataCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setBorderTop(CellStyle.BORDER_THIN);

        dataCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

        dataCellStyle.setAlignment(CellStyle.VERTICAL_CENTER);

        return dataCellStyle;

    }

    private CellStyle createLinkStyle(){

        linkCellStyle = wb.createCellStyle();

        linkCellStyle.cloneStyleFrom(dataCellStyle);

        Font hlink_font = wb.createFont();

        hlink_font.setUnderline(Font.U_SINGLE);

        hlink_font.setColor(IndexedColors.BLUE.getIndex());

        linkCellStyle.setFont(hlink_font);

        return linkCellStyle;

    }

    private Cell createCell(Row row, int cellId, String cellValue, CellStyle cellStyle){

        Cell cell = row.createCell((short)cellId);

        cell.setCellValue(cellValue);

        cell.setCellStyle(cellStyle);

        return cell;

    }

    public Workbook getWb() {

        return wb;

    }

    public void setWb(Workbook wb) {

        this.wb = wb;

    }

    public CreationHelper getCreateHelper() {

        return createHelper;

    }

    public void setCreateHelper(CreationHelper createHelper) {

        this.createHelper = createHelper;

    }

    public Sheet getSheet() {

        return sheet;

    }

    public void setSheet(Sheet sheet) {

        this.sheet = sheet;

    }

    public CellStyle getTitleCellStyle() {

        return titleCellStyle;

    }

    public void setTitleCellStyle(CellStyle titleCellStyle) {

        this.titleCellStyle = titleCellStyle;

    }

    public CellStyle getDataCellStyle() {

        return dataCellStyle;

    }

    public void setDataCellStyle(CellStyle dataCellStyle) {

        this.dataCellStyle = dataCellStyle;

    }

    public CellStyle getLinkCellStyle() {

        return linkCellStyle;

    }

    public void setLinkCellStyle(CellStyle linkCellStyle) {

        this.linkCellStyle = linkCellStyle;

    }

    public int getRowHeight() {

        return rowHeight;

    }

    public void setRowHeight(int rowHeight) {

        this.rowHeight = rowHeight;

    }

}

    String []columns=new String[]{"Backend User Story","Test Case Key","User Stories","Site","Priority","Automation Keyword"};

    ExcelOperation excelOperation = new ExcelOperation(columns);

    public void saveToExcel(String savePath,List<Task> tasks){

        Hyperlink link = excelOperation.getCreateHelper().createHyperlink(Hyperlink.LINK_URL);

        int i=1;

         for(Task task:tasks){

             String []dataColumns=new String[]{task.getMainKeyword(),task.getTestCaseKey(),task.getUserStories(), task.getSite(),task.getPriority(),task.getAutomationStatus()};

             link.setAddress(String.format(testCaseURL, task.getTestCaseId()));

             excelOperation.createDataRow(i, dataColumns);

             excelOperation.setHylinkForCell(i, 1, link);

             i++;

            }

         excelOperation.saveToExcel(savePath);

    }