Python:Openpyxl输出"None"；用于空单元格 [英] Python: Openpyxl outputs "None" for empty cells

问题描述

下面的代码应遍历目录，打开文件，转换等.问题是当单元格为空时，生成的CSV文件将在其位置输出"None".

The below code should go through directories, open files, convert etc. The thing is when a cell is empty, the resulting CSV file outputs "None" in its place.

为什么可以并且可以对此采取任何补救措施?

Any reason why and can this be remedied?

谢谢

import os
from openpyxl import load_workbook
import csv

for subdir, dirs, files in os.walk("C:\Users\Alan\Downloads\Knowledge\HOW DO I"):
    for file in files:
        filepath = subdir + os.sep + file

        wb = load_workbook(filename=filepath)
        sh = wb.active
        your_csv_file = open(filepath.replace(".xlsx","")+'_csv.csv','wb')
        wr = csv.writer(your_csv_file,quoting=csv.QUOTE_ALL)

        for rownum in sh.iter_rows():
            wr.writerow([unicode(val.value).encode('ascii','ignore') for val in rownum])

        your_csv_file.close()

推荐答案

OpenPyXl不存储空单元格(表示没有值，字体，边框等的空单元).如果从工作表中获得一个单元格，它将动态创建一个具有None值的新的空单元格.

OpenPyXl doesn’t store empty cells (empty means without value, font, border, and so on). If you get a cell from a worksheet, it dynamically creates a new empty cell with a None value.

Worksheet.iter_rows()的当前实现(v2.4.0)使用Worksheet.cell()方法，该方法调用没有 value 的Cell()构造函数.

The current implementation (v2.4.0) of Worksheet.iter_rows() use Worksheet.cell() method which calls Cell() constructor with no value.

您需要更改代码以处理空"单元格:

You need to change your code to handle "empty" cells:

for rownum in sh.iter_rows():
    values = [(u"" if cell.value is None else unicode(cell.value))
              for cell in rownum]
    wr.writerow([value.encode('ascii', 'ignore') for value in rownum])

注意:由于可能是Windows用户将数据导出到CSV文件，因此您可以选择更有用的编码，例如:cp1252.

Note: since you export your data to a CSV file for, presumably Windows users, you may choose a more useful encoding like: cp1252.

这篇关于Python:Openpyxl输出"None"；用于空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！