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, <ng.duct...@gmail.com> 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 <ahar...@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.duct...@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 <ahar...@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: ja...@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-proje...@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
>>>
>>> <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 ansible-proje...@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
>>>
>>> <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 ansible-proje...@googlegroups.com.
>> 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 ansible-proje...@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
>>
>> <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 ansible-project+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/ansible-project/9c9a828b-cf63-422a-b717-2fc6597a3361n%40googlegroups.com.
