Hi Sumanth and Everyone,
I have tested and it is working but i has to make small changes to work it.
Is that okay to keep?
xlsx_writer._save() --> it is getting success
xlsx_writer.save() --> mentioned in script but getting error if i use it.
I observed the first line ( header) in xlsx printing with Bold letters
which is really good.
1, Is it possible to add any theme colour for the headline?
2. speed wise is there any difference for csv vs xlsx files updating data
and sending reports to Email?
Once again thank you very much!
On Wed, Aug 9, 2023 at 1:16 PM Sumanth Parakala <sumanth.parak...@gmail.com>
wrote:
> Try using a python to merge csvs into xlsx and shell module before mail
> module.
> ----------------------------------------------
> localhost@root tmp # cat csv_xlsx_combine.py
> import pandas as pd
>
> csv1_data = pd.read_csv('file1.csv')
> csv2_data = pd.read_csv('file2.csv')
>
> xlsx_writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
>
> csv1_data.to_excel(xlsx_writer, sheet_name='Sheet1', index=False)
> csv2_data.to_excel(xlsx_writer, sheet_name='Sheet2', index=False)
>
> xlsx_writer.save()
> -------------------------------------------------------------
> for above python code to work, you ll need pandas and openpyxl modules
> install alongside python3
> pip install pandas
> pip install openpyxl
>
> ------------------------------------------------------------------------------------------
>
>
> tasks:
>
> - name: Preparing attachment
> shell: |
> python3 /tmp/csv_xlsx_combine.py
> ls /tmp/output.xlsx && echo "Good to go"
>
>
> - name: Sendmail
> community.general.mail:
> subject: "{{ email_subject }}"
> body: "{{ email_body }}"
> attach: "/tmp/output.xlsx"
> sender: "{{ email_sender }}"
> to: "{{ email_recipient }}"
> host: "{{ smtp_host }}"
> port: "{{ smtp_port }}"
> username: "{{ smtp_username }}"
> password: "{{ smtp_password }}"
> delegate_to: localhost
> - debug:
> msg: "Mail sent successfully"
>
>
> this should work
>
> On Wed, 9 Aug 2023 at 18:23, Aharonu <aharon...@gmail.com> wrote:
>
>> Noted.
>>
>> Thank you!
>>
>> On Wed, 9 Aug 2023, 18:17 Evan Hisey, <ehi...@gmail.com> wrote:
>>
>>> I would send it as two files and let the user integrate to one file if
>>> they prefer since it is not a technical requirement of usage. Csv as you
>>> note does not support tabs. With out a hard technical requirement the
>>> effort to supply tabs may exceed the value.
>>>
>>> On Wed, Aug 9, 2023, 7:42 AM Thanh Nguyen Duc <ng.ducthanh1...@gmail.com>
>>> wrote:
>>>
>>>> Technically csv doesn’t support multiple tabs so it is not able to
>>>> achieve.
>>>>
>>>> Thanks and Best Regards,
>>>>
>>>> Thanh.
>>>>
>>>> On 9 Aug 2023, at 19:39, Aharonu <aharon...@gmail.com> wrote:
>>>>
>>>>
>>>> Hi Evan Hisey,
>>>>
>>>> Thanks for your response.
>>>>
>>>> It is end user requested preferences. We don't need to avoid multiple
>>>> files but segerate required data in one csv file with multiple tabs.
>>>>
>>>> Example,
>>>> If I want to pull filesystem which are online and offline staus. We
>>>> need both in one file cvs ox excel but two different tabs.
>>>>
>>>> File1.cvs
>>>> Tab1: Off_fs
>>>> Tab2: On_fs
>>>>
>>>>
>>>> On Wed, 9 Aug 2023, 17:57 Evan Hisey, <ehi...@gmail.com> wrote:
>>>>
>>>>> 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
>>>>> <https://groups.google.com/d/msgid/ansible-project/9c9a828b-cf63-422a-b717-2fc6597a3361n%40googlegroups.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/CANGEjuXSx3gfKG9hCPpku9pDUx1%2BT88Um8g9oB%3DgX6eyJaE6yw%40mail.gmail.com
>>>> <https://groups.google.com/d/msgid/ansible-project/CANGEjuXSx3gfKG9hCPpku9pDUx1%2BT88Um8g9oB%3DgX6eyJaE6yw%40mail.gmail.com?utm_medium=email&utm_source=footer>
>>>> .
>>>>
>>>> --
>>>> You received this message because you are subscribed to a topic in the
>>>> Google Groups "Ansible Project" group.
>>>> To unsubscribe from this topic, visit
>>>> https://groups.google.com/d/topic/ansible-project/2PO6VyDeAxg/unsubscribe
>>>> .
>>>> To unsubscribe from this group and all its topics, 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/397BB4BA-7C21-4E58-B9D3-EA7D8FC3F3EF%40gmail.com
>>>> <https://groups.google.com/d/msgid/ansible-project/397BB4BA-7C21-4E58-B9D3-EA7D8FC3F3EF%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/CAEcFzYy_xAboowq-N6Voq_w0no9zoSMWhbByanytbhM3DGOkKA%40mail.gmail.com
>>> <https://groups.google.com/d/msgid/ansible-project/CAEcFzYy_xAboowq-N6Voq_w0no9zoSMWhbByanytbhM3DGOkKA%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-project+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/ansible-project/CANGEjuUfk4pbm%3Du9aBAP_A1K9FKb%3DgGN2uvynq%3DQ67AyLsoSZw%40mail.gmail.com
>> <https://groups.google.com/d/msgid/ansible-project/CANGEjuUfk4pbm%3Du9aBAP_A1K9FKb%3DgGN2uvynq%3DQ67AyLsoSZw%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-project+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ansible-project/CAMZqFbv0-FWEudwO-bBv7tdQXXH7nFCgQ6s0SqOpUgZMWXWkxg%40mail.gmail.com
> <https://groups.google.com/d/msgid/ansible-project/CAMZqFbv0-FWEudwO-bBv7tdQXXH7nFCgQ6s0SqOpUgZMWXWkxg%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-project+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/ansible-project/CANGEjuVtrOWioXsKUmCv8tii9hTftcUtO9LxkG_Z6Z8XRU8iSw%40mail.gmail.com.
