在openpyxl中保存Excel文件有问题。 我正在尝试创建一个处理脚本,该脚本将从一个excel文件中获取数据,并将其转储到转储excel文件中,在对excel中的公式进行一些调整之后,我将把所有已处理的数据都保存在转储excel文件中。我现在的代码是这样的。
from openpyxl import load_workbook
import os
import datetime
from openpyxl.cell import get_column_letter, Cell, column_index_from_string, coordinate_from_string
dump = dumplocation
desktop = desktoplocation
date = datetime.datetime.now().strftime("%Y-%m-%d")
excel = load_workbook(dump+date+ ".xlsx", use_iterators = True)
sheet = excel.get_sheet_by_name("Sheet1")
try:
query = raw_input('How many rows of data is there?\n')
except ValueError:
print 'Not a number'
#sheetname = raw_input('What is the name of the worksheet in the data?\n')
for filename in os.listdir(desktop):
if filename.endswith(".xlsx"):
print filename
data = load_workbook(filename, use_iterators = True)
ws = data.get_sheet_by_name(name = '17270115')
#copying data from excel to data excel
n=16
for row in sheet.iter_rows():
for cell in row:
for rows in ws.iter_rows():
for cells in row:
n+=1
if (n>=17) and (n<=32):
cell.internal_value = cells.internal_value
#adding column between time in UTC and the data
column_index = 1
new_cells = {}
sheet.column_dimensions = {}
for coordinate, cell in sheet._cells.iteritems():
column_letter, row = coordinate_from_string(coordinate)
column = column_index_from_string(column_letter)
# shifting columns
if column >= column_index:
column += 1
column_letter = get_column_letter(column)
coordinate = '%s%s' % (column_letter, row)
# it's important to create new Cell object
new_cells[coordinate] = Cell(sheet, column_letter, row, cell.value)
sheet.cells = new_cells
#setting columns to be hidden
for coordinate, cell in sheet._cells.iteritems():
column_letter, row = coordinate_from_string(coordinate)
column = column_index_from_string(column_letter)
if (column<=3) and (column>=18):
column.set_column(column, options={'hidden': True})
自从两三周前我刚开始使用Python以来,我知道我的很多代码都很混乱。我也有一些悬而未决的问题,我可以在以后处理。 似乎没有多少人为了我的目的而使用openpyxl。 我试过使用普通的工作簿模块,但似乎不起作用,因为您无法在单元格项中迭代。(我需要将相关数据从一个excel文件复制粘贴到另一个excel文件)
更新:我意识到openpyxl只能创建工作簿,但不能编辑当前工作簿。因此,我决定在将数据传输到其中之后,更改曲调并编辑新工作簿。我已使用“返回工作簿”传输数据:
from openpyxl import Workbook
from openpyxl import worksheet
from openpyxl import load_workbook
import os
from openpyxl.cell import get_column_letter, Cell, column_index_from_string, coordinate_from_string
dump = "c:/users/y.lai/desktop/data/201501.xlsx"
desktop = "c:/users/y.lai/desktop/"
excel = Workbook()
sheet = excel.add_sheet
try:
query = raw_input('How many rows of data is there?\n')
except ValueError:
print 'Not a number'
#sheetname = raw_input('What is the name of the worksheet in the data?\n')
for filename in os.listdir(desktop):
if filename.endswith(".xlsx"):
print filename
data = load_workbook(filename, use_iterators = True)
ws = data.get_sheet_by_name(name = '17270115')
#copying data from excel to data excel
n=16
q=0
for x in range(6,int(query)):
for s in range(65,90):
for cell in Cell(sheet,chr(s),x):
for rows in ws.iter_rows():
for cells in rows:
q+=1
if q>=5:
n+=1
if (n>=17) and (n<=32):
cell.value = cells.internal_value
但这似乎还不管用
Traceback (most recent call last):
File "xxx\Desktop\xlspostprocessing.py", line 40, in <module>
for cell in Cell(sheet,chr(s),x):
File "xxx\AppData\Local\Continuum\Anaconda\lib\site-packages\openpyxl\cell.py", line 181, in __init__
self._shared_date = SharedDate(base_date=worksheet.parent.excel_base_date)
AttributeError: 'function' object has no attribute 'parent'
通过了API但是..我被里面的代码压得喘不过气来,所以我无法理解API。对我来说,我好像用错了手机模块。我读了单元格的定义及其属性,因此chr(s)给出了26个字母A-Z
可以使用标准工作簿模式进行迭代。
use_iterators=True
已重命名为read_only=True
,以强调此模式的用途(按需读取部件)。您当前的代码无法使用此方法,因为工作簿是只读的,
cell.internal_value
始终是只读属性。但是,由于Excel文件有问题,您似乎没有达到这一目标。您可能需要提交其中一个文件的错误。此外,邮件列表可能是更好的讨论场所。
您可以尝试使用xlrd和xlwt,而不是pyopenxl,但是您可能会在xlutil中找到您想要做的事情-所有这些都来自python-excel。
相关问题 更多 >
编程相关推荐