Scripts to convert to and from .csv and .xlsx

csv2xlsx.py

#!/usr/bin/env python3

# Use this for converting a .csv to a .xlsx when you don't want to treat numbers as text.

import csv
import sys
import types
import xlsxwriter


def monkey_patch_ws(ws):
    # hackety-hack: this stops Excel complaining about numbers in text format
    ws.ignore_error_cols = []
    _old_write_page_margins = ws._write_page_margins

    def _write_page_margins_and_other_stuff(self):
        _old_write_page_margins()

        self._xml_start_tag("ignoredErrors")

        for col in self.ignore_error_cols:
            col_name = xlsxwriter.utility.xl_col_to_name(col)

            attributes = [
                ("sqref", f"{col_name}:{col_name}"),
                ("numberStoredAsText", "1"),
            ]
            self._xml_empty_tag("ignoredError", attributes)
        self._write_sheet_view()
        self._xml_end_tag("ignoredErrors")

    ws._write_page_margins = types.MethodType(_write_page_margins_and_other_stuff, ws)


if len(sys.argv) != 3:
    print("Usage: csv2xlsx.py [in.csv] [out.xlsx]")
    sys.exit(1)


with open(sys.argv[1]) as f:
    rows = list(csv.reader(f))

width = max(len(row) for row in rows)

wb = xlsxwriter.Workbook(sys.argv[2])
ws = wb.add_worksheet("Sheet1")
monkey_patch_ws(ws)

for ix in range(width):
    ws.ignore_error_cols.append(ix)

for ix, row in enumerate(rows):
    ws.write_row(ix, 0, row)

wb.close()

xlsx2csv.py

#!/usr/bin/env python3

import csv
import sys
from openpyxl import load_workbook

if len(sys.argv) != 3:
    print('Usage: xlsx2csv.py [in.xlsx] [out.csv]')
    sys.exit(1)

wb = load_workbook(filename=sys.argv[1])

f = open(sys.argv[2], 'w')
writer = csv.writer(f)

for row in wb.active.rows:
    writer.writerow([c.value for c in row])