Excel导入、导出

 

一、导出

public function export(){
$data = array();//获取数据
import("Org.Util.PHPExcel");
error_reporting(E_ALL);
date_default_timezone_set('Europe/London'); 
$objPHPExcel = new \PHPExcel(); 
import("Org.Util.PHPExcel.Reader.Excel5");
/*设置excel的属性*/
$objPHPExcel->getProperties()->setCreator("slackck")//创建人
->setLastModifiedBy("slackck")//最后修改人
->setTitle('')//标题
->setSubject("")//题目
->setDescription("")//描述
->setKeywords("")//关键字
->setCategory("result file");//种类

//第一行数据
$objPHPExcel->getActiveSheet()->mergeCells( 'A1:Y1' );
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(25);

$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', 'XXXXXXX');
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A2', '姓名')
    ->setCellValue('B2','性别')
    ->setCellValue('C2','身份证');

foreach($data as $k => $v){
    $k=$k+2;
    $num=$k+1;//数据从第二行开始录入
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A'.$num, $v['name'])
        ->setCellValue('B'.$num, $v['sex'])
        ->setCellValue('C'.$num, $v['identity']);

}

$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.time().'.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
​​​​​​​
}

 

二、导入

public function Import(){
    $filename = 'xxx.xls';
    import("Org.Util.PHPExcel");
    import("Org.Util.PHPExcel.Reader.Excel5".'','.php');
    $objReader = new \PHPExcel_Reader_Excel5();
    $objPHPExcel = $objReader->load($filename,'utf-8');
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow(); // 取得总行数
    $highestColumn = $sheet->getHighestColumn(); // 取得总列数
    //循环读取excel文件,读取一条,插入一条
    $arr=array();
           //从第一行开始读取数据
           for($j=1;$j<=$highestRow;$j++){
                //从A列读取数据
                for($k='A';$k<=$highestColumn;$k++){
                    // 读取单元格
                    $cell = $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
                    //将对象转换成字符串
                    if(is_object($cell)){
                        $cell= $cell->__toString();
                    }
                    $arr[$j][]=$cell;
                }
            }
   foreach ($arr as $key => $value) {
        $data['name'] = $value[0];
        $data['sex'] = $value[1];
        $data['identity'] = $value[2];
        $newData[$key] = $data;
   }
   $arrs = array_values($newData);
   $resume->addAll($arrs);
   exit;
}