{"id":339,"date":"2018-11-13T13:42:50","date_gmt":"2018-11-13T05:42:50","guid":{"rendered":"https:\/\/www.yinyubo.cn\/?p=339"},"modified":"2022-05-17T09:44:58","modified_gmt":"2022-05-17T01:44:58","slug":"outlook","status":"publish","type":"post","link":"https:\/\/www.yinyubo.com\/?p=339","title":{"rendered":"python\u8bfb\u53d6excel\u5185\u5bb9\u518d\u8f6c\u53d8\u6210html\u6dfb\u52a0\u5230outlook\u4e2d"},"content":{"rendered":"<h1><strong>\u9700\u6c42<\/strong><\/h1>\n<p>\u8bfb\u53d6excel\u91cc\u7684\u8868\u683c\u91cc\u7684\u5185\u5bb9\uff0c\u7136\u540e\u6253\u5f00\u672c\u673a\u7684outlook\u3002\u628aexcel\u91cc\u7684\u5185\u5bb9\u6dfb\u52a0\u5230<span style=\"color: #ff0000;\">\u6b63\u6587\u91cc<span style=\"color: #000000;\">\uff0c\u6ce8\u610f\u3002\u8fd9\u91cc\u662f\u8981\u6dfb\u52a0\u5230\u6b63\u6587\uff01\u6b63\u6587\uff01\u6b63\u6587\uff01\u800c\u4e0d\u662f\u6dfb\u52a0\u5230\u9644\u4ef6\u91cc<\/span><\/span><\/p>\n<h1><strong>\u8bbe\u8ba1\u601d\u8def<\/strong><\/h1>\n<h5><span style=\"color: #3366ff;\">1.excel\u5904\u7406<\/span><\/h5>\n<p>\u6253\u5f00excel\u7684\u65b9\u6cd5\u6709\u5f88\u591a\uff0c\u4f46\u662f\u5728\u4e0d\u77e5\u9053excel\u91cc\uff0c\u884c\u548c\u5217\u7684\u5927\u5c0f\u7684\u60c5\u51b5\u4e0b\uff0c\u5c31\u80fd\u83b7\u5f97excel\u91cc\u7684\u975e\u7a7a\u503c\u884c\u5217\u7684\u529e\u6cd5\u4e0d\u591a\u3002\u6211\u8fd9\u8fb9\u91c7\u7528\u7684\u662fxlwings\u8fd9\u4e2a\u5e93\uff0c\u7528\u7684\u65b9\u6cd5\u662frange.current_region\u8fd9\u4e2a\u65b9\u6cd5\u3002\u8fd9\u4e2a\u65b9\u6cd5\u4f1a\u9009\u62e9\u5f53\u524drange\u4e0b\uff0c\u6709\u503c\u7684\u533a\u57df(\u975e\u7a7a\u533a\u57df)<br \/>\n\u901a\u8fc7\u914d\u7f6eoptions\u9009\u9879\uff0c\u53ef\u4ee5\u6307\u5b9aexcel\u83b7\u5f97\u7684\u503c\u7684\u683c\u5f0f\uff0cint\u6216\u8005string\uff0c\u6216\u8005\u7a7a\u503c\u8fd4\u56deN\/A<\/p>\n<h5><span style=\"color: #3366ff;\">2.\u6253\u5f00outlook<\/span><\/h5>\n<p>\u6253\u5f00outlook\u5728windows\u4e0a\u53ea\u80fd\u7528win32\u6a21\u5757\u4e86\uff0c\u901a\u8fc7\u4e0b\u9762\u65b9\u6cd5\u53ef\u4ee5\u6253\u5f00outlook\u5e76\u4e14\u521b\u5efa\u4e00\u4e2a\u7a7a\u7684email<\/p>\n<pre class=\"\"><code class=\"EnlighterJSRAW\" data-enlighter-language=\"golang\">olook = win32com.client.Dispatch(\"Outlook.Application\")\nmail = olook.CreateItem(0)<\/code><\/pre>\n<p>\u7136\u540e\u914d\u7f6e\u90ae\u4ef6\u7684htmlbody\u548coutlook\u90ae\u4ef6\u60ac\u505c(\u53ef\u4ee5\u624b\u52a8\u66f4\u6539\u90ae\u4ef6\u5185\u5bb9\uff0c\u624b\u52a8\u53d1\u9001)\uff0c\u53ef\u4ee5\u7528\u4ee5\u4e0b\u65b9\u6cd5<\/p>\n<pre class=\"\"><code class=\"EnlighterJSRAW\" data-enlighter-language=\"golang\">mail.HTMLBody = body_html\nmail.Display(True)<\/code><\/pre>\n<h1><strong>\u5b8c\u6574\u4ee3\u7801<\/strong><\/h1>\n<p>\u4e0b\u9762\u662f\u5b8c\u6574\u4ee3\u7801\uff0c\u8bfb\u8005\u4fee\u6539\u4e00\u4e0bexcel\u7684\u8def\u5f84\uff0c\u5c31\u53ef\u4ee5\u81ea\u5df1\u53bb\u8bd5\u8bd5\u5566<\/p>\n<pre class=\"lang:python decode:true EnlighterJSRAW\" data-enlighter-language=\"golang\"><code class=\"EnlighterJSRAW\" data-enlighter-language=\"golang\"># -*- coding: UTF-8 -*-\nimport sys\nreload(sys)\nsys.setdefaultencoding('utf-8')\nimport win32com.client\nimport xlwings\ndef get_excel_date(filename):\n    '''\n    \u83b7\u5f97excel\u91cc\u7684\u6240\u6709\u5185\u5bb9\uff0c\u8fd4\u56delist\n    :param filename:  excel\u8def\u5f84\n    :return: list[list[]]\n    '''\n    app = xlwings.App(visible=False, add_book=True)\n    app.display_alerts = False\n    app.screen_updating = False\n    wb = app.books.open(filename)\n    sht = wb.sheets[0]\n    rng = sht.range('A1')\n    # \u628aexcel\u91cc\u7684\u6570\u636e\u8bfb\u53d6\u6210 \u5e74-\u6708-\u65e5 \u65f6:\u5206:\u79d2\u7684\u683c\u5f0f\n    my_date_handler = lambda year, month, day, hour, minute, second, **kwargs: \"%04i-%02i-%02i %02i:%02i:%02i\" % (\n    year, month, day, hour, minute, second)\n    # \u53d6\u51fa\u6240\u6709\u5185\u5bb9,\u8fd9\u91cc\u7528ig\u8fd9\u4e2a\u53d8\u91cf\uff0c\u662f\u4e3a\u4e86\u5e86\u795dI.G\u83b7\u5f97LOL S8\u8d5b\u5b63\u603b\u51a0\u519b\n    ig = rng.current_region.options(index=False, numbers=int, empty='N\/A', dates=my_date_handler)\n    result = ig.value\n    wb.close()\n    app.quit()\n    return result\nif __name__ == '__main__':\n    olook = win32com.client.Dispatch(\"Outlook.Application\")\n    mail = olook.CreateItem(0)\n    mail.Recipients.Add(\"357244849@qq.com\")\n    mail.Subject = \"test report\"\n    body_html = \"\"\n    body_html = body_html + '&lt;body&gt;Hi all:&lt;br\/&gt;\u4ee5\u4e0b\u662fXXXXX\u9879\u76ee\u4eca\u5929\u7684\u6d4b\u8bd5\u60c5\u51b5:&lt;br\/&gt;&lt;br\/&gt;\u660e\u5929\u7684\u6d4b\u8bd5\u8ba1\u5212:&lt;br\/&gt;&lt;br\/&gt;\u76ee\u524d\u7684bug:'\n    body_html = body_html + '&lt;table width=\"1\" border=\"1\" cellspacing=\"1\" cellpadding=\"1\" height=\"100\"&gt;'\n    # \u8fd9\u91cc\u7528rng \u662f\u56e0\u4e3a\u8fd9\u4e00\u6b21rng\u6b62\u6b658\u5f3a\uff01\n    rng_list = get_excel_date(\"C:\\\\lzw_programming\\\\resource\\\\reports\\\\CurrentVersionAllDefectTable.xlsx\")\n    # \u8868\u5934\n    for tr_list in rng_list[:1]:\n        body_html = body_html + \"&lt;tr&gt;\"\n        for td_list in tr_list:\n            # \u8fd9\u91cc\u4e5f\u662f\u5947\u8469\u9700\u6c42\uff0c\u56e0\u4e3a\u8981\u6c42\u8868\u5934\u4e0d\u80fd\u6362\u884c\uff0c\u6240\u4ee5\u7528\u4e86nowrap\n            body_html = body_html + '&lt;th bgcolor=\"#C3C3C3\" nowrap=\"nowrap\"&gt;' + td_list + '&lt;\/th&gt;'\n        body_html = body_html + \"&lt;\/tr&gt;\"\n    # \u8868\u5185\u5bb9\n    for tr_list in rng_list[1:]:\n        body_html = body_html + \"&lt;tr&gt;\"\n        for td_list in tr_list:\n            body_html = body_html + \"&lt;td&gt;\" + td_list + \"&lt;\/td&gt;\"\n        body_html = body_html + \"&lt;\/tr&gt;\"\n    body_html = body_html + '&lt;\/table&gt;'\n    body_html = body_html + \"&lt;\/body&gt;\"\n    mail.HTMLBody = body_html\n    mail.Display(True)<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u9700\u6c42 \u8bfb\u53d6excel\u91cc\u7684\u8868\u683c\u91cc\u7684\u5185\u5bb9\uff0c\u7136\u540e\u6253\u5f00\u672c\u673a\u7684outlook\u3002\u628aexcel\u91cc\u7684\u5185\u5bb9\u6dfb\u52a0\u5230\u6b63\u6587\u91cc\uff0c\u6ce8\u610f\u3002\u8fd9 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":340,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-339","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python"],"_links":{"self":[{"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/posts\/339","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=339"}],"version-history":[{"count":1,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/posts\/339\/revisions"}],"predecessor-version":[{"id":937,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/posts\/339\/revisions\/937"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=\/wp\/v2\/media\/340"}],"wp:attachment":[{"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=339"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yinyubo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}