[/code]
Code: Select all
'''
Updated till 31.01.2024
Working on Bill data integration
Bill Migration done
'''
import pyodbc
import warnings
import pandas as pd
import time
from datetime import datetime
import pathlib
import subprocess
import os
warnings.filterwarnings('ignore')
#####################################################################Functions############################################################################3
def GetSimpledate(date):
Sdate = date.strftime("%Y-%m")
return (Sdate)
pathlib.Path('c:/python/output').mkdir(parents=True, exist_ok=True)
def Format_Sheet(sheetname, dfname):
workbook = writer.book
worksheet = writer.sheets[f"{sheetname}"]
# Get the dimensions of the dataframe.
(max_row, max_col) = f"{dfname}".shape
#print ("The Shape of Data Frame is : ", max_row, max_col)
# Create a list of column headers, to use in add_table().
column_settings = []
for header in {dfname}.columns:
column_settings.append({'header': header})
# Add the table.
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
# Make the columns wider for clarity.
worksheet.set_column(0, max_col - 1, 12)
worksheet.autofit()
writer.close()
##User Input Function
def get_yes_or_no_input(prompt):
while True:
user_input = input(prompt + " (yes/no): ").lower()
if user_input in ["yes", "no"]:
return user_input
else:
print("Invalid input. Please enter 'yes' or 'no'.")
########################################################Connector Parameters variables############################################################
pyodbc.drivers()
server = 'localhost,9000'
#database = 'database_name' # enter database name
cnxn = pyodbc.connect('DRIVER={Tally ODBC Driver64};SERVER='+server+';Trusted_Connection=yes;')
cursor = cnxn.cursor()
start_time = time.time()
#####################################################################Query############################################################################3
query1 = '''Select $Name, $_ThisYearBeg, $_ThisYearEnd from Company'''
query2 = '''SELECT $Key, $MasterId, $AlterID, $VoucherNumber, $Date, $VoucherTypeName, $Led_Lineno, $Type, $LedgerName, $Amount, $Led_Parent, $Led_Group, $Party_LedName, $Vch_GSTIN, $Led_GSTIN, $Party_GST_Type, $GST_Classification, $Narration, $EnteredBy, $LastEventinVoucher, $UpdatedDate, $UpdatedTime, $Nature_Led, $Led_MID, $CompanyName, $Year_from, $Year_to, $Company_number, $Path FROM A__DayBook'''
#print(Comp_Name)
#########################################################################Execution################################################################################
df_Vch_Master = pd.read_sql(query2, cnxn)
df_Master = pd.read_sql(query1, cnxn)
print(df_Master.head())
#####################################################################Variables############################################################################3
Comp_Name = df_Master.loc[0, '$Name']
Start_Date = df_Master.loc[0, '$_ThisYearBeg']
End_Date = df_Master.loc[0, '$_ThisYearEnd']
print(Comp_Name, Start_Date, End_Date)
Sim_dateS = GetSimpledate(Start_Date)
Sim_dateE = GetSimpledate(End_Date)
current_datetime = datetime.now()
formatted_datetime = current_datetime.strftime("%d-%m-%y %H-%M-%S")
print(formatted_datetime)
File_String = Comp_Name + " From " + Sim_dateS + " To " + Sim_dateE + " Gen on " + formatted_datetime
print(File_String)
#print(df_Vch_Master)
#print(df_Vch_Master.shape)
####################################################################Creating and writing to Sheets ##########################################################
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(f"c:\\Python\\Output\\{File_String}.xlsx", engine='xlsxwriter')
df_Vch_Master.to_excel(writer, sheet_name='DayBook', startrow=1, header=False, index=False)
####################################################################Formating the Sheets ##########################################################
workbook = writer.book
worksheet = writer.sheets['DayBook']
# Get the dimensions of the dataframe.
(max_row, max_col) = df_Vch_Master.shape
#print ("The Shape of Data Frame is : ", max_row, max_col)
# Create a list of column headers, to use in add_table().
column_settings = []
for header in df_Vch_Master.columns:
column_settings.append({'header': header})
# Add the table.
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
# Make the columns wider for clarity.
worksheet.set_column(0, max_col - 1, 12)
worksheet.autofit()
writer.close()
writer.close()
#Format_Sheet(ms, dfname)
print("--- %s seconds for the current Process---" % (time.time() - start_time))
############################################################## Open Folder and Working Files #############################################
subprocess.Popen('explorer "c:\\python\\output"')
# User input to open the file:
user_response = get_yes_or_no_input("Do you want to continue?")
if user_response == "yes":
print("You chose to Open Excel File.")
excel_file_path = f'C:\\python\\output\\{File_String}.xlsx'
os.startfile(excel_file_path)
else:
print("You chose not to Open Excel File.")