text: #!/usr/bin/env python3
  # -*- coding: utf-8 -*-
  
  #------------------------------------------------------------------------------
  # Function   : This scrip is used for convert testcase's macro to number
  # Enviroment : python 3.6.2
  # Author     : Shibo Jiang
  # Time       : 2018.4.4
  # Version    : 0.1
  # Notes      : None
  #------------------------------------------------------------------------------
  import xlrd
  import openpyxl
  
  # Maro define
  FILE = 'CANSigRec_TestCase.xlsx'
  SHEET_DEF = 'TestCase_MacroDef'
  SHEET_OUT = 'TestCase_convert'
  SHEET_ORGIN = 'TestCase_orgin'
  
  def test_case_convert():
      # Get excel files and sheets
      work_book = xlrd.open_workbook(FILE)
      macro_sheet = work_book.sheet_by_name(SHEET_DEF)
      orgin_sheet = work_book.sheet_by_name(SHEET_ORGIN)
  
      # Get macro_sheet data and creat dict
      macro_dict = dict()
      for i in range(macro_sheet.nrows):
          data = macro_sheet.row_values(i)
          # Set dict
          macro_dict[str(data[0])] = data[1]
      
      # Get orgin sheet and use dict's value to replace out sheet
      w_row = list()
      w_col = list()
      w_value = list()
      for temp_row in range(orgin_sheet.nrows):
          temp_list = orgin_sheet.row_values(temp_row)
          temp_col = 0
          for temp_data in temp_list:
              if temp_data in macro_dict:
                  w_row.append(temp_row)
                  w_col.append(temp_col)
                  w_value.append(macro_dict[temp_data])
              temp_col = temp_col + 1
  
      # Write new value to out sheet
      write_book = openpyxl.load_workbook(FILE)
      out_sheet = write_book.get_sheet_by_name(SHEET_OUT)
      for i in range(len(w_row)):
          out_sheet.cell(row = w_row[i],column = w_col[i]).value = w_value[i]
  
  # Run script
  test_case_convert()
  