import openpyxl
from openpyxl.styles import Border, Side, PatternFill
import webcolors
from colorama import Fore, Style
import os
import csv
import time
from tqdm import tqdm
def create_workbook():
workbook = openpyxl.Workbook()
return workbook
def add_sheet(workbook):
sheet = workbook.active
return sheet
def get_column_values(sheet, column_index):
values = []
while True:
value = input(Fore.MAGENTA + Style.BRIGHT + f"Enter a value for column {chr(ord('A')+column_index)} (press Enter to finish): ")
if value == "":
print()
break
values.append(value)
return values
def write_column_values(sheet, column_index, values):
for row_index, value in enumerate(values, start=1):
sheet.cell(row=row_index, column=column_index + 1, value=value)
def set_column_width(sheet, column_index, max_length):
column_letter = chr(ord('A') + column_index)
sheet.column_dimensions[column_letter].width = max_length + 2
def get_color_rgb(color_name):
while True:
try:
color_rgb = webcolors.name_to_rgb(color_name)
return color_rgb
except ValueError:
print(Fore.RED + Style.BRIGHT + "Invalid color name entered.")
color_name = input(Fore.LIGHTCYAN_EX + Style.BRIGHT + "Please enter a valid color name: ")
def convert_rgb_to_hex(rgb):
return f"{rgb.red:02X}{rgb.green:02X}{rgb.blue:02X}"
def apply_fill_color(sheet, fill_color_hex, min_row, max_row, min_col, max_col):
fill = PatternFill(start_color=fill_color_hex, end_color=fill_color_hex, fill_type="solid")
for row in sheet.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
for cell in row:
cell.fill = fill
def apply_borders(sheet, min_row, max_row, min_col, max_col):
border_style = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin'))
for row in sheet.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
for cell in row:
cell.border = border_style
def save_workbook(workbook, file_name):
workbook.save(file_name)
def open_file(file_name):
os.startfile(file_name)
def generate_excel():
workbook = create_workbook()
sheet = add_sheet(workbook)
while True:
try:
choice = input(Fore.YELLOW + Style.BRIGHT + "Do you want to enter data manually (M) or import from a CSV file (C)? ").strip().upper()
if choice == "M":
clm = int(input(Fore.YELLOW + Style.BRIGHT + 'Enter the number of columns to be used: '))
break
elif choice == "C":
csv_file_path = input(Fore.YELLOW + Style.BRIGHT + "Enter the path of the CSV file: ")
with open(csv_file_path, 'r') as csv_file:
csv_reader = csv.reader(csv_file)
first_row = next(csv_reader)
clm = len(first_row)
break
else:
print(Fore.RED + "Invalid choice. Please enter 'M' or 'C'.")
except ValueError:
print(Fore.RED + "Invalid input. Please enter a valid choice.")
print()
if choice == "M":
for i in range(clm):
values = get_column_values(sheet, i)
write_column_values(sheet, i, values)
max_length = max(len(str(value)) for value in values)
set_column_width(sheet, i, max_length)
elif choice == "C":
with open(csv_file_path, 'r') as csv_file:
csv_reader = csv.reader(csv_file)
for i, row in enumerate(csv_reader):
write_column_values(sheet, i, row)
max_length = max(len(str(value)) for value in row)
set_column_width(sheet, i, max_length)
while True:
try:
first_row_color = input(Fore.LIGHTCYAN_EX + Style.BRIGHT + "Enter the color for the first row: ")
first_row_color_rgb = get_color_rgb(first_row_color)
break
except ValueError:
print(Fore.RED + Style.BRIGHT + "Invalid color name entered.")
while True:
try:
remaining_cells_color = input(Fore.LIGHTCYAN_EX + Style.BRIGHT + "Enter the color for the remaining cells: ")
remaining_cells_color_rgb = get_color_rgb(remaining_cells_color)
break
except ValueError:
print(Fore.RED + Style.BRIGHT + "Invalid color name entered.")
first_row_color_hex = convert_rgb_to_hex(first_row_color_rgb)
remaining_cells_color_hex = convert_rgb_to_hex(remaining_cells_color_rgb)
apply_fill_color(sheet, first_row_color_hex, 1, 1, 1, sheet.max_column)
apply_fill_color(sheet, remaining_cells_color_hex, 2, sheet.max_row, 1, sheet.max_column)
apply_borders(sheet, 1, sheet.max_row, 1, sheet.max_column)
file_name = f''
print()
workbook.save(file_name)
print()
print(Fore.RED + Style.BRIGHT + f'''Your excel sheet - {Name}-Annual Performance
has been made and saved in the \code folder''')
print()
time.sleep(2)
print(Fore.RED + Style.BRIGHT + "Opening Document....")
os.startfile(file_name)
print(Fore.LIGHTBLUE_EX + Style.BRIGHT + '''Hey want to create an Excel document of your annual sales report
Dont worry we got you!!!
Just type the required fields below.''')
print()
Name = input(Fore.LIGHTCYAN_EX + Style.BRIGHT + 'Enter the name of the company - ')
print()
print(Fore.LIGHTYELLOW_EX + Style.BRIGHT + 'The Table')
generate_excel()