CODEWITHSUNDEEP
pythonfilesortingxls
halo
halo

Reputation: 13

how to sort xls file column wise and write it to another file with entire row using python?

how to sort xls file column wise and write it to another file with entire row using python ? the xls file has to be sorted column wise. And after sorting it has to be writen into another file.

Upvotes: 1

Views: 8548

Answers (1)

Bogdan
Bogdan

Reputation: 8246

How about:

     column = 0 #The column you want to sort by
     reader = list(csv.reader(open('input.xsl')))
     reader.sort(key=lambda x: x[column])
     writer = csv.writer(open('output.xsl', 'w'))
     writer.writerows(reader)

My bad, well you can always export as csv i guess. If you want to stick to xls you can use xlrd and xlwt. I haven't worked much with this but I do have a sample from a task I had to do a while back. Here it is(not that is not 100% good because the cell titles for each columns will be stored as the first row on data on the output file):

    import xlwt
    from xlrd import open_workbook

    target_column = 0

    book = open_workbook('input.xls', formatting_info=True)
    sheet = book.sheets()[0]
    data = [sheet.row_values(i) for i in xrange(sheet.nrows)]
    labels = data[0]
    data = data[1:]
    data.sort(key=lambda x: x[target_column])

    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet(sheet.name)

    for idx, label in enumerate(labels):
         sheet.write(0, idx, label)

    for idx_r, row in enumerate(data):
        for idx_c, value in enumerate(row):
            sheet.write(idx_r+1, idx_c, value)

    wbk.save('result.xls')

Upvotes: 5

Related Questions