ThinkPHP5框架使用phpExcel对数据进行导出和导入的详细教程

一、安装phpExcel扩展包

此处省略20个字。

二、封装一函数，将需要导出的数据整理，设置excel表格的属性和样式，直接上代码

导出 参数1：$expTitle :导出excel的标题

参数2：$expCellName:导出到表格中需要显示每一列的标题 如下：

$expCellName=array(
    array('supplier_id','供应商ID'),
    array('goods_id','商品ID'),
    array('goods_name','商品名称'),
);

参数三：$expTableData : 需要导出的表中数据

/**
 * 导出EXCEL
 */
function exportExcel($expTitle, $expCellName, $expTableData)
{
    $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);
    //文件名称
    $fileName = $expTitle.date('_YmdHis');
    //or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
    $dataNum = count($expTableData);
    vendor("phpexcel.Classes.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    $cellName    = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
    // 设置水平垂直居中
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //设置excel表格 从 A1 到 AB1 这一行的字体加粗
    $objPHPExcel->getActiveSheet()->getStyle('A1:AB1')->getFont()->setBold(true);
    // 设置某一行的高度 1.2.3
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

    //J 、 K列为文本 (输入什么就是什么，不会随着excel系统格式变化)
    $objPHPExcel->getActiveSheet()->getStyle('J')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    $objPHPExcel->getActiveSheet()->getStyle('K')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
    //设置某一列的宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(45);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
    $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
    // 设置某一行的高度 1.2.3
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

    // 隐藏某一列
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setVisible(false);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setVisible(false);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);

    //合并单元格
    // $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');

    // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
    for ($i = 0; $i < $cellNum; $i++) {
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i][1]);
    }
    // Miscellaneous glyphs, UTF-8

    for ($i = 0; $i < $dataNum; $i++) {
        for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 2), $expTableData[$i][$expCellName[$j][0]]);
            
        }
    }
    // header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
    header("Content-Disposition:attachment;filename={$fileName}.xls");
    //attachment新窗口打印inline本窗口打印
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}

导入 $info  接收的上传.xls  表格文件

 //将excel数据插入数据库
    public function insertExcelArray($info){
         $exclePath = $info->getSaveName();  //获取文件名
         $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;   //上传文件的地址
         //截取文件后缀 xlsx xls
         $extension = strtolower( pathinfo($file_name, PATHINFO_EXTENSION) );

         //区分上传文件格式
         if($extension == 'xlsx') {
             $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
             $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
         }else if($extension == 'xls'){
             $objReader =\PHPExcel_IOFactory::createReader('Excel5');
             $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
         }
         $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
         array_shift($excel_array);  //删除第一个数组(标题);

         $data = [];

         foreach($excel_array as $k=>$v) {
                $data[$k]['xxx']        = $v[0];
                $data[$k]['xxx']        = $v[1];
                $data[$k]['xxx']        = $v[4];
         }
        Db::name('tableName')->insertAll($data);
      
          return ['code'=>0,'msg'=>'添加成功'];
    }