Home  >  Article  >  php教程  >  PHPExcel读取excel

PHPExcel读取excel

WBOY
WBOYOriginal
2016-06-21 08:47:441131browse

主要功能是读取上传的excel文件,插入或更新到数据库。

 

  iconv("gbk","utf8",$_FILES["file"]["tmp_name"]),mysql_query("SET NAMES 'utf8'"),编码转换防止中文在数据库中显示乱码;

 

以下是主要的源代码:

 

复制代码

 1 header("Content-type:text/html;charset=utf-8");

 2 require_once('PHPExcel/Classes/PHPExcel.php');

 3 require_once('PHPExcel/Classes/PHPExcel/IOFactory.php');

 4 

 5 iconv("gbk","utf8",$_FILES["file"]["tmp_name"]);//编码转换

 6 

 7 $fileType=PHPExcel_IOFactory::identify($_FILES["file"]["tmp_name"]);//判断文件的类型

 8 $objReader=PHPExcel_IOFactory::createReader($fileType);//创建对象

 9 $objPHPExcel=$objReader->load($_FILES['file']['tmp_name']);//导入数据

10 

11 $currentSheet = $objPHPExcel->getSheet(0); //第一个工作簿

12 $allRow = $currentSheet->getHighestRow(); //行数

13 $letters_arr = array(1=>'A',2=>'B',3=>'C',4=>'D',5=>'E',6=>'F',7=>'G',8=>'H',9=>'I',10=>'J',11=>'K',12=>'L',13=>'M', 14=>'N',15=>'O',16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',26=>'Z');

14  

15  

16 $con=mysql_connect('localhost','root','root') 

17 or die('数据库链接失败!
'.mysql_error());

18 

19 $db=mysql_select_db('db_product',$con);

20 mysql_query("SET NAMES 'utf8'");

21 for($currentRow = 3;$currentRow

22   $rowValues='';

23   $id=(string)($currentSheet->getCell('C'.$currentRow)->getValue());

24 

25   $select_id=mysql_query("SELECT * FROM t_shop WHERE productNum='$id'"); 

26   $result=mysql_fetch_array($select_id);

27   

28  

29   if(!$result){//判断是否存在

30     foreach ($letters_arr as $key => $value) {

31     $xh=$currentSheet->getCell($value.$currentRow)->getValue();

32     if($key>14)break;

33     $rowValues=$rowValues.($key>1?',':'').'\''.$xh.'\'';

34 

35 

36     }

37     

38     $sql="INSERT INTO t_shop (shopName,brand,productNum,spec,barCode,category,productName,orderNumber,deliveryNumber,returnNumber,returnBackNumber,salesPrice,costPrice,profit) VALUES (".$rowValues.")";

39     mysql_query($sql);

40   }

41   else{

42     

43     $reValues=re_row($result,$letters_arr,$currentSheet,$currentRow);

44     $sql="UPDATE t_shop SET shopName='$reValues[0]',brand='$reValues[1]',spec='$reValues[2]',barCode='$reValues[3]',category='$reValues[4]',productName='$reValues[5]',orderNumber=$reValues[6],deliveryNumber=$reValues[7],returnNumber=$reValues[8],returnBackNumber=$reValues[9],salesPrice=$reValues[10],costPrice=$reValues[11],profit=$reValues[12] WHERE productNum='$id'";

45     mysql_query($sql);

46     echo mysql_error().$id.'###';

47     print_r($reValues);

48     echo '
';

49    } 

50   

51   

52 

53   }

54  

55 mysql_close($con);

56 

57 function re_row($s,$a,$obj,$currentRow){

58   $b=$d='';

59    foreach ($a as $key => $value) {

60     $xh=$obj->getCell($value.$currentRow)->getValue();

61     if($key>14)break;

62      $d[]=$xh;

63 

64 

65     }

66 

67   foreach ($d as $key => $value) {

68     if($key!=2 && $key

69          $b[]= (string)$value;

70     }

71     elseif($key>6){

72       $b[]=$s[$key]+$value;

73     }

74 

75    

76   }

77   return $b;

78 }



Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn