<?php
namespace lib;
/**
 * exportXlsxDll
 * https://xlswriter-docs.viest.me/zh-cn/kuai-su-shang-shou/chuang-jian-wen-jian
 * @author HzqGhost admin@whiledo.com QQ:313143468
 * @version 1.2.0
 *
 */
class exportXlsxDll
{
    protected $br = "\r\n"; //换行符
    protected $isoutput; //是否输出到客户端
    protected $sheets = [];
    protected $rowI;
    protected $excel;
    protected $xlsfile;
    protected $dir;
    protected $filename;
    protected $fileHandle;
    protected $headerStyle;
    protected $wrapStyle;
    protected $maxColLabel = 'A';
    protected $hasHeader = false;
	public function __construct(string $dir,string $filename)
    {
        $this->dir = $dir;
        $this->filename = basename($filename);
    }
	public function header(int $filezie=0, string $filename='')
    {
        $this->isoutput = true;
        if($filename==''){
            $this->filename;
        }
        ob_end_clean();
        header('Content-Description: File Transfer');
        header("Content-type:application/vnd.ms-excel");
        header('Content-Disposition: attachment; filename='.$filename);
        ob_start();
    }

	public function bookBegin()
    {
        $this->rowI = 0;
        $this->excel  = new \Vtiful\Kernel\Excel(['path' => $this->dir]);
    }
	public function bookEnd($isDelete=false)
    {
        $this->xlsfile->output();
        $this->excel->close();
        if($this->isoutput){
            unlink($this->dir.'/'.$this->filename);
        }
    }
    
    public function formatHeaderRow(array $HeaderFields){
        $data = [];
        foreach($HeaderFields as $k=>$v){
            $data[$k] = $v[0];
        }
        return $data;
    }
    public function formatColumnStyle(array $HeaderFields){
        $data = [];
        foreach($HeaderFields as $k=>$v){
            $data[$k] = $v[1];
        }
        return $data;
    }

	public function sheetBegin(string $sheetName,array $headerRow=[],array $columnStyle=[],bool $autoFilter=false)
    {
        //$sheetName = str_replace(['/','\\',':','?','*','[',']','"',"'"],' ',$sheetName);
        $sheetName = strtr($sheetName,[
			'/'=>'-',
			'\\'=>'-',
			'?'=>'-',
			'*'=>'-',
			"'"=>'-',
			'"'=>'-',
			'['=>'-',
			']'=>'-',
		]);
        $sheetName = mb_substr($sheetName,0,20);
        $this->rowI = 0;
        $this->maxColLabel = 'A';
        $this->hasHeader = false;
        if(empty($this->sheets)){
            $this->sheets[] = $sheetName;
            $this->xlsfile = $this->excel->fileName($this->filename,$sheetName);
            //$this->xlsfile = $this->excel->constMemory($this->filename,$sheetName,false);
            $this->fileHandle = $this->xlsfile->getHandle();
            $format = new \Vtiful\Kernel\Format($this->fileHandle);
            //头样式
            $this->headerStyle = $format->bold()
                        ->background(0x002F5F)
                        ->fontColor(0xFFFFFF)
                        ->toResource();
            //自动换行样式           
            $format = new \Vtiful\Kernel\Format($this->fileHandle);
            $this->wrapStyle = $format
                ->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)
                ->wrap()
                ->toResource(); 
            //垂直居中对齐
            $format = new \Vtiful\Kernel\Format($this->fileHandle);
            $this->vCenterStyle = $format
                ->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)
                ->toResource();            
        }else{
            $this->sheets[] = $sheetName;
            $this->excel->addSheet($sheetName);
        }
        if($headerRow){
            $this->hasHeader = true;
            $this->excel->checkoutSheet($this->sheets[count($this->sheets)-1]);
            // $this->excel->autoFilter('A0:'.$this->int2Excel(count($headerRow)-1).'0');
            //$this->xlsfile->setRow('A1', 16, $this->headerStyle); // 写入数据前设置行样式(范围,高度,样式)
            $colI=0;
            $data=[];
            foreach($headerRow as $k=>$cell){
                if($columnStyle && array_key_exists($k,$columnStyle)){
                    $width = $columnStyle[$k]['width'];
                    $wrap = $columnStyle[$k]['wrap'] ?? false;
                    if($wrap){
                        $this->xlsfile->setColumn($this->int2Excel($colI).':'.$this->int2Excel($colI), $width, $this->wrapStyle);
                    }else{
                        $this->xlsfile->setColumn($this->int2Excel($colI).':'.$this->int2Excel($colI), $width, $this->vCenterStyle);
                    }
                }else{
                    $this->xlsfile->setColumn($this->int2Excel($colI).':'.$this->int2Excel($colI), 20, $this->vCenterStyle);
                }
                $data[] = $cell;
                $colI++;
            }
            $this->xlsfile->header($data);
            $this->rowI++;
        }
    }

	public function sheetEnd()
    {
        ;
    }

	public function append(array $row=[])
    {
        if($row){
            $colI=0;
            foreach($row as $k=>$cell){
                $cell = $this->specialchars($cell);
                $this->xlsfile->insertText($this->rowI, $colI, $cell);
                $colI++;
            }
            if($this->maxColLabel=='A'){
                $this->maxColLabel = $this->int2Excel($colI);
            }
            $this->rowI++;
        }
        if($this->isoutput){
            ob_flush();
            flush();
        }
    }

    protected function specialchars($value){
        //$value = is_null($value) ? '' : $value;
        //$value = str_replace('&amp;','&',htmlspecialchars($value));
        return $value;
    }
    
    //数字转字母列
    protected function int2Excel($num)
    {
    	$az = 26;
    	$m = (int)($num % $az);
    	$q = (int)($num / $az);
    	$letter = chr(ord('A') + $m);
    	if ($q > 0) {
    		return int2Excel($q - 1) . $letter;
    	}
    	return $letter;
    }
    
    public function formatFilename($filename)
    {
    	return str_replace(['/','\\',':','?','*','[',']','"',"'","|","<",">"],'_',$filename);
    }
}