使用Pandas修改Excel文件，而对布局的更改最少 [英] Modify an Excel file with Pandas, with minimal change of the layout

问题描述

我已经阅读了

(2)而不修改显示列宽".Excel中显示的列数

(3)而不删除某些单元格上可能存在的公式

?

这是我尝试的示例，这是一个简短的示例(实际上，我对Pandas进行了更多处理):

 将pandas导入为pddf = pd.read_excel('in.xlsx')df ['AB'] = df ['A'].astype(str)+''+ df ['B'].astype(str)#从另外2个创建新列del df ['Date']#删除列del df ['Time']df.to_excel('out.xlsx'，index = False) 

使用此代码，将删除第一行的 Filter 并将显示的列宽设置为默认值，该值不是很方便(因为我们必须手动设置正确的宽度所有列).

解决方案

如果您使用的计算机上安装了Excel，那么我强烈建议您使用灵活的

app.py

 将xlwings导入为xw#pip install xlwings将熊猫作为pd导入wb = xw.Book('demo.xlsx') 

这将创建一个启动xl工作簿实例并打开Excel编辑器以允许您调用Python命令.

---

假设我们要使用以下数据框替换ID和名称"列:

  new_name约翰新B亚当斯_新C Mo_newD Safia_newwb.sheets ['Sheet1'] ['A1:B1'].value = df 

---

最后，您可以保存并关闭.

  wb.save()wb.close() 

I've already read Can Pandas read and modify a single Excel file worksheet (tab) without modifying the rest of the file? but here my question is specific to the layout mentioned hereafter.

How to open an Excel file with Pandas, do some modifications, and save it back:

• (1) without removing that there is a Filter on the first row

• (2) without modifying the "displayed column width" of the columns as displayed in Excel

• (3) without removing the formulas which might be present on some cells

?

Here is what I tried, it's a short example (in reality I do more processing with Pandas):

import pandas as pd

df = pd.read_excel('in.xlsx')
df['AB'] = df['A'].astype(str) + ' ' + df['B'].astype(str)  # create a new column from 2 others
del df['Date']                                              # delete columns
del df['Time']
df.to_excel('out.xlsx', index=False)

With this code, the Filter of the first row is removed and the displayed column width are set to a default, which is not very handy (because we would have to manually set the correct width for all columns).

解决方案

If you are using a machine that has Excel installed on it, then I highly recommend using the flexible xlwings API. This answers all your questions.

Let's assume I have an Excel file called demo.xlxs in the same directory as my program.

app.py

import xlwings as xw # pip install xlwings
import pandas as pd

wb = xw.Book('demo.xlsx')

This will create a initiate an xl workbook instance and open your Excel editor to allow you to invoke Python commands.

---

Let's assume we have the following dataframe that we want to use to replace the ID and Name column:

    new_name
A   John_new
B  Adams_new
C     Mo_new
D  Safia_new

wb.sheets['Sheet1']['A1:B1'].value = df

---

Finally, you can save and close.

wb.save()
wb.close()

这篇关于使用Pandas修改Excel文件，而对布局的更改最少的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！