php使用phpexcel插件實(shí)現(xiàn)導(dǎo)入導(dǎo)入道出功能
首先下載phpexcel插件下載結(jié)果如下:

image.png

          /** 循環(huán)讀取并插入每個(gè)單元格的數(shù)據(jù) */      
         if ($highestRow > 1) 
         {       
             //獲取輸入的字段  表的第一行
             for ($row = 1; $row <= 1 ; $row++)
             {          
                 for ($column = 'A'; $column <=  $highestColumm; $column++) 
                 {   
                    $string = $sheet->getCell($column.$row)->getValue();
                    $data[$column ] = $string;//單元格值放進(jìn)數(shù)組    
                 }      
             }
             //獲取輸入信息    表的第二行以后數(shù)據(jù)
             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;//單元格值放進(jìn)數(shù)組
                 }
                 $dataset["uid"]=$uid;  //導(dǎo)入這些數(shù)據(jù)用戶的id
                 $dataset["fid"]=$id;   //數(shù)據(jù)文件id
                 $dataset["time"]=time();
                 //$array[$dataset["phone"]]=$dataset;
                 $array[]=$dataset;
              }  
             
         }else{
            return 2;
         }
   
    }
    
  // 導(dǎo)入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;  //導(dǎo)入這些數(shù)據(jù)用戶的id        
       }          
       $len=count($array);
       $dataFile["num"]=$len;  //添加數(shù)據(jù)條數(shù)
       $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;
    //插入數(shù)據(jù)到數(shù)據(jù)庫(kù)
    while ($i<$count_data) {
       $users=$data_list->addAll($data[$i]);
       $i++;
    }
    unlink($path);
   return 1;

注意:導(dǎo)入時(shí)文件路徑要是真實(shí)路徑臨時(shí)路徑會(huì)出問(wèn)題

phpexcel導(dǎo)出
if ($types=='xls' || $types=='xlsx' || $types=='csv' || $types == 'txt')
{
import("Vendor.PHPExcel"); //從PHPExcel目錄導(dǎo)PHPExcel.php類(lèi)文件
$excel = new \PHPExcel();

        //Excel表格式,這里簡(jiǎn)略寫(xiě)了9列  
        $letter = array('A','B','C',"D","E","F","G","H","I");  
        //表頭數(shù)組  
        $tableheader = array('1','2'....);  
        
        $count= count($data);//總的數(shù)據(jù)行數(shù)
        $listNum = 100;//每個(gè)sheet頁(yè)最大數(shù)據(jù)行數(shù)  
        $num = ceil($count/$listNum);//sheet頁(yè)個(gè)數(shù)  
        $MuitData =  array_chunk($data,$listNum,false);//分割總的數(shù)據(jù)诅炉，每頁(yè)最多$listNum行有效數(shù)據(jù)  
        //缺省情況下,PHPExcel會(huì)自動(dòng)創(chuàng)建第一個(gè)SHEET,其索引SheetIndex=0  
        //設(shè)置 當(dāng)前處于活動(dòng)狀態(tài)的SHEET 為PHPExcel自動(dòng)創(chuàng)建的第一個(gè)SHEET   
        $excel->setActiveSheetIndex(0); //objPHPExcel  
        //設(shè)置sheet的title  
        $excel->getActiveSheet()->setTitle($titles.'第1'.'頁(yè)');        
        //設(shè)置sheet的列名稱  
        for($k = 0; $k < count($tableheader); ++$k) 
        {  
            $excel->getActiveSheet()->setCellValue("$letter[$k]".'1',"$tableheader[$k]");//第一行數(shù)據(jù)  

        }  
        //填充表格信息 處理第1塊數(shù)據(jù)  
        $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);//進(jìn)度條記錄當(dāng)前執(zhí)行條數(shù)

        }  
        //后續(xù)的sheet頁(yè)及數(shù)據(jù)塊  
        for ( $i = 1; $i < $num; ++$i) 
        {  
            //創(chuàng)建第$i個(gè)sheet  
            $msgWorkSheet = new \PHPExcel_Worksheet($excel, $titles."第".($i + 1)."頁(yè)"); //創(chuàng)建一個(gè)工作表  
            $excel->addSheet($msgWorkSheet); //插入工作表  
            $excel->setActiveSheetIndex($i); //切換到新創(chuàng)建的工作表  
  
            //設(shè)置sheet的列名稱  
            for($k = 0; $k < count($tableheader); ++$k) 
            {  
                $excel->getActiveSheet()->setCellValue("$letter[$k]1","$tableheader[$k]");//第一行數(shù)據(jù)  

            }   
            //填充表格信息 處理第$i塊數(shù)據(jù)  
            $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);  
        }
        //創(chuàng)建Excel輸出對(duì)象  
        $filename = $titles.".".$types;  
       $write = new \PHPExcel_Writer_Excel5($excel);  
        ob_end_clean();//清除緩沖區(qū),避免亂碼     
    } 
    //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;

簡(jiǎn)單導(dǎo)出:
public function holeExport()
{
$str = "時(shí)間,標(biāo)題,分類(lèi),展現(xiàn)量,點(diǎn)擊數(shù),排序\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;
}