Creating Charts in Excel with Python

import openpyxl, os
from openpyxl.styles import Font
from openpyxl.styles.colors import Color

os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')
print(os.getcwd())
 
'''Adapted from https://automatetheboringstuff.com/2e/chapter13/
Data files used can be found at this link

Charts
======
OpenPyXL supports creating bar, line, scatter, and pie charts using the data in
a sheet’s cells.

1. Create a Reference object from a rectangular selection of cells.
2. Create a Series object by passing in the Reference object.
3. Create a Chart object.
4. Append the Series object to the Chart object.
5. Add the Chart object to the Worksheet object, optionally specifying which cell
   should be the top-left corner of the chart.

You create Reference objects by calling the openpyxl.chart.Reference() function
and passing three arguments:

1. The Worksheet object containing your chart data.
2. A tuple of two integers, denoting the top-left row/column cell of the selection:
   Note: 1 is the first row, not 0.
3. A tuple of two integers, denoting the bottom-right row/column cell of the

   From top-left to bottom-right: (3, 2), (6, 4)

   ___|_A_|_B_|_C_|_D_|_E_
   _1_|_10|___|___|___|___
   _2_|_20|___|___|___|___
   _3_|_30|_x_|_x_|_x_|___
   _4_|_40|_x_|_x_|_x_|___
   _5_|_50|_x_|_x_|_x_|___
   _6_|_60|_x_|_x_|_x_|___
   _7_|_70|___|___|___|___
   _8_|_80|___|___|___|___
   _9_|_90|___|___|___|___
   10_|100|___|___|___|___
   11_|___|___|___|___|___
  
'''
# Open blank wb, create 3 tabs, delete default tab "Sheet", set sheet 'S3' as active
# Create myFont and list worksheets in workbook
wb = openpyxl.Workbook()
wb.create_sheet(index=0, title='S1')
wb.create_sheet(index=1, title='S2')
wb.create_sheet(index=2, title='S3')
del wb['Sheet']
wb.active = wb['S3']
sheet = wb.active
myFont = Font(name='Arial', color='FF0000', size=14, bold=True)
print(wb.sheetnames)

# Create some data in cells A1-A10
for i in range(1, 11):
    sheet['A' + str(i)] = (i * 10)

# 1. Create a reference object for the data in A1-A10
refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)

# 2. Create a series object from the reference object
seriesObj = openpyxl.chart.Series(refObj, title='First series')

# 3. Create a chart object (bar chart)
  #Types of charts include:
  #------------------------------
chartObj = openpyxl.chart.BarChart()
  #chartObj = openpyxl.chart.LineChart()
  #chartObj = openpyxl.chart.ScatterChart()
  #chartObj = openpyxl.chart.PieChart()
chartObj.title = 'My Chart'

# 4. Append the series object to the chart object
chartObj.append(seriesObj)

# 5. The charts top left corner is B3
sheet.add_chart(chartObj, 'B3')
wb.save('sampleChart.xlsx')
# To reopen this file:
#wb = openpyxl.load_workbook('sampleChart.xlsx')
wb.close()

Reading Excel Data from a Spreadsheet with Python

import openpyxl
import os

'''
 Adapted from https://automatetheboringstuff.com/2e/chapter13/
 Data files used can be found at this link
'''

#os.chdir('E:\\Google Drive\\python_work\\MyPythonScripts\\datafiles')
os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')

print('''
Workbooks, Sheets, Cells
========================
As a quick review, here’s a rundown of all the functions, methods, and data
types involved in reading a cell out of a spreadsheet file:

1. Import the openpyxl module.
2. Call the openpyxl.load_workbook() function.
3. Get a Workbook object.
4. Use the active or sheetnames attributes.
5. Get a Worksheet object.
6. Use indexing or the cell() sheet method with row and column keyword arguments.
7. Get a Cell object.
8. Read the Cell object’s value attribute.

''')

# Create a workbook object by opening the xlsx file
workbook = openpyxl.load_workbook('example.xlsx')

print(workbook)
#<openpyxl.workbook.workbook.Workbook object at 0x000001F0E4786250>

# Is it a workbook object?
print(type(workbook))

# What sheets are in the workbook
print(workbook.sheetnames)

# Deprecated
#sheet = workbook.get_sheet_by_name('Sheet1')
# New
sheet = workbook['Sheet1']

print(type(sheet))
#<class 'openpyxl.worksheet.worksheet.Worksheet'>

# Get the sheet's title as a string.
print(sheet.title)

# Get the active sheet.
print(workbook.active)

# A cell
print(sheet['A1'])
#<Cell 'Sheet1'.A1>

# Whats the value in A1
print(sheet['A1'].value)

# Get another cell from the sheet.
c = sheet['B1']

# Whats the value in B1
print(c.value)

 # Get the row, column, and value from the cell.
print('Row %s, Column %s is %s' % (c.row, c.column, c.value))

print('Cell %s is %s' % (c.coordinate, c.value))

# Using cell()
print(sheet.cell(row=1, column=2))

# Whats the cell value at intersection of row1 coumn2
print(sheet.cell(row=1, column=2).value)
print(sheet['B1'])

# Go through every other row:
for i in range(1, 8, 2):
    print(i, sheet.cell(row=i, column=2).value)

# Get the highest row number.
print(sheet.max_row)

# Get the highest column number.
print(sheet.max_column)

# Converting between column letters and numbers
from openpyxl.utils import get_column_letter, column_index_from_string

# Translate column 1 to a letter.
print(get_column_letter(1))
print(get_column_letter(2))
print(get_column_letter(27))
print(get_column_letter(900))
print(get_column_letter(sheet.max_column))
# Get A's number.
print(column_index_from_string('A'))
# Get AA's number.
print(column_index_from_string('AA'))
print()

# Getting Rows and Columns from the Sheets
# Get all cells from the Area A1 to C8.
print(tuple(sheet['A1':'C8']))
print()
# Print the Area's cell values using two for loops:
# The outer for goes over each row in the slice;
#   The nested for loop goes through each cell in that row
for rowOfCellObjects in sheet['A1':'C8']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate, cellObj.value, end=' ')
    print('\n--- END OF ROW ---\n')

# Access all cell values in a particular row or column
# A Worksheet object’s rows and columns attribute must be converted to lists with the list() function
# Print second column's cells.
print(list(sheet.columns)[1])
# List all cell values in column B
print('\nList all cell values in column B')
for cellObj in list(sheet.columns)[1]:
    print(cellObj.coordinate, cellObj.value)
#
# List all cell values in row 2
print('\nList all cell values in row 2')
for cellObj in list(sheet.rows)[1]:
    print(cellObj.coordinate, cellObj.value)
       

Excel Spreadsheet font manipulation with Python

import openpyxl, os
from openpyxl.styles import Font
from openpyxl.styles.colors import Color

'''
 Adapted from https://automatetheboringstuff.com/2e/chapter13/
 Data files used can be found at this link

 Customizing the Font Style of Cells
 Import the Font() function from the openpyxl.styles module.

 Table 13-2: Keyword Arguments for Font Objects
 ==============================================
 Keyword argument   Data type    Description
 ----------------   ---------    -----------
 name               String       The font name, such as 'Calibri' or 'Times New Roman'
 size               Integer      The point size
 bold               Boolean      True, for bold font
 italic             Boolean      True, for italic font

 Call Font() to create a Font object and store that Font object in a variable.
 Then assign that variable to a Cell object’s font attribute.
'''

os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')
print(os.getcwd())

wb = openpyxl.Workbook() # creates a WB with a single sheet called 'Sheet'
wb.create_sheet() # Creates a second shhet called 'Sheet1'
print(wb.sheetnames)
wb.create_sheet(index=0, title='S1')
wb.create_sheet(index=1, title='S2')
wb.create_sheet(index=2, title='S3')
print(wb.sheetnames)
# Deleting worksheets
del wb['Sheet']
del wb['Sheet1']
try:
    del wb['First']
except KeyError:
    print('Worksheet "First" does not exist')
print(wb.sheetnames)

### Font Objects
sheet = wb['S3']
# Create a font object.
italic24Font = Font(size=24, italic=True)
# Apply the font to A1.
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello!'
#
# Add a value to cell A2
sheet['A2'] = 'Bye'
# Create a font object.
myFont = Font(name='New Courier', strike=True, color='CC0000', size=16, italic=False, shadow=True, bold=True)
# Apply the font to A2.
sheet['A2'].font = myFont
sheet['A2'] = 'This is my font'
#
# Create a font object.
myFont = Font(name='New Courier', color='00FF00', size=10, shadow=True, bold=True)
# Add a value and apply the font to A3.
sheet['A3'] = 'This is my other font'
sheet['A3'].font = myFont

sheet = wb['S1']
# Set the Area's cell font values using two for loops:
# The outer for goes over each row in the slice;
#   The nested for loop goes through each cell in that row,
#    sets the font to myFont
#    and sets the value to the cells coordinate name (.coordinate)
for rowOfCellObjects in sheet['A1':'C4']:
    for cellObj in rowOfCellObjects:
        cellObj.font = myFont
        cellObj.value = str(cellObj.coordinate)

print(wb.active)
wb.save('styles.xlsx')
wb.close() 

Excel Spreadsheet Manipulation with ord() and chr() in Python

import openpyxl, os
from openpyxl.styles import Font
from openpyxl.styles.colors import Color

'''
 Adapted from https://automatetheboringstuff.com/2e/chapter13/
 Data files used can be found at this link

 Formulas
 ========
 Excel formulas can configure cells to contain values calculated from other cells.

 >>> sheet['B9'] = '=SUM(B1:B8)'
 This will store =SUM(B1:B8) as the value in cell B9.

'''

os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')
print(os.getcwd())

wb = openpyxl.Workbook() # creates a WB with a single default sheet called 'Sheet'
wb.create_sheet(index=0, title='S1')
wb.create_sheet(index=1, title='S2')
wb.create_sheet(index=2, title='S3')
# Deleting default worksheet
try:
    del wb['Sheet']
    print('Removed Worksheet "Sheet"')
except KeyError:
    print('Worksheet "Sheet" does not exist')
print(wb.sheetnames)

myFont = Font(name='Arial', color='FF0000', size=14, bold=True)

# Set sheet 'S3' as active worksheet
wb.active = wb['S3']
print(wb.active)
# Manipulate sheet 'S3'
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
# Set the formula.
sheet['A3'] = '=SUM(A1:A2)'
print(sheet['A3'].value)

# Set sheet 'S2' as active worksheet
wb.active = wb['S2']
print(wb.active)
sheet = wb.active
'''
# Set the Area's cell values using two for loops:
# The outer for goes over each row in the slice;
#   The nested for loop goes through each cell in that row,
#    sets the font to myFont
#    and sets the value to val
'''
for row in sheet['A1':'C4']:
    val = 0
    alpha = ord('A')
    chr(alpha)
    for cell in row:
        val += 1
        cell.value = val
        cell.font = myFont
'''
Sets the row segment (Area A5 to C5) to the sum of the cells in its column
ord('A') returns the ascii integer value for 'A' --> 65
chr(65) returns character value for ascii integer value 65 --> 'A'
Set alpha in the loop to increment by 1 and then use chr(alpha) to move from
column A to B to C. This loop accomplishes the same task as doing this:

 sheet['A5'] = '=SUM(A1:A4)'
 sheet['B5'] = '=SUM(B1:B4)'
 sheet['C5'] = '=SUM(C1:C4)'
'''
number = 5
i = 0
for row in sheet['A5':'C5']:
    for cell in row:
        alpha = ord('A') + i
        alphaNum = '%s%s' %(chr(alpha), number)
        print('%s   %s   %s' %(alphaNum, alpha, i))
        sheet[alphaNum] = '=SUM(%s1:%s4)' %(chr(alpha), chr(alpha))
        i += 1


wb.save('formulas.xlsx')
wb.close() 

Create and save MS Excel Documents in Python

import openpyxl, pprint, os

"""
Writing Excel Documents
 Creating and Saving Excel Documents

"""
### Change dir to datafiles
os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')
# Verify datafiles is pwd
print(os.getcwd())

### Create a blank workbook.
wb = openpyxl.Workbook()
# It starts with one sheet.
print(wb.sheetnames)
sheet = wb.active
# Change the sheet name by storing a new string in its title attribute.
sheet.title = 'Duke Today Sheet'
print(wb.sheetnames)
print(sheet.title)

### Creating and Removing Sheets
# Sheets can be added and removed with the create_sheet() method and del operator.
# Add a new sheet.
wb.create_sheet()
print(wb.sheetnames)
# The create_sheet() method returns a new Worksheet object named SheetX, which by
# default is set to be the last sheet in the workbook. Optionally, the index and
# name of the new sheet can be specified with the index and title keyword arguments.
# Create a new sheet at index 0 and index 2
wb.create_sheet(index=0, title='First Sheet')
wb.create_sheet(index=2, title='Middle Sheet')
print(wb.sheetnames)
# Deleting a worksheet
del wb['Spam Bacon Eggs Sheet']
print(wb.sheetnames)

### Writing Values to Cells
sheet = wb['Sheet']
sheet['A1'] = 'Hello, world!'
print(sheet['A1'].value)

### Save workbook
# The spreadsheet file will not be saved until you call the save() workbook method
# with a name string.
wb.save('Workbook.xlsx')

Setting LACP short/fast Timeout on ArubaOS-Switch/ProCurve Interfaces For LAGs/Trunks/Ether-Channel

Trunking (LAG)

Trunking in HP terms is bundling 2 or more ports together to create a larger bandwidth port. In Cisco terms it is referred to as Etherchannel. The connecting switch must also be configured for trunking.

#trunk 1-4 trk1 trunk (Creates a 4 port bundled static trunk)

#trunk 1-4 trk1 lacp (Creates a 4 port bundled LACP trunk)

#show trunk   (Shows Port name, connection speeds, trunk group and type trunk | LACP)

#show trunk 4 (Shows trunk information for the interface 4 member)

#show lacp    (Displays information about lacp status on port members)

Verifying Link Partner is using LACP Fast/Short vs. Slow/Long

I have not discovered if there is a parameter that allows you to set the Procurve to LACP short/long per port as no other switching platforms. The only way I know to do so this is to set the Procurve to be a passive LACP members, ensure the link partner is using active LACP and set the link partner (6300-AOS-CX in this case) to LACP short/fast. This will force the Procurve to set its interfaces to LACP short/fast.

Procurve

3810M(config)# trunk 1-2 trk127 lacp

3810M(config)# int 1-2

3810M(eth-1-2)# lacp static active 

3810M(eth-1-2)# exit

Note: This sets the interface to be a static active LACP member which is kind of counter-intuitive. But if you use the 'lacp active' setting then this sets the ports to be part of a dynamic (or dyn) LAG which is not what we want here.  

3810M(config)# show lacp peer 

LACP Peer Information.

System ID: 70106f-904a80 

  Local  Local                          Port      Oper    LACP     Tx   

  Port   Trunk  System ID         Port  Priority  Key     Mode     Timer

  ------ ------ ----------------- ----- --------- ------- -------- -----

  1      Trk127 883a30-96f0c0     2     1         127     Active   Fast <-Partners LACP is set to fast/short

  2      Trk127 883a30-96f0c0     66    1         127     Active   Fast <-Partners LACP is set to fast/short

Link Partner

With the link partner (6300-AOS-CX in this case) set to LACP short/fast and LACP Active and the Procurve set to LACP active the link Partner sees the Procurve interfaces set to long/slow. I do not know if there is a way to manually set the Procurve to short/fast manually.

6300(config-lag-if)# show lacp interfaces 

State abbreviations :

A - Active        P - Passive      F - Aggregable I - Individual

S - Short-timeout L - Long-timeout N - InSync     O - OutofSync

C - Collecting    D - Distributing 

X - State m/c expired              E - Default neighbor state

Actor details of all interfaces: 

------------------------------------------------------------------------------

Intf    Aggr       Port  Port  State   System-ID         System Aggr Forwarding

        Name       Id    Pri                             Pri    Key  State     

------------------------------------------------------------------------------

1/1/1   lag127     2     1     ASFNCD  88:3a:30:96:f0:c0 65534  127  up        

2/1/1   lag127     66    1     ASFNCD  88:3a:30:96:f0:c0 65534  127  up        

Partner details of all interfaces: Procurve in this case

------------------------------------------------------------------------------

Intf    Aggr       Port  Port  State   System-ID         System Aggr           

        Name       Id    Pri                             Pri    Key            

------------------------------------------------------------------------------

1/1/1   lag127     1     0     ALINCD  70:10:6f:90:4a:80 19072  0              

2/1/1   lag127     2     0     ALINCD  70:10:6f:90:4a:80 19072  0              

Forcing the Procurve to be LACP short/fast

With Link partner (6300-AOS-CX in this case) set to LACP short/fast and LACP Active and the Procurve set to LACP passive the Procurve will automatically match the settings on the Active LACP link members and the link Partner sees the Procurve interfaces set to short/fast. This is the only way to get the Procurve to set to LACP short/fast (that I know of.)

On the Procurve:

3810M(eth-1-2)# lacp static passive 

3810M(eth-1-2)# exit

On the link partner (6300-AOS-CX in this case)

6300(config-lag-if)# show lacp interfaces 

State abbreviations :

A - Active        P - Passive      F - Aggregable I - Individual

S - Short-timeout L - Long-timeout N - InSync     O - OutofSync

C - Collecting    D - Distributing 

X - State m/c expired              E - Default neighbor state

Actor details of all interfaces:

------------------------------------------------------------------------------

Intf    Aggr       Port  Port  State   System-ID         System Aggr Forwarding

        Name       Id    Pri                             Pri    Key  State     

------------------------------------------------------------------------------

1/1/1   lag127     2     1     ASFNCD  88:3a:30:96:f0:c0 65534  127  up        

2/1/1   lag127     66    1     ASFNCD  88:3a:30:96:f0:c0 65534  127  up        

Partner details of all interfaces: Procurve in this case

------------------------------------------------------------------------------

Intf    Aggr       Port  Port  State   System-ID         System Aggr           

        Name       Id    Pri                             Pri    Key            

------------------------------------------------------------------------------

1/1/1   lag127     1     0     PSINCD  70:10:6f:90:4a:80 19072  0              

2/1/1   lag127     2     0     PSINCD  70:10:6f:90:4a:80 19072  0              

6300(config-lag-if)#

Hex to Dec

FFFA = (15*16^3)+(15*16^2) + (15*16^1) + (10*16^0)

 (15 * 16^3) = 61440

 (15 * 16^2) = 3840

 (15 * 16^1) = 240

 (10 * 16^0) = 10

FFFA = 61440+3840+240+10 = 65530

FIPS mode in Comware Devices

FIPS mode is available, in which the device provides only a limited set of its functionality so that the security is then in compliance with NIST FIPS 140-2. After enabling the FIPS mode, at least the following takes place:

• FTP, TFTP, HTTP servers are disabled.

• Remote access via Telnet protocol is disabled.

• SNMPv3 is the only available version of SNMP.

• The SSL server supports only TLS1.0.

• The SSH server does not support SSHv1 clients.

• SSH supports only RSA.

• The generated RSA key pairs must have a modulus length of 2048 bits. The generated DSA key pair must have a modulus of at least 1024 bits.

• SSH, SNMPv3, IPsec, and SSL do not support DES, 3DES, RC4, or MD5.

Why Not Use VLAN 1?

Seperates user and management traffic. 
 
ACL's or security features applied to an interface might not always affect traffic going to the actual device. Therefore, add ACLs to the vty lines in case traffic gets routed to the management interface that should still be blocked unless from authorized IP addresses.

You can't actually stop VLAN 1 sending LLDP/STP/LACP/VRRP/BPDU/CDP/PaGP/VTP/GVRP/etc. Across trunk (802.1Q) links, but what you can do is:

• Create a VLAN, e.g., VLAN 999, to put all ports that are unused into.

• There is no need to create an L3 SVI for VLAN 999 because it is only for unused ports.

• Change the native VLAN from VLAN 1 to another VLAN.

• Again this VLAN does not need an L3 SVI because the native VLAN does not need to be routed. 

• Create a separate VLAN (other than VLAN 1) to manage your switches.

• From the console, shutdown the VLAN 1 interface on each switch and add an L3 SVI for the new VLAN.

• Move user ports in VLAN 1 into different VLANs.

After this, VLAN 1 will only be used for L2 management protocols (LLDP/STP/LACP/VRRP/BPDU,etc.)

ArubaOS-Switch QinQ Solution with the 3810M

Problem

I have a single uplink (LAG) from my core to my QinQ provider Cisco switch. If I don't add QinQ to my replacement 8400 ArubaOS-CX cores, how can I service multiple svlans with a single uplink from to customer vlans on an ArubaOS 3810M ?

Solution

Link ports as shown in Topology with patch cable jumpers and configure the QinQ switch as below

Topology

Enable qinq mixed-vlan mode and let switch reboot

Procurve-QinQ(config)# qinq mixedvlan

 tag-type              Configure qinq tag-type

<cr>

This command will reboot the device. Any prior configuration

on this config file will be erased and the device will boot up

with a default configuration for the new qinq mode.

Do you want to continue? [y/n] y

Disable BPDU Throttling

no spanning-tree bpdu-throttle

Disable gvrp on interfaces added to svlans

interface 1-3,10,12,14

   unknown-vlans disable

  

Add ports to svlan’s 150, 151, 152

svlan 150

   tagged 1

   untagged 10

svlan 151

   tagged 2

   untagged 12

  

svlan 152

   tagged 3

   untagged 14

Add the interfaces to the trunk

trunk 47-48 trk1 lacp

Tell interfaces what type of qinq port they are

interface 1-3,10,12,14

   qinq port-type provider-network

interface 9,11,13,trk1

   qinq port-type customer-network

Add Dot1q VLANs and Ports

vlan 50

   tagged 9,Trk1

   no ip address

  

vlan 51

   tagged 11,Trk1

   no ip address

  

vlan 52

   tagged 13,Trk1

   no ip address

  

I can now ping across the svlan from Host to Core and back again

Hosts to Core

Host1(config)# show ip

  -------------------- + ---------- -------------- --------------

  VLAN50               | Manual     10.0.50.254     255.255.255.0

Host1# ping 10.0.50.1

10.0.50.1 is alive, time = 1 ms

Host2(config)# show ip

  -------------------- + ---------- -------------- --------------

  VLAN51               | Manual     10.0.51.254     255.255.255.0

Host2# ping 10.0.51.1

10.0.51.1 is alive, time = 1 ms

Host3(config)# show ip

  -------------------- + ---------- -------------- --------------

  VLAN52               | Manual     10.0.52.254     255.255.255.0

Host3# ping 10.0.52.1

10.0.52.1 is alive, time = 1 ms

Core to Hosts

Core(config)# ping 10.0.50.254 

10.0.50.254 is alive, time = 1 ms

Core(config)# ping 10.0.51.254

10.0.51.254 is alive, time = 1 ms

Core(config)# ping 10.0.52.254

10.0.52.254 is alive, time = 1 ms