<?php
namespace common\helpers;

use Yii;

class ExcelHelper
{
    /**
     * @param string $pFilename
     * @param bool $skipFirstRow
     * @return array|bool
     */
    public static function load($pFilename, $skipFirstRow = true)
    {
        if (!is_file($pFilename)) {
            return false;
        }
        $data = [];
        $objPHPExcel = \PHPExcel_IOFactory::load($pFilename);
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            foreach ($worksheet->getRowIterator() as $row) {
                if ($row->getRowIndex() == 1 && $skipFirstRow) {
                    continue;
                }
                $newRow = [];
                /** @var \PHPExcel_Worksheet_RowCellIterator $cellIterator */
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
                foreach ($cellIterator as $cell) {
                    $newRow[] = $cell == null ? null : $cell->getCalculatedValue();
                }
                $data[] = $newRow;
            }
        }
        return $data;
    }

    /**
     * @param array $source
     * @param array $header
     * @param string $attachmentName
     * @throws \Exception
     */
    public static function output($source, $header = [], $attachmentName)
    {
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            throw new \Exception($cacheMethod . " caching method is not available");
        }
        \PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_MyDefaultValueBinder());
        // Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
        // Add some data
        if ($header) {
            $objPHPExcel->getActiveSheet()->fromArray($header, null, 'A1', true);
            $objPHPExcel->getActiveSheet()->fromArray($source, null, 'A2', true);
        } else {
            $objPHPExcel->getActiveSheet()->fromArray($source, null, 'A1', true);
        }
        // Redirect output to a client’s web browser (Excel2007)
//        Yii::$app->getResponse()->setDownloadHeaders($attachmentName)->send();
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//        $objWriter->save('php://output');
        $saveDir = Yii::getAlias('@webroot') . '/excel/';
        if (!is_dir($saveDir)) {
            mkdir($saveDir, 0755, true);
        }
        $saveFile = $saveDir . $attachmentName . '_' . mt_rand(10000, 99999);
        $objWriter->save($saveFile);
        Yii::$app->getResponse()->sendFile($saveFile, $attachmentName, ['mimeType' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'])->send();
        @unlink($saveFile);
        Yii::$app->end();
    }

    public static function init()
    {
        set_time_limit(0);
        ini_set('memory_limit', '1024M');
    }
}