{"id":842,"date":"2022-09-18T22:25:24","date_gmt":"2022-09-18T14:25:24","guid":{"rendered":"http:\/\/viplao.com\/?p=842"},"modified":"2022-09-18T22:25:49","modified_gmt":"2022-09-18T14:25:49","slug":"%e8%bf%90%e7%bb%b4%e5%b7%a5%e5%85%b7-%e5%bc%82%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90%ef%bc%8c%e6%89%b9%e9%87%8f%e6%8b%86%e5%88%86%e5%88%97%e6%95%b0%e6%8d%ae%e6%88%96%e6%89%b9%e9%87%8f","status":"publish","type":"post","link":"http:\/\/viplao.com\/index.php\/2022\/09\/18\/%e8%bf%90%e7%bb%b4%e5%b7%a5%e5%85%b7-%e5%bc%82%e5%b8%b8%e6%95%b0%e6%8d%ae%e5%88%86%e6%9e%90%ef%bc%8c%e6%89%b9%e9%87%8f%e6%8b%86%e5%88%86%e5%88%97%e6%95%b0%e6%8d%ae%e6%88%96%e6%89%b9%e9%87%8f\/","title":{"rendered":"\u8fd0\u7ef4\u5de5\u5177 \u2013 \u5f02\u5e38\u6570\u636e\u5206\u6790\u5b9e\u8df5\uff0c\u6279\u91cf\u62c6\u5206\u5217\u6570\u636e\u6216\u6279\u91cf\u6c47\u603b\u6570\u636e"},"content":{"rendered":"\n

Excel\u7684\u5206\u5217\u529f\u80fd\u53ef\u4ee5\u5c06\u4e00\u5217\u6570\u636e\u6309\u7167\u6307\u5b9a\u7684\u5206\u9694\u7b26\u62c6\u5206\u4e3a\u591a\u5217\uff0c\u672c\u8282\u5219\u8981\u5229\u7528Python\u7f16\u7a0b\u5b9e\u73b0\u7c7b\u4f3c\u7684\u529f\u80fd\uff0c\u5bf9\u591a\u4e2a\u5de5\u4f5c\u7c3f\u4e2d\u7684\u5217\u8fdb\u884c\u62c6\u5206\u3002<\/p>\n\n\n\n

from pathlib import Path\r\nimport xlwings as xw\r\nimport pandas as pd\r\nsrc_folder = Path('\\\\\u6bcf\u6708\u7edf\u8ba1\u8868\\\\')\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['Sheet1']\r\n    data = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value\r\n    new_data = data['\u4ea7\u54c1\u5c3a\u5bf8\uff08mm\uff09'].str.split('*', expand=True)\r\n    new_data.columns = ['\u957f\uff08mm\uff09', '\u5bbd\uff08mm\uff09', '\u9ad8\uff08mm\uff09']\r\n    for j in range(new_data.shape[1] - 1):\r\n        worksheet['F:F'].insert(shift='right', copy_origin='format_from_left_or_above')\r\n    worksheet['F1'].options(index=False).value = new_data\r\n    worksheet.autofit()\r\n    workbook.save()\r\n    workbook.close()\r\napp.quit()\r\n<\/code><\/pre>\n\n\n\n
Excel\u7684\u5206\u5217\u529f\u80fd\u53ef\u4ee5\u5c06\u4e00\u5217\u6570\u636e\u6309\u7167\u6307\u5b9a\u7684\u5206\u9694\u7b26\u62c6\u5206\u4e3a\u591a\u5217\uff0c\u672c\u8282\u5219\u8981\u5229\u7528Python\u7f16\u7a0b\u5b9e\u73b0\u7c7b\u4f3c\u7684\u529f\u80fd\uff0c\u5bf9\u591a\u4e2a\u5de5\u4f5c\u7c3f\u4e2d\u7684\u5217\u8fdb\u884c\u62c6\u5206\u3002<\/p>\n\n\n\n
import xlwings as xw\r\nimport pandas as pd\r\napp = xw.App(visible=False, add_book=False)\r\nworkbook = app.books.open('\\\\\u4ea7\u54c1\u9500\u552e\u7edf\u8ba1\u8868.xlsx')\r\nworksheets = workbook.sheets\r\ntable = pd.DataFrame()\r\nfor i, j in enumerate(worksheets):\r\n    data = j.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value\r\n    data = data.reindex(columns=['\u5355\u53f7', '\u9500\u552e\u65e5\u671f', '\u4ea7\u54c1\u540d\u79f0', '\u6210\u672c\u4ef7\uff08\u5143\/\u4e2a\uff09', '\u9500\u552e\u4ef7\uff08\u5143\/\u4e2a\uff09', '\u9500\u552e\u6570\u91cf\uff08\u4e2a\uff09', '\u4ea7\u54c1\u6210\u672c\uff08\u5143\uff09', '\u9500\u552e\u6536\u5165\uff08\u5143\uff09', '\u9500\u552e\u5229\u6da6\uff08\u5143\uff09'])\r\n    table = table.append(data, ignore_index=True)\r\ntable = table.groupby('\u4ea7\u54c1\u540d\u79f0')\r\nnew_workbook = xw.books.add()\r\nfor idx, group in table:\r\n    new_worksheet = new_workbook.sheets.add(idx)\r\n    new_worksheet['A1'].options(index=False).value = group\r\n    last_cell = new_worksheet['A1'].expand('table').last_cell\r\n    last_row = last_cell.row\r\n    last_column = last_cell.column\r\n    last_column_letter = chr(64 + last_column)\r\n    sum_cell_name = f'{last_column_letter}{last_row + 1}'\r\n    sum_last_row_name = f'{last_column_letter}{last_row}'\r\n    formula = f'=SUM({last_column_letter}2:{sum_last_row_name})'\r\n    new_worksheet[sum_cell_name].formula = formula\r\n    new_worksheet.autofit()\r\nnew_workbook.save('\\\\\u4ea7\u54c1\u9500\u552e\u7edf\u8ba1\u8868\uff08\u5df2\u6c47\u603b\uff09.xlsx')\r\napp.quit()<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Excel\u7684\u5206\u5217\u529f\u80fd\u53ef\u4ee5\u5c06\u4e00\u5217\u6570\u636e\u6309\u7167\u6307\u5b9a\u7684\u5206\u9694\u7b26\u62c6\u5206\u4e3a\u591a\u5217\uff0c\u672c\u8282\u5219\u8981\u5229\u7528Python\u7f16\u7a0b\u5b9e\u73b0\u7c7b\u4f3c\u7684… \u7ee7\u7eed\u9605\u8bfb \u8fd0\u7ef4\u5de5\u5177 \u2013 \u5f02\u5e38\u6570\u636e\u5206\u6790\u5b9e\u8df5\uff0c\u6279\u91cf\u62c6\u5206\u5217\u6570\u636e\u6216\u6279\u91cf\u6c47\u603b\u6570\u636e<\/span><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"views":401,"_links":{"self":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/842"}],"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=842"}],"version-history":[{"count":2,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/842\/revisions"}],"predecessor-version":[{"id":844,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/842\/revisions\/844"}],"wp:attachment":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/media?parent=842"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/categories?post=842"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/tags?post=842"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}