PHPExcel实现导入导出功能

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

下载好phpexcel解压后：

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

导入功能

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->ttitle('测试');    // 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->tcellvalue('a1', '姓名')        ->tcellvalue('b1', '性别');    $activesheet->tcellvalue('a2', '张三')        ->tcellvalue('b2', '男');    $activesheet->tcellvalue('a3', '小红')        ->tcellvalue('b3', '女');    // 根据数组创建activesheet，可以不用tcellvalue()    //$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');    }el{        // 输出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");}

样式设置

$dirna珠海天气报告me = dirname(__file__);require $dirname.'/phpexcel/phpexcel.php';$projects = array (    array(        array ( 'urname' => '张三', 'mobile' => '13500000001' ),        array ( 'urname' => '李四', 'mobile' => '15000000003' ),        array ( 'urname' => '王五', 'mobile' => '13900000002' ),        array ( 'urname' => '测试', 'mobile' => '13000000000' ),        array ( 'urname' => '老六', 'mobile' => '13700000004' ),        array ( 'urname' => '老王', 'mobile' => '13600000006' ),        array ( 'urname' => '老李', 'mobile' => '13700000001' ),    ),    array(        array ( 'urname' => '张三', 'mobile' => '13500000001', ),        array ( 'urname' => '李四', 'mobile' => '15000000003', ),        array ( 'urname' => '王和', 'mobile' => '13600000006', ),        array ( 'urname' => '老七', 'mobile' => '13700000001', ),    ),);// 创建phpexcel对象$objphpexcel = new phpexcel();// 获取活动状态的sheet$objsheet = $objphpexcel->getactivesheet();// 合并cell$objsheet->mergecells('a1:d1');// 设置$objsheet->tcellvalue('a1', '用户信息表');//设置背景颜色、边框颜色$objsheet->getstyle('a1:d1')->getfill()->tfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->trgb('00ff00');$objsheet->getstyle('a1:d1')->applyfromarray(getborderstyle('0099ff'));// 设置表格内 文字水平居中、垂直居中$objsheet->getdefaultstyle()->getalignment()->thorizontal(phpexcel_style_alignment::horizontal_center);$objsheet->getdefaultstyle()->getalignment()->tvertical(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()->twraptext(true);  //设置换行，需要写在设置cell的值之前    $objsheet->tcellvalue($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()->tfilltype(phpexcel_style_fill::fill_solid)->getstartcolor()->trgb('448cbb');    $objsheet->getstyle("{$startidx}:{$endidx}")->applyfromarray(getborderstyle('808080'));        foreach ($project as $index=>$ur) {        $row++;        // 设置用户//        $objsheet->tcellvalue($nameidx . $row, $ur['urname'])->tcellvalue($mobileidx . $row, $ur['mobile']);        $mobile = 123456789098765432123456789;        $objsheet->tcellvalue($nameidx . $row, $ur['urname'])->tcellvalue($mobileidx . $row, $mobile."\t");//        $objsheet->tcellvalueexplicit($mobileidx . $row, 123456789098765432123456789, phpexcel_cell_datatype::type_string);//        $objsheet->getstyle($mobileidx)->getnumberformat()->tformatcode(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()->tsize(20)->tbold(true);$objsheet->getstyle('a2:z2')->getfont()->tsize(15)->tbold(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),            ),        ),    );}