将txt的cell属性设置为number时，将txt转换为xlsx [英] Converting txt to xlsx while setting the cell property for number cells as number
问题描述
我修改了以下代码，谢谢Anand S Kumar.
import csv
import openpyxl
import sys
def convert(input_path, output_path):
"""
Read a csv file (with no quoting), and save its contents in an excel file.
"""
wb = openpyxl.Workbook()
ws = wb.worksheets[0]
with open(input_path) as f:
reader = csv.reader(f, delimiter='\t', quoting=csv.QUOTE_NONE)
for row_index, row in enumerate(reader, 1):
for col_index, value in enumerate(row, 1):
ws.cell(row=row_index, column=col_index).value = value
wb.save(output_path)
def main():
try:
input_path, output_path = sys.argv[1:]
except ValueError:
print 'Usage: python %s input_path output_path' % (sys.argv[0],)
else:
convert(input_path, output_path)
if __name__ == '__main__':
main()
一个问题是，这会将xlsx保存为将纯数字单元格保存为普通文本的方式.
因此，当我不得不使用MS-Excel手动打开xlsx文件，然后单击转换为数字"时.
如果单元格纯粹是数字，那么此代码是否可以通过自动将单元格属性设置为数字的方式将txt转换为xlsx?
我认为问题是当您使用csv模块读取数据时，您正在读取所有字符串.示例-
a.csv看起来像-
1,2,3
3,4,5
4,5,6
代码和结果-
>>> import csv
>>> with open('a.csv','r') as f:
... reader = csv.reader(f)
... for row in reader:
... print(row)
...
['1', '2', '3']
['3', '4', '5']
['4', '5', '6']
在您的特定代码中，您将直接将csv模块返回的值设置为openpyxl，因此您获取的是字符串，而不是数字.
这里最好的解决方案是，如果您知道期望数据作为整数的列，则可以在将其设置为excel之前检查一下代码以将这些数据转换为整数.示例-
int_cols = set([2,4,5]) #This should be the list of all columns , 1 indexed, that contain integers.
with open(input_path) as f:
reader = csv.reader(f, delimiter='\t', quoting=csv.QUOTE_NONE)
for row_index, row in enumerate(reader, 1):
for col_index, value in enumerate(row, 1):
if col_index in int_cols:
ws.cell(row=row_index, column=col_index).value = int(value)
else:
ws.cell(row=row_index, column=col_index).value = value
如果有浮点数，则可以为它们使用类似的逻辑，定义一组浮点数，然后如果col_index是该列，则在保存之前将值转换为float.
如果按行-
如果单元格纯粹是数字，那么此代码是否可以通过自动将单元格属性设置为数字的方式将txt转换为xlsx?
您的意思是要为仅digits(甚至不是小数)的所有单元格将其设置为number，然后可以使用如下所示的方法-
def int_or_str(x):
try:
return int(x)
except ValueError:
return x
然后在代码中，您可以将设置值的行更改为-
ws.cell(row=row_index, column=col_index).value = int_or_str(value)
如果还要转换浮点数，请在上述方法中使用float().
Related question: Error in converting txt to xlsx using python
I have the following code which I revised thanks you Anand S Kumar.
import csv
import openpyxl
import sys
def convert(input_path, output_path):
"""
Read a csv file (with no quoting), and save its contents in an excel file.
"""
wb = openpyxl.Workbook()
ws = wb.worksheets[0]
with open(input_path) as f:
reader = csv.reader(f, delimiter='\t', quoting=csv.QUOTE_NONE)
for row_index, row in enumerate(reader, 1):
for col_index, value in enumerate(row, 1):
ws.cell(row=row_index, column=col_index).value = value
wb.save(output_path)
def main():
try:
input_path, output_path = sys.argv[1:]
except ValueError:
print 'Usage: python %s input_path output_path' % (sys.argv[0],)
else:
convert(input_path, output_path)
if __name__ == '__main__':
main()
A problem with this is that this saves xlsx in a way that saves purely number-only cells as normal text.
So when I had to open the xlsx file manually using MS-Excel and then click "Convert to number".
Can this code convert txt to xlsx in a way that automatically sets the cell property as number, if the cell is purely number?
I think the issue is that when you read data using csv module, you are reading in all strings. Example -
a.csv looks like -
1,2,3
3,4,5
4,5,6
Code And result -
>>> import csv
>>> with open('a.csv','r') as f:
... reader = csv.reader(f)
... for row in reader:
... print(row)
...
['1', '2', '3']
['3', '4', '5']
['4', '5', '6']
And in your particular code, you are directly setting this value returned by the csv module to openpyxl , hence you are getting the strings, instead of numbers.
The best solution here would be that if you know which are the columns that you are expecting data to be an integer for, you can put a checking your code to convert those data to integer before setting it to excel . Example -
int_cols = set([2,4,5]) #This should be the list of all columns , 1 indexed, that contain integers.
with open(input_path) as f:
reader = csv.reader(f, delimiter='\t', quoting=csv.QUOTE_NONE)
for row_index, row in enumerate(reader, 1):
for col_index, value in enumerate(row, 1):
if col_index in int_cols:
ws.cell(row=row_index, column=col_index).value = int(value)
else:
ws.cell(row=row_index, column=col_index).value = value
If there are floats, you can use similar logic for them , define a set of columns that are float, and then if the col_index is that column, convert value to float before saving.
If by the line -
Can this code convert txt to xlsx in a way that automatically sets the cell property as number, if the cell is purely number?
You mean you want to set it to number for all cells that are only digits (not even decimals) , then you can use a method like the below -
def int_or_str(x):
try:
return int(x)
except ValueError:
return x
Then in your code, you can change the line setting the value, to -
ws.cell(row=row_index, column=col_index).value = int_or_str(value)
Use float() in the above method, if you want to convert floats as well.
这篇关于将txt的cell属性设置为number时，将txt转换为xlsx的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
