{

    "file": "OrderDetail-2020-06-03.xls",

    "startRow": 1,

    "table": "tk_bill",

    "comitcount":50,

    "map": {

        "trade_id": "o",

        "tk_status": "p",

        "item_title": "h",

        "alipay_total_price": "r",

        "tk_paid_time": "c",

        "pub_share_pre_fee": "ad",

        "subsidy_fee": "w",

        "tk_settletime": "d"

    }

}

import math

import xlrd

import json

from Db import Db

db = Db()

def loadConfig():

    '''

    加载配置

    :return:

    '''

    with open("map.conf") as f:

        return json.load(f)

def chang26to10(value):

    '''

    26进制转10进制

    '''

    transfArray = {'A': 1, 'B': 2, 'C': 3, 'D': 4, 'E': 5, 'F': 6, 'G': 7, 'H': 8, 'I': 9, 'G': 10, 'K': 11, 'L': 12,

                   'M': 13,

                   'N': 14, 'O': 15, 'P': 16, 'Q': 17, 'R': 18, 'S': 19, 'T': 20, 'U': 21, 'V': 22, 'W': 23, 'X': 24,

                   'Y': 25, 'Z': 26};

    sum = 0

    length = len(value)

    for idx in range(length):

        char = value[idx]

        if char.upper() not in transfArray:

            raise Exception("无效字符:<%s>" % char)

        v = transfArray[char.upper()]

        tem = v * math.pow(26, length - idx - 1)

        sum = tem + sum

    return int(sum)

if __name__ == '__main__':

    importConfig = loadConfig()

    # 获取要导入的表名

    tableName = importConfig["table"]

    comitcount = importConfig["comitcount"]

    startRow = importConfig["startRow"]

    cols = []

    colsidx = []

    for k, v in importConfig["map"].items():

        cols.append(k)

        colsidx.append(v)

    sqlpre = "insert into %s(%s) values " % (tableName, ",".join(cols))

    xls = xlrd.open_workbook(importConfig["file"])

    sheet = xls.sheets()[0]

    startIdx = 0

    startIdx = startRow

    vlist = []

    for i in range(startIdx, sheet.nrows):

        print("处理第%s行" % i)

        temlist = [];

        for cidx in colsidx:

            s = "'%s'" % sheet.cell_value(i, chang26to10(cidx) - 1).replace("'", "''")

            temlist.append(s)

        vlist.append("(%s)" % ",".join(temlist))

        # 每50行提交一次

        if len(vlist) == comitcount:

            sql = sqlpre + ",".join(vlist)

            db.execSql(sql)

            vlist.clear()

            continue

    if len(vlist) > 0:

        sql = sqlpre + ",".join(vlist)

        db.execSql(sql)

        vlist.clear()

    print("处理完成")