php使用PhpSpreadsheet导出excel

1. 下载安装,

2. 大致包括: 单元格拆分, 单元格合并, 字体样式, 单元内容垂直居中, 边框样式, 单元格宽度设置等; 代码如下所示:

<?php
//引用composer自动加载文件
require $_SERVER['DOCUMENT_ROOT'].'/standard/vendor/autoload.php';
//引用phpspreadsheet类库
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class EXCEL 
{
	public static $excel;
	
    public static function getInstance(){
        if(!isset(self::$excel)){
            self::$excel = new self();
        }
        return self::$excel;
    }
	
    private function __construct(){
		//require './PHPExcel/Classes/PHPExcel.php';
		//require './PHPExcel/Classes/PHPExcel/IOFactory.php';
	}
	
    //禁止外部克隆
    private function __clone(){

    }
    
	//下载
	private function downloadExcel($newExcel, $filename, $format)
	{
		// $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="
            . $filename . date('Ymdhis') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);

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

        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        exit;
	}

	//读取excel, 支持一个excel中多张工作表, 要求格式为第一行名称,第二行开始数据
	public function getExcel($files){
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($files);

        $arr = [];
        //一个excel多个工作表,循环读取
        foreach ($spreadsheet->getWorksheetIterator() as $k=> $worksheet) {
            //工作表的名称
            $worksheetTitle = $worksheet->getTitle();
            $arr[$k]['title']=$worksheetTitle;
            //获取行数,返回的是数字
            $highestRow = $worksheet->getHighestRow();
            //获取列数,返回的是字母
            $highestColumn = $worksheet->getHighestColumn();
            //列数字母索引转数字
            $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
            $data = [];
            for($i=2;$i<=$highestRow;$i++){
                for ($j=1;$j<=$highestColumnIndex;$j++){
                    $data[$i][$j]= $worksheet->getCellByColumnAndRow($j, $i)->getValue();
                }
            }

            $arr[$k]['data']=$data;
        }

        return $arr;
    }


    //项目商务信息导出excel
    public function outputProjectExcel($info){
        $newExcel = new Spreadsheet();//创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();//获取当前操作sheet的对象
        $date = date('Ymd',time());
        $name = '项目商务信息'.$date;
        $objSheet->setTitle($name);//设置当前sheet的标题

        //样式设置 - 合并和拆分
        $objSheet->mergeCells('A1:N1'); //合并单元格
        //$sheet -> unmergeCells('C3:G3'); //拆分单元格
        $objSheet->setCellValue('A1',$name);

        //设置第一栏的中文标题
        $objSheet->setCellValue('A2', '序号')
            ->setCellValue('B2', '项目名称')
            ->setCellValue('C2', '区域')
            ->setCellValue('D2', '类型')
            ->setCellValue('E2', '项目面积')
            ->setCellValue('F2', '安装情况')
            ->setCellValue('G2', '实施公司')
            ->setCellValue('H2', '合同金额')
            ->setCellValue('I2', '评估')
            ->setCellValue('J2', '目标计划')
            ->setCellValue('K2', '项目动态进度')
            ->setCellValue('L2', '联系人')
            ->setCellValue('M2', '商务负责人')
            ->setCellValue('N2', '技术负责人');

        //写入数据
        $dataCount = count($info);
        $k = 2;
        if($dataCount == 0){
            exit;
        }else{
            for ($i=0;$i<$dataCount;$i++){
                $k = $k + 1;
                $order=$i+1;
                $objSheet->setCellValue('A' . $k, $order)
                    ->setCellValue('B' . $k, $info[$i]['project_name'])
                    ->setCellValue('C' . $k, $info[$i]['name'])
                    ->setCellValue('D' . $k, $info[$i]['type_name'])
                    ->setCellValue('E' . $k, $info[$i]['built_area'])
                    ->setCellValue('F' . $k, $info[$i]['installation_progress'])
                    ->setCellValue('G' . $k, $info[$i]['construction_unit'])
                    ->setCellValue('H' . $k, $info[$i]['project_money'])
                    ->setCellValue('I' . $k, $info[$i]['estimate'])
                    ->setCellValue('J' . $k, $info[$i]['target'])
                    ->setCellValue('K' . $k, $info[$i]['trends'])
                    ->setCellValue('L' . $k, $info[$i]['contacts'])
                    ->setCellValue('M' . $k, $info[$i]['business_director'])
                    ->setCellValue('N' . $k, $info[$i]['technical_director']);
            }
        }

        //设定样式
        //所有sheet的表头样式 加粗
        $font = [
            'font' => [
                'bold' => true,
                'size' => 20,
            ],
        ];
        $objSheet->getStyle('A1:N1')->applyFromArray($font);

        //样式设置 - 水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER
            ],
        ];
        $objSheet->getStyle('A1:N2')->applyFromArray($styleArray);

        //所有sheet的内容样式-加黑色边框
        $borders = [
            'borders' => [
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
                'inside' => [
                    'borderStyle' => Border::BORDER_THIN,
                ]
            ],
        ];
        $objSheet->getStyle('A1:N'.$k)->applyFromArray($borders);

        //设置宽度
        $cell = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'];
        foreach($cell as $k=>$v){
            $objSheet->getColumnDimension($v)->setWidth(20);

//            $objSheet->getColumnDimension($v)->setAutoSize(true);
        }

        $this->downloadExcel($newExcel, $name, 'Xls');
    }
	
}

$excel = EXCEL::getInstance(null);
?>