{"id":912,"date":"2022-06-21T11:46:38","date_gmt":"2022-06-21T03:46:38","guid":{"rendered":"https:\/\/usei.cn\/?p=912"},"modified":"2022-06-21T11:46:39","modified_gmt":"2022-06-21T03:46:39","slug":"php-%e5%af%bc%e5%87%baexcel","status":"publish","type":"post","link":"https:\/\/usei.cn\/index.php\/2022\/06\/21\/php-%e5%af%bc%e5%87%baexcel\/","title":{"rendered":"php \u5bfc\u51faexcel"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\">\u4e0b\u8f7d\u62d3\u5c55\u5305<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>composer require phpoffice\/phpexcel<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\u53c2\u8003\u4ee3\u7801<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\/\/\u5982\u679c\u4f7f\u7528thinkphp5.1\u6846\u67b6\uff0c\u5219\u76f4\u63a5use\u5373\u53ef\r\nuse PHPExcel_IOFactory;\r\nuse PHPExcel;\r\n\r\n\/\/\u5b9e\u4f8b\u5316phpexcel\u5bf9\u8c61\r\n$objPHPExcel = new \\PHPExcel();\r\n\r\n\/\/\u8bbe\u7f6eExcel\u5c5e\u6027\r\n$objPHPExcel->getProperties()\r\n\t        ->setCreator(\"Maarten Balliauw\")\t\t\t\t\t\/\/\u521b\u5efa\u4eba\r\n\t        ->setLastModifiedBy(\"Maarten Balliauw\")\t\t\t\t\/\/\u6700\u540e\u4fee\u6539\u4eba\r\n\t        ->setTitle(\"Office 2007 XLSX Test Document\")\t\t\/\/\u8bbe\u7f6e\u6807\u9898\r\n\t        ->setSubject(\"Office 2007 XLSX Test Document\")\t\t\/\/\u8bbe\u7f6e\u4e3b\u9898\r\n\t        ->setDescription(\"Test document \")\t\t\t\t\t\/\/\u8bbe\u7f6e\u5907\u6ce8\r\n\t        ->setKeywords( \"office 2007 openxml php\")\t\t\t\/\/\u8bbe\u7f6e\u5173\u952e\u5b57\r\n\t        ->setCategory( \"Test result file\");\t\t\t\t\t\/\/\u8bbe\u7f6e\u7c7b\u522b\r\n\r\n\/\/ \u7ed9\u8868\u683c\u6dfb\u52a0\u6570\u636e\r\n$objPHPExcel->setActiveSheetIndex(0)             \/\/\u8bbe\u7f6e\u7b2c\u4e00\u4e2a\u5185\u7f6e\u8868\uff08\u4e00\u4e2axls\u6587\u4ef6\u91cc\u53ef\u4ee5\u6709\u591a\u4e2a\u8868\uff09\u4e3a\u6d3b\u52a8\u7684\r\n            ->setCellValue( 'A1', 'Hello' )         \/\/\u7ed9\u8868\u7684\u5355\u5143\u683c\u8bbe\u7f6e\u6570\u636e\r\n            ->setCellValue( 'B2', 'world!' )      \/\/\u6570\u636e\u683c\u5f0f\u53ef\u4ee5\u4e3a\u5b57\u7b26\u4e32\r\n            ->setCellValue( 'C1', 12)            \/\/\u6570\u5b57\u578b\r\n            ->setCellValue( 'D2', 12)            \/\/\r\n            ->setCellValue( 'D3', **true** )           \/\/\u5e03\u5c14\u578b\r\n            ->setCellValue( 'D4', '=SUM(C1:D2)' );\/\/\u516c\u5f0f\r\n\r\n\/\/\u6fc0\u6d3b\u5f53\u524d\u8868\r\n$objPHPExcel->setActiveSheetIndex(0);\r\nob_end_clean();\/\/\u6e05\u9664\u7f13\u51b2\u533a,\u907f\u514d\u4e71\u7801\r\n\r\n\/\/\u6700\u540e\u53ea\u9700\u8981\u751f\u6210Excel\u6216\u8005\u63d0\u793a\u4e0b\u8f7d\u5373\u53ef\r\n\/\/\u751f\u6210Excel\uff0c\u5e76\u81ea\u5b9a\u4e49\u4fdd\u5b58\u8def\u5f84\r\n\/\/\"Excel2007\"\u751f\u62102007\u7248\u672c\u7684xlsx\uff0c\"Excel5\"\u751f\u62102003\u7248\u672c\u7684xls\r\n$objWriter = \\PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');\r\n$objWriter->save($path);\r\n\r\n\/\/\u5f39\u51fa\u63d0\u793a\u4e0b\u8f7d\u6587\u4ef6\r\nheader('pragma:public');  \r\nheader(\"Content-Disposition:attachment;filename=$expFileName\");  \r\nheader('Cache-Control: max-age=0');\r\n$objWriter = PHPExcel\\_IOFactory:: *createWriter*($objPHPExcel, 'Excel2007');\r\n$objWriter->save( 'php:\/\/output');<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\u8bbe\u7f6e\u6837\u5f0f<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\/\/\u8bbe\u7f6e\u5bbd\u5ea6\r\n$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(200);   \/\/\u8bbe\u7f6e\u5355\u5143\u683c\u5bbd\u5ea6\r\n$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);   \/\/\u5185\u5bb9\u81ea\u9002\u5e94\r\n\/\/\u8bbe\u7f6ealign\uff08\u9700\u8981\u5f15\u5165PHPExcel_Style_Alignment\uff09\r\n$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);\/\/\u6c34\u5e73\u65b9\u5411\u4e0a\u4e24\u7aef\u5bf9\u9f50\r\n$objPHPExcel->getActiveSheet()->getStyle( 'A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);    \/\/\u5782\u76f4\u65b9\u5411\u4e0a\u4e2d\u95f4\u5c45\u4e2d\r\n\/\/\u5408\u5e76\u62c6\u5206\u5355\u5143\u683c\r\n$objPHPExcel->getActiveSheet()->mergeCells('A28:B28');      \/\/ A28:B28\u5408\u5e76\r\n$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');    \/\/ A28:B28\u518d\u62c6\u5206\r\n\/\/\u5b57\u4f53\u5927\u5c0f\u3001\u7c97\u4f53\u3001\u5b57\u4f53\u3001\u4e0b\u5212\u7ebf\u3001\u5b57\u4f53\u989c\u8272(\u9700\u5f15\u5165PHPExcel_Style_Font\u3001PHPExcel_Style_Color)\r\n$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);\r\n$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);\r\n$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');\r\n$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);\r\n$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);\r\n\/\/\u9ed8\u8ba4\u5b57\u4f53\u3001\u5927\u5c0f\r\n$objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');\r\n$objPHPExcel->getDefaultStyle()->getFont()->setSize(20);\r\n\/\/\u80cc\u666f\u586b\u5145\r\n$objPHPExcel->getActiveSheet()->getStyle( 'A3:E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);\r\n$objPHPExcel->getActiveSheet()->getStyle( 'A4:E4')->getFill()->getStartColor()->setARGB('FFC125');\r\n\/\/ \u5355\u5143\u683c\u5bc6\u7801\u4fdd\u62a4\u4e0d\u8ba9\u4fee\u6539\r\n$objPHPExcel->getActiveSheet()->getProtection()->setSheet( **true**);  \/\/ \u4e3a\u4e86\u4f7f\u4efb\u4f55\u8868\u4fdd\u62a4\uff0c\u9700\u8bbe\u7f6e\u4e3a\u771f\r\n$objPHPExcel->getActiveSheet()->protectCells( 'A3:E13', 'PHPExcel' ); \/\/ \u5c06A3\u5230E13\u4fdd\u62a4 \u52a0\u5bc6\u5bc6\u7801\u662f PHPExcel\r\n$objPHPExcel->getActiveSheet()->getStyle( 'B1')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED); \/\/\u53bb\u6389\u4fdd\u62a4\r\n\/\/\u7ed9\u5355\u5143\u683c\u5185\u5bb9\u8bbe\u7f6eurl\u8d85\u94fe\u63a5\r\n$objActSheet->getCell('E26')->getHyperlink()->setUrl( 'http:\/\/www.phpexcel.net');    \/\/\u8d85\u94fe\u63a5url\u5730\u5740\r\n$objActSheet->getCell('E26')->getHyperlink()->setTooltip( 'Navigate to website');  \/\/\u9f20\u6807\u79fb\u4e0a\u53bb\u8fde\u63a5\u63d0\u793a\u4fe1\u606f<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4e0b\u8f7d\u62d3\u5c55\u5305 \u53c2\u8003\u4ee3\u7801 \u8bbe\u7f6e\u6837\u5f0f<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-912","post","type-post","status-publish","format-standard","hentry","category-php"],"_links":{"self":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts\/912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/comments?post=912"}],"version-history":[{"count":1,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts\/912\/revisions"}],"predecessor-version":[{"id":914,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/posts\/912\/revisions\/914"}],"wp:attachment":[{"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/media?parent=912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/categories?post=912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/usei.cn\/index.php\/wp-json\/wp\/v2\/tags?post=912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}