Django recursive tree import with XLRD

My data (spre开发者_如何学Cadsheet):

'1',,,
,'1.1',,
,,'1.1.1',
,,'1.1.2',
,,'1.1.3',
,'1.2',,
,'1.3',,
,,'1.3.1',
,,'1.3.2',
,,'1.3.3',
'2',,,
,'2.1',,
,,'2.1.1',
,,,'2.1.1.1'
,,,'2.1.1.2'
,,,'2.1.1.3'

My model:

class Vocabulary(models.Model):
    name = CharField(max_length=60)

class Concept(models.Model):
    parent = ForeignKey('self', blank=True, null=True)
    vocabulary = ForeignKey(Vocabulary)
    name = CharField(max_length=60)
    order = IntegerField(default=0)

What I am trying to do:

def recurse(sheet):
    'Recurse outer edges of the tree saving concepts.'
        + 'Imply subtree order numbers. There are no numbers in the real data.'

It wasn't easy to figure out, just to share it. This is how I imported hierarchy from Excel to simple adjacency list tree store using Python XLRD and Django.

class XLRDParseError(Exception):
    """The XLS file was malformed."""

def load_xls(fname):
    """Import a hierarchy into the DB from Excel"""
    import xlrd
    xlrd.open_workbook(fname)
    firstSheet = book.sheet_by_index(0)
    v = Vocabulary(title='New Import')
    v.save()
    vid = Vocabulary.objects.get(id=v.id)
    conceptstack = []
    for row in range(firstSheet.nrows):
        blank = 0
        while True:
            cell = firstSheet.cell(row, blank)
            if cell.value:
                break
            blank += 1
        concept = Concept(vocabulary=vid, name=cell.value)
        concept.save()
        if len(conceptstack) < blank:
            raise XLRDParseError
        if len(conceptstack) > blank:
            for i in range(len(conceptstack) - blank):
                conceptstack.pop()
        if conceptstack:
            concept.parent = conceptstack[-1]
            concept.save()
        conceptstack.append(concept)

load_xls('/home/frank/top-navigation.xls')

Keywords: hierarchy, import tree from Excel, import comma/tab delimited hierarchy, import categories from Excel, Python Django XLRD tree import