PHP脚本自动生成Excel方法,从Excel读取数据
2018-06-22| 程成| 1135| 1| PHP技术

一、Composer 安装组件


在 require 中添加 Excel 组件


"require":   {
            "moonlandsoft/yii2-phpexcel": "*"
            },



二、Excel导入数据


//文件路径
        $fileName = './commands/Excel/622.xls';
        //读取 Excel
        $data_lines = Excel::import($fileName, [
            'setFirstRecordAsKeys' => true,
            'setIndexSheetByName' => true,
            'getOnlySheet' => 'Sheet1',
        ]);



三、Excel导出数据


public function actionIndex(){
        $lists = [
            ['name'=>'zhuhui','age'=>12,'greed'=>'121'],
            ['name'=>'zhangsan','age'=>15,'greed'=>'eqweqw'],
            ['name'=>'lisi','age'=>20,'greed'=>'657867867'],
        ];
        $filename = 'export_mall.xlsx';
        $this->excelExport($filename,$lists,"mall_count");
    }
    
    public function excelExport($filename,$data,$sheet){
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setCreator("E421083458");
        $objPHPExcel->getProperties()->setLastModifiedBy("E421083458");
        $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
        $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
        $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
        $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
        $objPHPExcel->getProperties()->setCategory("Test result file");
    
        // Add some data
        $objPHPExcel->setActiveSheetIndex(0);
        $charlist = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
        foreach($data as $key=>$value){
            $j=0;
            if($key==0){
                foreach($value as $k=>$v){
                    if($j<25){
                        $objPHPExcel->getActiveSheet()->setCellValue($charlist[$j].($key+1), mb_convert_encoding($k, "UTF-8", "GBK"));
                        $j++;
                    }
                }
                $j=0;
            }
            foreach($value as $k=>$v){
                if($j<25){
                    $objPHPExcel->getActiveSheet()->setCellValue($charlist[$j].($key+2), mb_convert_encoding($v, "UTF-8", "GBK"));
                    $j++;
                }
            }
        }
    
        // Rename sheet
        $objPHPExcel->getActiveSheet()->setTitle(mb_convert_encoding($sheet, "UTF-8", "GBK"));
    
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);
    
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($filename);
    }



成功生成截图:


image.png



通过以上方法,你可以实现脚本生成Excel,从Excel读取数据。




×
作者:程成
QQ:492245711