Re: [ansible-project] Mail module - multiple tabs in csv file

[Thanh Nguyen Duc]

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() ansible localhost -m ncs_csvtoexcel \ -a "csv_dir=/ancenter/opt-tasks/TESTPROD/Monthly/Windows/Compliance/2023-06-05 \ output_xlsx_file=/ancenter/opt-tasks/TESTPROD/Monthly/Windows/Compliance/2023-06-05/test.xlsx \ format_header=true \  format_error=Non-Compliance \ format_correct=Compliance" \  split_data: True \ owner=ancenter \ group=ancenter \ summary_csv_list=Summary_{{todaytime}}.csv"  csv_dir: description: The directory containing the csv file with csv extension.The xlsx file will contain a sheet for each csv type: string required: true output_xlsx_file: description: The path of the output xlsx file type: string  required: true format_header: description: If true the header(the first line of each csv) will be formatted type: boolean  required: true summary_csv_list: description: List of csv files inserted in the first sheet(s) of the workbook type: list required: false format_error  description: high light the keyword in red type: list required: false format_correct  description: high light the keyword in green type: list required: false owner:  description: change owner of file type: string  required: false group:  description: change group of file  type: string required: false split_data: description: If true the data in all the data row will be split with comma delimiter  type: boolean  required: false Thanks and Best Regards, Thanh. On 9 Aug 2023, at 18:04, Aharonu <aharon...@gmail.com> wrote:  Hi  Thanh Nguyen Duc, Thanks for quick response. May I get that reference details so I will give try for my requirement. On Wed, 9 Aug 2023, 16:29 Thanh Nguyen Duc, <ng.ducthanh1...@gmail.com> wrote: Csv i don’t think have multiple tabs. I have done with excel instead. You can use j2 template to create a csv then python to consolidate them to 1 file. On 9 Aug 2023, at 17:55, Aharonu <aharon...@gmail.com> wrote:  Hi Todd and Team, Could you please help one below query? Thank you. Example, I have  file1.csv creates throught mail module as mentionedbelow. I need to create 2 tabs (data-set1, data_set2)  in file1.csv and update required data. How can I deal with this? Thank you - name: Send csv file to the user community.general.mail: host: port: subject: Ansible-report body: Hello, this is an e-mail from: j...@example.net (Jane Jolie) to: John Doe <j...@example.org> attach: ./file1.csv delegate_to: localhost -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to ansible-project+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CANGEjuUuBxieKOifmcEqN9OY6Y%2BuqEdXyG2B%2BUv_eUk1FmiOVA%40mail.gmail.com. -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to ansible-project+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/655EE3A2-3194-4219-B7D2-C7236C831F11%40gmail.com. -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to ansible-project+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CANGEjuWWTHkMSuTRJgKju70S9GOBSNok1oxndCkBr_aMa0vqGw%40mail.gmail.com. -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to ansible-project+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/C95EF81F-7406-48DD-A8ED-A4C59F5B7850%40gmail.com.