读取excel不同列生成对应的strings或者json文件

作者: 辛乐 | 来源:发表于2019-10-24 14:12 被阅读0次

读取excel不同列生成对应的strings或者json文件
SheetJS实现excel预览编辑
PHPExcel-将JSON数据写入Excel
python3 基于pandas读写Excel
Python学习日记（二）
离网用户预警
R读取excel文件的最佳方式
R语言读写excel文件2021.2.24
iOS 多国语言创建
iOS 生成Excel文件（并手机访问）

1.xlrd的使用
2.字符串的逻辑处理

#!/usr/bin/env python 
# coding:utf-8

#处理中文字符的情况
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import xlrd

# ****************** 1.0 读取某个tab转换得到strings文件
def read(filename, sheetNo=0 ,keyCol=0,valueCol=1,startRow=0,endRow=0,toFileName='apptrans'):
    book=xlrd.open_workbook(filename)
    sh=book.sheet_by_index(sheetNo)
    cols = sh.ncols
    rows = sh.nrows
    print 'cols=',cols, 'rows=',rows
    resultStr = ''
    for r in range(rows): # cols and rows start from 0
        if endRow == 0:
            endRow = range(rows)

        if r > startRow and r < endRow:
            key = sh.cell_value(rowx=r, colx=keyCol)
            key = str(key)
            key = key.replace('"', '\\"')  # 双引号替换
            key = key.replace("\n", "")  # 换行符替换
            if key == "":
                continue
            value = sh.cell_value(rowx=r, colx=valueCol)
            value = str(value)
            value = value.replace('"', '\\"')
            value = value.replace("\n", "")
            if value == "":
                continue
            # print '\"%s\"=\"%s\";' %(key,value)
            rowStr = '\"%s\"=\"%s\";\n' % (key, value)
            resultStr = resultStr + rowStr

    # '/Users/le.xin/Desktop/apptrans.strings'
    # fileName = 'apptrans'+str(valueCol)
    filePath = '/Users/apbc/Desktop/谷歌文档多语言/'+toFileName+'.strings'
    f = open(filePath,'w')
    # print resultStr
    f.write(resultStr)
    f.close()
    print "保存成功"


# readToJson("Languagepack.DCEX.xlsx",0,0,1,0,'japanese')
# read("Languagepack.DCEX.xlsx",1,1,2,714,760,'cn')
# read("Languagepack.DCEX.xlsx",4,1,3,423,451,'ko')

# read("Languagepack.DCEX.xlsx",0,2,4,0,0,'vi0')
# read("Languagepack.DCEX.xlsx",1,2,4,0,0,'vi1')
# read("Languagepack.DCEX.xlsx",2,2,4,0,0,'vi2')
# read("Languagepack.DCEX.xlsx",3,2,4,0,0,'vi3')
# read("Languagepack.DCEX.xlsx",4,2,4,0,0,'vi4')
# read("Languagepack.DCEX.xlsx",5,2,4,0,0,'vi5')
# read("Languagepack.DCEX.xlsx",2,2,12,0,0,'tr2')
# read("Languagepack.DCEX.xlsx",5,2,12,0,0,'tr3')

# ****************** 2.0批量读取多个tab得到文件
def getStrs(filename, sheetNo=0 ,keyCol=0,valueCol=1,startRow=0,endRow=0,toFileName='apptrans'):
    book=xlrd.open_workbook(filename)
    sh=book.sheet_by_index(sheetNo)
    cols = sh.ncols
    rows = sh.nrows
    print 'cols=',cols, 'rows=',rows
    resultStr = ''
    for r in range(rows): # cols and rows start from 0
        if endRow == 0:
            endRow = range(rows)

        if r > startRow and r < endRow:
            key = sh.cell_value(rowx=r, colx=keyCol)
            key = str(key)
            key = key.replace('"', '\\"')  # 双引号替换
            key = key.replace("\n", "")  # 换行符替换
            if key == "":
                continue
            value = sh.cell_value(rowx=r, colx=valueCol)
            value = str(value)
            value = value.replace('"', '\\"')
            value = value.replace("\n", "")
            if value == "":
                continue
            # print '\"%s\"=\"%s\";' %(key,value)
            rowStr = '\"%s\"=\"%s\";\n' % (key, value)
            resultStr = resultStr + rowStr
    return resultStr

def getAllFilesStr(fileName = '文案',keyCol=0,valueCol=0):

    str1 = getStrs("Languagepack.DCEX.xlsx", 4, keyCol, valueCol, 0, 0, '')
    str2 = getStrs("Languagepack.DCEX.xlsx", 1, keyCol, valueCol, 0, 0, '')
    str3 = getStrs("Languagepack.DCEX.xlsx", 2, keyCol, valueCol, 0, 0, '')
    resultStr = str1 + str2 + str3
    filePath = '/Users/apbc/Desktop/谷歌文档多语言/' + fileName + '.strings'
    f = open(filePath, 'w')
    # print resultStr
    f.write(resultStr)
    f.close()
    print fileName + "保存成功"

# getAllFilesStr('vi',2,4)
# # getAllFilesStr('ar',2,6)
# getAllFilesStr('de',2,7)
# getAllFilesStr('es',2,8)
# # getAllFilesStr('th',2,9)
# # getAllFilesStr('pt',2,10)
# getAllFilesStr('id',2,13)

# ****************** 3.0 得到FAB和TUNE的协议的txt文件
def getFABAndTUNETxtFile(filename, sheetNo=0 ,keyCol=0,valueCol=1,startRow=0,endRow=0,toFileName='FABAndTUNE'):
    book=xlrd.open_workbook(filename)
    sh=book.sheet_by_index(sheetNo)
    cols = sh.ncols
    rows = sh.nrows
    print 'cols=',cols, 'rows=',rows
    resultStr = ''
    for r in range(rows): # cols and rows start from 0
        if endRow == 0:
            endRow = range(rows)

        if r > startRow and r < endRow:

            value = sh.cell_value(rowx=r, colx=valueCol)
            value = str(value)
            value = value.replace('"', '\\"')
            value = value.replace("\n", "")
            value = value.replace('\\"', '"')
            if value == "":
                continue
            # print '\"%s\"=\"%s\";' %(key,value)
            rowStr = '%s\n\r' % (value)
            resultStr = resultStr + rowStr

    # '/Users/le.xin/Desktop/apptrans.strings'
    # fileName = 'apptrans'+str(valueCol)
    filePath = '/Users/apbc/Desktop/谷歌文档多语言/'+toFileName+'.txt'
    f = open(filePath,'w')
    # print resultStr
    f.write(resultStr)
    f.close()
    print toFileName + "保存成功"

# getFABAndTUNETxtFile("Languagepack.DCEX.xlsx",2,0,8,2303,2353,'FABAndTUNE')

#***************************** 根据中文key得到对应的 文件
def getAllkeysAndValues(filename):
    filePath = "/Users/apbc/Desktop/谷歌文档多语言/" + filename + ".strings"
    print '打开文件' + filePath
    f = open(filePath, 'r')
    content = f.read()
    contentList = content.split(";")
    keys = []
    values = []
    dic = {}
    for str in contentList:

        if str.find("=") > 0:
            strList = str.split("=")

            key = strList[0]
            key = key.replace("\n", "")
            key = key.replace('"', '')

            value = strList[1]
            value = value.replace("\n", "")
            value = value.replace('"', '')

            keys.append(key)
            values.append(value)
            dic[key] = value

    f.close()
    return (keys,values,dic)

def getiOSStrings(fileName,toFileName='多语言',fileType='strings'):

    cnKeys = getAllkeysAndValues("cn")[0]
    cnDics = getAllkeysAndValues("cn")[2]

    dic = getAllkeysAndValues(fileName)[2]

    resultStr = ''
    if fileType == 'json':
        cnSet = set(cnKeys)
        cnKeys = list(cnSet)

    for key in cnKeys:

        cnKey = cnDics[key]
        if dic.has_key(cnKey):
            value = dic[cnKey]
        else:
            value = '未匹配'

        if fileType == 'json':

            firstStr = ''
            lastStr = ',\n'
            if key == cnKeys[0]:
                firstStr = '{\n'

            length = len(cnKeys)
            if key == cnKeys[length - 1]:
                lastStr = '\n}'

            resultStr = firstStr + resultStr + '\"' + key + '\":\"' + value + '\"' + lastStr

        else:
            resultStr = resultStr + '\"' + key + '\"=\"' + value + '\";\n'

    resultStr = resultStr.replace("\\", "\\\"")

    filePath = '/Users/apbc/Desktop/谷歌文档多语言/' + toFileName + '.' + fileType
    f = open(filePath, 'w')
    # print resultStr
    f.write(resultStr)
    f.close()
    print toFileName + '.' + fileType + "保存成功"


getAllFilesStr('es',2,8)
getiOSStrings("es","iOS_es",'json')