最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

PHP导入导出excel(环境Tp3.2 + phpExcel)

XAMPP教程 中文小张 943浏览 0评论

将PHPExcel文件夹和PHPExcel.php文件拷贝到如下的目录位置

2. 将PHPExcel.php修改成PHPExcel.class.php

3.导入
public function push_excel()
{
$file = $_FILES[‘file’];
$data = $this->importExecl($file[‘tmp_name’]);
$company = M(“company”)->field(“company_id as id,company_name as name”)->select();
$company = $this->tarrTooarr($company);
$store = M(“store”)->field(“store_id as id,store_name as name”)->select();
$store = $this->tarrTooarr($store);
$time = time();
$insert = [];
foreach ($data as $key => $value) {
if(!false == $data[$key]){
$insert[] = [
‘company_id’    =>  array_search($value[‘A’],$company), //A         公司
‘store_id’      =>  array_search($value[‘B’],$store),   //B         门店
‘position_name’ =>  $value[‘C’],                        //C         职位名称
‘add_time’      =>  $time,
];
}
}
// var_dump($insert);exit;
$res = M(‘position’)->addAll($insert);
if($res){
$this->success(‘添加完成!’);
}else{
$this->success(‘添加失败。’);
}
}
public function tarrTooarr($data)
{
$arr = [];
foreach($data as $k=>$v){
$arr[$v[‘id’]] = $v[‘name’];
}
return $arr;
}
/**
*  数据导入
* @param string $file excel文件
* @param string $sheet
* @return string   返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
public function importExecl($file=”, $sheet=0){
$file = iconv(“utf-8″,”gb2312”, $file);   //转码
if(empty($file) OR !file_exists($file)) {
die(‘文件不存在请重新选择。’);
}
Vendor(“PHPExcel.Classes.PHPExcel”);
$objRead = new \PHPExcel_Reader_Excel2007();   //建立reader对象
if(!$objRead->canRead($file)){
$objRead = new \PHPExcel_Reader_Excel5();
if(!$objRead->canRead($file)){
die(‘此文件不是Excel指定格式。’);
}
}
$cellName = array(‘A’, ‘B’, ‘C’);
$obj = $objRead->load($file);  //建立excel对象
$currSheet = $obj->getSheet($sheet);   //获取指定的sheet表
$columnH = $currSheet->getHighestColumn();   //取得最大的列号
$columnCnt = array_search($columnH, $cellName);
$rowCnt = $currSheet->getHighestRow();   //获取总行数
$data = array();
for($_row=2; $_row<=$rowCnt; $_row++){  //读取内容
for($_column=0; $_column<=$columnCnt; $_column++){
$cellId = $cellName[$_column].$_row;
$cellValue = $currSheet->getCell($cellId)->getValue();
//$cellValue = $currSheet->getCell($cellId)->getCalculatedValue();  #获取公式计算的值
if($cellValue instanceof PHPExcel_RichText){   //富文本转换字符串
$cellValue = $cellValue->__toString();
}
$data[$_row][$cellName[$_column]] = $cellValue;
}
}
return $data;
}
/**
* 导出模板
* ‘company_id’                –> A         公司
* ‘store_id’                   –> B         门店
* ‘position_name’             –> C         职位名称
*/
public function export(){
Vendor(“PHPExcel.Classes.IOFactory”);
Vendor(“PHPExcel.Classes.PHPExcel”);
$objExcel = new \PHPExcel_Reader_Excel2007();   //建立reader对象
$objExcel = new \PHPExcel();
$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, ‘Excel2007’);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setCellValue(‘A1′,’公司名称’);
$objActSheet->setCellValue(‘B1′,’门店名称’);
$objActSheet->setCellValue(‘C1′,’职位名称’);
$filename = ‘岗位表’.date(‘YmdHis’).’.xlsx’;
$filename = iconv(“utf-8”, “gb2312”, $filename);
header(“Content-Type: application/force-download”);
header(“Content-Type: application/octet-stream”);
header(“Content-Type: application/download”);
header(‘Content-Disposition:inline;filename=”‘.$filename.'”‘);
header(“Content-Transfer-Encoding: binary”);
header(“Cache-Control: must-revalidate, post-check=0, pre-check=0”);
header(“Pragma: no-cache”);
ob_end_clean();
$objWriter->save(‘php://output’);
exit;

转载请注明:XAMPP中文组官网 » PHP导入导出excel(环境Tp3.2 + phpExcel)

您必须 登录 才能发表评论!