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' => '13580522501' ),
        array ( 'username' => '黄宙洪', 'mobile' => '15011700233' ),
        array ( 'username' => '谭沙', 'mobile' => '13929540712' ),
        array ( 'username' => '测试01', 'mobile' => '13000000000' ),
        array ( 'username' => '方总', 'mobile' => '13719188314' ),
        array ( 'username' => '王开盘', 'mobile' => '13632125376' ),
        array ( 'username' => '和平', 'mobile' => '13710283761' ),
    ),
    array(
        array ( 'username' => '巫敏华', 'mobile' => '13580522501', ),
        array ( 'username' => '黄宙洪', 'mobile' => '15011700233', ),
        array ( 'username' => '王模拟', 'mobile' => '13632125376', ),
        array ( 'username' => '和平', 'mobile' => '13710283761', ),
    ),
);

// 创建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)."
你好");  // 转义字符都需要用双引号包含起来


    // 合并单元格
    // $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."	");

//        $objSheet->setCellValueExplicit($mobileIdx . $row, 123456789098765432123456789, PHPExcel_Cell_DataType::TYPE_STRING);

//        $objSheet->getStyle($mobileIdx)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);


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

        $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),
            ),
        ),
    );
}