Mail module - multiple tabs in csv file

Archives Ansible Project
1

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: [jane@example.net](mailto:jane@example.net) (Jane Jolie)
    to: John Doe <[j.d@example.org](mailto:j.d@example.org)> 
    attach: ./**file1.csv**
  delegate_to: localhost
2

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.

3

Hi Thanh Nguyen Duc,

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

4

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.

5

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

6

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?

7

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

8

Technically csv doesn’t support multiple tabs so it is not able to achieve.

Thanks and Best Regards,

Thanh.

9

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

10

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.

11

Noted.

Thank you!

12

Try using a python to merge csvs into xlsx and shell module before mail module.

13

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!

14

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.

15

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