欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

PHPExcel

程序员文章站 2022-06-14 08:12:42
...

导入数据

$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();
}
相关标签: PHPExcel