{"id":1714,"date":"2020-06-22T17:40:01","date_gmt":"2020-06-22T09:40:01","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1714"},"modified":"2020-06-22T17:40:01","modified_gmt":"2020-06-22T09:40:01","slug":"%e4%bd%bf%e7%94%a8-python-%e7%9a%84-openpyxl-%e8%af%bb%e5%8f%96-excel","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1714","title":{"rendered":"\u4f7f\u7528 python \u7684 openpyxl \u8bfb\u53d6 Excel"},"content":{"rendered":"
\n
\n
\n
Python \u7684 openpyxl \u6a21\u5757\u53ef\u4ee5\u8ba9\u6211\u4eec\u80fd\u8bfb\u53d6\u548c\u4fee\u6539 Excel \u6587\u4ef6\u3002<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\u9996\u5148\u8ba9\u6211\u4eec\u5148\u7406\u89e3\u4e00\u4e9b Excel \u57fa\u7840\u6982\u5ff5\u3002<\/p>\n

1 Excel \u57fa\u7840\u6982\u5ff5<\/h2>\n

Excel \u6587\u4ef6\u4e5f\u79f0\u505a\u4e3a\u5de5\u4f5c\u7c3f\u3002\u6bcf\u4e2a\u5de5\u4f5c\u7c3f\u53ef\u4ee5\u5305\u542b\u591a\u4e2a\u5de5\u4f5c\u8868(Sheet)\u3002\u7528\u6237\u5f53\u524d\u67e5\u770b\u7684\u8868\u6216\u5173\u95ed Excel \u524d\u6700\u540e\u67e5\u770b\u7684\u8868\uff0c\u79f0\u4e3a\u6d3b\u52a8\u8868\u3002
\n\u6bcf\u4e00\u5f20\u8868\u90fd\u662f\u7531\u5217\u548c\u884c\u6784\u6210\u7684\u3002\u5217\u662f\u4ee5 A \u5f00\u59cb\u7684\u5b57\u6bcd\u8868\u793a\uff1b\u800c\u884c\u662f\u4ee5 1 \u5f00\u59cb\u7684\u6570\u5b57\u8868\u793a\u7684\u3002\u7531\u7279\u5b9a\u884c\u548c\u5217\u6240\u6307\u5b9a\u7684\u65b9\u683c\u79f0\u4e3a\u5355\u5143\u683c\u3002\u6bcf\u4e2a\u5355\u5143\u683c\u90fd\u53ef\u4ee5\u5305\u542b\u4e00\u4e2a\u6570\u5b57\u6216\u6587\u672c\u3002\u8fd9\u4e9b\u5355\u5143\u683c\u5c31\u6784\u6210\u4e86\u8fd9\u5f20\u8868\u3002<\/p>\n

2 \u5b89\u88c5 openpyxl<\/h2>\n

\u901a\u8fc7 pip \u5c31\u53ef\u4ee5\u5b89\u88c5\u6700\u65b0\u7248\u7684 openpyxl\u3002<\/p>\n

\n
pip install openpyxl\n<\/pre>\n<\/div>\n
3 \u8bfb\u53d6 Excel<\/h2>\n
\u5047\u8bbe\u6709\u8fd9\u6837\u4e00\u4efd\u4e16\u754c\u4eba\u53e3\u7edf\u8ba1 Excel \u6587\u6863\uff0c\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n
\n
\n
<\/div>\n
<\/div>\n<\/div>\n<\/div>\n
3.1 \u52a0\u8f7d Excel<\/h3>\n
\n
wb = openpyxl.load_workbook(\\'population.xlsx\\')\nprint(\\'wb \u7c7b\u578b :\\')\nprint(type(wb), \\'\\\\n\\')\n<\/pre>\n<\/div>\n
\u8fd0\u884c\u7ed3\u679c\uff1a<\/p>\n
wb \u7c7b\u578b :
\n<class \\’openpyxl.workbook.workbook.Workbook\\’><\/p><\/blockquote>\n
\u5bfc\u5165 openpyxl \u6a21\u5757\u4e4b\u540e\uff0c\u5c31\u53ef\u4ee5\u4f7f\u7528 openpyxl.load_workbook() \u51fd\u6570\u6765\u52a0\u8f7d Excel \u6587\u6863\u54af\u3002\u8fd9\u4e2a Excel \u6587\u4ef6\u8868\u793a\u4e3a Workbook \u5bf9\u8c61\u3002
\n\u6ce8\u610f\uff1a load_workbook() \u51fd\u6570\u4e2d\u7684\u6587\u4ef6\uff0c\u5fc5\u987b\u5728\u5f53\u524d\u5de5\u4f5c\u76ee\u5f55\u3002\u53ef\u4ee5\u5bfc\u5165 os \u6a21\u5757\uff0c\u5229\u7528 os.getcwd() \u6765\u83b7\u6089\u5f53\u524d\u5de5\u4f5c\u76ee\u5f55\u3002os \u8fd8\u63d0\u4f9b\u4e86 chdir() \u65b9\u6cd5\uff0c\u53ef\u4ee5\u6539\u53d8\u5f53\u524d\u5de5\u4f5c\u76ee\u5f55\u3002<\/p>\n
\n
import os\nprint(\\'\u5f53\u524d\u5de5\u4f5c\u76ee\u5f55 :\\')\nprint(os.getcwd(), \\'\\\\n\\')\n<\/pre>\n<\/div>\n
\u8fd0\u884c\u7ed3\u679c\uff1a<\/p>\n
\n
\n
<\/div>\n
<\/div>\n<\/div>\n<\/div>\n
3.2 \u8bfb\u53d6 Excel \u5de5\u4f5c\u8868\uff08sheet\uff09<\/h3>\n
\n
...\nprint(\\'\u53d6\u5f97\u6240\u6709\u5de5\u4f5c\u8868\u7684\u8868\u540d :\\')\nprint(wb.sheetnames, \\'\\\\n\\')\nprint(\\'\u53d6\u5f97\u67d0\u5f20\u5de5\u4f5c\u8868 :\\')\nsheet = wb[\\'Sheet3\\']\nprint(type(sheet))\nprint(\\'\u8868\u540d - \\' + sheet.title, \\'\\\\n\\')\nprint(\\'\u53d6\u5f97\u6d3b\u52a8\u5de5\u4f5c\u8868 :\\')\nactive_sheet = wb.active\nprint(\\'\u8868\u540d - \\' + active_sheet.title, \\'\\\\n\\')\n<\/pre>\n<\/div>\n
\u8fd0\u884c\u7ed3\u679c\uff1a<\/p>\n
\u53d6\u5f97\u6240\u6709\u5de5\u4f5c\u8868\u7684\u8868\u540d :
\n[\\’Sheet1\\’, \\’Sheet2\\’, \\’Sheet3\\’]<\/p><\/blockquote>\n
\u53d6\u5f97\u67d0\u5f20\u5de5\u4f5c\u8868 :
\n<class \\’openpyxl.worksheet.worksheet.Worksheet\\’>
\n\u8868\u540d – Sheet3<\/p><\/blockquote>\n
\u53d6\u5f97\u6d3b\u52a8\u5de5\u4f5c\u8868 :
\n\u8868\u540d – Sheet1<\/p><\/blockquote>\n