Python:根据先前单元格中的公式自动填充Excel中的单元格 [英] Python: Autofill cells in Excel according to a formula in previous cells

问题描述

使用Python和win32com:

Using Python and win32com:

我在Excel文件中的单元格A5:A54中有一个公式.我试图根据相同的公式用值填充单元格A55:A61.我在网上搜索后发现: http://pythonexcels.com/python-excel-mini-cookbook/

I have a formula in an Excel file, at cells A5:A54. I'm trying to fill the cells A55:A61 with values according to the same formula. I searched online and found this: http://pythonexcels.com/python-excel-mini-cookbook/

从链接中引用:此脚本使用Excel的自动填充功能来检查单元格A1和A2中的数据，然后通过A10自动填充剩余的单元格列."链接中的代码:

Quote from the link: "This script uses Excel’s autofill capability to examine data in cells A1 and A2, then autofill the remaining column of cells through A10." The code from the link:

#
# Autofill cell contents
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()
ws = wb.Worksheets("Sheet1")
ws.Range("A1").Value = 1
ws.Range("A2").Value = 2
ws.Range("A1:A2").AutoFill(ws.Range("A1:A10"),win32.constants.xlFillDefault)
wb.SaveAs('autofill_cells.xlsx')
excel.Application.Quit()

所以我在代码中尝试过:

So I tried it in my code:

from win32com.client import Dispatch      
from win32com.client import constants
import os  
from Tkinter import Tk
from tkFileDialog import askopenfilename
import win32com.client as win32
xlApp = Dispatch("Excel.Application")



...  
print "Choose the excel file that you want to copy the data to"
Tk().withdraw() 
filename_to = askopenfilename()
xlBook_to = xlApp.Workbooks.Open(filename_to) 
xlApp.Visible=1  
xlSheet_to_final = xlBook_to.Sheets(1)  
xlBook_to.Activate()

xlSheet_to_final.Range("A53:A54").Select()         

xlSheet_to_final.Range("A53:A54").AutoFill(xlSheet_to_final.Range("A55:A61"),win32.constants.xlFillDefault)

我收到此错误:

File "C:\Users\<user>\excel_test.py", line 74, in <module>
  xlSheet_to_final.Range("A53:A54").AutoFill(xlSheet_to_final.Range("A55:A61"),win32.constants.xlFillDefault)
File "C:\Python27\Lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x8\Range.py", line 66, in AutoFill
  , Type)
File "C:\Python27\Lib\site-packages\win32com\client\__init__.py", line 459, in _ApplyTypes_
  self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u'AutoFill method of Range class failed', u'xlmain11.chm', 0, -2146827284), None)

我该如何解决?如何将先前单元格的公式应用于随后的单元格?

How do I fix it? How do I apply the formulas of the previous cells on the following cells?

推荐答案

尝试

from win32com.constants import xlFillDefault
....
xlSheet_to_final.Range("A53:A54").AutoFill(xlSheet_to_final.Range("A53:A61"), xlFillDefault)

这篇关于Python:根据先前单元格中的公式自动填充Excel中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！