{"id":180,"date":"2018-11-15T11:00:11","date_gmt":"2018-11-15T03:00:11","guid":{"rendered":"http:\/\/www.subingkang.com\/?p=180"},"modified":"2025-08-06T13:58:24","modified_gmt":"2025-08-06T05:58:24","slug":"python-excel%e6%93%8d%e4%bd%9c%e5%ba%93-xlrd-%e5%92%8cxlwt","status":"publish","type":"post","link":"https:\/\/subk.me\/?p=180","title":{"rendered":"Python Excel\u64cd\u4f5c\u5e93 xlrd \u548cxlwt"},"content":{"rendered":"
\n\nxlrd<\/strong>\u00a0<\/a>\u548c\u00a0xlwt<\/strong>\u00a0<\/a>\u662f Python \u7684\u7b2c\u4e09\u65b9\u5e93\uff0c\u5b83\u53ef\u4ee5\u7528\u6765\u8bfb\u5199\u00a0Excel \u4e2d\u7684\u6570\u636e\uff0c\u652f\u6301 .xls \u548c .xlsx \u7684 EXCEL \u683c\u5f0f\n\nxlrd\u00a0\u548c\u00a0xlwt\u00a0\u8981\u662f\u9488\u5bf9\u00a0Office 2013 \u6216\u66f4\u65e9\u7248\u672c\u7684 XLS \u6587\u4ef6\u683c\u5f0f \u00a0\uff0c\u5bf9 xlsx \u652f\u6301\u8f83\u5f31\u3002\n\n<\/span>\n

xlrd<\/h3>\n\u4e3b\u8981\u7528\u4e8e\u8bfb\u53d6 EXCEL \u4e2d\u7684\u4fe1\u606f\n\n\u5b89\u88c5 xlrd\n
pip <span class="hljs-keyword">install<\/span> xlrd   <span class="hljs-comment">#\u5728\u7ebf\u5b89\u88c5xlrd<\/span><\/code><\/pre>\n\u5bfc\u5165\u6a21\u5757\n
<span class="hljs-keyword">import<\/span> xlrd<\/code><\/pre>\n\u6253\u5f00\u6307\u5b9a\u7684 Excel\n\n\u65b9\u6cd5 1\uff1axxx.py \u7a0b\u5e8f \u548c xxx.xlsx \u5728\u540c\u4e00\u76ee\u5f55\n
<span class="hljs-comment"># encoding : utf-8<\/span>\nimport xlrd\n\ndata = xlrd.open_workbook(<span class="hljs-string">"1.xlsx"<\/span>)   <span class="hljs-comment">#\u83b7\u53d6EXCEL \u5de5\u4f5c\u7c3f\u5bf9\u8c61<\/span>\n\n<\/code><\/pre>\n\u65b9\u6cd5 2\uff1a\u6253\u5f00\u6307\u5b9a\u76ee\u5f55\u7684 Excel\n\n\u6ce8\u610f\u8981\u8fd9\u91cc\u7684\u00a0\u2018 \/ \u2019\u6b63\u659c\u6760 \u548c\u00a0‘ \\ ‘ \u53cd\u659c\u6760 \uff0cr \u4ee3\u8868\u4e0d\u8f6c\u4e49<\/strong>\n\u5728 Windows \u7cfb\u7edf\u4e2d\uff0c\u6b63\u659c\u6760 \/ \u8868\u793a\u9664\u6cd5\uff0c\u7528\u6765\u8fdb\u884c\u6574\u9664\u8fd0\u7b97\uff1b\u53cd\u659c\u6760 \\ \u7528\u6765\u8868\u793a\u76ee\u5f55\u3002\n\u5728 Unix \u7cfb\u7edf\u4e2d\uff0c\/ \u8868\u793a\u76ee\u5f55\uff1b\\ \u8868\u793a\u8df3\u8131\u5b57\u7b26\u5c06\u7279\u6b8a\u5b57\u7b26\u53d8\u6210\u4e00\u822c\u5b57\u7b26\uff08\u5982 enter,$, \u7a7a\u683c\u7b49\uff09\u3002\n
filepath=<span class="hljs-string">r'D:\/python demo\/text\/1.xlsx'<\/span>\ndata = xlrd.open_workbook(filepath) <span class="hljs-comment">#\u83b7\u53d6EXCEL<\/span><\/code><\/pre>\n\u83b7\u53d6\u5de5\u4f5c\u8868\n
<span class="hljs-attr">sheet<\/span>= data.sheets()[<span class="hljs-number">0<\/span>]             <span class="hljs-comment">#\u901a\u8fc7\u7d22\u5f15\u987a\u5e8f\u83b7\u53d6  <\/span>\n<span class="hljs-attr">sheet<\/span>= data.sheet_by_index(<span class="hljs-number">0<\/span>)       <span class="hljs-comment">#\u901a\u8fc7\u7d22\u5f15\u987a\u5e8f\u83b7\u53d6  <\/span>\n<span class="hljs-attr">sheet<\/span>= data.sheet_by_name(<span class="hljs-string">'Sheet1'<\/span>) <span class="hljs-comment">#\u901a\u8fc7\u540d\u79f0\u83b7\u53d6<\/span><\/code><\/pre>\n\u83b7\u53d6 Excel \u603b\u884c\u6570\u548c\u603b\u5217\u6570\n
nrows = sheet.nrows    <span class="hljs-comment">#\u83b7\u53d6EXCEL\u8868\u683c\u4e2d\u7684\u603b\u884c\u6570  <\/span>\nncols = sheet.ncols    <span class="hljs-comment">#\u603b\u5217\u6570<\/span><\/code><\/pre>\n\u83b7\u53d6\u6307\u5b9a\u884c \u7684\u5bf9\u8c61\uff0c\u8fd4\u56de\u4e00\u4e2a LIST \u5217\u8868 \u00a0\u7b2c\u4e00\u884c\uff0c\u5217 \u7684\u5e8f\u53f7\uff08\u5750\u6807\uff09\u4e3a 0\n
row_list = sheet.row_values(0)   <span class="hljs-comment">#\u8fd4\u56de\u7b2c\u884c\u7684\u6570\u636e\uff0c\u7528\u4e00\u4e2a\u5217\u8868\u4fdd\u5b58<\/span>\ncol_list = sheet.col_values(0)   <span class="hljs-comment">#\u7b2c\u4e00\u5217<\/span><\/code><\/pre>\n\u5faa\u73af\u5217\u8868\u4e2d\u7684\u6570\u636e\uff0c\u5982\u4e0b\u56fe\n
<span class="hljs-keyword">for<\/span> i in range(nrows):  \n    <span class="hljs-keyword">print<\/span> (sheet.row_values(i)) \n<span class="hljs-string">''<\/span>\n<span class="hljs-string">'\n['<\/span>\u5e8f\u53f7<span class="hljs-string">', '<\/span>\u4e0b\u5355\u65f6\u95f4<span class="hljs-string">', '<\/span>\u56fe\u7247<span class="hljs-string">', '<\/span>\u54c1\u724c<span class="hljs-string">', '<\/span>\u7801\u6570<span class="hljs-string">', '<\/span>\u5355\u4ef7<span class="hljs-string">', '<\/span>\u6c47\u7387<span class="hljs-string">', '<\/span>\u5883\u5185\u8fd0\u8d39<span class="hljs-string">', '<\/span>\u56fd\u9645\u8fd0\u8d39<span class="hljs-string">', '<\/span>\u603b\u4ef7<span class="hljs-string">'] \n['<\/span>A1<span class="hljs-string">', 42778.0, '<\/span>\n<span class="hljs-string">', '<\/span>\u963f\u8fea<span class="hljs-string">', 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]\n['<\/span>A2<span class="hljs-string">', 42779.0, '<\/span>\n<span class="hljs-string">', '<\/span>\u8010\u514b<span class="hljs-string">', 2.0, 2.0, 2.0, 2.0, 2.0, 2.0]\n'<\/span>\n<span class="hljs-string">''<\/span><\/code><\/pre>\n\u83b7\u53d6\u5355\u5143\u683c (CELL) \u4e2d\u7684 \u6570\u636e\uff0c\u7c7b\u578b\u4e3a str \uff0c\u6709\u4e24\u79cd\u65b9\u6cd5\n
A1=sheet.cell_value(0,0)  <span class="hljs-comment">#\u65b9\u6cd51<\/span>\nB2=sheet.cell(1,1).value  <span class="hljs-comment">#\u65b9\u6cd52<\/span><\/code><\/pre>\u6ce8\u610f\u4e8b\u9879\uff1a<\/strong>\n\n1\u3001xlsx \u4e2d\u7684\u56fe\u7247\u4fe1\u606f\u4e0d\u80fd\u8bfb\u53d6\uff0c\uff08\u6d4b\u8bd5\u4e0d\u80fd\u8bfb\u53d6\uff0c\u8fd4\u56de\u7a7a\u5b57\u7b26\u4e32 ” \uff09<\/strong>\n\n2\u3001\u5982\u679c\u4f60\u8bbe\u5728 Excel \u4e2d \u8bbe\u7f6e\u4e86\u65e5\u671f\u683c\u5f0f\uff0c\u9700\u8981\u8fdb\u884c\u8f6c\u6362\uff0c\u5426\u5219\u8fd4\u56de EXCEL \u65e5\u671f\u7684 \u5e38\u89c4\u6570\u636e\u5f62\u5f0f<\/strong>\n\n\u5176\u4ed6\u5e93\uff0c\u53c2\u8003\uff1ahttp:\/\/www.gocalf.com\/blog\/python-read-write-excel.html\n\n<\/div>\n
<\/p>\n
\u6765\u6e90\uff1a\u00a0https:\/\/my.oschina.net\/esdn\/blog\/837139<\/a><\/small><\/div>\n
<\/div>\n
<\/div>\n
\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n<\/div>\n
\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"
xlrd\u00a0\u548c\u00a0xlwt\u00a0\u662f Python \u7684\u7b2c\u4e09\u65b9\u5e93\uff0c\u5b83\u53ef\u4ee5\u7528\u6765\u8bfb\u5199\u00a0Excel \u4e2d\u7684\u6570\u636e\uff0c\u652f\u6301 .xls \u548c […]<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[23],"class_list":["post-180","post","type-post","status-publish","format-standard","hentry","category-it","tag-python"],"_links":{"self":[{"href":"https:\/\/subk.me\/index.php?rest_route=\/wp\/v2\/posts\/180","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/subk.me\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/subk.me\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/subk.me\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/subk.me\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=180"}],"version-history":[{"count":3,"href":"https:\/\/subk.me\/index.php?rest_route=\/wp\/v2\/posts\/180\/revisions"}],"predecessor-version":[{"id":562,"href":"https:\/\/subk.me\/index.php?rest_route=\/wp\/v2\/posts\/180\/revisions\/562"}],"wp:attachment":[{"href":"https:\/\/subk.me\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=180"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/subk.me\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=180"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/subk.me\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=180"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}