<?php
#require_once("PHPExcel.php");
#require_once("PHPExcel/Reader/Excel2007.php");
#require_once("PHPExcel/Reader/Excel5.php");
// require_once ('PHPExcel/IOFactory.php');
class Common_Upload
{
    /**
     * 读取CSV文件
     * @param string $filePath
     * @return array
     */
    public static function readCSV($filePath)
    {
        $content = file_get_contents($filePath);
        $arr = preg_split('/\n/', $content);
        $data = array();
        foreach ($arr as $k => $v) {
            if ($v) {
                $data[] = explode(",", trim($v));
            }
        }
        return $data;
    }

    public static function readCSVFromTitle($filePath,$title)
    {
        $content = file_get_contents($filePath);
        $arr = preg_split('/\n/', $content);
        $data = array();
        $newIndex = array();
        $fileTitle = explode(",", trim( $arr[0]));
        foreach ($title as $key=>$value){
            $fileKey = array_search($key,$fileTitle);
            if($fileKey!==false){
                $newIndex[$fileKey] = $value;
            }
        }
        unset($arr[0]);
        foreach ($arr as $k => $v) {
            if ($v) {
                $lineData= explode(",", trim($v));
                $dataTuble = array();
                foreach ($newIndex as $k=>$v){
                    $dataTuble[$v]= $lineData[$k];
                }
                $data[]= $dataTuble;
            }
        }
        return $data;
    }
    /**
     * 读取EXCEL文件
     * @param string $filePath
     * @return array
     */
    public static function readEXCEL($filePath,$readDataOnly=false)
    {
        $PHPExcel = new PHPExcel();
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                return "Invalid File.";
            }
        }
        if($readDataOnly){
          $PHPReader->setReadDataOnly(true);
        }
        $PHPExcel = $PHPReader->load($filePath);
        $currentSheet = $PHPExcel->getSheet(0);
        $keyArr="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 BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZCA CB CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW CX CY CZ";
        $keyArr = explode(' ', $keyArr);
        $keyArrFlip = array_flip($keyArr);
// 		print_r($keyArrFlip);exit;
        /**取得一共有多少列*/
        $maxColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $rowCount = $currentSheet->getHighestRow();
        $result = array();
        for ($row = 1; $row <= $rowCount; $row++) {
            $totalLen = 0; //记录行总长度
            for ($column = $keyArrFlip['A']; $column <= $keyArrFlip[$maxColumn]; $column++) {
                $value = $currentSheet->getCell($keyArr[$column] . $row)->getValue();
                if (is_object($value)) {
                    $value = $value->__toString();
                }

                /*日期转换 simple
                if(preg_match('/^\d{5}$/',$value)){
                	$value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
                }
          */
                // 日期转换 events
                /*if(preg_match('/^\d{5}+(\.\d+)?$/',$value)){
                    $value = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($value));
                }*/

                $result[$row][] = $value;
                $totalLen += strlen(trim($value));
            }
            if ($totalLen == 0) unset($result[$row]); //去掉空行
        }
//         print_r($result);exit;
        return array_values($result);
    }

    /**
     * 读取EXCEL文件
     * @param string $filePath
     * @return array
     */
    public static function readEXCELNew($filePath,$readDataOnly=false)
    {
        $PHPExcel = new PHPExcel();
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                return "Invalid File.";
            }
        }
        if($readDataOnly){
          $PHPReader->setReadDataOnly(true);
        }
        $PHPExcel = $PHPReader->load($filePath);
        $currentSheet = $PHPExcel->getSheet(0);
        $keyArr="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 BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZCA CB CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW CX CY CZ";
        $keyArr = explode(' ', $keyArr);
        $keyArrFlip = array_flip($keyArr);
        /**取得一共有多少列*/
        $maxColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $rowCount = $currentSheet->getHighestRow();
        $result = array();
        for ($row = 1; $row <= $rowCount; $row++) {
            $totalLen = 0; //记录行总长度
            for ($column = $keyArrFlip['A']; $column <= $keyArrFlip[$maxColumn]; $column++) {
                $value = $currentSheet->getCell($keyArr[$column] . $row)->getValue();
                if (is_object($value)) {
                    $value = $value->__toString();
                }

                // 日期转换 events
                if(preg_match('/^\d{5}+(\.\d+)?$/',$value)){
                    $value = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($value));
                }

                $result[$row][] = $value;
                $totalLen += strlen(trim($value));
            }
            if ($totalLen == 0) unset($result[$row]); //去掉空行
        }
        return array_values($result);
    }
    /**
     * Note:
     * User: Yao
     * Date: 2020/5/29
     * Time: 15:57
     * @param $filePath 文件地址
     * @param $title 标题
     * @param bool $readDataOnly 是否只读
     * @param int $sheetIndex 要读的sheet
     * @return array
     */
    public static function readXlsFromTitle($filePath,$title,$readDataOnly=true,$sheetIndex=0)
    {
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                return "Invalid File.";
            }
        }
        $PHPReader->setReadDataOnly($readDataOnly);
        $PHPExcel = $PHPReader->load($filePath);
        $currentSheet = $PHPExcel->getSheet($sheetIndex);

        $maxColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $rowCount = $currentSheet->getHighestRow();
        $maxColumn=PHPExcel_Cell::columnIndexFromString($maxColumn);
        $sheetTitle=array();
        for ($column =0; $column < $maxColumn; $column++) {
            $value = $currentSheet->getCellByColumnAndRow($column,1)->getValue();
            if (is_object($value)) {
                $value = $value->__toString();
            }
            $sheetTitle[$column] = trim($value);
        }
        foreach($sheetTitle as $col=>$value){
            foreach($title as $t=>$index){
                if($value==$t){
                    $readCol[$col]=$index;
                }
            }
        }

        $result = array();
        if(empty($readCol)){
            return $result;
        }
        for ($row = 2; $row <=$rowCount; $row++) {
            $totalLen = 0; //记录行总长度
            foreach($readCol as $column=>$index){
                $value = $currentSheet->getCellByColumnAndRow($column,$row)->getValue();
                if (is_object($value)) {
                    $value = $value->__toString();
                }
                $result[$row][$index] = trim($value);
                $totalLen += strlen(trim($value));
            }
            if ($totalLen == 0) unset($result[$row]); //去掉空行
        }
//         print_r($result);exit;
        return $result;
    }

    /**
     * @desc 解析文件内容转为数组
     * @tips 将第一行转为Key
     * @param $fileName
     * @param $filePath
     * @return array
     */
    public static function readUploadFile($fileName, $filePath)
    {
        $pathinfo = pathinfo($fileName);
        $fileData = array();

        if (isset($pathinfo["extension"]) && $pathinfo["extension"] == "xls") {
            $fileData = self::readEXCEL($filePath);
        }
        $result = array();
        if (is_array($fileData) && !empty($fileData)) {
            foreach ($fileData[0] as $key => $value) {
                if (isset($columnMap[$value])) {
                    $fileData[0][$key] = $columnMap[$value];
                }
            }
            foreach ($fileData as $key => $value) {
                if ($key == 0) {
                    continue;
                }
                foreach ($value as $vKey => $vValue) {
                    if ($fileData[0][$vKey] == "") continue;
                    $result[$key][$fileData[0][$vKey]] = $vValue;
                }
            }
        }
        return $result;
    }
   public static function readEXCELAndType($filePath,$readDataOnly=false)
    {
        $PHPExcel = new PHPExcel();
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                return "Invalid File.";
            }
        }
        if($readDataOnly){
          $PHPReader->setReadDataOnly(true);
        }
        $PHPExcel = $PHPReader->load($filePath);
        $currentSheet = $PHPExcel->getSheet(0);
        $keyArr="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 BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZCA CB CC CD CE CF CG CH CI CJ CK CL CM CN CO CP CQ CR CS CT CU CV CW CX CY CZ";
        $keyArr = explode(' ', $keyArr);
        $keyArrFlip = array_flip($keyArr);
// 		print_r($keyArrFlip);exit;
        /**取得一共有多少列*/
        $maxColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $rowCount = $currentSheet->getHighestRow();
        $resultValue = array();
        $resultType=array();
        for ($row = 1; $row <= $rowCount; $row++) {
            $totalLen = 0; //记录行总长度
            for ($column = $keyArrFlip['A']; $column <= $keyArrFlip[$maxColumn]; $column++) {
                $cell=$currentSheet->getCell($keyArr[$column] . $row);
                $value =$cell ->getValue();
                if (is_object($value)) {
                    $value = $value->__toString();
                }
                $type=$cell->getDataType();
                $cellstyleformat=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();  
                $fomart=$cellstyleformat->getFormatCode();  
              

                /*日期转换 simple
                if(preg_match('/^\d{5}$/',$value)){
                	$value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
                }
          */
                
                $resultValue[$row][] = $value;
                $resultType[$row][]=array('type'=>$type,'fomart'=>$fomart);
                $totalLen += strlen(trim($value));
            }
            if ($totalLen == 0) {//去掉空行
                unset($resultValue[$row]); 
                unset($resultType[$row]);
            }
        }
//         print_r($result);exit;
        return array('value'=>  array_values($resultValue),'type'=>  array_values($resultType));
    }
    public static function checkCellType($fomate){
         if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $fomate)){
                         return true;
            }
            return false;
    }

}