PHPExcel实现导入导出功能

之前做应用后台的时候，都是用的现有后台框架的导入导出功能，这次由于需要对导出的excel做特殊的要求，所以研究了下phpexcel，并整理了一下，结合网上的例子和官方文档整理了导入、导出的方法，并对常用的样式做了一份总结。

下载好phpexcel解压后：

documentation/phpexcel function reference developer documentation.doc是phpexcel详细的文档，如果有什么特定的样式需求都可以在上面找到。将classes目录下文件拖入项目即可。

导入功能

function importexcel($filename = ''){
    $filename = iconv('utf-8', 'gb2312', $filename);

    if (empty($filename) || !file_exists($filename)){
        die('file not exists');
    }

    // 兼容 excel03 和 excel07
    $objread = new phpexcel_reader_excel2007();
    if (!$objread->canread($filename)){
        $objread = new phpexcel_reader_excel5();
        if (!$objread->canread($filename)){
            die('no excel');
        }
    }

    // 获取 excel 中的文件内容
    $objphpexcel = phpexcel_iofactory::load($filename);

    // 一次性取出整个 sheet 内的内容（不常用）
//    $sheetcount = $objphpexcel->getsheetcount();
//    for ($i=0; $i<$sheetcount; $i++){
//        $data = $objphpexcel->getsheet($i)->toarray();
//        var_dump($data);
//    }

    // 逐行读取 sheet 内的内容（常用）
    foreach ($objphpexcel->getworksheetiterator() as $sheet){   //循环sheet
        foreach ($sheet->getrowiterator() as $row){     //循环row
            if ($row->getrowindex() == 1){  // 默认从第二行开始
                continue;
            }

            foreach ($row->getcelliterator() as $cell){     //循环cell
                $data = $cell->getvalue();
                echo $data;
            }
        }
    }
}

导出功能

function exportexcel($filename = '', $type = 'excel5'){
    $phpexcel = new phpexcel();

    // 获取当前活动sheet操作对象
    $activesheet = $phpexcel->getactivesheet();

    // 设置sheet标题
    $activesheet->settitle('测试');

    // excel文件中，横坐标依次是：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');
    // 纵坐标是：1, 2, 3, 4, 5, 6, 7, 8...
    // 所以如果具体设置某个行列的值时：a8、b9这种

    // 填充数据
    $activesheet->setcellvalue('a1', '姓名')
        ->setcellvalue('b1', '性别');
    $activesheet->setcellvalue('a2', '张三')
        ->setcellvalue('b2', '男');
    $activesheet->setcellvalue('a3', '小红')
        ->setcellvalue('b3', '女');

    // 根据数组创建activesheet，可以不用setcellvalue()
    //$dataarr = array(
    //    [
    //
    //    ]
    //);
    //$activesheet->fromarray();
    
    // 生成excel文件
    $objwriter = phpexcel_iofactory::createwriter($phpexcel, $type);
    //$objwriter->save('list.xls'); //保存到本地

    if ($type == 'excel5'){
        // 输出excel03文件
        header('content-type: application/vnd.ms-excel');
    }else{
        // 输出excel07文件
        header('content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    }

    header('content-disposition: attachment;filename="'.$filename.'"'); //
    header('cache-control: max-age=0'); // 禁止缓存

    // "php://output"：只写数据流, 允许你以 print 和echo一样的方式写入到输出缓冲区
    $objwriter->save("php://output");
}

样式设置

$dirname = dirname(__file__);
require $dirname.'/phpexcel/phpexcel.php';

$projects = array (
    array(
        array ( 'username' => '张三', 'mobile' => '13500000001' ),
        array ( 'username' => '李四', 'mobile' => '15000000003' ),
        array ( 'username' => '王五', 'mobile' => '13900000002' ),
        array ( 'username' => '测试', 'mobile' => '13000000000' ),
        array ( 'username' => '老六', 'mobile' => '13700000004' ),
        array ( 'username' => '老王', 'mobile' => '13600000006' ),
        array ( 'username' => '老李', 'mobile' => '13700000001' ),
    ),
    array(
        array ( 'username' => '张三', 'mobile' => '13500000001', ),
        array ( 'username' => '李四', 'mobile' => '15000000003', ),
        array ( 'username' => '王和', 'mobile' => '13600000006', ),
        array ( 'username' => '老七', 'mobile' => '13700000001', ),
    ),
);

// 创建phpexcel对象
$objphpexcel = new phpexcel();

// 获取活动状态的sheet
$objsheet = $objphpexcel->getactivesheet();

// 合并cell
$objsheet->mergecells('a1:d1');

// 设置
$objsheet->setcellvalue('a1', '用户信息表');

//设置背景颜色、边框颜色
$objsheet->getstyle('a1:d1')->getfill()->setfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->setrgb('00ff00');
$objsheet->getstyle('a1:d1')->applyfromarray(getborderstyle('0099ff'));

// 设置表格内 文字水平居中、垂直居中
$objsheet->getdefaultstyle()->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center);
$objsheet->getdefaultstyle()->getalignment()->setvertical(phpexcel_style_alignment::vertical_center);

foreach ($projects as $k=>$project){
    $row = 2; // 从第二行开始循环

    $nameidx = getcellidx($k * 2);
    $mobileidx = getcellidx($k *2 + 1);

    // 设置 项目标题
    $objsheet->getstyle($nameidx)->getalignment()->setwraptext(true);  //设置换行，需要写在设置cell的值之前
    $objsheet->setcellvalue($nameidx.$row, '项目'.($k+1)."\n你好");  // 转义字符都需要用双引号包含起来


    // 合并单元格
    // $objphpexcel->getactivesheet()->mergecells('a18:e22');
    $startidx = $nameidx.$row;
    $endidx = $mobileidx.$row;
    $objsheet->mergecells("{$startidx}:{$endidx}");

    $objsheet->getstyle("{$startidx}:{$endidx}")->getfill()->setfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->setrgb('448cbb');

    $objsheet->getstyle("{$startidx}:{$endidx}")->applyfromarray(getborderstyle('808080'));
    

    foreach ($project as $index=>$user) {
        $row++;
        // 设置用户
//        $objsheet->setcellvalue($nameidx . $row, $user['username'])->setcellvalue($mobileidx . $row, $user['mobile']);
        $mobile = 123456789098765432123456789;
        $objsheet->setcellvalue($nameidx . $row, $user['username'])->setcellvalue($mobileidx . $row, $mobile."\t");

//        $objsheet->setcellvalueexplicit($mobileidx . $row, 123456789098765432123456789, phpexcel_cell_datatype::type_string);

//        $objsheet->getstyle($mobileidx)->getnumberformat()->setformatcode(phpexcel_style_numberformat::format_text);


        //pvalue 数字过长，如果是文本则显示全，如果是数字则显示不全，可以在文本后面加上"\t"来实现

        $objsheet->getstyle($nameidx . $row)->applyfromarray(getborderstyle('808080'));
        $objsheet->getstyle($mobileidx . $row)->applyfromarray(getborderstyle('808080'));

    }
}

// 设置文字粗细
$objsheet->getstyle('a1:z1')->getfont()->setsize(20)->setbold(true);
$objsheet->getstyle('a2:z2')->getfont()->setsize(15)->setbold(true);

$objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5');
$filename = '用户.xls';

header('content-type: application/vnd.ms-excel');
header('content-disposition: attachment;filename="'.$filename.'"'); //
header('cache-control: max-age=0'); // 禁止缓存

$objwriter->save("php://output");

function getcellidx($key){
    $header_arr = range('a', 'z');
    return $header_arr[$key];
}

function getborderstyle($color){
    return array(
        'borders' => array(
            'outline' => array(
                'style' => phpexcel_style_border::border_thick,
                'color' => array('rgb' => $color),
            ),
        ),
    );
}