本文实例讲述了ThinkPHP 框架实现的读取excel导入数据库操作。分享给大家供大家参考,具体如下:
入口文件中:
require_once VENDOR_PATH."PHPExcel/PHPExcel/IOFactory.php";
require_once VENDOR_PATH."PHPExcel/PHPExcel.php";
PHP:
namespace HomeController;
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 . "
";
// dump($user_id);
} catch (Exception $e) {
die("加载文件发生错误:"" . pathinfo($fileName, PATHINFO_BASENAME) . "": " . $e->getMessage()); } }}
php读取excel表数据:
include "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 "
";
}
//$data为从excel中获取到的数组
for ($i =0; $i
echo "
";
$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];
}
include "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相关内容感兴趣的读者可查看本站专题:《ThinkPHP入门教程》、《thinkPHP模板操作技巧总结》、《ThinkPHP常用方法总结》、《codeigniter入门教程》、《CI(CodeIgniter)框架进阶教程》、《Zend FrameWork框架入门教程》及《PHP模板技术总结》。
希望本文所述对大家基于ThinkPHP框架的PHP程序设计有所帮助。