{"id":169848,"date":"2023-07-29T17:23:23","date_gmt":"2023-07-29T09:23:23","guid":{"rendered":"http:\/\/www.idc.net\/help\/169848\/"},"modified":"2023-07-29T17:23:23","modified_gmt":"2023-07-29T09:23:23","slug":"%e5%90%8e%e6%b5%aa%e4%ba%91python%e6%95%99%e7%a8%8b%ef%bc%9apython%e6%96%b0%e6%89%8b%e5%85%a5%e9%97%a8%ef%bc%9aexcel%e5%9f%ba%e6%9c%ac%e6%93%8d%e4%bd%9c%ef%bc%88%e4%ba%8c%ef%bc%89","status":"publish","type":"post","link":"https:\/\/idc.net\/help\/169848\/","title":{"rendered":"\u540e\u6d6a\u4e91Python\u6559\u7a0b\uff1aPython\u65b0\u624b\u5165\u95e8\uff1aExcel\u57fa\u672c\u64cd\u4f5c\uff08\u4e8c\uff09"},"content":{"rendered":"<p><strong><img decoding=\"async\" src=\"https:\/\/oss.py.cn\/pycn\/upload\/article\/000\/000\/003\/5de202b7542fb367.jpg\" class=\"aligncenter\"><\/strong><\/p>\n<p><strong>\u5199\u5165 Excel<\/strong><\/p>\n<p>\u9996\u5148\u5f53\u7136\u662f\u5b89\u88c5\u7b2c\u4e09\u65b9\u6a21\u5757\uff1a<\/p>\n<pre>pip&nbsp;install&nbsp;xlsxwriter<\/pre>\n<p>\u9996\u5148\u6211\u4eec\u9700\u8981\u5148\u521b\u5efa\u4e00\u4e2a WorkBook\uff1a<\/p>\n<pre>import&nbsp;xlsxwriter\nworkbook&nbsp;=&nbsp;xlsxwriter.Workbook('demo.xlsx')<\/pre>\n<p>\u5728\u6240\u6709\u64cd\u4f5c\u4e4b\u524d\uff0c\u9700\u8981\u8bb0\u5f97\u5148\u5bfc\u5165\u6211\u4eec\u521a\u624d\u5b89\u88c5\u7684 xlsxwriter \u7684\u6a21\u5757\u3002<\/p>\n<p>\u63a5\u4e0b\u6765\uff0c\u6211\u4eec\u521b\u5efa\u4e00\u4e2a Sheet\uff1a<\/p>\n<pre>sheet1&nbsp;=&nbsp;workbook.add_worksheet('test_sheet')<\/pre>\n<p>\u521b\u5efa\u5b8c\u6210\u540e\uff0c\u9700\u8981\u5173\u95ed workbook\uff0c\u8fd9\u4e00\u6b65\u4f1a\u5c06\u6211\u4eec\u521a\u624d\u521b\u5efa\u7684 workbook \u8fdb\u884c\u4fdd\u5b58\u3002<\/p>\n<pre>workbook.close()<\/pre>\n<p>\u597d\u4e86\uff0c\u6211\u4eec\u5df2\u7ecf\u521b\u5efa\u597d\u4e86\u4e00\u4e2a excel \uff0c\u64cd\u4f5c\u7ed3\u675f\uff0c\u4e0b\u8bfe\u3002<\/p>\n<p>\u8001\u5e08\uff0c\u4f60\u56de\u6765\uff0c\u8fd9\u5c31\u5b8c\u4e86\uff1f<\/p>\n<p style=\"text-align: center\"><img decoding=\"async\" src=\"https:\/\/oss.py.cn\/pycn\/upload\/image\/972\/889\/443\/1575092733991140.gif\" class=\"aligncenter\"><\/p>\n<p>\u6211\u4eec\u63a5\u7740\u4ecb\u7ecd\u5982\u4f55\u5c06\u6570\u636e\u5199\u5165\u81f3 Excel \u4e2d\u3002<\/p>\n<p>\u9996\u5148\u6211\u4eec\u53ef\u4ee5\u5148\u8bbe\u7f6e\u4e00\u4e9b\u7684\u5355\u5143\u683c\u7684\u683c\u5f0f\uff1a<\/p>\n<pre>workfomat&nbsp;=&nbsp;workbook.add_format()\n#&nbsp;\u5b57\u4f53\u52a0\u7c97\nworkfomat.set_bold(True)\n#&nbsp;\u5355\u5143\u683c\u8fb9\u6846\u5bbd\u5ea6\nworkfomat.set_border(1)\n#&nbsp;\u5bf9\u9f50\u65b9\u5f0f\nworkfomat.set_align('left')\n#&nbsp;\u683c\u5f0f\u5316\u6570\u636e\u683c\u5f0f\u4e3a\u5c0f\u6570\u70b9\u540e\u4e24\u4f4d\nworkfomat.set_num_format('0.00')<\/pre>\n<p>\u7136\u540e\u6211\u4eec\u5c06\u5185\u5bb9\u5199\u5165\uff0c\u5177\u4f53\u5185\u5bb9\u5c0f\u7f16\u61d2\u5f97\u60f3\u4e86\uff0c\u76f4\u63a5\u590d\u5236\u4e0a\u4e00\u7bc7\u6587\u7ae0\u4e2d\u7684\u5185\u5bb9\uff1a<\/p>\n<pre>heads&nbsp;=&nbsp;['',&nbsp;'\u8bed\u6587',&nbsp;'\u6570\u5b66',&nbsp;'\u82f1\u8bed']\ndatas&nbsp;=&nbsp;[\n&nbsp;&nbsp;&nbsp;&nbsp;['\u5c0f\u660e',&nbsp;76,&nbsp;85,&nbsp;95],\n&nbsp;&nbsp;&nbsp;&nbsp;['\u5c0f\u7ea2',&nbsp;85,&nbsp;58,&nbsp;92],\n&nbsp;&nbsp;&nbsp;&nbsp;['\u5c0f\u738b',&nbsp;98,&nbsp;96,&nbsp;91]\n]\nsheet1.write_row('A1',&nbsp;heads,&nbsp;workfomat)\nsheet1.write_row('A2',&nbsp;datas[0],&nbsp;workfomat)\nsheet1.write_row('A3',&nbsp;datas[1],&nbsp;workfomat)\nsheet1.write_row('A4',&nbsp;datas[2],&nbsp;workfomat)<\/pre>\n<p>\u7136\u540e\u6267\u884c\u7a0b\u5e8f\uff0c\u6211\u4eec\u6765\u770b\u4e0b\u6700\u7ec8\u8f93\u51fa\u7684\u7ed3\u679c\uff1a<\/p>\n<p style=\"text-align:center\"><img decoding=\"async\" src=\"https:\/\/oss.py.cn\/pycn\/upload\/image\/810\/141\/264\/1575092749263008.png\" style=\"width: 432px;height: 201px\" class=\"aligncenter\"><\/p>\n<p>\u9664\u4e86\u53ef\u4ee5\u8fd9\u6837\u8f93\u51fa\u4ee5\u5916\uff0c\u6211\u4eec\u8fd8\u53ef\u4ee5\u6307\u5b9a\u8f93\u51fa\u7684\u5355\u5143\u683c\u683c\u5f0f\uff1a<\/p>\n<p>\u6211\u4eec\u5217\u4e3e\u4e00\u4e2a\u6bd4\u8f83\u590d\u6742\u7684\u8f93\u51fa\u65e5\u671f\u7c7b\u578b\uff1a<\/p>\n<pre>fomat1&nbsp;=&nbsp;workbook.add_format({'num_format':&nbsp;'yy\/mm\/dd\/&nbsp;hh:mm:ss'})\nsheet1.write_datetime('E5',&nbsp;datetime.datetime(2019,&nbsp;11,&nbsp;9,&nbsp;22,&nbsp;44,&nbsp;26),&nbsp;fomat1)<\/pre>\n<p>\u6ce8\u610f\uff1a \u4e0a\u9762\u7684\u683c\u5f0f\u5316\u4e00\u5b9a\u8981\u52a0\uff0c\u5426\u5219\u5728 Excel \u4e2d\u663e\u793a\u51fa\u6765\u7684\u53ea\u4f1a\u662f\u4e00\u4e2a\u65f6\u95f4\u6233\u3002<\/p>\n<p>\u5176\u4ed6\u7684\u8f93\u51fa\u7c7b\u578b\u5c0f\u7f16\u8fd9\u91cc\u5c31\u4e0d\u4e00\u4e00\u4e3e\u4f8b\u4e86\uff0c\u4e0b\u9762\u5217\u51fa\u4e00\u4e9b\u5e38\u7528\u7684\uff1a<\/p>\n<pre>#&nbsp;\u5b57\u7b26\u4e32\u7c7b\u578b\nsheet1.write_string()\n#&nbsp;\u6570\u5b57\u578b\nsheet1.wirte_number()\n#&nbsp;\u7a7a\u7c7b\u578b\nsheet1.write_blank()\n#&nbsp;\u516c\u5f0f\nsheet1.write_formula()\n#&nbsp;\u5e03\u5c14\u578b\nsheet1.write_boolean()\n#&nbsp;\u8d85\u94fe\u63a5\nsheet1.write_url()<\/pre>\n<p>\u6211\u4eec\u8fd8\u53ef\u4ee5\u5728 Excel \u4e2d\u63d2\u5165\u56fe\u7247\uff0c\u6837\u4f8b\u5982\u4e0b\uff1a<\/p>\n<pre>sheet1.insert_image('I6',&nbsp;'wx.jpg')<\/pre>\n<p>\u8bed\u6cd5\u5982\u4e0b\uff1a<\/p>\n<pre>insert_image(row,&nbsp;col,&nbsp;image[,&nbsp;options])\nrow\uff1a\u884c\u5750\u6807\uff0c\u8d77\u59cb\u7d22\u5f15\u503c\u4e3a0\uff1b\ncol\uff1a\u5217\u5750\u6807\uff0c\u8d77\u59cb\u7d22\u5f15\u503c\u4e3a0\uff1b\nimage\uff1astring\u7c7b\u578b\uff0c\u662f\u56fe\u7247\u8def\u5f84\uff1b\noptions\uff1adict\u7c7b\u578b\uff0c\u662f\u53ef\u9009\u53c2\u6570\uff0c\u7528\u4e8e\u6307\u5b9a\u56fe\u7247\u4f4d\u7f6e\uff0c\u5982URL\u7b49\u4fe1\u606f\uff1b<\/pre>\n<p>\u6211\u4eec\u8fd8\u53ef\u4ee5\u5728 Excel \u4e2d\u7ed8\u56fe\uff0c\u652f\u6301\u5305\u62ec\u9762\u79ef\u3001\u6761\u5f62\u56fe\u3001\u67f1\u72b6\u56fe\u3001\u6298\u7ebf\u56fe\u3001\u6563\u70b9\u56fe\u7b49\u3002<\/p>\n<p>\u56fe\u8868\u5bf9\u8c61\u662f\u901a\u8fc7 Workbook add_chart() \u65b9\u6cd5\u521b\u5efa\u7684\uff0c\u5176\u4e2d\u6307\u5b9a\u4e86\u56fe\u8868\u7c7b\u578b\uff1a<\/p>\n<pre>chart&nbsp;=&nbsp;workbook.add_chart({'type':&nbsp;'column'})<\/pre>\n<p>\u5e38\u89c1\u7684\u56fe\u8868\u6837\u5f0f\u5982\u4e0b\uff1a<\/p>\n<pre>area\uff1a\u9762\u79ef\u6837\u5f0f\u7684\u56fe\u8868\nbar\uff1a\u6761\u5f62\u56fe\ncolumn\uff1a\u67f1\u72b6\u56fe\nline\uff1a\u7ebf\u6761\u6837\u5f0f\u7684\u56fe\u8868\npie\uff1a\u997c\u5f62\u56fe\nscatter\uff1a\u6563\u70b9\u56fe\nstock\uff1a\u80a1\u7968\u6837\u5f0f\u7684\u56fe\u8868\nradar\uff1a\u96f7\u8fbe\u6837\u5f0f\u7684\u56fe\u8868<\/pre>\n<p>\u7136\u540e\u4f7f\u7528 insert_chart() Worksheet\u65b9\u6cd5\u5c06\u5176\u4f5c\u4e3a\u5d4c\u5165\u7684\u56fe\u8868\u63d2\u5165\u5230\u5de5\u4f5c\u8868\u4e2d\uff1a<\/p>\n<pre>sheet1.insert_chart('A7',&nbsp;chart)<\/pre>\n<p>\u5b8c\u6574\u793a\u4f8b\u5982\u4e0b\uff1a<\/p>\n<pre>chart&nbsp;=&nbsp;workbook.add_chart({'type':&nbsp;'column'})\nchart.add_series({'values':&nbsp;'=test_sheet!$B$2:$B$4'})\nchart.add_series({'values':&nbsp;'=test_sheet!$C$2:$C$4'})\nchart.add_series({'values':&nbsp;'=test_sheet!$D$2:$D$4'})\nsheet1.insert_chart('A7',&nbsp;chart)<\/pre>\n<p>\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/oss.py.cn\/pycn\/upload\/image\/546\/255\/978\/1575092778662316.png\" class=\"aligncenter\"><\/p>\n<p>\u4e00\u4e9b\u5e38\u7528\u7684\u7b80\u5355\u7684\u64cd\u4f5c\u5c31\u4ecb\u7ecd\u5230\u8fd9\u91cc\uff0c\u60f3\u4e86\u89e3\u66f4\u591a\u7684\u540c\u5b66\u53ef\u4ee5\u8bbf\u95ee\u5b98\u65b9\u6587\u6863\uff0c\u94fe\u63a5\uff1ahttps:\/\/xlsxwriter.readthedocs.io\/ \u3002<\/p>\n<p>python\u5b66\u4e60\u7f51\uff0c\u5927\u91cf\u7684\u514d\u8d39python\u89c6\u9891\u6559\u7a0b\uff0c\u6b22\u8fce\u5728\u7ebf\u5b66\u4e60\uff01<\/p>\n<p>\u672c\u6587\u8f6c\u81ea\uff1ahttps:\/\/blog.csdn.net\/meteor_93\/article\/details\/103135021<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5199\u5165 Excel \u9996\u5148\u5f53\u7136\u662f\u5b89\u88c5\u7b2c\u4e09\u65b9\u6a21\u5757\uff1a pip&nbsp;install&nbsp;xlsxwriter [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":169849,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[182397],"tags":[],"class_list":["post-169848","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python"],"_links":{"self":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/169848","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/comments?post=169848"}],"version-history":[{"count":0,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/169848\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/media\/169849"}],"wp:attachment":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/media?parent=169848"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/categories?post=169848"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/tags?post=169848"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}