Thank you. Even I am not much aware of Python coming in advance level. It looks like big task. I will wait anyone suggest us more better way
On Wed, 9 Aug 2023, 17:15 Thanh Nguyen Duc, <[email protected]> wrote: > 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 <[email protected]> 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, <[email protected]> > 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 <[email protected]> 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: >> [email protected] (Jane Jolie) to: John Doe <[email protected]> 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 [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/ansible-project/CANGEjuUuBxieKOifmcEqN9OY6Y%2BuqEdXyG2B%2BUv_eUk1FmiOVA%40mail.gmail.com >> <https://groups.google.com/d/msgid/ansible-project/CANGEjuUuBxieKOifmcEqN9OY6Y%2BuqEdXyG2B%2BUv_eUk1FmiOVA%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> >> -- >> 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 [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/ansible-project/655EE3A2-3194-4219-B7D2-C7236C831F11%40gmail.com >> <https://groups.google.com/d/msgid/ansible-project/655EE3A2-3194-4219-B7D2-C7236C831F11%40gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/ansible-project/CANGEjuWWTHkMSuTRJgKju70S9GOBSNok1oxndCkBr_aMa0vqGw%40mail.gmail.com > <https://groups.google.com/d/msgid/ansible-project/CANGEjuWWTHkMSuTRJgKju70S9GOBSNok1oxndCkBr_aMa0vqGw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/ansible-project/C95EF81F-7406-48DD-A8ED-A4C59F5B7850%40gmail.com > <https://groups.google.com/d/msgid/ansible-project/C95EF81F-7406-48DD-A8ED-A4C59F5B7850%40gmail.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CANGEjuX2O1coE9OC9tE_1cDEswy-8SRWSzND8YLeoJNky6%2BYkw%40mail.gmail.com.
