App for excel sheet analysis - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Homework (https://python-forum.io/forum-9.html) +--- Thread: App for excel sheet analysis (/thread-23843.html) |
App for excel sheet analysis - awookado - Jan-20-2020 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? RE: App for excel sheet analysis - gruntfutuk - Jan-20-2020 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) RE: App for excel sheet analysis - Larz60+ - Jan-20-2020 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/stable/getting_started/overview.html Tutorials: There are many tutorials available on you tube, and here: https://pandas.pydata.org/pandas-docs/stable/getting_started/tutorials.html RE: App for excel sheet analysis - gruntfutuk - Jan-20-2020 (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.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. RE: App for excel sheet analysis - Larz60+ - Jan-20-2020 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. RE: App for excel sheet analysis - jefsummers - Jan-20-2020 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. RE: App for excel sheet analysis - awookado - Jan-27-2020 Wow guys, thanks a lot for your kind help! I will start trying it out immediately :) |