{"id":608,"date":"2023-02-28T14:17:40","date_gmt":"2023-02-28T14:17:40","guid":{"rendered":"http:\/\/www.nywzjs.com\/?p=608"},"modified":"2024-01-02T12:55:19","modified_gmt":"2024-01-02T12:55:19","slug":"phpoffice-phpspreadsheet%e8%af%bb%e5%8f%96%e5%92%8c%e5%86%99%e5%85%a5excel","status":"publish","type":"post","link":"http:\/\/www.nywzjs.com\/?p=608","title":{"rendered":"PhpOffice\/PhpSpreadsheet\u8bfb\u53d6\u548c\u5199\u5165Excel"},"content":{"rendered":"\n

\u6765\u6e90\uff1ahttps:\/\/blog.csdn.net\/hpsiling\/article\/details\/117741489<\/a><\/a><\/p>\n\n\n\n

PHPExcel \u662f\u7528\u6765\u64cd\u4f5cOffice Excel \u6587\u6863\u7684\u4e00\u4e2aPHP\u7c7b\u5e93\uff0c\u5b83\u57fa\u4e8e\u5fae\u8f6f\u7684OpenXML\u6807\u51c6\u548cPHP\u8bed\u8a00\u3002 \u53ef\u4ee5\u4f7f\u7528\u5b83\u6765\u8bfb\u53d6\u3001\u5199\u5165\u4e0d\u540c\u683c\u5f0f\u7684\u7535\u5b50\u8868\u683c<\/p>\n\n\n\n

PHPExcel\u7b80\u4ecb
PHPExcel\u662f\u4e00\u4e2a\u751f\u6210excel\u7684\u63d2\u4ef6\u3002\u63d2\u4ef6\u5c01\u88c5\u4e86excel\u7684\u8bfb\u53d6\u3001\u4fee\u6539\u548c\u751f\u6210\u3002\u5305\u62ec\u8868\u683c\u7684\u6837\u5f0f\u3001\u56fe\u8868\u6837\u5f0f\u3002\u4e5f\u662fphp\u8bed\u8a00\u4e0b\u6bd4\u8f83\u6210\u719f\u7684excel\u64cd\u4f5c\u63d2\u4ef6\uff0c\u662fphp\u4e0b\u751f\u4ea7\u5c0f\u578b\u62a5\u8868\u7684\u9996\u9009\u3002<\/p>\n\n\n\n

PHPExcel\u6027\u80fd\u548c\u5176\u4ed6\u8bed\u8a00\u6bd4\u8f83
PHPExcel\u5728\u751f\u6210\u4e00\u4e07\u884c\u4e8c\u767e\u5217\u4ee5\u4e0a\u7684\u6570\u636e\uff08\u533a\u5206\u670d\u52a1\u5668\u914d\u7f6e\uff09\u5c31\u4f1a\u53d8\u5f97\u975e\u5e38\u6162\u3002\u5f00\u59cb\u4ee5\u4e3a\u662fphp\u6027\u80fd\u95ee\u9898\uff0c\u540e\u6765\u5c1d\u8bd5\u5728python\u3001java\u4e0a\u751f\u6210excel\uff0c\u4e5f\u540c\u6837\u51fa\u73b0\u8fd9\u79cd\u95ee\u9898\u3002\u5b9e\u9645\u4e0a\u662f\u538b\u7f29\u6210excel\u65f6\u6d88\u8017\u8f83\u5927\u3002
\u6240\u4ee5\u8bf4\uff0c\u5982\u679c\u60f3\u5728\u9879\u76ee\u4e2d\u751f\u4ea7\u62a5\u8868\uff08\u5c0f\u6570\u636e\u91cf\uff09\u6216\u8005\u4f7f\u7528\u5230excel\u3002phpexcel\u662f\u53ef\u4ee5\u80dc\u4efb\u7684\u3002\u6570\u636e\u91cf\u4e0d\u5927\u7684\u65f6\u5019\uff0c\u751f\u6210\u662f\u5341\u5206\u8fc5\u901f\u3002
PHPExcel\u5e38\u7528\u5c5e\u6027\u4f7f\u7528\u5b9e\u4f8b\u5982\u4e0b\uff1a<\/p>\n\n\n\n

\u8bbe\u7f6eheader
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);<\/p>\n\n\n\n

header(\"Pragma: no-cache\");
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=down.xlsx');
header(\"Content-Transfer-Encoding:binary\");<\/p>\n\n\n\n

$objWriter->save('php:\/\/output');
PHPExcel\u5e38\u7528\u5c5e\u6027\u4f7f\u7528\u5b9e\u4f8b \u8bbe\u7f6e\u6587\u4ef6\u5c5e\u6027\u4fe1\u606f
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/ \u8bbe\u7f6e\u6587\u4ef6\u5c5e\u6027\u8be6\u7ec6\u4fe1\u606f
$objPHPExcel->getProperties()
->setTitle(\"\u6807\u9898\")
->setSubject(\"\u4e3b\u9898\")
->setKeywords(\"\u6807\u8bb0\")
->setCategory(\"\u7c7b\u522b\")
->setDescription(\"\u5907\u6ce8\")
->setCreator(\"\u4f5c\u8005\")
->setLastModifiedBy(\"\u6700\u540e\u4e00\u6b21\u4fdd\u5b58\u8005\")
->setCompany('\u516c\u53f8')
->setManager('\u7ba1\u7406\u8005');
PHPExcel\u521b\u5efa\u5de5\u4f5c\u8868sheet\u3001\u8bbe\u7f6e\u540d\u79f0 \u5c5e\u6027\u4f7f\u7528\u5b9e\u4f8b
\/\/ \u521b\u5efasheet
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex($sheet_index);<\/p>\n\n\n\n

\/\/\u83b7\u53d6\u6d3b\u52a8\u5de5\u4f5c\u8868
$objPHPExcel->getActiveSheet();
\/\/\u83b7\u53d6\u6d3b\u52a8\u5de5\u8d44\u8868\u7684\u7d22\u5f15\u53f7
$activeSheet_index=$objPHPExcel->getActiveSheetIndex();
\/\/\u8bbe\u7f6e\u5de5\u4f5c\u8868\u540d\u79f0
$objPHPExcel->getActiveSheet()->setTitle(\"\u5de5\u4f5c\u8868\u540d\u79f0\");
PHPExcel\u6dfb\u52a0\u6570\u636e\u8bbe\u7f6e\u6837\u5f0f\u4f7f\u7528\u5b9e\u4f8b
\/\/Add some data \u6dfb\u52a0\u6570\u636e
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '\u4e16\u754c');<\/p>\n\n\n\n

\/\/\u65e5\u671f\u683c\u5f0f\u5316
$objPHPExcel->getActiveSheet()->setCellValue('D1', time());
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);<\/p>\n\n\n\n

\/\/Add comment \u6dfb\u52a0\u6ce8\u91ca
$objPHPExcel->getActiveSheet()->getComment('E11')->setAuthor('PHPExcel');
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('PHPExcel:');
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun(\"\\r\\n\");
$objPHPExcel->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');<\/p>\n\n\n\n

\/\/Add rich-text string \u6dfb\u52a0\u6587\u5b57 \u53ef\u8bbe\u7f6e\u6837\u5f0f
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell('A18') );
$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.');
PHPExcel\u6587\u6863\u5b89\u5168\u3001\u5de5\u4f5c\u8868\u5b89\u5168\u5c5e\u6027\u4f7f\u7528
\/\/Set document security \u8bbe\u7f6e\u6587\u6863\u5b89\u5168 \u4e0d\u80fd\u6dfb\u52a0\u5220\u9664\u5de5\u4f5c\u8868
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword(\"PHPExcel\");<\/p>\n\n\n\n

\/\/Set sheet security \u8bbe\u7f6e\u5de5\u4f5c\u8868\u5b89\u5168 \u4e0d\u80fd\u7f16\u8f91\u5355\u5143\u683c\u5185\u5bb9
$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);<\/p>\n\n\n\n

\/\/Protect cells \u4fdd\u62a4\u5355\u5143\u683c
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'mypwd');<\/p>\n\n\n\n

\/\/ \u6807\u660e\u4e0d\u9700\u8981\u9501\u5b9a\u4fdd\u62a4\u7684\u5355\u5143\u683c
$objPHPExcel->getActiveSheet()->getStyle('A2\uff1aB2')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);<\/p>\n\n\n\n

\/\/ \u7ed9\u5de5\u4f5c\u8868\u8bbe\u7f6e\u9501\u5b9a\u72b6\u6001\u751f\u6548
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
PHPExcel\u5408\u5e76 \u5206\u79bb \u5355\u5143\u683c \u5c5e\u6027\u4f7f\u7528\u5b9e\u4f8b
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/Merge cells \u5408\u5e76\u5206\u79bb\u5355\u5143\u683c
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
$objPHPExcel->getActiveSheet()->unmergeCells('A18:E22');
PHPExcel \u6570\u5b57\u683c\u5f0f\u5316
\/\/Set cell number formats \u6570\u5b57\u683c\u5f0f\u5316
$objPHPExcel->getActiveSheet()->getStyle('F4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('F4'), 'F5:F13' );
PHPExcel\u8bbe\u7f6e\u5b57\u4f53\u5bbd\u5ea6 \u8bbe\u7f6e\u884c\u9ad8
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/Set column widths \u8bbe\u7f6e\u5217\u5bbd\u5ea6
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(100); \/\/\u8bbe\u7f6e\u884c\u9ad8<\/p>\n\n\n\n

\/\/Set fonts \u8bbe\u7f6e\u5b57\u4f53
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
PHPExcel\u8bbe\u7f6e\u5bf9\u9f50\u65b9\u5f0f\u3001\u6362\u884c
\/\/\u8bbe\u7f6e\u5bf9\u9f50 Set alignments
$objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);\/\/\u6c34\u5e73\u53f3\u5bf9\u9f50
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);\/\/\u4e24\u7aef\u5bf9\u9f50
$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);\/\/\u5782\u76f4\u5c45\u4e2d
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setWrapText(true);\/\/\u5355\u5143\u683c\u5185\u6362\u884c \u8981\u6362\u884c\u7684\u6587\u5b57\u5fc5\u987b\u662f\u53cc\u5f15\u53f7\u63d2\u5165\u6362\u884c\u7b26\"\\n\"
PHPExcel \u8bbe\u7f6e\u5217\u8fb9\u6846
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/\u8bbe\u7f6e\u5217\u8fb9\u6846
$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);<\/p>\n\n\n\n

\/\/\u8bbe\u7f6e\u8fb9\u6846\u989c\u8272
$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');
PHPExcel \u8bbe\u7f6e\u5355\u5143\u683c\u586b\u5145\u8272
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/Set fills \u8bbe\u7f6e\u586b\u5145
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
PHPExcel \u6dfb\u52a0\u94fe\u63a5
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/Add a hyperlink to the sheet \u6dfb\u52a0\u94fe\u63a5
$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);
PHPExcel \u6dfb\u52a0\u56fe\u7247
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/\u6dfb\u52a0\u56fe\u7247 Add a drawing to the worksheet
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('.\/images\/myimg.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());
PHPExcel \u63d2\u5165\u6216\u5220\u9664\u5217\u548c\u884c
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\n

\/\/\u63d2\u5165\u884c
$pBefore = 5; \/\/\u63d2\u5165\uff08\u5220\u9664\uff09\u7684\u8d77\u59cb\u884c
$pNumRows = 3; \/\/\u63d2\u5165\uff08\u5220\u9664\uff09\u7684\u884c\u6570
$objPHPExcel->getActiveSheet()->insertNewRowBefore($pBefore, $pNumRows);
$objPHPExcel->getActiveSheet()->removeRow($pBefore , $pNumRows);<\/p>\n\n\n\n

$pBefore = 'F'; \/\/\u63d2\u5165\uff08\u5220\u9664\uff09\u7684\u8d77\u59cb\u5217\uff08A\u3001B\u3001C\u3001\u2026..\uff09
$pNumCols = 5; \/\/\u63d2\u5165\uff08\u5220\u9664\uff09\u7684\u5217\u6570
$objPHPExcel->getActiveSheet()->insertNewColumnBefore($pBefore, $pNumCols);
$objPHPExcel->getActiveSheet()->removeColumn($pBefore, $pNumCols);
PHPExcel\u51bb\u7ed3\u3001\u9501\u5b9a\u8868\u5934\uff08\u884c\u3001\u5217\uff09
$objPHPExcel = new PHPExcel();<\/p>\n<\/blockquote>\n\n\n\n

\/\/\u9700\u8981\u9501\u5b9a\u7684\u884c\u3001\u5217
$C_R='A2'; \/\/\u5c06\u51bb\u7ed3\u5355\u5143\u683cA2\u4e0a\u65b9\u7684\u884c\uff08\u5373\u7b2c1\u884c\uff09
$C_R='B1'; \/\/\u5c06\u51bb\u7ed3\u5355\u5143\u683cB1\u5de6\u4fa7\u7684\u5217\uff08\u5373A\u5217\uff09
$C_R='C3'; \/\/\u5c06\u51bb\u7ed3\u5355\u5143\u683cC3\u4e0a\u65b9\u6240\u6709\u884c\u548c\u5de6\u4fa7\u7684\u6240\u6709\u5217\uff08\u5373\u7b2c1\u884c\u3001\u7b2c2\u884c\u3001A\u5217\u3001B\u5217\u88ab\u9501\u5b9a\uff09
$objPHPExcel->getActiveSheet()->freezePane($C_R);
PHPExcel\u9690\u85cf\u5217\u3001\u6dfb\u52a0\u6761\u4ef6\u683c\u5f0f\u3001\u81ea\u52a8\u8fc7\u6ee4\u3001\u8ba1\u7b97\u3001\u8bbe\u7f6e\u5927\u7eb2
$objPHPExcel = new PHPExcel();<\/p>\n\n\n\n

\/\/\u6dfb\u52a0\u6761\u4ef6\u683c\u5f0f 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);<\/p>\n\n\n\n

\/\/Set autofilter \u81ea\u52a8\u8fc7\u6ee4
$objPHPExcel->getActiveSheet()->setAutoFilter('A1:C9');<\/p>\n\n\n\n

\/\/Hide \"Phone\" and \"fax\" column \u9690\u85cf\u5217
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);<\/p>\n\n\n\n

\/\/Calculated data \u8ba1\u7b97
echo 'Value of B14 [=COUNT(B2:B12)]: ' . $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue() . \"\\r\\n\";<\/p>\n\n\n\n

\/\/\u8bbe\u7f6e\u5927\u7eb2\u7ea7\u522b Set outline levels
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setOutlineLevel(1);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setCollapsed(true);<\/p>\n\n\n\n

PHPExcel \u5217\u6570\u8d85\u8fc726\u5217\u53ef\u52a8\u6001\u8bbe\u7f6e\u5bf9\u5e94\u5217
\/*<\/p>\n\n\n\n