{"id":2280,"date":"2023-08-22T22:54:54","date_gmt":"2023-08-22T14:54:54","guid":{"rendered":"http:\/\/viplao.com\/?p=2280"},"modified":"2023-08-22T22:54:55","modified_gmt":"2023-08-22T14:54:55","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-%e5%88%86%e7%b1%bb%e6%b1%87%e6%80%bb","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-%e5%88%86%e7%b1%bb%e6%b1%87%e6%80%bb\/","title":{"rendered":"Python \u65e5\u5e38\u6570\u636e\u5206\u6790 \u2013 EXCEL\u5b9e\u6218- \u5206\u7c7b\u6c47\u603b"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>import xlwings as xw\r\nimport pandas as pd\r\napp = xw.App(visible=False, add_book=False)\r\nworkbook = app.books.open('\u6587\u4ef6\u540d.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=&#91;'\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&#91;'A1'].options(index=False).value = group\r\n    last_cell = new_worksheet&#91;'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&#91;sum_cell_name].formula = formula\r\n    new_worksheet.autofit()\r\nnew_workbook.save('\u6587\u4ef6\u540d.xlsx')\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":627,"_links":{"self":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2280"}],"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=2280"}],"version-history":[{"count":1,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2280\/revisions"}],"predecessor-version":[{"id":2281,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2280\/revisions\/2281"}],"wp:attachment":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/media?parent=2280"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/categories?post=2280"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/tags?post=2280"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}