Python Forum

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?

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) 

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

(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.

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.

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.

Wow guys, thanks a lot for your kind help! I will start trying it out immediately :)