excel导入导出

//<h1><strong>第一步首先建立一个表单</strong></h1><meta charset="utf-8"/><form method="post" action="daoru.php" enctype="multipart/form-data"><input type="hidden" name="leadExcel" value="true">    <table align="center" width="90%" border="0">    <tr>       <td>        <input type="file" name="inputExcel"><input type="submit" value="导入数据">       </td>    </tr>    </table></form><button><a href='./main.php'>导出</a></button>//<h1><strong>excel导入代码编写</strong></h1>

<?phprequire_once "./PHPExcel/Classes/PHPExcel.php";  require_once './PHPExcel/Classes/PHPExcel/IOFactory.php';  require_once './PHPExcel/Classes/PHPExcel/Writer/Excel5.php'; if($_POST['leadExcel'] == "true"){    $filename = $_FILES['inputExcel']['name'];    $tmp_name = $_FILES['inputExcel']['tmp_name'];    $msg = uploadFile($filename,$tmp_name);    echo $msg;}function uploadFile($file,$filetempname) { //自己设置的上传文件存放路径 $filePath = './file/'; $str = "";//下面的路径按照你PHPExcel的路径来修改    require_once './PHPExcel/Classes/PHPExcel.php';    require_once './PHPExcel/Classes/PHPExcel/IOFactory.php';    require_once './PHPExcel/Classes/PHPExcel/Reader/Excel5.php';//注意设置时区    $time=date("y-m-d-H-i-s");//去当前上传的时间 //获取上传文件的扩展名    $extend=strrchr ($file,'.');    //上传后的文件名    $name=$time.$extend;$uploadfile=$filePath.$name;//上传后的文件名地址 $result=move_uploaded_file($filetempname,$uploadfile);//假如上传到当前目录下    if($result) //如果上传文件成功，就执行导入excel操作    {        //include "conn.php";        $objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format         $objPHPExcel = $objReader->load($uploadfile);         $sheet = $objPHPExcel->getSheet(0);         $highestRow = $sheet->getHighestRow();           //取得总行数         $highestColumn = $sheet->getHighestColumn(); //取得总列数 /* 第二种方法*/        $objWorksheet = $objPHPExcel->getActiveSheet();        $highestRow = $objWorksheet->getHighestRow();         //echo 'highestRow='.$highestRow;        echo "<br>";        $highestColumn = $objWorksheet->getHighestColumn();        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数       // echo 'highestColumnIndex='.$highestColumnIndex;        echo "<br>";        $headtitle=array(); /***********连接数据库**********/$pdo=new PDO("mysql:host=127.0.0.1;dbname=zong","root","song123",array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';"));//$row不能为第一行,因为第一行是字段名  for ($row = 2;$row <= $highestRow;$row++)         {            $strs=array();            //注意highestColumnIndex的列数索引从0开始            for ($col = 0;$col < $highestColumnIndex;$col++)            {                $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();//print_r($strs);die;            } //print_r($strs);die; $sql = "INSERT INTO news(`title`, `time`, `content`, `s_id`) VALUES (            '{$strs[1]}',            '{$strs[2]}',            '{$strs[3]}',            '{$strs[4]}')";if(!$pdo->query($sql)){return false;echo "sql语句有误!!!!!!";}}$msg = "你成功了,真牛逼";}else{$msg = "导入失败！";}return $msg;}

//<h1><strong>excel导出编码</strong></h1>

<?phprequire_once "./PHPExcel/Classes/PHPExcel.php";//新建$resultPHPExcel = new PHPExcel(); //设置参数 //设值 $resultPHPExcel->getActiveSheet()->setCellValue('A1', 'id'); $resultPHPExcel->getActiveSheet()->setCellValue('B1', 'title'); $resultPHPExcel->getActiveSheet()->setCellValue('C1', 'time'); $resultPHPExcel->getActiveSheet()->setCellValue('D1', 'content'); $resultPHPExcel->getActiveSheet()->setCellValue('E1', 's_id'); //连接数据库$pdo=new PDO("mysql:host=127.0.0.1;dbname=zong","root","song123",array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';"));$rs = $pdo->query("SELECT * FROM news");$i = 2; foreach($rs as $item){ $resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['id']); $resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['title']); $resultPHPExcel->getActiveSheet()->setCellValue('C' . $i, $item['time']); $resultPHPExcel->getActiveSheet()->setCellValue('D' . $i, $item['content']); $resultPHPExcel->getActiveSheet()->setCellValue('E' . $i, $item['s_id']); $i ++; }//设置导出文件名 $time = time();$outputFileName = $time.'.xls'; $xlsWriter = new PHPExcel_Writer_Excel5($resultPHPExcel); //ob_start(); ob_flush(); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="'.$outputFileName.'"'); header("Content-Transfer-Encoding: binary"); header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $xlsWriter->save( "php://output" );