问题描述
我写了一个代码,通过openpyxl将.csv文件(包含数字)导入excel文件。它的工作原理,但是,所有的单元格都将数字写入excel文件作为文本。然后我必须手动纠正excel中的错误:格式为文本的数字(在单元格的角落显示小的绿色三角形)。
I have written a code to import a .csv file (containing numbers) into an excel file through openpyxl. It works, however, all the cells have written the numbers to the excel file as text. I then have to manually correct the error in excel: "Numbers formatted as text (displaying little green triangles in the corner of the cell).
有一种方法可以防止感谢
Is there a way to prevent this from happening? It occurs with any csv file, even if I make it with just numbers. Thanks
#!python2
# Add csv file to an xlsx
import os, csv, sys, openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.cell import get_column_letter
#Open an xlsx for reading
wb = load_workbook('Test.xlsx')
ws = wb.get_sheet_by_name("RUN")
dest_filename = "Test_NEW.xlsx"
csv_filename = "csvfile.csv"
#Copy in csv
f = open(csv_filename)
reader = csv.reader(f)
for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell
wb.save(filename = dest_filename)
print "new Cashflow created"
***** UPDATE * **
谢谢,这很有帮助。我的问题是,我的csv文件混合了文本和数字没有任何定义引号。所以我实现了以下,只要没有错误,从字符串更改为浮动。
Thanks, that helps. My problem was that my csv file had a mixture of text and numbers without any defining quotes. So I implemented the below to change it from a string to float as long as there isn't an error.
#Copy in csv
f = open(csv_filename,'rb')
reader = csv.reader(f)
for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
s = cell
try:
s=float(s)
except ValueError:
pass
ws.cell('%s%s'%(column_letter, (row_index + 1))).value = s
推荐答案
您需要将CSV文件中的值转换为您需要的值。 CSV文件中的所有值都是字符串。
ws.cell('%s%s'%(column_letter,(row_index + 1)))value = int(cell)
应该这样做。
You need to convert the value from the CSV file to what you need. All values in CSV files are strings.ws.cell('%s%s'%(column_letter, (row_index + 1))).value = int(cell)
ought to do it.
BTW。您可能需要查看 ws.append()
方法。
BTW. you might want to look at the ws.append()
method.
这篇关于openpyxl python - 写csv到excel给出'数字格式为文本'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!