Read and Write python excel

This article once again demonstrates that python is a powerful tool for dealing with chores. But why are there so many chores? Recently, I received a job from my superiors to collect a large number of pictures, home pages, and other information on the Internet, and then fill in the information in the Excel form. One of the most boring jobs is to write down the English names of all people. Because they are all Chinese, this job is to test the Chinese pinyin level. So I thought of python. I found some information on the Internet and came up with the following ideas: 1. Someone has completed the conversion of Chinese characters to PinYin on github. I only need to use the ready-made information. 2. excel operations on python excel, because some low-quality blog posts on the Internet are misleading, many detours have taken place. In Python, xlrd (excel read) is generally used to read Excel files, and xlwt (excel write) is used to generate Excel files (the format of cells in Excel can be controlled ), note that you cannot use xlrd to read excel files: xlrd. the open_workbook () method returns xlrd. the Book type is read-only and cannot be operated on. The xlwt. Workbook () method returned by xlwt. Workbook can save excel files. Therefore, it is very easy to read and generate Excel files, but it is troublesome to modify existing Excel files. However, there is also an xlutils (depending on xlrd and xlwt) that provides the ability to copy and modify the content of an excel file. In practice, it only creates an pipeline between xlrd. Book and xlwt. Workbook. In this way, the remaining problem is to write the script. Now, the excel format is as follows: school_name name_cn name_enTianjin University Zhou Tian Tianjin University Han Dong Tianjin name_en. The Code is as follows: [python] view plaincopy #! /Usr/bin/python #-*-coding: UTF-8-*-from pinyin import PinYin # module for excel handle import xlrd from xlutils. copy import copy def name_tran (str): test = PinYin () test. load_word () str [0] family = test. hanzi2pinyin (string = str [0]) [0] last = u''print str [1:] for word in test. hanzi2pinyin (string = str [1:]): last = last + word name_en = last. title () + u'' + family. title () return name_en def file_fill (file_name, sheet _ Name, row_count): # open an Excel file to read data = xlrd. open_workbook (file_name) # obtain a worksheet # table = data. sheets () [0] # obtain from index order # table = data. sheet_by_name (sheet_name) # obtain table = data by name. sheet_by_index (3) # obtain through index order # using xlutils to modify excel wb = copy (data) # sheet obtained through get_sheet (), with the write () method ws = wb. get_sheet (3) for I in range (1, row_count): name_cn = table. cell (I, 1 ). value print name_cn try: name_en = name_tran (n Ame_cn) ws. write (I, 2, name_en) print name_en failed T: print I + 1, "th row fail to translate." wb. save (file_name) return "Over! "If _ name __= =" _ main _ ": file_fill (u" scholar&school.xls ", u" Scholars ", 442) after the code is run, the name_en column of the excel table is filled as follows: school_name name_cn name_enTianjin University Tian ZhouTianjin University Han Dong HanTianjin University Li Xing Li