Excel Module¶

Public Methods¶

Python class to serialize Excel spreadsheet to Python structures

Allow to extract data from an Excel sheet to convert it to different This class supports import from different tab from Excel file and can serialize both: table and list

class inetsix.excel.ExcelSerializer(excel_path=None, sheet='Sheet1', has_header=True, nb_columns=0)[source]¶

Convert Excel data into python structures.

Allow to extract data from an Excel sheet to convert it to different This class supports import from different tab from Excel file and can serialize both: table and list

Example:

Output example for a table

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialize_table(sheet="Topology", nb_columns=6)
>>> print table.get_data(sheet="Topology")
[   {   'id': '1',
    u'local_device': u'dev1',
    u'local_port': u'port1',
    u'local_port_name': u'et-0/0/0',
    u'remote_device': u'dev2',
    u'remote_port': u'port1',
    u'remote_port_name': u'et-0/0/0'},
{   'id': '2',
    u'local_device': u'dev1',
    u'local_port': u'port2',
    u'local_port_name': u'et-0/0/1',
    u'remote_device': u'dev3',
    u'remote_port': u'port1',
    u'remote_port_name': u'et-0/0/1'},
{   'id': '3',
    u'local_device': u'dev2',
    u'local_port': u'port3',
    u'local_port_name': u'et-0/0/2',
    u'remote_device': u'dev4',
    u'remote_port': u'port1',
    u'remote_port_name': u'et-0/0/2'}]

Example:

Output example for a list

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialized_list(sheet="List")
>>> print table.get_data(sheet="List")
{   'asn_base': '65000',
    'bgp_export': 'underlay-export',
    'bgp_group': 'underlay',
    'bgp_import': 'underlay-import',
    'mtu_phy_int': ['9200', '1000']
}

Todo

Create a method to locate data in Excel Sheet.

_ExcelSerializer__find_header(sheet=None, nb_columns=2)¶

Look for array in the table and provide list of them.

Extract table header and create a dict() to store them with a standardize name: lower and replace space by “_”

Parameters:	sheet (str.) – Sheet name to read (default=None, optional)) nb_columns (int.) – Number of column to read (optional)
Returns:	list of headers found in given sheet
Return type:	list

_serialize_cell(xlsCellStr=None)[source]¶

Serialize a cell by doing some cleanup.

Transform multi lines content to a single line str.

Parameters:	xlsCellStr (str) – content of a cell to serialize and cleanup (default=None)
Returns:	Single line string
Return type:	str

_string_cleanup(string)[source]¶

Cleanup a string to lower and remove space.

Parameters:	string (str) – String to cleanup
Returns:	Cleanup string
Return type:	str

get_data(sheet=None)[source]¶

Provide a read only access to table data

Note

This function is a wrapper to get_yaml() function

Example:

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialized_list(sheet="List")
>>> print table.get_data(sheet="List")
{   'asn_base': '65000',
    'bgp_export': 'underlay-export',
    'bgp_group': 'underlay',
    'bgp_import': 'underlay-import',
    'mtu_phy_int': ['9200', '1000']
}

Parameters:	sheet (str.) – Sheet name to read (default=None, optional))
Returns:	A YAML structure with data found in given sheet
Return type:	dict

get_header()[source]¶

Provide a read only access to header table data.

Example:

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialize_table(sheet="Topology", nb_columns=6)
>>> print table.get_header()
[   'asn_base',
    'bgp_export',
    'bgp_group',
    'bgp_import',
    'mtu_phy_int'
]

Returns:	A YAML structure with headers found in given sheet
Return type:	list

get_yaml(sheet=None)[source]¶

Return a per sheet Python structure compatible with YAML language

Example:

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialized_list(sheet="List")
>>> print table.get_yaml(sheet="List")
{   'asn_base': '65000',
    'bgp_export': 'underlay-export',
    'bgp_group': 'underlay',
    'bgp_import': 'underlay-import',
    'mtu_phy_int': ['9200', '1000']
}

Parameters:	sheet (str.) – Sheet name to read (default=None, optional))
Returns:	A YAML structure with data found in given sheet
Return type:	dict

get_yaml_all()[source]¶

Get Yaml data.

Provide a dictionary of all data found in all sheets parsed in this Excel file.

Example:

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialized_list(sheet="List")
>>> table.serialize_table(sheet="Topology", nb_columns=6)
>>> table.get_yaml_all()
{   'List': {
        'asn_base': '65000',
        'bgp_export': 'underlay-export',
        'bgp_group': 'underlay',
        'bgp_import': 'underlay-import',
        'mtu_phy_int': ['9200', '1000']
    },
    'Topology': [
       {   'id': '1',
            u'local_device': u'dev1',
            u'local_port': u'port1',
            u'local_port_name': u'et-0/0/0',
            u'remote_device': u'dev2',
            u'remote_port': u'port1',
            u'remote_port_name': u'et-0/0/0'},
        {   'id': '2',
            u'local_device': u'dev1',
            u'local_port': u'port2',
            u'local_port_name': u'et-0/0/1',
            u'remote_device': u'dev3',
            u'remote_port': u'port1',
            u'remote_port_name': u'et-0/0/1'},
        {   'id': '3',
            u'local_device': u'dev2',
            u'local_port': u'port3',
            u'local_port_name': u'et-0/0/2',
            u'remote_device': u'dev4',
            u'remote_port': u'port1',
            u'remote_port_name': u'et-0/0/2'}]
    ]
}

Parameters:	sheet (str.) – Sheet name to read (default=None, optional))
Returns:	A YAML structure with data found in sheets parsed previously
Return type:	dict

serialize_list(sheet=None, nb_columns=2)[source]¶

Serialize a list into a YAML compliant format.

Example:

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialized_list(sheet="List")

Note

Values are also saved in self._data_array for later use.

Parameters:	sheet (str.) – Sheet name to read (default=None, optional)) nb_columns (int.) – Number of column to read (optional)
Returns:	YAML compatible dictionnary with all extracted values from the list
Return type:	dict

serialize_table(sheet=None, nb_columns=2, has_header=True)[source]¶

Serialize Excel file into python structure.

Open Excel file defined in self.__filename and look for data Because columns are using letters instead of numbers, it is required to convert decimal values to alphabet letters.

For every line, function is reading every cell and move them to a dict entry Once line is completely read, dict is appended to a list of all lines.

Example:

>>> table = ExcelSerializer(excel_path='../excel/input.xlsx')
>>> table.serialize_table(sheet="Topology", nb_columns=6)

Note

Values are also saved in self._data_array for later use.

Parameters:	sheet (str.) – Sheet name to read nb_columns (int.) – Number of column to read has_header (bool.) – Boolean to enable header finding.
Returns:	YAML compatible list of all extracted lines
Return type:	list

Private Methods¶

Python class to serialize Excel spreadsheet to Python structures

Allow to extract data from an Excel sheet to convert it to different This class supports import from different tab from Excel file and can serialize both: table and list