PHP用PHPExcel 对excel进行读写并写入到mysql数据库

最新推荐文章于 2024-09-05 16:11:33 发布

longyinfengwu

最新推荐文章于 2024-09-05 16:11:33 发布

阅读量1k

点赞数

分类专栏： PHP

本文链接：https://blog.csdn.net/tenggeer0789/article/details/89396689

版权

这篇博客详细介绍了如何利用PHP的PHPExcel库来读取Excel文件，处理数据，并将这些数据有效地写入到MySQL数据库中，实现数据的导入与导出功能。

摘要由CSDN通过智能技术生成

//先下载好PHPExcel 放在与脚本同目录中
<?php
//ini_set("memory_limit","4096M");
//ini_set(”memory_limit”,”4096M”);

header("Content-Type: text/html;charset=utf-8"); 
$serverName = "";
$username = "root";//linux数据库名称
$password = "ssdddd";//数据库密码
$dbname = "jenkinsdata";//自己创建的数据库名
// 创建连接
ini_set('memory_limit', '512M');
$conn = new mysqli($serverName,$username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}	

//开始用PHPExcel进行读写
$tmp_name = './新建 Microsoft Excel 工作表.xlsx';
$uploadfile = 'C:\\wamp64\\www\\dist\\aa.xlsx';
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../phpexcel/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
/** PHPExcel_Reader */
include 'PHPExcel/Reader/Excel5.php';
include 'PHPExcel/Reader/Excel2007.php';
		//var_dump($filename);
		 // if($extend==".xlsx"){
			 //PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized; 
			 //$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;  
			$cacheMethod =PHPExcel_CachedObjectStorageFactory::cache_in_memory; 

            PHPExcel_Settings::setCacheStorageMethod($cacheMethod);  
			   $objReader = PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format 
		//}
		//else{
		//		$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format 
		//}
        $objPHPExcel = $objReader->load($uploadfile); 
        $sheet = $objPHPExcel->getSheet(0); 
        $highestRow = $sheet->getHighestRow();           //取得总行数 
        $highestColumn = $sheet->getHighestColumn(); //取得总列数
		//var_dump($highestRow);
		//var_dump($highestColumn);
       
        $objWorksheet = $objPHPExcel->getActiveSheet();
		//var_dump($objWorksheet);
        $highestRow = $objWorksheet->getHighestRow(); 
       // echo 'highestRow='.$highestRow;
       // echo "<br>";
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PH