php之Excel导入导出

php之Excel导出

composer安装phpexcel

composer require phpoffice/phpexcel

导出

tp框架 直接引用

use PHPExcel_IOFactory;
use PHPExcel;

调用方法

public function index($data=[],$name = ‘excel’)
{

    $data['0']['order_id']=123;
    $data['0']['order_status']=123;
    $data['0']['name']=123;
    $data['0']['order_name']=123;
    $data['0']['create_time']=123;
    $data['0']['remark']=123;
    $data['0']['poper_size']=123;
    $data['0']['color_num']=123;
    $data['0']['color_type']=123;
    $data['0']['print_type']=123;
    $data['0']['final_dimension']=123;
    $data['0']['print_num']=123;
    $data['0']['prints_num']=123;


     $excel = new \PHPExcel(); //引用phpexcel
    iconv('UTF-8', 'gb2312', $name); //针对中文名转码
    $header = ['订单编号', '订单状态', '客户名字', '订单名字', '创建时间','订单备注','开料尺寸','印件色数','印件色别','印刷方式','成品尺寸','印刷板数','千印数量']; //表头,名称可自定义
    $excel->setActiveSheetIndex(0);
    $excel->getActiveSheet()->setTitle($name); //设置表名
    $excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
    $excel->getActiveSheet()->getColumnDimension('B')->setWidth(80);

    $letter = ['A', 'B', 'C', 'D', 'E','F','G','H','I','J','K','L','M'];//列坐标

    //设置表头
    $excel->setActiveSheetIndex(0)
        ->setCellValue('A1', '订单编号')
        ->setCellValue('B1', '订单状态')
        ->setCellValue('C1', '客户名字')
        ->setCellValue('D1', '订单名字')
        ->setCellValue('E1', '创建时间')
        ->setCellValue('F1', '订单备注')
        ->setCellValue('G1', '开料尺寸')
        ->setCellValue('H1', '印件色数')
        ->setCellValue('I1', '印件色别')
        ->setCellValue('J1', '印刷方式')
        ->setCellValue('K1', '成品尺寸')
        ->setCellValue('L1', '印刷板数')
        ->setCellValue('M1', '千印数量');
    
    //写入数据
    foreach ($data as $k => $v) {
        //从第二行开始写入数据（第一行为表头）
        //$excel->getActiveSheet()->setCellValue('A'.($k+2),$v['gid']);
        $excel->getActiveSheet()->setCellValue('A' . ($k + 2), $v['order_id']."\t");
        $excel->getActiveSheet()->setCellValue('B' . ($k + 2), $v['order_status']);
        $excel->getActiveSheet()->setCellValue('C' . ($k + 2), $v['name']);
        $excel->getActiveSheet()->setCellValue('D' . ($k + 2), $v['order_name']);
        $excel->getActiveSheet()->setCellValue('E' . ($k + 2), $v['create_time']);
        $excel->getActiveSheet()->setCellValue('F' . ($k + 2), $v['remark']);
        $excel->getActiveSheet()->setCellValue('G' . ($k + 2), $v['poper_size']);
        $excel->getActiveSheet()->setCellValue('H' . ($k + 2), $v['color_num']);
        $excel->getActiveSheet()->setCellValue('I' . ($k + 2), $v['color_type']);
        $excel->getActiveSheet()->setCellValue('J' . ($k + 2), $v['print_type']);
        $excel->getActiveSheet()->setCellValue('K' . ($k + 2), $v['final_dimension']);
        $excel->getActiveSheet()->setCellValue('L' . ($k + 2), $v['print_num']);
        $excel->getActiveSheet()->setCellValue('M' . ($k + 2), $v['prints_num']);
    }

    //设置单元格边框
    $excel->getActiveSheet()->getStyle("A1:E" . (count($data) + 1))->getBorders()->getAllBorders()->setBorderStyle();

    ob_end_clean();
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
    header('Cache-Control: max-age=0');


    $res_excel = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    $res_excel->save('php://output');
}

导入
(一)

	use PHPExcel_IOFactory;        引入文件
	
    $excelio=PHPExcel_IOFactory::load($_FILES['excel']['tmp_name']); //第一个参数是表单提交的name,第二个是临时名
    $sheetcount=$excelio->getSheetCount();
    $datas=$excelio->getSheet(0)->toArray(); // 获取第一个sheet数据
    unset($datas[0]);  // 删除表头名字，
    echo "<pre>";
    print_r($datas);

(二)

  use PHPExcel_IOFactory;    引入文件
  
  $filename = './storage/chuli/某某.xlsx';       //默认路径public
  if(file_exists($filename)) 
  { 
 	$objPHPExcel = PHPExcel_IOFactory::load($filename); 
 	$importData = $objPHPExcel->getSheet(0)->toArray();
 	unset($importData[0]); 
  	if(!empty($importData))
  	{ 
   		 $dataAll = []; // print_r($importData);die();
	}