How to modify XLSX column formatting with Python
Question: I have hundreds of XLSX files which all have columns containing long numeric
account numbers. I need to automatically convert all of these files to CSV.
This is trivial with tools like `ssconvert`. However, due to a ~~bug~~
[feature](http://superuser.com/questions/413226/why-does-excel-treat-long-
numeric-strings-as-scientific-notation-even-after-chan) in Excel and
Libreoffice, long numeric fields will be displayed using scientific notation
and this formatted number (not the underlying data) will be preserved if
exported to CSV.

This means that any automated conversion to CSV will truncate account numbers,
since the value `1240800388917` will be written to the CSV as 1.2408E+12 or
1240800000000, causing data corruption.

This is easy to fix by manually opening the Excel file and setting these
columns to "text" format. However, it's a bit tedious to do this for hundreds
of files, especially because many of these files have strange macros and
formatting that make Libreoffice take several minutes to open each one
(another reason why I'd like to convert them all to CSV in the first place).

What's the easiest way to use Python to automatically open each file and
change an entire column's formatting to "text"? I see plenty of Python
examples with how to read XLS/XLSX files, and in some cases write them, but I
can find few guides on manipulating a column's default formatting.


Answer: Took me some trial and error and digging around in the code, but the solution
turned out to be trivial.

    
    
    from openpyxl import load_workbook
    wb = load_workbook('myfile.xlsx')
    ws = wb.active
    for row in ws.rows:
        row[col_index].number_format = row[col_index].style.number_format = '@'
    wb.save('myfile-fixed.xlsx')
    

