PHPExcel

导入数据

$insertId = 0;		//获取插入数据库的ID
function explodeXLSX($file_path)	//$file_path 为 XLSX 的路径
{
    require_once _LIBS . 'PHPExcel/Classes/PHPExcel.php';		//PHPExcel的路径
    require_once _LIBS . 'PHPExcel/Classes/PHPExcel/IOFactory.php';

    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objPHPExcel = $objReader->load($file_path,'utf-8');
    $sheet = $objPHPExcel->getSheet(0);   // 取得第一个Sheet
    $highestRow = $sheet->getHighestRow();   // 取得总行数

    global $insertId;

    //DB::Debug();
    DB::tranBegin();		//事务开始
    for($j=2; $j <= $highestRow; $j++) {	//从第二行开始读取数据，第一行为标题

        $row = array();
        $level_1_id = null;
        for($col = 'A'; $col != 'AA'; $col++) {		//AA为数据列的后一个列号
            $value = $objPHPExcel->getActiveSheet()->getCell("$col$j")->getValue();
            $value = trim($value);
            
            if('A' === $col) $row['name'] = $value;		//name为数据库对应的字段
            if('B' === $col) $row['alias'] = $value;
            if('C' === $col) $row['degree'] = $value;
            if('D' === $col) $row['type'] = $value;
            if('E' === $col) $row['insurance'] = $value;
            if('F' === $col){
                if($value) $row['pic_url'] = $value;
            }
            ......
        }

        if(!empty($row))
        {
            $id = Student::add($row);	//保存该行数据到数据库
            if($id < 1){
                exit('<meta charset="utf-8"> 保存数据出错，请检查是否有空行。<br>所有数据被滚回。');
            }
            if($insertId != $id){
                $insertId = $id;
            }else{		//说明该行数据保存不成功
                exit('<meta charset="utf-8"> 保存数据出错<br>所有数据被滚回。');
            }
        }
    }
    DB::tranEnd();		//事务结束

    header("Location: /?a=import&msg=导入成功");exit;
}

导出数据

function excel()
{
    $title = isset($_REQUEST['title']) ? $_REQUEST['title'] : '';
    if(!$title) return false;

    $rows = Student::getList("title='$title'");

    $k = 1;
    $data = array();
    foreach ($rows as $row){
        $data[$k]['考试名称'] = $title;
        $data[$k]['姓名'] = $row['name'];
        $data[$k]['时间'] = date('Y-m-d H:i:s', $row['create_at']);
        $data[$k]['IP'] = $row['ip'];
        $json = $row['data'];
        $array = json_decode($json, true);
        foreach ($array as $kk => $value){
            if('others' === $kk) continue;
            $data[$k][$kk] = $value;
        }
        $k++;
    }

   //获得标题，并赋值给 $data[0]
    if(!empty($data)){
        $dt = $data[1];
        foreach ($dt as $t => $d){
            $data[0][] = $t;
        }
    }
    ksort($data);
    //var_dump($data);

    createXLSX($title, $data);
}

/**
$data = array(
            array('111' => "QQQ", '222' => "EEE", '33aa' => "CCC", 200),
            array("2010", "Q2", "www", 100),
            ......
        );
*/
function createXLSX($title, &$data)
{
    require_once _LIBS . 'PHPExcel/Classes/PHPExcel.php';
    require_once _LIBS . 'PHPExcel/Classes/PHPExcel/IOFactory.php';

    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->fromArray($data, null, 'A1');

    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$title.'.xlsx"');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    $objPHPExcel->disconnectWorksheets();
}