通过自动填充列中的空格来操作excel工作表的Python脚本

Question

您好，我有一个类似结构的excel文件，如下所示：

      Location       column2    column3
1     South Africa
2     
3     
4     
5     England
6     
7     
8     
9     U.S
10    
11    
12    

我正在尝试写一个python脚本，它可以用前一个位置的名称填充每个位置之间的空格(即用南非作为位置填充从2到4的空格，用英格兰作为位置填充6-8，等等)

如果有人能给我指出正确的direction.Thanks，我将不胜感激。

Answer 1

好吧，伙计，我想答案就是我为xlrd做的这个愚蠢的包装器(或者你自己写的包装器！)。关键是该函数一次读取一行到列表中，并且Python列表记住它们被填充的顺序。包装器生成一个字典，它将Excel工作表名称映射到该工作表上的行列表(这里假设每个工作表有一个表，否则您必须对其进行泛化)。每一行都是一个字典，它的键是列名。

对于你，我会读入你的数据，然后做一些类似这样的事情(未测试)：

import see_below as sb
dict = sb.workbookToDict(your_file)
output = []
this_location = None
for row in dict[relevant_sheet_name]:
    output_row = row
    if row['Location'] is not None:
        this_location = row['Location']
    else:
        output_row['Location'] = this_location

也许你可以用列表理解来做一些有趣的事情，但是今晚我已经喝了太多的酒来玩弄它:)

这是给读者的包装器：

import xlrd


def _isEmpty(_):
        return ''


def _isString(element):
        return element.value.encode('ascii', 'ignore')


def _isFloat(element):
    return float(element.value)


def _isDate(element):
    import datetime
    rawDate = float(element.value)
    return (datetime.datetime(1899, 12, 30) +
            datetime.timedelta(days=rawDate))


def _isBool(element):
    return element.value == 1


def _isExcelGarbage(element):
    return int(element.value)


_options = {0: _isEmpty,
            1: _isString,
            2: _isFloat,
            3: _isDate,
            4: _isBool,
            5: _isExcelGarbage,
            6: _isEmpty}


def WorkbookToDict(filename):
    '''
        Reads .xlsx file into dictionary.

        The keys of the dictionary correspond to sheet names in the Excel workbook.
        The first row of the Excel workbook is taken to be column names, and each row
        of the worksheet is read into a separate dictionary, whose keys correspond to
        column names. The collection of dictionaries (as a list) forms the value in the
        dictionary. The output maps sheet names (keys) to a collection of dictionaries
        (value).
    '''
    book = xlrd.open_workbook(filename)
    allSheets = {}
    for s in book.sheets():
        thisSheet = []
        headings = [_options[x.ctype](x) for x in s.row(0)]

        for i in range(s.nrows):
            if i == 0:
                continue

            thisRow = s.row(i)
            if len(thisRow) != len(headings):
                raise Exception("Mismatch between headings and row length in ExcelReader")

            rowDict = {}
            for h, r in zip(headings, thisRow):
                rowDict[h] = _options[r.ctype](r)
            thisSheet.append(rowDict)
        allSheets[str(s.name)] = thisSheet
    return allSheets

作者在这里：

import xlwt

def write(workbookDict, colMap, filename):
    '''
       workbookDict should be a map of sheet names to a list of dictionaries.
       Each member of the list should be a mapping of column names to contents,
       missing keys are handled with the nullEntry field. colMap should be a
       dictionary whose keys are identical tto the sheet names in the workbookDict.
       Each value is a list of column names that are assumed to be in order.
       If a key exists in the workbookDict that does not exist in the colDict, the
       entry in workbookDict will not be written.
    '''

    workbook = xlwt.Workbook()

    for sheet in workbookDict.keys():
        worksheet = workbook.add_sheet(sheet)
        cols = colMap[sheet]
        i = 0
        writeCols = True
        while i <= len(workbookDict[sheet]):
            if writeCols:
                for j in range(len(cols)):
                    if writeCols:  # write col headings
                        worksheet.write(i, j, cols[j])
                writeCols = False
            else:
                for j in range(len(cols)):
                    worksheet.write(i, j, workbookDict[sheet][(i-1)][cols[j]])
            i += 1

    workbook.save(filename)

无论如何，我真的希望这对你有用！

Answer 2

wb = openpyxl.load_workbook('enter your workbook name')

sheet = wb.get_sheet_by_name('enter your sheet name')

row=sheet.max_row

for row in range (3,row):

   if sheet.cell(row=row, column=1).value is not None and sheet.cell(row=row+1,column=1).value is None:

        sheet.cell(row=row+1, column=1).value = sheet.cell(row=row, column=1).value

 wb.save('enter your workbook name')