Sorting with openpyxl

Question

I am trying to sort columns from least to greatest using openpyxl. I am open to using other libraries to accomplish this. Here is the code that I have right now, however, nothing is being sorted.

from openpyxl import load_workbook

wb=load_workbook('NotSorted.xlsx')
ws1=wb.get_sheet_by_name('Mean')

ws1.auto_filter.add_sort_condition('J2:J21')

wb.save('Sorted.xlsx')

Any help is greatly appreciated!

Answer 1

You can sort using win32com.client (install it with pip install pypiwin32).

Example workbook named MyWorkbook.xlsx with contents (before and after):

import win32com.client

excel = win32com.client.Dispatch("Excel.Application")

wb = excel.Workbooks.Open('MyWorkbook.xlsx')
ws = wb.Worksheets('Sheet1')

ws.Range('A2:A9').Sort(Key1=ws.Range('A1'), Order1=1, Orientation=1)

wb.Save()
excel.Application.Quit()

If you don't want to alter the original workbook, use SaveAs() or create another workbook and copy data like so: ws_from.Range("A1:AF100").Copy(ws_to.Range("A1:AF100")) (with appropriate range).

See these documentation links for more information about Sort() and its parameters:

• Range.Sort()
• XlSortOrientation
• XlSortOrder
• Better way to spawn excel process.