CI框架教程5——整合PHPExcel库应用

来源：互联网发布：万网维启网络编辑：程序博客网时间：2024/05/16 16:10

本文讲的是解析PHPExcel使用的常用说明以及把PHPExcel整合进CI框架的介绍和excel的写入与生成操作，以及当写入一串数字字符串是变成科学计数法的解决方法。

1、CI框架整合PHPExcel方法

步骤：

Ⅰ：下载 PHPExcel http://phpexcel.codeplex.com/releases/view/107442；
Ⅱ：将下载完成的 PHPExcel 文件夹和 PHPexcel.php 文件放在项目中的 libraries 里面；
Ⅲ：修改application/libraries/PHPExcel/IOFactory.php文件：将其类名从PHPExcel_IOFactory改为IOFactory，遵从CI类命名规则；将其构造函数改为public；
Ⅳ：将 Iofactory 类里的构造函数改为public。

使用代码（在CI框架控制器里面写方法）：

    public function testPHPExcel()    {        //加载PHPExcel的类        $this->load->library('PHPExcel');        $this->load->library('PHPExcel/IOFactory');        //创建PHPExcel实例        $excel = new PHPExcel();        //下面介绍项目中用到的几个关于excel的操作        $charActors = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K');        $widthSize = array(5, 10, 30, 12, 16, 28, 18, 18, 20, 30, 45);        $titleName = array('ID', '姓名', '身份证号码', '联系方式', '申请提交日期', '360骑卫士设备IMEI编号', '电机号', '车辆品牌', '车架号', '所属保险公司', '个人图片文件夹路径');        foreach ($charActors as $k => $v) {            //对齐方式，水平剧中            $excel->getActiveSheet()->getStyle($v)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);            //设置表格宽度            $excel->getActiveSheet()->getColumnDimension($v)->setWidth($widthSize[$k]);            //设置单元格为文本            $excel->getActiveSheet()->getStyle($v)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);            //为单元格赋值            $excel->getActiveSheet()->setCellValue($v . 1, $titleName[$k]);        }        $a = 2;        for ($i = 0; $i <= 10; $i++) {            $excel->getActiveSheet()->setCellValue('A' . $a, $i);            $excel->getActiveSheet()->setCellValue('B' . $a, $i . '姓名');            $excel->getActiveSheet()->setCellValue('C' . $a, '身份证号码' . $i);            $excel->getActiveSheet()->setCellValue('D' . $a, time());            $excel->getActiveSheet()->setCellValue('E' . $a, date("Y-m-d"));            $excel->getActiveSheet()->setCellValue('F' . $a, $i . '这是id');            $excel->getActiveSheet()->setCellValue('G' . $a, $i . '电机号');            $excel->getActiveSheet()->setCellValue('H' . $a, $i . '车辆品牌');            $excel->getActiveSheet()->setCellValue('I' . $a, $i . '车架号');            $excel->getActiveSheet()->setCellValue('J' . $a, $i . '车架号');            $excel->getActiveSheet()->setCellValue('K' . $a, $i . '个人图片文件夹路径');            $a++;        }        //输出到浏览器        $write = new PHPExcel_Writer_Excel2007($excel);        $file_name = date("YmdHis");        header("Pragma: public");        header("Expires: 0");        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");        header("Content-Type:application/force-download");        header("Content-Type:application/vnd.ms-execl");        header("Content-Type:application/octet-stream");        header("Content-Type:application/download");        header('Content-Disposition:attachment;filename="' . $file_name . '.xlsx"');        header("Content-Transfer-Encoding:binary");        $write->save('php://output');    }

访问调用即可。

2、PHPExcel的写入与生成操作

各种代码如下：

        //设置excel的属性：        //创建人        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");        //最后修改人        $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");        //标题        $objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");        //题目        $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");        //描述        $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");        //关键字        $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");        //种类        $objPHPExcel->getProperties()->setCategory("Test result file");        //设置当前的sheet        $objPHPExcel->setActiveSheetIndex(0);        //设置sheet的name        $objPHPExcel->getActiveSheet()->setTitle('Simple');        //设置单元格的值        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');        $objPHPExcel->getActiveSheet()->setCellValue('A2', 12);        $objPHPExcel->getActiveSheet()->setCellValue('A3', true);        $objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');        $objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');        //合并单元格        $objPHPExcel->getActiveSheet()->mergeCells('A18:E22');        //分离单元格        $objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');        //保护cell        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!        $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');        //设置格式        // Set cell number formats        echo date('H:i:s') . " Set cell number formats\n";        $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);        $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13');        //设置宽width        // Set column widths        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);        //设置font        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);        $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);        $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);        //设置align        $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);        $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);        $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);        //垂直居中        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);        //设置column的border        $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);        $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);        $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);        $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);        $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);        //设置border的color        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');        //设置填充颜色        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');        //加图片        $objDrawing = new PHPExcel_Worksheet_Drawing();        $objDrawing->setName('Logo');        $objDrawing->setDescription('Logo');        $objDrawing->setPath('./images/officelogo.jpg');        $objDrawing->setHeight(36);        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());        $objDrawing = new PHPExcel_Worksheet_Drawing();        $objDrawing->setName('Paid');        $objDrawing->setDescription('Paid');        $objDrawing->setPath('./images/paid.png');        $objDrawing->setCoordinates('B15');        $objDrawing->setOffsetX(110);        $objDrawing->setRotation(25);        $objDrawing->getShadow()->setVisible(true);        $objDrawing->getShadow()->setDirection(45);        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());        //处理中文输出问题        //需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理：         $str = iconv('gb2312', 'utf-8', $str);

3、解决PHPExcel 长数字串显示为科学计数

解决方案：在数字字符串前加一个空格使之成为字符串即可。

        $objPHPExcel = new PHPExcel();        $objPHPExcel->setActiveSheetIndex(0);        $objPHPExcel->getActiveSheet()->setTitle('Simple');        $objPHPExcel->getActiveSheet()->setCellValue('D1', ' ' . 123456789033);

阅读全文

0 0