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

最新推荐文章于 2021-03-12 08:01:13 发布

ZhiHuaWei

最新推荐文章于 2021-03-12 08:01:13 发布

阅读量921

点赞数

分类专栏： CodeIgniter 文章标签： CI框架整合PHPExcel PHPExcel操作方法如何解决PHPExcel 长数字串显示为

本文链接：https://blog.csdn.net/zhihua_w/article/details/78104099

版权

CodeIgniter 专栏收录该内容

28 篇文章 4 订阅

订阅专栏

本文讲的是解析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);

ZhiHuaWei

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
CI框架教程5——整合PHPExcel库应用

本文讲的是解析PHPExcel使用的常用说明以及把PHPExcel整合进CI框架的介绍和excel的写入与生成操作，以及当写入一串数字字符串是变成科学计数法的解决方法。1、CI框架整合PHPExcel方法步骤： Ⅰ：下载 PHPExcel http://phpexcel.codeplex.com/releases/view/107442
复制链接

扫一扫