<?php //首先导入PHPExcel require_once 'PHPExcel.php'; $filePath = "test.xlsx"; //建立reader对象 $PHPReader = new PHPExcel_Reader_Excel2007(); if(!$PHPReader->canRead($filePath)){ $PHPReader = new PHPExcel_Reader_Excel5(); if(!$PHPReader->canRead($filePath)){ echo 'no Excel'; return ; } } //建立excel对象,此时你即可以通过excel对象读取文件,也可以通过它写入文件 $PHPExcel = $PHPReader->load($filePath); /**获取工作表数量*/ $sheetCount = $PHPExcel->getSheetCount(); for($sheetid=0;$sheetid<=$sheetCount-1;$sheetid++){ /**读取excel文件中的第N个工作表*/ $currentSheet = $PHPExcel->getSheet($sheetid); //获取sheet名 $currentSheetName = $currentSheet->getTitle(); /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); //循环读取每个单元格的内容。注意行从1开始,列从A开始 for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){ $tmpline = NULL; for($colIndex='A';$colIndex<=$allColumn;$colIndex++){ $addr = $colIndex.$rowIndex; $cell = $currentSheet->getCell($addr)->getValue(); if($cell instanceof PHPExcel_RichText) //富文本转换字符串 $cell = $cell->__toString(); $tmpline .= $cell.","; } $tmpline .= "\n"; $tmpline=str_replace(",\n","\n",$tmpline); echo $currentSheetName.",".$tmpline; } } ?>
<?php //连接 $c = new PDO( "sqlsrv:server=(local); Database = ", "sa", "123456", array(PDO::SQLSRV_ATTR_DIRECT_QUERY => true)); //返回不含字段名称的查询 function query($query){ global $c; //定义全局变量 $stmt = $c->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); $stmt->execute(); global $rowcount; $rowcount=NULL; //判断sql类型 $rowcount = $stmt->rowCount(); if(stripos($query, 'update') !== false){ return "UP".$rowcount; }elseif(stripos($query, 'delete') !== false){ return "DE".$rowcount; }elseif(stripos($query, 'insert') !== false){ return "IN".$rowcount; }else{ //初始化变量 $content=NULL; if( $stmt->rowCount() == 0){ return ""; }else{ while ( $row = $stmt->fetch( PDO::FETCH_NUM ) ){ for($i = 0; $i < $stmt->columnCount(); $i++) { $type=gettype($row[$i]); //判断是否object类型 if ($type=="object"){ $var=$row[$i]; //格式化时间 $time=str_replace("T"," ",$var->format(DateTime::ISO8601)); $time=substr($time,0,19); $time=$time.".000"; $content=$content.$time.","; }else{ $ctmp=str_replace(",",",",$row[$i]); $content=$content.$ctmp.","; //追加 } } $content=$content."\n"; } $content=str_replace(",\n","\n",$content); return $content; } } } //返回含字段名称的查询 function qt($query){ global $c; $stmt = $c->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); $stmt->execute(); global $rowcount; $rowcount=NULL; $rowcount = $stmt->rowCount(); if(stripos($query, 'update') !== false){ return "UP".$rowcount; }elseif(stripos($query, 'delete') !== false){ return "DE".$rowcount; }elseif(stripos($query, 'insert') !== false){ return "IN".$rowcount; }else{ $content=NULL; if( $stmt->rowCount() == 0){ return "NO"; }else{ $a = $stmt->fetch( PDO::FETCH_ASSOC); $Fields = array_keys($a); global $titles; $titles=NULL; //循环获取字段名称 for($i = 0; $i < $stmt->columnCount(); $i++) { $name=iconv('GB2312','UTF-8',$Fields[$i]); $titles=$titles."$name,"; } $titles=$titles."\r\n"; $titles=str_replace(",\r\n","",$titles); $content = $titles."\r\n"; $stmt->execute(); while ( $row = $stmt->fetch( PDO::FETCH_NUM ) ){ for($i = 0; $i < $stmt->columnCount(); $i++) { $type=gettype($row[$i]); if ($type=="object"){ $var=$row[$i]; $time=str_replace("T"," ",$var->format(DateTime::ISO8601)); $time=substr($time,0,19); $time=$time.".000"; $content=$content.$time.","; }else{ $ctmp=str_replace(",",",",$row[$i]); $content=$content.$ctmp.","; } } $content=$content."\r\n"; } $content=str_replace(",\r\n","\r\n",$content); return $content; } } } //仅执行 function get($sql){ global $c; global $result; $result = $c->prepare( $sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)); $result->execute(); global $rowcount; $rowcount=NULL; $rowcount = $result->rowCount(); } ?>
49 queries in 3.516 seconds |