Python解析excel文件并存入sqlite数据库

   <p>最近由于工作上的需求 需要使用Python解析excel文件并存入sqlite 就此做个总结<br> 功能：<br> 1.数据库设计 建立数据库<br> 2.Python解析excel文件<br> 3.Python读取文件名并解析<br> 4.将解析的数据存储入库</p>    <h2><strong>一 建立数据库 </strong></h2>    <p>根据需求建立数据库,建立了两个表，并保证了可以将数据存储到已有的数据库中，代码如下：</p>    <pre>  <code class="language-python">import sqlite3    def createDataBase():  cn = sqlite3.connect('check.db')    cn.execute('''CREATE TABLE IF NOT EXISTS TB_CHECK  (ID integer PRIMARY KEY AUTOINCREMENT,  NUMBER INTEGER,  ITEM TEXT,  REFERENCE TEXT,  SUMMARY TEXT,  OBJECT TEXT,  METHOD TEXT,  CONDITION TEXT,  VALUE TEXT,  RESULT TEXT,  SCORE TEXT,  REMARKS TEXT,  PROVINCE TEXT,  TIME TEXT);''')    cn.execute('''CREATE TABLE IF NOT EXISTS TB_SCORE  (ID integer PRIMARY KEY AUTOINCREMENT,  PROVINCE TEXT,  TIME TEXT,  FILETYPE TEXT,  SCORE INTEGER);''')    if __name__ == '__main__':  createDataBase()  </code></pre>    <h2>二 使用Python解析excel</h2>    <p>Python中的xlrd模块用来解析excel。相关功能介绍如下：<br> 1. 导入<br> import xlrd<br> 2. 读取数据<br> data = xlrd.open_workbook('file.xls')<br> 3. 功能<br> （1） 通过索引获取<br> table = data.sheet()[0]<br> table = data.sheet_by_index(0)<br> （2）通过名称获取<br> table = data.sheet_by_name(u'sheet1')<br> （3）获取整行和整列的值（数组)<br> table.row_values(i)<br> table.col_values(i)<br> （4）获取行数和列数<br> nrows = table.nrows<br> ncols = table.ncols<br> （5）循环行列表数据<br> for i in range(nrows):<br> print table.row_values(i)<br> （6）单元格<br> cell_A1 = table.cell(0,0).value<br> （7）使用行列索引<br> cell_A1 = table.cell(0,0).value</p>    <p>练习代码：</p>    <pre>  <code class="language-python">import xlrd  import xlwt  from datetime import date,datetime    def read_excel():  # 打开文件  workbook = xlrd.open_workbook(r'file.xls')  # 获取所有sheet  sheet_name = workbook.sheet_names()[0]  sheet = workbook.sheet_by_name(sheet_name)    #获取一行的内容  for i in range(6,sheet.nrows):  for j in range(0,sheet.ncols):  print sheet.cell(i,j).value.encode('utf-8')    if __name__ == '__main__':  read_excel()  </code></pre>    <h2>三 Python读取文件名并解析</h2>    <p>为了将各个文件的数据加以区分，需要将文件名中标志性字段入库，解析文件的代码如下：</p>    <pre>  <code class="language-python">import os    def getFileList(dir,wildcard,recursion):      os.chdir(dir)        fileList = []      check_province = []      check_time = []      file_type = []        exts = wildcard.split(" ")      files = os.listdir(dir)      for name in files:          fullname=os.path.join(dir,name)          if(os.path.isdir(fullname) & recursion):              getFileList(fullname,wildcard,recursion)          else:              for ext in exts:                  if(name.endswith(ext)):                      fileList.append(name)                      check_province.append(name.split('-')[1])                      check_time.append(name.split('-')[0])                      file_type.append(name.split('-')[2])      return fileList,check_time,check_province,file_type  </code></pre>    <p>在接下来的使用中 会遇到编码问题 所以在使用这些字段时需要先转码，编写转码函数如下：</p>    <pre>  <code class="language-python">#转码函数  def changeCode(name):              name = name.decode('GBK')              name = name.encode('UTF-8')              return name  </code></pre>    <h2>四  解析excel文件并将其存储到sqlite</h2>    <p>Python连接数据库 选取了Python自带的sqlite数据库 相对简单 在此不做太多介绍 如果大家对Python操作sqlite有疑惑的话 个人推荐菜鸟教程～</p>    <p>下面是解析excel文件并存入数据库,其中包含了判断单元格内容：</p>    <pre>  <code class="language-python">def readExcel(filename,cn,check_province,check_time,FileType):    #读取    workbook = xlrd.open_workbook(filename)    # 获取sheet    sheet_name = workbook.sheet_names()[0]            sheet = workbook.sheet_by_name(sheet_name)      check_Item = 'a'      itemCount = 0    score = 0      second = sheet.cell(7,1).value.encode('utf-8')      for i in range(7,sheet.nrows):        if sheet.cell(i,1).value.encode('utf-8') == second:            check_Item = sheet.cell(i,0).value.encode('utf-8')            continue          temp = []        for j in range(0,sheet.ncols):            temp.append(sheet.cell(i,j).value.encode('utf-8'))          answer = sheet.cell(i,7).value.encode('utf-8')          if answer == "yes" or answer == "no":            score = score + 1          if answer == "other":            print "!!!Failed to import'%s'" % (filename)            print "!!!Please Choose an Right Answer for '%s'--------"%(filename)            break        else:            cn.execute("insert into TB_CHECK (ITEM,FIELD,TYPE,CONTENT,"                       "ATTRIBUTE,CHECKPOINT,REMARKS,ANSWER,DESCRIPTION,"                       "SUGGESTION,PROVINCE,TIME,STYLE) "                       "values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"                       ""%(temp[0],temp[1],temp[2],temp[3],temp[4],temp[5],temp[6],temp[7],temp[8],temp[9],check_province,check_time,check_Item))              itemCount = itemCount + 1    if itemCount != 0:        score = round(score * (100 / itemCount), 2)        cn.execute("insert into TB_SCORE (PROVINCE,TIME,FILETYPE,SCORE) "               "values('%s','%s','%s','%.2f')"%(check_province,check_time,FileType,score))        print "Successful for'%s'--------" % (filename)    cn.commit()  </code></pre>    <p><br> 整合上述功能：</p>    <pre>  <code class="language-python">def importData(path):      # 数据库      createDataBase()      database = sqlite3.connect("check.db")        #文件类型      wildcard = ".xls"        list = getFileList(path,wildcard,1)        nfiles = len(list[0])      #文件名      file = list[0]      #时间      time = list[1]      #省份      province = list[2]      # #文件类型      FileType = list[3]        for count in range(0,nfiles):          filename = file[count]          check_province = changeCode(province[count])          check_time = time[count]          File_type = changeCode(FileType[count])          readExcel(filename,database,check_province,check_time,File_type)    if __name__ == '__main__':      if len(sys.argv) != 2:          print "Wrong Parameters"      else:          path = sys.argv[1]          importData(path)  </code></pre>    <p>这就是解析excel文件并存入sqlite的主要代码<br> 完整代码见：https://github.com/Ben0825/Python</p>    <p> </p>    <p>来自： <a href="/misc/goto?guid=4959673627678882738" rel="nofollow">http://www.cnblogs.com/ybjourney/p/5523878.html</a></p>    <p> </p>