* PHPExcel基本使用
* PS:文章如果有误,请及时指出,给予修改
* 项目中导入PHPExcel
* 可以去网上下载 github composer 都可以
* 为了方便下载,我将压缩包添加到了百度云 下面是地址和提取密码
* 链接:https://pan.baidu.com/s/10eaZ0-Ij6kd4jriP3QOABA 提取密码:enwi
* 踩的坑:
* 坑1:excel的单元格读取的值 可能是空字符串 使用empty()函数进行判断 返回值为true 使用时先进行trim();
* 坑2:excel单元格的值若是函数计算出的值 读取出来无法进行直接使用 需要判断
* 坑3:getOldCalculatedValue 和 getCalculatedValue , getCalculatedValue()这个方法已经不推荐使用了
$value = $objPHPExcel->getActiveSheet()->getCell($column.$row)->getValue(); if(substr($value,0,1) == '='){ $value = $objPHPExcel->getActiveSheet()->getCell($column.$row)->getOldCalculatedValue(); }
* 坑4:如果当前excel数据列数超过Z列,后续AA,AB ... 此时循环需要自己手动处理循环列数 则不能再使用 ++ 完成自动运算
* 坑5:导出默认会自己创建一个sheet,如果需要导出多个sheet,在第一个sheet后再执行 $objPHPExcel->createSheet();
* 坑6:设置单元格不以科学技术法显示不生效,强制将数字字符串转换为字符串,拼接空格即可
* 坑7:不要闲麻烦,每个取值和不确定的数据 都使用isset()进行判断 ,很有可能当前取得列,其他sheet页没有
* 坑8:最好在方法开始的最外层,增加异常的捕获.不然程序异常影响用户体验
* 坑9:在require引入PHPExcel扩展的时候,确保文件夹有可读权限
* excel文件上传 : 由于无法在上传的时候 直接读取文件的值 只有先将文件存储后使用 使用完成后删除即可[使用:unlink()]
* laravel 框架自带文件存储 使用也相当简单
$request = new Request();
* 两种存储文件方式 store 和 storeAs
* store 方法接受相对于文件系统配置的存储文件根目录的路径。这个路径不能包含文件名，因为系统会自动生成唯一的 ID 作为文件名。
* 该方法还接受可选的第二个参数，用于存储文件的磁盘名称。这个方法会返回相对于磁盘根目录的文件路径
$path = $request->file->store('xxx'); $path = $request->file->store('xxx', 's3');
* storeAs 方法，它接受路径、文件名和磁盘名
$path = $request->file->storeAs($save_path, $tmpName);
* 解释为什么中间为file 是因为传递参数 name为file 如果是其他 则换为传递的name即可
* storeAs只能将文件存储到storage文件夹下 如果要自定义文件存储除storage以外的路径
* 使用PHP原生处理文件机制:move_uploaded_file 若成功，则返回 true，否则返回 false。
$bool = move_uploaded_file($_FILES["file"]["tmp_name"], '$save_path);//$save_path 为文件写入路径
*
* 使用:
* 项目中引用路径
require_once './PHPExcel/PHPExcel.php';
* 导出:PHPExcel实例化对象
* 导入:PHPExcel_IOFactory实例化对象
$objPHPExcel = new PHPExcel(); $objPHPExcel = PHPExcel_IOFactory::load($path); //$path为excel文件绝对路径
*******************获取表格内容--使用时根据实际内容做修改******************************
/** * 获取excel信息和内容 TODO:使用时根据实际业务逻辑进行逻辑调整 * @param string $path * @param bool $isReadContent * @return array */ private static function _getExcelInfoAndContent($path = '',$isReadContent = false) { $info = array(); //文件内容获取 $objPHPExcel = PHPExcel_IOFactory::load($path); //获取sheet表格数目 $info['sheetCount'] = $objPHPExcel->getSheetCount(); //获取sheet表格名称 $info['sheetName'] = $objPHPExcel->getSheetNames(); $totalRowCount = 0; foreach ($info['sheetName'] as $k => $v){ $objPHPExcel->setActiveSheetIndex($k); //获取表格行数 $rowCount = $objPHPExcel->getActiveSheet()->getHighestRow(); $info['sheetRows'][$k] = rowCount == 0 ? 0 : $rowCount; $totalRowCount += $info['sheetRows'][$k]; if($isReadContent){ //获取表格列数 $columnCount = $objPHPExcel->getActiveSheet()->getHighestColumn(); //如果超出范围 只读取到Z列 TODO:如果需要读取到AA及以后列,请自行处理 if(strlen($columnCount)>=2){ $columnCount = 'Z'; } $dataArr = array(); $data = array(); //行数循环 for ($row = 1; $row <= $rowCount; $row++){ //列数循环 , 列数是以A列开始 for ($column = 'A'; $column <= $columnCount; $column++) { $dataArr[$column] = $objPHPExcel->getActiveSheet()->getCell($column.$row)->getValue(); //查看是否为公式取值 if(substr($dataArr[$column],0,1) == '='){ $dataArr[$column] = $objPHPExcel->getActiveSheet()->getCell($column.$row)->getOldCalculatedValue(); } } $data[$row] = $dataArr; $dataArr = NULL; } //默认对应的k对应sheet表名 $info['data'][$k] = $data; }else{ $info['data'][$k] = array(); } } $info['totalRowsCount'] = $totalRowCount; return $info; }
********************写入表格内容--使用时根据实际内容做修改***************************
/** * 导出数据 TODO:使用时根据实际业务逻辑进行逻辑调整 * @param $fileName 文件名 * @param $columnName 设置的列名 (数组) * @param $expTableData 导出的data */ private static function _exportExcel($fileName,$columnName,$expTableData){ $objPHPExcel = new PHPExcel(); $sheets = $expTableData['sheets']; $data = $expTableData['data']; foreach ($sheets as $k => $v){ if($k != 0){ $objPHPExcel->createSheet(); } //设置sheet名称 $objPHPExcel->setactivesheetindex($k)->setTitle($v['name']); //示例:设置宽度 $objPHPExcel->setActiveSheetIndex($k)->getColumnDimension('C')->setWidth(25); //行数计数器 逻辑默认有主标题和列名 根据实际情况修改 $row = 3; $status = false; if(!empty($data) && is_array($data) && count($data) > 0){ foreach ($data as $mk => $mv){ foreach ($columnName as $ck => $cv) { if($mk == 0) { //合并,设置主标题 $objPHPExcel->getActiveSheet($k)->mergeCells('A1:Y1')->setCellValue('A1','信息登记表')->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置标题 $objPHPExcel->getActiveSheet($k)->setCellValue($ck . $columnName[$ck]['num'], $columnName[$ck]['name']); } //判断是否属于该组 if($mv['sheet_sheet_id'] == $v['id']){ //获取当前单元格的值,TODO:_getCurrentCellValue方法为自己封装 处理当前单元格数据 <自己写哦> $value = self::_getCurrentCellValue($ck,$mv,$row-2); //拼接数据 $objPHPExcel->getActiveSheet($k)->setCellValue($ck . $row, $value); $status = true; } } if($status){ $row++; $status = false; } } }else{ foreach ($columnName as $ck => $cv) { //合并,设置主标题 $objPHPExcel->getActiveSheet($k)->mergeCells('A1:Y1')->setCellValue('A1','信息登记表')->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置标题 $objPHPExcel->getActiveSheet($k)->setCellValue($ck . $columnName[$ck]['num'], $columnName[$ck]['name']); } } } 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=".$fileName.".xls"); header('Content-Transfer-Encoding:binary'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); }
****************************经常使用方法**************************************
//获取sheet表格数目 $objPHPExcel->getSheetCount(); //获取sheet表格名称 $objPHPExcel->getSheetNames(); //设置$k为活动sheet $k 为正整数 [0,+∞) $objPHPExcel->setActiveSheetIndex($k); //获取表格行数 $objPHPExcel->getActiveSheet()->getHighestRow();//若没有设置活动sheet 那么以下的操作都需要getActiveSheet($k) //获取表格列数 $objPHPExcel->getActiveSheet()->getHighestColumn(); //获取单元格值(无公式) $column -> 列 $row -> 行 如:A2 $objPHPExcel->getActiveSheet()->getCell($column.$row)->getValue(); //获取单元格值(有公式) $column -> 列 $row -> 行 如:A2 $objPHPExcel->getActiveSheet()->getCell($column.$row)->getOldCalculatedValue();//getCalculatedValue新版本已不推荐使用 //创建sheet $objPHPExcel->createSheet(); //设置sheet名称 $objPHPExcel->setactivesheetindex($k)->setTitle('sheetDIY'); //设置cell宽度 //$column : 列 如:A $objPHPExcel->setActiveSheetIndex($k)->getColumnDimension($column)->setWidth(25); //指定宽度 $objPHPExcel->setActiveSheetIndex($k)->getColumnDimension($column)->setAutoSize(true); //自动调整 //设置行高度 $objPHPExcel->getActiveSheet($k)->getDefaultRowDimension($column)->setRowHeight(30); //设置单元格不以科学计数法显示 如果没有生效 将过长字符串拼接空格即可 $objPHPExcel->setActiveSheetIndex($k)->getStyle($column)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT); //分离单元格 $objPHPExcel->getActiveSheet($k)->unmergeCells('A1:E22'); //合并单元格 $objPHPExcel->getActiveSheet($k)->mergeCells('A1:Y1')->setCellValue('A1','信息登记表'); //合并单元格居中显示 $objPHPExcel->getActiveSheet($k)->mergeCells('A1:Y1')->setCellValue('A1','信息登记表')->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //单元格设置值 $column:列 , $row:行 如:A2 $objPHPExcel->getActiveSheet($k)->setCellValue($column . $row, $value); //写入数据导出内容 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output');
****************************导出header头**************************************
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=".$fileName.".xls"); header('Content-Transfer-Encoding:binary'); //如果不使用PHPExcel导出 则使用readfile($filePath) //$filePath:需要导出文件路径
***************************其他方法********************************************
//Set properties 设置文件属性 $objPHPExcel->getProperties()->setCreator("Maarten Balliauw"); $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw"); $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"); //日期格式化 $objPHPExcel->getActiveSheet()->setCellValue('D1', time()); $objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); //Add comment 添加注释 $objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel'); $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:'); $objCommentRichText->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun(" "); $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.'); //Set fonts 设置字体 $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); //Set alignments 设置对齐 $objPHPExcel->getActiveSheet()->getStyle('D11')->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); $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true); //Set column borders 设置列边框 $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('A10')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('E10')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK); //Set border colors 设置边框颜色 $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()->getRight()->getColor()->setARGB('FF993300'); //Set fills 设置填充 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); //Add a hyperlink to the sheet 添加链接 $objPHPExcel->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net'); $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('http://www.phpexcel.net'); $objPHPExcel->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website'); $objPHPExcel->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //Add a drawing to the worksheet 添加图片 $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing->setName('Logo'); $objDrawing->setDescription('Logo'); $objDrawing->setPath('./images/officelogo.jpg'); $objDrawing->setHeight(36); $objDrawing->setCoordinates('B15'); $objDrawing->setOffsetX(110); $objDrawing->setRotation(25); $objDrawing->getShadow()->setVisible(true); $objDrawing->getShadow()->setDirection(45); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //inserting and removing rows and columns 插入/删除 行/列 $objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10); $objPHPExcel->getActiveSheet()->removeRow(6, 10); $objPHPExcel->getActiveSheet()->insertNewColumnBefore('E', 5); $objPHPExcel->getActiveSheet()->removeColumn('E', 5); //Add conditional formatting 添加条件格式 $objConditional1 = new PHPExcel_Style_Conditional(); $objConditional1->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS); $objConditional1->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN); $objConditional1->setCondition('0'); $objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); $objConditional1->getStyle()->getFont()->setBold(true); //Set autofilter 自动过滤 $objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9'); //Hide "Phone" and "fax" column 隐藏列 $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false); //Set document security 设置文档安全 $objPHPExcel->getSecurity()->setLockWindows(true); $objPHPExcel->getSecurity()->setLockStructure(true); $objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel"); //Set sheet security 设置工作表安全 $objPHPExcel->getActiveSheet()->getProtection()->setPassword('PHPExcel'); $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);// This should be enabled in order to enable any of the following! $objPHPExcel->getActiveSheet()->getProtection()->setSort(true); $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true); $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true); //Calculated data 计算 echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . " "; //Set outline levels $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true); //Set data validation 验证输入值 $objValidation = $objPHPExcel->getActiveSheet()->getCell('B3')->getDataValidation(); $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_WHOLE ); $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP ); $objValidation->setAllowBlank(true); $objValidation->setShowInputMessage(true); $objValidation->setShowErrorMessage(true); $objValidation->setErrorTitle('Input error'); $objValidation->setError('Number is not allowed!'); $objValidation->setPromptTitle('Allowed input'); $objValidation->setPrompt('Only numbers between 10 and 20 are allowed.'); $objValidation->setFormula1(10); $objValidation->setFormula2(20); $objPHPExcel->getActiveSheet()->getCell('B3')->setDataValidation($objValidation); //Create a new worksheet, after the default sheet 创建新的工作标签 $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex(1); //Set header and footer. When no different headers for odd/even are used, odd header is assumed. 页眉页脚 $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&HPlease treat this document as confidential!'); $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N'); //Set page orientation and size 方向大小 $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); //Rename sheet 重命名工作表标签 $objPHPExcel->getActiveSheet()->setTitle('Simple'); //设置为当前活动sheet $objPHPExcel->setActiveSheetIndex(0); //Save Excel 2007 file 保存 $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); //Save Excel 5 file 保存 require_once('Classes/PHPExcel/Writer/Excel5.php'); $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save(str_replace('.php', '.xls', __FILE__)); //1.6.2新版保存 require_once('Classes/PHPExcel/IOFactory.php'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save(str_replace('.php', '.xls', __FILE__)); //读excel //Include class require_once('Classes/PHPExcel/Reader/Excel2007.php'); $objReader = new PHPExcel_Reader_Excel2007; $objPHPExcel = $objReader->load("05featuredemo.xlsx"); //读写csv require_once("05featuredemo.inc.php"); require_once('Classes/PHPExcel/Writer/CSV.php'); require_once('Classes/PHPExcel/Reader/CSV.php'); require_once('Classes/PHPExcel/Writer/Excel2007.php'); //Write to CSV format 写 $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $objWriter->setDelimiter(';'); $objWriter->setEnclosure(''); $objWriter->setLineEnding(" "); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.php', '.csv', __FILE__)); //Read from CSV format 读 $objReader = new PHPExcel_Reader_CSV(); $objReader->setDelimiter(';'); $objReader->setEnclosure(''); $objReader->setLineEnding(" "); $objReader->setSheetIndex(0); $objPHPExcelFromCSV = $objReader->load(str_replace('.php', '.csv', __FILE__)); //Write to Excel2007 format $objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV); $objWriter2007->save(str_replace('.php', '.xlsx', __FILE__)); 写html require_once("05featuredemo.inc.php"); require_once('Classes/PHPExcel/Writer/HTML.php'); //Write to HTML format $objWriter = new PHPExcel_Writer_HTML($objPHPExcel); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.php', '.htm', __FILE__)); //写pdf require_once("05featuredemo.inc.php"); require_once('Classes/PHPExcel/IOFactory.php'); //Write to PDF format $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF'); $objWriter->setSheetIndex(0); $objWriter->save(str_replace('.php', '.pdf', __FILE__)); //Echo memory peak usage echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB ";
-------------- 全文end -------------