Try starting with reviewing the problem the solution is trying to solve. Why does it have too be two tabs? Is it a technical requirement or just a preference? What will be consuming the data? Can it be used as two cvs?
On Wednesday, August 9, 2023 at 6:56:50 AM UTC-5 Aharonu wrote: > 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/9c9a828b-cf63-422a-b717-2fc6597a3361n%40googlegroups.com.
