{"id":1095,"date":"2023-06-11T22:40:19","date_gmt":"2023-06-11T14:40:19","guid":{"rendered":"http:\/\/xinyixx.com\/?p=1095"},"modified":"2023-08-14T16:06:26","modified_gmt":"2023-08-14T08:06:26","slug":"python13","status":"publish","type":"post","link":"https:\/\/www.xinyixx.com\/index.php\/2023\/06\/11\/python13\/","title":{"rendered":"python\u7a0b\u5e8f13\uff1a\u4e00\u4e2a\u4eceexcel\u63d0\u53d6\u6570\u636e\u7684\u7a0b\u5e8f"},"content":{"rendered":"<p>\u5bf9\u4e8e\u7528python\u5904\u7406excel\u7684\u6570\u636e\uff0c\u9700\u8981openpyxl\u6a21\u5757\uff0c\u7b80\u5355\u4ecb\u7ecd\u4e00\u4e0b\u3002<\/p>\n\n\n\n<p><code>openpyxl<\/code>\u662f\u4e00\u4e2a\u7528\u4e8e\u8bfb\u5199Excel\u6587\u4ef6\u7684Python\u5e93\u3002\u5b83\u53ef\u4ee5\u8bfb\u53d6\u548c\u5199\u5165<code>.xlsx<\/code>\u6587\u4ef6\uff0c\u5e76\u4e14\u63d0\u4f9b\u4e86\u8bb8\u591a\u529f\u80fd\u6765\u64cd\u4f5cExcel\u6587\u4ef6\uff0c\u4f8b\u5982\u521b\u5efa\u3001\u4fee\u6539\u3001\u5220\u9664\u5de5\u4f5c\u8868\u3001\u5355\u5143\u683c\u548c\u56fe\u8868\u7b49\u3002<code>openpyxl<\/code>\u8fd8\u652f\u6301\u8bb8\u591aExcel\u7279\u6027\uff0c\u5982\u516c\u5f0f\u3001\u56fe\u8868\u3001\u56fe\u50cf\u548c\u6570\u636e\u9a8c\u8bc1\u7b49\u3002\u6b64\u5916\uff0c\u5b83\u8fd8\u652f\u6301Pandas\u6570\u636e\u7ed3\u6784\u548cNumPy\u6570\u7ec4\uff0c\u4f7f\u5f97\u6570\u636e\u7684\u5bfc\u5165\u548c\u5bfc\u51fa\u66f4\u52a0\u65b9\u4fbf\u3002<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>import openpyxl<br>workbook = openpyxl.load_workbook('example.xlsx')# \u6253\u5f00Excel\u6587\u4ef6<br>sheet = workbook.active# \u83b7\u53d6\u5de5\u4f5c\u8868<br>cell = sheet['A1']# \u8bfb\u53d6\u5355\u5143\u683c<br>print(cell.value)<br>sheet['A2'] = 'Hello, world!'# \u5199\u5165\u5355\u5143\u683c<br>workbook.save('example.xlsx')# \u4fdd\u5b58\u6587\u4ef6<\/code><\/pre>\n\n\n\n<p>\u4e0b\u9762\u5199\u4e00\u4e0b\u6e90\u4ee3\u7801\uff0c\u5b9e\u73b0\u529f\u80fd\u662f\u4ece\u6307\u5b9a\u6587\u4ef6\u5939\u4e0b\u7684\u6240\u6709Excel\u6587\u4ef6\u4e2d\u63d0\u53d6ID\u6570\u636e\uff0c\u5e76\u5c06\u8fd9\u4e9b\u6570\u636e\u5199\u5165\u4e00\u4e2a\u65b0\u7684Excel\u6587\u4ef6\u4e2d\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import os\nfrom openpyxl import load_workbook, Workbook\nfrom openpyxl.styles import Font, colors, Alignment, PatternFill\n\nwork_path = os.getcwd() + \"\\\u8d44\u6599\"\npathss = []\n\nfor root, dirs, files in os.walk(work_path):\n    path = [os.path.join(root, name) for name in files]\n    for i in range(len(path)):\n        if path[i].endswith(\".xlsx\"):\n            pathss.append(path[i])\n\ndef Get_system_ID(file):\n    wb = load_workbook(file)\n    ws = wb.active\n    ID_list = []\n    for row in range(2, ws.max_row+1):\n        ID = ws[\"A\"+str(row)].value\n        if ID != None:\n            ID_list.append(ID)\n    return ID_list\n\ntotal_list = []\nfor file in pathss:\n    info = Get_system_ID(file)\n    total_list += info\n\nwb = Workbook()\nws = wb.active\nws.column_dimensions['A'].width=18.5\nws.cell(row=1,column=1,value=\"ID\")\ncolor_fill = PatternFill(fill_type='solid', fgColor=\"B3CFA1\")\nws.cell(row=1, column=1).fill = color_fill\n\nfor row in range(1,len(total_list)+1):\n    ws.cell(row=row+1,column=1,value=total_list[row-1])\n\nfont_set = Font(name='Arial', size=9)\nfor i in range(1,ws.max_row+1):\n    ws.cell(row=i,column=1).font = font_set\n    ws.cell(row=i,column=1).alignment = Alignment(horizontal='left', vertical='center',shrink_to_fit=True)\n\nwb.save(os.getcwd()+\"\\ID.xlsx\")\nprint(f\"\\n\u5171\u83b7\u53d6\u5230 {len(pathss)} \u4e2a Excel\u8868\uff0c\u5171 {len(total_list)} \u4e2aID\u3002\")\n<\/pre>\n\n\n\n<p>\u5177\u4f53\u5b9e\u73b0\u662f\u4f7f\u7528<code>os<\/code>\u6a21\u5757\u83b7\u53d6\u6307\u5b9a\u6587\u4ef6\u5939\u4e0b\u6240\u6709\u7684Excel\u6587\u4ef6\u8def\u5f84\uff0c\u5e76\u5c06\u5b83\u4eec\u5b58\u5165\u4e00\u4e2a\u5217\u8868\u4e2d\u3002\u7136\u540e\u5b9a\u4e49\u4e86\u4e00\u4e2a\u51fd\u6570<code>Get_system_ID<\/code>\uff0c\u7528\u4e8e\u4ece\u4e00\u4e2aExcel\u6587\u4ef6\u4e2d\u8bfb\u53d6ID\u6570\u636e\u3002\u63a5\u7740\uff0c\u4ee3\u7801\u904d\u5386\u6240\u6709Excel\u6587\u4ef6\uff0c\u4f7f\u7528<code>Get_system_ID<\/code>\u51fd\u6570\u83b7\u53d6ID\u6570\u636e\uff0c\u5e76\u5c06\u5b83\u4eec\u5b58\u5165\u4e00\u4e2a\u603b\u5217\u8868\u4e2d\u3002\u6700\u540e\uff0c\u4ee3\u7801\u521b\u5efa\u4e86\u4e00\u4e2a\u65b0\u7684Excel\u6587\u4ef6\uff0c\u5c06\u603b\u5217\u8868\u4e2d\u7684ID\u6570\u636e\u5199\u5165\u5176\u4e2d\u3002<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><noscript><img decoding=\"async\" width=\"1024\" height=\"235\" src=\"http:\/\/xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-1024x235.png\" alt class=\"wp-image-1096\" srcset=\"https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-1024x235.png 1024w, https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-300x69.png 300w, https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-768x176.png 768w, https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38.png 1171w\" sizes=\"(max-width: 1024px) 100vw, 1024px\"><\/noscript><img decoding=\"async\" width=\"1024\" height=\"235\" src=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%201024%20235%22%3E%3C%2Fsvg%3E\" alt class=\"wp-image-1096 lazyload\" srcset=\"data:image\/svg+xml,%3Csvg%20xmlns%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2Fsvg%22%20viewBox%3D%220%200%201024%20235%22%3E%3C%2Fsvg%3E 1024w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" data-srcset=\"https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-1024x235.png 1024w, https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-300x69.png 300w, https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-768x176.png 768w, https:\/\/www.xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38.png 1171w\" data-src=\"http:\/\/xinyixx.com\/wp-content\/uploads\/2023\/06\/image-38-1024x235.png\"><\/figure>\n\n\n\n<p>\u8fd9\u91cc\u662f\u904d\u5386\u8d44\u6599\u6587\u4ef6\u5939\u4e0b\u7684\u6240\u6709excel\u6587\u4ef6\u63d0\u51faA\u5217\u4fe1\u606f\u5e76\u8f93\u51fa\u5230ID\u8868\u683c\u91cc\uff0c\u5f97\u5230\u6570\u636e\u3002<\/p>\n\n\n\n<p>\u6709\u6761\u4ef6\u7684\u540c\u5b66\u53ef\u4ee5\u5c1d\u8bd5\u4e00\u4e0b\uff0c\u9700\u8981\u5728\u8d44\u6599\u6587\u4ef6\u5939\u91cc\u9762\u6dfb\u52a0\u4e00\u4e9bexcel\u6587\u6863\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u5bf9\u4e8e\u7528python\u5904\u7406excel\u7684\u6570\u636e\uff0c\u9700\u8981openpyxl\u6a21\u5757\uff0c\u7b80\u5355\u4ecb\u7ecd\u4e00\u4e0b\u3002 openpyxl\u662f\u4e00\u4e2a\u7528\u4e8e\u8bfb [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[14,10],"tags":[71,69,77],"class_list":["post-1095","post","type-post","status-publish","format-standard","hentry","category-teacher","category-coding","tag-python","tag-learning","tag-coding","entry"],"_links":{"self":[{"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/posts\/1095","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/comments?post=1095"}],"version-history":[{"count":0,"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/posts\/1095\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/media?parent=1095"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/categories?post=1095"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xinyixx.com\/index.php\/wp-json\/wp\/v2\/tags?post=1095"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}