# -*- coding: utf-8 -*-

import sqlite3

import xlrd



data = xlrd.open_workbook('gk.xls')

con = sqlite3.connect('data.db')

cursor = con.cursor()

for x in range(0,4):

    table = data.sheets()[x]

    rows = table.nrows

    print 'rows:%s cols:%s'%(rows,table.ncols)

    print table.ncols

    for i in range(1, rows):

         rowData = table.row_values(i)

        departNo = rowData[0]

        departName = rowData[1]

        useDepart = rowData[2]

        jgxz = rowData[3]

        jgcj = rowData[4]

        zwsx = rowData[5]

        zwmc = rowData[6]

        zwjj = rowData[7]

        zwdm = rowData[8]

        kslb = rowData[9]

        bkrs = rowData[10]

        zy = rowData[11]

        xl = rowData[12]

        xw = rowData[13]

        zzmm = rowData[14]

        jcgznx = rowData[15]

        szyf = rowData[16]

        xbzyz = rowData[17]

        dxscg = rowData[18]

        tgjhjs = rowData[19]

        wxz = rowData[20]

        sfzms = rowData[21]

        msrybl = rowData[22]

        bz = rowData[23]

        zwfb = rowData[24]

        bmwz = rowData[25]

        zxdh1 = rowData[26]

        zxdh2 = rowData[27]

        zxdh3 = rowData[28]

        cursor.execute('insert into gk values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', (departNo,departName,useDepart,jgxz,jgcj,zwsx,zwmc,zwjj,zwdm,kslb,bkrs,zy,xl,xw,zzmm,jcgznx,szyf,xbzyz,dxscg,tgjhjs,wxz,sfzms,msrybl,bz,zwfb,bmwz,zxdh1,zxdh2,zxdh3))

con.commit()

con.close()

# -*- coding: utf-8 -*-

import sqlite3

import xlwt

# connect to sqlite

con = sqlite3.connect('data.db')

cursor = con.cursor()

cursor.execute("select * from gk where useDepart like '%永州%'")

# create excel

wbk = xlwt.Workbook(encoding='utf-8', style_compression=0)

sheet = wbk.add_sheet('sheet', cell_overwrite_ok=True)

column_head = (u'部门代码',u'部门名称',u'用人司局',u'机构性质',u'机构层级',u'职位属性',u'职位名称',u'职位简介',u'职位代码',u'考试类别',u'招考人数',u'专业',u'学历',u'学位',u'政治面貌',u'基层工作最低年限',u'三支一扶大学生',u'西部志愿者',u'大学生村官',u'特岗计划教师',u'无限制',u'是否在面试阶段组织专业能力测试',u'面试人员比例',u'备注',u'职位分布',u'部门网站',u'咨询电话1',u'咨询电话2',u'咨询电话3')

for i in range(len(column_head)):

    sheet.write(0,i,column_head[i])

i = 1

while 1:

    one = cursor.fetchone()

    if not one:

        break

    for j in range(len(one)):

        sheet.write(i,j,one[j])

    i += 1

# save excel

wbk.save('out.xls')

# close sqlite connection

con.commit()

con.close()