PHPExcel

php使用phpexcel插件实现导入导入道出功能
首先下载phpexcel插件下载结果如下:

image.png
然后在功能模块从PHPExcel目录导PHPExcel.php类文件实例化excel读取类
导入功能:
//判断导入文件类别
if ($type=="xlsx" || $type=="csv")
{
import("Vendor.PHPExcel"); //从PHPExcel目录导PHPExcel.php类文件
$PHPExcel=new \PHPExcel();
import("Vendor.PHPExcel.Reader.Excel2007");
//实例化excel读取类
$PHPReader = new \PHPExcel_Reader_Excel2007();
//若读取失败,则实例化excel5读取类
if(!$PHPReader->canRead($path)){
$PHPReader = new \PHPExcel_Reader_Excel5();
}
// $reader = \PHPExcel_IOFactory::createReader('Excel2007');
$PHPExcel = $PHPReader->load($path); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
$dataFile["num"]=intval($highestRow-1); //添加数据条数
$id=M("data_file")->add($dataFile);

          /** 循环读取并插入每个单元格的数据 */      
         if ($highestRow > 1) 
         {       
             //获取输入的字段  表的第一行
             for ($row = 1; $row <= 1 ; $row++)
             {          
                 for ($column = 'A'; $column <=  $highestColumm; $column++) 
                 {   
                    $string = $sheet->getCell($column.$row)->getValue();
                    $data[$column ] = $string;//单元格值放进数组    
                 }      
             }
             //获取输入信息    表的第二行以后数据
             for ($row = 2; $row <= $highestRow ; $row++)
             {
                 for ($column = 'A'; $column <=  $highestColumm; $column++) 
                 {   
                     $sheet_value = $sheet->getCell($column.$row)->getValue()?$sheet->getCell($column.$row)->getValue():"null";
                     $dataset[$data[$column]] =$sheet_value;//单元格值放进数组
                 }
                 $dataset["uid"]=$uid;  //导入这些数据用户的id
                 $dataset["fid"]=$id;   //数据文件id
                 $dataset["time"]=time();
                 //$array[$dataset["phone"]]=$dataset;
                 $array[]=$dataset;
              }  
             
         }else{
            return 2;
         }
   
    }
    
  // 导入txt文件 
    if ($type=="txt") 
    {      
       $f= fopen($path,"r");
       $num=0;
       while (!feof($f))
       {
         $num++;
         $line = fgets($f);
         $array[$num]["phone"]=substr(htmlspecialchars($line),0,11);
         $array[$num]["uid"]=$uid;  //导入这些数据用户的id        
       }          
       $len=count($array);
       $dataFile["num"]=$len;  //添加数据条数
       $id=M("data_file")->add($dataFile); 
       foreach ($array as $key => $value)
       {  
          $value["time"]=time();
          $array[$key]=$value;  
       }
      
    }
   $array=array_merge($array);             
   $data=array_chunk($array,2000);
   $count_data=count($data);

    $data_list = M('data_list');
    
    $i=0;
    //插入数据到数据库
    while ($i<$count_data) {
       $users=$data_list->addAll($data[$i]);
       $i++;
    }
    unlink($path);
   return 1;

注意:导入时文件路径要是真实路径临时路径会出问题

phpexcel导出
if ($types=='xls' || $types=='xlsx' || $types=='csv' || $types == 'txt')
{
import("Vendor.PHPExcel"); //从PHPExcel目录导PHPExcel.php类文件
$excel = new \PHPExcel();

        //Excel表格式,这里简略写了9列  
        $letter = array('A','B','C',"D","E","F","G","H","I");  
        //表头数组  
        $tableheader = array('1','2'....);  
        
        $count= count($data);//总的数据行数
        $listNum = 100;//每个sheet页最大数据行数  
        $num = ceil($count/$listNum);//sheet页个数  
        $MuitData =  array_chunk($data,$listNum,false);//分割总的数据，每页最多$listNum行有效数据  
        //缺省情况下,PHPExcel会自动创建第一个SHEET,其索引SheetIndex=0  
        //设置 当前处于活动状态的SHEET 为PHPExcel自动创建的第一个SHEET   
        $excel->setActiveSheetIndex(0); //objPHPExcel  
        //设置sheet的title  
        $excel->getActiveSheet()->setTitle($titles.'第1'.'页');        
        //设置sheet的列名称  
        for($k = 0; $k < count($tableheader); ++$k) 
        {  
            $excel->getActiveSheet()->setCellValue("$letter[$k]".'1',"$tableheader[$k]");//第一行数据  

        }  
        //填充表格信息 处理第1块数据  
        $crrntSheetLineNo = count($MuitData[0]) + 1;  
        for ( $j = 2; $j <= $crrntSheetLineNo; ++$j) 
        { 
          //遍历每一行  
            $k = 0;  
            foreach ( $MuitData[0][$j - 2] as $key => $value ) 
            {
        //遍历具体行的某一列  
                $excel->getActiveSheet()->setCellValue("$letter[$k]".$j,"$value");//第$k列 第$j行  
                $k++;  
            }
            S('PROCESS_CSC',$j-1);//进度条记录当前执行条数

        }  
        //后续的sheet页及数据块  
        for ( $i = 1; $i < $num; ++$i) 
        {  
            //创建第$i个sheet  
            $msgWorkSheet = new \PHPExcel_Worksheet($excel, $titles."第".($i + 1)."页"); //创建一个工作表  
            $excel->addSheet($msgWorkSheet); //插入工作表  
            $excel->setActiveSheetIndex($i); //切换到新创建的工作表  
  
            //设置sheet的列名称  
            for($k = 0; $k < count($tableheader); ++$k) 
            {  
                $excel->getActiveSheet()->setCellValue("$letter[$k]1","$tableheader[$k]");//第一行数据  

            }   
            //填充表格信息 处理第$i块数据  
            $crrntSheetLineNo = count($MuitData[$i]) + 1; 
            for ( $j = 2; $j <= $crrntSheetLineNo; ++$j) 
            { //遍历每一行  
                $k = 0;            
                foreach ( $MuitData[$i][$j - 2] as $key => $value ) 
                {//遍历具体行的某一列  
                      $excel->getActiveSheet()->setCellValue("$letter[$k]$j","$value");//第$k列 第$j行  
                      ++$k;

                } 
                S('PROCESS_CSC',$j-1);
             
            }  
             usleep(100);  
        }
        //创建Excel输出对象  
        $filename = $titles.".".$types;  
       $write = new \PHPExcel_Writer_Excel5($excel);  
        ob_end_clean();//清除缓冲区,避免乱码     
    } 
    //header("Content-type: application/octet-stream;charset=utf-8");
    //输出到浏览器  
    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:application/vnd.ms-execl");  
    header("Content-Type:application/download");               
    header('Content-Type:application/octet-stream');  
    $encoded_filename = urlencode($filename);  
    $encoded_filename = str_replace("+", "%20", $encoded_filename);  
    $ua = $_SERVER["HTTP_USER_AGENT"];    
    if (preg_match("/MSIE/", $ua)) {  
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');  
    } else if (preg_match("/Firefox/", $ua)) {  
        header('Content-Disposition: attachment; filename*="utf-8\'\'' . $filename . '"');  
    } else {  
        header('Content-Disposition: attachment; filename="' . $filename . '"');  
    }   
    header("Content-Transfer-Encoding:binary"); 
    ob_clean();     
    $write->save('php://output'); 
    return 1;

简单导出:
public function holeExport()
{
$str = "时间,标题,分类,展现量,点击数,排序\n";
$str = iconv('utf-8', 'gb2312', $str);
foreach ($data as $item) {
$a = iconv('utf-8', 'gb2312', date('Y-m-d' ,$start_time_data));
$b = iconv('utf-8', 'gb2312', $item['title']);
$c = iconv('utf-8', 'gb2312', $item['menu_name'].'('.$item['remarks'].')');
$d = iconv('utf-8', 'gb2312', $item['count_show']);
$t = iconv('utf-8', 'gb2312', $item['count_click']);
$f = iconv('utf-8', 'gb2312', $item['sort']);
$str .= $a . "," . $b . "," . $c . "," . $d . "," . $t . "," . $f . "\n";
}
$filename = mt_rand(0000,9999).'.csv';
$this->export_csv($filename,$str);
}
//输出
public function export_csv($filename, $data){
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:application/vnd.ms-execl");
header("Content-Type:application/download");
header('Content-Type:application/octet-stream');
$encoded_filename = urlencode($filename);
$encoded_filename = str_replace("+", "%20", $encoded_filename);
$ua = $_SERVER["HTTP_USER_AGENT"];
if (preg_match("/MSIE/", $ua)) {
header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
} else if (preg_match("/Firefox/", $ua)) {
header('Content-Disposition: attachment; filename*="utf-8''' . $filename . '"');
} else {
header('Content-Disposition: attachment; filename="' . $filename . '"');
}
header("Content-Transfer-Encoding:binary");
echo $data;
}