-
PHPExcel-设置表格字体颜色背景样式、数据格式、对齐方式、添加图片、批注、文字块、合并拆分单元格、单元格密码保护
- 首先到phpexcel官网上下载最新的phpexcel类,下周解压缩一个classes文件夹,里面包含了PHPExcel.php和PHPExcel的文件夹,这个类文件和文件夹是我们需要的,把classes解压到你项目的一个目录中,重名名为phpexcel,开始喽,(代码都摘自自带实例)
-
- 程序部分
-
- require_once './phpexcel/PHPExcel.php';
-
- $objPHPExcel = new PHPExcel();
-
- $objPHPExcel
- ->getProperties()
- ->setCreator( "Maarten Balliauw")
- ->setLastModifiedBy( "Maarten Balliauw")
- ->setTitle( "Office 2007 XLSX Test Document" )
- ->setSubject( "Office 2007 XLSX Test Document" )
- ->setDescription( "Test document for Office 2007 XLSX, generated using PHP classes.")
- ->setKeywords( "office 2007 openxml php")
- ->setCategory( "Test result file");
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue( 'A1', 'Hello' )
- ->setCellValue( 'B2', 'world!' )
- ->setCellValue( 'C1', 12)
- ->setCellValue( 'D2', 12)
- ->setCellValue( 'D3', true )
- ->setCellValue( 'D4', '=SUM(C1:D2)' );
-
- $objActSheet = $objPHPExcel->getActiveSheet();
- $objActSheet->setTitle('Simple2222');
- 代码还没有结束,可以复制下面的代码来决定我们将要做什么
-
- 我们将要做的是
- 1,直接生成一个文件
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
- $objWriter->save('myexchel.xlsx');
-
- 2、提示下载文件
- excel 2003 .xls
- header('Content-Type: application/vnd.ms-excel');
- header('Content-Disposition: attachment;filename="01simple.xls"');
- header('Cache-Control: max-age=0');
-
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save('php://output');
- exit;
-
- excel 2007 .xlsx
- header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
- header('Content-Disposition: attachment;filename="01simple.xlsx"');
- header('Cache-Control: max-age=0');
-
- $objWriter = PHPExcel_IOFactory:: createWriter($objPHPExcel, 'Excel2007');
- $objWriter->save( 'php://output');
- exit;
-
- pdf 文件
- header('Content-Type: application/pdf');
- header('Content-Disposition: attachment;filename="01simple.pdf"');
- header('Cache-Control: max-age=0');
-
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
- $objWriter->save('php://output');
- exit;
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
- $objWriter->save('a.pdf');
-
-
- CSV 文件
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')->setDelimiter(',' )
- ->setEnclosure('"' )
- ->setLineEnding("
" )
- ->setSheetIndex(0)
- ->save(str_replace('.php' , '.csv' , __FILE__));
-
- HTML 文件
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
- $objWriter->setSheetIndex(0);
- $objWriter->save(str_replace('.php', '.htm', __FILE__));
-
-
-
-
- 设置表格样式和数据格式
- 设置默认的字体和文字大小 锚:aaa
- $objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');
- $objPHPExcel->getDefaultStyle()->getFont()->setSize(20);
-
- 日期格式 锚:bbb
- $dateTimeNow = time();
- $objActSheet->setCellValue( 'C9', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
- $objActSheet->getStyle( 'C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
- $objActSheet->setCellValue( 'C10', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
- $objActSheet->getStyle( 'C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
- $objActSheet->setCellValue( 'C10', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
- $objActSheet->getStyle( 'C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
- $objPHPExcel->getActiveSheet()->getStyle( 'E4:E13')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
-
- 设置列的宽度 锚:bbb
- $objActSheet->getColumnDimension( 'B')->setAutoSize(true);
- $objActSheet->getColumnDimension( 'A')->setWidth(30);
-
- 设置文件打印的页眉和页脚 锚:bbb
- $objActSheet->getHeaderFooter()->setOddHeader( '&L&G&C&HPlease treat this document as confidential!');
- $objActSheet->getHeaderFooter()->setOddFooter( '&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N' );
-
- 设置页面文字的方向和页面大小 锚:bbb
- $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup:: ORIENTATION_LANDSCAPE);
- $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup:: PAPERSIZE_A4);
-
- 为页眉添加图片 office中有效 wps中无效 锚:bbb
- $objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing();
- $objDrawing->setName('PHPExcel logo');
- $objDrawing->setPath('./images/phpexcel_logo.gif');
- $objDrawing->setHeight(36);
- $objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT );
-
- 设置单元格的批注 锚:bbb
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->setAuthor('PHPExcel' );
- $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E13' )->getText()->createTextRun('PHPExcel:');
- $objCommentRichText->getFont()->setBold( true);
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->getText()->createTextRun("
" );
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.' );
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->setWidth('100pt' );
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->setHeight('100pt' );
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->setMarginLeft('150pt' );
- $objPHPExcel->getActiveSheet()->getComment( 'E13')->getFillColor()->setRGB('EEEEEE' );
-
- 添加文字块 看效果图 office中有效 wps中无效 锚:bbb
- $objRichText = new PHPExcel_RichText();
- $objRichText->createText('This invoice is ');
- $objPayable = $objRichText->createTextRun( 'payable within thirty days after the end of the month');
- $objPayable->getFont()->setBold( true);
- $objPayable->getFont()->setItalic( true);
- $objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
- $objRichText->createText(', unless specified otherwise on the invoice.');
- $objPHPExcel->getActiveSheet()->getCell( 'A18')->setValue($objRichText);
-
- 合并拆分单元格 锚:bbb
- $objPHPExcel->getActiveSheet()->mergeCells( 'A28:B28');
- $objPHPExcel->getActiveSheet()->unmergeCells( 'A28:B28');
-
- 单元格密码保护 锚:bbb
- $objPHPExcel->getActiveSheet()->getProtection()->setSheet( true);
- $objPHPExcel->getActiveSheet()->protectCells( 'A3:E13', 'PHPExcel' );
- $objPHPExcel->getActiveSheet()->getStyle( 'B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
-
- 设置单元格字体 锚:bbb
- $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);
-
- 文字对齐方式 锚:bbb
- $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);
-
- 设置单元格边框 锚:bbb
- $styleThinBlackBorderOutline = array(
- 'borders' => array (
- 'outline' => array (
- 'style' => PHPExcel_Style_Border::BORDER_THIN,
-
- 'color' => array ('argb' => 'FF000000'),
- ),
- ),
- );
- $objPHPExcel->getActiveSheet()->getStyle( 'A4:E10')->applyFromArray($styleThinBlackBorderOutline);
-
- 背景填充颜色 锚:bbb
- $objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
- $objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFill()->getStartColor()->setARGB('FF808080');
-
- 综合设置样例
- $objPHPExcel->getActiveSheet()->getStyle( 'A3:E3')->applyFromArray(
- array(
- 'font' => array (
- 'bold' => true
- ),
- 'alignment' => array (
- 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT ,
- ),
- 'borders' => array (
- 'top' => array (
- 'style' => PHPExcel_Style_Border::BORDER_THIN
- )
- ),
- 'fill' => array (
- 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR ,
- 'rotation' => 90,
- 'startcolor' => array (
- 'argb' => 'FFA0A0A0'
- ),
- 'endcolor' => array (
- 'argb' => 'FFFFFFFF'
- )
- )
- )
- );
-
-
- 给单元格内容设置url超链接 锚:bbb
- $objActSheet->getCell('E26')->getHyperlink()->setUrl( 'http://www.phpexcel.net'); //超链接url地址
- $objActSheet->getCell('E26')->getHyperlink()->setTooltip( 'Navigate to website');
-
- 给表中添加图片 锚:bbb
- $objDrawing = new PHPExcel_Worksheet_Drawing();
- $objDrawing->setName('Paid');
- $objDrawing->setDescription('Paid');
- $objDrawing->setPath('./images/paid.png');
- $objDrawing->setCoordinates('B15');
- $objDrawing->setOffsetX(210);
- $objDrawing->setRotation(25);
- $objDrawing->setHeight(36);
- $objDrawing->getShadow()->setVisible (true );
- $objDrawing->getShadow()->setDirection(45);
- $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
-
- 创建一个新工作表和设置工作表标签颜色 锚:bbb
- $objExcel->createSheet();
- $objPHPExcel->setActiveSheetIndex(1);
- $objExcel->getSheet(1)->setTitle( '测试2');
- $objPHPExcel->getActiveSheet()->getTabColor()->setARGB( 'FF0094FF');
-
- 添加或删除行和列 锚:bbb
- $objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
- $objPHPExcel->getActiveSheet()->removeRow(6, 10);
- $objPHPExcel->getActiveSheet()->insertNewColumnBefore( 'E', 5);
- $objPHPExcel->getActiveSheet()->removeColumn( 'E', 5);
-
- 隐藏和显示某列 锚:bbb
- $objPHPExcel->getActiveSheet()->getColumnDimension( 'C')->setVisible(false);
- $objPHPExcel->getActiveSheet()->getColumnDimension( 'D')->setVisible(true);
-
- 重新命名活动的表的标签名称 锚:bbb
- $objPHPExcel->getActiveSheet()->setTitle( 'Invoice');
-
- 设置工作表的安全
- $objPHPExcel->getActiveSheet()->getProtection()->setPassword( 'PHPExcel');
- $objPHPExcel->getActiveSheet()->getProtection()->setSheet( true);
- $objPHPExcel->getActiveSheet()->getProtection()->setSort( true);
- $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows( true);
- $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells( true);
-
- 设置文档安全 锚:bbb
- $objPHPExcel->getSecurity()->setLockWindows( true);
- $objPHPExcel->getSecurity()->setLockStructure( true);
- $objPHPExcel->getSecurity()->setWorkbookPassword( "PHPExcel");
-
- 样式复制 锚:bbb
- $objPHPExcel->getActiveSheet()->duplicateConditionalStyle(
- $objPHPExcel->getActiveSheet()->getStyle( 'B2')->getConditionalStyles(),
- 'B3:B7'
- );
-
- Add conditional formatting 锚:bbb
- echo date('H:i:s' ) , " Add conditional formatting" , PHP_EOL;
- $objConditional1 = new PHPExcel_Style_Conditional ();
- $objConditional1->setConditionType(PHPExcel_Style_Conditional ::CONDITION_CELLIS );
- $objConditional1->setOperatorType(PHPExcel_Style_Conditional ::OPERATOR_BETWEEN );
- $objConditional1->addCondition('200');
- $objConditional1->addCondition('400');
-
- 设置分页(主要用于打印) 锚:bbb
- $objPHPExcel->getActiveSheet()->setBreak( 'A' . $i, PHPExcel_Worksheet::BREAK_ROW );
-
-
- 用数组填充表 锚:bbb
- $dataArray = array( array("2010" , "Q1", "United States", 790),
- array("2010" , "Q2", "United States", 730),
- );
- $objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A2');
-
- 设置自动筛选 锚:bbb
- $objPHPExcel->getActiveSheet()->setAutoFilter($objPHPExcel->getActiveSheet()->calculateWorksheetDimension());
-
- 打印出的到所有的公式
- $objCalc = PHPExcel_Calculation::getInstance();
- print_r($objCalc->listFunctionNames())
-
- 设置单元格值的范围 锚:bbb
- $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('Only numbers between 10 and 20 are allowed!');
- $objValidation->setPromptTitle('Allowed input');
- $objValidation->setPrompt('Only numbers between 10 and 20 are allowed.');
- $objValidation->setFormula1(10);
- $objValidation->setFormula2(120);
-
- 其他
- $objPHPExcel->getActiveSheet()->getStyle( 'B5')->getAlignment()->setShrinkToFit(true);
- $objPHPExcel->getActiveSheet()->getStyle( 'B5')->getAlignment()->setShrinkToFit(true);
- $objPHPExcel->getActiveSheet()->getCell(B14)->getValue();
- $objPHPExcel->getActiveSheet()->getCell(B14)->getCalculatedValue();
-
-
- 导入或读取文件
- require_once '../Classes/PHPExcel/IOFactory.php';
- $objPHPExcel = PHPExcel_IOFactory::load(
- $dataArray = $objPHPExcel->getActiveSheet()->toArray();
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
- $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
-
- 读取xml文件
- $objReader = PHPExcel_IOFactory:: createReader('Excel2003XML' );
- $objPHPExcel = $objReader->load( "Excel2003XMLTest.xml" );
- 读取ods文件
- $objReader = PHPExcel_IOFactory:: createReader('OOCalc' );
- $objPHPExcel = $objReader->load("OOCalcTest.ods" );
- 读取numeric文件
- $objReader = PHPExcel_IOFactory:: createReader('Gnumeric' );
- $objPHPExcel = $objReader->load( "GnumericTest.gnumeric" );
- 读取slk文件
- $objPHPExcel = PHPExcel_IOFactory:: load("SylkTest.slk" );
-
-
- 循环遍历数据
- $objReader = PHPExcel_IOFactory::createReader('Excel2007' );
- $objPHPExcel = $objReader->load ("05featuredemo.xlsx" );
- foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
- echo 'Worksheet - ' , $worksheet->getTitle() , PHP_EOL;
- foreach ($worksheet->getRowIterator() as $row) {
- echo ' Row number - ' , $row->getRowIndex() , PHP_EOL;
- $cellIterator = $row->getCellIterator();
- $cellIterator->setIterateOnlyExistingCells( false);
- foreach ($cellIterator as $cell) {
- if (!is_null($cell)) {
- echo ' Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , PHP_EOL;
- }
- }
- }
- }
-
- 数组插入的表中
- $data = array( array('title' => 'Excel for dummies',
- 'price' => 17.99,
- 'quantity' => 2
- ),
- array('title' => 'PHP for dummies',
- 'price' => 15.99,
- 'quantity' => 1
- ),
- array('title' => 'Inside OOP',
- 'price' => 12.95,
- 'quantity' => 1
- )
- );
- $baseRow = 5;
- foreach($data as $r => $dataRow) {
- $row = $baseRow + $r;
- $objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
-
- $objPHPExcel->getActiveSheet()->setCellValue( 'A'.$row, $r+1);
- $objPHPExcel->getActiveSheet()->setCellValue( 'B'.$row, $dataRow['title']);
- $objPHPExcel->getActiveSheet()->setCellValue( 'C'.$row, $dataRow['price']);
- $objPHPExcel->getActiveSheet()->setCellValue( 'D'.$row, $dataRow['quantity']);
- $objPHPExcel->getActiveSheet()->setCellValue( 'E'.$row, '=C'.$row.'*D' .$row);
- }
- $objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
-
- 作者:黄聪
- 出处:http:
- 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
-
相关阅读:
js修改剪切板内容的方法
使用jq获取文字的宽度
如何改变placeholder的样式
如何使用phpmyadmin建立外键约束
php上传文件中文文件名乱码的解决方法
如何禁止审查元素扒代码(F12)
如何使用css影藏滚动条
webstorm配置babel自动转译es6的方法
css纯字母或者字母换行显示
使用Jquery做分页效果
-
原文地址:https://www.cnblogs.com/grimm/p/9208517.html
Copyright © 2020-2023
润新知