使用PHPExcel 读取excel表格数据到数据库;
//创建一个读取excel数据,可用于入库
function readExcel($file)
{
// $file = '.'.$file;
ini_set('max_execution_time', '0');
$objPHPExcel = new \PHPExcel();
// 判断使用哪种格式
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($file);
$sheet = $objPHPExcel->getSheet(0);
$allColumn = $sheet->getHighestColumn();//**取得最大的列号*/
$allRow = $sheet->getHighestRow();//**取得一共有多少行*/
$ColumnNum = \PHPExcel_Cell::columnIndexFromString($allColumn);//列号 转 列数
//mysql_query("set names 'utf8'");//输出中文
//循环读取excel文件,读取一条,插入一条
for ($j1 = 2; $j1 <= $allRow; $j1++) {
$a = $objPHPExcel->getActiveSheet()->getCell("A" . $j1)->getValue();//获取A列的值
$b = $objPHPExcel->getActiveSheet()->getCell("B" . $j1)->getValue();//获取B列的值
$c = $objPHPExcel->getActiveSheet()->getCell("C" . $j1)->getValue();//获取C列的值
$d = $objPHPExcel->getActiveSheet()->getCell("D" . $j1)->getValue();//获取D列的值
$e = $objPHPExcel->getActiveSheet()->getCell("E" . $j1)->getValue();//获取E列的值
$f = $objPHPExcel->getActiveSheet()->getCell("F" . $j1)->getValue();//获取E列的值
$g = $objPHPExcel->getActiveSheet()->getCell("G" . $j1)->getValue();//获取E列的值
//将获取的excel内的内容存入二维数组
$datas[] = array(
'question'=>$a,
'state'=>$b,
'a' => $c,
'b' => $d,
'c' => $e,
'd' => $f,
'e' => $g
);
}
return $datas;
}
/**
* excel表格数据 批量导入数据库
* @param string $table 表名
* @param array $excel_result excel数组
* @return string
*/
function sql_batch($excel_result = [] , $table = '')
{
if (count($excel_result) == 0) {
return false;
}
if (!$table) {
return false;
}
$data_value = [];
foreach ($excel_result as $key => $value) {
$data_key = $value;
array_push($data_value,array_values($value));
}
$data_key = array_keys($data_key);
$list_array = $data_key; //字段名
$value_array = $data_value; //写入得值
$sql = sql_batch_add($table,$list_array,$value_array);
return $sql;
}
/**
* 数据批量写入数据库
* @param string $table 表名
* @param array $list_array 列数组
* @param array $value_array 值数组 二维数组
* @param string $sql 返回拼接完整得insert语句
*/
function sql_batch_add($table , $list_array = array() , $value_array = array())
{
$list = implode(',',$list_array);
$sql = 'INSERT INTO '. $table .' ('. $list .') VALUES';
$sql_push = [];
foreach ($value_array as $key => $value) {
$tmpstr = [];
foreach ($value as $k => $v) {
$tmp = '"'. $v .'"';
array_push($tmpstr,$tmp);
}
$tmpstr = implode(',',$tmpstr);
$tmpstr = '('. $tmpstr .')';
array_push($sql_push,$tmpstr);
}
$sql_push = implode(',',$sql_push);
$sql = $sql . $sql_push;
return $sql;
}