{"id":2278,"date":"2023-08-22T22:53:47","date_gmt":"2023-08-22T14:53:47","guid":{"rendered":"http:\/\/viplao.com\/?p=2278"},"modified":"2023-08-22T22:53:49","modified_gmt":"2023-08-22T14:53:49","slug":"python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%ae%9e%e6%88%98-%e6%89%b9%e9%87%8f%e6%8b%86%e5%88%86%e5%88%97","status":"publish","type":"post","link":"http:\/\/viplao.com\/index.php\/2023\/08\/22\/python-%e6%97%a5%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90-excel%e5%ae%9e%e6%88%98-%e6%89%b9%e9%87%8f%e6%8b%86%e5%88%86%e5%88%97\/","title":{"rendered":"Python \u65e5\u5e38\u6570\u636e\u5206\u6790 \u2013 EXCEL\u5b9e\u6218- \u6279\u91cf\u62c6\u5206\u5217"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>from pathlib import Path\r\nimport xlwings as xw\r\nimport pandas as pd\r\nsrc_folder = Path('\u6587\u4ef6\u5939')\r\nfile_list = list(src_folder.glob('*.xlsx'))\r\napp = xw.App(visible=False, add_book=False)\r\nfor i in file_list:\r\n    if i.name.startswith('~$'):\r\n        continue\r\n    workbook = app.books.open(i)\r\n    worksheet = workbook.sheets&#91;'Sheet1']\r\n    data = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value\r\n    new_data = data&#91;'\u4ea7\u54c1\u5c3a\u5bf8\uff08mm\uff09'].str.split('*', expand=True)\r\n    new_data.columns = &#91;'\u957f\uff08mm\uff09', '\u5bbd\uff08mm\uff09', '\u9ad8\uff08mm\uff09']\r\n    for j in range(new_data.shape&#91;1] - 1):\r\n        worksheet&#91;'F:F'].insert(shift='right', copy_origin='format_from_left_or_above')\r\n    worksheet&#91;'F1'].options(index=False).value = new_data\r\n    worksheet.autofit()\r\n    workbook.save()\r\n    workbook.close()\r\napp.quit()<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[28],"views":1005,"_links":{"self":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2278"}],"collection":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/comments?post=2278"}],"version-history":[{"count":1,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2278\/revisions"}],"predecessor-version":[{"id":2279,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2278\/revisions\/2279"}],"wp:attachment":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/media?parent=2278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/categories?post=2278"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/tags?post=2278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}