使用PhpSpreadsheet將Excel匯入的MySQL資料庫

woff · 發表於 2023-5-15 22:58:25

日常開發中，我們經常遇到這樣的場景，需要將一個Excel表格資料如客戶資訊匯入到系統資料庫中，然後在系統中進行進一步操作，如給匯入的客戶群發短信。

PHP匯入Excel避免了人工錄入資訊的麻煩和出錯，提高效率。

給大家講解使用PhpSpreadsheet將Excel匯入的MySQL資料庫。準備首先我們需要準備一張MySQL表，表名net_costomer，

接著，我們準備excel表格檔案，我特意製作了一個Excel檔案

aa.xlsx (8.66 KB, 下載次數: 19) ：然後將Excel檔案放置程式應用目錄下。當然，實際應用中，我們一般通過web上傳到伺服器指定目錄下，然後再進行匯入資料庫操作。

最後，你還需要安裝好PhpSpreadsheet，在前一節文章中有介紹，非常簡單：使用PhpSpreadsheet讀取和寫入Excel 匯入Excel 準備工作做好後，我們來開始匯入。

思路很簡單：使用PhpSpreadsheet讀取Excel表格中的有用資訊，然後組裝成sql語句，最後批量插入到MySQL表中。我把程式碼貼出來。

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Excel匯入</title>
<meta name="generator" content="NetYea! 1.32" />
</head>
<body>
<?php
include_once ('admin_global.php');
$r=$db->Get_user_shell_check($uid, $shell);
session_start();
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
if (isset($_POST["send"])) {
$leadExcel=$_POST["leadExcel"];
if($leadExcel == "true"){
$filePath = './import/';
//獲取上傳的文件名
$filename = $_FILES['inputExcel']['name'];
//上傳到服務器上的臨時文件名
$tmp_name = $_FILES['inputExcel']['tmp_name'];
$filename=explode(".",$file);//把上傳的文件名以「.」好為準做一個數組。
$time=date("y-m-d-H-i-s");//去當前上傳的時間
$filename[0]=$time;//取文件名t替換
$name=implode(".",$filename); //上傳後的文件名
$uploadfile=$filePath.$name.".xlsx";//上傳後的文件名地址
if(!move_uploaded_file($tmp_name ,$uploadfile)){
die("該目錄無法寫入");
}
//move_uploaded_file() 函數將上傳的文件移動到新位置。若成功，則返回 true，否則返回 false。
$result=move_uploaded_file($tmp_name ,$uploadfile);//假如上傳到當前目錄下
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($uploadfile); //載入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 總行數
$highestColumn = $worksheet->getHighestColumn(); // 總列數
$highestColumnIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
//循環讀取excel文件,讀取一條,插入一條
for($j=1;$j<=$highestRow;$j++){
for($k='A';$k<=$highestColumn;$k++){
$str .= $spreadsheet->getActiveSheet()->getCell("$k$j")->getValue().'\\';
//讀取單元格
}
//explode:函數把字符串分割為數組。
//echo $highestRow."<br>".$highestColumn."<br>";
//echo $str."<br>";
$strs = explode("\\",$str);
$id=$strs[0];
$delivernum=$strs[1];
$costomernum=sprintf("%06d",$strs[2]);
$packages = $strs[3];
$cmid = $strs[4];
$name = $strs[5];
$tel = $strs[6];
$cell = $strs[7];
$address = $strs[8];
$total = $strs[9];
$bonus = $strs[10];
$spc = $strs[11];
$slc = $strs[12];
$deliverdate = $strs[13];
$delivertime = $strs[14];
$fuid = $strs[15];
$suid = $strs[16];
$fax = $strs[17];
$close = $strs[18];
if ($deliverdate == "") {
$deliverdate = mktime();
} else {
list($y,$m,$J) = sscanf($deliverdate,"%d-%d-%d");
$deliverdate = mktime("0","0","0",$m,$J,$y);
}
$sql_check = "SELECT * FROM net_order WHERE delivernum='".$delivernum."'";
$query_check = $db->query($sql_check);
$row_check = mysqli_num_rows($query_check);
if($row_check>=1)
echo "訂單編號：".$delivernum."重覆匯入..<br>";
if ($delivernum!=""){
$sql = "INSERT INTO net_order(id,delivernum,costomernum,packages,cmid,name,tel,cell,address,total,bonus,spc,slc,deliverdate,delivertime,fuid,suid,fax,close,itemspc,itemslc,scolor,backordernum,levelc,faxdate,closedate,returns,returnsdate,packages_id)
VALUES('$id','$delivernum','$costomernum','$packages','$cmid','$name','$tel','$cell','$address','$total','$bonus','$spc','$slc','$deliverdate','$delivertime','$fuid','$suid','$fax','$close','0','0','0','0','0','0','0','0','0','0')";
echo "匯入".$sql."<br>";
if(!$db->query($sql)){
echo "匯入失敗，請洽程式管理員！";
return false;
}
}
$str = "";
}
unlink($uploadfile); //刪除上傳的excel文件
echo '<script>alert(\'匯入完成！\');window.location=\'excelintoorder.php\';</script>';
}else{
echo '<script>alert(\'匯入失敗！\');window.location=\'excelintoorder.php\';</script>';
}
}
if (isset($_POST["clear"])) {
$sql = "TRUNCATE TABLE net_mailuser";
if(!$db->query($sql)){
return false;
}
echo '<script>alert(\'電子報會員資料已清空！\');window.location=\'test1.php\';</script>';
}
?>
<form name="form2" method="post" action="<?php $_SERVER['PHP_SELF']?>" enctype="multipart/form-data">
<input type="hidden" name="leadExcel" value="true">
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tr><td colspan="19">匯入訂單</td></tr>
<tr><td colspan="19">EXCEL請按照下面格式匯入，不然會出錯！</td></tr>
<tr><td>id</td><td>訂單編號</td><td>客戶編號</td><td>廠商包裝</td><td>客戶id</td><td>姓名</td><td>電話1</td><td>電話2</td><td>地址</td><td>總價</td><td>獎金</td><td>茶品品項</td><td>數量</td><td>送達日</td><td>送貨時段</td><td>開發業務</td><td>客服業務</td><td>傳真</td><td>結案</td></tr>
</table>
<br /><br />
<table align="center" width="100%" border="0">
<tr>
<td>
<input type="file" name="inputExcel"><input type="submit" value="上傳" name="send">
</td>
</tr>
</table>
</form>
</body>
</html>

複製代碼

範例圖：

$worksheet->getCellByColumnAndRow($col, $row)->getValue() 可以獲取表格中任意單元格資料內容， $col 表示單元格所在的列，以數字表示，A列表示第一列， $row 表示所在的行。
用雙迴圈匯入表格

當然你也可以逐條插入，但是效率沒有批量插入高。最後執行匯入程式碼，你會發現資料表裏有資料了

參考文章https://itw01.com/FSYNSE2.html

帳號		自動登錄		找回密碼
密碼				註冊

[教學] 使用PhpSpreadsheet將Excel匯入的MySQL資料庫

相關帖子