<p>因为OP使用的是<code>openpyxl</code>模块,所以我想展示一种使用该模块的方法。有了这个答案,我演示了一种将原始数据移动到新的列和行坐标的方法(可能有更好的方法)。</p>
<p>这个完全可复制的示例首先创建一个名为“test.xlsx”的工作簿,用于演示,其中有三个工作表名为“test_1”、“test_2”和“test_3”。然后使用<code>openpyxl</code>,它将“test_2”复制到一个名为“new.xlsx”的新工作簿中,将单元格移到4列上,移到3列下。它利用了<code>ord()</code>和<code>chr()</code>函数。</p>
<pre><code>import pandas as pd
import numpy as np
import openpyxl
# This section is sample code that creates a worbook in the current directory with 3 worksheets
df = pd.DataFrame(np.random.randn(10, 3), columns=list('ABC'))
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='test_1', index=False)
df.to_excel(writer, sheet_name='test_2', index=False)
df.to_excel(writer, sheet_name='test_3', index=False)
wb = writer.book
ws = writer.sheets['test_2']
writer.close()
# End of sample code that creates a worbook in the current directory with 3 worksheets
wb = openpyxl.load_workbook('test.xlsx')
ws_name_wanted = "test_2"
list_all_ws = wb.get_sheet_names()
for item in list_all_ws:
if item != ws_name_wanted:
remove = wb.get_sheet_by_name(item)
wb.remove_sheet(remove)
ws = wb['%s' % (ws_name_wanted)]
for row in ws.iter_rows():
for cell in row:
cell_value = cell.value
new_col_loc = (chr(int(ord(cell.coordinate[0:1])) + 4))
new_row_loc = cell.coordinate[1:]
ws['%s%d' % (new_col_loc ,int(new_row_loc) + 3)] = cell_value
ws['%s' % (cell.coordinate)] = ' '
wb.save("new.xlsx")
</code></pre>
<p><strong>下面是“test.xlsx”的外观:</strong></p>
<p><a href="https://i.stack.imgur.com/2SSvE.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2SSvE.jpg" alt="Expected Output of test.xlsx"/></a></p>
<p><strong>下面是“new.xlsx”的外观:</strong></p>
<p><a href="https://i.stack.imgur.com/aT0DZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aT0DZ.jpg" alt="Expected Output of new.xlsx"/></a></p>