使用python写入现有的Excel文件 [英] Writing into existing excel file using python

问题描述

我有一个.xlsx文件，其中有多个工作表(有一些内容).我想将数据写入特定的工作表，例如sheet1和sheet5.现在，我正在使用xlrd，xlwt和xlutils copy()函数进行此操作.但是，有什么方法可以通过在追加模式下打开文件并添加数据并保存来实现(就像我们在处理text/csv文件时一样)?

I have a .xlsx file in which multiple worksheets are there(with some content). I want to write some data into specific sheets say sheet1 and sheet5. Right now i am doing it using xlrd, xlwt and xlutils copy() function. But is there any way to do it by opening the file in append mode and adding the data and save it(Like as we do it for the text/csv files)?

这是我的代码:

rb = open_workbook("C:\text.xlsx",formatting_info='True')
wb = copy(rb)
Sheet1 = wb.get_sheet(8)
Sheet2 = wb.get_sheet(7)
Sheet1.write(0,8,'Obtained_Value')
Sheet2.write(0,8,'Obtained_Value')
value1 = [1,2,3,4]  
value2 = [5,6,7,8]
for i in range(len(value1)):
 Sheet1.write(i+1,8,value1[i])
for j in range(len(value2)):
 Sheet2.write(j+1,8,value2[j])
wb.save("C:\text.xlsx")

谢谢

推荐答案

您可以使用openpyxl模块或xlwings模块

You can do it using the openpyxl module or using the xlwings module

1. 使用openpyxl

from openpyxl import workbook #pip install openpyxl
from openpyxl import load_workbook

wb = load_workbook("C:\text.xlsx")
sheets = wb.sheetnames
Sheet1 = wb[sheets[8]]
Sheet2 = wb[sheets[7]]
#Then update as you want it
Sheet1 .cell(row = 2, column = 4).value = 5 #This will change the cell(2,4) to 4
wb.save("HERE PUT THE NEW EXCEL PATH") 

text.xlsx文件将用作模板，text.xlsx文件中的所有值以及更新后的值将保存在新文件中

the text.xlsx file will be used as a template, all the values from text.xlsx file together with the updated values will be saved in the new file

使用xlwings

 import xlwings 
 wb = xlwings.Book("C:\text.xlsx")  
 Sheet1 = wb.sheets[8]
 Sheet2 = wb.sheets[7]
 #Then update as you want it
 Sheet1.range(2, 4).value = 4 #This will change the cell(2,4) to 4
 wb.save()
 wb.close()

此处文件将在text.xlsx文件中更新，但是如果您想获得该文件的副本，则可以使用下面的代码

Here the file will be updated in the text.xlsx file but if you want to have a copy of the file you can use the code below

shutil.copy("C:\text.xlsx", "C:\newFile.xlsx") #copies text.xslx file to newFile.xslx

并使用

wb = xlwings.Book("C:\newFile.xlsx") instead of wb = xlwings.Book("C:\text.xlsx")

作为两个模块的用户，与第一个模块相比，我更喜欢第二个模块.

这篇关于使用python写入现有的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！