achoudh5 / manipulating_excel_sheet

Real world application of Manipulating Excel Sheet using python.

Huge Shout Out 👏

The person who owns this repo is mere face of it, it's you who are making a difference! Keep contributing :) Please make a comment if you see I am missing any tag or label which might miss your contribution, I don't want that to happen that was the whole point.

• Shout out to Ishita Mehta for pointing out some missing labels!
• Add yourself to Hall of fame

Change the state of your Issue (keep it professional)

Join my ZenHub

Add Plugin Zenhub

Manipulating_Excel_Sheet 📁

• Being a Network Security Professional, I am currently working on this exciting project which makes the NetOps work easy
• Challenge is to make the users use a tool which is easy to navigate considering they don't have software mindset!
• Think like a Noob, there are no assumptions for this project :)
• Convey your message with minimal Knowledge transfer
• I am all ears to suggestions, don't hesitate to suggest

All files below are samples only

The name and position of columns will be same, don't worry about it but it would be great if you can identify the heading from row 1 and use from that perspective

There might be case where the Hacktobefest_Expected_Output.xlsx might have colors which don't precisely map to Hacktoberfest_database.xlsx (I tried a sample, please bare with this for now. I'll update precisely the correct colors. Incase you understand the requirements, feel free to grab the issues)

Hacktoberfest_Inputt.xlsx

Hacktoberfest_Expected_Outcome.xlsx

Hackoberfest_Database.xlsx

Sample of how zip.py is working currently

• Each Arrow represents anomaly from expected output (check below image)

• Each number represents an issue faced, you can create an issue and raise a PR!

Expected of zip.py

Sample Expectation of GUI

Current GUI using tKinter on Windows and MAC

Windows GUI

MAC GUI (Needs Help)

Guide to Github & Git:

Make sure you have a GitHub account. In case you don't have one, you can create your account by visiting https://github.com/ and clicking on Sign up option at the top right corner.

1. Register yourself for Hacktoberfest

Link to register: https://hacktoberfest.digitalocean.com/

Click on "Start Hacking" and add your GitHub account.

2. Star and Fork this Repository

You can star and fork this repository on GitHub by navigating at the top of this repository.

GitHub repository URLs will reference both the username associated with the owner of the repository, as well as the repository name. For example, acmbvp is the owner of the Hacktoberfest repository, so the GitHub URL for this project is:

https://github.com/achoudh5/Manipulating_Excel_Sheet

When you’re on the main page of a repository, you’ll see a button to "Star" and “Fork” the repository on the upper right-hand side of the page, underneath your user icon.

3. Clone the Repository

To make your own local copy of the repository you would like to contribute to, let’s first open up a terminal window.

We’ll use the git clone command along with the URL that points to your fork of the repository.

This URL will be similar to the URL above, except now it will end with .git. In the cloud_haiku example above, the URL will look like this: https://github.com/achoudh5/Manipulating_Excel_Sheet.git

You can alternatively copy the URL by using the green “Clone or download” button from your repository page that you just forked from the original repository page. Once you click the button, you’ll be able to copy the URL by clicking the binder button next to the URL:

Once we have the URL, we’re ready to clone the repository. To do this, we’ll combine the git clone command with the repository URL from the command line in a terminal window:

git clone https://github.com/your-username/Manipulating_Excel_Sheet.git

4. Create a New Branch

To create your branch, from your terminal window, change your directory so that you are working in the directory of the repository. Be sure to use the actual name of the repository (i.e. Manipulating_Excel_Sheet) to switch into that directory.

cd Manipulating_Excel_Sheet

Now, we’ll create our new branch with the git branch command. Make sure you name it descriptively so that others working on the project understand what you are working on.

git branch new-branch

Now that our new branch is created, we can switch to make sure that we are working on that branch by using the git checkout command:

git checkout new-branch

Once you enter the git checkout command, you will receive the following output:

Output:

Switched to branch 'new-branch'

At this point, you can now modify existing files or add new files to the project on your own branch.

Make Changes Locally

Once you have modified existing files or added new files to the project, you can add them to your local repository, which you can do with the git add command. Let’s add the -A flag to add all changes that we have made:

git add -A or git add .

Next, we’ll want to record the changes that we made to the repository with the git commit command.

The commit message is an important aspect of your code contribution; it helps the other contributors fully understand the change you have made, why you made it, and how significant it is. Additionally, commit messages provide a historical record of the changes for the project at large, helping future contributors along the way.

If you have a very short message, you can record that with the -m flag and the message in quotes:

Example:

git commit -m "Updated Readme.md"

At this point you can use the git push command to push the changes to the current branch of your forked repository:

Example:

git push --set-upstream origin new-branch

5. Update Local Repository

While working on a project alongside other contributors, it is important for you to keep your local repository up-to-date with the project as you don’t want to make a pull request for code that will cause conflicts. To keep your local copy of the code base updated, you’ll need to sync changes.

We’ll first go over configuring a remote for the fork, then syncing the fork.

6. Configure a Remote for the Fork

Next up, you’ll have to specify a new remote upstream repository for us to sync with the fork. This will be the original repository that you forked from. you’ll have to do this with the git remote add command.

git remote add upstream https://github.com/achoudh5/Manipulating_Excel_Sheet.git

In this example, // upstream // is the shortname we have supplied for the remote repository since in terms of Git, “upstream” refers to the repository that you cloned from. If you want to add a remote pointer to the repository of a collaborator, you may want to provide that collaborator’s username or a shortened nickname for the shortname.

7. Sync the Fork

Once you have configured a remote that references the upstream and original repository on GitHub, you are ready to sync your fork of the repository to keep it up-to-date.

To sync your fork, from the directory of your local repository in a terminal window, you’ll have to use the // git fetch // command to fetch the branches along with their respective commits from the upstream repository. Since you used the shortname “upstream” to refer to the upstream repository, you’ll have to pass that to the command:

git fetch upstream

Switch to the local master branch of our repository:

git checkout master

Now merge any changes that were made in the original repository’s master branch, that you will access through your local upstream/master branch, with your local master branch:

git merge upstream/master

8. Create Pull Request

At this point, you are ready to make a pull request to the original repository.

Navigate to your forked repository, and press the “New pull request” button on your left-hand side of your Repo page.

No contribution is small,don't forget to update Contributing.md file

Raise a PR 🚀 and get Hacktoberfest 2020 T-Shirt 😄

Thank you for your contribution 👏

Do Star ⭐ this repository

manipulating_excel_sheet's Issues

UI/UX for GUI.py using tKinter (Amazon Product review)

• The current GUI has some space I want to utilize for feedback

• I would like a rating option maybe ⭐ or number either would work

• This feedback should be stored in a csv file with how many stars and their frequency

• Ideally it should be something like on Amazon Product review

tKinter for MAC

• Currently the GUI/gui.py is working seamlessly for windows but there are issues with MAC

Sort the entries in Hacktoberfest_Inputt.xlsx

• The entries in the Hacktoberfest_Inputt.xlsx file can be duplicate in certain rows, I would like to sort the rows based on either source or destination column (column D and F). if the same entries/cell values appear in rows then they should be one after another.

For ex.
Let's say in row 3->cell value is 1.2.3.4 and then in row 45 again the cell value is 1.2.3.4. In this situation, i would like to see row 45 below row 3 ie at row 4 and shift the rows from 4 down one position.

Chatbot to query Hacktoberfest_database.xlsx

• Build a chatbot that can be integrated in a slack channel (preferrably)

• This bot will query the Hacktoberfest_database.xlsx

• Some use cases but not limited to can be:-
  - User can request all ip's under one particular color

         - User can query a particular ip or network address and the bot should return corresponding color from excel file
  
         - User can also add new ip or network address under a particular color they like, first prompt them which sheet they would 
            like to add or delete an entry from
  

Give me all ip's under one particular color

Set up .cirrus.yml

• CI for PRs

• Put your file in the root folder of this repo

• After review, I'll merge your PR

• Reference Material

Happy Continuous Learning :)

Make Readme.md pretty and exciting for newcomers!

I am a big fan of visuals, I would like to see your innovative skills that amazes the user and is pleasing to eye.

• Currently the font or orientation of topics might not be up to mark, I'll give you this opportunity to show your designing skillset

• I want newcomers and veterans both to say wow!

• I would like to keep this issue open until this Mahaparv of Hacktoberfest , raise your PRs

Reference
Bring that WoW!!

Data Visualisation

• Visualise the data of the Hacktoberfest_Inputt.xlsx file by either using matplotlib or even within excel file add another sheet

• What do I want to see?

  • Can the visualization be done on a GUI like tkinter?
  • I want to see the following relations:-
    - Column A to Column B aka zones relation. Ex. How many flows are from Green to Blue , grey to red etc. and it
    should also display which row number of excel sheet has that flow. Ex. Green->Blue is for Row 3
    - Relation source and destination , check valid_ip.py ;
    Ex. Instead of {'Source': '1.2.1.0/24', 'Destination': '6.208.46.0/23, 6.208.48.81, 6.208.48.48'} I would like to see
    {'1.2.1.0/24':['6.208.46.0/23, 6.208.48.81, 6.208.48.48']} instead

Improve zip.py (check tasks associated at top of file in comments and above functions as well)

Read a Hacktober_databse.xlsx that has mapped ip addresses to the source and destination color like in Hacktober_Inputt.xlsx

This Hacktober_database.xlsx subnets belonging to green, yellow,grey,brown etc. colors. Check for each ip address in column D and F of Hacktober_Inputt.xlsx and color the cell in Hacktober_Inputy.xlsx based on return value (which will be a color) from Hacktober_databse.xlsx.

Color the text in Column D and F rather than filling the cell bgcolor

If you check the Hacktoberfest_Inputt.xlsx you will notice that each cell can have single or multiple entries. Based on the current logic, each cell in columns D and F gets colored with the last returned color for last value if there are multiple.

The requirement is to color the text of each cell value with the corresponding returned value so that if there is a case where two values in a cell have different values, I can easily distinguish.

Ex. In column D we have below cell value:-
1.2.11.3,1.2.11.5, 1.2.11.5
1.2.12.1
1.2.12.4

Expectation:-

if 1.2.11.3 returns a green color after checking from Hacktoberfest_database.xlsx then, it should color itself with green color. Similarly, if 1.2.11.5 returns blue , it should color itself as blue . All colors should correctly mapped if that makes sense.

If no entry for an input is found in the database, keep it black in text ie do nothing to text color.

For any questions, please comment on this issue and I'll reply asap :)

Create a GUI for this application

GUI should contain atleast:-

• Upload button to upload an excel sheet as input

• Parse the excel file using the code logic (leave the logic space blank for now) Logic code will be in python

• After parsing the input excel file, the GUI should return an Output Excel File which will be the newly manipulated Excel File

• GUI can be made using any web-framework of your choice

• Documentation

Update Issue Status (Keep it professional)

Adjacently right of your issue:-

• Hacktoberfest_2020 -> change the state once you grab the issue

• Join my Zenhub and track

• Download Zenhub Plugin

Your contribution is always welcome :)

• Not satisfied with any aspect of this project and have ideas, I welcome your suggestion with open arms

• Please suggest your idea and after proof of concept we can work together to hone this project

Thank you for dropping by, I look forward to your contribution :)

Iterate over specific columns but all the rows, check if the entry is an ip address only then proceed

Make GUI/dashboard.py an application

Currently, the dashboard.py file under GUI folder is a mere code that someone needs to run on their cli.

Problem statement:-

• If a user who doesn't know how to use cli might never run this code under dashboard.py. We have to make sure they do use this wonderful product. (Hint: Can we make this tkinter GUI an application?)

• Find a best way for users to download this application, do they need to git clone only? can there be any other way of doing it?

• We need to make sure all the requirements are met when they install application ie all necessary packages and everything shouldn't be an issue irrespective of their OS (Maybe docker)

Ingest and Dockerise everything

Ingest and Dockerise it!

• Create basic structure of docker files
• This structure should ingest any python file and dockerise it (use prompt to input python file)

Create .travis.yml to use the test cases and do Continuous Integration

• Use the unit tests and check for any errors

• Errors should be sent to a slack channel, use something like below:-

notifications: 
  slack: 
   rooms:
     - secure: <channel_key>
   on_success: always
   on_failure: always
   template:
     - "Repo `%{repository_slug}` *%{result}* build (<%{build_url}|#%{build_number}>) for commit (<%{compare_url}|%{commit}>) on branch `%{branch}`."
     - "Execution time: *%{duration}*"
     - "Message: %{message}"

Add unit tests for zip.py

Clean code in zip.py

• Use OOP concepts

• Clean the code

• Reduce the length of code

• Optimize the code

• DRY (Don't Repeat Yourself)