<?php namespace Dever\Support;

use Dever;
use Dever\Output\Export;
# 以下代码来源自网络
set_time_limit(0);
class Excel
{
    /**
     * instance
     *
     * @var string
     */
    protected static $instance;

    /**
     * cell
     *
     * @var array
     */
    private $cell = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

    /**
     * getInstance
     *
     * @return Dever\Support\Excel
     */
    public static function getInstance()
    {
        if (empty(self::$instance)) {
            self::$instance = new self();
        }

        return self::$instance;
    }

    /**
     * export
     *
     * @return mixed
     */
    public static function export($data = array(), $header = array(), $fileName = '', $sheet = 0, $sheetName = '', $return = false, $xls = false, $method = false)
    {
        $type = Dever::input('excel_type');
        if ($type == 1) {
            if ($data) {
                foreach ($data as $k => $v) {
                    foreach ($v as $k1 => $v1) {
                        if (isset($header[$k1])) {
                            $t = $header[$k1];
                            if (strstr($t, '身份证') || strstr($t, '手机') || strstr($t, '电话') || strstr($t, '编号') || strstr($t, '编码')) {
                                $data[$k][$k1] .= "\t";
                            }
                        }
                        if (is_numeric($data[$k][$k1])) {
                            $data[$k][$k1] = round($v1, 4);
                        }
                    }
                }
            }
            return array('body' => $data, 'head' => $header, 'filename' => $fileName);
        }
        if (!$method) {
            if (Dever::project('excel')) {
                Dever::apply('src/PHPExcel', 'excel');
            }
            $state = class_exists('\PHPExcel');
            if ($state) {
                $method = 'excel';
            } else {
                $method = 'csv';
            }
        }

        $method .= '_export';

        return self::getInstance()->$method($data, $header, $fileName, $sheet, $sheetName, $return, $xls);
    }

    /**
     * import
     *
     * @return array
     */
    public static function import($file, $sheet = 0, $offset = 0, $method = false)
    {
        if (Dever::project('excel')) {
            Dever::apply('src/PHPExcel', 'excel');
        }
        if (!$method) {
            $state = class_exists('\PHPExcel');
            if ($state) {
                $method = 'excel';
            } else {
                $method = 'csv';
            }
        }

        $method .= '_import';

        return self::getInstance()->$method($file, $sheet, $offset);
    }

    private function excel_import($file = '', $sheet = 0, $offset = 0)
    {
        $file = mb_convert_encoding($file, "UTF-8", "gbk");
        if(empty($file) OR !file_exists($file)) {
            Export::alert('file not exists!');
        }
        $objRead = new \PHPExcel_Reader_Excel2007();
        if (!$objRead->canRead($file)) {
            $objRead = new \PHPExcel_Reader_Excel5();
            if (!$objRead->canRead($file)) {
                Export::alert('file not exists!');
            }
        }
      
        $obj = $objRead->load($file);
        $currSheet = $obj->getSheet($sheet);
        $columnH = $currSheet->getHighestColumn();
        $columnCnt = array_search($columnH, $this->cell);
        if (!$columnCnt) {
            $columnCnt = $offset - 1;
        }
        $rowCnt = $currSheet->getHighestRow();
      
        $data = array();
        for ($_row = 1; $_row <= $rowCnt; $_row++) {
            for ($_column = 0; $_column <= $columnCnt; $_column++) {
                $cellId = $this->cell[$_column].$_row;
                //$cellValue = $currSheet->getCell($cellId)->getValue();
                $cellValue = $currSheet->getCell($cellId)->getCalculatedValue();
                if ($cellValue instanceof \PHPExcel_RichText) {
                    $cellValue = $cellValue->__toString();
                }
                $data[$_row][$this->cell[$_column]] = $cellValue;
            }
        }
      
        return $data;  
    }

    private function excel_export($data = array(), $header = array(), $fileName = '', $sheet = 0, $sheetName = '', $return = false, $xls = false)
    {
        if (!$xls) {
            $xls = new \PHPExcel();
        }

        if ($sheet > 0) {
            $xls->createSheet();
        }
        $act = $xls->setActiveSheetIndex($sheet);

        if ($sheetName) {
            $act->setTitle($sheetName);
        }
        
        $row = 1;
        if($header) {
            $i = 0;
            if (isset($header['top'])) {
                foreach($header['top'] as $v) {
                    $act->setCellValue($this->cell[$i] . $row, $v);
                    $act->getColumnDimension($this->cell[$i])->setWidth(30);
                    $i++;
                }
                $row++;
                unset($header['top']);
            }
            $i = 0;
            foreach($header as $v) {
                $act->setCellValue($this->cell[$i] . $row, $v);
                $act->getColumnDimension($this->cell[$i])->setWidth(30);
                $i++;
            }
            $row++;
        }

        if($data) {
            $i = 0;
            $height = $max = 80;
            if (Dever::config('host')->project && isset(Dever::config('host')->project['upload'])) {
                $project = Dever::config('host')->project;
                $project['upload'] = array();
                Dever::config('host')->project = $project;
            }
            foreach($data as $v) {
                $j = 0;
                foreach($v as $cell) {
                    //$cell = strip_tags($cell);
                    $html = Dever::ishtml($cell);
                    if ($html) {
                        $wizard = new \PHPExcel_Helper_HTML;
                        $cell = $wizard->toRichTextObject('<?xml encoding="UTF-8">' . $cell);
                    }
                    if (!$html && (strstr($cell, '.jpg') || strstr($cell, '.gif') || strstr($cell, '.png'))) {
                        $key = ($i+$row);
                        $value = false;

                        if (strpos($cell, '||')) {
                            $t = explode('||', $cell);
                            $cell = $t[1];
                            $value = $t[0];
                        }
                        $temp = explode(',', $cell);

                        foreach ($temp as $ck => $cv) {
                            $objDrawing[$ck] = new \PHPExcel_Worksheet_Drawing();
                            if (Dever::project('upload')) {
                                $cv = str_replace('.jpg', '_t1.jpg', $cv);
                                $cv = str_replace('.png', '_t1.png', $cv);
                                $cv = Dever::load('upload/view')->get($cv);
                            }
                            $cv = Dever::local($cv);
                            if (!is_file($cv)) {
                                continue;
                            }
                            $objDrawing[$ck]->setPath($cv);
                            $objDrawing[$ck]->setHeight($height);
                            //$objDrawing[$ck]->setWidth(150);
                            $objDrawing[$ck]->setCoordinates($this->cell[$j] . ($i+$row));
                            $objDrawing[$ck]->setOffsetX(12);
                            if ($ck == 0) {
                                $offsetY = 5;
                            } else {
                                $offsetY = $offsetY + $height + 5;
                            }
                            $objDrawing[$ck]->setOffsetY($offsetY);
                            $objDrawing[$ck]->setWorksheet($act);
                        }
                        if ($value) {
                            $act->setCellValue($this->cell[$j] . ($i+$row), $value);
                        }
                        
                        $th = $height * count($temp);
                        if ($th > $max) {
                            $max = $th;
                        }
                        $act->getRowDimension($i+$row)->setRowHeight($max);
                        
                    } else {
                        if (!$cell) {
                            //$cell = "";
                        }
                        if (is_numeric($cell) && mb_strlen($cell) >= 10) {
                            $cell .= "\t";
                        }
                        $act->setCellValue($this->cell[$j] . ($i+$row), $cell);
                        $act->getStyle($this->cell[$j] . ($i+$row))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    }
                    
                    $act->getColumnDimension($this->cell[$j])->setAutoSize();
                    $act->getColumnDimension($this->cell[$j])->setWidth(30);
                    $j++;
                }
                $i++;
            }
        }

        if ($header && $return) {
            return $xls;
        }

        if (!$fileName) {
            $fileName = uniqid(time(),true);
        }
        
        $write = \PHPExcel_IOFactory::createWriter($xls, 'Excel2007');
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('pragma:public');
        header("Content-Disposition:attachment;filename=$fileName.xlsx");
        $write->save('php://output');
    }

    private function csv_import($file, $lines = 0, $offset = 0)
    {
        if (!$fp = fopen($file, 'r')) {
            return false;
        }
        $i = $j = 0;
        while (false !== ($line = fgets($fp))) {
            if ($i++ < $offset) {
                continue;
            }
            break;
        }
        $data = array();
        while (($j++ < $lines) && !feof($fp)) {
            $data[] = fgetcsv($fp);
        }
        fclose($fp);
        return $data;
    }

    private function csv_export($data = array(), $header = array(), $fileName = '', $sheet = 0, $sheetName = '', $return = false, $xls = false)
    {
        header('Content-Type: application/vnd.ms-excel;charset=gb2312,\uFEFF');
        header('Content-Disposition: attachment;filename='.$fileName.'.csv');
        header('Cache-Control: max-age=0');
        header('Content-Transfer-Encoding: binary'); 
        $fp = fopen('php://output', 'a');
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
        if (isset($header['top'])) {
            fputcsv($fp, $header['top']);
            unset($header['top']);
        }
        if (!empty($header)) {
            fputcsv($fp, $header);
        }
        $num = 0;
        //每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
        $limit = 100000;
        //逐行取出数据，不浪费内存
        $count = count($data);
        if ($count > 0) {
            for ($i = 0; $i < $count; $i++) {
                if (isset($data[$i])) {
                    $num++;
                    //刷新一下输出buffer，防止由于数据过多造成问题
                    if ($limit == $num) {
                        ob_flush();
                        flush();
                        $num = 0;
                    }
                    $row = $data[$i];
                    foreach ($row as $k => $v) {
                        if (is_numeric($row[$k]) && mb_strlen($row[$k]) >= 10) {
                            $row[$k] .= "\t";
                        }
                    }
                    
                    fputcsv($fp, $row);
                }
            }
        }
        fclose($fp);
    }
}
