Detailed explanation of common methods of PHP processing Excel plug-in PHPExcel-PHP Tutorial-php.cn

Detailed explanation of common methods of PHP processing Excel plug-in PHPExcel

PHPExcel official website: http://phpexcel.codeplex.com/

PHPExcel On GitHub: https://github.com/PHPOffice/PHPExcel

1.header

header("Content-Type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=product.xls");
header("Pragma:no-cache");
header("Expires:0");

Copy after login

2.PHPExcel

Development Kit Tests The directory has detailed usage examples and supports Chinese. Pay attention to the file encoding and save the file as utf-8.

Write excel

//Include class
require_once(&#39;Classes/PHPExcel.php&#39;);
require_once(&#39;Classes/PHPExcel/Writer/Excel2007.php&#39;);
$objPHPExcel = new PHPExcel();
//设置文件属性
$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");
//添加数据
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A1&#39;, &#39;Hello&#39;);//可以指定位置
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A2&#39;, true);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A3&#39;, false);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;B2&#39;, &#39;world!&#39;);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;B3&#39;, 2);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;C1&#39;, &#39;Hello&#39;);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;D2&#39;, &#39;world!&#39;);
//循环
for($i = 1;$i<200;$i++) {
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;A&#39; . $i, $i);
    $objPHPExcel->getActiveSheet()->setCellValue(&#39;B&#39; . $i, &#39;Test value&#39;);
}
//日期格式化
$objPHPExcel->getActiveSheet()->setCellValue(&#39;D1&#39;, time());
$objPHPExcel->getActiveSheet()->getStyle(&#39;D1&#39;)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
//添加注释
$objPHPExcel->getActiveSheet()->getComment(&#39;E11&#39;)->setAuthor(&#39;PHPExcel&#39;);
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment(&#39;E11&#39;)->getText()->createTextRun(&#39;PHPExcel:&#39;);
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment(&#39;E11&#39;)->getText()->createTextRun("\r\n");
$objPHPExcel->getActiveSheet()->getComment(&#39;E11&#39;)->getText()->createTextRun(&#39;Total amount on the current invoice, excluding VAT.&#39;);
//添加文字 可设置样式
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell(&#39;A18&#39;) );
$objRichText->createText(&#39;This invoice is &#39;);
$objPayable = $objRichText->createTextRun(&#39;payable within thirty days after the end of the month&#39;);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
$objRichText->createText(&#39;, unless specified otherwise on the invoice.&#39;);
//合并分离单元格
$objPHPExcel->getActiveSheet()->mergeCells(&#39;A18:E22&#39;);
$objPHPExcel->getActiveSheet()->unmergeCells(&#39;A18:E22&#39;);
//保护单元格
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells(&#39;A3:E13&#39;, &#39;PHPExcel&#39;);
//数字格式化
$objPHPExcel->getActiveSheet()->getStyle(&#39;E4&#39;)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(&#39;E4&#39;), &#39;E5:E13&#39; );
//设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(12);
//设置字体
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setName(&#39;Candara&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//设置对齐
$objPHPExcel->getActiveSheet()->getStyle(&#39;D11&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A18&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A18&#39;)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A3&#39;)->getAlignment()->setWrapText(true);
//设置列边框
$objPHPExcel->getActiveSheet()->getStyle(&#39;A4&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A10&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E10&#39;)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E13&#39;)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
//设置边框颜色
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getLeft()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getTop()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getBottom()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E13&#39;)->getBorders()->getRight()->getColor()->setARGB(&#39;FF993300&#39;);
//设置填充
$objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getFill()->getStartColor()->setARGB(&#39;FF808080&#39;);
//添加链接
$objPHPExcel->getActiveSheet()->setCellValue(&#39;E26&#39;, &#39;www.phpexcel.net&#39;);
$objPHPExcel->getActiveSheet()->getCell(&#39;E26&#39;)->getHyperlink()->setUrl(&#39;http://www.phpexcel.net&#39;);
$objPHPExcel->getActiveSheet()->getCell(&#39;E26&#39;)->getHyperlink()->setTooltip(&#39;Navigate to website&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E26&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//添加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(&#39;Logo&#39;);
$objDrawing->setDescription(&#39;Logo&#39;);
$objDrawing->setPath(&#39;./images/officelogo.jpg&#39;);
$objDrawing->setHeight(36);
$objDrawing->setCoordinates(&#39;B15&#39;);
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
//Play around with inserting and removing rows and columns
$objPHPExcel->getActiveSheet()->insertNewRowBefore(6, 10);
$objPHPExcel->getActiveSheet()->removeRow(6, 10);
$objPHPExcel->getActiveSheet()->insertNewColumnBefore(&#39;E&#39;, 5);
$objPHPExcel->getActiveSheet()->removeColumn(&#39;E&#39;, 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(&#39;0&#39;);
$objConditional1->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED);
$objConditional1->getStyle()->getFont()->setBold(true);
//自动过滤
$objPHPExcel->getActiveSheet()->setAutoFilter(&#39;A1:C9&#39;);
//隐藏列
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setVisible(false);
//设置文档安全
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("PHPExcel");
//设置工作表安全
$objPHPExcel->getActiveSheet()->getProtection()->setPassword(&#39;PHPExcel&#39;);
$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);
//计算
echo &#39;Value of B14 [=COUNT(B2:B12)]: &#39; . $objPHPExcel->getActiveSheet()->getCell(&#39;B14&#39;)->getCalculatedValue() . "\r\n";
//Set outline levels
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setCollapsed(true);
//Freeze panes
$objPHPExcel->getActiveSheet()->freezePane(&#39;A2&#39;);
//Rows to repeat at top
$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1);
//验证输入值
$objValidation = $objPHPExcel->getActiveSheet()->getCell(&#39;B3&#39;)->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(&#39;Input error&#39;);
$objValidation->setError(&#39;Number is not allowed!&#39;);
$objValidation->setPromptTitle(&#39;Allowed input&#39;);
$objValidation->setPrompt(&#39;Only numbers between 10 and 20 are allowed.&#39;);
$objValidation->setFormula1(10);
$objValidation->setFormula2(20);
$objPHPExcel->getActiveSheet()->getCell(&#39;B3&#39;)->setDataValidation($objValidation);
//创建新的工作标签
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
//页眉页脚
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(&#39;&C&HPlease treat this document as confidential!&#39;);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(&#39;&L&B&#39; . $objPHPExcel->getProperties()->getTitle() . &#39;&RPage &P of &N&#39;);
//方向大小
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
//重命名工作表标签
$objPHPExcel->getActiveSheet()->setTitle(&#39;Simple&#39;);
//Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
//保存
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace(&#39;.php&#39;, &#39;.xlsx&#39;, __FILE__));
//保存
require_once(&#39;Classes/PHPExcel/Writer/Excel5.php&#39;);
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save(str_replace(&#39;.php&#39;, &#39;.xls&#39;, __FILE__));
//1.6.2新版保存
require_once(&#39;Classes/PHPExcel/IOFactory.php&#39;);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel2007&#39;);
$objWriter->save(str_replace(&#39;.php&#39;, &#39;.xls&#39;, __FILE__));

Copy after login

Read Excel

//Include class
require_once(&#39;Classes/PHPExcel/Reader/Excel2007.php&#39;);
$objReader = new PHPExcel_Reader_Excel2007;
$objPHPExcel = $objReader->load("05featuredemo.xlsx");

Copy after login

Read and write csv

require_once("05featuredemo.inc.php");
require_once(&#39;Classes/PHPExcel/Writer/CSV.php&#39;);
require_once(&#39;Classes/PHPExcel/Reader/CSV.php&#39;);
require_once(&#39;Classes/PHPExcel/Writer/Excel2007.php&#39;);
//Write to CSV format 写
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel);
$objWriter->setDelimiter(&#39;;&#39;);
$objWriter->setEnclosure(");
$objWriter->setLineEnding("\r\n");
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace(&#39;.php&#39;, &#39;.csv&#39;, __FILE__));
//Read from CSV format 读
$objReader = new PHPExcel_Reader_CSV();
$objReader->setDelimiter(&#39;;&#39;);
$objReader->setEnclosure(");
$objReader->setLineEnding("\r\n");
$objReader->setSheetIndex(0);
$objPHPExcelFromCSV = $objReader->load(str_replace(&#39;.php&#39;, &#39;.csv&#39;, __FILE__));
//Write to Excel2007 format
$objWriter2007 = new PHPExcel_Writer_Excel2007($objPHPExcelFromCSV);
$objWriter2007->save(str_replace(&#39;.php&#39;, &#39;.xlsx&#39;, __FILE__));

Copy after login

Write HTML

require_once("05featuredemo.inc.php");
require_once(&#39;Classes/PHPExcel/Writer/HTML.php&#39;);
//Write to HTML format
$objWriter = new PHPExcel_Writer_HTML($objPHPExcel);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace(&#39;.php&#39;, &#39;.htm&#39;, __FILE__));

Copy after login

Write PDF

require_once("05featuredemo.inc.php");
require_once(&#39;Classes/PHPExcel/IOFactory.php&#39;);
//Write to PDF format
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;PDF&#39;);
$objWriter->setSheetIndex(0);
$objWriter->save(str_replace(&#39;.php&#39;, &#39;.pdf&#39;, __FILE__));
//Echo memory peak usage
echo date(&#39;H:i:s&#39;) . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

Copy after login