CODEWITHSUNDEEP
pythonexcelsortingxlsxwriter
Okapi575
Okapi575

Reputation: 718

Sorting tables xlsxwriter for python

I have a calculation that creates an excel spreadsheet using xlsxwriter to show results. It would be useful to sort the table after knowing the results.

One solution would be to create a separate Data structure in python, and sort the data structure, and use xlsx later, but it is not very elegant, requires a lot of data type handling.

I cannot find a way to sort the structures in the xlsx module. Can anybody help with the internal data structure of that module? Can that be sorted, before writing it to disk. Another solution would be reopening the file, sort the stuff and close it again?

    import xlsxwriter
    workbook=xlsxwriter("Trial.xlsx")
    worksheet=workbook.add_worksheet("first")
    worksheet.write_number(0,1,2)
    worksheet.write_number(0,2,1)

    ...worksheet.sort

Upvotes: 2

Views: 10627

Answers (2)

Martin Evans
Martin Evans

Reputation: 46779

You should process your data before writing it to a Workbook as it is not easily possible to manipulate the data once in the spreadsheet.

The following example would write a column of numbers unsorted:

import xlsxwriter

with xlsxwriter.Workbook("Trial.xlsx") as workbook:
    worksheet = workbook.add_worksheet("first")

    data = [5, 2, 7, 3, 8, 1]

    for rowy, value in enumerate(data):
        worksheet.write_number(rowy, 0, value)      # use column 0

But if you first sort the data as follows:

import xlsxwriter

with xlsxwriter.Workbook("Trial.xlsx") as workbook:
    worksheet = workbook.add_worksheet("first")

    data = sorted([5, 2, 7, 3, 8, 1])

    for rowy, value in enumerate(data):
        worksheet.write_number(rowy, 0, value)      # use column 0

You would get something like:

Sorted data in a single column

Upvotes: 3

jmcnamara
jmcnamara

Reputation: 41634

Can anybody help with the internal data structure of that module? Can that be sorted, before writing it to disk.

I am the author of the module and the short answer is that this can't or shouldn't be done.

It is possible to sort worksheet data in Excel at runtime but that isn't part of the file specification so it can't be done with XlsxWriter.

One solution would be to create a separate Data structure in python, and sort the data structure, and use xlsx later, but it is not very elegant, requires a lot of data type handling.

That sounds like a reasonable solution to me.

Upvotes: 7

Related Questions