CODEWITHSUNDEEP
pythonansibleexport-to-excelxlsxwriter
Santosh Kulkarni
Santosh Kulkarni

Reputation: 21

Custom Ansible Module Python XlSXWRITER cannot format rows greater than Z

This Ansible module convert and combine csv files in xlsx format.

I have copied module from https://github.com/giovannisciortino/ansible-generate-report and made some format changes like alignment and other module is working fine for rows less than for sheets which has width of less than Z it throws error on columns more than Z like AA AB .

Can we add the ability to convert csv to xlsx which are bigger in width as well.

My code is as below:

#!/usr/bin/python

from ansible.module_utils.basic import *
import sys
import os
import csv
import xlsxwriter
import glob


def main():
    fields = {
        "csv_dir": {"required": True, "type": "str"},
        "output_xlsx_file": {"required": True, "type": "str"},
        "format_header": {"required": True, "type": "bool"},
        # if defined use this csv list as first sheets of the workbook
        "summary_csv_list": {"required": False, "type": "list", "default": []},
    }

    module = AnsibleModule(argument_spec=fields)

    wb = xlsxwriter.Workbook(module.params['output_xlsx_file'])
    format_header = wb.add_format({'border': 1})
    format_header.set_bold()
    format_header.set_bg_color('red')
    format_header.set_font_color('white')
    format_header.set_text_wrap()
    format_header.set_align('center')
    format_header.set_align('vcenter')

    cell_format = wb.add_format({'border': 1})
    cell_format.set_center_across('center_across')
    cell_format.set_align('left')
    cell_format.set_align('vcenter')

    csv_dir = module.params['csv_dir']
    csv_file_list = sorted(glob.glob(csv_dir + '/*.csv'))

    for summary_filename_csv in reversed(module.params['summary_csv_list']):
        summary_filename = csv_file_list.index(csv_dir + '/' + summary_filename_csv)
        csv_file_list.insert(0, csv_file_list.pop(summary_filename))

    for csv_file_path in csv_file_list:

        sheet_title = os.path.splitext(os.path.basename(csv_file_path))[0][0:31]
        ws = wb.add_worksheet(sheet_title)

        with open(csv_file_path, 'r') as csvfile:
            table = csv.reader(csvfile)
            num_row = 0
            num_cols = 0
            columns_width = []
            for row in table:
                if module.params['format_header'] and num_row == 0:
                    ws.write_row(num_row, 0, row, format_header)
                else:
                    ws.write_row(num_row, 0, row, cell_format)

                num_row += 1
                num_cols = max(num_cols, len(row))
                columns_width = [max(len(j), columns_width[i] if len(columns_width) > i else 1) for i, j in enumerate(row)]
            # Simulate autofit column
            for i, j in enumerate(columns_width):
                column_name = "%s:%s" % (chr(ord('A') + i), chr(ord('A') + i))
                ws.set_column(column_name, j)

        if module.params['format_header']:
            ws.autofilter(0, 0, num_row-1, num_cols-1)

    wb.close()

    response = {"result": "file %s created" % (module.params['output_xlsx_file'])}
    module.exit_json(changed=False, meta=response)


if __name__ == '__main__':
    main()

Ansible error on sheets which greater in width like columa AA AB

TASK [genrating report] ******************************************************** An exception occurred during task execution. To see the full traceback, use -vvv. The error was: AttributeError: 'NoneType' object has no attribute 'group' fatal: [localhost]: FAILED! => {"changed": false, "module_stderr": "Traceback (most recent call last):\n File "/root/.ansible/tmp/ansible-tmp-1603338799.11-110921-229302004092192/AnsiballZ_xlswriter.py", line 102, in \n _ansiballz_main()\n File "/root/.ansible/tmp/ansible-tmp-1603338799.11-110921-229302004092192/AnsiballZ_xlswriter.py", line 94, in _ansiballz_main\n invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n File "/root/.ansible/tmp/ansible-tmp-1603338799.11-110921-229302004092192/AnsiballZ_xlswriter.py", line 40, in invoke_module\n runpy.run_module(mod_name='ansible.modules.xlswriter', init_globals=None, run_name='main', alter_sys=True)\n File "/usr/lib64/python2.7/runpy.py", line 176, in run_module\n fname, loader, pkg_name)\n File "/usr/lib64/python2.7/runpy.py", line 82, in _run_module_code\n mod_name, mod_fname, mod_loader, pkg_name)\n File "/usr/lib64/python2.7/runpy.py", line 72, in _run_code\n exec code in run_globals\n File "/tmp/ansible_xlswriter_payload_TOQ4f5/ansible_xlswriter_payload.zip/ansible/modules/xlswriter.py", line 77, in \n File "/tmp/ansible_xlswriter_payload_TOQ4f5/ansible_xlswriter_payload.zip/ansible/modules/xlswriter.py", line 65, in main\n File "/usr/lib/python2.7/site-packages/xlsxwriter/worksheet.py", line 120, in column_wrapper\n _, col_1 = xl_cell_to_rowcol(cell_1)\n File "/usr/lib/python2.7/site-packages/xlsxwriter/utility.py", line 121, in xl_cell_to_rowcol\n col_str = match.group(2)\nAttributeError: 'NoneType' object has no attribute 'group'\n", "module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1} ...ignoring

Upvotes: 1

Views: 418

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

The logic of this part of the code only works from 'A' to 'Z':


for i, j in enumerate(columns_width):
    column_name = "%s:%s" % (chr(ord('A') + i), chr(ord('A') + i))
    ws.set_column(column_name, j)

Rather than trying to work out ranges like AA:AA it is simpler to use set_column() with (row, col) notation instead of A1 notation (see the XlsxWriter docs on Cell Notation):


for i, j in enumerate(columns_width):
    ws.set_column(i, i, j)

Upvotes: 0

Related Questions