<?php
/**
 * The excel library of xxb, can be used to export excel file.
 *
 * @copyright   Copyright 2009-2023 禅道软件（青岛）有限公司(ZenTao Software (Qingdao) Co., Ltd., www.zentao.net)
 * @license     ZOSL (https://zpl.pub/page/zoslv1.html)
 * @author      Gang Liu <liugang@cnezsoft.com>
 * @author      yaozeyuan <yaozeyuan@cnezsoft.com>
 * @package     Excel
 * @version     $Id$
 * @link        https://xuanim.com
 *
 * excel - a library to export excel file, depends on phpexcel.
 *
 * Here are some tips of excelData(named $data) structure maybe uesful. you can use these to create xls/xlsx file . The API is interchangeable.
 *
 * Base property:
 * data->fileName set the fileName
 * data->kind set the kind of excel module
 * data->fields is array like ($field => $fieldTitle). The order of data->fields is also the column's order
 * data->row is array like ($rowNumber => array($field => $value)).This is the data of Excel file. System will fill $value as data into every cell according to $rowNumber and $field
 *
 * Merge cell
 * if there is set data->dataRowspan[$row][$field] or data->dataColspan[$row][$field], the cells will be merge
 *      data->dataRowspan[$row][$field] => merge excelKey[$field]:$rowNumber to excelKey[$field]:($rowNumber + data->dataRowspan[$row][$field]) into one cell
 *      data->dataColspan[$row][$field] => merge excelKey[$field]:$rowNumber to transIntoExcelKey(int(excelKey[$field]) + dataColspan[$row][$field]):$rowNumber into one cell
 *
 * html content
 *      if you set config->excel->editor[$this->rawExcelData->kind] like 'excelKey1,excelKey2...', and excelKey in that, then $value of all column's cell will be process to remove html tag
 *
 * write sysData and use droplist style on cell
 * sysData is an array like (excelKey => valueList), system use this sheet to store extraData.
 * if you want to have droplist style on some column in sheet1, you can set data->($exceKey . 'List'), data->listStyle and  data->sysData sothat the data will be writen into the sysData sheet and you can see the droplist style is enable.
 * the data->listStyle and data->sysData is an  array of series value like ['dropListStyleColumnName' . 'List'] , like ('nameList', 'categoryList', ...) the dropListStyleColumnName used to indicate witch column need that style and data->[dropListStyleColumnName . 'List'] use to transfer data for system get real data to build datalist in sysdata sheet.
 *
 * FreezePane
 * if you set config->excel->freeze->{$this->data->kind}->targetColumnName, this column will be freezed
 *
 * Set width
 * You can set $data->customWidth like array($field => width, $field2 => width, $field3 => width,...) to set width by you like
 * or modify
 *       config->excel->titleFields
 *       config->excel->centerFields
 *       config->excel->dateFields
 * to have default style
 *
 * color
 * if you set data->nocolor, the excel file won't have color
 *
 * File title
 * The lang->excel->title->{data->kind} is the title of data->kind excel file
 *
 * SysData title
 * The lang->excel->title->sysValue is the name of sysData sheet , this is only can use for xls file.
 */
class excel extends model
{
    /**
     * __construct
     *
     * @access public
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
        $this->phpExcel        = $this->app->loadClass('phpexcel');
        $this->file            = $this->loadModel('file');
        $this->sysDataColIndex = 0;
        $this->hasSysData      = false;
    }

    /**
     * Init for excel data.
     *
     * @param  int    $data
     * @access public
     * @return void
     */
    public function init($data)
    {
        $this->rawExcelData   = $data;
        $this->fields         = $data->fields;
        $this->rows           = $data->rows;
        $this->headerRowCount = isset($data->headerRowCount) ? $data->headerRowCount : 1;
        $this->fieldsKey      = $this->headerRowCount == 1 ? array_keys($this->fields) : array_keys(reset($this->fields));
    }

    /**
     * Export data to Excel. This is main function.
     *
     * @param  object $data
     * @param  string $fileType xls | xlsx
     * @param  string $savePath, if $savePath != '', then the file will save in $savePath
     * @access public
     * @return void
     */
    public function export($excelData, $fileType = 'xls', $savePath = '', $schemaSheets = '', $schemaSheetIndex = null)
    {
        $sheetIndex = 0;
        foreach($excelData->dataList as $data) $this->phpExcel->createSheet(); // Create sheets.

        foreach($excelData->dataList as $data)
        {
            $this->init($data);

            $this->excelKey = array();
            foreach($this->fieldsKey as $colIndex => $field) $this->excelKey[$field] = $this->setExcelField($colIndex);

            /* Set file base property */
            $excelProps = $this->phpExcel->getProperties();
            $excelProps->setCreator('ZDOO');
            $excelProps->setLastModifiedBy('ZDOO');
            $excelProps->setTitle('Office XLS Document');
            $excelProps->setSubject('Office XLS Document');
            $excelProps->setDescription('Document generated by PHPExcel.');
            $excelProps->setKeywords('office excel PHPExcel');
            $excelProps->setCategory('Result file');

            $excelSheet = $this->phpExcel->getSheet($sheetIndex);
            $sheetTitle = isset($this->rawExcelData->title) ? $this->rawExcelData->title : $this->rawExcelData->kind;
            if($sheetTitle) $excelSheet->setTitle($sheetTitle);

            $sheetRow = 1;
            if($this->headerRowCount == 1)
            {
                foreach($this->fields as $field => $fieldName)
                {
                    $cell = $this->excelKey[$field] . $sheetRow;
                    $excelSheet->setCellValueExplicit($cell, $fieldName, PHPExcel_Cell_DataType::TYPE_STRING);
                }
                $sheetRow++;
            }
            else
            {
                foreach($this->fields as $fieldIndex => $fields)
                {
                    foreach($fields as $field => $fieldName)
                    {
                        if(isset($this->excelKey[$field]))
                        {
                            $cell = $this->excelKey[$field] . $sheetRow;
                            /* Merge Cells.*/
                            if(isset($this->rawExcelData->headerRowspan[$fieldIndex][$field]) && is_int($this->rawExcelData->headerRowspan[$fieldIndex][$field]))
                            {
                                $endCell = $this->excelKey[$field] . ($sheetRow + $this->rawExcelData->headerRowspan[$fieldIndex][$field] - 1);
                                $excelSheet->mergeCells($cell . ":" . $endCell);
                            }
                            if(isset($this->rawExcelData->headerColspan[$fieldIndex][$field]) && is_int($this->rawExcelData->headerColspan[$fieldIndex][$field]))
                            {
                                $column  = $this->setExcelField($this->rawExcelData->headerColspan[$fieldIndex][$field] - 1, $this->excelKey[$field]);
                                $endCell = $column . $sheetRow;
                                $excelSheet->mergeCells($cell . ":" . $endCell);
                            }

                            $excelSheet->setCellValueExplicit($cell, $fieldName, PHPExcel_Cell_DataType::TYPE_STRING);
                        }
                    }
                    $sheetRow++;
                }
            }

            /* Write system data in excel.*/
            $this->writeSysData();

            foreach($this->rows as $rowIndex => $row)
            {
                foreach($row as $field => $value)
                {
                    if(isset($this->excelKey[$field]))
                    {
                        $cell = $this->excelKey[$field] . $sheetRow;
                        /* Merge Cells.*/
                        if(isset($this->rawExcelData->dataRowspan[$rowIndex][$field]) && is_int($this->rawExcelData->dataRowspan[$rowIndex][$field]))
                        {
                            $endCell = $this->excelKey[$field] . ($sheetRow + $this->rawExcelData->dataRowspan[$rowIndex][$field] - 1);
                            $excelSheet->mergeCells($cell . ":" . $endCell);
                        }
                        if(isset($this->rawExcelData->dataColspan[$rowIndex][$field]) && is_int($this->rawExcelData->dataColspan[$rowIndex][$field]))
                        {
                            $column  = $this->setExcelField($this->rawExcelData->dataColspan[$rowIndex][$field] - 1, $this->excelKey[$field]);
                            $endCell = $column . $sheetRow;
                            $excelSheet->mergeCells($cell . ":" . $endCell);
                        }

                        /* Wipe off html tags.*/
                        if(isset($this->config->excel->editor[$this->rawExcelData->kind]) and in_array($field, $this->config->excel->editor[$this->rawExcelData->kind])) $value = $this->file->excludeHtml($value);
                        if(isset($this->rawExcelData->percentageFields[$rowIndex][$field]))
                        {
                            $excelSheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
                        }
                        elseif(isset($this->rawExcelData->numberFields) && in_array($field, $this->rawExcelData->numberFields))
                        {
                            $excelSheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
                        }
                        else
                        {
                            $excelSheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
                        }

                        /* Add comments to cell, Excel5 don't work, must be Excel2007. */
                        if(isset($this->rawExcelData->comments[$rowIndex][$field]))
                        {
                            $excelSheet->getComment($cell)->getText()->createTextRun($this->rawExcelData->comments[$rowIndex][$field]);
                        }

                        /* Calculate date. */
                        if($value && (strpos($field, 'Date') !== false or in_array($field, $this->config->excel->dateFields)))
                        {
                            $excelSheet->setCellValue($cell, PHPExcel_Calculation_DateTime::DATEVALUE($value));
                        }
                    }

                    /* Build excel list.*/
                    if(isset($this->rawExcelData->listStyle) and in_array($field, $this->rawExcelData->listStyle)) $this->buildList($excelSheet, $field, $sheetRow);
                }
                $sheetRow++;
            }

            $this->setStyle($excelSheet, $sheetRow);
            $this->setAlignment($excelSheet);
            $sheetRow++;

            if(isset($this->rawExcelData->help))
            {
                $excelSheet->mergeCells("A" . $sheetRow . ":" . end($this->excelKey) . $sheetRow);
                $excelSheet->setCellValue("A" . $sheetRow, $this->rawExcelData->help);
            }

            $sheetIndex++;
        }

        /* If hasn't sys data remove the last sheet. */
        if(!$this->hasSysData) $this->phpExcel->removeSheetByIndex($this->phpExcel->getSheetCount() - 1);
        $this->phpExcel->setActiveSheetIndex(0);

        if(!empty($schemaSheets))
        {
            foreach($schemaSheets as $schemaSheet)
            {
                $schemaSheetRows = $schemaSheet->getHighestRow();
                if($schemaSheetRows > 1) $this->phpExcel->addSheet($schemaSheet, $schemaSheetIndex);
            }
        }

        /* urlencode the filename for ie. */
        $fileName = $excelData->fileName;
        if(strpos($this->server->http_user_agent, 'MSIE') !== false || strpos($this->server->http_user_agent, 'Trident') !== false) $fileName = urlencode($fileName);

        $writer      = $fileType == 'xls' ? 'Excel5' : 'Excel2007';
        $excelWriter = PHPExcel_IOFactory::createWriter($this->phpExcel, $writer);
        $excelWriter->setPreCalculateFormulas(false);
        if($savePath == '')
        {
            /* Clean the ob content to make sure no space or utf-8 bom output. */
            $obLevel = ob_get_level();
            for($i = 0; $i < $obLevel; $i++) ob_end_clean();

            setcookie('downloading', 1);
            header('Content-Type: application/vnd.ms-excel');
            header("Content-Disposition: attachment;filename=\"{$fileName}.{$fileType}\"");
            header('Cache-Control: max-age=0');

            $excelWriter->save('php://output');
        }
        else
        {
            $excelWriter->save($savePath);
        }
        exit;
    }

    /**
     * Set excel filed name.
     *
     * @param  int    $colIndex
     * @param  string $letter
     * @access public
     * @return string
     */
    public function setExcelField($colIndex, $letter = 'A')
    {
        for($i = 1; $i <= $colIndex; $i++) $letter++;
        return $letter;
    }

    /**
     * Write SysData sheet in xls.
     *
     * @access public
     * @return void
     */
    public function writeSysData()
    {
        if(!isset($this->rawExcelData->sysDataList)) return true;

        $this->hasSysData = true;

        $sheetIndex = $this->phpExcel->getSheetCount() - 1;
        $this->phpExcel->getSheet($sheetIndex)->setTitle($this->lang->excel->title->sysValue);

        foreach($this->rawExcelData->sysDataList as $field)
        {
            $column  = $this->setExcelField($this->sysDataColIndex);
            $dataKey = $field . 'List';
            if(!isset($this->rawExcelData->$dataKey)) continue;

            $row = 1;
            foreach($this->rawExcelData->$dataKey as $value)
            {
                $this->phpExcel->getSheet($sheetIndex)->setCellValueExplicit("$column$row", $value, PHPExcel_Cell_DataType::TYPE_STRING);
                $row++;
            }
            $this->sysDataColIndex++;
        }
    }

    /**
     * Build dropmenu list.
     * For a tip , if you want to modify that function , search "phpExcel DataValidation namedRange" in stackoverflow.com maybe helpful.
     *
     * @param  int    $excelSheet
     * @param  int    $field
     * @param  int    $row
     * @access public
     * @return void
     */
    public function buildList($excelSheet, $field, $row)
    {
        $listName = $field . 'List';
        $colIndex = array_search($field, $this->rawExcelData->sysDataList);
        $column   = $this->setExcelField($colIndex);
        if(isset($this->rawExcelData->$listName))
        {
            $itemCount = count($this->rawExcelData->$listName);
            if($itemCount == 0) $itemCount = 1;
            $range = "{$this->lang->excel->title->sysValue}!\${$column}\$1:\${$column}\$" . $itemCount;
        }
        else
        {
            $range = is_array($this->rawExcelData->$listName) ? '' : '"' . $this->rawExcelData->$listName . '"';
        }
        $objValidation = $excelSheet->getCell($this->excelKey[$field] . $row)->getDataValidation();
        $objValidation->setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            ->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
            ->setAllowBlank(false)
            ->setShowErrorMessage(false)
            ->setShowDropDown(true)
            ->setErrorTitle($this->lang->excel->error->title)
            ->setError($this->lang->excel->error->info)
            ->setFormula1($range);
    }

    /**
     * Set excel style.
     *
     * @param  object $excelSheet
     * @param  int    $endRow
     * @access public
     * @return void
     */
    public function setStyle($excelSheet, $endRow)
    {
        $startRow  = $this->headerRowCount + 1;
        $endColumn = $this->setExcelField(count($this->excelKey) - 1);

        if(isset($this->rawExcelData->help) and isset($this->rawExcelData->extraNum)) $endRow--;

        /* Freeze column.*/
        if(isset($this->config->excel->freeze->{$this->rawExcelData->kind}))
        {
            $column = $this->excelKey[$this->config->excel->freeze->{$this->rawExcelData->kind}];
            $column++;
            $excelSheet->FreezePane($column . $startRow);
        }

        /* Set row height. */
        $excelSheet->getRowDimension(1)->setRowHeight(20);

        /* Set header style for this table.*/
        $headerStyle = array(
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('rgb' => '808080')
                )
            ),
            'font' => array(
                'bold'  => true,
                'color' => array('rgb' => 'ffffff')
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
            ),
            'fill' => array(
                 'type'       => PHPExcel_Style_Fill::FILL_SOLID,
                 'startcolor' => array('rgb' => '343399')
            )
        );
        if(isset($this->rawExcelData->nocolor))
        {
            $headerStyle['font']['color']['rgb']      = '000000';
            $headerStyle['fill']['startcolor']['rgb'] = 'ffffff';
        }
        $this->setAreaStyle($excelSheet, $headerStyle, 'A1:' . $endColumn . $this->headerRowCount);

        /* Set content style for this table.*/
        $contentStyle = array(
            'font'    => array(
                'size' => 9
            ),
            'alignment' => array(
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                'wrap'     => true
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('rgb' => '808080')
                )
            )
        );
        $this->setAreaStyle($excelSheet, $contentStyle, 'A' . $startRow . ':' . $endColumn . $endRow);

        /* Set column width and style. */
        $customWidth = isset($this->rawExcelData->customWidth) ? array_keys($this->rawExcelData->customWidth) : array();
        foreach($this->excelKey as $key => $letter)
        {
            $titleWidth = $this->config->excel->width->title;
            $contWidth  = $this->config->excel->width->content;

            /* Set width of date fields. */
            if(strpos($key, 'Date') !== false) $excelSheet->getColumnDimension($letter)->setWidth(12);

            /* Set width of title fields. */
            if(in_array($key, $this->config->excel->titleFields)) $excelSheet->getColumnDimension($letter)->setWidth($titleWidth);

            /* Set width of editor fields. */
            if(isset($this->config->excel->editor[$this->rawExcelData->kind]) and in_array($key, $this->config->excel->editor[$this->rawExcelData->kind]))
            {
                $excelSheet->getColumnDimension($letter)->setWidth($contWidth);
            }

            /* Set cell format of center fields. */
            if(in_array($key, $this->config->excel->centerFields))
            {
                $centerStyle = array(
                    'font'    => array(
                        'size' => 9
                    ),
                    'alignment' => array(
                        'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                    ),
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN,
                            'color' => array('rgb' => '808080')
                        )
                    )
                );
                $this->setAreaStyle($excelSheet, $centerStyle, $letter . $startRow . ':' . $letter . $endRow);
            }

            /* Set cell format of date fields. */
            if(strpos($key, 'Date') !== false or in_array($key, $this->config->excel->dateFields))
            {
                $dateFormat = array(
                    'font'    => array(
                        'size' => 9
                    ),
                    'alignment' => array(
                        'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        'wrap'     => true
                    ),
                    'numberformat' => array(
                        'code' => PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
                    ),
                    'borders' => array(
                        'allborders' => array(
                            'style' => PHPExcel_Style_Border::BORDER_THIN,
                            'color' => array('rgb' => '808080')
                        )
                    )
                );
                $this->setAreaStyle($excelSheet, $dateFormat, $letter . $startRow . ':' . $letter . $endRow);
            }

            /* Set column width by userdefined settings. */
            if(in_array($key, $customWidth)) $excelSheet->getColumnDimension($letter)->setWidth($this->rawExcelData->customWidth[$key]);
        }

        /* Set row color. */
        if(isset($this->rawExcelData->colors))
        {
            foreach($this->rawExcelData->colors as $row => $color)
            {
                $beginColumn = $this->excelKey[$color->begin];
                $endColumn   = $this->excelKey[$color->end];
                $excelSheet->getStyle("$beginColumn$row:$endColumn$row")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $excelSheet->getStyle("$beginColumn$row:$endColumn$row")->getFill()->getStartColor()->setRGB($color->color);
            }
        }
        elseif(!isset($this->rawExcelData->nocolor))
        {
            /* Set interlaced color for this table. */
            for($row = $startRow; $row <= $endRow; $row++)
            {
                $excelSheet->getRowDimension($row)->setRowHeight(20);

                $area  = "A$row:$endColumn$row";
                $color = $row % 2 == 0 ? 'FFB2D7EA' : 'FFdee6fb';
                $excelStyle = $excelSheet->getStyle($area);
                $excelFill  = $excelStyle->getFill();
                $excelFill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $excelFill->getStartColor()->setARGB($color);
            }
        }
    }

    /**
     * Set area style.
     *
     * @param  object $excelSheet
     * @param  array  $style
     * @param  string $area
     * @access public
     * @return void
     */
    public function setAreaStyle($excelSheet, $style, $area)
    {
        $styleObj = new PHPExcel_Style();
        $styleObj->applyFromArray($style);
        $excelSheet->setSharedStyle($styleObj, $area);
    }

    /**
     * Set alignment.
     *
     * @param  object $excelSheet
     * @access public
     * @return bool
     */
    public function setAlignment($excelSheet)
    {
        if(!isset($this->rawExcelData->percentageFields)) return true;

        foreach($this->rawExcelData->percentageFields as $row => $fields)
        {
            foreach($fields as $key => $field)
            {
                if(isset($this->excelKey[$key]))
                {
                    $cell = $this->excelKey[$key] . ($this->headerRowCount + $row + 1);
                    $excelSheet->getStyle($cell)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                }
            }
        }
    }
}