Python Forum
App for excel sheet analysis
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
App for excel sheet analysis
#1
Hey all,


A python beginner here. I need to create an app that will analyze excel sheets. The idea is:

1. A user inputs 2 excel sheets, one will work as a template and the other one needs to be filled in
2. the app needs to analyze the content of the cells and find cells that match the titles between the template sheet and the draft sheet and fill out the numeric value from the template sheet accordingly.
In other words I just need to automatize a specific process, where right now whenever we need to fill out new excel sheets we need to work on two windows and just copy paste numeric values from the template sheet to our works sheets.

Could someone give me some tips on how to start?
Reply
#2
Look at examples for using the openpyxl library. Here's some code showing a simple example I shared on the learnpython subreddit a little while back.

import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Fill
from openpyxl.styles import colors 
filepath = "namesplaces.xlsx"
wb = load_workbook(filepath)
filepath = "namesplaces.xlsx"
wb = load_workbook(filepath)
sheet = wb.active
for row in range(2, 25):
    name = sheet.cell(row=row, column=1)
    place = sheet.cell(row=row, column=2)
    if name.value == None or place.value == None:
        break
    print(name.value, place.value)
    if name.value[0] == place.value[0]:
        name.fill = PatternFill(fill_type='solid', start_color='ff8200', end_color='ff8200')
        place.fill = PatternFill(fill_type='solid', start_color='ff8200', end_color='ff8200')
wb.save(filepath) 
I am trying to help you, really, even if it doesn't always seem that way
Reply
#3
You can certainly use openpyxl, but if you are willing to spend a bit of time learning, I would suggest that Pandas is the way to go.
Package overview: https://pandas.pydata.org/pandas-docs/st...rview.html
Tutorials:
There are many tutorials available on you tube, and here: https://pandas.pydata.org/pandas-docs/st...rials.html
Reply
#4
(Jan-20-2020, 04:27 PM)Larz60+ Wrote: You can certainly use openpyxl, but if you are willing to spend a bit of time learning, I would suggest that Pandas is the way to go.
Package overview: https://pandas.pydata.org/pandas-docs/st...rview.html
Tutorials:
There are many tutorials available on you tube, and here: https://pandas.pydata.org/pandas-docs/st...rials.html
Is pandas a good option for a beginner do you think? I've suggested that elsewhere in the past and been told it will be too confusing for them.
I am trying to help you, really, even if it doesn't always seem that way
Reply
#5
Pandas Is only a suggestion.
Pandas as a whole is complicated, but the basics can be quickly learned making it a viable option for a simple application.

It All depends on the application.
If this is a one-off that will not be expanded, then openpyxl is a valid choice.

For my employees, I'd rather they spend the time and learn to use pandas, but that's a personal choice.
Reply
#6
Pandas has a lot of capability, but can be simple. For example:
import pandas as pd
import numpy as np

dataframe1 = pd.read_excel('C:\\entry.XLS')

dataframe2 = pd.DataFrame()
dataframe2['chart'] = dataframe1['chart']
dataframe2['first'] = dataframe1['first']
dataframe2.head()

dataframe2.to_excel('C:\\mod.xls')
This reads the excel spreadsheet entry.XLS into a dataframe (the Pandas equivalent of a spreadsheet). It then copies the chart and first columns. head() displays the first 5 rows. to_excel writes the second dataframe out in excel format.
Reply
#7
Wow guys, thanks a lot for your kind help! I will start trying it out immediately :)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Dealing with duplicates to an Excel sheet DistraughtMuffin 6 3,197 Oct-28-2020, 05:16 PM
Last Post: Askic
  Help me with yahoo finance excel sheet Learner4life 2 2,243 Nov-27-2019, 12:55 PM
Last Post: Learner4life

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020