将值写入python中pandas中工作表中的特定单元格

时间:2022-07-25 15:36:45

I have an excel sheet, which already has some values in some cells.

我有一张excel表,在某些单元格中已有一些值。

For ex :-

例如: -

        A      B      C      D
1      val1   val2          val3
2             valx   valy        

I want pandas to write to specific cells without touching any other cells,sheet etc

我想要大熊猫写入特定的细胞而不接触任何其他细胞,薄片等

This is the code i tried.

这是我试过的代码。

import pandas as pd
from openpyxl import load_workbook

df2 = pd.DataFrame({'Data': [13, 24, 35, 46]})
book = load_workbook('b.xlsx')
writer = pd.ExcelWriter('b.xlsx', engine='openpyxl')

df2.to_excel(writer, "Sheet1", startcol=7,startrow=6)

writer.save()

However this code deletes the older cell values.

但是,此代码会删除较旧的单元格值。

I have reffered to :- How to write to an existing excel file without overwriting data (using pandas)? but this solution does not work.

我已经提到: - 如何写入现有的excel文件而不覆盖数据(使用pandas)?但是这个解决方案不起作用。

2 个解决方案

#1


4  

UPDATE2: appending data to existing Excel sheet, preserving other (old) sheets:

UPDATE2:将数据附加到现有Excel工作表,保留其他(旧)工作表:

import pandas as pd
from openpyxl import load_workbook

fn = r'C:\Temp\.data\doc.xlsx'

df = pd.read_excel(fn, header=None)
df2 = pd.DataFrame({'Data': [13, 24, 35, 46]})

writer = pd.ExcelWriter(fn, engine='openpyxl')
book = load_workbook(fn)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, sheet_name='Sheet1', header=None, index=False)
df2.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
             startcol=7,startrow=6)

writer.save()

UPDATE: your Excel file doesn't have a header, so you should process it accordingly:

更新:您的Excel文件没有标题,因此您应该相应地处理它:

In [57]: df = pd.read_excel(fn, header=None)

In [58]: df
Out[58]:
     0    1
0  abc  def
1  ghi  lmn

In [59]: df2
Out[59]:
   Data
0    13
1    24
2    35
3    46

In [60]: writer = pd.ExcelWriter(fn)

In [61]: df.to_excel(writer, header=None, index=False)

In [62]: df2.to_excel(writer, startcol=7,startrow=6, header=None, index=False)

In [63]: writer.save()

将值写入python中pandas中工作表中的特定单元格

OLD answer:

老答案:

You can use the following trick:

您可以使用以下技巧:

first read the existing contents of the excel file into a new DF:

首先将excel文件的现有内容读入新DF:

In [17]: fn = r'C:\Temp\b.xlsx'

In [18]: df = pd.read_excel(fn)

In [19]: df
Out[19]:
       A      B     C      D
0   val1    NaN  val3   val4
1  val11  val22   NaN  val33

now we can write it back and append a new DF2:

现在我们可以把它写回来并添加一个新的DF2:

In [20]: writer = pd.ExcelWriter(fn)

In [21]: df.to_excel(writer, index=False)

In [22]: df2.to_excel(writer, startcol=7,startrow=6, header=None)

In [23]: writer.save()

将值写入python中pandas中工作表中的特定单元格

#2


1  

I was not able to do what was asked by me in the question by using pandas, but was able to solve it by using Openpyxl.

我无法通过使用pandas来完成我在问题中提出的问题,但是能够通过使用Openpyxl来解决它。

I will write few code snippets which would help in achieving what was asked.

我将编写一些代码片段,这些代码片段有助于实现所要求的内容。

import openpyxl

srcfile = openpyxl.load_workbook('docname.xlsx',read_only=False, keep_vba= True)#to open the excel sheet and if it has macros

sheetname = srcfile.get_sheet_by_name('sheetname')#get sheetname from the file
sheetname['B2']= str('write something') #write something in B2 cell of the supplied sheet
sheetname.cell(row=1,column=1).value = "something" #write to row 1,col 1 explicitly, this type of writing is useful to write something in loops

srcfile.save('newfile.xlsm')#save it as a new file, the original file is untouched and here I am saving it as xlsm(m here denotes macros).

So Openpyxl writes to a purticular cell, without touching the other sheets,cells etc. It basically writes to a new file respecting the properties of the original file

因此,Openpyxl写入一个特殊细胞,而不接触其他床单,细胞等。它基本上写入一个尊重原始文件属性的新文件

#1


4  

UPDATE2: appending data to existing Excel sheet, preserving other (old) sheets:

UPDATE2:将数据附加到现有Excel工作表,保留其他(旧)工作表:

import pandas as pd
from openpyxl import load_workbook

fn = r'C:\Temp\.data\doc.xlsx'

df = pd.read_excel(fn, header=None)
df2 = pd.DataFrame({'Data': [13, 24, 35, 46]})

writer = pd.ExcelWriter(fn, engine='openpyxl')
book = load_workbook(fn)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, sheet_name='Sheet1', header=None, index=False)
df2.to_excel(writer, sheet_name='Sheet1', header=None, index=False,
             startcol=7,startrow=6)

writer.save()

UPDATE: your Excel file doesn't have a header, so you should process it accordingly:

更新:您的Excel文件没有标题,因此您应该相应地处理它:

In [57]: df = pd.read_excel(fn, header=None)

In [58]: df
Out[58]:
     0    1
0  abc  def
1  ghi  lmn

In [59]: df2
Out[59]:
   Data
0    13
1    24
2    35
3    46

In [60]: writer = pd.ExcelWriter(fn)

In [61]: df.to_excel(writer, header=None, index=False)

In [62]: df2.to_excel(writer, startcol=7,startrow=6, header=None, index=False)

In [63]: writer.save()

将值写入python中pandas中工作表中的特定单元格

OLD answer:

老答案:

You can use the following trick:

您可以使用以下技巧:

first read the existing contents of the excel file into a new DF:

首先将excel文件的现有内容读入新DF:

In [17]: fn = r'C:\Temp\b.xlsx'

In [18]: df = pd.read_excel(fn)

In [19]: df
Out[19]:
       A      B     C      D
0   val1    NaN  val3   val4
1  val11  val22   NaN  val33

now we can write it back and append a new DF2:

现在我们可以把它写回来并添加一个新的DF2:

In [20]: writer = pd.ExcelWriter(fn)

In [21]: df.to_excel(writer, index=False)

In [22]: df2.to_excel(writer, startcol=7,startrow=6, header=None)

In [23]: writer.save()

将值写入python中pandas中工作表中的特定单元格

#2


1  

I was not able to do what was asked by me in the question by using pandas, but was able to solve it by using Openpyxl.

我无法通过使用pandas来完成我在问题中提出的问题,但是能够通过使用Openpyxl来解决它。

I will write few code snippets which would help in achieving what was asked.

我将编写一些代码片段,这些代码片段有助于实现所要求的内容。

import openpyxl

srcfile = openpyxl.load_workbook('docname.xlsx',read_only=False, keep_vba= True)#to open the excel sheet and if it has macros

sheetname = srcfile.get_sheet_by_name('sheetname')#get sheetname from the file
sheetname['B2']= str('write something') #write something in B2 cell of the supplied sheet
sheetname.cell(row=1,column=1).value = "something" #write to row 1,col 1 explicitly, this type of writing is useful to write something in loops

srcfile.save('newfile.xlsm')#save it as a new file, the original file is untouched and here I am saving it as xlsm(m here denotes macros).

So Openpyxl writes to a purticular cell, without touching the other sheets,cells etc. It basically writes to a new file respecting the properties of the original file

因此,Openpyxl写入一个特殊细胞,而不接触其他床单,细胞等。它基本上写入一个尊重原始文件属性的新文件