Python操作Excel生成数据库定义。

#!/usr/bin/env python
# -*- coding:utf8  -*-
import xlrd


class WorkBook():
    def __init__(self):
        pass
    def __del__(self):
        pass
    def readExcel(self,excelFile):
        book = xlrd.open_workbook(excelFile)
        return book
    def readSheet(self,sheet):
        lifiled = sheet.col(2)[5:]  #字段
        litype = sheet.col(4)[5:]   #类型
        lilength = sheet.col(5)[5:] #长度
        linull = sheet.col(6)[5:]   #是否为空
        #print "++++++++++++++++++++++++++++++++++++"

        print "CREATE TABLE DamsDB.dbo.%s" %(sheet.name,)

        print "("
        for i in range(0,(sheet.nrows - 5)):
            if lilength[i].value != "":
                if linull[i].value == "":
                    print "    %s %s (%s)," %(lifiled[i].value,litype[i].value,lilength[i].value)
                else:
                     print "    %s %s (%s) %s," %(lifiled[i].value,litype[i].value,lilength[i].value,"NOT NULL")
            else:
                if linull[i].value != "":
                    print "    %s %s %s," %(lifiled[i].value,litype[i].value,linull[i].value)
                else:
                     print "    %s %s," %(lifiled[i].value,litype[i].value)
        print ")"
        print "GO"
        print "\n"
        #print "++++++++++++++++++++++++++++++++++++"


if __name__ == "__main__":
    workbook = WorkBook()
    book = workbook.readExcel("d:\\a.xls")
    print "The number of worksheets is", book.nsheets

    for i in range(3, book.nsheets):
        #print "+++++++++++ i ==",i
        workbook.readSheet(book.sheet_by_index(i))

#!/usr/bin/env python
# -*- coding:utf8  -*-
import xlrd 


class WorkBook():
    def __init__(self):
        pass
    def __del__(self):
        pass
    def readExcel(self,excelFile):
        book = xlrd.open_workbook(excelFile)
        return book
    def readSheet(self,sheet):        
        lifiled = sheet.col(2)[5:]  #字段
        litype = sheet.col(4)[5:]   #类型
        lilength = sheet.col(5)[5:] #长度
        linull = sheet.col(6)[5:]   #是否为空
        #print "++++++++++++++++++++++++++++++++++++"
       
        print "CREATE TABLE DamsDB.dbo.%s" %(sheet.name,)
    
        print "("
        for i in range(0,(sheet.nrows - 5)):
            if lilength[i].value != "":
                if linull[i].value == "":
                    print "    %s %s (%s)," %(lifiled[i].value,litype[i].value,lilength[i].value)
                else:
                     print "    %s %s (%s) %s," %(lifiled[i].value,litype[i].value,lilength[i].value,"NOT NULL")
            else:
                if linull[i].value != "":
                    print "    %s %s %s," %(lifiled[i].value,litype[i].value,linull[i].value)
                else:
                     print "    %s %s," %(lifiled[i].value,litype[i].value)
        print ")"
        print "GO"
        print "\n"
        #print "++++++++++++++++++++++++++++++++++++"


if __name__ == "__main__":
    workbook = WorkBook()
    book = workbook.readExcel("d:\\a.xls")
    print "The number of worksheets is", book.nsheets
    
    for i in range(3, book.nsheets):
        #print "+++++++++++ i ==",i
        workbook.readSheet(book.sheet_by_index(i))

• (181.6 KB)
• 下载次数: 47