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