电子表格数据导入数据库和数据库下载数据到excel PHP代码phpexecl

本文链接：https://blog.csdn.net/qq_35669659/article/details/71533755

//qq:2579679536

<?php
     require_once '../Classes/PHPExcel.php'; 
    $file_name= "./templates/mytext.xls";
    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $objPHPExcel = $objReader->load($file_name,$encode='utf-8');
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow(); // 取得总行数
    $highestColumn = $sheet->getHighestColumn(); // 取得总列数
     $str = '';      
     for($j=2;$j<=$highestRow;$j++)               //从第二行开始读取数据     
     {           
          $str.='(';         
          $strValue = '';             
          for($k='A';$k<=$highestColumn;$k++)      //从A列读取数据                
          {                      
                      //读取单元格 
               $strValue.="'".$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue()."'".',';               
           } 

           $strValue=substr($strValue,0,-1);                 
           $str.=$strValue.'),';              
           }

         $str=substr($str,0,-1);
        echo $str;
       //$this->success('导入成功！');
        /*$sql = "INSERT INTO user (uid,password,mail,phone,name,add_time,ipaddress) VALUES $str";           
       $res = $nsodb->query($sql);      
        if($res){ 
            echo "<script>alert('导入成功！');</script>";        
         } */

?>

数据库下载数据到excel

<?php

$sql="select * from weixin_send where  send_time between '".$start_time."' and '".$end_time."'";
$query4=$this->db->query($sql);
$rs4 = $query4->result_array();

$this->load->library('doExcel/PHPExcel'); 

   // Create new PHPExcel object
   $objPHPExcel = new PHPExcel();

   // Set document properties
   $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                             ->setLastModifiedBy("Maarten Balliauw")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");
       // 表头  
    $objPHPExcel->setActiveSheetIndex(0)  
            ->setCellValue('A1', '序号')  
            ->setCellValue('B1', '旺旺号')  
            ->setCellValue('C1', '微信open_id')  
            ->setCellValue('D1', '金额')  
            ->setCellValue('E1', '日期');
    //内容
    foreach($rs4 as $k=>$v){
        $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($k+2), $v['id']); 
        $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($k+2), $v['buyer_nick']); 
        $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($k+2), $v['open_id']); 
        $objPHPExcel->getActiveSheet(0)->setCellValue('D' . ($k+2), $v['money']); 
        $objPHPExcel->getActiveSheet(0)->setCellValue('E' . ($k+2), $v['send_time']); 

    }
        // Rename sheet    
    $objPHPExcel->getActiveSheet()->setTitle("fnkuan");  

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$datetime=date('Y-m-d',time());
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=fankuan{$datetime}.xls");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
 //print_r( $objPHPExcel);
?>

HTML界面提交表格代码

<html>
    <head>

    </head>
    <body>
    <P><a href="{:U('Index/expUser')}" >导出数据并生成excel</a></P><br/>
        <form action="{:U('Index/impUser')}" method="post" enctype="multipart/form-data">
            <input type="file" name="import"/>
            <input type="hidden" name="table" value="tablename"/>
            <input type="submit" value="导入"/>
        </form>
    </body>

 </html>