1

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.

halo
  • 23
  • 1
  • 4
  • 1
    In what part of the problem are you facing the problem? – shadyabhi Feb 03 '12 at 08:04
  • You can use COM with pywin32 and [Excel.Application](http://msdn.microsoft.com/en-us/library/wss56bz7(v=vs.80).aspx) or the [Python-Excel](http://www.python-excel.org/) tools. Check them out, then come back with specific problems. – tharen Feb 03 '12 at 08:46
  • 1
    Do you want to sort rows based on the contents of a given column or columns? sort columns based on the contents of a given row or rows? something else? – John Machin Feb 03 '12 at 09:51
  • sort columns based on the contents of a given row or rows – halo Feb 06 '12 at 10:02

1 Answers1

5

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')
Bogdan
  • 8,017
  • 6
  • 48
  • 64