-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathfile_convertion.py
executable file
·155 lines (142 loc) · 6.63 KB
/
file_convertion.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
from collections import OrderedDict
from datetime import datetime
from operator import itemgetter
import openpyxl
import simplejson as json
import sys
def convert_to_exel(input_worksheet, output_workbook, row_count, col_count):
output_worksheet = output_workbook["b2b"]
headers = ["Customer GSTIN", "Customer Name", "Invoice", "Posting Date", "Grand Total", "Place of Supply", "Reverse Charge", "skip", "Invoice Type", "E-Commerce GSTIN", "skip_rate", "Net Total"]
index = 0
for header in (headers):
index += 1
col_index = get_colunm_index(col_count, header, input_worksheet)
if(header == "skip"):
continue
if(header <> "skip_rate"):
selected_data = copy_data(col_index , 2 , col_index , row_count , input_worksheet, header)
paste_data(index, 5, index, row_count+2, header, output_worksheet, input_worksheet, selected_data)
if sys.argv[2] == "Exel":
output_workbook.save("converted_gstin1.xlsx")
return output_worksheet
def copy_data(start_col, start_row, end_col, end_row, sheet, header):
range_selected = []
for i in range(start_row, end_row + 1, 1):
row_selected = []
for j in range(start_col, end_col+1, 1):
if((sheet.cell(row = i, column = j).value == "29" or sheet.cell(row = i, column = j).value == "0" or sheet.cell(row = i, column = j).value == None) and header == "Place of Supply" ):
row_selected.append("29-Karnataka")
else:
row_selected.append(sheet.cell(row = i, column = j).value)
range_selected.append(row_selected)
return range_selected
def paste_data(start_col, start_row, end_col, end_row, header, output_sheet, input_sheet, copied_data):
count_row = 0
for i in range(start_row, end_row+1, 1):
count_col = 0
for j in range(start_col, end_col+1, 1):
if header == "Posting Date":
invoice_date = (copied_data[count_row][count_col]).date()
invoice_object = datetime.strftime(invoice_date, '%d-%b-%Y')
output_sheet.cell(row = i, column = j).value = invoice_object
elif(header == "skip_rate"):
rate = calc_rate(input_sheet, i-3) #i-3 gives the row of which the data is to be calculate
output_sheet.cell(row = i, column = j).value = rate
else:
output_sheet.cell(row = i, column = j).value = copied_data[count_row][count_col]
count_col += 1
count_row += 1
def calc_rate(sheet, row_index):
Total_value = sheet.cell(row = row_index, column = 26).value
Tax_value = sheet.cell(row = row_index, column = 27).value
return int(round(Tax_value*200/Total_value))
def get_colunm_index(col_count, header, sheet):
col_index = 0
for col in sheet.iter_cols(min_row=1, max_col=col_count, max_row=1):
for cell in col:
col_index += 1
if header == cell.value:
return col_index
def convert_to_json(sheet_data, gstin, row_count):
sheet_details = OrderedDict()
Date = datetime.strptime(sheet_data[0][3], '%d-%b-%Y')
sheet_details['fp'] = str(Date.strftime("%m%Y"))
sheet_details['gstin'] = str(gstin)
sheet_details['hash'] = 'hash'
sheet_details['version'] = 'GST2.2.6'
gstins = []
for row in sheet_data:
gstins.append(row[0])
data_list = []
gstin_index = 0
index = 0
while(index < len(sheet_data)):
gstin_details = OrderedDict()
gstin_details_list = sheet_data[index]
invoice_list=[]
while True:
invoice = {}
gstin_details['ctin'] = gstin_details_list[0]
invoice['inum'] = gstin_details_list[2]
invoice_date = datetime.strptime(gstin_details_list[3], "%d-%b-%Y")
invoice['idt'] = datetime.strftime(invoice_date, '%d-%m-%Y')
invoice['val'] = gstin_details_list[4]
invoice['pos'] = gstin_details_list[5][:2]
invoice['rchrg'] = gstin_details_list[6]
invoice['inv_typ'] = gstin_details_list[8][:1]
num_list = []
num = OrderedDict()
num['num'] = 1201
items_details = OrderedDict()
items_details['txval'] = gstin_details_list[11]
items_details['rt'] = int(gstin_details_list[10])
items_details['camt'] = ''
items_details['samt'] = ''
items_details['csamt'] = 0
num['itm_det'] = items_details
num_list.append(num)
invoice['itms'] = num_list
invoice_list.append(invoice)
gstin_index += 1
if ( gstin_index >= len(gstins)):
break
if (gstins[gstin_index-1] == gstins[gstin_index]):
index += 1
gstin_details = OrderedDict()
gstin_details_list = sheet_data[index]
else:
break
index += 1
gstin_details['inv'] = invoice_list
sheet_details['b2b'] = data_list
data_list.append(gstin_details)
json_data = json.dumps(sheet_details)
with open('GSTR_JSON_FILE.json', 'w') as json_file:
json_file.write(json_data)
def sort_converted_sheet(converted_sheet, row_count):
details = []
for i in range(5, row_count+3):
row_details = []
for cell in converted_sheet[i]:
row_details.append(cell.value),
details.append(row_details)
details = sorted(details, key=itemgetter(1))
for index_r, row in enumerate(details):
for index_c, value in enumerate(row):
converted_sheet.cell(row = index_r+5, column = index_c+1).value = value
in_w_book = openpyxl.load_workbook(filename=sys.argv[1])
in_w_sheet = in_w_book["Query Report"]
gstin = in_w_sheet.cell(row = 2, column = 7).value
out_w_book = openpyxl.load_workbook(filename="GSTR1_Excel_Workbook_Template_V1.5.xlsx")
row_count = in_w_sheet.max_row
col_count = in_w_sheet.max_column
converted_sheet = convert_to_exel(in_w_sheet, out_w_book, row_count, col_count)
if sys.argv[2] == "Json":
sort_converted_sheet(converted_sheet, row_count)
converted_sheet_data_list = []
for row in range(5, row_count+3):
converted_sheet_data = []
for column in range(1, 14):
converted_sheet_data.append(converted_sheet.cell(row, column).value)
converted_sheet_data_list.append(converted_sheet_data)
convert_to_json(converted_sheet_data_list, gstin, row_count+4)