<?php
/**
 * Copyright 2012-2026 ShopeX (https://www.shopex.cn)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

require_once ROOT_DIR.'/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

/**
 *  PHPEXECL 处理类
 */

class financebase_phpexcel
{

	/**
	 * 使用PHPEXECL导入
	 * 
	 * @param string $file      文件地址
	 * @param int    $sheet     工作表sheet(传0则获取第一个sheet)
	 * @param int    $columnCnt 列数(传0则自动获取最大列)
	 * @param array  $options   操作选项
	 *                          array mergeCells 合并单元格数组
	 *                          array formula    公式数组
	 *                          array format     单元格格式数组
	 * 
	 * @return array
	 * @throws Exception
	 */
	public function importExecl($file = '', $sheet = 0, $columnCnt = 0, &$options = array())
	{
	    try {
	        /* 转码 */
	        $file = iconv("utf-8", "gb2312", $file);

	        if (empty($file) OR !file_exists($file)) {
	            throw new \Exception('文件不存在!');
	        }

	        /** @var Xlsx $objRead */
	        $objRead = IOFactory::createReader('Xlsx');

	        if (!$objRead->canRead($file)) {
	            /** @var Xls $objRead */
	            $objRead = IOFactory::createReader('Xls');

	            if (!$objRead->canRead($file)) {
	                throw new \Exception('只支持导入Excel文件！');
	            }
	        }

	        /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
	        empty($options) && $objRead->setReadDataOnly(true);
	        /* 建立excel对象 */
	        $obj = $objRead->load($file);
	        /* 获取指定的sheet表 */
	        $currSheet = $obj->getSheet($sheet);

	        if (isset($options['mergeCells'])) {
	            /* 读取合并行列 */
	            $options['mergeCells'] = $currSheet->getMergeCells();
	        }

	        if (0 == $columnCnt) {
	            /* 取得最大的列号 */
	            $columnH = $currSheet->getHighestColumn();
	            /* 兼容原逻辑，循环时使用的是小于等于 */
	            $columnCnt = Coordinate::columnIndexFromString($columnH);
	        }

	        /* 获取总行数 */
	        $rowCnt = $currSheet->getHighestRow();
	        $data   = [];

	        /* 读取内容 */
	        for ($_row = 1; $_row <= $rowCnt; $_row++) {
	            $isNull = true;

	            for ($_column = 1; $_column <= $columnCnt; $_column++) {
	                $cellName = Coordinate::stringFromColumnIndex($_column);
	                $cellId   = $cellName . $_row;
	                $cell     = $currSheet->getCell($cellId);

	                if (isset($options['format'])) {
	                    /* 获取格式 */
	                    $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
	                    /* 记录格式 */
	                    $options['format'][$_row][$cellName] = $format;
	                }

	                if (isset($options['formula'])) {
	                    /* 获取公式，公式均为=号开头数据 */
	                    $formula = $currSheet->getCell($cellId)->getValue();

	                    if (0 === strpos($formula, '=')) {
	                        $options['formula'][$cellName . $_row] = $formula;
	                    }
	                }

	                if (isset($format) && 'm/d/yyyy' == $format) {
	                    /* 日期格式翻转处理 */
	                    $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
	                }

	                $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

	                if (!empty($data[$_row][$cellName])) {
	                    $isNull = false;
	                }
	            }

	            /* 判断是否整行数据为空，是的话删除该行数据 */
	            if ($isNull) {
	                unset($data[$_row]);
	            }
	        }

	        return $data;
	    } catch (\Exception $e) {
	        throw $e;
	    }
	}

	/**
	 * Excel导出，TODO 可继续优化
	 * 
	 * @param array  $datas      导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号']
	 * @param string $fileName   导出文件名称
	 * @param array  $options    操作选项，例如：
	 *                           bool   print       设置打印格式
	 *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
	 *                           array  setARGB     设置背景色，例如['A1', 'C1']
	 *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
	 *                           bool   setBorder   设置单元格边框
	 *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
	 *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
	 *                           array  format      设置格式，整列设置，例如['A' => 'General']
	 *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
	 *                           array  bold        设置加粗样式，例如['A1', 'A2']
	 *                           string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
	 */
	public function exportExcel($datas, $fileName = '', $options = array())
	{
	    try {
	        if (empty($datas)) {
	            return false;
	        }

	        set_time_limit(0);
	        /** @var Spreadsheet $objSpreadsheet */
	        $objSpreadsheet = new Spreadsheet;
	        /* 设置默认文字居左，上下居中 */
	        $styleArray = [
	            'alignment' => [
	                'horizontal' => Alignment::HORIZONTAL_LEFT,
	                'vertical'   => Alignment::VERTICAL_CENTER,
	            ],
	        ];
	        $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
	        /* 设置Excel Sheet */
	        $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

	        /* 打印设置 */
	        if (isset($options['print']) && $options['print']) {
	            /* 设置打印为A4效果 */
	            $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
	            /* 设置打印时边距 */
	            $pValue = 1 / 2.54;
	            $activeSheet->getPageMargins()->setTop($pValue / 2);
	            $activeSheet->getPageMargins()->setBottom($pValue * 2);
	            $activeSheet->getPageMargins()->setLeft($pValue / 2);
	            $activeSheet->getPageMargins()->setRight($pValue / 2);
	        }

	        /* 行数据处理 */
	        foreach ($datas as $sKey => $sItem) {
	            /* 默认文本格式 */
	            $pDataType = DataType::TYPE_STRING;

	            /* 设置单元格格式 */
	            if (isset($options['format']) && !empty($options['format'])) {
	                $colRow = Coordinate::coordinateFromString($sKey);

	                /* 存在该列格式并且有特殊格式 */
	                if (isset($options['format'][$colRow[0]]) &&
	                    NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
	                    $activeSheet->getStyle($sKey)->getNumberFormat()
	                        ->setFormatCode($options['format'][$colRow[0]]);

	                    if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
	                        is_numeric(str_replace(['￥', ','], '', $sItem))) {
	                        /* 数字格式转换为数字单元格 */
	                        $pDataType = DataType::TYPE_NUMERIC;
	                        $sItem     = str_replace(['￥', ','], '', $sItem);
	                    }
	                } elseif (is_int($sItem)) {
	                    $pDataType = DataType::TYPE_NUMERIC;
	                }
	            }

	            $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);

	            /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
	            if (false !== strstr($sKey, ":")) {
	                $options['mergeCells'][$sKey] = $sKey;
	            }
	        }

	        unset($datas);

	        /* 设置锁定行 */
	        if (isset($options['freezePane']) && !empty($options['freezePane'])) {
	            $activeSheet->freezePane($options['freezePane']);
	            unset($options['freezePane']);
	        }

	        /* 设置宽度 */
	        if (isset($options['setWidth']) && !empty($options['setWidth'])) {
	            foreach ($options['setWidth'] as $swKey => $swItem) {
	                $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
	            }

	            unset($options['setWidth']);
	        }

	        /* 设置背景色 */
	        if (isset($options['setARGB']) && !empty($options['setARGB'])) {
	            foreach ($options['setARGB'] as $sItem) {
	                $activeSheet->getStyle($sItem)
	                    ->getFill()->setFillType(Fill::FILL_SOLID)
	                    ->getStartColor()->setARGB(Color::COLOR_YELLOW);
	            }

	            unset($options['setARGB']);
	        }

	        /* 设置公式 */
	        if (isset($options['formula']) && !empty($options['formula'])) {
	            foreach ($options['formula'] as $fKey => $fItem) {
	                $activeSheet->setCellValue($fKey, $fItem);
	            }

	            unset($options['formula']);
	        }

	        /* 合并行列处理 */
	        if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
	            $activeSheet->setMergeCells($options['mergeCells']);
	            unset($options['mergeCells']);
	        }

	        /* 设置居中 */
	        if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
	            $styleArray = [
	                'alignment' => [
	                    'horizontal' => Alignment::HORIZONTAL_CENTER,
	                    'vertical'   => Alignment::VERTICAL_CENTER,
	                ],
	            ];

	            foreach ($options['alignCenter'] as $acItem) {
	                $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
	            }

	            unset($options['alignCenter']);
	        }

	        /* 设置加粗 */
	        if (isset($options['bold']) && !empty($options['bold'])) {
	            foreach ($options['bold'] as $bItem) {
	                $activeSheet->getStyle($bItem)->getFont()->setBold(true);
	            }

	            unset($options['bold']);
	        }

	        /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
	        if (isset($options['setBorder']) && $options['setBorder']) {
	            $border    = [
	                'borders' => [
	                    'allBorders' => [
	                        'borderStyle' => Border::BORDER_THIN, // 设置border样式
	                        'color'       => ['argb' => 'FF000000'], // 设置border颜色
	                    ],
	                ],
	            ];
	            $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
	            $activeSheet->getStyle($setBorder)->applyFromArray($border);
	            unset($options['setBorder']);
	        }

	        $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

	        if (!isset($options['savePath'])) {
	            /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
	            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	            header(
	                "Content-Disposition:attachment;filename=" . iconv(
	                    "utf-8", "GB2312//TRANSLIT", $fileName
	                )
	            );
	            header('Cache-Control: max-age=0');//禁止缓存
	            $savePath = 'php://output';
	        } else {
	            $savePath = $options['savePath'];
	        }

	        ob_clean();
	        ob_start();
	        $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
	        $objWriter->save($savePath);
	        /* 释放内存 */
	        $objSpreadsheet->disconnectWorksheets();
	        unset($objSpreadsheet);
	        ob_end_flush();

	        return true;
	    } catch (Exception $e) {
	        return false;
	    }
	}

    /**
     * 获取TotalRow
     * @param mixed $file file
     * @param mixed $sheet sheet
     * @return mixed 返回结果
     */
    public function getTotalRow($file = '', $sheet = 0)
	{
		try {
	        /* 转码 */
	        $file = iconv("utf-8", "gb2312", $file);

	        if (empty($file) OR !file_exists($file)) {
	            throw new \Exception('文件不存在!');
	        }

	        /** @var Xlsx $objRead */
	        $objRead = IOFactory::createReader('Xlsx');

	        if (!$objRead->canRead($file)) {
	            /** @var Xls $objRead */
	            $objRead = IOFactory::createReader('Xls');

	            if (!$objRead->canRead($file)) {
	                throw new \Exception('只支持导入Excel文件！');
	            }
	        }

	      
	        /* 建立excel对象 */
	        $obj = $objRead->load($file);
	        /* 获取指定的sheet表 */
	        $currSheet = $obj->getSheet($sheet);

	        return $currSheet->getHighestRow();
	        
	    } catch (\Exception $e) {
	        throw $e;
	    }
	}

	/**
	 * @param $data 数据 二维数组
	 * @param $fileName 文件名 没有后缀
	 * @param $fileType 文件类型
	 * @param null $title 标题
	 * @param null $path 保存路径
	 * @return bool
	 * @throws Exception
	 */
	public function newExportExcel($data,$fileName,$fileType,$title = null,$path = null) {
//文件名称校验
        try {
            if(!$fileName) {
                throw new \Exception('文件名不能为空');
            }

            $fileType  = ucfirst(strtolower($fileType));
            //Excel文件类型校验
            $type = ['Excel2007', 'Xlsx', 'Excel5', 'Xls'];
            if(!in_array($fileType, $type)) {
                throw new \Exception('未知文件类型');
            }

            $spreadsheet = new Spreadsheet();
            $worksheet = $spreadsheet->getActiveSheet();


            if($fileType == 'Excel2007' || $fileType == 'Xlsx') {
                $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="'.iconv("utf-8", "GB2312//TRANSLIT", $fileName).'.xlsx"');
                header('Cache-Control: max-age=0');
            } else { //Excel5
                $writer = IOFactory::createWriter($spreadsheet, 'Xls');
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="'.iconv("utf-8", "GB2312//TRANSLIT", $fileName).'.xls"');
                header('Cache-Control: max-age=0');
            }


            $row = 1;
            if (!empty($title)) {
                foreach ($title as $key => $value) {
                    $worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
                }
                $row++;
            }

            if (!empty($data)) {
                foreach ($data as $item) {
                    $column = 1;
                    foreach ($item as $value) {
                        $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                        $column++;
                    }
                    $row++;
                }
            }

//        $writer->save('php://output');

            ob_clean();
            ob_start();

            if (!empty($path)) {
                $savePath = $path;
            } else {
                $savePath = 'php://output';
            }
            $writer->save($savePath);
            /* 释放内存 */
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            ob_end_flush();
            return true;
        }catch (\Exception $e) {
            throw $e;
        }
        return false;
    }

}