python 将中文转拼音后填充到url做参数并写入excel

 闲着没事写了个小工具,将中文转拼音后填充到url做参数并写如excel

 

一.先看下演示,是个什么东西

 

 

 

 

 

 

二.代码

　　代码用到一个中文转拼音的库,库是网上下的,稍微做了下修改,已经找不原来下载的地址了,然后需要装个pywin32库,用来写excel表格的,下面看代码.

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


from lib.chinese2pinyin import search
from time import sleep
import win32com.client as win32
import getopt, sys


class Excel(object):
    def __init__(self):
        app = 'Excel'
        xl = win32.gencache.EnsureDispatch('%s.Application' %app)
        ss = xl.Workbooks.Add()
        self.sh = ss.ActiveSheet
        xl.Visible = True

    def write(self, row, column, string):
        sleep(0.1)
        print string, row, column
        try:
            self.sh.Cells(row,column).Value = u'%s' %string
        except UnicodeDecodeError:
            self.sh.Cells(row,column).Value = '%s' %string



    
def Links(url,title,readfile):
    fobj = open(readfile, 'r')
    excel = Excel()
    title = title.split(',')
    for column,string in enumerate(title):
        column += 1
        excel.write(1,column, string)
    
    row = 2
    for eachline in fobj:
        LIST = eachline.split()
        PIN = []
        for ch in LIST:
            #ch = ch.decode('utf-8')
            #ch = ch.encode('gbk')
            result = search(ch)
            PIN.append(result)
        try:
            tp = tuple(PIN)
            link = url %tp
            LIST.append(link)
            print LIST
            
            for column,string in enumerate(LIST):
                column += 1
                excel.write(row,column, string)
        except TypeError,e:
            print e
        row += 1     
    fobj.close()

def usage():
    print u"Usage: python trf_excel.py -u http://www.xxcc.cn/?%s?%s?%s -t 计划,单元,关键词,链接"

def main():
    if len(sys.argv) <2:
        usage()
    try:
        opts, args = getopt.getopt(sys.argv[1:], "hu:t:", ["url=", "title="])
    except getopt.GetoptError as err:
        print str(err)
        usage()
        sys.exit(2)
    for o, a in opts:
        if o in ("-h"):
            usage()
        if o in ("-u", "--url"):
            url = a
            print url
        if o in ("-t", "--title"):
            title = a
            print title



    
    readfile = 'citiao.txt'
    Links(url,title,readfile)

    
if __name__ == '__main__':
    main()

 

可以命令行执行: python trf_excel.py -u http://www.xxcc.cn/?%s?%s?%s -t 字段1,字段2,字段3,链接

注意:url地址占位符用%s, -t 这个是生成excel字段标题,然后要转成中文的放到citiao.txt文件中

三.下载地址

已经放到github上了

git clone git@github.com:ca0gu0/tools.git