Mail module - multiple tabs in csv file

[Aharonu]

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

[Thanh Nguyen Duc]

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:



--
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.

[Aharonu]

Hi  Thanh Nguyen Duc,

Thanks for quick response. May I get that reference details so I will give try for my requirement.

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/655EE3A2-3194-4219-B7D2-C7236C831F11%40gmail.com.

[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 <ahar...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CANGEjuWWTHkMSuTRJgKju70S9GOBSNok1oxndCkBr_aMa0vqGw%40mail.gmail.com.

[Aharonu]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/C95EF81F-7406-48DD-A8ED-A4C59F5B7850%40gmail.com.

[Evan Hisey]

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?

[Aharonu]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/9c9a828b-cf63-422a-b717-2fc6597a3361n%40googlegroups.com.

[Thanh Nguyen Duc]

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 <ahar...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CANGEjuXSx3gfKG9hCPpku9pDUx1%2BT88Um8g9oB%3DgX6eyJaE6yw%40mail.gmail.com.

[Aharonu]

If not with csv, may I know if possible with exceel or xlsx..etc.    Thx

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/397BB4BA-7C21-4E58-B9D3-EA7D8FC3F3EF%40gmail.com.

[Evan Hisey]

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.

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-proje...@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.

[Aharonu]

Noted.

Thank you!

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAEcFzYy_xAboowq-N6Voq_w0no9zoSMWhbByanytbhM3DGOkKA%40mail.gmail.com.

[Sumanth Parakala]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CANGEjuUfk4pbm%3Du9aBAP_A1K9FKb%3DgGN2uvynq%3DQ67AyLsoSZw%40mail.gmail.com.

[Aharonu]

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!

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAMZqFbv0-FWEudwO-bBv7tdQXXH7nFCgQ6s0SqOpUgZMWXWkxg%40mail.gmail.com.

[Sumanth Parakala]

Hi,

Maybe deprecated, but this should be fine. You may use  xlsx_writer.close() and also check.

Speed of excecution depends on no: of rows. 

Never played with Colors, but font and book, worksheet modifications can be done using Xlsxwriter modules in pd.

pd.ExcelWriter.book pd.ExcelWriter.sheets etc, need to explore.

[Aharonu]

Thank you Sumanth. 

I saw coloring to do for file level but as I am new of it, not got how to do it for tabs wise(sheet1, sheet2)

How to make it for tabs level in one file?

Example I got for doing file level:

Cell Background and Foreground Colors

Two important properties of Format object are bg_color and fg_color to set the background and foreground color of a cell.

Example

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.set_column('B:B', 30)

f1=wb.add_format({'bg_color':'red', 'font_size':20})
f2=wb.add_format({'bg_color':'#0000FF', 'font_size':20})

ws.write('B1', 'Hello World', f1)
ws.write('B2', 'HELLO WORLD', f2)
wb.close()

Regards,

Aharonu