英文:
Read an excel file with Python and modify it without changing the formating and the style of the file
问题
我有一个包含注释和颜色的Excel文件。
我正在使用Python和pandas和openpyxl库来读取文件并添加一个新的行。
代码运行完毕后，我发现文件完全改变了，好像删除了所有的格式和样式。
我希望添加这一行时不对文件的格式和样式进行任何更改。
我需要做什么来防止这种情况发生？
我需要使用其他库吗？如果是这样，我应该使用哪个库？
我尝试了xlsxWriter库，但它对我没有起作用。
英文:
I have an excel file which has some cells with comments and colors.
I'm using python and the pandas and openpyxl libraries to read the file and adding a new row with details to it.
After the code finished to run I saw the file changed completely, like it deleted all the formating and the style of the file.
I want it to add the row but without making any change to the file formating and style.
What i need to do to prevent it to do so?
Do I need to use other library? and if that is the case which library should i use?
I tried the xlsxWriter library but it didnt work for me.
答案1
得分: 1
你可以尝试使用pandas.ExcelWriter。示例代码如下：
现有文件 existing_file.xlsx：
代码：
import pandas as pd
# 读取现有的Excel文件
df = pd.read_excel('existing_file.xlsx')
# 创建一个包含新行的新数据框
new_row = pd.DataFrame({'key': ['black'], 'value': ['2']})
df = pd.concat([df, new_row], ignore_index=True)
# 使用ExcelWriter将数据框写入现有的Excel文件
with pd.ExcelWriter('existing_file.xlsx', mode='a', if_sheet_exists='overlay') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
现有文件 existing_file.xlsx：
pandas版本：pandas==2.0.3
编辑：删除行（根据评论中的要求）
# 读取现有的Excel文件
df = pd.read_excel('existing_file.xlsx')
# 删除值为'black'的行
df = df.drop(df[df['key'] == 'green'].index)
# 使用ExcelWriter将更新后的数据框写入Excel文件
with pd.ExcelWriter('existing_file.xlsx', mode='w') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
结果：
编辑2：另一种删除行的方法
之前：
import openpyxl
path = 'existing_file.xlsx'
wb = openpyxl.load_workbook(path)
sheet = wb.active
sheet.delete_rows(idx=3)
path1 = 'final.xlsx'
wb.save(path1)
之后：
英文:
You could try the pandas.ExcelWriter. Example:
existing_file.xlsx file:
Code:
import pandas as pd
# Read the existing Excel file
df = pd.read_excel('existing_file.xlsx')
# Create a new dataframe with the new row
new_row = pd.DataFrame({'key': ['black'], 'value': ['2']})
df = pd.concat([df, new_row], ignore_index=True)
# Write the dataframe to the existing Excel file using ExcelWriter
with pd.ExcelWriter('existing_file.xlsx', mode='a', if_sheet_exists='overlay') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
existing_file.xlsx file:
pandas version: pandas==2.0.3
EDIT: Removing the row (requested in comments)
# Read the existing Excel file
df = pd.read_excel('existing_file.xlsx')
# Remove the row with the value 'black'
df = df.drop(df[df['key'] == 'green'].index)
# Write the updated DataFrame to the Excel file using ExcelWriter
with pd.ExcelWriter('existing_file.xlsx', mode='w') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
Result:
EDIT2: Another way of removing the row
Before:
import openpyxl
path = 'existing_file.xlsx'
wb = openpyxl.load_workbook(path)
sheet = wb.active
sheet.delete_rows(idx=3)
path1 = 'final.xlsx'
wb.save(path1)
After:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论