本文實例講述了ThinkPHP 框架實現的讀取excel導入數據庫操作。分享給大家供大家參考，具體如下：
入口文件中：
|
1
2
|
require_once VENDOR_PATH.'PHPExcel/PHPExcel/IOFactory.php';require_once VENDOR_PATH.'PHPExcel/PHPExcel.php'; |
PHP：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
namespace Home\Controller;class ExcelController extends CommonController{public function Import() {// vendor('PHPExcel.PHPExcel.IOFactory');vendor("PHPExcel.PHPExcel.PHPExcel");vendor("PHPExcel.PHPExcel.Writer.Excel5");vendor("PHPExcel.PHPExcel.Writer.Excel2007");//$excel = new PHPExcel();$fileName = './trans_rate.xlsx';date_default_timezone_set('PRC');// 讀取excel文件try {$objPHPExcel = \PHPExcel_IOFactory::load($fileName);$inputFileType = \PHPExcel_IOFactory::identify($fileName);$objReader = \PHPExcel_IOFactory::createReader($inputFileType);// $objPHPExcel = $objReader->load($fileName);// 確定要讀取的sheet $sheet = $objPHPExcel->getSheet(0);$highestRow = $sheet->getHighestRow();$highestColumn = $sheet->getHighestColumn();// 獲取一行的數據// $phone_str = '';for ($row = 3; $row <= $highestRow; $row++) { $row_data = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);//獲取excel表中一行的數組數據//dump($row_data);$row_data = $row_data[0];$time = date('Y-m-d H:i:s', strtotime(trim($row_data[0])));$start_province = trim($row_data[1]);$start_city = trim($row_data[2]);...// $phone_str .= '"' . $phone . '",';$where['phone'] = $phone;$id_arr = M(數據表名)->where($where)->getField('id');$user_id = !empty($id_arr) ? $id_arr : 0;$fields[] = ['數據表字段' => $user_id,//用戶id ... ];}// dump($fields);$rate_add = M(數據表名)->addAll($fields);dump($rate_add);echo M()->getLastSql();if (!(0 < $rate_add)) {CommonController::logProfile('添加excel數據，SQL：' . M()->getLastSql()); $this->endBack(0); }// echo $phone_str . '<br />';// dump($user_id);} catch (Exception $e) {die('加載文件發生錯誤："' . pathinfo($fileName, PATHINFO_BASENAME) . '": ' . $e->getMessage()); } }} |
php讀取excel表數據：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
<?phpinclude 'ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';$inputFileName = './trans_rate.xlsx';date_default_timezone_set('PRC');// 讀取excel文件try { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName);} catch(Exception $e) { die('加載文件發生錯誤："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());}// 確定要讀取的sheet$sheet = $objPHPExcel->getSheet(0);$highestRow = $sheet->getHighestRow();$highestColumn = $sheet->getHighestColumn();// 獲取一行的數據for ($row = 1; $row <= $highestRow; $row++){// Read a row of data into an array$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);//這里得到的rowData都是一行的數據，得到數據后自行處理var_dump($rowData);echo "<br>";}//$data為從excel中獲取到的數組for ($i =0; $i<count($data);$i++){ echo '<br>'; $gettime= explode('-',$data[$i][0]); if (checkdate($month=$gettime[0],$day=$gettime[1],$year=$gettime[2])){ echo gmdate('Y-m-d',gmmktime(0,0,0,$month,$day,$year)); }else{ echo ($data[$i][0]); } echo '-----------'; echo $data[$i][1];} |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
<?phpinclude 'ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php';$inputFileName = './test.xlsx';date_default_timezone_set('Asia/Shanghai');// 讀取excel文件try { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); // 確定要讀取的sheet，什么是sheet，看excel的右下角，真的不懂去百度吧 $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow();//最大行 $highestColumn = $sheet->getHighestColumn();//最大列 $data = array(); for($rowIndex=2;$rowIndex<=$highestRow;$rowIndex++){ //循環讀取每個單元格的內容。注意行從1開始，列從A開始 for($colIndex='A';$colIndex<=$highestColumn;$colIndex++){ $addr = $colIndex.$rowIndex; if($colIndex==="A"){ //指定H列為時間所在列 $cell = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell($addr)->getValue()));// $cell = PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell($addr)->getValue());// var_dump($cell);die; }else{ $cell = $sheet->getCell($addr)->getValue(); }// if($cell instanceof PHPExcel_RichText){ //富文本轉換字符串// $cell = $cell->__toString();// } $data[$rowIndex][$colIndex] = $cell; } }// return $data; var_dump($data);} catch(Exception $e) { die('加載文件發生錯誤："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());} |
希望本文所述對大家基于ThinkPHP框架的PHP程序設計有所幫助。
原文鏈接：https://blog.csdn.net/qq_42176520/article/details/80975353
