XLSX writer
Note in the excel, the cell is indexed by a letter and number, like this:
| A1 | B1 | C1 |
| A2 | B2 | C2 |
| A3 | B3 | C3 |
| A… | B… | C… |
In the xlsx we can use the letter and number combo way, Or by two numbers, in this way:
| 0,0 | 0,1 | 0,… |
| 1,0 | 1,1 | 1,… |
| 2,0 | 2,1 | 2,… |
| 3,0 | 3,0 | 3,… |
Syntax:
First of all, we need to import the module:
import xlsxwriter
We can use methods to work on the excel file, but we have to create a file first and pass it to a object:
workbook = xlsxwriter.Workbook('demo1.xlsx')
Then we can use mothod write add content, which can be text, number, formula or picture:
workbook.write(cell_number, content, format)
workbook.insert_image(cell_number, picture_path,format)
#coding:utf-8
import xlsxwriter
workbook = xlsxwriter.Workbook('demo1.xlsx') #create a Excel file named demo1.xlsx and pass it to the object workbook.
worksheet = workbook.add_worksheet() #create a workbook
worksheet.set_column('A:A',20) #Set the first column's width to 20 pixels
bold = workbook.add_format({'bold':True}) #create a format object "bold" to set the fold to bold
worksheet.write('A1','Hello') #write " hello" into the A1 cell.
worksheet.write('A2','World',bold)
worksheet.write('B2',u'Just a test',bold) #Write a bold text in B2 cell
worksheet.write(2,0,32) #用行列表示法写入数字'32'与'35.5'
worksheet.write(3,0,35.5) #行列表示法的单元格下标以0作为起始值， '3， 0'等价
worksheet.write(4,0,'=SUM(A3:A4) ') #求A3： A4的和， 并将结果写入'4， 0'，
worksheet.insert_image('B5','img/python-logo.png') #在B5单元格插入图片
workbook.close()
chart
Ref: http://xlsxwriter.readthedocs.io/chart.html
#coding: utf-8
import xlsxwriter
workbook = xlsxwriter.Workbook('chart.xlsx') #Create a Excel file
worksheet = workbook.add_worksheet() #create a worksheet object
chart = workbook.add_chart({'type': 'column'}) #create a chart object
#Create the header data
title = [u'Name', u'Mon', u'Tue', u'Wed', u'Thu', u'Fri', u'Sat', u'Sun', u'Ave']
channelname= [u'Business', u'News', u'Shopping', u'Sport', u'Woman'] #Channel name
#Define the speed data
data = [
[150,152,158,149,155,145,148],
[89,88,95,93,98,100,99],
[201,200,198,175,170,198,195],
[75,77,78,78,74,70,79],
[88,85,87,90,93,88,84],
]
format=workbook.add_format() #define the object "format" as a "Format object"
format.set_border(1) #set the border width as 1 pixel
format_title=workbook.add_format() #define the object "format_title" as a "Format object"
format_title.set_border(1)
format_title.set_bg_color('#cccccc') #Set background color
format_title.set_align('center')
format_title.set_bold()
format_ave=workbook.add_format()
format_ave.set_border(1)
format_ave.set_num_format('0.00')
worksheet.write_row('A1', title, format_title)
worksheet.write_column('A2', channelname, format)
worksheet.write_row('B2', data[0], format)
worksheet.write_row('B3', data[1], format)
worksheet.write_row('B4', data[2], format)
worksheet.write_row('B5', data[3], format)
worksheet.write_row('B6', data[4], format)
#Define the function for the data
def chart_series(cur_row) :
worksheet.write_formula('I'+cur_row, \
'=AVERAGE(B'+cur_row+': H'+cur_row+') ', format_ave)
chart.add_series({
'categories':'=Sheet1!$B$1:$H$1', #add First row (monday to sunday) as the X axis in the chart,
# $1 means the first row, and so on.
'values':'=Sheet1!$B$'+cur_row+':$H$'+cur_row, #The mandatory part, pass the value of each row to the chart.
'line':{'color': 'black'}, #define the line color as black
'name':'=Sheet1!$A$'+cur_row, #
})
for row in range(2, 7): #use data in row 2 ~ 6 to create the chart
chart_series(str(row) )
#chart.set_table() #Set the x axis
#chart.set_style(30) #??????, ??????
chart.set_size({'width': 577, 'height': 287}) #set the size of the chart
chart.set_title ({'name': u'Weekly report'}) #set the title for the chart
chart.set_y_axis({'name': 'Mb/s'}) #set the y axis title
worksheet.insert_chart('A8', chart) #insert the chart into A8
workbook.close()
The result will be:
Create routing map
First of all we needs to have three apps:
yum -y install tcpdump graphviz ImageMagick
Then install scapy:
pip install scapy
The python code will be like:
#coding:utf-8
import os, sys, time, subprocess
import warnings, logging
warnings.filterwarnings("ignore", category=DeprecationWarning) #block the scapy warnings
logging.getLogger("scapy.runtime") .setLevel(logging.ERROR) #block IPv6 module
from scapy.all import traceroute
domains = raw_input('Please input one or more IP/domain: ') #Ask user to type an IP
target = domains.split(' ')
dport = [80] #the target port
if len(target) >= 1 and target[0]!='':
res, unans = traceroute(target, dport=dport, retry=-2)
res.graph(target="> test.svg")
time.sleep(1)
subprocess.Popen("/usr/bin/convert test.svg test.png", shell=True) #convert svg to png format
else:
print "IP/domain number of errors, exit"