Hope can help. I am not python programmer so code may not clean.
from ansible.module_utils.basic import *
import sys
import os
import csv
import xlsxwriter
import glob
import codecs
import pwd
import grp
def main():
fields = {
"csv_dir": {"required": True, "type": "str"},
"output_xlsx_file": {"required": True, "type": "str"},
"format_header": {"required": True, "type": "bool"},
"format_error": {"required": False, "type": "list"},
"format_correct": {"required": False, "type": "list"},
"owner": {"required": False, "type": "str"},
"group": {"required": False, "type": "str"},
"split_data": {"required": False, "type": "bool"},
"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()
format_header.set_bold()
format_header.set_bg_color('blue')
format_header.set_font_color('white')
f1 = wb.add_format({'bg_color': 'red', 'font_color': 'black', 'bold': True })
f2 = wb.add_format({'bg_color': 'green', 'font_color': 'black', 'bold': True })
f3 = wb.add_format({'border':1, 'border_color':'black', 'text_wrap': True})
csv_dir = module.params['csv_dir']
csv_file_list = sorted(glob.glob(csv_dir + '/*.csv'))
summary_worksheets = []
for summary_filename_csv in module.params['summary_csv_list']:
summary_csv_file_path = os.path.join(csv_dir, summary_filename_csv)
summary_sheet_title = os.path.splitext(os.path.basename(summary_csv_file_path))[0][0:31]
summary_ws = wb.add_worksheet(summary_sheet_title)
with codecs.open(summary_csv_file_path, 'r') as summary_csvfile:
summary_table = csv.reader((l.replace('\0', '') for l in summary_csvfile))
summary_num_row = 0
summary_num_cols = 0
summary_columns_width = []
for summary_row in summary_table:
if module.params['format_header'] and summary_num_row == 0:
summary_ws.write_row(summary_num_row, 0, summary_row, format_header)
else:
modified_summary_row = []
for item in summary_row:
modified_summary_row.append(item)
summary_ws.write_row(summary_num_row, 0, modified_summary_row, f3)
summary_num_row += 1
summary_num_cols = max(summary_num_cols, len(summary_row))
summary_columns_width = [max(len(j), summary_columns_width[i] if len(summary_columns_width) > i else 1) for i, j in enumerate(summary_row)]
# Simulate autofit column
for i, j in enumerate(summary_columns_width):
column_name = "%s:%s" % (chr(ord('A') + i), chr(ord('A') + i))
summary_ws.set_column(column_name, j)
summary_worksheets.append(summary_ws)
summary_ws.autofit()
summary_ws.conditional_format(
'C2:C10000',
{'type': 'no_blanks', 'format': f2}
)
summary_ws.conditional_format(
'D2:D10000',
{'type': 'no_blanks', 'format': f1}
)
# Move the summary sheets to the first position
for summary_ws in summary_worksheets:
summary_ws.set_first_sheet()
for csv_file_path in csv_file_list:
if os.path.basename(csv_file_path) in module.params['summary_csv_list']:
continue
sheet_title = os.path.splitext(os.path.basename(csv_file_path))[0][0:31]
ws = wb.add_worksheet(sheet_title)
with codecs.open(csv_file_path, 'r') as csvfile:
table = csv.reader((l.replace('\0', '') for l in 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:
modified_row = []
for item in row:
if ',' in item and module.params['split_data']:
split_data = item.split(',')
trimmed_data = [value.strip() for value in split_data]
modified_row.append('\n'.join(trimmed_data))
else:
modified_row.append(item)
ws.write_row(num_row, 0, modified_row, f3)
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)]
if module.params['format_error']:
for i in module.params['format_error']:
ws.conditional_format('A2:S10000',
{
'type': 'text',
'criteria': 'containing',
'value': "%s" %i,
'format': f1
}
)
if module.params['format_correct']:
for i in module.params['format_correct']:
ws.conditional_format('A2:S10000',
{
'type': 'text',
'criteria': 'containing',
'value': "%s" %i,
'format': f2
}
)
if module.params['format_header']:
ws.autofilter(0, 0, num_row-1, num_cols-1)
ws.autofit()
wb.close()
# change ownership
if module.params['owner'] and module.params['group']:
uid = pwd.getpwnam(module.params['owner']).pw_uid
gid = grp.getgrnam(module.params['group']).gr_gid
os.chown(module.params['output_xlsx_file'], uid, gid)
elif module.params['owner']:
uid = pwd.getpwnam(module.params['owner']).pw_uid
gid = grp.getgrnam(module.params['owner']).gr_gid
os.chown(module.params['output_xlsx_file'], uid, gid)
elif module.params['group']:
uid = pwd.getpwnam(module.params['group']).pw_uid
gid = grp.getgrnam(module.params['group']).gr_gid
os.chown(module.params['output_xlsx_file'], uid, gid)
response = {"result": "file %s created" % (module.params['output_xlsx_file'])}
module.exit_json(changed=False, meta=response)
if __name__ == '__main__':
main()
