-
Notifications
You must be signed in to change notification settings - Fork 0
/
read_xl.py
132 lines (110 loc) · 3.63 KB
/
read_xl.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
import os
import xlrd
from xlrd.sheet import ctype_text
import django
from django.core.exceptions import MultipleObjectsReturned, ObjectDoesNotExist
os.environ["DJANGO_SETTINGS_MODULE"] = "dialoguesforpeace.settings"
django.setup()
from dialogues.models import *
#scan through all excel files in this directory
#---------------
#Utility API
#---------------
def try_create_region(region_name):
try:
d = Region.objects.get(name=region_name)
#print "found! %d: %s"%(d.id, d.name)
except ObjectDoesNotExist:
z = Region(name=region_name)
z.save()
#print "Region %s created!"%(region_name)
def try_create_ga(child, parent):
try:
d = GA.objects.get(name=child)
#print "found! %d: %s"%(d.id, d.name)
except MultipleObjectsReturned:
print "Duplicate GA %s in Region%s"%(child, parent)
except ObjectDoesNotExist:
p = Region.objects.get(name=parent)
r = GA(name=child, parent=p)
r.save()
#print "GA %s created!"%(child)
def try_create_area(child, parent):
try:
d = Area.objects.get(name=child)
except MultipleObjectsReturned:
print "Duplicate Area %s in GA %s"%(child, parent)
except ObjectDoesNotExist:
p = GA.objects.get(name=parent)
r = Area(name=child, parent=p)
r.save()
#print "Area %s created!"%(child)
def try_create_chapter(child, parent):
try:
d = Chapter.objects.get(name=child)
except MultipleObjectsReturned:
print "Duplicate Chapter %s in Area %s"%(child, parent)
except ObjectDoesNotExist:
p = Area.objects.get(name=parent)
r = Chapter(name=child, parent=p)
r.save()
#print "Chapter %s created!"%(child)
def try_create_district(child, parent):
try:
d = District.objects.get(name=child)
print "Duplicate District found %s in %s chapter"%(child, parent)
#create district with chapter name appended to district name
p = Chapter.objects.get(name=parent)
dist_name = "%s - %s chapter"%(child,parent)
r = District(name=dist_name, parent=p)
r.save()
print "created district %s to avoid nameclash"%dist_name
except ObjectDoesNotExist:
p = Chapter.objects.get(name=parent)
r = District(name=child, parent=p)
r.save()
#print "District %s created!"%(child)
def parse_mds_leader_organogram(filename):
#open worksheet
xl_wb = xl_workbook = xlrd.open_workbook(filename)
sheet_names = xl_workbook.sheet_names()
xl_sheet = xl_workbook.sheet_by_index(0)
from dialogues.models import *
row = xl_sheet.row(0)
district_count = 0
for i in range(8,xl_sheet.nrows):
row = xl_sheet.row(i)
valid_dist = False
for idx, cell_obj in enumerate(row):
cell_type_str = ctype_text.get(cell_obj.ctype, 'unknown type')
if idx == 0:
region = cell_obj.value.strip()
if idx == 1:
ga = cell_obj.value.strip()
if idx == 2:
area = cell_obj.value.strip()
if idx == 3:
chapter = cell_obj.value.strip()
if idx == 4:
district = cell_obj.value.strip()
if idx == 6:
structure = cell_obj.value.strip()
if structure.lower() == 'district':
district_count += 1
valid_dist = True
#print "%s, %s, %s, %s, %s"%(region, ga, area, chapter, district)
if valid_dist:
try_create_region(region)
try_create_ga(ga, region)
try_create_area(area, ga)
try_create_chapter(chapter, area)
try_create_district(district, chapter)
print "Total number of districts added: %d"%district_count
return district_count
#============ script begins ===============
total_district_count = 0
for i in os.listdir(os.path.join(os.getcwd(), 'scripts/')):
if i.endswith(".xls"):
print "Parsing %s"%i
total_district_count += parse_mds_leader_organogram(os.path.join(os.getcwd(),'scripts', i))
print "Total number of districts added pan India: %d"%total_district_count