phpexcel 导入/导出文件

 composer下载

composer require phpoffice/phpexcel -vvv

public input_excel(){ 
    vendor("PHPExcel.PHPExcel"); //获取PHPExcel类
        $obj_PHPExcel = new \PHPExcel();
        //$_FILES["excel"]s上传上来的文件,可以不用移动文件,直接使用临时文件.
        if(isset($_FILES["excel"])){
            if($_FILES["excel"]['tmp_name']){
                $objReader =\PHPExcel_IOFactory::createReaderForFile($_FILES["excel"]['tmp_name']);
                $objReader->setReadDataOnly(true);//这段其实也可以不要
                $obj_PHPExcel =\PHPExcel_IOFactory::load($_FILES["excel"]['tmp_name']);
//这里是加载文件,千万要注意你导入文件的格式不是文件后缀是文件格式,笔者在这个坑上待了很久,
//$fileType=PHPExcel_IOFactory::identify($filename);上面load()500了可以用着个来校验上传的文件格式如果是html格式你就要注意了.
                $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
                unset($excel_array[0]);
                dump($excel_array);//到这剩下的就交给你自己了
            }
}

 

以上为导入

以下为导出

public function output_excel(){
            //phpexcel
            vendor("PHPExcel.PHPExcel"); //获取PHPExcel类
            $lists = Db::name('orders')->select();
            $objectPHPExcel = new \PHPExcel();
            $objectPHPExcel->setActiveSheetIndex(0);
            $current_page = 0;
            $n = 0;
            foreach ( $lists as $k=>$v )
            {
                $current_page = $current_page +1;
                //表格头的输出
                $objectPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','订单编号');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B1','商品名称');
                $objectPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
                $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('C1','商品型号');
                
                $objectPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objectPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objectPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
               
                $objectPHPExcel->getActiveSheet()->setCellValue('A'.($n+2) ,$v['order_sn']);
                $objectPHPExcel->getActiveSheet()->setCellValue('B'.($n+2) ,$v['goods_name']);
                $objectPHPExcel->getActiveSheet()->setCellValue('C'.($n+2) ,$v['goods_name_en']);
                //设置边框
                $n = $n +1;
            }
            //设置分页显示
            //$objectPHPExcel->getActiveSheet()->setBreak( 'I55' , PHPExcel_Worksheet::BREAK_ROW );
            //$objectPHPExcel->getActiveSheet()->setBreak( 'I10' , PHPExcel_Worksheet::BREAK_COLUMN );
            $objectPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
            $objectPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);
            ob_end_clean();
            ob_start();
            $file_name = date('Y-m-d_His').'.xls';
            header('Content-Disposition: attachment;filename='.$file_name);
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $objWriter= \PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel2007');
            $objWriter->save('php://output');
            exit;
       }