PHP基础巩固【PHP导入导出EXCEl，TP实用封装函数，可定义表格样式】

这个方法是自己封装了，方法确实也好用，自己也用了好几年了

但是最近发现好多小年轻用到都是PhpOffice，咱也应该与时俱进，就先把这方法分享出来，等熟悉下phpoffice再贴出更新后的版本

把以下代码写入公共函数

/**
 * 导入EXCEL
 * @param file 需导入的文件
 */
function GetExcel($file){
    ini_set('max_execution_time', '0');
    if(empty($file) or !file_exists($file)){return false;}
    import('phpexcel.PHPExcel', EXTEND_PATH);
    $objPHPExcel = new \PHPExcel();      
    $type = pathinfo($file); 
    $type = strtolower($type["extension"]);   
    $type = $type==='csv' ? $type : 'Excel2007';     
    $PHPReader = new \PHPExcel_Reader_Excel2007();
    $objPHPExcel = $PHPReader->load($file);    
    $sheet = $objPHPExcel->getSheet(0); 
    $allColumn = $sheet->getHighestColumn();   
    $allRow = $sheet->getHighestRow();      
    $ColumnNum = PHPExcel_Cell::columnIndexFromString($allColumn);    
    $data = array();
    for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){     
        for($colIndex=0;$colIndex<=$ColumnNum;$colIndex++){
            $data[$rowIndex][] =(string)$sheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();  
        }
    }
    return $data; 
}


/**
 * 导出EXCEL
 * @param titary 标题
 * @param list 数据
 * @param styleary 单元格长度
 */
function Toexcel($titary, $list, $styleary = array())
{
    $charary = array(0 => "A", 1 => "B", 2 => "C", 3 => "D", 4 => "E", 5 => "F", 6 => "G", 7 => "H", 8 => "I", 9 => "J", 10 => "K", 11 => "L", 12 => "M", 13 => "N", 14 => "O", 15 => "P", 16 => "Q", 17 => "R", 18 => "S", 19 => "T", 20 => "U", 21 => "V", 22 => "W", 23 => "X", 24 => "Y", 25 => "Z");
    import('phpexcel.PHPExcel', EXTEND_PATH);
    $objPHPExcel  = new \PHPExcel();
    $sharedStyle1 = new \PHPExcel_Style();
    $borders      = array(
        'left'   => array('style' => \PHPExcel_Style_Border::BORDER_MEDIUM),
        'right'  => array('style' => \PHPExcel_Style_Border::BORDER_MEDIUM),
        'top'    => array('style' => \PHPExcel_Style_Border::BORDER_MEDIUM),
        'bottom' => array('style' => \PHPExcel_Style_Border::BORDER_MEDIUM),
    );
    $sharedStyle1->applyFromArray(
        array(
            'font'    => array(
                'bold'  => true,
                'type'  => \PHPExcel_Style_Color::COLOR_DARKGREEN,
                'color' => array(
                    'argb' => '00000000'
                )
            ),
            'borders' => $borders,
            'fill'    => array(
                'type'       => \PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array(
                    'argb' => '00CCCCCC'
                )
            )
        )
    );
    $sharedStyle40 = new \PHPExcel_Style();
    $sharedStyle40->applyFromArray(
        array(
            'font'    => array(
                'type'  => \PHPExcel_Style_Color::COLOR_DARKGREEN,
                'color' => array(
                    'argb' => '00000000'
                )
            ),
            'borders' => $borders
        )
    );

    $lastitem = $charary[count($list["tit"]) - 1];
    $item     = 1;
    foreach ($titary as $key => $val) {
        $objPHPExcel->getActiveSheet()->getRowDimension($item)->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $item, $val);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->mergeCells('A' . $item . ':' . $lastitem . '' . $item);
        $item++;
    }

    $objPHPExcel->getActiveSheet()->getDefaultColumnDimension('A')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getRowDimension($item)->setRowHeight(30);

    $tempary = [];
    foreach ($list["tit"] as $k => $v) {
        $tempary[] = $k;
    }
    foreach ($tempary as $k => $v) {
        if (isset($styleary[$v])) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($charary[$k])->setWidth($styleary[$v]);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit($charary[$k] . $item, $list["tit"][$v], \PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, $charary[$k] . $item);
    }
    unset($list["tit"]);
    $item++;
    foreach ($list as $key => $row) {
        $objPHPExcel->getActiveSheet()->getRowDimension($item)->setRowHeight(30);
        foreach ($tempary as $k => $v) {
            $objPHPExcel->getActiveSheet()->setCellValueExplicit($charary[$k] . $item, $row[$v], \PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle40, $charary[$k] . $item);
        }
        $item++;
    }
    // $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:".$lastitem.($item-1));
    $objPHPExcel->setActiveSheetIndex(0)->getStyle("A1:" . $lastitem . ($item - 1))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->setActiveSheetIndex(0)->getStyle("A1:" . $lastitem . ($item - 1))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    $file_name = date('YmdHis') . '.xls';
    ob_end_clean();
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:applicationnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment; filename=" . $file_name);
    header("Content-Transfer-Encoding:binary");
    $objWriter->save('php://output');
    exit;
}

导出的可能有些朋友没看明白，这里贴出调用代码

$titary      = array("导出标题");
$list["tit"] = array(
    "name"     => "分组1",
    "number"   => "数量",
);
$styleary = array(
    "name" => 20
);
Toexcel($titary,$list,$styleary);