如何用openpyxl替换特定列中的值？

I want to replace dates present in a particular format with the standard format of dd-mm-yyyy. Actual date present in the 10th column would be like 6.2017, 7.2017 where 7 is the month and 2017 is the year. I just need to add 1- before all the values and then I want to replace the dot present in the date value with - (hyphen). So the output looks like 1-6-2017, 1-7-2017. Note that the column may contain none values. In that case, I want to skip those none values.

我想用dd-mm-yyyy的标准格式替换特定格式的日期。第10列中的实际日期将类似于6.2017,7.2017,其中7是月份,2017年是年份。我只需要在所有值之前添加1-然后我想用 - (连字符)替换日期值中的点。所以输出看起来像1-6-2017,1-7-2017。请注意,该列可能不包含任何值。在这种情况下,我想跳过这些没有值。

This is what I tried,

这是我试过的,

from openpyxl import load_workbook
wb = load_workbook(r'E:\foo\Python\stocktable.xlsx')
ws = wb.get_active_sheet()
for rownum in range(2, ws.get_highest_row()):
    print(str(ws.cell(row=rownum, column=10).value))

Output:

So I add a litle bit tweak to the above code, to work for my case. But it fails.

所以我在上面的代码中添加了一些小小的调整,以适应我的情况。但它失败了。

from openpyxl import load_workbook
wb = load_workbook(r'E:\foo\Python\stocktable.xlsx')
ws = wb.get_active_sheet()
for rownum in range(2, ws.get_highest_row()):
    ws.cell(row=rownum, column=10).value = '1-' + str(ws.cell(row=rownum, column=10).value.replace('.', '-')

wb.save(r'E:\foo\Python\stocktable.xlsx')

That is, it shows a symtax error like,

也就是说,它显示了一个symtax错误,如,

File "E:\foo\Python\python-excel.py", line 7
    wb.save() 
     ^
SyntaxError: invalid syntax

2 个解决方案

#1

If you just want to change the formatting of dates or times, you only need to change the number_format for the cells.

如果您只想更改日期或时间的格式,则只需更改单元格的number_format。

for row in range(2, ws.max_row):
   cell = ws.cell(row=row, column=10)
   cell.number_format = "dd-mm-yyyy"

#2

Ok, I figured out the problem and the below code works for me.

好的,我发现了问题,下面的代码对我有用。

from openpyxl import load_workbook
wb = load_workbook(r'E:\foo\Python\stocktable.xlsx')
ws = wb.get_active_sheet()
for rownum in range(2, ws.get_highest_row()):
    try:
        month, year = str(ws.cell(row=rownum, column=10).value).split(".")
        ws.cell(row=rownum, column=10).value = '1-' + str(month) + '-' + str(year) 
    except ValueError:
        pass    

wb.save(r'E:\foo\Python\stocktable.xlsx')

Like @shahkalpesh said in his comment, I just split the string (At first it was in float format, so I convert it to string and then do splitting based on dot) according to the dot and save the values to month and year variable.

就像@shahkalpesh在他的评论中所说的那样,我只是根据点分割字符串(首先它是浮点格式,所以我将它转换为字符串,然后根据点进行拆分)并将值保存为月份和年份变量。

Then I put the code inside try except block since the column contains none values.

然后我把代码放在try除了块之外,因为该列不包含任何值。

#1