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();