import addutils.toc ; addutils.toc.js(ipy_notebook=True)
import os
import numpy as np
import pandas as pd
from addutils import css_notebook
css_notebook()
XlsxWriter is a Python module for creating Excel XLSX files.
import xlsxwriter
Let’s start by creating a simple spreadsheet using Python and the XlsxWriter module.
current_dir = os.path.abspath(os.path.curdir)
file_path = os.path.join(current_dir, 'tmp', 'my_excel_file.xlsx')
workbook = xlsxwriter.Workbook(file_path)
Now we add a spreadsheet. If we pass a string we can specify the name of the sheet.
worksheet = workbook.add_worksheet('My First Try')
Cells can be written with any value, and can be referenced either with row and column or with an identifier like 'D3'. Remember that positional reference is zero based.
worksheet.write(0, 0, 1024)
worksheet.write('C2', 'Some Text')
0
How to assign numerical values and formulas to cells (notice that formulas are assigned as strings):
worksheet.write(5, 4, 12)
worksheet.write(6, 4, 15)
worksheet.write(7, 4, '=E6+E7')
0
It is also possible to format cells:
# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})
and use it to format a cell:
worksheet.write(7, 3, 'Total:', bold)
0
It is also possible to format the size of the cell:
# Adjust the column width.
worksheet.set_column('D:E', 25)
0
Closing the workbook, saves the .xslx file.
workbook.close()
The file can be read with pandas:
a = pd.read_excel(file_path)
as you can see the first column is interpreted as a column name in pandas. It is possible to write data to excel directly from pandas using the same engine (xlsxwriter)
# Specify a writer
writer = pd.ExcelWriter(file_path, engine='xlsxwriter')
# Create some data
expenses = {key: np.random.randint(100, size=10) for key in ['Rent', 'Gas', 'Food', 'Gym']}
expenses = pd.DataFrame(expenses)
# Write your DataFrame to a file
expenses.to_excel(writer, 'Sheet1', index=False)
# Save the result
writer.save()
This example is about sending mails using Python smtplib through an SMTP server.
sender = 'ACCOUNT E-MAIL'
password = 'PASSWORD'
recipient = 'RECIPIENT'
subject = 'A mail using Python'
body = 'This is the body of the message'
NOTE: If you want to use Gmail, please consider that it has strong security mechanism and it may block your mail.
The smtplib module defines an SMTP client session object that can be used to send mail to any Internet machine with an SMTP or ESMTP listener daemon.
The first step is to create a SMTP connection to the server. The smtplib.SMTP
class encapsulates an SMTP connection. If the optional host and port parameters are given, the SMTP connect()
method is called with those parameters during initialization. An SMTPConnectError
is raised if the specified host doesn’t respond correctly. The optional timeout parameter specifies a timeout in seconds for blocking operations like the connection attempt (if not specified, the global default timeout setting will be used).
Next we will identify ourself to an ESMTP server using HELO. The SMTP HELO clause is the stage of the SMTP protocol where SMTP servers get introduced each other. EHLO is just like HELO except that the server’s response text provides computer-readable information about the server’s abilities.
Remember Google’s SMTP server is ‘smtp.gmail.com’ and the port is 587.
import smtplib
session = smtplib.SMTP('smtp.gmail.com', 587)
Next we call SMTP.starttls
function to put the SMTP connection in TLS (Transport Layer Security) mode. All SMTP commands that follow will be encrypted. You should then call ehlo()
again. If keyfile and certfile are provided, these are passed to the socket module’s ssl()
function.If there has been no previous EHLO or HELO command this session, this method tries ESMTP EHLO first.
session.starttls()
session.ehlo()
OK, now we are safe to login to the server using SMTP.login(user, password). After successful login we use SMTP.sendmail(from_addr, to_addrs, msg[, mail_options, rcpt_options]) to send mails via Gmail.
session.login(sender, password)
headers = ["from: " + sender,
"subject: " + subject,
"to: " + recipient,
"mime-version: 1.0",
"content-type: text/html"]
headers = "\r\n".join(headers)
session.sendmail(sender, recipient, headers + "\r\n\r\n" + body)
Using duckduckgo API is possible to query duckduckgo search engine. Say we are going to search "barack obama"
import json
import urllib
searchfor = 'barack obama'
To do this we need to create a specially crafted url to pass to the google api. urllib
is a standard python
library that allows to create and retrive URL(s).
query = urllib.parse.urlencode({'q': searchfor})
url = 'http://api.duckduckgo.com/?format=json&{}'.format(query)
search_response = urllib.request.urlopen(url)
search_results = search_response.read()
Now we have a json encoded string containing our results, we are going to use json.loads
in order to get what we are interested to (say Total results and top hits).
results = json.loads(search_results.decode(encoding='UTF-8'))
related_topics = results['RelatedTopics']
hits = results['Results']
print('Related Topics: {}'.format(len(related_topics)))
print('Total results: {}'.format(len(hits)))
print('Top {} hits:'.format(len(hits)))
for h in hits:
print(' ', h['FirstURL'])
Related Topics: 34 Total results: 0 Top 0 hits:
Visit www.add-for.com for more tutorials and updates.
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.