#openpyxl

[thebrothers-003]

Mastering Excel: A Beginner's Guide to Organizing, Analyzing and Visualizing Data

Mastering Excel: A Beginner’s Guide to Organizing, Analyzing and Visualizing Data

Familiarize yourself with the layout of the Excel window, including the ribbon and the various tabs and buttons. Learn how to navigate and select cells, rows, and columns. Learn the basic mathematical functions and how to use them, such as SUM, AVERAGE, and COUNT. Understand how to format cells, including font size and color, cell alignment, and borders. Learn how to sort and filter data, and…

[dragonfly7022003]

Some programs I have created and use.

File Scanner

This program is more of a scanner to search a server and find all the older files. I set it up to scan for older files that are over 7 years old and compile them into an excel file so they can be reviewed before deletion. This is a good program for users for file retention policies. Also to find those information hoarders.

Now the program will ask you for a file path, then ask where you want to store the excel folder.

import os import datetime from openpyxl import Workbook from tkinter import filedialog import tkinter as tk

def get_file_creation_time(file_path): """ Get the creation time of a file. """ print("File path:", file_path) #Debug Print try: return datetime.datetime.fromtimestamp(os.path.getctime(file_path)) except OSError as e: print("Error:", e) #debug print return None

def get_file_size(file_path): """ Get the size of a file. """ return os.path.getsize(file_path)

def list_old_files(folder_path, output_directory): """ List files older than 7 years in a folder and store their information in an Excel file. """ # Initialize Excel workbook wb = Workbook() ws = wb.active ws.append(["File Name", "File Path", "Creation Date", "Size (bytes)"])

# Get current date current_date = datetime.datetime.now()

# Traverse through files in the folder for root, dirs, files in os.walk(folder_path): for file in files: file_path = os.path.join(root, file) creation_time = get_file_creation_time(file_path) if creation_time is None: continue #Skip files that could not be retrived

file_age = current_date - creation_time if file_age.days > 7 * 365: # Check if file is older than 7 years file_size = get_file_size(file_path) ws.append([file, file_path, creation_time.strftime('%Y-%m-%d %H:%M:%S'), file_size])

# Save Excel file to the specified directory output_excel = os.path.join(output_directory, "old_files.xlsx") wb.save(output_excel) print("Old files listed and saved to", output_excel)

if __name__ == "__main__": # Initialize Tkinter root = tk.Tk() root.withdraw() # Hide the main window

# Ask for folder path folder_path = filedialog.askdirectory(title="Select Folder")

# Ask for output directory output_directory = filedialog.askdirectory(title="Select Output Directory")

list_old_files(folder_path, output_directory)

------------------------------------------------------------------------------

Older file Scanner and Delete

Working in the IT field, you will find that the users will fill up the space on the servers with older files.

Especially if you work within an industry that needs to have document retention policies where you can not keep some documents longer than a certain amount of time or you just have hoarders on your network. You will know those people who do not delete anything and save everything.

So I wrote up a program that will search through a selected server and find all empty files, older files, and delete them.

import os import datetime import tkinter as tk from tkinter import filedialog

def list_files_and_empty_folders_to_delete(folder_path): # Get the current date current_date = datetime.datetime.now()

# Calculate the date seven years ago seven_years_ago = current_date - datetime.timedelta(days=7*365)

files_to_delete = [] empty_folders_to_delete = []

# Iterate over files and folders recursively for root, dirs, files in os.walk(folder_path, topdown=False): # Collect files older than seven years for file_name in files: file_path = os.path.join(root, file_name) # Get the modification time of the file file_modified_time = datetime.datetime.fromtimestamp(os.path.getmtime(file_path)) # Check if the file is older than seven years if file_modified_time < seven_years_ago: files_to_delete.append(file_path)

# Collect empty folders for dir_name in dirs: dir_path = os.path.join(root, dir_name) if not os.listdir(dir_path): # Check if directory is empty empty_folders_to_delete.append(dir_path)

return files_to_delete, empty_folders_to_delete

def delete_files_and_empty_folders(files_to_delete, empty_folders_to_delete): # Print files to be deleted print("Files to be deleted:") for file_path in files_to_delete: print(file_path)

# Print empty folders to be deleted print("\nEmpty folders to be deleted:") for folder_path in empty_folders_to_delete: print(folder_path)

# Confirmation before deletion confirmation = input("\nDo you want to proceed with the deletion? (yes/no): ") if confirmation.lower() == "yes": # Delete files for file_path in files_to_delete: os.remove(file_path) print(f"Deleted file: {file_path}")

# Delete empty folders for folder_path in empty_folders_to_delete: os.rmdir(folder_path) print(f"Deleted empty folder: {folder_path}") else: print("Deletion canceled.")

def get_folder_path(): root = tk.Tk() root.withdraw() # Hide the main window

folder_path = filedialog.askdirectory(title="Select Folder") return folder_path

# Ask for the folder path using a dialog box folder_path = get_folder_path()

# Check if the folder path is provided if folder_path: # List files and empty folders to be deleted files_to_delete, empty_folders_to_delete = list_files_and_empty_folders_to_delete(folder_path) # Delete files and empty folders if confirmed delete_files_and_empty_folders(files_to_delete, empty_folders_to_delete) else: print("No folder path provided.")

______________________________________________________________

Batch File Mod

This program is used for when you need to mod Batch files. Any person in IT that has had to manage Batch files for a large company can think how annoying it would be to go through each one and make a single line change.

Well this program is made to search through all the batch files and you can write in a line, and it will replace it with another line you choose.

import os

def find_files_with_text(directory_path, text_to_find): files_with_text = [] for root, _, files in os.walk(directory_path): for file_name in files: if file_name.endswith('.bat'): file_path = os.path.join(root, file_name) with open(file_path, 'r') as file: if any(text_to_find in line for line in file): files_with_text.append(file_path) return files_with_text

def remove_line_from_file(file_path, text_to_remove): try: with open(file_path, 'r') as file: lines = file.readlines()

with open(file_path, 'w') as file: for line in lines: if text_to_remove not in line: file.write(line)

print(f"Removed lines containing '{text_to_remove}' from {file_path}.")

except FileNotFoundError: print(f"Error: The file {file_path} does not exist.") except Exception as e: print(f"An error occurred: {e}")

def process_directory(directory_path, text_to_remove): files_with_text = find_files_with_text(directory_path, text_to_remove)

if not files_with_text: print(f"No files found containing the text '{text_to_remove}'.") return

for file_path in files_with_text: print(f"Found '{text_to_remove}' in {file_path}") user_input = input( f"Do you want to remove the line containing '{text_to_remove}' from {file_path}? (yes/no): ").strip().lower() if user_input == 'yes': remove_line_from_file(file_path, text_to_remove) else: print(f"Skipped {file_path}.")

if __name__ == "__main__": directory_path = input("Enter the path to the directory containing batch files: ") text_to_remove = input("Enter the text to remove: ") process_directory(directory_path, text_to_remove)

[abbiistabbii]

I just automated something using Python for the first time and my brain released the

HAPPY CHEMICALS

in a way I have not felt in a long, looooooooooooooong time to the point I wanted to tell everyone what I did despite it being really fucking nerdy.

Basically every mainline railway station on Great Britain has a three letter code like airports, KGX for King's Cross, EDB for Edinburgh, GLC for Glasgow Central. You get the idea. Anyway I have this thing called Espanso where you can type a prompt and it will replace it with something you set it to. For example, I can type :date and it will write the current date (20/07/2023). You can set up your own prompts by learning the very simple mark up language and setting your own prompts.

Anyway I looked at Espanso and thought "I wonder if I could set it up so I could type a station code as an Espanso prompt and get the station code. So after thinking about how I could write it by hand and realising there were 2575 stations in Great Britain, I knew I would have to automate it.

My knowlegde of Python is basic, but after watching part of a video course, learning how to install Python Packages, learning how to use Openpyxl and reading so many blogposts I learnt how to use Python to take info from a spreadsheet, insert it into Espanso's markup langauge, and put that in a fucking text file and after some trial and error and test runs, I did it and my mind just exploded with the happy chemicals.

My monkey brain was like "HEE HOO PUZZLE SOLVED" and I got so much happy brain sauce. And after telling my Mum, Dad, Friends and an elderly neighbour what I did, something set in. Is this...how they get you? Is this how people get into coding for fun or even a career? Has coding bitten me? Will I ever be able to escape this addiction?

[fortunatelycoldengineer]

Python Openpyxl . . . . for more information and tutorial https://bit.ly/4bhz9ap check the above link

[spreadsheetautomation]

What is Python Automation?

In the rapidly evolving world of technology, automation stands as a cornerstone, significantly enhancing efficiency, accuracy and productivity in various domains. At the heart of this transformation lies Python automation, a powerful approach to scripting and automating repetitive tasks that otherwise consume valuable time and resources.

The Essence of Python Automation

Python automation leverages the simplicity and versatility of the Python programming language to create scripts that perform tasks automatically. This can range from data analysis, file management and network configuration, to web scraping. The beauty of Python lies in its extensive libraries and frameworks that cater to different automation needs, making it accessible to both beginners and seasoned developers. Its syntax is clear and concise, reducing the complexity of writing automation scripts and making the process more intuitive.

Automating Excel Using Python

One of the most practical applications of Python automation is managing and manipulating Excel files, a task known as "automating Excel with Python". This involves using libraries such as Pandas and OpenPyXL to read, write and modify Excel spreadsheets without the need for manual input. Automating Excel using Python not only speeds up data processing tasks but also minimizes errors, ensuring that data management is both efficient and reliable.

In workplaces where Excel is a staple for reporting and data analysis, this aspect of Python automation proves invaluable. It allows users to automate data entry, formatting and even complex calculations, turning hours of manual work into a few minutes of script execution.

Conclusion

Python automation is transforming the landscape of digital workflows, offering scalable and efficient solutions to mundane tasks. From web development to automating Excel with Python, its applications are vast and varied, catering to the needs of a wide range of industries. As we move forward, the role of Python in automation is set to grow, highlighting its importance in driving productivity and innovation in the digital age.

Read a similar article about Python for healthcare here at this page.

[isabellanithya]

Unleashing the Power of Python in Finance: Driving Efficiency and Insight

Python has revolutionized the finance industry, emerging as a dynamic programming language that empowers professionals with its versatility and efficiency. With an extensive collection of libraries, user-friendly syntax, and adaptability, Python has become an indispensable tool in various financial applications. In this article, we delve into the multifaceted uses of Python in finance and explore how it enables professionals to enhance efficiency and gain valuable insights.

In order to gain the necessary skills and information for successfully navigating this ever-changing landscape, anyone seeking to master the creative works of python programmer should register in a Python training in Pune.

Unleashing the Potential of Data Analysis and Visualization: Python's prowess lies in its comprehensive libraries such as Pandas, NumPy, and Matplotlib, which are extensively employed for data analysis and visualization in finance. By leveraging these libraries, finance professionals can seamlessly manipulate vast datasets, perform sophisticated calculations, generate comprehensive reports, and create visually compelling charts and graphs. Python equips them with the means to extract valuable insights from financial data efficiently.

Harnessing the Power of Algorithmic Trading: Quantitative analysts and algorithmic traders widely adopt Python for developing and implementing trading strategies. The availability of libraries like NumPy, Pandas, and SciPy facilitates efficient numerical computations, while frameworks such as Zipline and PyAlgoTrade offer robust algorithmic trading capabilities. Python's flexibility allows traders to rapidly prototype and rigorously backtest strategies, enabling them to make well-informed trading decisions.

Unlocking New Frontiers with Financial Modeling: Python serves as a powerful tool for financial modeling, encompassing asset valuation, risk analysis, and portfolio optimization. Libraries like QuantLib and PyFin equip professionals with a comprehensive set of tools to build intricate models for pricing derivatives, simulating market scenarios, and conducting risk assessments. Python's intuitive syntax and extensive mathematical libraries enable accurate modeling of complex financial instruments.

Embracing the Power of Machine Learning and Artificial Intelligence: Python's expansive ecosystem of machine learning libraries, including Scikit-learn, TensorFlow, and Keras, have revolutionized predictive modeling in finance. Machine learning techniques find applications in credit risk assessment, fraud detection, market forecasting, sentiment analysis, and algorithmic trading. Python's simplicity and flexibility facilitate the development and deployment of advanced machine learning models, empowering finance professionals to extract valuable insights from complex data.

Furthermore, selecting the appropriate tactics and procedures is critical, as is acquiring the necessary abilities. This is where enrolling in the Top Python Online Certification can make a big difference.

Empowering Data Retrieval with Web Scraping: Python's libraries, such as BeautifulSoup and Requests, enable finance professionals to retrieve financial data from diverse sources through web scraping. This capability facilitates the collection of real-time market data, financial statements, news, and other pertinent information. Python's versatility in data retrieval equips professionals with the necessary tools to make data-driven decisions and stay ahead of market trends.

Streamlining Financial Analysis and Reporting: Python simplifies financial analysis and reporting tasks through libraries like Pandas and openpyxl. By automating data processing and report generation, professionals can optimize their workflow and dedicate more time to critical analysis. Interactive visualization and dashboarding capabilities provided by tools like Jupyter Notebook, Plotly, and Dash enable finance professionals to present complex financial information in a concise and visually appealing manner.

Enhancing Risk Management Capabilities: Python finds extensive use in risk management processes, including value at risk (VaR) calculations, stress testing, and scenario analysis. Statistical libraries like SciPy and Statsmodels offer powerful tools for assessing and managing financial risks. Python's flexibility enables risk managers to tailor risk models to specific requirements, facilitating insightful risk assessments and informed decision-making.

Python has emerged as a game-changer in the finance industry, empowering professionals to drive efficiency and gain valuable insights. Its versatility, extensive libraries, and user-friendly syntax make it a preferred choice in data analysis, algorithmic trading, financial modeling, machine learning, data retrieval, financial analysis, and risk management.

As Python continues to evolve, it is poised to play an increasingly vital role in shaping the future of finance, enabling professionals to thrive in an ever-changing landscape.

[infinetsoft]

ModuleNotFoundError: No module named 'openpyxl' - Python

[planilhaexcel2022]

Integração do Python e Excel com PANDAS e OPENPYXL

Python e Excel com PANDAS e OPENPYXL Integração do Python com Excel: Uma Visão Prática A integração entre Python e Excel é uma habilidade essencial para muitos profissionais que trabalham com análise de dados. O Python, sendo uma linguagem de programação poderosa, oferece ferramentas que permitem manipular e analisar dados de forma eficiente. O Excel, por outro lado, é amplamente utilizado em…

[ai-news]

#AI #ML #Automation

[computercodingclass]

insert image in excel using python openpyxl | automate excel with python | Computer Coding Class

via IFTTT

[solspuren]

今日は、pythonでseleniumを使って、

PC起動時にあるwebページから値を取ってきて

openpyxlでエクセルデータに書き込むプログラムを作りました。

[science-005]

Learning openpyxl

Openpyxl is a python package for reading and writing excel documents in other words it can can be used to manipulate excel files. First we need to import the openpyxl module using the python command import openpyxl then we create an openpyxl object or avatar which we would use to load the excel spreadsheet into our IDE ,we use the command: wb = openpyxl.load_workbook('example.xlsx'),but make sure the file is in your current directory or else you would get an error message to do that , input this command into your terminal : import os ,os.getcwd() the os.getcwd() command tells you what folder or directory you are in currently, it's usually the root directory that would be printed out but to go into the directory the excel spreadsheet is located we need to use the command os.chdir() which in simple terms means change directory,inside the bracket input the path to the excel file there or the easiest way is just to copy paste the excel file into the folder containing your python files.To get the sheet we are currently working with we type : sheet = wb.get_sheet_by_name('example sheet') the sheet name is just the excel file name.After this you are ready to go!

[infomantra]

Using Pandas read excel for multiple worksheets of the same workbook

import openpyxl import warnings warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

sheets = pd.ExcelFile('./pdfconv/Test_NEW.xlsx').sheet_names

# # sheets = ['Sheet1', 'Sheet3']

# df = pd.concat([pd.read_excel('./pdfconv/Test.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True)

df = pd.concat([pd.read_excel('./pdfconv/Test.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True)

print(df)

print(sheets)

for sh in sheets: # if sh == 'Sheet1': # for variation=1 month Jan 2022 if sh == 'Sheet1' and 'Sheet3': # for variation1 month April 2022 df = pd.concat([pd.read_excel('./pdfconv/Test_NEW.xlsx', sheet_name = sheet, header=None) for sheet in sheets], ignore_index = True, axis=0) #print(df) df.to_csv('./pdfconv/ddtest.csv') else: # df = pd.concat([pd.read_excel('./pdfconv/Test.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True) print('Sorry!! not to print')

Url: https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook