PHPexcel导入导出excel文件

抠脚强 2017-06-09

/* 
导入excel 
*/ 
function importExcel($filepath){ 
    require_once "/Libs/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel/IOFactory.php"; 
    if (!file_exists($filepath)) { 
        exit("not found {$filepath}.\n"); 
    } 
      
    $reader = PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel5格式(Excel97-2003工作簿) 
    $PHPExcel = $reader->load($filepath); // 载入excel文件 
    $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表 
    $highestRow = $sheet->getHighestRow(); // 取得总行数 
    $highestColumm = $sheet->getHighestColumn(); // 取得总列数 
     
    /*返回数据变量 */  
    $return=array( 
        "rows"=>$highestRow-1, 
        "cols"=>$highestColumm, 
        "colindex"=>strpos("ABCDEFGHIJKLMNOPQRSTUVWXYZ",$highestColumm)+1, 
        "data"=>array(), 
    ); 
    /** 循环读取每个单元格的数据 */ 
    for ($row = 2; $row <= $highestRow; $row++){//行数是以第1行开始 
        for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始 
            $dataset[] = $sheet->getCell($column.$row)->getValue(); 
            //echo $column.$row.":".$sheet->getCell($column.$row)->getValue()."<br />"; 
            $return['data'][$row-2][$column]=$sheet->getCell($column.$row)->getValue(); 
        } 
    } 
    return $return; 
} 
/* 
导出excel 
$titles为一维数组
$data为多维数组
*/ 
function exportExcel($titles=array(),$data=array(),$filename='icaodan'){ 
    require_once "/Libs/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel.php"; 
    $objPHPExcel = new PHPExcel(); 
    #设置表头 
    $rownum=1; 
    $settiele=$objPHPExcel->setActiveSheetIndex(0); 
    for($i=0;$i<count($titles);$i++){                      
        $colname=substr("ABCDEFGHIJKLMNOPQRSTUVWXYZ",$i,1); 
        $settiele->setCellValue($colname.$rownum, $titles[$i]); 
    } 
    if(count($titles)>0){$rownum++;} 
    // 设置行高度     
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); 
    foreach($data as $k => $v){ 
        $num=0; 
        foreach($v as $key=>$val){ 
            $colname=substr("ABCDEFGHIJKLMNOPQRSTUVWXYZ",$num++,1); 
            $settiele->setCellValue($colname.$rownum, $val); 
        } 
        $rownum++; 
    } 
    $objPHPExcel->getActiveSheet()->setTitle('User'); 
    $objPHPExcel->setActiveSheetIndex(0); 
    header('Content-Type: application/vnd.ms-excel'); 
    header('Content-Disposition: attachment;filename="'.$filename.'.xls"'); 
    header('Cache-Control: max-age=0'); 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
    $objWriter->save('php://output'); 
    exit; 
}

相关推荐