def make_protected(workbook_male, workbook_female, worksheet_male, worksheet_female, country):

    row_country_m, col_country_m = find_row_col_index(country, worksheet_male)
    row_country_f, col_country_f = find_row_col_index(country, worksheet_female)

    wb_male = copy(workbook_male)
    wb_female = copy(workbook_female)

    for i in range(17, 258):
        for j in range(3, 50):
            if i != row_country_m:
                wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;'))
            else:
                wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;'))
    wb_male.get_sheet(0).set_protect(True)
    wb_male.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.XLS')

    for i in range(17, 258):
        for j in range(3, 50):
            if i != row_country_f:
                wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;'))
            else:
                wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;'))
    wb_female.get_sheet(0).set_protect(True)
    wb_female.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_FEMALE.XLS')

def main():
    while(1):
        file_input = raw_input("Nombre del archivo: ")
        ask = raw_input("Esta seguro? (y/n)  ")
        if ask == 'y':
            break
            
     
    while (1):
        row_input = raw_input("Introducir el numero de la fila: ")
        row = int(row_input)
        ask = raw_input("Esta seguro? (y/n)  ")
        if ask == 'y':
            break
            
    
    array = []
    array = ServerConnect()
    
    #Modificador de excell
    rb = open_workbook(file_input, formatting_info=True)
    wb = copy(rb)             # a writable copy (I can't read values out of this, only write to it)
    w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
    
    #Escribir en excell
    counter = 1
    for x in array:
        w_sheet.write(row - 1, counter, x)
        counter += 1
    
    #Salvar el documento
    wb.save(file_input)

    #Leer el archivo para encontrar difenrencia
    workbook = open_workbook(file_input)
    worksheet = workbook.sheet_by_index(0)
    cell_value1 = worksheet.cell_value(row - 1, 4)
    value1 = float(cell_value1)
    cell_value2 = worksheet.cell_value(row - 2, 4)
    value2 = float(cell_value2)
    dif = value1 -value2
    
    #Modificador de excell
    rb = open_workbook(file_input, formatting_info=True)
    wb = copy(rb)             # a writable copy (I can't read values out of this, only write to it)
    w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy
    
    w_sheet.write(row - 1, 6, dif)
    
    wb.save(file_input)

    raw_input("Datos actualizados!")

def write(name,match,data):
	file = name + '.xls'
	try:
		w = copy(open_workbook(file))
	except:
		initExcel(name)
		w = copy(open_workbook(file))
	row = match
	col = 1
	for d in data:
		w.get_sheet(0).write(row,col,str(d))
		col += 1
	w.save(file)
	print("Data written to " + file)

def main(argv):
    try:
       opts, args = getopt.getopt(argv,"hi:d:")
    except getopt.GetoptError:
       print sys.argv[0] + ' -i <inputfile> -d <directory>'
       sys.exit(2)
    for opt, arg in opts:
       if opt == '-h':
          print sys.argv[0] + ' -i <inputfile> -d <directory>'
          sys.exit()
       elif opt in ("-i"):
          inputfile = arg
       elif opt in ("-d"):
          directory = arg
    onlyfiles = [ f for f in listdir(directory) if isfile(join(directory,f)) ]
    
    wb = open_workbook(inputfile)

    sheet = wb.sheet_by_index(0)

    book = copy(wb)
    csheet = book.get_sheet(0)
    
    for row in range(sheet.nrows):
        v = sheet.cell(row,1).value
        if isinstance(v, float):
            v = int(v)
        name = str(v) + '.pdf'
        check = name in onlyfiles;
        if check:
            csheet.write(row,0,'1')
            
    book.save('result.xls')

    def write(self, tpl, dest_file, tuple_args):
        '''
        Args:
          tuple_args: 从数据库获取的结果集(list)
          dest_file: 生成的excel文件名
          tpl: excel模板
        '''

        # 单元格样式
        style = xlwt.XFStyle()
        font = xlwt.Font()
        font.name = 'SimSun' #设置字体
        style.font = font
        tpl_book = open_workbook(tpl, formatting_info=True)
        target = copy(tpl_book)
        i = 0
        for arg in tuple_args:
            sheet = target.get_sheet(i) # a writable copy
            i += 1
            size = len(arg)
            if size == 0:
                continue
            cols = len(arg[0])
            for row in xrange(0,size):
                for col in xrange(0, cols):
                    sheet.write(row + 1, col, arg[row][col], style)
        target.save(dest_file)

def GetQ(session, baseurl, row):
    rb = open_workbook('zhihu.xlsx')
    wb = copy(rb)
    ws = wb.get_sheet(0)

    for i in range(1, 11):
        url = ''.join([baseurl, '?page=', str(i)])
        topicObj = topic(session, url)
        row_topic = row
        for ques in topicObj.question():
            ws.write(row_topic, 0, ques)
            row_topic += 1
            print '%d, 1' % row_topic
        links = topicObj.links()
        for link in links:
            try:
                print link
                answerObj = answer(session, link)
                ws.write(row, 1, answerObj.answerednum())
                ws.write(row, 2, answerObj.type())
                ws.write(row, 3, answerObj.watched())
                row += 1
            except TypeError:
                continue
            except AttributeError:
                continue
            print '%d, 123' % row
        wb.save('zhihu.xlsx')

    return row

def init():

    global ws,ws2,ws3,firstTime,wb,rs

    if os.path.exists(excelFileName):

        rb = open_workbook(excelFileName, formatting_info=True)

        rs = rb.sheet_by_index(0)

        wb = copy(rb)

        ws = wb.get_sheet(0)

        ws2 = wb.get_sheet(1)

        ws3 = wb.get_sheet(2)

        firstTime = False

    else:

        wb = Workbook()

        ws = wb.add_sheet('string')

        ws2 = wb.add_sheet('string_array')

        ws3 = wb.add_sheet('plurals')

        firstTime = True

def main():
	data = xlrd.open_workbook('result.xls')
	t0 = data.sheets()[0]#第一张表
	rows = t0.nrows
	pubDate = '2018-04-12T12:00:00'
	
	
	json_xls = copy(data)
	table = json_xls.get_sheet(0)
	
	for i in range(1,rows):#行
		id,title,headline,QuestionacceptedAnswer = '','','',''
		for j in range(11):#列
			if j == 7:
				id = 'https://m.jd.com/phb/zhishi/'+t0.row_values(i)[j]+'.html'				
			elif j == 8:
				title = t0.row_values(i)[j]
				headline = t0.row_values(i)[j]				
			elif j == 9:
				QuestionacceptedAnswer = t0.row_values(i)[j]
			
		_str = generate_json(id,title,pubDate,headline,QuestionacceptedAnswer)
		
		if len(t0.row_values(i)[10]+_str) < 32767:
			table.write(i,11,t0.row_values(i)[10]+_str)
		else:
			table.write(i,11,'String longer than 32767 characters')
		print i
		
	json_xls.save('result.xls')
	
	
	'''

	def appendexcel(self,whichone,**values):
		rsheet=self.rxld.sheet_by_index(whichone)
		rows=rsheet.nrows-1
		while(rsheet.cell_value(rows,5)==''):
			rows-=1
		wxls=copy(self.rxld)
		wsheet=wxls.get_sheet(whichone)
		wsheet.write(rows,0,values['name'],self.style1)
		wsheet.write(rows,1,values['address'],self.style1)
		wsheet.write(rows,2,values['product'],self.style1)
		wsheet.write(rows,3,values['price'],self.style1)
		wsheet.write(rows,4,values['counts'],self.style1)
		wsheet.write(rows,5,values['fee'],self.style1)
		wsheet.write(rows,6,float(values['price'])*int(values['counts'])+float(values['fee']),self.style1)
		wsheet.write(rows,7,'%.2f'%float(self.changerate),self.style1)
		wsheet.write(rows,8,'%.2f'%((float(values['price'])*int(values['counts'])+float(values['fee']))/100.0*float(self.changerate)),self.style1)
		total=0
		maxfee=float(values['fee'])
		maxrate=float(values['rate'])
		for i in xrange(1,rows):
			fee=float(rsheet.cell_value(i,5))
			rate=float(rsheet.cell_value(i,7))
			total+=float(rsheet.cell_value(i,6))-fee
			if fee>maxfee:
				maxfee=fee
			if rate>maxrate:
				maxrate=rate			
		wsheet.write(rows+1,5,maxfee,self.style2)
		wsheet.write(rows+1,6,total+maxfee+float(values['price'])*int(values['counts']),self.style2)
		wsheet.write(rows+1,7,'%.2f'%maxrate,self.style2)
		wsheet.write(rows+1,8,'%.2f'%(maxrate*(total+float(values['fee'])+float(values['price'])*int(values['counts']))/100.0),self.style2)
		wxls.save(self.file)

def new_row(path,newpath,val1,val2,
            sheet_index=None,sheet_name=None,
            row1=None, col1=None):
    logger.info(str(("new row for sheet",sheet_index, val1,val2)))
    # open our xls file, there's lots of extra default options in this call, 
    # for logging etc. take a look at the docs
    book = xlrd.open_workbook(path) 

    worksheet = None
    if sheet_name:
        worksheet = book.sheet_by_name(sheet_name)
        sheet_index = worksheet.number
    else:
        worksheet = book.sheet_by_index(sheet_index) 
    num_rows = worksheet.nrows

    wb = copy(book)
    # doesnt work: wb.encoding='utf-8'
    worksheet = wb.get_sheet(sheet_index) 
    
    start_col = col1 or 0
    start_row = row1 or num_rows
    worksheet.write(start_row,start_col,val1)
    # NOTE: will lose chars in ascii encoding
    worksheet.write(start_row,start_col+1,smart_str(val2, 'ascii', errors='ignore'))    
    wb.save(newpath)   
    print 'wrote new workbook row:', newpath, val1, val2

def write_excelfile(filePath, fileName, sheetName, xCell, yCell, inValue):
        sourceFile = os.path.join(filePath, fileName)
        wWorkBook = None
        sheetIndex = None
        
        if os.path.isfile(sourceFile):
                sWorkBook = xlrd.open_workbook(sourceFile)
                if sheetName in sWorkBook.sheet_names():
                        sheetIndex = sWorkBook.sheet_names().index(sheetName)
                else:
                        print "WRN - Sheet %s not exist!" % sheetName

                wWorkBook = copy(sWorkBook)
        else:
                print "ERR - Source excel %s is not exist!" % sourceFile
                print "INF - Create new excel named %s" % sourceFile
		
                wWorkBook = xlwt.Workbook(encoding = 'utf-8')

        if wWorkBook:
                if sheetIndex != None:
                        wSheet = wWorkBook.get_sheet(sheetIndex)
                        wSheet.write(int(xCell), int(yCell), inValue)
                else:
                        print "INF - Add new sheet named %s" % sheetName
                        wSheet = wWorkBook.add_sheet(sheetName, cell_overwrite_ok = True)
                        wSheet.write(int(xCell), int(yCell), inValue)

                wWorkBook.save(sourceFile)
                return True
        else:
                return False

def edit_file(filename, base_id=[]):
    font0 = xlwt.Font()
    font0.name = 'Times New Roman'
    font0.colour_index = 2  # 红色
    font0.bold = True

    style0 = xlwt.XFStyle()
    style0.font = font0

    style1 = xlwt.XFStyle()
    style1.num_format_str = 'YYYY/MM/DD'  # 对日期格式的处理

    rb = open_workbook(filename)
    wb = copy(rb)
    ws = wb.get_sheet(0)
    # table = rb.get_sheet()[0] #这个方法好像过时了，这里会报错
    table = rb.sheets()[0]
    for row_number in range(table.nrows):
        if row_number:
            if table.row_values(row_number)[0] in base_id:
                print xldate.xldate_as_datetime(table.row_values(row_number)[1], 0)
                ws.write(row_number, 0, table.row_values(row_number)[0], style0)  # 这个地方需要改一个颜色
            ws.write(row_number, 1, xldate.xldate_as_datetime(table.row_values(row_number)[1], 0),style1)  # 这个地方需要改一个颜色

    wb.save(filename)
    # wb.save('b' + filename)# 可以把文件保存为另外的名字，原文件不会改变
    print 'ok'

    def updateDB(self):
        print "正在写入数据库.....\n"
        rb = open_workbook(self.FileName)
        sheet = rb.sheets()[self.OutFileSheetNumber]
        wb = copy(rb)
        row = wb.get_sheet(self.OutFileSheetNumber).row(rb.sheets()[self.OutFileSheetNumber].nrows)
        row.write(0, self.dataTag)
        row.write(1, encode(self.Time))
        row.write(2, self.userid)
        row.write(3, encode(self.username))
        row.write(4, encode(self.Product))
        row.write(5, encode(self.Machine))
        row.write(6, encode(self.shift))
        for parm in self.ParmDict:
            for col in range(sheet.ncols):
                cellValue = sheet.cell(self.OutFileKeyRowNumber, col).value
                if cellValue == unicode(parm, "cp936"):
                    data = self.ParmDict[parm]
                    if type(data) == type(u"hh"):
                        writedata = data
                    else:
                        writedata = encode(data)
                    row.write(col, writedata)

        # 保存到文件
        wb.save(self.FileName)
        # 拷贝一份
        try:
            shutil.copyfile(self.FileName, self.BackupFileName)
        except IOError:
            print "备份文件只读"

        print "写入数据库完成！\n"

def ucf_to_xls(ifile_ucf,ofile_xls,sheet_name):
    file_path = ofile_xls

    book = open_workbook(file_path,formatting_info=True)
    for index in range(book.nsheets):
	worksheet_name = book.sheet_by_index(index)
	if worksheet_name.name == sheet_name:
	    index_sheet_numb = index

    # use r_sheet if you want to make conditional writing to sheets
    #r_sheet = book.sheet_by_index(index_sheet_numb) # read only copy to introspect the file
    wb = copy(book) # a writable copy (can't read values out of this, only write to it)
    w_sheet = wb.get_sheet(index_sheet_numb) # sheet write within writable copy

    ucfMap = extractUCF(ifile_ucf)    # Name of file to 
    keylist = ucfMap.keys()
    keylist.sort()
    excelMapCoords = routeExcel()     

    i = 0
    for key in keylist:
	x,y = excelMapCoords[str(key)]
	changeCell(w_sheet,x,y,ucfMap[key]) # Write to Sheet without changing format
	#w_sheet.write(x,y,ucfMap[key])  # Write to sheet but changes format
	i += 1
    
    # Save .xls file into a diff file name
    wb.save(os.path.splitext(file_path)[-2]+"_rv"+os.path.splitext(file_path)[-1])

def dealwithFile(fileName):
    try:
        xls = xlrd.open_workbook(fileName)
        sheetNames = xls.sheet_names()
        # sheet = xls.sheet_by_name("Sheet1")
        for sheetName in sheetNames:
            try:
                sheetName1 = str(sheetName).upper().replace('SELECT ', '')
                print 'sheetName:' + sheetName1
                if 'SQL' == sheetName1:
                    continue
                workbook = xlrd.open_workbook(BaseDicPath + "/" + sheetName1 + ".xls")
                workbook_t = copy(workbook)
                sheet_t = workbook_t.add_sheet(str('Example'), cell_overwrite_ok=True)
                cur_sheet = xls.sheet_by_name(sheetName)
                for row in range(0, cur_sheet.nrows):
                    for col in range(0, cur_sheet.ncols):
                        sheet_t.write(row, col, cur_sheet.cell_value(row, col), style)

                workbook_t.save(BaseDicPath + "/" + sheetName1 + ".xls")
                print sheetName1, ' gen sucess'
            except Exception, e:
                print Exception, ":", e
    except Exception, e:
        print Exception, ":", e

    def writeXls(self,book):
        print "write xls ..."
        #sheet=book
        #r_xls = xlrd.open_workbook(filename) 
        sheet_reader = book.sheet_by_index(5)
        w_xls = copy(book) 
        sheet_write = w_xls.get_sheet(5) 
        cols = sheet_reader.ncols 
        if sheet_reader.nrows > 0 and sheet_reader.ncols > 0:
            #for row in range(7, r_sheet.nrows):
            for row in range(7, sheet_reader.nrows ):    
                if len(sheet_reader.cell(row, 0).value)!=12:
                    continue
                banxue=self.findBanxue(sheet_reader.cell(row, 0).value)
                #print banxue.decode('utf-8')
                sheet_write.write(row, 15, banxue.decode('utf-8')) 

        sheet_reader = book.sheet_by_index(8)
        sheet_write = w_xls.get_sheet(8) 
  

        if sheet_reader.nrows > 0 and sheet_reader.ncols > 0:
            #for row in range(7, r_sheet.nrows):
            for row in range(7, sheet_reader.nrows ):    
                if len(sheet_reader.cell(row, 0).value)<=0:
                    continue
                jincai=self.findJincai(sheet_reader.cell(row, 0).value)
                #print banxue.decode('utf-8')
                sheet_write.write(row, 21, jincai.decode('utf-8')) 


        w_xls.save(self.outputFile)

def add_result(status1,tc_no1,exp_result1,act_result1,comments1):
       global ws
       global Locaitonfile
       status = status1
       tc_no = tc_no1
       act_result = act_result1
       exp_result = exp_result1
       comment = comments1
       #fail_color = easyxf('pattern: pattern solid, fore_colour red')
       #pass_color = easyxf('pattern: pattern solid, fore_colour green')
       wbR = xlrd.open_workbook(Locaitonfile)
       ws1 = wbR.sheet_by_name('Summary')
       ws = wbR.sheet_by_name('Validations')
       row_count = ws.nrows
       print row_count
       wbW = copy(wbR)       
       wbW.get_sheet(1).write(row_count,0,status)
       wbW.get_sheet(1).write(row_count,1,tc_no)
       wbW.get_sheet(1).write(row_count,2,exp_result)
       wbW.get_sheet(1).write(row_count,3,act_result)
       wbW.get_sheet(1).write(row_count,4,comment)
       wbW.get_sheet(1).col(0).width = 6000
       wbW.get_sheet(1).col(1).width = 4000
       wbW.get_sheet(1).col(2).width = 20000
       wbW.get_sheet(1).col(3).width = 20000
       wbW.get_sheet(1).col(4).width = 20000
       wbW.save(Locaitonfile)
       summary(Locaitonfile)

def new_col(path,newpath,val1,val2,
            sheet_index=None,sheet_name=None,
            row1=None, col1=None):
    logger.info(str(('new column for sheet', sheet_index, val1, val2)))
    # open our xls file, there's lots of extra default options in this call, 
    # for logging etc. take a look at the docs
    book = xlrd.open_workbook(path) 

    worksheet = None
    if sheet_name:
        worksheet = book.sheet_by_name(sheet_name)
        sheet_index = worksheet.number
    else:
        worksheet = book.sheet_by_index(sheet_index) 
    num_rows = worksheet.nrows
    num_cols = worksheet.ncols

    wb = copy(book)
    worksheet = wb.get_sheet(sheet_index) 

    start_col = col1 or num_cols
    start_row = row1 or 0

    worksheet.write(start_row,start_col,val1)
    for x in range(start_row+1,num_rows):
        #worksheet.write(x,start_col,val2)
        # NOTE: will lose chars in ascii encoding
        worksheet.write(x,start_col,smart_str(val2, 'ascii', errors='ignore'))    
    wb.save(newpath)   
    print 'wrote new workbook col to:', newpath, val1, val2

 def getstress(self, path, name):           
       f = setfont.Font(0, 250)
       f1 = setfont.Font(4, 300)
       
       style = xlwt.XFStyle()
       style1 = xlwt.XFStyle()
       
       style.font = f.fontset(0, 250)
       style1.font = f.fontset(4, 300)
       
       try:
             stress = self.getstressdata(path)     
             rb = open_workbook(os.path.join(path, '('+name+')'+'performance.xls'), formatting_info=True)       
             wb = copy(rb) 
             w_sheet = wb.add_sheet('stress')
             w_sheet.write(0, 0, u'压力测试报告', style1)
             
       except KeyboardInterrupt:
             pass
       try:                  
             for i in range(0,10):
                   w_sheet.col(i).width = 0x0d00 + 2500    
            
             for i in range(len(stress)):
                   for j in range(len(stress[i])):
                               w_sheet.write(i+1, j, unicode(stress[i][j], 'UTF-8'), style)
       except KeyboardInterrupt:
             pass
       wb.save(os.path.join(path, '('+name+')'+'performance.xls'))

def useTemplate(rows):


    id_ROW = 6 # 0 based (subtract 1 from excel row number)
    col_name = 1
    col_description = 2
    col_avalaible = 3
    
    rb = open_workbook('./Book.xlt',formatting_info=True)

    r_sheet = rb.sheet_by_index(0) # read only copy to introspect the file
    wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
    w_sheet = wb.get_sheet(0) # the sheet to write to within the writable copy


    for row in rows:
      
        setOutCell(w_sheet, col_name, id_ROW, row['name'])
        setOutCell(w_sheet, col_description, id_ROW, row['description'])
        setOutCell(w_sheet, col_avalaible, id_ROW, row['available'])
        #w_sheet.write(id_ROW, col_name, row['name'])
        #w_sheet.write(id_ROW, col_description, row['description'])
        #w_sheet.write(id_ROW, col_avalaible, row['available'])
        id_ROW = id_ROW + 1

    output = StringIO.StringIO()
    wb.save(output)

    return output