PHPExcel类导出excel表格

PHPExcel类导出excel表格

1. PHPExcel属性设置

2. PHPExcel工具类

   案例

<?php
    $objExecl = new PHPExcel(); //实例化一个PHPExcel的实例
    $this->objExcel->setActiveSheetIndex(); //设置要操作的sheet页
    $this->objActSheet = $this->objExcel->getActiveSheet() // 获取当前要操作的sheet页
    $objStyle = $this->objActSheet->getStyle('A1');//获取要设置单元格的样式，括号里的内容也可是：('A1:H1');
    $objAlign = $objStyle->getAlignment();//设置对齐属性和单元格内文本换行的一个变量
    $objFont = $objStyle->getFont();//获取字体属性

1.设置标题

$this->objActSheet->setTitle($title);

2.设置字体并设置字体居中加粗

$this->objActSheet->getStyle('A1')->applyFromArray(
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );//设置A1列字体加粗,水平方向居中

• 2.1 水平对齐

$objAlign->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); //设置单元格内容水平对齐

说明:

---

竖直对齐的变量有：PHPExcel_Style_Alignment::VERTICAL_TOP[顶部对齐], PHPExcel_Style_Alignment::VERTICAL_CENTER[竖直居中对齐],
PHPExcel_Style_Alignment::VERTICAL_BOTTOM[底部对齐]

• 2.2 竖直对齐

$objAlign->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

---

竖直对齐的变量有：PHPExcel_Style_Alignment::VERTICAL_TOP[顶部对齐]、
PHPExcel_Style_Alignment::VERTICAL_CENTER[竖直居中对齐]、
PHPExcel_Style_Alignment::VERTICAL_BOTTOM[底部对齐]

• 2.3 单元格内换行

$objAlign->setWrapText(true);

---

1、此举是为了实现单元格内可以手动指定换行的位置。只要指定的文本本身是换行的，或者插入换行符（’\n’）。
2、要换行的文字，外面必须是双引号
示例:

$newline = "我是小娜娜\nHelloWorld"; 
//效果
/**
我是小娜娜
HelloWorld
*/

3.设置字体,颜色

• 3.1 设置字体

$objFont->setName('微软雅黑' );　　//设置要使用的字体

• 3.2 设置字号

$objFont->setSize(12);

• 3.2 设置字颜色

$objFont->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);　　

4.合并拆分单元格

$this->objActSheet->mergeCells( 'A28:B28');// A28:B28合并
$this->objActSheet->unmergeCells( 'A28:B28');// A28:B28再拆分

5.设置单元格边框

$styleThinBlackBorderOutline = array(
       'borders' => array (
             'outline' => array (
                   'style' => PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                   //'style' => PHPExcel_Style_Border::BORDER_THICK,  另一种样式
                   'color' => array ('argb' => 'FF000000'),          //设置border颜色
            ),
      ),
);
$this->objActSheet->getStyle('A5:E10')->applyFromArray($styleThinBlackBorderOutline);

6.设置URL超链接

$this->objActSheet->getCell('A2')->getHyperlink()->setUrl('http://blog.csdn.net/u011650048'); //设置A2文本框超链接
$this->objActSheet->getCell('A2')->getHyperlink()->setTooltip('这是本人的博客地址'); //设置鼠标移上去的文本内容

项目案例分享:

$resultPHPExcel = new PHPExcel();
        $resultPHPExcel->getActiveSheet()->mergeCells( 'A1:G1');
        $resultPHPExcel->getActiveSheet()->setCellValue('A1', "设置第一行导出文件的标题");
        $objStyle = $resultPHPExcel->getActiveSheet()->getStyle('A1');
        $objFont = $objStyle->getFont();
        $objFont->setBold(true);
        $objFont->setSize(15);
        $resultPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );
        $resultPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $resultPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
        $resultPHPExcel->getActiveSheet()->setCellValue('A2', '订单号');
        $resultPHPExcel->getActiveSheet()->setCellValue('B2', '账号');
        $resultPHPExcel->getActiveSheet()->setCellValue('C2', '游戏编号');
        $resultPHPExcel->getActiveSheet()->setCellValue('D2', '区服编号');
        $resultPHPExcel->getActiveSheet()->setCellValue('E2', '产品名称');
        $resultPHPExcel->getActiveSheet()->setCellValue('F2', '订单金额');
        $resultPHPExcel->getActiveSheet()->setCellValue('G2', '下单时间');
        $objStyle = $resultPHPExcel->getActiveSheet()->getStyle('A2:G2');
        $objFont = $objStyle->getFont();
        $objFont->setBold(true);
        foreach ($info as $k => $item) {
            $resultPHPExcel->getActiveSheet()->setCellValue("A" . ($k + 3), $item['order_id']);
            $resultPHPExcel->getActiveSheet()->setCellValue("B" . ($k + 3), $item['user_id']);
            $resultPHPExcel->getActiveSheet()->setCellValue("C" . ($k + 3), $item['game_id']);
            $resultPHPExcel->getActiveSheet()->setCellValue("D" . ($k + 3), $item['server_id']);
            $resultPHPExcel->getActiveSheet()->setCellValue("E" . ($k + 3), $item['product_name']);
            $resultPHPExcel->getActiveSheet()->setCellValue("F" . ($k + 3), $item['amount'] / 100);
            $resultPHPExcel->getActiveSheet()->setCellValue("G" . ($k + 3), date('Y-m-d H:i:s', $item['timeline']));
        }
        //设置导出文件名
        $outputFileName = '自定义导出文件的名称.xls';
        $xlsWriter = new PHPExcel_Writer_Excel5($resultPHPExcel);
        header('Content-type:application/vnd.ms-excel');
        header('Content-Disposition:inline;filename="'.$outputFileName.'"');
        $xlsWriter->save( "php://output" );
        Yii::app()->end();