使用 python 中的 csv 模块写入特定单元格

Question

我必须向 csv 文件中的特定单元格（例如第 8 个单元格）写入一个值。 我可以看到有一个 csvwriter.writerow(row) 方法可以写入整行，但我没有看到任何可以将值写入特定单元格的内容。

Answer 1

csv 模块 提供了读取和写入 csv 文件的功能，但是不允许就地修改特定单元格。

即使您在问题中突出显示的 csvwriter.writerow(row) 方法也不允许您识别和覆盖特定行。相反，它将 row 参数写入写入器的文件对象，实际上它只是在与写入器关联的 csv 文件中追加一行。

不过，不要阻止使用 csv 模块，它使用起来很简单，并且通过提供的原语，您可以相对轻松地实现您正在寻找的更高级别的功能。

例如，看一下以下 csv 文件：

1,2,3,four,5
1,2,3,four,5
1,2,3,four,5

单词 four 位于第 3 列（第四列，但行只是一个列表，因此索引是从零开始的），可以轻松更新为使用以下程序包含数字 4：

import csv
in_file = open("d:/in.csv", "rb")
reader = csv.reader(in_file)
out_file = open("d:/out.csv", "wb")
writer = csv.writer(out_file)
for row in reader:
    row[3] = 4
    writer.writerow(row)
in_file.close()    
out_file.close()

结果输出：

1,2,3,4,5
1,2,3,4,5
1,2,3,4,5

创建一些允许识别和更新特定行和列的通用函数需要更多工作，但与操作Python 中的 .csv 文件只是操作一系列列表。

Answer 2

假设您有一个名为 mylist.csv 的 csv 文件，其中包含以下几行：

a, b, c, d

e, f, g, h

i, j, k, l

如果您想将 'h' 修改为 'X'，可以使用此代码，需要导入 csv 模块：

    f = open('mylist.csv', 'r')
    reader = csv.reader(f)
    mylist = list(reader)
    f.close()
    mylist[1][3] = 'X'
    my_new_list = open('mylist.csv', 'w', newline = '')
    csv_writer = csv.writer(my_new_list)
    csv_writer.writerows(mylist)
    my_new_list.close()

如果您想修改每一行的特定列，只需添加for循环即可进行迭代。

Answer 3

我同意，这很烦人。我最终将 csv.DictReader 子类化。这允许就地进行基于单元格的查找编辑和转储。我在 activestate 上发布了代码： 就地 csv 查找、操作和导出

import csv, collections, copy

"""
# CSV TEST FILE 'test.csv'

TBLID,DATETIME,VAL
C1,01:01:2011:00:01:23,5
C2,01:01:2012:00:01:23,8
C3,01:01:2013:00:01:23,4
C4,01:01:2011:01:01:23,9
C5,01:01:2011:02:01:23,1
C6,01:01:2011:03:01:23,5
C7,01:01:2011:00:01:23,6
C8,01:01:2011:00:21:23,8
C9,01:01:2011:12:01:23,1


#usage (saving this cose as CustomDictReader.py)

>>> import CustomDictReader
>>> import pprint
>>> test = CustomDictReader.CSVRW()
>>> success, thedict = test.createCsvDict('TBLID',',',None,'test.csv')
>>> pprint.pprint(dict(thedict))
{'C1': OrderedDict([('TBLID', 'C1'), ('DATETIME', '01:01:2011:00:01:23'), ('VAL', '5')]),
 'C2': OrderedDict([('TBLID', 'C2'), ('DATETIME', '01:01:2012:00:01:23'), ('VAL', '8')]),
 'C3': OrderedDict([('TBLID', 'C3'), ('DATETIME', '01:01:2013:00:01:23'), ('VAL', '4')]),
 'C4': OrderedDict([('TBLID', 'C4'), ('DATETIME', '01:01:2011:01:01:23'), ('VAL', '9')]),
 'C5': OrderedDict([('TBLID', 'C5'), ('DATETIME', '01:01:2011:02:01:23'), ('VAL', '1')]),
 'C6': OrderedDict([('TBLID', 'C6'), ('DATETIME', '01:01:2011:03:01:23'), ('VAL', '5')]),
 'C7': OrderedDict([('TBLID', 'C7'), ('DATETIME', '01:01:2011:00:01:23'), ('VAL', '6')]),
 'C8': OrderedDict([('TBLID', 'C8'), ('DATETIME', '01:01:2011:00:21:23'), ('VAL', '8')]),
 'C9': OrderedDict([('TBLID', 'C9'), ('DATETIME', '01:01:2011:12:01:23'), ('VAL', '1')])}
>>> thedict.keys()
['C1', 'C2', 'C3', 'C4', 'C5', 'C6', 'C7', 'C8', 'C9']
>>> thedict['C2']['VAL'] = "BOB"
>>> pprint.pprint(dict(thedict))
{'C1': OrderedDict([('TBLID', 'C1'), ('DATETIME', '01:01:2011:00:01:23'), ('VAL', '5')]),
 'C2': OrderedDict([('TBLID', 'C2'), ('DATETIME', '01:01:2012:00:01:23'), ('VAL', 'BOB')]),
 'C3': OrderedDict([('TBLID', 'C3'), ('DATETIME', '01:01:2013:00:01:23'), ('VAL', '4')]),
 'C4': OrderedDict([('TBLID', 'C4'), ('DATETIME', '01:01:2011:01:01:23'), ('VAL', '9')]),
 'C5': OrderedDict([('TBLID', 'C5'), ('DATETIME', '01:01:2011:02:01:23'), ('VAL', '1')]),
 'C6': OrderedDict([('TBLID', 'C6'), ('DATETIME', '01:01:2011:03:01:23'), ('VAL', '5')]),
 'C7': OrderedDict([('TBLID', 'C7'), ('DATETIME', '01:01:2011:00:01:23'), ('VAL', '6')]),
 'C8': OrderedDict([('TBLID', 'C8'), ('DATETIME', '01:01:2011:00:21:23'), ('VAL', '8')]),
 'C9': OrderedDict([('TBLID', 'C9'), ('DATETIME', '01:01:2011:12:01:23'), ('VAL', '1')])}
>>> test.updateCsvDict(thedict)
>>> test.createCsv('wb')
"""

class CustomDictReader(csv.DictReader):
    """
        override the next() function and  use an
        ordered dict in order to preserve writing back
        into the file
    """

    def __init__(self, f, fieldnames = None, restkey = None, restval = None, dialect ="excel", *args, **kwds):
        csv.DictReader.__init__(self, f, fieldnames = None, restkey = None, restval = None, dialect = "excel", *args, **kwds)

    def next(self):
        if self.line_num == 0:
            # Used only for its side effect.
            self.fieldnames
        row = self.reader.next()
        self.line_num = self.reader.line_num

        # unlike the basic reader, we prefer not to return blanks,
        # because we will typically wind up with a dict full of None
        # values
        while row == []:
            row = self.reader.next()
        d = collections.OrderedDict(zip(self.fieldnames, row))

        lf = len(self.fieldnames)
        lr = len(row)
        if lf < lr:
            d[self.restkey] = row[lf:]
        elif lf > lr:
            for key in self.fieldnames[lr:]:
                d[key] = self.restval
        return d

class CSVRW(object):

    def __init__(self):
        self.file_name = ""
        self.csv_delim = ""
        self.csv_dict  = collections.OrderedDict()

    def setCsvFileName(self, name):
        """
            @brief stores csv file name
            @param name- the file name
        """
        self.file_name = name

    def getCsvFileName(self):
        """
            @brief getter
            @return returns the file name
        """
        return self.file_name

    def getCsvDict(self):
        """
            @brief getter
            @return returns a deep copy of the csv as a dictionary
        """
        return copy.deepcopy(self.csv_dict)

    def clearCsvDict(self):
        """
            @brief resets the dictionary
        """
        self.csv_dict = collections.OrderedDict()

    def updateCsvDict(self, newCsvDict):
        """
            creates a deep copy of the dict passed in and
            sets it to the member one
        """
        self.csv_dict = copy.deepcopy(newCsvDict)

    def createCsvDict(self,dictKey, delim, handle = None, name = None, readMode = 'rb', **kwargs):
        """
            @brief create a dict from a csv file where:
                the top level keys are the first line in the dict, overrideable w/ **kwargs
                each row is a dict
                each row can be accessed by the value stored in the column associated w/ dictKey

                that is to say, if you want to index into your csv file based on the contents of the
                third column, pass the name of that col in as 'dictKey'

            @param dictKey  - row key whose value will act as an index
            @param delim    - csv file deliminator
            @param handle   - file handle (leave as None if you wish to pass in a file name)
            @param name     - file name   (leave as None if you wish to pass in a file handle)
            @param readMode - 'r' || 'rb'
            @param **kwargs - additional args allowed by the csv module
            @return bool    - SUCCESS|FAIL
        """
        self.csv_delim = delim
        try:
            if isinstance(handle, file):
                self.setCsvFileName(handle.name)
                reader = CustomDictReader(handle, delim, **kwargs)
            else:
                if None == name:
                    name = self.getCsvFileName()
                else:
                    self.setCsvFileName(name)
                reader = CustomDictReader(open(name, readMode), delim, **kwargs)
            for row in reader:
                self.csv_dict[row[dictKey]] = row
            return True, self.getCsvDict()
        except IOError:
            return False, 'Error opening file'

    def createCsv(self, writeMode, outFileName = None, delim = None):
        """
            @brief create a csv from self.csv_dict
            @param writeMode   - 'w' || 'wb'
            @param outFileName - file name || file handle
            @param delim       - csv deliminator
            @return none
        """
        if None == outFileName:
            outFileName = self.file_name
        if None == delim:
            delim = self.csv_delim
        with open(outFileName, writeMode) as fout:
            for key in self.csv_dict.values():
                fout.write(delim.join(key.keys()) + '\n')
                break
            for key in self.csv_dict.values():
                fout.write(delim.join(key.values()) + '\n')

Answer 4

使用 xlwt 模块，可以在电子表格上执行多种操作。可以写入 Excel 中的特定或特定单元格。

import xlwt 

from xlwt import Workbook
wb = Workbook() 
sheet1 = wb.add_sheet('Sheet 1') 
sheet1.write(1, 0, 'stack') 
sheet1.write(0, 1, 'overflow') 
wb.save('stackoverflow.xls') 

Answer 5

如果您确实需要在 .csv 文件中写入特定单元格并且无法避免使用此模块，则可以通过硬编码方法来实现。

假设您想要单元格 H3 中的某些内容：

with open("your_csv_file_name.csv","w",newline="") as openfile:
    your_csv_file_name = csv.writer(openfile)


    value_for_cell_h3 = "X"

    list_of_blank_columns_and_h3value = ["","","","","","","",value_for_cell_h3]   
    #Columns from A to G will be blank

    your_csv_file_name.writerow("")   #Row 1 will be blank
    your_csv_file_name.writerow("")   #Row 2 will be blank

    your_csv_file_name.writerow(list_of_blank_columns_and_h3value)
    #The "X" will be in the H3 cell