function parse($excelfile, $template, $data = array())
{
try {
//获取上传文件的文件名扩展名
$extend = strtolower(strrchr($excelfile, '.'));
$readerType = $extend == '.xlsx' ? 'Excel2007' : 'Excel5';
$objReader = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader($readerType);
//use Excel5 for 5fromat ,use excel2007 for 2007 format
$objPHPExcel = $objReader->load($excelfile);
if (!$objPHPExcel) {
$error = '加载Excel出错';
return array('success' => false, 'error' => $error);
}
$objWorksheet = $objPHPExcel->getActiveSheet();
//取得活动sheet
if (!$objWorksheet) {
$error = '加载Excel出错';
return array('success' => false, 'error' => $error);
}
$title = $objWorksheet->getTitle();
//取得sheet名称
$highestRow = $objWorksheet->getHighestRow();
//取得总行数
$highestColumn = $objWorksheet->getHighestColumn();
//取得总列数
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
//总列数
//执行结果
$error = "";
$first_row = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
$first_row[$col] = $objWorksheet->getCellByColumnAndRow($col, 1)->getValue();
}
//验证表结构，表名称和字段列表
if (!$this->validateExcel($template, $first_row)) {
$error = "Excel内容与模板不符合";
//$error = $first_row;
return array('success' => false, 'error' => $error);
}
//生成插入语句的头部
$sql_header = $this->generateSqlHeader($template, $first_row);
if ($sql_header == "") {
$error = "SQL语句头部生成失败";
return array('success' => false, 'error' => $error);
}
//生成SQL语句
$sql = $sql_header;
for ($row = 2; $row <= $highestRow; $row++) {
//每行的第一列数据不能为空
$first_value = $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
if (empty($first_value)) {
continue;
}
$data_new = array();
$sql_data = '(';
////注意highestColumnIndex的列数索引从0开始
for ($col = 0; $col < $highestColumnIndex; $col++) {
$data_new[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
if ($col == 6) {
// 车品牌
$data_new[$col] = TransportMake::getCode($data_new[$col]);
}
if ($col == 7) {
$data_new[$col] = TransportCar::getCode($data_new[$col]);
}
$sql_data .= "'" . trim($data_new[$col]) . "',";
}
if ($template == "promotion") {
//促销商品
$sql_data .= "'" . $data['createtime'] . "','" . $data['userID'] . "'";
} else {
if ($template == 'subdealer') {
$sql_data .= "'" . $data['flag'] . "','" . $data['UserID'] . "'";
}
}
$sql_data .= ")";
//var_dump($sql_data);exit;
//验证数据是否正确
//$this->validateData($data_new[$col]);
$sql .= $sql_data . ',';
}
$sql = rtrim($sql, ",") . ";";
//返回结果数据
$success = false;
if ($error == "" && $sql != "") {
$success = true;
}
} catch (Exception $e) {
$success = false;
$error = '解析Excel出错' . $e->getMessage();
}
return array('success' => $success, 'error' => $error, 'sql' => $sql);
}
