{"id":887,"date":"2022-06-14T14:17:29","date_gmt":"2022-06-14T06:17:29","guid":{"rendered":"http:\/\/47.98.165.52\/?p=887"},"modified":"2022-06-14T14:17:29","modified_gmt":"2022-06-14T06:17:29","slug":"python-%e6%93%8d%e4%bd%9c-excel-%e6%8a%a5%e8%a1%a8%e8%87%aa%e5%8a%a8%e5%8c%96%e6%8c%87%e5%8d%97","status":"publish","type":"post","link":"\/?p=887","title":{"rendered":"Python \u64cd\u4f5c Excel \u62a5\u8868\u81ea\u52a8\u5316\u6307\u5357"},"content":{"rendered":"<h1>Python \u64cd\u4f5c Excel \u62a5\u8868\u81ea\u52a8\u5316\u6307\u5357\uff01<\/h1>\n<p>\u516c\u4f17\u53f7<\/p>\n<p>\u4f5c\u8005\uff1a\u8d85\u7ea7\u5927\u6d0b\u8471806<\/p>\n<p>\u6765\u6e90\uff1a<a href=\"https:\/\/blog.csdn.net\/u014779536\/article\/details\/108182833\">https:\/\/blog.csdn.net\/u014779536\/article\/details\/108182833<\/a><\/p>\n<h2>0. Python Excel\u5e93\u5bf9\u6bd4<\/h2>\n<p><strong>\u6211\u4eec\u5148\u6765\u770b\u4e00\u4e0bpython\u4e2d\u80fd\u64cd\u4f5cExcel\u7684\u5e93\u5bf9\u6bd4\uff08\u4e00\u5171\u4e5d\u4e2a\u5e93\uff09\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141359239.jpeg\" alt=\"\u56fe\u7247\" \/><\/p>\n<h2>1. Python xlrd \u8bfb\u53d6 \u64cd\u4f5cExcel<\/h2>\n<h3><strong>1.1 xlrd\u6a21\u5757\u4ecb\u7ecd<\/strong><\/h3>\n<p>\uff081\uff09\u4ec0\u4e48\u662fxlrd\u6a21\u5757\uff1f<\/p>\n<ul>\n<li>python\u64cd\u4f5cexcel\u4e3b\u8981\u7528\u5230xlrd\u548cxlwt\u8fd9\u4e24\u4e2a\u5e93\uff0c\u5373xlrd\u662f\u8bfbexcel\uff0cxlwt\u662f\u5199excel\u7684\u5e93\u3002<\/li>\n<\/ul>\n<p>\uff082\uff09\u4e3a\u4ec0\u4e48\u4f7f\u7528xlrd\u6a21\u5757\uff1f<\/p>\n<ul>\n<li>\u5728UI\u81ea\u52a8\u5316\u6216\u8005\u63a5\u53e3\u81ea\u52a8\u5316\u4e2d\u6570\u636e\u7ef4\u62a4\u662f\u4e00\u4e2a\u6838\u5fc3\uff0c\u6240\u4ee5\u6b64\u6a21\u5757\u975e\u5e38\u5b9e\u7528\u3002<\/li>\n<\/ul>\n<p>xlrd\u6a21\u5757\u53ef\u4ee5\u7528\u4e8e\u8bfb\u53d6Excel\u7684\u6570\u636e\uff0c\u901f\u5ea6\u975e\u5e38\u5feb\uff0c\u63a8\u8350\u4f7f\u7528\uff01<\/p>\n<p><strong>\u5b98\u65b9\u6587\u6863\uff1a<\/strong><a href=\"https:\/\/xlrd.readthedocs.io\/en\/latest\/\">https:\/\/xlrd.readthedocs.io\/en\/latest\/<\/a><\/p>\n<h3><strong>1.2 \u5b89\u88c5xlrd\u6a21\u5757<\/strong><\/h3>\n<ul>\n<li>\u5230python\u5b98\u7f51\u4e0b\u8f7dhttp:\/\/pypi.python.org\/pypi\/xlrd\u6a21\u5757\u5b89\u88c5\uff0c\u524d\u63d0\u662f\u5df2\u7ecf\u5b89\u88c5\u4e86python \u73af\u5883\u3002<\/li>\n<li>\u6216\u8005\u5728cmd\u7a97\u53e3 pip install xlrd<\/li>\n<\/ul>\n<pre><code>pip install xlrd<\/code><\/pre>\n<p>\u6211\u8fd9\u91cc\u662fanaconda\u81ea\u5e26\u6709xlrd\uff0c\u6240\u4ee5\u63d0\u793a\u5df2\u7ecf\u5b89\u88c5\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141400311.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>1.3 \u4f7f\u7528\u4ecb\u7ecd<\/strong><\/h3>\n<ol>\n<li>\u5e38\u7528\u5355\u5143\u683c\u7684\u6570\u636e\u7c7b\u578b<\/li>\n<\/ol>\n<ul>\n<li>empty\uff08\u7a7a\u7684\uff09<\/li>\n<li>string\uff08text\uff09<\/li>\n<li>number<\/li>\n<li>date<\/li>\n<li>boolean<\/li>\n<li>error<\/li>\n<li>blank\uff08\u7a7a\u767d\u8868\u683c\uff09<\/li>\n<\/ul>\n<ol>\n<li>\u5bfc\u5165\u6a21\u5757<\/li>\n<\/ol>\n<pre><code class=\"language-python\">import xlrd<\/code><\/pre>\n<ol>\n<li>\u6253\u5f00Excel\u6587\u4ef6\u8bfb\u53d6\u6570\u636e<\/li>\n<\/ol>\n<pre><code class=\"language-python\">data = xlrd.open_workbook(filename)#\u6587\u4ef6\u540d\u4ee5\u53ca\u8def\u5f84\uff0c\u5982\u679c\u8def\u5f84\u6216\u8005\u6587\u4ef6\u540d\u6709\u4e2d\u6587\u7ed9\u524d\u9762\u52a0\u4e00\u4e2a r<\/code><\/pre>\n<ol>\n<li>\u5e38\u7528\u7684\u51fd\u6570<\/li>\n<\/ol>\n<ul>\n<li>excel\u4e2d\u6700\u91cd\u8981\u7684\u65b9\u6cd5\u5c31\u662fbook\u548csheet\u7684\u64cd\u4f5c<\/li>\n<\/ul>\n<p>\uff081\uff09\u83b7\u53d6book\uff08excel\u6587\u4ef6\uff09\u4e2d\u4e00\u4e2a\u5de5\u4f5c\u8868<\/p>\n<pre><code class=\"language-python\">table = data.sheets()[0]             #\u901a\u8fc7\u7d22\u5f15\u987a\u5e8f\u83b7\u53d6\ntable = data.sheet_by_index(sheet_indx)  #\u901a\u8fc7\u7d22\u5f15\u987a\u5e8f\u83b7\u53d6\ntable = data.sheet_by_name(sheet_name)  #\u901a\u8fc7\u540d\u79f0\u83b7\u53d6\n\n# \u4ee5\u4e0a\u4e09\u4e2a\u51fd\u6570\u90fd\u4f1a\u8fd4\u56de\u4e00\u4e2axlrd.sheet.Sheet()\u5bf9\u8c61\n\nnames = data.sheet_names()        #\u8fd4\u56debook\u4e2d\u6240\u6709\u5de5\u4f5c\u8868\u7684\u540d\u5b57\ndata.sheet_loaded(sheet_name or indx)    # \u68c0\u67e5\u67d0\u4e2asheet\u662f\u5426\u5bfc\u5165\u5b8c\u6bd5<\/code><\/pre>\n<p>\uff082\uff09 \u884c\u7684\u64cd\u4f5c<\/p>\n<pre><code class=\"language-python\">nrows = table.nrows\n    # \u83b7\u53d6\u8be5sheet\u4e2d\u7684\u884c\u6570\uff0c\u6ce8\uff0c\u8fd9\u91cctable.nrows\u540e\u9762\u4e0d\u5e26().\n\ntable.row(rowx)\n    # \u8fd4\u56de\u7531\u8be5\u884c\u4e2d\u6240\u6709\u7684\u5355\u5143\u683c\u5bf9\u8c61\u7ec4\u6210\u7684\u5217\u8868,\u8fd9\u4e0etabel.raw()\u65b9\u6cd5\u5e76\u6ca1\u6709\u533a\u522b\u3002\n\ntable.row_slice(rowx)\n    # \u8fd4\u56de\u7531\u8be5\u884c\u4e2d\u6240\u6709\u7684\u5355\u5143\u683c\u5bf9\u8c61\u7ec4\u6210\u7684\u5217\u8868\n\ntable.row_types(rowx, start_colx=0, end_colx=None)\n    # \u8fd4\u56de\u7531\u8be5\u884c\u4e2d\u6240\u6709\u5355\u5143\u683c\u7684\u6570\u636e\u7c7b\u578b\u7ec4\u6210\u7684\u5217\u8868\uff1b    \n    # \u8fd4\u56de\u503c\u4e3a\u903b\u8f91\u503c\u5217\u8868\uff0c\u82e5\u7c7b\u578b\u4e3aempy\u5219\u4e3a0\uff0c\u5426\u5219\u4e3a1\n\ntable.row_values(rowx, start_colx=0, end_colx=None)\n    # \u8fd4\u56de\u7531\u8be5\u884c\u4e2d\u6240\u6709\u5355\u5143\u683c\u7684\u6570\u636e\u7ec4\u6210\u7684\u5217\u8868\n\ntable.row_len(rowx)\n    # \u8fd4\u56de\u8be5\u884c\u7684\u6709\u6548\u5355\u5143\u683c\u957f\u5ea6\uff0c\u5373\u8fd9\u4e00\u884c\u6709\u591a\u5c11\u4e2a\u6570\u636e<\/code><\/pre>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141359684.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\uff083\uff09\u5217\uff08colnum\uff09\u7684\u64cd\u4f5c<\/p>\n<pre><code class=\"language-python\">ncols = table.ncols\n    # \u83b7\u53d6\u5217\u8868\u7684\u6709\u6548\u5217\u6570\n\ntable.col(colx, start_rowx=0, end_rowx=None)\n    # \u8fd4\u56de\u7531\u8be5\u5217\u4e2d\u6240\u6709\u7684\u5355\u5143\u683c\u5bf9\u8c61\u7ec4\u6210\u7684\u5217\u8868\n\ntable.col_slice(colx, start_rowx=0, end_rowx=None)\n    # \u8fd4\u56de\u7531\u8be5\u5217\u4e2d\u6240\u6709\u7684\u5355\u5143\u683c\u5bf9\u8c61\u7ec4\u6210\u7684\u5217\u8868\n\ntable.col_types(colx, start_rowx=0, end_rowx=None)\n    # \u8fd4\u56de\u7531\u8be5\u5217\u4e2d\u6240\u6709\u5355\u5143\u683c\u7684\u6570\u636e\u7c7b\u578b\u7ec4\u6210\u7684\u5217\u8868\n\ntable.col_values(colx, start_rowx=0, end_rowx=None)\n    # \u8fd4\u56de\u7531\u8be5\u5217\u4e2d\u6240\u6709\u5355\u5143\u683c\u7684\u6570\u636e\u7ec4\u6210\u7684\u5217\u8868<\/code><\/pre>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141400366.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\uff084\uff09\u5355\u5143\u683c\u7684\u64cd\u4f5c<\/p>\n<pre><code class=\"language-python\">table.cell(rowx,colx)\n    # \u8fd4\u56de\u5355\u5143\u683c\u5bf9\u8c61\n\ntable.cell_type(rowx,colx)\n    # \u8fd4\u56de\u5bf9\u5e94\u4f4d\u7f6e\u5355\u5143\u683c\u4e2d\u7684\u6570\u636e\u7c7b\u578b\n\ntable.cell_value(rowx,colx)\n    # \u8fd4\u56de\u5bf9\u5e94\u4f4d\u7f6e\u5355\u5143\u683c\u4e2d\u7684\u6570\u636e<\/code><\/pre>\n<h3><strong>1.4 \u5b9e\u6218\u8bad\u7ec3<\/strong><\/h3>\n<p>\u6211\u4eec\u5148\u5728\u8868\u683c\u653e\u5165\u4ee5\u4e0b\u6570\u636e\uff0c\u70b9\u51fb\u4fdd\u5b58\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401564.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\u4f7f\u7528xlrd\u6a21\u5757\u8fdb\u884c\u8bfb\u53d6\uff1a<\/p>\n<pre><code class=\"language-python\">import xlrd\n\nxlsx = xlrd.open_workbook(&#039;.\/3_1 xlrd \u8bfb\u53d6 \u64cd\u4f5c\u7ec3\u4e60.xlsx&#039;)\n\n# \u901a\u8fc7sheet\u540d\u67e5\u627e\uff1axlsx.sheet_by_name(&quot;sheet1&quot;)\n# \u901a\u8fc7\u7d22\u5f15\u67e5\u627e\uff1axlsx.sheet_by_index(3)\ntable = xlsx.sheet_by_index(0)\n\n# \u83b7\u53d6\u5355\u4e2a\u8868\u683c\u503c (2,1)\u8868\u793a\u83b7\u53d6\u7b2c3\u884c\u7b2c2\u5217\u5355\u5143\u683c\u7684\u503c\nvalue = table.cell_value(2, 1)\nprint(&quot;\u7b2c3\u884c2\u5217\u503c\u4e3a&quot;,value)\n\n# \u83b7\u53d6\u8868\u683c\u884c\u6570\nnrows = table.nrows\nprint(&quot;\u8868\u683c\u4e00\u5171\u6709&quot;,nrows,&quot;\u884c&quot;)\n\n# \u83b7\u53d6\u7b2c4\u5217\u6240\u6709\u503c\uff08\u5217\u8868\u751f\u6210\u5f0f\uff09\nname_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]\nprint(&quot;\u7b2c4\u5217\u6240\u6709\u7684\u503c\uff1a&quot;,name_list)<\/code><\/pre>\n<p>\u6253\u5370\u7ed3\u679c\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401734.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\u5217\u8868\u751f\u6210\u5f0f\u4ecb\u7ecd\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401829.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\u5217\u8868\u751f\u6210\u5f0f\u5b66\u4e60\u94fe\u63a5\uff1a<\/p>\n<p><a href=\"https:\/\/www.liaoxuefeng.com\/wiki\/1016959663602400\/1017317609699776\">https:\/\/www.liaoxuefeng.com\/wiki\/1016959663602400\/1017317609699776<\/a><\/p>\n<h2>2. Python xlwt \u5199\u5165 \u64cd\u4f5cExcel\uff08\u4ec5\u9650xls\u683c\u5f0f\uff01\uff09<\/h2>\n<p>xlwt\u53ef\u4ee5\u7528\u4e8e\u5199\u5165\u65b0\u7684Excel\u8868\u683c\u6216\u8005\u5728\u539f\u8868\u683c\u57fa\u7840\u4e0a\u8fdb\u884c\u4fee\u6539\uff0c\u901f\u5ea6\u4e5f\u5f88\u5feb\uff0c\u63a8\u8350\u4f7f\u7528\uff01<\/p>\n<p>\u5b98\u65b9\u6587\u6863\uff1a<a href=\"https:\/\/xlwt.readthedocs.io\/en\/latest\/\">https:\/\/xlwt.readthedocs.io\/en\/latest\/<\/a><\/p>\n<h3><strong>2.1 pip\u5b89\u88c5xlwt<\/strong><\/h3>\n<pre><code class=\"language-python\">pip install xlwt<\/code><\/pre>\n<p>\u6211\u8fd9\u91cc\u662fanaconda\u81ea\u5e26\u6709xlwt\uff0c\u6240\u4ee5\u63d0\u793a\u5df2\u7ecf\u5b89\u88c5\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401453.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.2 \u4f7f\u7528xlwt\u521b\u5efa\u65b0\u8868\u683c\u5e76\u5199\u5165<\/strong><\/h3>\n<p>\u4e00\u5f00\u59cb\u76ee\u5f55\u4e0b\u53ea\u6709\u8fd9\u4e24\u4e2a\u6587\u4ef6\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401021.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\u7f16\u5199xlwt\u65b0\u8868\u683c\u5199\u5165\u7a0b\u5e8f:<\/p>\n<pre><code class=\"language-python\"># 3.2.2 \u4f7f\u7528xlwt\u521b\u5efa\u65b0\u8868\u683c\u5e76\u5199\u5165\ndef fun3_2_2():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;)\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401433.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\u751f\u6210\u7684\u8868\u683c\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"https:\/\/mmbiz.qpic.cn\/mmbiz_png\/PvP6qjUpvIro0I67PdvBOWIE9fbaibCXb53BCR2WzooG6H5DaEJdjDra2KdLg7aVXVak1tTvDWN9swaTVtNgNGw\/640?wx_fmt=png&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.3 xlwt \u8bbe\u7f6e\u5b57\u4f53\u683c\u5f0f<\/strong><\/h3>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code># 3.2.3 xlwt\u8bbe\u7f6e\u5b57\u4f53\u683c\u5f0f\ndef fun3_2_3():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u521d\u59cb\u5316\u6837\u5f0f\n    style = xlwt.XFStyle()\n\n    # \u4e3a\u6837\u5f0f\u521b\u5efa\u5b57\u4f53\n    font = xlwt.Font()\n    font.name = &#039;Times New Roman&#039;   #\u5b57\u4f53\n    font.bold = True                #\u52a0\u7c97\n    font.underline = True           #\u4e0b\u5212\u7ebf\n    font.italic = True              #\u659c\u4f53\n\n    # \u8bbe\u7f6e\u6837\u5f0f\n    style.font = font\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;,style)\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141401975.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.4 xlwt \u8bbe\u7f6e\u5217\u5bbd<\/strong><\/h3>\n<p>xlwt\u4e2d\u5217\u5bbd\u7684\u503c\u8868\u793a\u65b9\u6cd5\uff1a\u9ed8\u8ba4\u5b57\u4f530\u76841\/256\u4e3a\u8861\u91cf\u5355\u4f4d\u3002<\/p>\n<p>xlwt\u521b\u5efa\u65f6\u4f7f\u7528\u7684\u9ed8\u8ba4\u5bbd\u5ea6\u4e3a2960\uff0c\u65e211\u4e2a\u5b57\u7b260\u7684\u5bbd\u5ea6<\/p>\n<p>\u6240\u4ee5\u6211\u4eec\u5728\u8bbe\u7f6e\u5217\u5bbd\u65f6\u53ef\u4ee5\u7528\u5982\u4e0b\u65b9\u6cd5\uff1a<\/p>\n<p>width = 256 * 20 256\u4e3a\u8861\u91cf\u5355\u4f4d\uff0c20\u8868\u793a20\u4e2a\u5b57\u7b26\u5bbd\u5ea6<\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.2.4 \u8bbe\u7f6e\u5217\u5bbd\ndef fun3_2_4():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;)\n\n    # \u8bbe\u7f6e\u5217\u5bbd\n    worksheet.col(0).width = 256*20\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.5 xlwt \u8bbe\u7f6e\u884c\u9ad8<\/strong><\/h3>\n<p>\u5728xlwt\u4e2d\u6ca1\u6709\u7279\u5b9a\u7684\u51fd\u6570\u6765\u8bbe\u7f6e\u9ed8\u8ba4\u7684\u5217\u5bbd\u53ca\u884c\u9ad8<\/p>\n<p>\u884c\u9ad8\u662f\u5728\u5355\u5143\u683c\u7684\u6837\u5f0f\u4e2d\u8bbe\u7f6e\u7684\uff0c\u4f60\u53ef\u4ee5\u901a\u8fc7\u81ea\u52a8\u6362\u884c\u901a\u8fc7\u8f93\u5165\u6587\u5b57\u7684\u591a\u5c11\u6765\u786e\u5b9a\u884c\u9ad8<\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code># 3.2.5 \u8bbe\u7f6e\u884c\u9ad8\ndef fun3_2_5():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;)\n\n    # \u8bbe\u7f6e\u884c\u9ad8\n    style = xlwt.easyxf(&#039;font:height 360;&#039;)  # 18pt,\u7c7b\u578b\u5c0f\u521d\u7684\u5b57\u53f7\n    row = worksheet.row(0)\n    row.set_style(style)\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.6 xlwt \u5408\u5e76\u5217\u548c\u884c<\/strong><\/h3>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code># 3.2.6 \u5408\u5e76\u5217\u548c\u884c\ndef fun3_2_6():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n\n    # \u5408\u5e76 \u7b2c1\u884c\u5230\u7b2c2\u884c \u7684 \u7b2c0\u5217\u5230\u7b2c3\u5217\n    worksheet.write_merge(1, 2, 0, 3, &#039;Merge Test&#039;)\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.7 xlwt \u6dfb\u52a0\u8fb9\u6846<\/strong><\/h3>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.2.7 \u6dfb\u52a0\u8fb9\u6846\ndef fun3_2_7():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n\n    # \u8bbe\u7f6e\u8fb9\u6846\u6837\u5f0f\n    borders = xlwt.Borders()  # Create Borders\n\n    # May be:   NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR,\n    #           MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,\n    #           MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.\n    # DASHED\u865a\u7ebf\n    # NO_LINE\u6ca1\u6709\n    # THIN\u5b9e\u7ebf\n\n    borders.left = xlwt.Borders.DASHED\n    borders.right = xlwt.Borders.DASHED\n    borders.top = xlwt.Borders.DASHED\n    borders.bottom = xlwt.Borders.DASHED\n    borders.left_colour = 0x40\n    borders.right_colour = 0x40\n    borders.top_colour = 0x40\n    borders.bottom_colour = 0x40\n\n    style = xlwt.XFStyle()  # Create Style\n    style.borders = borders  # Add Borders to Style\n\n    worksheet.write(0, 0, &#039;\u5185\u5bb91&#039;, style)\n\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;)\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.8 xlwt\u4e3a\u5355\u5143\u683c\u8bbe\u7f6e\u80cc\u666f\u8272<\/strong><\/h3>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># \u8bbe\u7f6e\u5355\u5143\u683c\u80cc\u666f\u8272\ndef fun3_2_8():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n\n    # \u521b\u5efa\u6837\u5f0f\n    pattern = xlwt.Pattern()\n\n    # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12\n    pattern.pattern = xlwt.Pattern.SOLID_PATTERN\n\n    # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,\n    # 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow ,\n    # almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...\n    pattern.pattern_fore_colour = 5\n    style = xlwt.XFStyle()\n    style.pattern = pattern\n\n    # \u4f7f\u7528\u6837\u5f0f\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;,style)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>2.9 xlwt\u8bbe\u7f6e\u5355\u5143\u683c\u5bf9\u9f50<\/strong><\/h3>\n<p>\u4f7f\u7528xlwt\u4e2d\u7684Alignment\u6765\u8bbe\u7f6e\u5355\u5143\u683c\u7684\u5bf9\u9f50\u65b9\u5f0f\uff0c\u5176\u4e2dhorz\u4ee3\u8868\u6c34\u5e73\u5bf9\u9f50\u65b9\u5f0f\uff0cvert\u4ee3\u8868\u5782\u76f4\u5bf9\u9f50\u65b9\u5f0f\u3002<\/p>\n<p>VERT_TOP = 0x00 \u4e0a\u7aef\u5bf9\u9f50<br \/>\nVERT_CENTER = 0x01 \u5c45\u4e2d\u5bf9\u9f50\uff08\u5782\u76f4\u65b9\u5411\u4e0a\uff09<br \/>\nVERT_BOTTOM = 0x02 \u4f4e\u7aef\u5bf9\u9f50<br \/>\nHORZ_LEFT = 0x01 \u5de6\u7aef\u5bf9\u9f50<br \/>\nHORZ_CENTER = 0x02 \u5c45\u4e2d\u5bf9\u9f50\uff08\u6c34\u5e73\u65b9\u5411\u4e0a\uff09<br \/>\nHORZ_RIGHT = 0x03 \u53f3\u7aef\u5bf9\u9f50<\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># \u8bbe\u7f6e\u5355\u5143\u683c\u5bf9\u9f50\ndef fun3_2_9():\n    # \u521b\u5efa\u65b0\u7684workbook\uff08\u5176\u5b9e\u5c31\u662f\u521b\u5efa\u65b0\u7684excel\uff09\n    workbook = xlwt.Workbook(encoding= &#039;ascii&#039;)\n\n    # \u521b\u5efa\u65b0\u7684sheet\u8868\n    worksheet = workbook.add_sheet(&quot;My new Sheet&quot;)\n\n    # \u5f80\u8868\u683c\u5199\u5165\u5185\u5bb9\n    worksheet.write(0,0, &quot;\u5185\u5bb91&quot;)\n\n    # \u8bbe\u7f6e\u6837\u5f0f\n    style = xlwt.XFStyle()\n    al = xlwt.Alignment()\n    # VERT_TOP = 0x00       \u4e0a\u7aef\u5bf9\u9f50\n    # VERT_CENTER = 0x01    \u5c45\u4e2d\u5bf9\u9f50\uff08\u5782\u76f4\u65b9\u5411\u4e0a\uff09\n    # VERT_BOTTOM = 0x02    \u4f4e\u7aef\u5bf9\u9f50\n    # HORZ_LEFT = 0x01      \u5de6\u7aef\u5bf9\u9f50\n    # HORZ_CENTER = 0x02    \u5c45\u4e2d\u5bf9\u9f50\uff08\u6c34\u5e73\u65b9\u5411\u4e0a\uff09\n    # HORZ_RIGHT = 0x03     \u53f3\u7aef\u5bf9\u9f50\n    al.horz = 0x02  # \u8bbe\u7f6e\u6c34\u5e73\u5c45\u4e2d\n    al.vert = 0x01  # \u8bbe\u7f6e\u5782\u76f4\u5c45\u4e2d\n    style.alignment = al\n\n    # \u5bf9\u9f50\u5199\u5165\n    worksheet.write(2,1, &quot;\u5185\u5bb92&quot;,style)\n\n    # \u4fdd\u5b58\n    workbook.save(&quot;\u65b0\u521b\u5efa\u7684\u8868\u683c.xls&quot;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h2>3. Python xlutils \u4fee\u6539 \u64cd\u4f5cExcel<\/h2>\n<p>xlutils\u53ef\u7528\u4e8e\u62f7\u8d1d\u539fexcel\u6216\u8005\u5728\u539fexcel\u57fa\u7840\u4e0a\u8fdb\u884c\u4fee\u6539\uff0c\u5e76\u4fdd\u5b58\uff1b<\/p>\n<p>\u5b98\u65b9\u6587\u6863\uff1a<a href=\"https:\/\/xlutils.readthedocs.io\/en\/latest\/\">https:\/\/xlutils.readthedocs.io\/en\/latest\/<\/a><\/p>\n<h3><strong>3.1 pip\u5b89\u88c5xlutils<\/strong><\/h3>\n<pre><code class=\"language-python\">pip install xlutils<\/code><\/pre>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141403887.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>3.2 xlutils\u62f7\u8d1d\u6e90\u6587\u4ef6\uff08\u9700\u914d\u5408xlrd\u4f7f\u7528\uff09<\/strong><\/h3>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141403581.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>\u8868\u683c\u5185\u5bb9\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141403594.png\" alt=\"\u56fe\u7247\" \/><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.3.2 \u62f7\u8d1d\u6e90\u6587\u4ef6\ndef fun3_3_2():\n    workbook = xlrd.open_workbook(&#039;3_3 xlutils \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&#039;)  # \u6253\u5f00\u5de5\u4f5c\u7c3f\n    new_workbook = copy(workbook)  # \u5c06xlrd\u5bf9\u8c61\u62f7\u8d1d\u8f6c\u5316\u4e3axlwt\u5bf9\u8c61\n    new_workbook.save(&quot;new_test.xls&quot;)  # \u4fdd\u5b58\u5de5\u4f5c\u7c3f<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141404955.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p>\u5185\u5bb9\u4e3a\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141404869.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p>\u4e0d\u8fc7\u8868\u683c\u7684\u6837\u5f0f\u5168\u90e8\u6d88\u5931\u4e86\u3002<\/p>\n<h3><strong>3.3 xlutils \u8bfb\u53d6 \u5199\u5165 \uff08\u4e5f\u5c31\u662f\u4fee\u6539\uff09Excel \u8868\u683c\u4fe1\u606f<\/strong><\/h3>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.3.3 xlutils\u8bfb\u53d6 \u5199\u5165 Excel \u8868\u683c\u4fe1\u606f\ndef fun3_3_3():\n    # file_path\uff1a\u6587\u4ef6\u8def\u5f84\uff0c\u5305\u542b\u6587\u4ef6\u7684\u5168\u540d\u79f0\n    # formatting_info=True\uff1a\u4fdd\u7559Excel\u7684\u539f\u683c\u5f0f\uff08\u4f7f\u7528\u4e0exlsx\u6587\u4ef6\uff09\n    workbook = xlrd.open_workbook(&#039;3_3 xlutils \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&#039;)\n\n    new_workbook = copy(workbook)  # \u5c06xlrd\u5bf9\u8c61\u62f7\u8d1d\u8f6c\u5316\u4e3axlwt\u5bf9\u8c61\n\n    # \u8bfb\u53d6\u8868\u683c\u4fe1\u606f\n    sheet = workbook.sheet_by_index(0)\n    col2 = sheet.col_values(1)  # \u53d6\u51fa\u7b2c\u4e8c\u5217\n    cel_value = sheet.cell_value(1, 1)\n    print(col2)\n    print(cel_value)\n\n    # \u5199\u5165\u8868\u683c\u4fe1\u606f\n    write_save = new_workbook.get_sheet(0)\n    write_save.write(0, 0, &quot;xlutils\u5199\u5165\uff01&quot;)\n\n    new_workbook.save(&quot;new_test.xls&quot;)  # \u4fdd\u5b58\u5de5\u4f5c\u7c3f<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141404236.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p>\u590d\u5236\u4e86\u6e90\u6587\u4ef6\u4fe1\u606f\uff0c\u5e76\u8fdb\u884c\u4e86\u8ffd\u52a0\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141404391.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<h2>4. Python xlwings \u8bfb\u53d6 \u5199\u5165 \u4fee\u6539 \u64cd\u4f5cExcel<\/h2>\n<p><strong>xlwings\u6bd4\u8d77xlrd\u3001xlwt\u548cxlutils\uff0cxlwings\u53ef\u8c6a\u534e\u591a\u4e86\uff0c\u5b83\u5177\u5907\u4ee5\u4e0b\u7279\u70b9\uff1a<\/strong><\/p>\n<ul>\n<li>xlwings\u80fd\u591f\u975e\u5e38\u65b9\u4fbf\u7684\u8bfb\u5199Excel\u6587\u4ef6\u4e2d\u7684\u6570\u636e\uff0c\u5e76\u4e14\u80fd\u591f\u8fdb\u884c\u5355\u5143\u683c\u683c\u5f0f\u7684\u4fee\u6539<\/li>\n<li>\u53ef\u4ee5\u548cmatplotlib\u4ee5\u53capandas\u65e0\u7f1d\u8fde\u63a5\uff0c\u652f\u6301\u8bfb\u5199numpy\u3001pandas\u6570\u636e\u7c7b\u578b\uff0c\u5c06matplotlib\u53ef\u89c6\u5316\u56fe\u8868\u5bfc\u5165\u5230excel\u4e2d\u3002<\/li>\n<li>\u53ef\u4ee5\u8c03\u7528Excel\u6587\u4ef6\u4e2dVBA\u5199\u597d\u7684\u7a0b\u5e8f\uff0c\u4e5f\u53ef\u4ee5\u8ba9VBA\u8c03\u7528\u7528Python\u5199\u7684\u7a0b\u5e8f\u3002<\/li>\n<li>\u5f00\u6e90\u514d\u8d39\uff0c\u4e00\u76f4\u5728\u66f4\u65b0<\/li>\n<\/ul>\n<p>\u5b98\u7f51\u5730\u5740\uff1a<a href=\"https:\/\/www.xlwings.org\/\">https:\/\/www.xlwings.org\/<\/a><\/p>\n<p>\u5b98\u65b9\u6587\u6863\uff1a<a href=\"https:\/\/docs.xlwings.org\/en\/stable\/api.html\">https:\/\/docs.xlwings.org\/en\/stable\/api.html<\/a><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141405291.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141405060.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>4.1 pip\u5b89\u88c5xlwings<\/strong><\/h3>\n<pre><code>pip install xlwings<\/code><\/pre>\n<h3><strong>4.2 \u57fa\u672c\u64cd\u4f5c<\/strong><\/h3>\n<p>\u5f15\u5165\u5e93<\/p>\n<pre><code class=\"language-python\">import xlwings as xw <\/code><\/pre>\n<p>\u6253\u5f00Excel\u7a0b\u5e8f\uff0c\u9ed8\u8ba4\u8bbe\u7f6e\uff1a\u7a0b\u5e8f\u53ef\u89c1\uff0c\u53ea\u6253\u5f00\u4e0d\u65b0\u5efa\u5de5\u4f5c\u8584<\/p>\n<pre><code class=\"language-python\">app = xw.App(visible=True,add_book=False)\n#\u65b0\u5efa\u5de5\u4f5c\u7c3f (\u5982\u679c\u4e0d\u63a5\u4e0b\u4e00\u6761\u4ee3\u7801\u7684\u8bdd\uff0cExcel\u53ea\u4f1a\u4e00\u95ea\u800c\u8fc7\uff0c\u5356\u4e2a\u840c\u5c31\u8d70\u4e86\uff09\nwb = app.books.add()<\/code><\/pre>\n<p>\u6253\u5f00\u5df2\u6709\u5de5\u4f5c\u7c3f\uff08\u652f\u6301\u7edd\u5bf9\u8def\u5f84\u548c\u76f8\u5bf9\u8def\u5f84\uff09<\/p>\n<pre><code class=\"language-python\">wb = app.books.open(&#039;example.xlsx&#039;)\n#\u7ec3\u4e60\u7684\u65f6\u5019\u5efa\u8bae\u76f4\u63a5\u7528\u4e0b\u9762\u8fd9\u6761\n#wb = xw.Book(&#039;example.xlsx&#039;)\n#\u8fd9\u6837\u7684\u8bdd\u5c31\u4e0d\u4f1a\u9891\u7e41\u6253\u5f00\u65b0\u7684Excel<\/code><\/pre>\n<p>\u4fdd\u5b58\u5de5\u4f5c\u7c3f<\/p>\n<pre><code class=\"language-python\">wb.save(&#039;example.xlsx&#039;)<\/code><\/pre>\n<p>\u9000\u51fa\u5de5\u4f5c\u7c3f\uff08\u53ef\u7701\u7565\uff09<\/p>\n<pre><code class=\"language-python\">wb.close()<\/code><\/pre>\n<p>\u9000\u51faExcel<\/p>\n<pre><code class=\"language-python\">app.quit()<\/code><\/pre>\n<p><strong>\u4e09\u4e2a\u4f8b\u5b50\uff1a<\/strong><\/p>\n<p>\uff081\uff09\u6253\u5f00\u5df2\u5b58\u5728\u7684Excel\u6587\u6863<\/p>\n<pre><code class=\"language-python\"># \u5bfc\u5165xlwings\u6a21\u5757\nimport xlwings as xw\n\n# \u6253\u5f00Excel\u7a0b\u5e8f\uff0c\u9ed8\u8ba4\u8bbe\u7f6e\uff1a\u7a0b\u5e8f\u53ef\u89c1\uff0c\u53ea\u6253\u5f00\u4e0d\u65b0\u5efa\u5de5\u4f5c\u8584\uff0c\u5c4f\u5e55\u66f4\u65b0\u5173\u95ed\napp=xw.App(visible=True,add_book=False)\napp.display_alerts=False\napp.screen_updating=False\n\n# \u6587\u4ef6\u4f4d\u7f6e\uff1afilepath\uff0c\u6253\u5f00test\u6587\u6863\uff0c\u7136\u540e\u4fdd\u5b58\uff0c\u5173\u95ed\uff0c\u7ed3\u675f\u7a0b\u5e8f\nfilepath=r&#039;g:\\Python Scripts\\test.xlsx&#039;\nwb=app.books.open(filepath)\nwb.save()\nwb.close()\napp.quit()<\/code><\/pre>\n<p>\uff082\uff09\u65b0\u5efaExcel\u6587\u6863\uff0c\u547d\u540d\u4e3atest.xlsx\uff0c\u5e76\u4fdd\u5b58\u5728D\u76d8<\/p>\n<pre><code class=\"language-python\">import xlwings as xw\n\napp=xw.App(visible=True,add_book=False)\nwb=app.books.add()\nwb.save(r&#039;d:\\test.xlsx&#039;)\nwb.close()\napp.quit()<\/code><\/pre>\n<p>\uff083\uff09\u5728\u5355\u5143\u683c\u8f93\u5165\u503c<\/p>\n<p>\u65b0\u5efatest.xlsx\uff0c\u5728sheet1\u7684\u7b2c\u4e00\u4e2a\u5355\u5143\u683c\u8f93\u5165 \u201c\u4eba\u751f\u201d \uff0c\u7136\u540e\u4fdd\u5b58\u5173\u95ed\uff0c\u9000\u51faExcel\u7a0b\u5e8f\u3002<\/p>\n<pre><code class=\"language-python\"> import xlwings as xw\n\n app=xw.App(visible=True,add_book=False)\n wb=app.books.add()\n\n # wb\u5c31\u662f\u65b0\u5efa\u7684\u5de5\u4f5c\u7c3f(workbook)\uff0c\u4e0b\u9762\u5219\u5bf9wb\u7684sheet1\u7684A1\u5355\u5143\u683c\u8d4b\u503c\n wb.sheets[&#039;sheet1&#039;].range(&#039;A1&#039;).value=&#039;\u4eba\u751f&#039;\n wb.save(r&#039;d:\\test.xlsx&#039;)\n wb.close()\n app.quit()<\/code><\/pre>\n<p>\u6253\u5f00\u5df2\u4fdd\u5b58\u7684test.xlsx\uff0c\u5728sheet2\u7684\u7b2c\u4e8c\u4e2a\u5355\u5143\u683c\u8f93\u5165\u201c\u82e6\u77ed\u201d\uff0c\u7136\u540e\u4fdd\u5b58\u5173\u95ed\uff0c\u9000\u51faExcel\u7a0b\u5e8f<\/p>\n<pre><code class=\"language-python\"> import xlwings as xw\n\n app=xw.App(visible=True,add_book=False)\n wb=app.books.open(r&#039;d:\\test.xlsx&#039;)\n\n # wb\u5c31\u662f\u65b0\u5efa\u7684\u5de5\u4f5c\u7c3f(workbook)\uff0c\u4e0b\u9762\u5219\u5bf9wb\u7684sheet1\u7684A1\u5355\u5143\u683c\u8d4b\u503c\n wb.sheets[&#039;sheet1&#039;].range(&#039;A1&#039;).value=&#039;\u82e6\u77ed&#039;\n wb.save()\n wb.close()\n app.quit()<\/code><\/pre>\n<p>\u638c\u63e1\u4ee5\u4e0a\u4ee3\u7801\uff0c\u5df2\u7ecf\u5b8c\u5168\u53ef\u4ee5\u628aExcel\u5f53\u4f5c\u4e00\u4e2atxt\u6587\u672c\u8fdb\u884c\u6570\u636e\u50a8\u5b58\u4e86\uff0c\u4e5f\u53ef\u4ee5\u8bfb\u53d6Excel\u6587\u4ef6\u7684\u6570\u636e\uff0c\u8fdb\u884c\u8ba1\u7b97\u540e\uff0c\u5e76\u5c06\u7ed3\u679c\u4fdd\u5b58\u5728Excel\u4e2d\u3002<\/p>\n<h3><strong>4.3 \u5f15\u7528\u5de5\u4f5c\u8584\u3001\u5de5\u4f5c\u8868\u548c\u5355\u5143\u683c<\/strong><\/h3>\n<p>\uff081\uff09\u6309\u540d\u5b57\u5f15\u7528\u5de5\u4f5c\u7c3f\uff0c\u6ce8\u610f\u5de5\u4f5c\u7c3f\u5e94\u8be5\u9996\u5148\u88ab\u6253\u5f00<\/p>\n<pre><code class=\"language-python\">wb=xw.books[&#039;\u5de5\u4f5c\u7c3f\u7684\u540d\u5b57\u2018]<\/code><\/pre>\n<p>\uff082\uff09\u5f15\u7528\u6d3b\u52a8\u7684\u5de5\u4f5c\u8584<\/p>\n<pre><code class=\"language-python\">wb=xw.books.active<\/code><\/pre>\n<p>\uff083\uff09\u5f15\u7528\u5de5\u4f5c\u7c3f\u4e2d\u7684sheet<\/p>\n<pre><code class=\"language-python\">sht=xw.books[&#039;\u5de5\u4f5c\u7c3f\u7684\u540d\u5b57\u2018].sheets[&#039;sheet\u7684\u540d\u5b57&#039;]\n# \u6216\u8005\nwb=xw.books[&#039;\u5de5\u4f5c\u7c3f\u7684\u540d\u5b57&#039;]\nsht=wb.sheets[sheet\u7684\u540d\u5b57]<\/code><\/pre>\n<p>\uff084\uff09\u5f15\u7528\u6d3b\u52a8sheet<\/p>\n<pre><code class=\"language-python\">sht=xw.sheets.active<\/code><\/pre>\n<p>\uff085\uff09\u5f15\u7528A1\u5355\u5143\u683c<\/p>\n<pre><code class=\"language-python\">rng=xw.books[&#039;\u5de5\u4f5c\u7c3f\u7684\u540d\u5b57\u2018].sheets[&#039;sheet\u7684\u540d\u5b57&#039;]\n# \u6216\u8005\nsht=xw.books[&#039;\u5de5\u4f5c\u7c3f\u7684\u540d\u5b57\u2018].sheets[&#039;sheet\u7684\u540d\u5b57&#039;]\nrng=sht.range(&#039;A1&#039;)<\/code><\/pre>\n<p>\uff086\uff09\u5f15\u7528\u6d3b\u52a8sheet\u4e0a\u7684\u5355\u5143\u683c<\/p>\n<pre><code class=\"language-python\"># \u6ce8\u610fRange\u9996\u5b57\u6bcd\u5927\u5199\nrng=xw.Range(&#039;A1&#039;)\n\n#\u5176\u4e2d\u9700\u8981\u6ce8\u610f\u7684\u662f\u5355\u5143\u683c\u7684\u5b8c\u5168\u5f15\u7528\u8def\u5f84\u662f\uff1a\n# \u7b2c\u4e00\u4e2aExcel\u7a0b\u5e8f\u7684\u7b2c\u4e00\u4e2a\u5de5\u4f5c\u8584\u7684\u7b2c\u4e00\u5f20sheet\u7684\u7b2c\u4e00\u4e2a\u5355\u5143\u683c\nxw.apps[0].books[0].sheets[0].range(&#039;A1&#039;)\n\u8fc5\u901f\u5f15\u7528\u5355\u5143\u683c\u7684\u65b9\u5f0f\u662f\nsht=xw.books[&#039;\u540d\u5b57&#039;].sheets[&#039;\u540d\u5b57&#039;]\n\n# A1\u5355\u5143\u683c\nrng=sht[\u2019A1&#039;]\n\n# A1:B5\u5355\u5143\u683c\nrng=sht[&#039;A1:B5&#039;]\n\n# \u5728\u7b2ci+1\u884c\uff0c\u7b2cj+1\u5217\u7684\u5355\u5143\u683c\n# B1\u5355\u5143\u683c\nrng=sht[0,1]\n\n# A1:J10\nrng=sht[:10,:10]\n\n#PS\uff1a \u5bf9\u4e8e\u5355\u5143\u683c\u4e5f\u53ef\u4ee5\u7528\u8868\u793a\u884c\u5217\u7684tuple\u8fdb\u884c\u5f15\u7528\n# A1\u5355\u5143\u683c\u7684\u5f15\u7528\nxw.Range(1,1)\n\n#A1:C3\u5355\u5143\u683c\u7684\u5f15\u7528\nxw.Range((1,1),(3,3))<\/code><\/pre>\n<p>\u5f15\u7528\u5355\u5143\u683c\uff1a<\/p>\n<pre><code class=\"language-python\">rng = sht.range(&#039;a1&#039;)\n#rng = sht[&#039;a1&#039;]\n#rng = sht[0,0] \u7b2c\u4e00\u884c\u7684\u7b2c\u4e00\u5217\u5373a1,\u76f8\u5f53\u4e8epandas\u7684\u5207\u7247<\/code><\/pre>\n<p>\u5f15\u7528\u533a\u57df\uff1a<\/p>\n<pre><code class=\"language-python\">rng = sht.range(&#039;a1:a5&#039;)\n#rng = sht[&#039;a1:a5&#039;]\n#rng = sht[:5,0]<\/code><\/pre>\n<h3><strong>4.4 \u5199\u5165&amp;\u8bfb\u53d6\u6570\u636e<\/strong><\/h3>\n<p><strong>1.\u5199\u5165\u6570\u636e<\/strong><\/p>\n<p><strong>\uff081\uff09\u9009\u62e9\u8d77\u59cb\u5355\u5143\u683cA1,\u5199\u5165\u5b57\u7b26\u4e32\u2018Hello\u2019<\/strong><\/p>\n<pre><code class=\"language-python\">sht.range(&#039;a1&#039;).value = &#039;Hello&#039;<\/code><\/pre>\n<p><strong>\uff082\uff09\u5199\u5165\u5217\u8868<\/strong><\/p>\n<pre><code class=\"language-python\"># \u884c\u5b58\u50a8\uff1a\u5c06\u5217\u8868[1,2,3]\u50a8\u5b58\u5728A1\uff1aC1\u4e2d\nsht.range(&#039;A1&#039;).value=[1,2,3]\n# \u5217\u5b58\u50a8\uff1a\u5c06\u5217\u8868[1,2,3]\u50a8\u5b58\u5728A1:A3\u4e2d\nsht.range(&#039;A1&#039;).options(transpose=True).value=[1,2,3]\n# \u5c062x2\u8868\u683c\uff0c\u5373\u4e8c\u7ef4\u6570\u7ec4\uff0c\u50a8\u5b58\u5728A1:B2\u4e2d\uff0c\u5982\u7b2c\u4e00\u884c1\uff0c2\uff0c\u7b2c\u4e8c\u884c3\uff0c4\nsht.range(&#039;A1&#039;).options(expand=&#039;table&#039;)=[[1,2],[3,4]]<\/code><\/pre>\n<ul>\n<li><strong>\u9ed8\u8ba4\u6309\u884c\u63d2\u5165\uff1aA1:D1\u5206\u522b\u5199\u51651,2,3,4<\/strong><\/li>\n<\/ul>\n<pre><code class=\"language-python\">sht.range(&#039;a1&#039;).value = [1,2,3,4]<\/code><\/pre>\n<p>\u7b49\u540c\u4e8e<\/p>\n<pre><code class=\"language-python\">sht.range(&#039;a1:d1&#039;).value = [1,2,3,4]<\/code><\/pre>\n<ul>\n<li><strong>\u6309\u5217\u63d2\u5165\uff1aA2:A5\u5206\u522b\u5199\u51655,6,7,8<\/strong><\/li>\n<\/ul>\n<p>\u4f60\u53ef\u80fd\u4f1a\u60f3\uff1a<\/p>\n<pre><code class=\"language-python\">sht.range(&#039;a2:a5&#039;).value = [5,6,7,8]<\/code><\/pre>\n<p>\u4f46\u662f\u4f60\u4f1a\u53d1\u73b0xlwings\u8fd8\u662f\u4f1a\u6309\u884c\u5904\u7406\u7684\uff0c\u4e0a\u9762\u4e00\u884c\u7b49\u540c\u4e8e\uff1a<\/p>\n<pre><code class=\"language-python\">sht.range(&#039;a2&#039;).value = [5,6,7,8]<\/code><\/pre>\n<p>\u6b63\u786e\u8bed\u6cd5:<\/p>\n<pre><code class=\"language-python\">sht.range(&#039;a2&#039;).options(transpose=True).value = [5,6,7,8]<\/code><\/pre>\n<p>\u65e2\u7136\u9ed8\u8ba4\u7684\u662f\u6309\u884c\u5199\u5165\uff0c\u6211\u4eec\u5c31\u628a\u5b83\u5012\u8fc7\u6765\u561b\uff08transpose\uff09\uff0c\u5355\u8bcd\u8981\u6253\u5bf9\uff0c\u5982\u679c\u4f60\u6253\u9519\u5355\u8bcd\uff0c\u5b83\u4e0d\u4f1a\u62a5\u9519\uff0c\u800c\u4f1a\u6309\u9ed8\u8ba4\u7684\u884c\u6765\u5199\u5165\uff08\u522b\u95ee\u6211\u600e\u4e48\u77e5\u9053\u7684\uff09<\/p>\n<ul>\n<li><strong>\u591a\u884c\u8f93\u5165\u5c31\u8981\u7528\u4e8c\u7ef4\u5217\u8868\u4e86\uff1a<\/strong><\/li>\n<\/ul>\n<pre><code class=\"language-python\">sht.range(&#039;a6&#039;).expand(&#039;table&#039;).value = [[&#039;a&#039;,&#039;b&#039;,&#039;c&#039;],[&#039;d&#039;,&#039;e&#039;,&#039;f&#039;],[&#039;g&#039;,&#039;h&#039;,&#039;i&#039;]]<\/code><\/pre>\n<p><strong>2.\u8bfb\u53d6\u6570\u636e<\/strong><\/p>\n<p><strong>\uff081\uff09\u8bfb\u53d6\u5355\u4e2a\u503c<\/strong><\/p>\n<pre><code class=\"language-python\"># \u5c06A1\u7684\u503c\uff0c\u8bfb\u53d6\u5230a\u53d8\u91cf\u4e2d\na=sht.range(&#039;A1&#039;).value<\/code><\/pre>\n<p><strong>\uff082\uff09\u5c06\u503c\u8bfb\u53d6\u5230\u5217\u8868\u4e2d<\/strong><\/p>\n<pre><code class=\"language-python\">#\u5c06A1\u5230A2\u7684\u503c\uff0c\u8bfb\u53d6\u5230a\u5217\u8868\u4e2d\na=sht.range(&#039;A1:A2&#039;).value\n# \u5c06\u7b2c\u4e00\u884c\u548c\u7b2c\u4e8c\u884c\u7684\u6570\u636e\u6309\u4e8c\u7ef4\u6570\u7ec4\u7684\u65b9\u5f0f\u8bfb\u53d6\na=sht.range(&#039;A1:B2&#039;).value<\/code><\/pre>\n<ul>\n<li><strong>\u9009\u53d6\u4e00\u5217\u7684\u6570\u636e<\/strong><\/li>\n<\/ul>\n<p>\u5148\u8ba1\u7b97\u5355\u5143\u683c\u7684\u884c\u6570(\u524d\u63d0\u662f\u8fde\u7eed\u7684\u5355\u5143\u683c)<\/p>\n<pre><code class=\"language-python\">rng = sht.range(&#039;a1&#039;).expand(&#039;table&#039;)\nnrows = rng.rows.count<\/code><\/pre>\n<p>\u63a5\u7740\u5c31\u53ef\u4ee5\u6309\u51c6\u786e\u8303\u56f4\u8bfb\u53d6\u4e86<\/p>\n<pre><code class=\"language-python\">a = sht.range(f&#039;a1:a{nrows}&#039;).value<\/code><\/pre>\n<ul>\n<li><strong>\u9009\u53d6\u4e00\u884c\u7684\u6570\u636e<\/strong><\/li>\n<\/ul>\n<pre><code class=\"language-python\">ncols = rng.columns.count\n#\u7528\u5207\u7247\nfst_col = sht[0,:ncols].value<\/code><\/pre>\n<h3><strong>4.5 \u5e38\u7528\u51fd\u6570\u548c\u65b9\u6cd5<\/strong><\/h3>\n<p><strong>1.Book\u5de5\u4f5c\u8584\u5e38\u7528\u7684api<\/strong><\/p>\n<pre><code class=\"language-python\">wb=xw.books[\u2018\u5de5\u4f5c\u7c3f\u540d\u79f0&#039;]<\/code><\/pre>\n<ul>\n<li>wb.activate() \u6fc0\u6d3b\u4e3a\u5f53\u524d\u5de5\u4f5c\u7c3f<\/li>\n<li>wb.fullname \u8fd4\u56de\u5de5\u4f5c\u7c3f\u7684\u7edd\u5bf9\u8def\u5f84<\/li>\n<li>wb.name \u8fd4\u56de\u5de5\u4f5c\u7c3f\u7684\u540d\u79f0<\/li>\n<li>wb.save(path=None) \u4fdd\u5b58\u5de5\u4f5c\u7c3f\uff0c\u9ed8\u8ba4\u8def\u5f84\u4e3a\u5de5\u4f5c\u7c3f\u539f\u8def\u5f84\uff0c\u82e5\u672a\u4fdd\u5b58\u5219\u4e3a\u811a\u672c\u6240\u5728\u7684\u8def\u5f84<\/li>\n<li>wb. close() \u5173\u95ed\u5de5\u4f5c\u7c3f<\/li>\n<\/ul>\n<p>\u4ee3\u7801\u793a\u4f8b\uff1a<\/p>\n<pre><code class=\"language-python\"># \u5f15\u7528Excel\u7a0b\u5e8f\u4e2d\uff0c\u5f53\u524d\u7684\u5de5\u4f5c\u7c3f\nwb=xw.books.acitve\n# \u8fd4\u56de\u5de5\u4f5c\u7c3f\u7684\u7edd\u5bf9\u8def\u5f84\nx=wb.fullname\n# \u8fd4\u56de\u5de5\u4f5c\u7c3f\u7684\u540d\u79f0\nx=wb.name\n# \u4fdd\u5b58\u5de5\u4f5c\u7c3f\uff0c\u9ed8\u8ba4\u8def\u5f84\u4e3a\u5de5\u4f5c\u7c3f\u539f\u8def\u5f84\uff0c\u82e5\u672a\u4fdd\u5b58\u5219\u4e3a\u811a\u672c\u6240\u5728\u7684\u8def\u5f84\nx=wb.save(path=None)\n# \u5173\u95ed\u5de5\u4f5c\u7c3f\nx=wb.close()<\/code><\/pre>\n<p><strong>2.sheet\u5e38\u7528\u7684api<\/strong><\/p>\n<pre><code class=\"language-python\"># \u5f15\u7528\u67d0\u6307\u5b9asheet\nsht=xw.books[&#039;\u5de5\u4f5c\u7c3f\u540d\u79f0&#039;].sheets[&#039;sheet\u7684\u540d\u79f0&#039;]\n# \u6fc0\u6d3bsheet\u4e3a\u6d3b\u52a8\u5de5\u4f5c\u8868\nsht.activate()\n# \u6e05\u9664sheet\u7684\u5185\u5bb9\u548c\u683c\u5f0f\nsht.clear()\n# \u6e05\u9664sheet\u7684\u5185\u5bb9\nsht.contents()\n# \u83b7\u53d6sheet\u7684\u540d\u79f0\nsht.name\n# \u5220\u9664sheet\nsht.delete<\/code><\/pre>\n<p><strong>3.range\u5e38\u7528\u7684api<\/strong><\/p>\n<pre><code class=\"language-python\"># \u5f15\u7528\u5f53\u524d\u6d3b\u52a8\u5de5\u4f5c\u8868\u7684\u5355\u5143\u683c\nrng=xw.Range(&#039;A1&#039;)\n# \u52a0\u5165\u8d85\u94fe\u63a5\n# rng.add_hyperlink(r&#039;www.baidu.com&#039;,&#039;\u767e\u5ea6&#039;,\u2018\u63d0\u793a\uff1a\u70b9\u51fb\u5373\u94fe\u63a5\u5230\u767e\u5ea6&#039;)\n# \u53d6\u5f97\u5f53\u524drange\u7684\u5730\u5740\nrng.address\nrng.get_address()\n# \u6e05\u9664range\u7684\u5185\u5bb9\nrng.clear_contents()\n# \u6e05\u9664\u683c\u5f0f\u548c\u5185\u5bb9\nrng.clear()\n# \u53d6\u5f97range\u7684\u80cc\u666f\u8272,\u4ee5\u5143\u7ec4\u5f62\u5f0f\u8fd4\u56deRGB\u503c\nrng.color\n# \u8bbe\u7f6erange\u7684\u989c\u8272\nrng.color=(255,255,255)\n# \u6e05\u9664range\u7684\u80cc\u666f\u8272\nrng.color=None\n# \u83b7\u5f97range\u7684\u7b2c\u4e00\u5217\u5217\u6807\nrng.column\n# \u8fd4\u56derange\u4e2d\u5355\u5143\u683c\u7684\u6570\u636e\nrng.count\n# \u8fd4\u56decurrent_region\nrng.current_region\n# \u8fd4\u56dectrl + \u65b9\u5411\nrng.end(&#039;down&#039;)\n# \u83b7\u53d6\u516c\u5f0f\u6216\u8005\u8f93\u5165\u516c\u5f0f\nrng.formula=&#039;=SUM(B1:B5)&#039;\n# \u6570\u7ec4\u516c\u5f0f\nrng.formula_array\n# \u83b7\u5f97\u5355\u5143\u683c\u7684\u7edd\u5bf9\u5730\u5740\nrng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)\n# \u83b7\u5f97\u5217\u5bbd\nrng.column_width\n# \u8fd4\u56derange\u7684\u603b\u5bbd\u5ea6\nrng.width\n# \u83b7\u5f97range\u7684\u8d85\u94fe\u63a5\nrng.hyperlink\n# \u83b7\u5f97range\u4e2d\u53f3\u4e0b\u89d2\u6700\u540e\u4e00\u4e2a\u5355\u5143\u683c\nrng.last_cell\n# range\u5e73\u79fb\nrng.offset(row_offset=0,column_offset=0)\n#range\u8fdb\u884cresize\u6539\u53d8range\u7684\u5927\u5c0f\nrng.resize(row_size=None,column_size=None)\n# range\u7684\u7b2c\u4e00\u884c\u884c\u6807\nrng.row\n# \u884c\u7684\u9ad8\u5ea6\uff0c\u6240\u6709\u884c\u4e00\u6837\u9ad8\u8fd4\u56de\u884c\u9ad8\uff0c\u4e0d\u4e00\u6837\u8fd4\u56deNone\nrng.row_height\n# \u8fd4\u56derange\u7684\u603b\u9ad8\u5ea6\nrng.height\n# \u8fd4\u56derange\u7684\u884c\u6570\u548c\u5217\u6570\nrng.shape\n# \u8fd4\u56derange\u6240\u5728\u7684sheet\nrng.sheet\n#\u8fd4\u56derange\u7684\u6240\u6709\u884c\nrng.rows\n# range\u7684\u7b2c\u4e00\u884c\nrng.rows[0]\n# range\u7684\u603b\u884c\u6570\nrng.rows.count\n# \u8fd4\u56derange\u7684\u6240\u6709\u5217\nrng.columns\n# \u8fd4\u56derange\u7684\u7b2c\u4e00\u5217\nrng.columns[0]\n# \u8fd4\u56derange\u7684\u5217\u6570\nrng.columns.count\n# \u6240\u6709range\u7684\u5927\u5c0f\u81ea\u9002\u5e94\nrng.autofit()\n# \u6240\u6709\u5217\u5bbd\u5ea6\u81ea\u9002\u5e94\nrng.columns.autofit()\n# \u6240\u6709\u884c\u5bbd\u5ea6\u81ea\u9002\u5e94\nrng.rows.autofit()<\/code><\/pre>\n<p>4.books \u5de5\u4f5c\u7c3f\u96c6\u5408\u7684api<\/p>\n<pre><code class=\"language-python\"># \u65b0\u5efa\u5de5\u4f5c\u7c3f\nxw.books.add()\n# \u5f15\u7528\u5f53\u524d\u6d3b\u52a8\u5de5\u4f5c\u7c3f\nxw.books.active<\/code><\/pre>\n<p>4.sheets \u5de5\u4f5c\u8868\u7684\u96c6\u5408<\/p>\n<pre><code class=\"language-python\"># \u65b0\u5efa\u5de5\u4f5c\u8868\nxw.sheets.add(name=None,before=None,after=None)\n# \u5f15\u7528\u5f53\u524d\u6d3b\u52a8sheet\nxw.sheets.active<\/code><\/pre>\n<h3><strong>4.6 \u6570\u636e\u7ed3\u6784<\/strong><\/h3>\n<p><strong>1.\u4e00\u7ef4\u6570\u636e<\/strong><\/p>\n<p>python\u7684\u5217\u8868\uff0c\u53ef\u4ee5\u548cExcel\u4e2d\u7684\u884c\u5217\u8fdb\u884c\u6570\u636e\u4ea4\u6362\uff0cpython\u4e2d\u7684\u4e00\u7ef4\u5217\u8868\uff0c\u5728Excel\u4e2d\u9ed8\u8ba4\u4e3a\u4e00\u884c\u6570\u636e\u3002<\/p>\n<pre><code class=\"language-python\">import xlwings as xw\n\nsht=xw.sheets.active\n\n# \u5c061\uff0c2\uff0c3\u5206\u522b\u5199\u5165\u4e86A1\uff0cB1\uff0cC1\u5355\u5143\u683c\u4e2d\nsht.range(&#039;A1&#039;).value=[1,2,3]\n\n# \u5c06A1\uff0cB1\uff0cC1\u5355\u5143\u683c\u7684\u503c\u5b58\u5165list1\u5217\u8868\u4e2d\nlist1=sht.range(&#039;A1:C1&#039;).value\n\n# \u5c061\uff0c2\uff0c3\u5206\u522b\u5199\u5165\u4e86A1\uff0cA2\uff0cA3\u5355\u5143\u683c\u4e2d\nsht.range(&#039;A1&#039;).options(transpose=True).value=[1,2,3]\n\n# \u5c06A1\uff0cA2\uff0cA3\u5355\u5143\u683c\u4e2d\u503c\u5b58\u5165list1\u5217\u8868\u4e2d\nlist1=sht.range(&#039;A1:A3&#039;).value<\/code><\/pre>\n<p><strong>2.\u4e8c\u7ef4\u6570\u636e<\/strong><\/p>\n<p>python\u7684\u4e8c\u7ef4\u5217\u8868\uff0c\u53ef\u4ee5\u8f6c\u6362\u4e3aExcel\u4e2d\u7684\u884c\u5217\u3002\u4e8c\u7ef4\u5217\u8868\uff0c\u5373\u5217\u8868\u4e2d\u7684\u5143\u7d20\u8fd8\u662f\u5217\u8868\u3002\u5728Excel\u4e2d\uff0c\u4e8c\u7ef4\u5217\u8868\u4e2d\u7684\u5217\u8868\u5143\u7d20\uff0c\u4ee3\u8868Excel\u8868\u683c\u4e2d\u7684\u4e00\u5217\u3002\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-python\"># \u5c06a1,a2,a3\u8f93\u5165\u7b2c\u4e00\u5217\uff0cb1,b2,b3\u8f93\u5165\u7b2c\u4e8c\u5217\nlist1=[[\u2018a1\u2019,&#039;a2&#039;,&#039;a3&#039;],[&#039;b1&#039;,&#039;b2&#039;,&#039;b3&#039;]]\nsht.range(&#039;A1&#039;).value=list1<\/code><\/pre>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<pre><code class=\"language-python\"># \u5c06A1\uff1aB3\u7684\u503c\u8d4b\u7ed9\u4e8c\u7ef4\u5217\u8868list1\nlist1=sht.range(&#039;A1:B3&#039;).value<\/code><\/pre>\n<p><strong>3.Excel\u4e2d\u533a\u57df\u7684\u9009\u53d6\u8868\u683c<\/strong><\/p>\n<pre><code class=\"language-python\"># \u9009\u53d6\u7b2c\u4e00\u5217\nrng=sht. range(&#039;A1&#039;).expand(&#039;down&#039;)\nrng.value=[&#039;a1&#039;,&#039;a2&#039;,&#039;a3&#039;]<\/code><\/pre>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<pre><code class=\"language-python\"># \u9009\u53d6\u7b2c\u4e00\u884c\nrng=sht.range(&#039;A1&#039;).expand(&#039;right&#039;)\nrng=[&#039;a1&#039;,&#039;b1&#039;]<\/code><\/pre>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<pre><code class=\"language-python\"># \u9009\u53d6\u8868\u683c\nrng.sht.range(&#039;A1&#039;).expand(&#039;table&#039;)\nrng.value=[[\u2018a1\u2019,&#039;a2&#039;,&#039;a3&#039;],[&#039;b1&#039;,&#039;b2&#039;,&#039;b3&#039;]]<\/code><\/pre>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>4.7 xlwings\u751f\u6210\u56fe\u8868<\/strong><\/h3>\n<p>\u751f\u6210\u56fe\u8868\u7684\u65b9\u6cd5\uff0c\u5177\u4f53\u65b9\u6cd5\u4e5f\u53ef\u53c2\u89c1\uff1a<a href=\"http:\/\/mp.weixin.qq.com\/s?__biz=MzUzODYwMDAzNA==&amp;mid=2247517310&amp;idx=3&amp;sn=5009e65912c662ea566077890586e902&amp;chksm=fad7f373cda07a65e9c467a2630ec78b5bc54a71583086f60f3d609877dd042eb55434f0b337&amp;scene=21#wechat_redirect\">Python \u64cd\u4f5c Excel \u5e93 xlwings \u5e38\u7528\u64cd\u4f5c\u8be6\u89e3\uff01<\/a><\/p>\n<pre><code class=\"language-python\">import xlwings as xw\napp = xw.App()\nwb = app.books.active\nsht = wb.sheets.active\n\nchart = sht.charts.add(100, 10)  # 100, 10 \u4e3a\u56fe\u8868\u653e\u7f6e\u7684\u4f4d\u7f6e\u5750\u6807\u3002\u4ee5\u50cf\u7d20\u4e3a\u5355\u4f4d\u3002\nchart.set_source_data(sht.range(&#039;A1&#039;).expand())  # \u53c2\u6570\u4e3a\u8868\u683c\u4e2d\u7684\u6570\u636e\u533a\u57df\u3002\n# chart.chart_type = i               # \u7528\u6765\u8bbe\u7f6e\u56fe\u8868\u7c7b\u578b\uff0c\u5177\u4f53\u53c2\u6570\u4ef6\u4e0b\u9762\u8be6\u7ec6\u8bf4\u660e\u3002\nchart.api[1].ChartTitle.Text = i          # \u7528\u6765\u8bbe\u7f6e\u56fe\u8868\u7684\u6807\u9898\u3002<\/code><\/pre>\n<p>\u793a\u4f8b\u4ee3\u7801\uff1a<\/p>\n<pre><code class=\"language-python\">import xlwings as xw\napp = xw.App()\nwb = app.books.active\nsht = wb.sheets.active\n# \u751f\u6210\u56fe\u8868\u7684\u6570\u636e\nsht.range(&#039;A1&#039;).value = [[&#039;\u65f6\u95f4&#039;, &#039;\u6570\u91cf&#039;], [&#039;1\u65e5&#039;, 2], [&#039;2\u65e5&#039;, 1], [&#039;3\u65e5&#039;, 3]\n             , [&#039;4\u65e5&#039;, 4], [&#039;5\u65e5&#039;, 5], [&#039;6\u65e5&#039;, 6]]\n&quot;&quot;&quot;\u56fe\u8868\u7c7b\u578b\u53c2\u6570\uff0c\u88ab\u6ce8\u91ca\u7684\u90a3\u51e0\u4e2a\uff0c\u65e0\u6cd5\u751f\u6210\u5bf9\u5e94\u7684\u56fe\u8868&quot;&quot;&quot;\ndic = {\n  &#039;3d_area&#039;: -4098,\n  &#039;3d_area_stacked&#039;: 78,\n  &#039;3d_area_stacked_100&#039;: 79,\n  &#039;3d_bar_clustered&#039;: 60,\n  &#039;3d_bar_stacked&#039;: 61,\n  &#039;3d_bar_stacked_100&#039;: 62,\n  &#039;3d_column&#039;: -4100,\n  &#039;3d_column_clustered&#039;: 54,\n  &#039;3d_column_stacked&#039;: 55,\n  &#039;3d_column_stacked_100&#039;: 56,\n  &#039;3d_line&#039;: -4101,\n  &#039;3d_pie&#039;: -4102,\n  &#039;3d_pie_exploded&#039;: 70,\n  &#039;area&#039;: 1,\n  &#039;area_stacked&#039;: 76,\n  &#039;area_stacked_100&#039;: 77,\n  &#039;bar_clustered&#039;: 57,\n  &#039;bar_of_pie&#039;: 71,\n  &#039;bar_stacked&#039;: 58,\n  &#039;bar_stacked_100&#039;: 59,\n  &#039;bubble&#039;: 15,\n  &#039;bubble_3d_effect&#039;: 87,\n  &#039;column_clustered&#039;: 51,\n  &#039;column_stacked&#039;: 52,\n  &#039;column_stacked_100&#039;: 53,\n  &#039;cone_bar_clustered&#039;: 102,\n  &#039;cone_bar_stacked&#039;: 103,\n  &#039;cone_bar_stacked_100&#039;: 104,\n  &#039;cone_col&#039;: 105,\n  &#039;cone_col_clustered&#039;: 99,\n  &#039;cone_col_stacked&#039;: 100,\n  &#039;cone_col_stacked_100&#039;: 101,\n  &#039;cylinder_bar_clustered&#039;: 95,\n  &#039;cylinder_bar_stacked&#039;: 96,\n  &#039;cylinder_bar_stacked_100&#039;: 97,\n  &#039;cylinder_col&#039;: 98,\n  &#039;cylinder_col_clustered&#039;: 92,\n  &#039;cylinder_col_stacked&#039;: 93,\n  &#039;cylinder_col_stacked_100&#039;: 94,\n  &#039;doughnut&#039;: -4120,\n  &#039;doughnut_exploded&#039;: 80,\n  &#039;line&#039;: 4,\n  &#039;line_markers&#039;: 65,\n  &#039;line_markers_stacked&#039;: 66,\n  &#039;line_markers_stacked_100&#039;: 67,\n  &#039;line_stacked&#039;: 63,\n  &#039;line_stacked_100&#039;: 64,\n  &#039;pie&#039;: 5,\n  &#039;pie_exploded&#039;: 69,\n  &#039;pie_of_pie&#039;: 68,\n  &#039;pyramid_bar_clustered&#039;: 109,\n  &#039;pyramid_bar_stacked&#039;: 110,\n  &#039;pyramid_bar_stacked_100&#039;: 111,\n  &#039;pyramid_col&#039;: 112,\n  &#039;pyramid_col_clustered&#039;: 106,\n  &#039;pyramid_col_stacked&#039;: 107,\n  &#039;pyramid_col_stacked_100&#039;: 108,\n  &#039;radar&#039;: -4151,\n  &#039;radar_filled&#039;: 82,\n  &#039;radar_markers&#039;: 81,\n  # &#039;stock_hlc&#039;: 88,\n  # &#039;stock_ohlc&#039;: 89,\n  # &#039;stock_vhlc&#039;: 90,\n  # &#039;stock_vohlc&#039;: 91,\n  # &#039;surface&#039;: 83,\n  # &#039;surface_top_view&#039;: 85,\n  # &#039;surface_top_view_wireframe&#039;: 86,\n  # &#039;surface_wireframe&#039;: 84,\n  &#039;xy_scatter&#039;: -4169,\n  &#039;xy_scatter_lines&#039;: 74,\n  &#039;xy_scatter_lines_no_markers&#039;: 75,\n  &#039;xy_scatter_smooth&#039;: 72,\n  &#039;xy_scatter_smooth_no_markers&#039;: 73\n}\nw = 385\nh = 241\nn = 0\nx = 100\ny = 10\nfor i in dic.keys():\n  xx = x + n % 3*w  # \u7528\u6765\u751f\u6210\u56fe\u8868\u653e\u7f6e\u7684x\u5750\u6807\u3002\n  yy = y + n\/\/3*h   # \u7528\u6765\u751f\u6210\u56fe\u8868\u653e\u7f6e\u7684y\u5750\u6807\u3002\n  chart = sht.charts.add(xx, yy)\n  chart.set_source_data(sht.range(&#039;A1&#039;).expand())\n  chart.chart_type = i\n  chart.api[1].ChartTitle.Text = i\n  n += 1\nwb.save(&#039;chart_\u56fe\u8868&#039;)\nwb.close()\napp.quit()<\/code><\/pre>\n<p>\u6548\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"data:image\/gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVQImWNgYGBgAAAABQABh6FO1AAAAABJRU5ErkJggg==\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>4.8 \u5b9e\u6218\u8bad\u7ec3<\/strong><\/h3>\n<p><strong>1. xlwings \u65b0\u5efa Excel \u6587\u6863<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141407207.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.4.2 xlwings \u65b0\u5efa Excle \u6587\u6863\ndef fun3_4_2():\n    &quot;&quot;&quot;\n    visible\n    Ture\uff1a\u53ef\u89c1excel\n    False\uff1a\u4e0d\u53ef\u89c1excel\n\n    add_book\n    True:\u6253\u5f00excel\u5e76\u4e14\u65b0\u5efa\u5de5\u4f5c\u7c3f\n    False\uff1a\u4e0d\u65b0\u5efa\u5de5\u4f5c\u7c3f\n    &quot;&quot;&quot;\n    app = xw.App(visible=True, add_book=False)\n\n    # \u65b0\u5efa\u5de5\u4f5c\u7c3f (\u5982\u679c\u4e0d\u63a5\u4e0b\u4e00\u6761\u4ee3\u7801\u7684\u8bdd\uff0cExcel\u53ea\u4f1a\u4e00\u95ea\u800c\u8fc7\uff0c\u5356\u4e2a\u840c\u5c31\u8d70\u4e86\uff09\n    wb = app.books.add()\n\n    # \u4fdd\u5b58\u5de5\u4f5c\u7c3f\n    wb.save(&#039;example.xlsx&#039;)\n\n    # \u9000\u51fa\u5de5\u4f5c\u7c3f\n    wb.close()\n\n    # \u9000\u51faExcel\n    app.quit()<\/code><\/pre>\n<p><strong>\u6267\u884c\u7a0b\u5e8f\u540e\u6587\u4ef6\u5939\u589e\u52a0\u4e86\u201cexample.xlsx\u201d:<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408785.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p>\u6b64\u65f6\u8868\u683c\u662f\u7a7a\u7684\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408874.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>2. xlwings \u6253\u5f00\u5df2\u5b58\u5728\u7684 Excel \u6587\u6863<\/strong><\/p>\n<p>\u73b0\u6709\u8868\u683c\u957f\u8fd9\u6837\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408406.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>\u8fd0\u884c\u7a0b\u5e8f\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.4.3 xlwings \u6253\u5f00\u5df2\u5b58\u5728\u7684Excel\u6587\u4ef6\ndef fun3_4_3():\n    # \u65b0\u5efaExcle \u9ed8\u8ba4\u8bbe\u7f6e\uff1a\u7a0b\u5e8f\u53ef\u89c1\uff0c\u53ea\u6253\u5f00\u4e0d\u65b0\u5efa\u5de5\u4f5c\u8584\uff0c\u5c4f\u5e55\u66f4\u65b0\u5173\u95ed\n    app = xw.App(visible=True, add_book=False)\n    app.display_alerts = False\n    app.screen_updating = False\n\n    # \u6253\u5f00\u5df2\u5b58\u5728\u7684Excel\u6587\u4ef6\n    wb=app.books.open(&#039;.\/3_4 xlwings \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&#039;)\n\n    # \u4fdd\u5b58\u5de5\u4f5c\u7c3f\n    wb.save(&#039;example_2.xlsx&#039;)\n\n    # \u9000\u51fa\u5de5\u4f5c\u7c3f\n    wb.close()\n\n    # \u9000\u51faExcel\n    app.quit()<\/code><\/pre>\n<p><strong>\u751f\u6210\u65b0\u7684\u8868\u683c\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408881.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>\u5185\u5bb9\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408171.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>3. xlwings \u8bfb\u5199 Excel<\/strong><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\">#  3.4.4 xlwings\u8bfb\u5199 Excel\ndef fun3_4_4():\n    # \u65b0\u5efaExcle \u9ed8\u8ba4\u8bbe\u7f6e\uff1a\u7a0b\u5e8f\u53ef\u89c1\uff0c\u53ea\u6253\u5f00\u4e0d\u65b0\u5efa\u5de5\u4f5c\u8584\uff0c\u5c4f\u5e55\u66f4\u65b0\u5173\u95ed\n    app = xw.App(visible=True, add_book=False)\n    app.display_alerts = False\n    app.screen_updating = False\n\n    # \u6253\u5f00\u5df2\u5b58\u5728\u7684Excel\u6587\u4ef6\n    wb=app.books.open(&#039;.\/3_4 xlwings \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&#039;)\n\n    # \u83b7\u53d6sheet\u5bf9\u8c61\n    print(wb.sheets)\n    sheet = wb.sheets[0]\n    # sheet = wb.sheets[&quot;sheet1&quot;]\n\n    # \u8bfb\u53d6Excel\u4fe1\u606f\n    cellB1_value = sheet.range(&#039;B1&#039;).value\n    print(&quot;\u5355\u5143\u683cB1\u5185\u5bb9\u4e3a\uff1a&quot;,cellB1_value)\n\n    # \u6e05\u9664\u5355\u5143\u683c\u5185\u5bb9\u548c\u683c\u5f0f\n    sheet.range(&#039;A1&#039;).clear()\n\n    # \u5199\u5165\u5355\u5143\u683c\n    sheet.range(&#039;A1&#039;).value = &quot;xlwings\u5199\u5165&quot;\n\n    # \u4fdd\u5b58\u5de5\u4f5c\u7c3f\n    wb.save(&#039;example_3.xlsx&#039;)\n\n    # \u9000\u51fa\u5de5\u4f5c\u7c3f\n    wb.close()\n\n    # \u9000\u51faExcel\n    app.quit()<\/code><\/pre>\n<p><strong>\u6267\u884c\u6548\u679c\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408880.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141408299.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>\u76f8\u5173\u6587\u7ae0\u9605\u8bfb\uff1a<\/strong><\/p>\n<p><a href=\"https:\/\/mp.weixin.qq.com\/s?__biz=MzUzODYwMDAzNA==&amp;mid=2247507562&amp;idx=3&amp;sn=c3c3147bd451ed62811e663adf291b8d&amp;chksm=fad7a967cda02071e4344c5e373a71faa508525157ee9c3779620b5d728175e451dad18129e1&amp;token=2008711431&amp;lang=zh_CN&amp;scene=21#wechat_redirect\">6000\u5b57\u957f\u6587\uff0c\u5e26\u4f60\u7528Python\u5b8c\u6210Excel\u7684\u5404\u79cd\u9a9a\u64cd\u4f5c\uff01<\/a><\/p>\n<p><a href=\"https:\/\/mp.weixin.qq.com\/s?__biz=MzUzODYwMDAzNA==&amp;mid=2247517310&amp;idx=3&amp;sn=5009e65912c662ea566077890586e902&amp;chksm=fad7f373cda07a65e9c467a2630ec78b5bc54a71583086f60f3d609877dd042eb55434f0b337&amp;token=2008711431&amp;lang=zh_CN&amp;scene=21#wechat_redirect\">Python \u64cd\u4f5c Excel \u5e93 xlwings \u5e38\u7528\u64cd\u4f5c\u8be6\u89e3\uff01<\/a><\/p>\n<p><a href=\"https:\/\/mp.weixin.qq.com\/s?__biz=MzUzODYwMDAzNA==&amp;mid=2247498277&amp;idx=4&amp;sn=44255a0c0d3166c8c497d58889d8ff80&amp;chksm=fad78d28cda0043e024f0823cf44ceead752fc7fcdbd9c65cc04b815de7bf5f2ee79942f7c7d&amp;token=2008711431&amp;lang=zh_CN&amp;scene=21#wechat_redirect\">\u8be6\u89e3 Python \u64cd\u4f5c Excel \u7684\u5404\u79cd\u9a9a\u64cd\u4f5c\uff01<\/a><\/p>\n<h2>5. Python openpyxl \u8bfb\u53d6 \u5199\u5165 \u4fee\u6539 \u64cd\u4f5cExcel<\/h2>\n<p>\u5728openpyxl\u4e2d\uff0c\u4e3b\u8981\u7528\u5230\u4e09\u4e2a\u6982\u5ff5\uff1aWorkbooks\uff0cSheets\uff0cCells\u3002<\/p>\n<ul>\n<li>Workbook\u5c31\u662f\u4e00\u4e2aexcel\u5de5\u4f5c\u8868\uff1b<\/li>\n<li>Sheet\u662f\u5de5\u4f5c\u8868\u4e2d\u7684\u4e00\u5f20\u8868\u9875\uff1b<\/li>\n<li>Cell\u5c31\u662f\u7b80\u5355\u7684\u4e00\u4e2a\u683c\u3002<\/li>\n<\/ul>\n<p>openpyxl\u5c31\u662f\u56f4\u7ed5\u7740\u8fd9\u4e09\u4e2a\u6982\u5ff5\u8fdb\u884c\u7684\uff0c\u4e0d\u7ba1\u8bfb\u5199\u90fd\u662f\u201c\u4e09\u677f\u65a7\u201d\uff1a\u6253\u5f00Workbook\uff0c\u5b9a\u4f4dSheet\uff0c\u64cd\u4f5cCell\u3002<\/p>\n<p>\u5b98\u65b9\u6587\u6863\uff1a<a href=\"https:\/\/openpyxl.readthedocs.io\/en\/stable\/\">https:\/\/openpyxl.readthedocs.io\/en\/stable\/<\/a><\/p>\n<p>\u5b98\u65b9\u793a\u4f8b\uff1a<\/p>\n<pre><code class=\"language-python\">from openpyxl import Workbook\nwb = Workbook()\n\n# grab the active worksheet\nws = wb.active\n\n# Data can be assigned directly to cells\nws[&#039;A1&#039;] = 42\n\n# Rows can also be appended\nws.append([1, 2, 3])\n\n# Python types will automatically be converted\nimport datetime\nws[&#039;A2&#039;] = datetime.datetime.now()\n\n# Save the file\nwb.save(&quot;sample.xlsx&quot;)<\/code><\/pre>\n<h3><strong>5.1 openpyxl \u57fa\u672c\u64cd\u4f5c<\/strong><\/h3>\n<p><strong>1.\u5b89\u88c5<\/strong><\/p>\n<pre><code>pip install openpyxl<\/code><\/pre>\n<p>\u56e0\u4e3a\u6211\u5df2\u7ecf\u5b89\u88c5\uff0c\u6240\u4ee5\u63d0\u793a\u5982\u4e0b\u4fe1\u606f\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141409303.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>2.\u6253\u5f00\u6587\u4ef6<\/strong><\/p>\n<p>\uff081\uff09\u65b0\u5efa<\/p>\n<pre><code class=\"language-python\">from  openpyxl import  Workbook \n# \u5b9e\u4f8b\u5316\nwb = Workbook()\n# \u6fc0\u6d3b worksheet\nws = wb.active<\/code><\/pre>\n<p>\uff082\uff09\u6253\u5f00\u5df2\u6709<\/p>\n<pre><code class=\"language-python\">from openpyxl  import load_workbook\n\nwb = load_workbook(&#039;\u6587\u4ef6\u540d\u79f0.xlsx&#039;)<\/code><\/pre>\n<p><strong>3.\u5199\u5165\u6570\u636e<\/strong><\/p>\n<pre><code class=\"language-python\"># \u65b9\u5f0f\u4e00\uff1a\u6570\u636e\u53ef\u4ee5\u76f4\u63a5\u5206\u914d\u5230\u5355\u5143\u683c\u4e2d(\u53ef\u4ee5\u8f93\u5165\u516c\u5f0f)\nws[&#039;A1&#039;] = 42\n# \u65b9\u5f0f\u4e8c\uff1a\u53ef\u4ee5\u9644\u52a0\u884c\uff0c\u4ece\u7b2c\u4e00\u5217\u5f00\u59cb\u9644\u52a0(\u4ece\u6700\u4e0b\u65b9\u7a7a\u767d\u5904\uff0c\u6700\u5de6\u5f00\u59cb)(\u53ef\u4ee5\u8f93\u5165\u591a\u884c)\nws.append([1, 2, 3])\n# \u65b9\u5f0f\u4e09\uff1aPython \u7c7b\u578b\u4f1a\u88ab\u81ea\u52a8\u8f6c\u6362\nws[&#039;A3&#039;] = datetime.datetime.now().strftime(&quot;%Y-%m-%d&quot;)<\/code><\/pre>\n<p><strong>4.\u521b\u5efa\u8868\uff08sheet\uff09<\/strong><\/p>\n<pre><code class=\"language-python\"># \u65b9\u5f0f\u4e00\uff1a\u63d2\u5165\u5230\u6700\u540e(default)\nws1 = wb.create_sheet(&quot;Mysheet&quot;) \n# \u65b9\u5f0f\u4e8c\uff1a\u63d2\u5165\u5230\u6700\u5f00\u59cb\u7684\u4f4d\u7f6e\nws2 = wb.create_sheet(&quot;Mysheet&quot;, 0)<\/code><\/pre>\n<p><strong>5.\u9009\u62e9\u8868\uff08sheet\uff09<\/strong><\/p>\n<pre><code class=\"language-python\"># sheet \u540d\u79f0\u53ef\u4ee5\u4f5c\u4e3a key \u8fdb\u884c\u7d22\u5f15\n>&gt;&gt; ws3 = wb[&quot;New Title&quot;]\n>&gt;&gt; ws4 = wb.get_sheet_by_name(&quot;New Title&quot;)\n>&gt;&gt; ws is ws3 is ws4\nTrue<\/code><\/pre>\n<p><strong>6.\u67e5\u770b\u8868\u540d\uff08sheet\uff09<\/strong><\/p>\n<pre><code class=\"language-python\"># \u663e\u793a\u6240\u6709\u8868\u540d\n>&gt;&gt; print(wb.sheetnames)\n[&#039;Sheet2&#039;, &#039;New Title&#039;,  &#039;Sheet1&#039;]\n# \u904d\u5386\u6240\u6709\u8868\n>&gt;&gt; for sheet in  wb:\n...     print(sheet.title)<\/code><\/pre>\n<p><strong>7.\u8bbf\u95ee\u5355\u5143\u683c\uff08cell\uff09<\/strong><\/p>\n<p>\uff081\uff09\u5355\u4e2a\u5355\u5143\u683c\u8bbf\u95ee<\/p>\n<pre><code class=\"language-python\"># \u65b9\u6cd5\u4e00\n>&gt;&gt; c = ws[&#039;A4&#039;]\n# \u65b9\u6cd5\u4e8c\uff1arow \u884c\uff1bcolumn \u5217\n>&gt;&gt; d = ws.cell(row=4, column=2, value=10)\n# \u65b9\u6cd5\u4e09\uff1a\u53ea\u8981\u8bbf\u95ee\u5c31\u521b\u5efa\n>&gt;&gt; for i in  range(1,101):\n...         for j in range(1,101):\n...            ws.cell(row=i, column=j)<\/code><\/pre>\n<p>\uff082\uff09\u591a\u4e2a\u5355\u5143\u683c\u8bbf\u95ee<\/p>\n<pre><code class=\"language-python\"># \u901a\u8fc7\u5207\u7247\n>&gt;&gt; cell_range = ws[&#039;A1&#039;:&#039;C2&#039;]\n# \u901a\u8fc7\u884c(\u5217)\n>&gt;&gt; colC = ws[&#039;C&#039;]\n>&gt;&gt; col_range = ws[&#039;C:D&#039;]\n>&gt;&gt; row10 = ws[10]\n>&gt;&gt; row_range = ws[5:10]\n# \u901a\u8fc7\u6307\u5b9a\u8303\u56f4(\u884c \u2192 \u884c)\n>&gt;&gt; for row in  ws.iter_rows(min_row=1, max_col=3, max_row=2):\n...    for cell in  row:\n...        print(cell)\n&lt;Cell Sheet1.A1&gt;\n&lt;Cell Sheet1.B1&gt;\n&lt;Cell Sheet1.C1&gt;\n&lt;Cell Sheet1.A2&gt;\n&lt;Cell Sheet1.B2&gt;\n&lt;Cell Sheet1.C2&gt; \n# \u901a\u8fc7\u6307\u5b9a\u8303\u56f4(\u5217 \u2192 \u5217)\n>&gt;&gt; for row in  ws.iter_rows(min_row=1, max_col=3, max_row=2):\n...    for cell in  row:\n...        print(cell)\n&lt;Cell Sheet1.A1&gt;\n&lt;Cell Sheet1.B1&gt;\n&lt;Cell Sheet1.C1&gt;\n&lt;Cell Sheet1.A2&gt;\n&lt;Cell Sheet1.B2&gt;\n&lt;Cell Sheet1.C2&gt;\n# \u904d\u5386\u6240\u6709 \u65b9\u6cd5\u4e00\n>&gt;&gt; ws = wb.active\n>&gt;&gt; ws[&#039;C9&#039;] = &#039;hello world&#039;\n>&gt;&gt; tuple(ws.rows)\n((&lt;Cell Sheet.A1&gt;, &lt;Cell Sheet.B1&gt;, &lt;Cell Sheet.C1&gt;),\n(&lt;Cell Sheet.A2&gt;, &lt;Cell Sheet.B2&gt;, &lt;Cell Sheet.C2&gt;),\n...\n(&lt;Cell Sheet.A8&gt;, &lt;Cell Sheet.B8&gt;, &lt;Cell Sheet.C8&gt;),\n(&lt;Cell Sheet.A9&gt;, &lt;Cell Sheet.B9&gt;, &lt;Cell Sheet.C9&gt;))\n# \u904d\u5386\u6240\u6709 \u65b9\u6cd5\u4e8c\n>&gt;&gt; tuple(ws.columns)\n((&lt;Cell Sheet.A1&gt;,\n&lt;Cell Sheet.A2&gt;,\n&lt;Cell Sheet.A3&gt;,\n...\n&lt;Cell Sheet.B7&gt;,\n&lt;Cell Sheet.B8&gt;,\n&lt;Cell Sheet.B9&gt;),\n(&lt;Cell Sheet.C1&gt;,\n...\n&lt;Cell Sheet.C8&gt;,\n&lt;Cell Sheet.C9&gt;))<\/code><\/pre>\n<p><strong>8.\u4fdd\u5b58\u6570\u636e<\/strong><\/p>\n<pre><code class=\"language-python\">wb.save(&#039;\u6587\u4ef6\u540d\u79f0.xlsx&#039;)<\/code><\/pre>\n<p><strong>9.\u5176\u5b83<\/strong><\/p>\n<p>\uff081\uff09\u6539\u53d8sheet\u6807\u7b7e\u6309\u94ae\u989c\u8272<\/p>\n<pre><code class=\"language-python\">ws.sheet_properties.tabColor = &quot;1072BA&quot; # \u8272\u503c\u4e3aRGB16\u8fdb\u5236\u503c<\/code><\/pre>\n<p>\uff082\uff09\u83b7\u53d6\u6700\u5927\u884c\uff0c\u6700\u5927\u5217<\/p>\n<pre><code class=\"language-python\"># \u83b7\u5f97\u6700\u5927\u5217\u548c\u6700\u5927\u884c\nprint(sheet.max_row)\nprint(sheet.max_column)<\/code><\/pre>\n<p>\uff083\uff09\u83b7\u53d6\u6bcf\u4e00\u884c\u6bcf\u4e00\u5217<\/p>\n<ul>\n<li><code>sheet.rows<\/code>\u4e3a\u751f\u6210\u5668, \u91cc\u9762\u662f\u6bcf\u4e00\u884c\u7684\u6570\u636e\uff0c\u6bcf\u4e00\u884c\u53c8\u7531\u4e00\u4e2atuple\u5305\u88f9\u3002<\/li>\n<li><code>sheet.columns<\/code>\u7c7b\u4f3c\uff0c\u4e0d\u8fc7\u91cc\u9762\u662f\u6bcf\u4e2atuple\u662f\u6bcf\u4e00\u5217\u7684\u5355\u5143\u683c\u3002<\/li>\n<\/ul>\n<pre><code class=\"language-python\"># \u56e0\u4e3a\u6309\u884c\uff0c\u6240\u4ee5\u8fd4\u56deA1, B1, C1\u8fd9\u6837\u7684\u987a\u5e8f\nfor row in sheet.rows:\n    for cell in row:\n        print(cell.value)\n\n# A1, A2, A3\u8fd9\u6837\u7684\u987a\u5e8f\nfor column in sheet.columns:\n    for cell in column:\n        print(cell.value)<\/code><\/pre>\n<p>\uff084\uff09\u6839\u636e\u6570\u5b57\u5f97\u5230\u5b57\u6bcd\uff0c\u6839\u636e\u5b57\u6bcd\u5f97\u5230\u6570\u5b57<\/p>\n<pre><code class=\"language-python\">from openpyxl.utils import get_column_letter, column_index_from_string\n\n# \u6839\u636e\u5217\u7684\u6570\u5b57\u8fd4\u56de\u5b57\u6bcd\nprint(get_column_letter(2))  # B\n# \u6839\u636e\u5b57\u6bcd\u8fd4\u56de\u5217\u7684\u6570\u5b57\nprint(column_index_from_string(&#039;D&#039;))  # 4<\/code><\/pre>\n<p>\uff085\uff09\u5220\u9664\u5de5\u4f5c\u8868<\/p>\n<pre><code class=\"language-python\"># \u65b9\u5f0f\u4e00\nwb.remove(sheet)\n# \u65b9\u5f0f\u4e8c\ndel wb[sheet]<\/code><\/pre>\n<p>\uff086\uff09\u77e9\u9635\u7f6e\u6362<\/p>\n<pre><code class=\"language-python\">rows = [\n    [&#039;Number&#039;, &#039;data1&#039;, &#039;data2&#039;],\n    [2, 40, 30],\n    [3, 40, 25],\n    [4, 50, 30],\n    [5, 30, 10],\n    [6, 25, 5],\n    [7, 50, 10]]\n\nlist(zip(*rows))\n\n# out\n[(&#039;Number&#039;, 2, 3, 4, 5, 6, 7),\n (&#039;data1&#039;, 40, 40, 50, 30, 25, 50),\n (&#039;data2&#039;, 30, 25, 30, 10, 5, 10)]\n\n# \u6ce8\u610f \u65b9\u6cd5\u4f1a\u820d\u5f03\u7f3a\u5c11\u6570\u636e\u7684\u5217(\u884c)\nrows = [\n    [&#039;Number&#039;, &#039;data1&#039;, &#039;data2&#039;],\n    [2, 40      ],    # \u8fd9\u91cc\u5c11\u4e00\u4e2a\u6570\u636e\n    [3, 40, 25],\n    [4, 50, 30],\n    [5, 30, 10],\n    [6, 25, 5],\n    [7, 50, 10],\n]\n# out\n[(&#039;Number&#039;, 2, 3, 4, 5, 6, 7), (&#039;data1&#039;, 40, 40, 50, 30, 25, 50)]<\/code><\/pre>\n<p><strong>10.\u8bbe\u7f6e\u5355\u5143\u683c\u98ce\u683c<\/strong><\/p>\n<p>\uff081\uff09\u9700\u8981\u5bfc\u5165\u7684\u7c7b<\/p>\n<pre><code class=\"language-python\">from openpyxl.styles import Font, colors, Alignment<\/code><\/pre>\n<p>\uff082\uff09\u5b57\u4f53<\/p>\n<ul>\n<li>\u4e0b\u9762\u7684\u4ee3\u7801\u6307\u5b9a\u4e86<code>\u7b49\u7ebf24\u53f7<\/code>\uff0c<code>\u52a0\u7c97\u659c\u4f53<\/code>\uff0c\u5b57\u4f53\u989c\u8272<code>\u7ea2\u8272<\/code>\u3002\u76f4\u63a5\u4f7f\u7528cell\u7684<code>font<\/code>\u5c5e\u6027\uff0c\u5c06Font\u5bf9\u8c61\u8d4b\u503c\u7ed9\u5b83\u3002<\/li>\n<\/ul>\n<pre><code class=\"language-python\">bold_itatic_24_font = Font(name=&#039;\u7b49\u7ebf&#039;, size=24, italic=True, color=colors.RED, bold=True)\n\nsheet[&#039;A1&#039;].font = bold_itatic_24_font<\/code><\/pre>\n<p>\uff083\uff09\u5bf9\u9f50\u65b9\u5f0f<\/p>\n<ul>\n<li>\u4e5f\u662f\u76f4\u63a5\u4f7f\u7528cell\u7684\u5c5e\u6027<code>aligment<\/code>\uff0c\u8fd9\u91cc\u6307\u5b9a\u5782\u76f4\u5c45\u4e2d\u548c\u6c34\u5e73\u5c45\u4e2d\u3002\u9664\u4e86center\uff0c\u8fd8\u53ef\u4ee5\u4f7f\u7528<code>right\u3001left<\/code>\u7b49\u7b49\u53c2\u6570<\/li>\n<\/ul>\n<pre><code class=\"language-python\"># \u8bbe\u7f6eB1\u4e2d\u7684\u6570\u636e\u5782\u76f4\u5c45\u4e2d\u548c\u6c34\u5e73\u5c45\u4e2d\nsheet[&#039;B1&#039;].alignment = Alignment(horizontal=&#039;center&#039;, vertical=&#039;center&#039;)<\/code><\/pre>\n<p>\uff084\uff09\u8bbe\u7f6e\u884c\u9ad8\u548c\u5217\u5bbd<\/p>\n<pre><code class=\"language-python\"># \u7b2c2\u884c\u884c\u9ad8\nsheet.row_dimensions[2].height = 40\n# C\u5217\u5217\u5bbd\nsheet.column_dimensions[&#039;C&#039;].width = 30<\/code><\/pre>\n<p>\uff085\uff09\u5408\u5e76\u548c\u62c6\u5206\u5355\u5143\u683c<\/p>\n<ul>\n<li>\u6240\u8c13\u5408\u5e76\u5355\u5143\u683c\uff0c\u5373\u4ee5\u5408\u5e76\u533a\u57df\u7684\u5de6\u4e0a\u89d2\u7684\u90a3\u4e2a\u5355\u5143\u683c\u4e3a\u57fa\u51c6\uff0c\u8986\u76d6\u5176\u4ed6\u5355\u5143\u683c\u4f7f\u4e4b\u79f0\u4e3a\u4e00\u4e2a\u5927\u7684\u5355\u5143\u683c\u3002<\/li>\n<li>\u76f8\u53cd\uff0c\u62c6\u5206\u5355\u5143\u683c\u540e\u5c06\u8fd9\u4e2a\u5927\u5355\u5143\u683c\u7684\u503c\u8fd4\u56de\u5230\u539f\u6765\u7684\u5de6\u4e0a\u89d2\u4f4d\u7f6e\u3002<\/li>\n<\/ul>\n<pre><code class=\"language-python\"># \u5408\u5e76\u5355\u5143\u683c\uff0c \u5f80\u5de6\u4e0a\u89d2\u5199\u5165\u6570\u636e\u5373\u53ef\nsheet.merge_cells(&#039;B1:G1&#039;) # \u5408\u5e76\u4e00\u884c\u4e2d\u7684\u51e0\u4e2a\u5355\u5143\u683c\nsheet.merge_cells(&#039;A1:C3&#039;) # \u5408\u5e76\u4e00\u4e2a\u77e9\u5f62\u533a\u57df\u4e2d\u7684\u5355\u5143\u683c<\/code><\/pre>\n<ul>\n<li>\u5408\u5e76\u540e\u53ea\u53ef\u4ee5\u5f80\u5de6\u4e0a\u89d2\u5199\u5165\u6570\u636e\uff0c\u4e5f\u5c31\u662f\u533a\u95f4\u4e2d:\u5de6\u8fb9\u7684\u5750\u6807\u3002<\/li>\n<li>\u5982\u679c\u8fd9\u4e9b\u8981\u5408\u5e76\u7684\u5355\u5143\u683c\u90fd\u6709\u6570\u636e\uff0c\u53ea\u4f1a\u4fdd\u7559\u5de6\u4e0a\u89d2\u7684\u6570\u636e\uff0c\u5176\u4ed6\u5219\u4e22\u5f03\u3002\u6362\u53e5\u8bdd\u8bf4\u82e5\u5408\u5e76\u524d\u4e0d\u662f\u5728\u5de6\u4e0a\u89d2\u5199\u5165\u6570\u636e\uff0c\u5408\u5e76\u540e\u5355\u5143\u683c\u4e2d\u4e0d\u4f1a\u6709\u6570\u636e\u3002<\/li>\n<li>\u4ee5\u4e0b\u662f\u62c6\u5206\u5355\u5143\u683c\u7684\u4ee3\u7801\u3002\u62c6\u5206\u540e\uff0c\u503c\u56de\u5230A1\u4f4d\u7f6e<\/li>\n<\/ul>\n<pre><code class=\"language-python\">sheet.unmerge_cells(&#039;A1:C3&#039;)<\/code><\/pre>\n<p><strong>11.\u793a\u4f8b\u4ee3\u7801<\/strong><\/p>\n<pre><code class=\"language-python\">import datetime\nfrom random import choice\nfrom time import time\nfrom openpyxl import load_workbook\nfrom openpyxl.utils import get_column_letter\n\n# \u8bbe\u7f6e\u6587\u4ef6 mingc\naddr = &quot;openpyxl.xlsx&quot;\n# \u6253\u5f00\u6587\u4ef6\nwb = load_workbook(addr)\n# \u521b\u5efa\u4e00\u5f20\u65b0\u8868\nws = wb.create_sheet()\n# \u7b2c\u4e00\u884c\u8f93\u5165\nws.append([&#039;TIME&#039;, &#039;TITLE&#039;, &#039;A-Z&#039;])\n\n# \u8f93\u5165\u5185\u5bb9\uff08500\u884c\u6570\u636e\uff09\nfor i in range(500):\n    TIME = datetime.datetime.now().strftime(&quot;%H:%M:%S&quot;)\n    TITLE = str(time())\n    A_Z = get_column_letter(choice(range(1, 50)))\n    ws.append([TIME, TITLE, A_Z])\n\n# \u83b7\u53d6\u6700\u5927\u884c\nrow_max = ws.max_row\n# \u83b7\u53d6\u6700\u5927\u5217\ncon_max = ws.max_column\n# \u628a\u4e0a\u9762\u5199\u5165\u5185\u5bb9\u6253\u5370\u5728\u63a7\u5236\u53f0\nfor j in ws.rows:    # we.rows \u83b7\u53d6\u6bcf\u4e00\u884c\u6570\u636e\n    for n in j:\n        print(n.value, end=&quot;\\t&quot;)   # n.value \u83b7\u53d6\u5355\u5143\u683c\u7684\u503c\n    print()\n# \u4fdd\u5b58\uff0csave\uff08\u5fc5\u987b\u8981\u5199\u6587\u4ef6\u540d\uff08\u7edd\u5bf9\u5730\u5740\uff09\u9ed8\u8ba4 py \u540c\u7ea7\u76ee\u5f55\u4e0b\uff0c\u53ea\u652f\u6301 xlsx \u683c\u5f0f\uff09\nwb.save(addr)<\/code><\/pre>\n<h3><strong>5.2 openpyxl\u751f\u62102D\u56fe\u8868<\/strong><\/h3>\n<p>\u793a\u4f8b\u4ee3\u7801\uff1a<\/p>\n<pre><code class=\"language-python\">from openpyxl import Workbook\nfrom openpyxl.chart import BarChart, Series, Reference\n\nwb = Workbook(write_only=True)\nws = wb.create_sheet()\n\nrows = [\n    (&#039;Number&#039;, &#039;Batch 1&#039;, &#039;Batch 2&#039;),\n    (2, 10, 30),\n    (3, 40, 60),\n    (4, 50, 70),\n    (5, 20, 10),\n    (6, 10, 40),\n    (7, 50, 30),\n]\n\nfor row in rows:\n    ws.append(row)\n\nchart1 = BarChart()\nchart1.type = &quot;col&quot;\nchart1.style = 10\nchart1.title = &quot;Bar Chart&quot;\nchart1.y_axis.title = &#039;Test number&#039;\nchart1.x_axis.title = &#039;Sample length (mm)&#039;\n\ndata = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)\ncats = Reference(ws, min_col=1, min_row=2, max_row=7)\nchart1.add_data(data, titles_from_data=True)\nchart1.set_categories(cats)\nchart1.shape = 4\nws.add_chart(chart1, &quot;A10&quot;)\n\nfrom copy import deepcopy\n\nchart2 = deepcopy(chart1)\nchart2.style = 11\nchart2.type = &quot;bar&quot;\nchart2.title = &quot;Horizontal Bar Chart&quot;\nws.add_chart(chart2, &quot;G10&quot;)\n\nchart3 = deepcopy(chart1)\nchart3.type = &quot;col&quot;\nchart3.style = 12\nchart3.grouping = &quot;stacked&quot;\nchart3.overlap = 100\nchart3.title = &#039;Stacked Chart&#039;\nws.add_chart(chart3, &quot;A27&quot;)\n\nchart4 = deepcopy(chart1)\nchart4.type = &quot;bar&quot;\nchart4.style = 13\nchart4.grouping = &quot;percentStacked&quot;\nchart4.overlap = 100\nchart4.title = &#039;Percent Stacked Chart&#039;\nws.add_chart(chart4, &quot;G27&quot;)\n\nwb.save(&quot;bar.xlsx&quot;)<\/code><\/pre>\n<p>\u6548\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141410476.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>5.3 openpyxl\u751f\u62103D\u56fe\u8868<\/strong><\/h3>\n<p>\u793a\u4f8b\u4ee3\u7801\uff1a<\/p>\n<pre><code class=\"language-python\">from openpyxl import Workbook\nfrom openpyxl.chart import (\n    Reference,\n    Series,\n    BarChart3D,\n)\n\nwb = Workbook()\nws = wb.active\n\nrows = [\n    (None, 2013, 2014),\n    (&quot;Apples&quot;, 5, 4),\n    (&quot;Oranges&quot;, 6, 2),\n    (&quot;Pears&quot;, 8, 3)\n]\n\nfor row in rows:\n    ws.append(row)\n\ndata = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)\ntitles = Reference(ws, min_col=1, min_row=2, max_row=4)\nchart = BarChart3D()\nchart.title = &quot;3D Bar Chart&quot;\nchart.add_data(data=data, titles_from_data=True)\nchart.set_categories(titles)\n\nws.add_chart(chart, &quot;E5&quot;)\nwb.save(&quot;bar3d.xlsx&quot;)<\/code><\/pre>\n<p>\u6548\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141411165.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>5.4 \u5b9e\u6218\u8bad\u7ec3<\/strong><\/h3>\n<p><strong>1.openpyxl \u65b0\u5efaExcel<\/strong><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.5.2 openpyxl \u65b0\u5efaExcel\ndef fun3_5_2():\n    wb = Workbook()\n\n    # \u6ce8\u610f\uff1a\u8be5\u51fd\u6570\u8c03\u7528\u5de5\u4f5c\u8868\u7684\u7d22\u5f15(_active_sheet_index)\uff0c\u9ed8\u8ba4\u662f0\u3002\n    # \u9664\u975e\u4f60\u4fee\u6539\u4e86\u8fd9\u4e2a\u503c\uff0c\u5426\u5219\u4f60\u4f7f\u7528\u8be5\u51fd\u6570\u4e00\u76f4\u662f\u5728\u5bf9\u7b2c\u4e00\u5f20\u5de5\u4f5c\u8868\u8fdb\u884c\u64cd\u4f5c\u3002\n    ws = wb.active\n\n    # \u8bbe\u7f6esheet\u540d\u79f0\n    ws.title = &quot;New Title&quot;\n\n    # \u8bbe\u7f6esheet\u989c\u8272\n    ws.sheet_properties.tabColor = &quot;1072BA&quot;\n\n    # \u4fdd\u5b58\u8868\u683c\n    wb.save(&#039;\u4fdd\u5b58\u4e00\u4e2a\u65b0\u7684excel.xlsx&#039;)<\/code><\/pre>\n<p><strong>\u6267\u884c\u6548\u679c\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141411399.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>\u5e76\u5bf9sheet\u8bbe\u7f6e\u4e86\u6807\u9898\u548c\u80cc\u666f\u989c\u8272\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141411459.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>2.openpyxl \u6253\u5f00\u5df2\u5b58\u5728Excel<\/strong><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.5.3 openpyxl \u6253\u5f00\u5df2\u5b58\u5728Excel\ndef fun3_5_3():\n    wb = load_workbook(&quot;.\/3_5 openpyxl \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&quot;)\n\n    # \u6ce8\u610f\uff1a\u8be5\u51fd\u6570\u8c03\u7528\u5de5\u4f5c\u8868\u7684\u7d22\u5f15(_active_sheet_index)\uff0c\u9ed8\u8ba4\u662f0\u3002\n    # \u9664\u975e\u4f60\u4fee\u6539\u4e86\u8fd9\u4e2a\u503c\uff0c\u5426\u5219\u4f60\u4f7f\u7528\u8be5\u51fd\u6570\u4e00\u76f4\u662f\u5728\u5bf9\u7b2c\u4e00\u5f20\u5de5\u4f5c\u8868\u8fdb\u884c\u64cd\u4f5c\u3002\n    ws = wb.active\n\n    # \u4fdd\u5b58\u8868\u683c\n    wb.save(&#039;copy.xlsx&#039;)<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141411692.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>3.openpyxl \u8bfb\u5199Excel<\/strong><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\"># 3.5.4 openpyxl \u8bfb\u5199Excel\ndef fun3_5_4():\n    wb = load_workbook(&quot;.\/3_5 openpyxl \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&quot;)\n\n    # \u6ce8\u610f\uff1a\u8be5\u51fd\u6570\u8c03\u7528\u5de5\u4f5c\u8868\u7684\u7d22\u5f15(_active_sheet_index)\uff0c\u9ed8\u8ba4\u662f0\u3002\n    # \u9664\u975e\u4f60\u4fee\u6539\u4e86\u8fd9\u4e2a\u503c\uff0c\u5426\u5219\u4f60\u4f7f\u7528\u8be5\u51fd\u6570\u4e00\u76f4\u662f\u5728\u5bf9\u7b2c\u4e00\u5f20\u5de5\u4f5c\u8868\u8fdb\u884c\u64cd\u4f5c\u3002\n    ws = wb.active\n\n    # \u8bfb\u53d6\u5355\u5143\u683c\u4fe1\u606f\n    cellB2_value = ws[&#039;B2&#039;].value\n    print(&quot;\u5355\u5143\u683cB2\u5185\u5bb9\u4e3a\uff1a&quot;,cellB2_value)\n\n    # \u5199\u5165\u5355\u5143\u683c\n    ws[&#039;A1&#039;].value = &quot;OPENPYXL&quot;\n\n    # \u4fdd\u5b58\u8868\u683c\n    wb.save(&#039;copy.xlsx&#039;)<\/code><\/pre>\n<p><strong>\u6267\u884c\u7ed3\u679c\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141412371.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141412830.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h2>6. Python xlswriter \u5199\u5165 \u64cd\u4f5cExcel<\/h2>\n<ul>\n<li>XlsxWriter\u662f\u4e00\u4e2a\u7528\u6765\u5199Excel2007\u548cxlsx\u6587\u4ef6\u683c\u5f0f\u7684python\u6a21\u5757\u3002\u5b83\u53ef\u4ee5\u7528\u6765\u5199\u6587\u672c\u3001\u6570\u5b57\u3001\u516c\u5f0f\u5e76\u652f\u6301\u5355\u5143\u683c\u683c\u5f0f\u5316\u3001\u56fe\u7247\u3001\u56fe\u8868\u3001\u6587\u6863\u914d\u7f6e\u3001\u81ea\u52a8\u8fc7\u6ee4\u7b49\u7279\u6027<\/li>\n<li>\u4f18\u70b9\uff1a\u529f\u80fd\u66f4\u591a\u3001\u6587\u6863\u9ad8\u4fdd\u771f\u3001\u6269\u5c55\u683c\u5f0f\u7c7b\u578b\u3001\u66f4\u5feb\u5e76\u53ef\u914d\u7f6e \u7f3a\u70b9\uff1a\u4e0d\u80fd\u7528\u6765\u8bfb\u53d6\u548c\u4fee\u6539excel\u6587\u4ef6<\/li>\n<\/ul>\n<p>\u5b98\u65b9\u6587\u6863\uff1a<a href=\"https:\/\/xlsxwriter.readthedocs.io\/\">https:\/\/xlsxwriter.readthedocs.io\/<\/a><\/p>\n<h3><strong>6.1 xlswriter\u57fa\u672c\u64cd\u4f5c<\/strong><\/h3>\n<p><strong>1.\u5b89\u88c5 xlswriter \u6a21\u5757<\/strong><\/p>\n<pre><code class=\"language-python\">pip install XlsxWriter<\/code><\/pre>\n<p>\u7531\u4e8e\u6211\u5df2\u7ecf\u5b89\u88c5\u8fc7\u4e86\uff0c\u6240\u4ee5\u63d0\u793a\u5df2\u7ecf\u5b89\u88c5\uff1a<\/p>\n<p><img src=\"https:\/\/mmbiz.qpic.cn\/mmbiz_png\/PvP6qjUpvIro0I67PdvBOWIE9fbaibCXbsUsVJq9JhzX8knm0ovKTgE7GsXsvf5EgMFibQ99M1E5rCibqhU39gceQ\/640?wx_fmt=png&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>2.\u521b\u5efaexcel\u6587\u4ef6<\/strong><\/p>\n<pre><code class=\"language-python\"># \u521b\u5efa\u6587\u4ef6\nworkbook = xlsxwriter.Workbook(&quot;new_excel.xlsx&quot;) <\/code><\/pre>\n<p><strong>3.\u521b\u5efasheet<\/strong><\/p>\n<pre><code class=\"language-python\"># \u521b\u5efasheet\nworksheet = workbook.add_worksheet(&quot;first_sheet&quot;) <\/code><\/pre>\n<p><strong>4.\u5199\u5165\u6570\u636e<\/strong><\/p>\n<p>\uff081\uff09\u5199\u5165\u6587\u672c<\/p>\n<pre><code class=\"language-python\"># \u6cd5\u4e00\uff1a\nworksheet.write(&#039;A1&#039;, &#039;write something&#039;)\n# \u6cd5\u4e8c\uff1a\nworksheet.write(1, 0, &#039;hello world&#039;)<\/code><\/pre>\n<p>\uff082\uff09\u5199\u5165\u6570\u5b57<\/p>\n<pre><code class=\"language-python\"># \u5199\u5165\u6570\u5b57\nworksheet.write(0, 1, 32)\nworksheet.write(1, 1, 32.3)<\/code><\/pre>\n<p>\uff083\uff09\u5199\u5165\u51fd\u6570<\/p>\n<pre><code class=\"language-python\">worksheet.write(2, 1, &#039;=sum(B1:B2)&#039;)<\/code><\/pre>\n<p>\uff084\uff09\u5199\u5165\u56fe\u7247<\/p>\n<pre><code class=\"language-python\"># \u63d2\u5165\u56fe\u7247\nworksheet.insert_image(0, 5, &#039;test.png&#039;)\nworksheet.insert_image(0, 5, &#039;test.png&#039;, {&#039;url&#039;: &#039;http:\/\/httpbin.org\/&#039;})<\/code><\/pre>\n<p>\uff085\uff09\u5199\u5165\u65e5\u671f<\/p>\n<pre><code class=\"language-python\"># \u5199\u5165\u65e5\u671f\nd = workbook.add_format({&#039;num_format&#039;: &#039;yyyy-mm-dd&#039;})\nworksheet.write(0, 2, datetime.datetime.strptime(&#039;2017-09-13&#039;, &#039;%Y-%m-%d&#039;), d)<\/code><\/pre>\n<p>\uff086\uff09\u8bbe\u7f6e\u884c\u3001\u5217\u5c5e\u6027<\/p>\n<pre><code class=\"language-python\"># \u8bbe\u7f6e\u884c\u5c5e\u6027\uff0c\u884c\u9ad8\u8bbe\u7f6e\u4e3a40\nworksheet.set_row(0, 40)\n\n# \u8bbe\u7f6e\u5217\u5c5e\u6027\uff0c\u628aA\u5230B\u5217\u5bbd\u8bbe\u7f6e\u4e3a20\nworksheet.set_column(&#039;A:B&#039;, 20)<\/code><\/pre>\n<p><strong>5.\u81ea\u5b9a\u4e49\u683c\u5f0f<\/strong><\/p>\n<p>\u5e38\u7528\u683c\u5f0f\uff1a<\/p>\n<ul>\n<li>\u5b57\u4f53\u989c\u8272\uff1acolor<\/li>\n<li>\u5b57\u4f53\u52a0\u7c97\uff1abold<\/li>\n<li>\u5b57\u4f53\u5927\u5c0f\uff1afont_site<\/li>\n<li>\u65e5\u671f\u683c\u5f0f\uff1anum_format<\/li>\n<li>\u8d85\u94fe\u63a5\uff1aurl<\/li>\n<li>\u4e0b\u5212\u7ebf\u8bbe\u7f6e\uff1aunderline<\/li>\n<li>\u5355\u5143\u683c\u989c\u8272\uff1abg_color<\/li>\n<li>\u8fb9\u6846\uff1aborder<\/li>\n<li>\u5bf9\u9f50\u65b9\u5f0f\uff1aalign<\/li>\n<\/ul>\n<pre><code class=\"language-python\"># \u81ea\u5b9a\u4e49\u683c\u5f0f\nf = workbook.add_format({&#039;border&#039;: 1, &#039;font_size&#039;: 13, &#039;bold&#039;: True, &#039;align&#039;: &#039;center&#039;,&#039;bg_color&#039;: &#039;cccccc&#039;})\nworksheet.write(&#039;A3&#039;, &quot;python excel&quot;, f)\nworksheet.set_row(0, 40, f)\nworksheet.set_column(&#039;A:E&#039;, 20, f)<\/code><\/pre>\n<p><strong>6.\u6279\u91cf\u5f80\u5355\u5143\u683c\u5199\u5165\u6570\u636e<\/strong><\/p>\n<pre><code class=\"language-python\"># \u6279\u91cf\u5f80\u5355\u5143\u683c\u5199\u5165\u6570\u636e\nworksheet.write_column(&#039;A15&#039;, [1, 2, 3, 4, 5])  # \u5217\u5199\u5165\uff0c\u4eceA15\u5f00\u59cb\nworksheet.write_row(&#039;A12&#039;, [6, 7, 8, 9])        # \u884c\u5199\u5165\uff0c\u4eceA12\u5f00\u59cb<\/code><\/pre>\n<p><strong>7.\u5408\u5e76\u5355\u5143\u683c\u5199\u5165<\/strong><\/p>\n<pre><code class=\"language-python\"># \u5408\u5e76\u5355\u5143\u683c\u5199\u5165\nworksheet.merge_range(7,5, 11, 8, &#039;merge_range&#039;)<\/code><\/pre>\n<p><strong>8.\u5173\u95ed\u6587\u4ef6<\/strong><\/p>\n<pre><code class=\"language-python\">workbook.close()<\/code><\/pre>\n<h3><strong>6.3 xlswriter \u751f\u6210\u6298\u7ebf\u56fe<\/strong><\/h3>\n<p>\u793a\u4f8b\u4ee3\u7801\uff1a<\/p>\n<pre><code class=\"language-python\"># -*- coding:utf-8 -*-\n\nimport xlsxwriter\n\n# \u521b\u5efa\u4e00\u4e2aexcel\nworkbook = xlsxwriter.Workbook(&quot;chart_line.xlsx&quot;)\n# \u521b\u5efa\u4e00\u4e2asheet\nworksheet = workbook.add_worksheet()\n# worksheet = workbook.add_worksheet(&quot;bug_analysis&quot;)\n\n# \u81ea\u5b9a\u4e49\u6837\u5f0f\uff0c\u52a0\u7c97\nbold = workbook.add_format({&#039;bold&#039;: 1})\n\n# --------1\u3001\u51c6\u5907\u6570\u636e\u5e76\u5199\u5165excel---------------\n# \u5411excel\u4e2d\u5199\u5165\u6570\u636e\uff0c\u5efa\u7acb\u56fe\u6807\u65f6\u8981\u7528\u5230\nheadings = [&#039;Number&#039;, &#039;testA&#039;, &#039;testB&#039;]\ndata = [\n    [&#039;2017-9-1&#039;, &#039;2017-9-2&#039;, &#039;2017-9-3&#039;, &#039;2017-9-4&#039;, &#039;2017-9-5&#039;, &#039;2017-9-6&#039;],\n    [10, 40, 50, 20, 10, 50],\n    [30, 60, 70, 50, 40, 30],\n]\n\n# \u5199\u5165\u8868\u5934\nworksheet.write_row(&#039;A1&#039;, headings, bold)\n\n# \u5199\u5165\u6570\u636e\nworksheet.write_column(&#039;A2&#039;, data[0])\nworksheet.write_column(&#039;B2&#039;, data[1])\nworksheet.write_column(&#039;C2&#039;, data[2])\n\n# --------2\u3001\u751f\u6210\u56fe\u8868\u5e76\u63d2\u5165\u5230excel---------------\n# \u521b\u5efa\u4e00\u4e2a\u67f1\u72b6\u56fe(line chart)\nchart_col = workbook.add_chart({&#039;type&#039;: &#039;line&#039;})\n\n# \u914d\u7f6e\u7b2c\u4e00\u4e2a\u7cfb\u5217\u6570\u636e\nchart_col.add_series({\n    # \u8fd9\u91cc\u7684sheet1\u662f\u9ed8\u8ba4\u7684\u503c\uff0c\u56e0\u4e3a\u6211\u4eec\u5728\u65b0\u5efasheet\u65f6\u6ca1\u6709\u6307\u5b9asheet\u540d\n    # \u5982\u679c\u6211\u4eec\u65b0\u5efasheet\u65f6\u8bbe\u7f6e\u4e86sheet\u540d\uff0c\u8fd9\u91cc\u5c31\u8981\u8bbe\u7f6e\u6210\u76f8\u5e94\u7684\u503c\n    &#039;name&#039;: &#039;=Sheet1!$B$1&#039;,\n    &#039;categories&#039;: &#039;=Sheet1!$A$2:$A$7&#039;,\n    &#039;values&#039;:   &#039;=Sheet1!$B$2:$B$7&#039;,\n    &#039;line&#039;: {&#039;color&#039;: &#039;red&#039;},\n})\n\n# \u914d\u7f6e\u7b2c\u4e8c\u4e2a\u7cfb\u5217\u6570\u636e\nchart_col.add_series({\n    &#039;name&#039;: &#039;=Sheet1!$C$1&#039;,\n    &#039;categories&#039;:  &#039;=Sheet1!$A$2:$A$7&#039;,\n    &#039;values&#039;:   &#039;=Sheet1!$C$2:$C$7&#039;,\n    &#039;line&#039;: {&#039;color&#039;: &#039;yellow&#039;},\n})\n\n# \u914d\u7f6e\u7b2c\u4e8c\u4e2a\u7cfb\u5217\u6570\u636e(\u7528\u4e86\u53e6\u4e00\u79cd\u8bed\u6cd5)\n# chart_col.add_series({\n#     &#039;name&#039;: [&#039;Sheet1&#039;, 0, 2],\n#     &#039;categories&#039;: [&#039;Sheet1&#039;, 1, 0, 6, 0],\n#     &#039;values&#039;: [&#039;Sheet1&#039;, 1, 2, 6, 2],\n#     &#039;line&#039;: {&#039;color&#039;: &#039;yellow&#039;},\n# })\n\n# \u8bbe\u7f6e\u56fe\u8868\u7684title \u548c x\uff0cy\u8f74\u4fe1\u606f\nchart_col.set_title({&#039;name&#039;: &#039;The xxx site Bug Analysis&#039;})\nchart_col.set_x_axis({&#039;name&#039;: &#039;Test number&#039;})\nchart_col.set_y_axis({&#039;name&#039;:  &#039;Sample length (mm)&#039;})\n\n# \u8bbe\u7f6e\u56fe\u8868\u7684\u98ce\u683c\nchart_col.set_style(1)\n\n# \u628a\u56fe\u8868\u63d2\u5165\u5230worksheet\u5e76\u8bbe\u7f6e\u504f\u79fb\nworksheet.insert_chart(&#039;A10&#039;, chart_col, {&#039;x_offset&#039;: 25, &#039;y_offset&#039;: 10})\n\nworkbook.close()<\/code><\/pre>\n<p>\u6548\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141413749.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>6.4 xlswriter \u751f\u6210\u67f1\u72b6\u56fe<\/strong><\/h3>\n<p>\u793a\u4f8b\u4ee3\u7801\uff1a<\/p>\n<pre><code class=\"language-python\"># -*- coding:utf-8 -*-\n\nimport xlsxwriter\n\n# \u521b\u5efa\u4e00\u4e2aexcel\nworkbook = xlsxwriter.Workbook(&quot;chart_column.xlsx&quot;)\n# \u521b\u5efa\u4e00\u4e2asheet\nworksheet = workbook.add_worksheet()\n# worksheet = workbook.add_worksheet(&quot;bug_analysis&quot;)\n\n# \u81ea\u5b9a\u4e49\u6837\u5f0f\uff0c\u52a0\u7c97\nbold = workbook.add_format({&#039;bold&#039;: 1})\n\n# --------1\u3001\u51c6\u5907\u6570\u636e\u5e76\u5199\u5165excel---------------\n# \u5411excel\u4e2d\u5199\u5165\u6570\u636e\uff0c\u5efa\u7acb\u56fe\u6807\u65f6\u8981\u7528\u5230\nheadings = [&#039;Number&#039;, &#039;testA&#039;, &#039;testB&#039;]\ndata = [\n    [&#039;2017-9-1&#039;, &#039;2017-9-2&#039;, &#039;2017-9-3&#039;, &#039;2017-9-4&#039;, &#039;2017-9-5&#039;, &#039;2017-9-6&#039;],\n    [10, 40, 50, 20, 10, 50],\n    [30, 60, 70, 50, 40, 30],\n]\n\n# \u5199\u5165\u8868\u5934\nworksheet.write_row(&#039;A1&#039;, headings, bold)\n\n# \u5199\u5165\u6570\u636e\nworksheet.write_column(&#039;A2&#039;, data[0])\nworksheet.write_column(&#039;B2&#039;, data[1])\nworksheet.write_column(&#039;C2&#039;, data[2])\n\n# --------2\u3001\u751f\u6210\u56fe\u8868\u5e76\u63d2\u5165\u5230excel---------------\n# \u521b\u5efa\u4e00\u4e2a\u67f1\u72b6\u56fe(column chart)\nchart_col = workbook.add_chart({&#039;type&#039;: &#039;column&#039;})\n\n# \u914d\u7f6e\u7b2c\u4e00\u4e2a\u7cfb\u5217\u6570\u636e\nchart_col.add_series({\n    # \u8fd9\u91cc\u7684sheet1\u662f\u9ed8\u8ba4\u7684\u503c\uff0c\u56e0\u4e3a\u6211\u4eec\u5728\u65b0\u5efasheet\u65f6\u6ca1\u6709\u6307\u5b9asheet\u540d\n    # \u5982\u679c\u6211\u4eec\u65b0\u5efasheet\u65f6\u8bbe\u7f6e\u4e86sheet\u540d\uff0c\u8fd9\u91cc\u5c31\u8981\u8bbe\u7f6e\u6210\u76f8\u5e94\u7684\u503c\n    &#039;name&#039;: &#039;=Sheet1!$B$1&#039;,\n    &#039;categories&#039;: &#039;=Sheet1!$A$2:$A$7&#039;,\n    &#039;values&#039;:   &#039;=Sheet1!$B$2:$B$7&#039;,\n    &#039;line&#039;: {&#039;color&#039;: &#039;red&#039;},\n})\n\n# \u914d\u7f6e\u7b2c\u4e8c\u4e2a\u7cfb\u5217\u6570\u636e(\u7528\u4e86\u53e6\u4e00\u79cd\u8bed\u6cd5)\nchart_col.add_series({\n    &#039;name&#039;: &#039;=Sheet1!$C$1&#039;,\n    &#039;categories&#039;:  &#039;=Sheet1!$A$2:$A$7&#039;,\n    &#039;values&#039;:   &#039;=Sheet1!$C$2:$C$7&#039;,\n    &#039;line&#039;: {&#039;color&#039;: &#039;yellow&#039;},\n})\n\n# \u914d\u7f6e\u7b2c\u4e8c\u4e2a\u7cfb\u5217\u6570\u636e(\u7528\u4e86\u53e6\u4e00\u79cd\u8bed\u6cd5)\n# chart_col.add_series({\n#     &#039;name&#039;: [&#039;Sheet1&#039;, 0, 2],\n#     &#039;categories&#039;: [&#039;Sheet1&#039;, 1, 0, 6, 0],\n#     &#039;values&#039;: [&#039;Sheet1&#039;, 1, 2, 6, 2],\n#     &#039;line&#039;: {&#039;color&#039;: &#039;yellow&#039;},\n# })\n\n# \u8bbe\u7f6e\u56fe\u8868\u7684title \u548c x\uff0cy\u8f74\u4fe1\u606f\nchart_col.set_title({&#039;name&#039;: &#039;The xxx site Bug Analysis&#039;})\nchart_col.set_x_axis({&#039;name&#039;: &#039;Test number&#039;})\nchart_col.set_y_axis({&#039;name&#039;:  &#039;Sample length (mm)&#039;})\n\n# \u8bbe\u7f6e\u56fe\u8868\u7684\u98ce\u683c\nchart_col.set_style(1)\n\n# \u628a\u56fe\u8868\u63d2\u5165\u5230worksheet\u4ee5\u53ca\u504f\u79fb\nworksheet.insert_chart(&#039;A10&#039;, chart_col, {&#039;x_offset&#039;: 25, &#039;y_offset&#039;: 10})\n\nworkbook.close()<\/code><\/pre>\n<p>\u6548\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141413548.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<h3><strong>6.5 xlswriter \u751f\u6210\u997c\u56fe<\/strong><\/h3>\n<p>\u793a\u4f8b\u4ee3\u7801\uff1a<\/p>\n<pre><code class=\"language-python\"># -*- coding:utf-8 -*-\n\nimport xlsxwriter\n\n# \u521b\u5efa\u4e00\u4e2aexcel\nworkbook = xlsxwriter.Workbook(&quot;chart_pie.xlsx&quot;)\n# \u521b\u5efa\u4e00\u4e2asheet\nworksheet = workbook.add_worksheet()\n\n# \u81ea\u5b9a\u4e49\u6837\u5f0f\uff0c\u52a0\u7c97\nbold = workbook.add_format({&#039;bold&#039;: 1})\n\n# --------1\u3001\u51c6\u5907\u6570\u636e\u5e76\u5199\u5165excel---------------\n# \u5411excel\u4e2d\u5199\u5165\u6570\u636e\uff0c\u5efa\u7acb\u56fe\u6807\u65f6\u8981\u7528\u5230\ndata = [\n    [&#039;closed&#039;, &#039;active&#039;, &#039;reopen&#039;, &#039;NT&#039;],\n    [1012, 109, 123, 131],\n]\n\n# \u5199\u5165\u6570\u636e\nworksheet.write_row(&#039;A1&#039;, data[0], bold)\nworksheet.write_row(&#039;A2&#039;, data[1])\n\n# --------2\u3001\u751f\u6210\u56fe\u8868\u5e76\u63d2\u5165\u5230excel---------------\n# \u521b\u5efa\u4e00\u4e2a\u67f1\u72b6\u56fe(pie chart)\nchart_col = workbook.add_chart({&#039;type&#039;: &#039;pie&#039;})\n\n# \u914d\u7f6e\u7b2c\u4e00\u4e2a\u7cfb\u5217\u6570\u636e\nchart_col.add_series({\n    &#039;name&#039;: &#039;Bug Analysis&#039;,\n    &#039;categories&#039;: &#039;=Sheet1!$A$1:$D$1&#039;,\n    &#039;values&#039;: &#039;=Sheet1!$A$2:$D$2&#039;,\n    &#039;points&#039;: [\n        {&#039;fill&#039;: {&#039;color&#039;: &#039;#00CD00&#039;}},\n        {&#039;fill&#039;: {&#039;color&#039;: &#039;red&#039;}},\n        {&#039;fill&#039;: {&#039;color&#039;: &#039;yellow&#039;}},\n        {&#039;fill&#039;: {&#039;color&#039;: &#039;gray&#039;}},\n    ],\n\n})\n\n# \u8bbe\u7f6e\u56fe\u8868\u7684title \u548c x\uff0cy\u8f74\u4fe1\u606f\nchart_col.set_title({&#039;name&#039;: &#039;Bug Analysis&#039;})\n\n# \u8bbe\u7f6e\u56fe\u8868\u7684\u98ce\u683c\nchart_col.set_style(10)\n\n# \u628a\u56fe\u8868\u63d2\u5165\u5230worksheet\u4ee5\u53ca\u504f\u79fb\nworksheet.insert_chart(&#039;B10&#039;, chart_col, {&#039;x_offset&#039;: 25, &#039;y_offset&#039;: 10})\nworkbook.close()<\/code><\/pre>\n<p>\u6548\u679c\u5982\u4e0b\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141413938.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>6.6 \u5b9e\u6218\u8bad\u7ec3<\/strong><\/h3>\n<p><strong>1.xlswriter\u65b0\u5efa\u5e76\u5199\u5165Excel<\/strong><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code># 3.6.2 xlswriter\u65b0\u5efa\u5e76\u5199\u5165Excel\ndef fun3_6_2():\n    # \u521b\u5efaExce\u5e76\u6dfb\u52a0sheet\n    workbook = xlsxwriter.Workbook(&#039;demo.xlsx&#039;)\n    worksheet = workbook.add_worksheet()\n\n    # \u8bbe\u7f6e\u5217\u5bbd\n    worksheet.set_column(&#039;A:A&#039;, 20)\n\n    # \u8bbe\u7f6e\u683c\u5f0f\n    bold = workbook.add_format({&#039;bold&#039;: True})\n\n    # \u6dfb\u52a0\u6587\u5b57\u5185\u5bb9\n    worksheet.write(&#039;A1&#039;, &#039;Hello&#039;)\n\n    # \u6309\u683c\u5f0f\u6dfb\u52a0\u5185\u5bb9\n    worksheet.write(&#039;A2&#039;, &#039;World&#039;, bold)\n\n    # \u5199\u4e00\u4e9b\u6570\u5b57\n    worksheet.write(2, 0, 123)\n    worksheet.write(3, 0, 123.456)\n\n    # \u6dfb\u52a0\u56fe\u7247\n    worksheet.insert_image(&#039;B5&#039;, &#039;demo.png&#039;)\n\n    workbook.close()<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/mmbiz.qpic.cn\/mmbiz_png\/PvP6qjUpvIro0I67PdvBOWIE9fbaibCXbxndiaYgPYXjdgxOGEKWm4IJPnsmJpO2RcBmzV2K26uHHS6V0Edekycg\/640?wx_fmt=png&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1\" alt=\"\u56fe\u7247\" \/> <\/p>\n<h2>7. Python win32com \u8bfb\u53d6 \u5199\u5165 \u4fee\u6539 \u64cd\u4f5cExcel<\/h2>\n<p>python\u53ef\u4ee5\u4f7f\u7528\u4e00\u4e2a\u7b2c\u4e09\u65b9\u5e93\u53eb\u505awin32com\u8fbe\u5230\u64cd\u4f5ccom\u7684\u76ee\u7684\uff0cwin32com\u529f\u80fd\u5f3a\u5927\uff0c\u53ef\u4ee5\u64cd\u4f5cword\u3001\u8c03\u7528\u5b8f\u7b49\u7b49\u7b49\u3002<\/p>\n<h3><strong>7.1 pip\u5b89\u88c5win32com<\/strong><\/h3>\n<pre><code>pip install pypiwin32<\/code><\/pre>\n<p>\u7531\u4e8e\u6211\u5df2\u7ecf\u5b89\u88c5\u8fc7\u4e86\uff0c\u6240\u4ee5\u63d0\u793a\u5df2\u7ecf\u5b89\u88c5\uff1a<\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141413579.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h3><strong>7.2 Python\u4f7f\u7528win32com\u8bfb\u5199Excel<\/strong><\/h3>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\">import win32com\nfrom win32com.client import Dispatch, constants\nimport os\n\n# \u83b7\u53d6\u5f53\u524d\u811a\u672c\u8def\u5f84\ndef getScriptPath():\n    nowpath = os.path.split(os.path.realpath(__file__))[0]\n    print(nowpath)\n    return nowpath\n\n# 3.7.2 Python\u4f7f\u7528win32com\u8bfb\u5199Excel\ndef fun3_7_2():\n    app = win32com.client.Dispatch(&#039;Excel.Application&#039;)\n\n    # \u540e\u53f0\u8fd0\u884c\uff0c\u4e0d\u663e\u793a\uff0c\u4e0d\u8b66\u544a\n    app.Visible = 0\n    app.DisplayAlerts = 0\n\n    # \u521b\u5efa\u65b0\u7684Excel\n    # WorkBook = app.Workbooks.Add()\n    # \u65b0\u5efasheet\n    # sheet = WorkBook.Worksheets.Add()\n\n    # \u6253\u5f00\u5df2\u5b58\u5728\u8868\u683c\uff0c\u6ce8\u610f\u8fd9\u91cc\u8981\u7528\u7edd\u5bf9\u8def\u5f84\n    WorkBook = app.Workbooks.Open(getScriptPath() + &quot;\\\\3_7 win32com \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&quot;)\n    sheet = WorkBook.Worksheets(&#039;Sheet1&#039;)\n\n    # \u83b7\u53d6\u5355\u5143\u683c\u4fe1\u606f \u7b2cn\u884cn\u5217\uff0c\u4e0d\u7528-1\n    cell01_value = sheet.Cells(1,2).Value\n    print(&quot;cell01\u7684\u5185\u5bb9\u4e3a\uff1a&quot;,cell01_value)\n\n    # \u5199\u5165\u8868\u683c\u4fe1\u606f\n    sheet.Cells(2, 1).Value = &quot;win32com&quot;\n\n    # \u4fdd\u5b58\u8868\u683c\n    #WorkBook.Save()\n\n    # \u53e6\u5b58\u4e3a\u5b9e\u73b0\u62f7\u8d1d\n    WorkBook.SaveAs(getScriptPath() + &quot;\\\\new.xlsx&quot;)\n\n    # \u5173\u95ed\u8868\u683c\n    WorkBook.Close()\n    app.Quit()\n\nif __name__ == &#039;__main__&#039;:\n    fun3_7_2()<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141413905.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>\u5185\u5bb9\u4e3a\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141413384.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<h2>8. Python pandas \u8bfb\u53d6 \u5199\u5165 \u64cd\u4f5cExcel<\/h2>\n<p><strong>\u7b80\u4ecb\uff1a<\/strong><\/p>\n<p>pandas \u662f\u57fa\u4e8eNumPy \u7684\u4e00\u79cd\u5de5\u5177\uff0c\u8be5\u5de5\u5177\u662f\u4e3a\u4e86\u89e3\u51b3\u6570\u636e\u5206\u6790\u4efb\u52a1\u800c\u521b\u5efa\u7684\u3002Pandas \u7eb3\u5165\u4e86\u5927\u91cf\u5e93\u548c\u4e00\u4e9b\u6807\u51c6\u7684\u6570\u636e\u6a21\u578b\uff0c\u63d0\u4f9b\u4e86\u9ad8\u6548\u5730\u64cd\u4f5c\u5927\u578b\u6570\u636e\u96c6\u6240\u9700\u7684\u5de5\u5177\u3002pandas\u63d0\u4f9b\u4e86\u5927\u91cf\u80fd\u4f7f\u6211\u4eec\u5feb\u901f\u4fbf\u6377\u5730\u5904\u7406\u6570\u636e\u7684\u51fd\u6570\u548c\u65b9\u6cd5\u3002\u4f60\u5f88\u5feb\u5c31\u4f1a\u53d1\u73b0\uff0c\u5b83\u662f\u4f7fPython\u6210\u4e3a\u5f3a\u5927\u800c\u9ad8\u6548\u7684\u6570\u636e\u5206\u6790\u73af\u5883\u7684\u91cd\u8981\u56e0\u7d20\u4e4b\u4e00\u3002<\/p>\n<p>\u5b98\u65b9\u7f51\u7ad9\uff1a<a href=\"https:\/\/pandas.pydata.org\/\">https:\/\/pandas.pydata.org\/<\/a><\/p>\n<p>\u5b98\u65b9\u6587\u6863\uff1a<a href=\"https:\/\/pandas.pydata.org\/pandas-docs\/stable\/\">https:\/\/pandas.pydata.org\/pandas-docs\/stable\/<\/a><\/p>\n<h3><strong>8.1 pip\u5b89\u88c5pandas<\/strong><\/h3>\n<pre><code>pip install pandas<\/code><\/pre>\n<h3><strong>8.2 pandas \u8bfb\u5199 Excel<\/strong><\/h3>\n<p><strong>\u8868\u683c\u5185\u5bb9\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141414821.png\" alt=\"\u56fe\u7247\" \/><\/p>\n<p><strong>\u7a0b\u5e8f\u793a\u4f8b\uff1a<\/strong><\/p>\n<pre><code class=\"language-python\">import pandas as pd\nfrom pandas import DataFrame\n\n# 3.8.2 pandas\u8bfb\u5199Excel\ndef fun3_8_2():\n    data = pd.read_excel(&#039;3_8 pandas \u4fee\u6539\u64cd\u4f5c\u7ec3\u4e60.xlsx&#039;, sheet_name=&#039;Sheet1&#039;)\n    print(data)\n\n    # \u589e\u52a0\u884c\u6570\u636e\uff0c\u5728\u7b2c5\u884c\u65b0\u589e\n    data.loc[4] = [&#039;4&#039;, &#039;john&#039;, &#039;pandas&#039;]\n\n    # \u589e\u52a0\u5217\u6570\u636e\uff0c\u7ed9\u5b9a\u9ed8\u8ba4\u503cNone\n    data[&#039;new_col&#039;] = None\n\n    # \u4fdd\u5b58\u6570\u636e\n    DataFrame(data).to_excel(&#039;new.xlsx&#039;, sheet_name=&#039;Sheet1&#039;, index=False, header=True)\n\nif __name__ == &#039;__main__&#039;:\n    fun3_8_2()<\/code><\/pre>\n<p><strong>\u6548\u679c\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141414025.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p><strong>\u751f\u6210\u7684excel\u5982\u4e0b\uff1a<\/strong><\/p>\n<p><img src=\"https:\/\/lemon-guess.oss-cn-hangzhou.aliyuncs.com\/img\/202206141414406.png\" alt=\"\u56fe\u7247\" \/> <\/p>\n<p>pandas\u529f\u80fd\u975e\u5e38\u5f3a\u5927\uff0c\u8fd9\u91cc\u53ea\u662f\u505a\u4e86\u53c8\u7ed9\u5f88\u7b80\u5355\u7684\u793a\u4f8b\uff0c\u8fd8\u6709\u5f88\u591a\u5176\u5b83\u64cd\u4f5c\uff0c\u53ef\u53c2\u8003\u5b98\u65b9\u6587\u6863\u6216\u5feb\u901f\u5165\u95e8\u8fdb\u884c\u5b66\u4e60\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Python \u64cd\u4f5c Excel \u62a5\u8868\u81ea\u52a8\u5316\u6307\u5357\uff01 \u516c\u4f17\u53f7 \u4f5c\u8005\uff1a\u8d85\u7ea7\u5927\u6d0b\u8471806 \u6765\u6e90\uff1ahttps:\/\/blog.csdn.net\/u0   \u2026 &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0},"categories":[48],"tags":[],"_links":{"self":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts\/887"}],"collection":[{"href":"\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=887"}],"version-history":[{"count":1,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/887\/revisions"}],"predecessor-version":[{"id":888,"href":"\/index.php?rest_route=\/wp\/v2\/posts\/887\/revisions\/888"}],"wp:attachment":[{"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=887"},{"taxonomy":"post_tag","embeddable":true,"href":"\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}