# Mail module - multiple tabs in csv file

**URL:** <https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314>\
**Category:** Ansible Project\
**Created:** [August 9, 2023, 10:54am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314 "2023-08-09T10:54:55Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 9, 2023, 10:54am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/1 "2023-08-09T10:54:55Z")

</div>

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

```auto

- 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

```

---

<div class="post-metadata">

**Author:** ![Thanh\_Nguyen\_Duc](https://avatars.discourse-cdn.com/v4/letter/t/c89c15/32.png) [@Thanh\_Nguyen\_Duc](https://forum.ansible.com/u/Thanh_Nguyen_Duc)\
**Post date:** [August 9, 2023, 10:59am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/2 "2023-08-09T10:59:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 9, 2023, 11:04am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/3 "2023-08-09T11:04:03Z")

</div>

Hi Thanh Nguyen Duc,

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

---

<div class="post-metadata">

**Author:** ![Thanh\_Nguyen\_Duc](https://avatars.discourse-cdn.com/v4/letter/t/c89c15/32.png) [@Thanh\_Nguyen\_Duc](https://forum.ansible.com/u/Thanh_Nguyen_Duc)\
**Post date:** [August 9, 2023, 11:45am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/4 "2023-08-09T11:45:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 9, 2023, 11:56am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/5 "2023-08-09T11:56:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Evan\_Hisey](https://avatars.discourse-cdn.com/v4/letter/e/ce73a5/32.png) [@Evan\_Hisey](https://forum.ansible.com/u/Evan_Hisey)\
**Post date:** [August 9, 2023, 12:27pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/6 "2023-08-09T12:27:43Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 9, 2023, 12:38pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/7 "2023-08-09T12:38:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Thanh\_Nguyen\_Duc](https://avatars.discourse-cdn.com/v4/letter/t/c89c15/32.png) [@Thanh\_Nguyen\_Duc](https://forum.ansible.com/u/Thanh_Nguyen_Duc)\
**Post date:** [August 9, 2023, 12:41pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/8 "2023-08-09T12:41:42Z")

</div>

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

Thanks and Best Regards,

Thanh.

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 9, 2023, 12:44pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/9 "2023-08-09T12:44:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Evan\_Hisey](https://avatars.discourse-cdn.com/v4/letter/e/ce73a5/32.png) [@Evan\_Hisey](https://forum.ansible.com/u/Evan_Hisey)\
**Post date:** [August 9, 2023, 12:47pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/10 "2023-08-09T12:47:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 9, 2023, 12:53pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/11 "2023-08-09T12:53:10Z")

</div>

Noted.

Thank you!

---

<div class="post-metadata">

**Author:** ![Sumanth\_Parakala](https://avatars.discourse-cdn.com/v4/letter/s/b77776/32.png) [@Sumanth\_Parakala](https://forum.ansible.com/u/Sumanth_Parakala)\
**Post date:** [August 9, 2023, 1:15pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/12 "2023-08-09T13:15:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 10, 2023, 5:57pm UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/13 "2023-08-10T17:57:33Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Sumanth\_Parakala](https://avatars.discourse-cdn.com/v4/letter/s/b77776/32.png) [@Sumanth\_Parakala](https://forum.ansible.com/u/Sumanth_Parakala)\
**Post date:** [August 11, 2023, 6:54am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/14 "2023-08-11T06:54:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AharonuReddy](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/aharonureddy/32/5464_2.png) [@AharonuReddy](https://forum.ansible.com/u/AharonuReddy)\
**Post date:** [August 11, 2023, 7:11am UTC](https://forum.ansible.com/t/mail-module-multiple-tabs-in-csv-file/38314/15 "2023-08-11T07:11:12Z")

</div>

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

```auto
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