<?php

class Php_ExcelWriter implements ExcelWriterInterface
{
    protected $spreadsheet;
    protected $writer;
    protected $templateSheet = -1;

    public function __construct()
    {
        $this->spreadsheet = new \ExcelBook('User', 'key', true);
     }

    public function setTemplate($path)
    {
        $this->spreadsheet->loadFile($path);
        $this->currentSheet = $this->spreadsheet->getSheet(0);
    }

    public function setTemplateSheet($index)
    {
        $this->templateSheet = $index;
    }

    public function selectSheet($index)
    {
        $this->currentSheet = $this->spreadsheet->getSheet($index);
        $this->spreadsheet->activeSheet($index);
    }

    public function setCreator($creator)
    {
        //TODO
        // $properties = $this->spreadsheet->getProperties();
        // $properties->setCreator($creator);
        // $properties->setLastModifiedBy("Maarten Balliauw");
        // $properties->setTitle("Office 2007 XLSX Test Document");
        // $properties->setSubject("Office 2007 XLSX Test Document");
        // $properties->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
        // $properties->setKeywords("office 2007 openxml php");
        // $properties->setCategory("Test result file");
    }

    public function setCellValue($cell, $value)
    {
        $pos = $this->currentSheet->addrToRowCol($cell);
        $this->currentSheet->write($pos['row'], $pos['column'], $value);
    }

    public function removeSheet($index)
    {
        $this->spreadsheet->deleteSheet($index);
    }

    public function addImage($path, $cell, $name, $height, $width)
    {
        $imageId = $this->spreadsheet->addPictureFromFile($path);
        $pos = $this->currentSheet->addrToRowCol($cell);
        $this->currentSheet->addPictureDim($pos['row'], $pos['column'], $imageId, $width, $height);
    }

    public function createNewFromTemplate()
    {
        if ($this->templateSheet > -1){
            $this->currentSheet = $this->spreadsheet->copySheet('', $this->templateSheet);
            $sheetCount = $this->spreadsheet->sheetCount();
            $this->spreadsheet->insertSheet($sheetCount, '', $this->currentSheet);
        }
        else{
            $this->spreadsheet->addSheet('');
        }
    }

    public function insertRowsBefore($row, $number = 1)
    {
        $firstRow = $row - 1;
        $lastRow = $number + $row - 2;
        $retval = $this->currentSheet->insertRow($firstRow, $lastRow);

        $formats = array();
        for ($i = 0; $i < 20; $i++) {
            $formats[] = $this->currentSheet->cellFormat($firstRow - 1, $i);
        }

        for ($i = $firstRow; $i < $lastRow + 1; $i++){
            for ($j = 0; $j < 20; $j++) {
                $this->currentSheet->setCellFormat($i, $j, $formats[$j]);
            }
        }
    }

    public function fromArray($data, $cell)
    {
        $pos = $this->currentSheet->addrToRowCol($cell);
        $length = count($data);
        for ($i = 0; $i < $length; $i++) {
            $row = $data[$i];
            if ($pos['column'] > 0){
                for ($j = 0; $j < $pos['column']; $j++) {
                    array_unshift($row, '');
                }
            }
            $this->currentSheet->writeRow($pos['row'] + $i, $row);
        }
    }

    public function setCellsCurrency($cells, $currencySymbol = null, $before = true)
    {
        // Setup a new format
        $formatCode = '#,##0.00';
        if (!is_null($currencySymbol)){
            if ($before){
                $formatCode = $currencySymbol . $formatCode;
            }
            else{
                $formatCode .= ' ' . $currencySymbol;
            }
        }

        // Create the object
        $formatId = $this->spreadsheet->addCustomFormat($formatCode);
        if (strpos($cells, ':') !== false){
            $cellRange = explode(':', $cells);
            $firstRange = $this->currentSheet->addrToRowCol($cellRange[0]);
            $secondRange = $this->currentSheet->addrToRowCol($cellRange[1]);
            // So we don't lose formatting, base this on the format of the first cell.
            $format = $this->currentSheet->cellFormat($firstRange['row'], $firstRange['column']);
            $format->numberFormat($formatId);

            // Loop through the range.
            for ($i = $firstRange['row']; $i < $secondRange['row'] + 1; $i++) {
                $this->currentSheet->setCellFormat($i, $firstRange['column'], $format);
            }
        }
        else{
            $pos = $this->currentSheet->addrToRowCol($cells);
            $format = $this->currentSheet->cellFormat($pos['row'], $pos['column']);
            $format->numberFormat($formatId);
            $this->currentSheet->setCellFormat($pos['row'], $pos['column'], $format);
        }
    }

    public function setCellsPercent($cells)
    {
        if (strpos($cells, ':') !== false){
            $cellRange = explode(':', $cells);
            $firstRange = $this->currentSheet->addrToRowCol($cellRange[0]);
            $secondRange = $this->currentSheet->addrToRowCol($cellRange[1]);
            // So we don't lose formatting, base this on the format of the first cell.
            $format = $this->currentSheet->cellFormat($firstRange['row'], $firstRange['column']);
            $format->numberFormat(\ExcelFormat::NUMFORMAT_PERCENT);

            // Loop through the range.
            for ($i = $firstRange['row']; $i < $secondRange['row'] + 1; $i++) {
                $this->currentSheet->setCellFormat($i, $firstRange['column'], $format);
            }
        }
        else{
            $pos = $this->currentSheet->addrToRowCol($cellRange[0]);
            $format = $this->currentSheet->cellFormat($pos['row'], $pos['column']);
            $format->numberFormat($formatId);
            $this->currentSheet->setCellFormat($pos['row'], $pos['column'], $format);
        }
    }

    public function setSheetTitle($title)
    {
        $this->currentSheet->setName($title);
    }

    public function writeFile($fullpath)
    {
        // Delete the template sheet
        $this->spreadsheet->deleteSheet($this->templateSheet);
        $this->spreadsheet->save($fullpath);
    }
}