{"id":980,"date":"2021-06-07T14:44:32","date_gmt":"2021-06-07T06:44:32","guid":{"rendered":"http:\/\/blog.yyhcw.com\/?p=980"},"modified":"2021-06-07T14:45:00","modified_gmt":"2021-06-07T06:45:00","slug":"%e4%bd%bf%e7%94%a8php-xlswriter%e6%89%a9%e5%b1%95%e5%af%bc%e5%87%ba%e7%99%be%e4%b8%87%e6%95%b0%e6%8d%ae","status":"publish","type":"post","link":"https:\/\/blog.yyhcw.com\/post\/980.html","title":{"rendered":"\u4f7f\u7528PHP-Xlswriter\u6269\u5c55\u5bfc\u51fa\u767e\u4e07\u6570\u636e"},"content":{"rendered":"<!--wp-compress-html--><!--wp-compress-html no compression--><p>xlsxwriter \u662f\u4e00\u4e2a <a href=\"https:\/\/blog.yyhcw.com\/post\/tag\/php\" class=\"tag-tooltip\" data-toggle=\"tooltip\" title=\"\u67e5\u770b\u66f4\u591a\u5173\u4e8e PHP \u7684\u6587\u7ae0\" target=\"_blank\">PHP<\/a> C \u6269\u5c55\uff0c\u53ef\u7528\u4e8e\u5728Excel 2007\u53ca\u4ee5\u4e0a\u7248\u672cXLSX\u6587\u4ef6\u4e2d\u5199\u5165\u591a\u4e2a\u5de5\u4f5c\u8868\u7684\u6587\u672c\uff0c\u6570\u5b57\uff0c\u516c\u5f0f\u548c\u8d85\u94fe\u63a5<\/p>\n<p>https:\/\/gitee.com\/viest\/php-ext-xlswriter<\/p>\n<p>https:\/\/github.com\/mk-j\/<a href=\"https:\/\/blog.yyhcw.com\/post\/tag\/php\" class=\"tag-tooltip\" data-toggle=\"tooltip\" title=\"\u67e5\u770b\u66f4\u591a\u5173\u4e8e PHP \u7684\u6587\u7ae0\" target=\"_blank\">PHP<\/a>_XLSXWriter<\/p>\n<p>\u76f4\u63a5\u4e0a\u4ee3\u7801\uff1a<\/p>\n<pre class=\"lang:php decode:true \" title=\"PHP \u5bfc\u51fa\u767e\u4e07\u6570\u636e\">ini_set('memory_limit','1024M');\r\nset_time_limit(0);\r\nfunction getTmpDir(): string\r\n{\r\n    $tmp = ini_get('upload_tmp_dir');\r\n    if ($tmp !== False &amp;&amp; file_exists($tmp)) {\r\n        return realpath($tmp);\r\n    }\r\n    return realpath(sys_get_temp_dir());\r\n}\r\n$config = [\r\n    'path' =&gt; getTmpDir() . '\/',\r\n];\r\n$<a href=\"https:\/\/blog.yyhcw.com\/post\/tag\/excel\" class=\"tag-tooltip\" data-toggle=\"tooltip\" title=\"\u67e5\u770b\u66f4\u591a\u5173\u4e8e excel \u7684\u6587\u7ae0\" target=\"_blank\">excel<\/a>  = new \\Vtiful\\Kernel\\Excel($config);\r\n\/\/ Init File\r\n$fileName   = 'emp.xlsx';\r\n\/\/\u56fa\u5b9a\u5185\u5b58\u6a21\u5f0f\r\n$<a href=\"https:\/\/blog.yyhcw.com\/post\/tag\/excel\" class=\"tag-tooltip\" data-toggle=\"tooltip\" title=\"\u67e5\u770b\u66f4\u591a\u5173\u4e8e excel \u7684\u6587\u7ae0\" target=\"_blank\">excel<\/a> = $<a href=\"https:\/\/blog.yyhcw.com\/post\/tag\/excel\" class=\"tag-tooltip\" data-toggle=\"tooltip\" title=\"\u67e5\u770b\u66f4\u591a\u5173\u4e8e excel \u7684\u6587\u7ae0\" target=\"_blank\">excel<\/a>-&gt;constMemory($fileName, 'sheet11');\r\n$<a href=\"https:\/\/blog.yyhcw.com\/post\/tag\/excel\" class=\"tag-tooltip\" data-toggle=\"tooltip\" title=\"\u67e5\u770b\u66f4\u591a\u5173\u4e8e excel \u7684\u6587\u7ae0\" target=\"_blank\">excel<\/a> = $excel-&gt;header(['id', 'empno', 'ename', 'job', 'mgr', 'hiredate', 'sal', 'comm', 'deptno']);\r\nfor($i=5000;$i&lt;=1000000;$i+=5000){\r\n    $start = $i-5000;\r\n    $limit = 5000;\r\n    $sth = $this-&gt;db-&gt;pdo-&gt;prepare(\"SELECT * FROM emp order by id asc limit {$start},{$limit}\");\r\n    $sth-&gt;execute();\r\n    $emps = $sth-&gt;fetchAll(PDO::FETCH_NUM);\r\n    $excel = $excel-&gt;data($emps);\r\n}\r\n\/\/ Output\r\n$filePath = $excel-&gt;output();\r\n\/\/ Set Header\r\nheader(\"Content-Type: application\/vnd.openxmlformats-officedocument.spreadsheetml.sheet\");\r\nheader('Content-Disposition: attachment;filename=\"' . $fileName . '\"');\r\nheader('Content-Length: ' . filesize($filePath));\r\nheader('Content-Transfer-Encoding: binary');\r\nheader('Cache-Control: must-revalidate');\r\nheader('Cache-Control: max-age=0');\r\nheader('Pragma: public');\r\nob_clean();\r\nflush();\r\nif (copy($filePath, 'php:\/\/output') === false) {\r\n    \/\/ Throw exception\r\n    exit('copy file to path error');\r\n}\r\n\/\/ Delete temporary file\r\n@unlink($filePath);<\/pre>\n<p>\u6b64\u4ee3\u7801\u672c\u4eba\u5e76\u672a\u8fdb\u884c\u6d4b\u8bd5\uff0c\u6682\u65f6\u53ea\u662f\u62f7\u8d1d\u8fc7\u6765\u505a\u8bb0\u5f55\uff0c\u5df2\u88ab\u540e\u7eed\u4f7f\u7528<\/p>\n<!--wp-compress-html no compression--><!--wp-compress-html-->","protected":false},"excerpt":{"rendered":"xlsxwriter \u662f\u4e00\u4e2a PHP C \u6269\u5c55\uff0c\u53ef\u7528\u4e8e\u5728Excel 2007\u53ca\u4ee5\u4e0a\u7248\u672cXLSX\u6587\u4ef6\u4e2d\u5199\u5165\u591a\u4e2a\u5de5 \u00b7\u00b7\u00b7","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[266,14,267],"views":307,"_links":{"self":[{"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/posts\/980"}],"collection":[{"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/comments?post=980"}],"version-history":[{"count":0,"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/posts\/980\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/media?parent=980"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/categories?post=980"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.yyhcw.com\/api\/wp\/v2\/tags?post=980"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}