3.4. Excel file preparation from facts files

• In this method we do not prepare the Excel Database.

• Instead, we use the Excel files (aka: -clean.xlsx) auto generated by facts-finder package. And convert it to the Excel Database desired for pyVig.

---

3.4.1. Pre-Requisites

Here below are two Pre-Requisite steps to be done before generating pyVig understandable Excel generation.

3.4.1.1. Capture

Use capture-it to capture a few commands output and to prepare commands parsed Excel Files as a first step.

• Use capture-it package for this purpose.

• Refer: capture-it documentation

3.4.1.2. Clean files generation

Using the captures and parsed Excel files, generate clean excel files (-clean.xlsx).

• Use facts_finder package for this purpose.

• Refer: facts-finder documentation

---

3.4.2. Create pyVig Excel file

Create a python file using below snippet steps.

3.4.2.1. module imports

# -------------------------------------
# general imports
# -------------------------------------
from pyVig import pyVig, pyVig_gui, DFGen
from nettoolkit import *
import os

# -------------------------------------
# custom imports
# -------------------------------------
# custom functions imports - part 1
# There must be two custom functions defined and to be imported
# 1. a function to generate - hierarchical order series
# 2. a function to generate - switch type series
# ... add more as required
from custom_pyvig import hierarchical_order_series, sw_type_series

# custom functions imports - part 2
# There can be n-number of optional var custom functions defined and can be imported for additional informations on device. such as 'serial', 'model'  from 'var' tab of -clean excel file.
# ... add more as necessary
from custom_pyvig.optional_var import get_hostname, get_dev_model, get_dev_serial

3.4.2.2. Generate pyVig readable Excel Database

Add below snippet, after modifing inputs.

# Define capture files #
# ----------------------
capture_folder = "Capturefolder/where/all/clean-files-stored"
files = [f'{capture_folder}/{file}'
         for file in os.listdir(capture_folder)
         if file.endswith("-clean.xlsx") ]

# create a DataFrameGenerator object instance #
# ---------------------------------------------
DFG = DFGen(files)

# add - custom attributes, functions, var_functions to it #
# ---------------------------------------------------------

DFG.custom_attributes(                                          # optional
        default_stencil='fullpath/where/stencilfiles/stored',
        default_x_spacing=3,
        default_y_spacing=5,
        line_pattern_style_separation_on='int_filter',   # other option 'media_type', based on device model
        line_pattern_style_shift_no=3,     # (if above not None, and should be > 0)
        #-- custom connector options --
        connector_type='curved',
        color='green',
        weight=5,
        pattern=2,
)

DFG.custom_functions(
        hierarchical_order=hierarchical_order_series,    # mandatory: custom function
        item=sw_type_series,    # mandatory: custom function
        # .add more as desired
)

DFG.custom_var_functions(
        hostname=get_hostname,              # mandatory
        device_model=get_dev_model,         # optional
        serial_number=get_dev_serial,       # optional
        # .add more as desired
)

# go thru all provided files,  generate a single pyVig readable Excel file #
# --------------------------------------------------------------------------
DFG.run()
write_to_xl(XL_PYVIG_OP_FILE, DFG.df_dict, index=False, overwrite=True)

Save it as a python file, and execute it.

• An excel file with provided XL_PYVIG_OP_FILE name will be generated.

• Verify it and update as necessary.

Note

In above example code, we have imported a few custom fuctions from custom project, build your own project and functions to generate the excel output customize as your need. Feel Free to experiment

Since custom code differs client to client and are proprietory, I didn’t revealed custom codes here I used.

---

3.4.3. Show time

Generate Visio using pyVig Excel Database created above.

Now, we can create visio using Either of below method mentioned in previous pages,

• CLI Execution

• GUI Execution

Where provide, as defined above static/global variables (in CLI mode)

• ‘data_file’: XL_PYVIG_OP_FILE,

• ‘stencil_folder’: STENCIL_FOLDER,

• ‘default_stencil’: DEFAULT_STENCIL,

Tip

Do not interrupt the visio application while visio generation is inprogress.

Once Finished save the file as required.

Verify drawing, Modify Excel Database if need adjustments, re-run pyVig() to regenerate drawing.