def calculateWeightOfCompletion(unitId):
    """
    calculate the weight of completion catagory,note that
    we classify the completion by 4 and L4,respectively.
    @params:
        unitId: unitId of every institution
    @return:
        prob: weight of completion field of each institution
    """
    database_4 = xlrd.open_workbook('completion_4.xlsx')
    table_4 = database_4.sheet_by_name('Sheet1')
    id_4 = table_4.col_values(0)
    class_4 = table_4.col_values(2)

    database_L4 = xlrd.open_workbook('completion_L4.xlsx')
    table_L4 = database_L4.sheet_by_name('Sheet1')
    id_L4 = table_L4.col_values(0)
    class_L4 = table_L4.col_values(2)

    prob = []
    weight = [0.5,0.3,0.1,0.05,0.05]
    for u in unitId:
        ind1 = unitId.index(u)
        if u in id_4:
            ind2 = id_4.index(u)
            prob.append(weight[int(class_4[ind2])])
        elif u in id_L4:
            ind2 = id_L4.index(u)
            prob.append(weight[int(class_L4[ind2])])
        else:
            prob.append(0)
    return prob

def xl_read_flags(cf,ds,level,VariablesInFile):
    # First data row in Excel worksheets.
    FirstDataRow = int(cf['Files'][level]['xl1stDataRow']) - 1
    # Get the full name of the Excel file from the control file.
    xlFullName = cf['Files'][level]['xlFilePath']+cf['Files'][level]['xlFileName']
    # Get the Excel workbook object.
    if os.path.isfile(xlFullName):
        xlBook = xlrd.open_workbook(xlFullName)
    else:
        log.error(' Excel file '+xlFullName+' not found, choose another')
        xlFullName = get_xlfilename()
        if len(xlFullName)==0:
            return
        xlBook = xlrd.open_workbook(xlFullName)
    ds.globalattributes['xlFullName'] = xlFullName
    
    for ThisOne in VariablesInFile:
        if 'xl' in cf['Variables'][ThisOne].keys():
            log.info(' Getting flags for '+ThisOne+' from spreadsheet')
            ActiveSheet = xlBook.sheet_by_name('Flag')
            LastDataRow = int(ActiveSheet.nrows)
            HeaderRow = ActiveSheet.row_values(int(cf['Files'][level]['xlHeaderRow'])-1)
            if cf['Variables'][ThisOne]['xl']['name'] in HeaderRow:
                xlCol = HeaderRow.index(cf['Variables'][ThisOne]['xl']['name'])
                Values = ActiveSheet.col_values(xlCol)[FirstDataRow:LastDataRow]
                Types = ActiveSheet.col_types(xlCol)[FirstDataRow:LastDataRow]
                ds.series[ThisOne]['Flag'] = numpy.array([-9999]*len(Values),numpy.int32)
                for i in range(len(Values)):
                    if Types[i]==2: #xlType=3 means a date/time value, xlType=2 means a number
                        ds.series[ThisOne]['Flag'][i] = numpy.int32(Values[i])
                    else:
                        log.error('  xl_read_flags: flags for '+ThisOne+' not found in xl file')
    return ds

    def _check_xls_export(self):
        xls_export_url = reverse(
            'xls_export', kwargs={'username': self.user.username,
                                  'id_string': self.xform.id_string})
        response = self.client.get(xls_export_url)
        expected_xls = open_workbook(os.path.join(
            self.this_directory, "fixtures", "transportation",
            "transportation_export.xls"))
        content = self._get_response_content(response)
        actual_xls = open_workbook(file_contents=content)
        actual_sheet = actual_xls.sheet_by_index(0)
        expected_sheet = expected_xls.sheet_by_index(0)

        # check headers
        self.assertEqual(actual_sheet.row_values(0),
                         expected_sheet.row_values(0))

        # check cell data
        self.assertEqual(actual_sheet.ncols, expected_sheet.ncols)
        self.assertEqual(actual_sheet.nrows, expected_sheet.nrows)
        for i in range(1, actual_sheet.nrows):
            actual_row = actual_sheet.row_values(i)
            expected_row = expected_sheet.row_values(i)

            # remove _id from result set, varies depending on the database
            del actual_row[22]
            del expected_row[22]
            self.assertEqual(actual_row, expected_row)

    def __init__(self, path_or_buf):
        self.use_xlsx = True
        self.path_or_buf = path_or_buf
        self.tmpfile = None

        if isinstance(path_or_buf, basestring):
            if path_or_buf.endswith('.xls'):
                self.use_xlsx = False
                import xlrd
                self.book = xlrd.open_workbook(path_or_buf)
            else:
                try:
                    from openpyxl.reader.excel import load_workbook
                    self.book = load_workbook(path_or_buf, use_iterators=True)
                except ImportError:  # pragma: no cover
                    raise ImportError(_openpyxl_msg)
        else:
            data = path_or_buf.read()

            try:
                import xlrd
                self.book = xlrd.open_workbook(file_contents=data)
                self.use_xlsx = False
            except Exception:
                from openpyxl.reader.excel import load_workbook
                buf = py3compat.BytesIO(data)
                self.book = load_workbook(buf, use_iterators=True)

def readTop50Schools():
    """
    read university name from Top50 file'
    """
    data1 = xlrd.open_workbook('last200Info.xlsx')
    table1 = data1.sheet_by_name('data')
    feature_name = table1.row_values(0)

    data2 = xlrd.open_workbook('candidates.xlsx')
    table2 = data2.sheet_by_name('data')
    headers = table2.row_values(0)

    feature_index=[]
    for feature in feature_name:
        if feature in headers:
            feature_index.append(headers.index(feature))
    feature_data = []
    print feature_index
    for i in range(0,2937):
        d=[]
        for j in feature_index:
            d.append(table2.row_values(i)[j])
        feature_data.append(d)
    print len(feature_data),len(feature_data[0])    
    data = xlwt.Workbook()
    sheet1 = data.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet        
    for i in range(0,len(feature_data)):
        for j in range(0,len(feature_data[0])):
            sheet1.write(i,j,feature_data[i][j])
    data.save('candidatesFeatures.xlsx') #save into a file 
    data.save('candidatesFeatures.csv') #save into a file 

def is_excel(filename):
    try:
        xlrd.open_workbook(filename)
    except:
        return None
    else:
        return True

 def __init__(self, url, localfile=True, hash_comments=True):
     self.book = None
     self.hash_comments = hash_comments
     
     if localfile:
         try:
             self.book = xlrd.open_workbook(on_demand=True,
                                            filename=url)
         except:
             print("Error on %s" % url)
     else:
         try:
             conn = urlopen(url)
             
         except URLError as strerr:
             print("\nURL Error reading url: %s\n %s" % (url, strerr))
         
         except: 
             print("\nGeneral Error reading url: %s\n" % url)
             
         else:
             try:
                 self.book = xlrd.open_workbook(on_demand=True,
                                                file_contents=conn.read())
             except:
                 print("Error on %s" % url)
         
         finally:
             conn.close()
         
     if self.book:
         self.datemode = self.book.datemode

def load_spreadsheet(source):
    """Attempt to open the specified file using xlrd.  

    'source' should either be an absolute filename, or an open
    file object (e.g., the result of urllib.urlopen)
    
    Catches and suppresses common exceptions, but outputs a warning.
    """
    # TODO: use real python warnings
    try:
        if hasattr(source,'read'):
            workbook = open_workbook(file_contents=source.read(), 
                                     formatting_info=True,
                                     logfile=sys.stderr)
        else:
            workbook = open_workbook(source, 
                                     formatting_info=True,
                                     logfile=sys.stderr)
    except XLRDError, e:
        if 'Expected BOF' in str(e):
            logging.error("Error reading file (file extension may be wrong):")
            logging.error(e)
        elif 'Workbook is encrypted' in str(e):
            logging.error("Encrypted workbook:")
            logging.error(e)
        elif "Can't find workbook in OLE2" in str(e):
            logging.error("Weird OLE2 doc format:")
            logging.error(e)
        else:
            raise
        return

 def __init__(self, spreadsheet, input_encoding='utf-8', sheet=1, control_row=None, force_dates=False, object_type='parent'):
     '''Open file and get data from correct sheet.
     
     First, try opening the file as an excel spreadsheet.
     If that fails, try opening it as a CSV file.
     Exit with error if CSV doesn't work.
     '''
     self.obj_type = object_type
     self._force_dates = force_dates
     self._input_encoding = input_encoding
     self._user_ctrl_row_number = control_row
     try:
         try:
             self.book = xlrd.open_workbook(spreadsheet)
         except TypeError:
             self.book = xlrd.open_workbook(file_contents=spreadsheet.read())
         self.dataset = self.book.sheet_by_index(int(sheet)-1)
         self.data_type = 'xlrd'
     except xlrd.XLRDError as xerr:
         #if it's not excel, try csv
         try:
             with open(spreadsheet, 'rt', encoding=self._input_encoding) as csv_file:
                 self._process_csv_file(csv_file)
         except TypeError:
             #got a file object, which might have been opened in binary format
             spreadsheet.seek(0)
             spreadsheet_bytes = spreadsheet.read()
             spreadsheet_text = spreadsheet_bytes.decode(self._input_encoding)
             spreadsheet_file = io.StringIO(spreadsheet_text)
             self._process_csv_file(spreadsheet_file)
         except RuntimeError:
             raise RuntimeError('Could not recognize file format - must be .xls, .xlsx, or .csv.')

def assemble_samples(layout_path, quant_path):
    A1 = np.array((3, 2))
    layout = xlrd.open_workbook(layout_path).sheet_by_name('Sheet1')
    quant = xlrd.open_workbook(quant_path).sheet_by_name('0')
    # make sure we're actually at A1
    if layout.cell_value(*(A1 + (0,-1))) != 'A' or layout.cell_value(*(A1 + (-1,0))) != 1:
        raise ValueError("A1 seems to be in the wrong place or the input files are swapped.")
    rows = 'ABCDEFGH'
    cols = np.arange(12)+1
    sample_index = {}
    for (i, row) in enumerate(rows):
        for (j, col) in enumerate(cols):
            value = layout.cell_value(*(A1+(i,j)))
            if value: sample_index['%s%02d' % (row, col)] = str(value)

    start_row = 1
    name_col = 1
    cq_col = 6
    cq_index = {}
    for row in range(96):
        name = quant.cell_value(start_row+row, name_col)
        value = quant.cell_value(start_row+row, cq_col) or 'nan'
        cq_index[name] = float(value)

    print 'Well\tSample\tCq\tTarget'
    wells = sorted(sample_index.keys())
    for well in wells:
        print '%s\t%s\t%f\t' % (well, sample_index[well], cq_index[well])

def upload_table(file_input, filename):

    if filename == '':
        with open("templates/index_error.template", 'r') as index_error:
            index_err_message = Template(index_error.read())
            return index_err_message.substitute(error_message="Please enter a file!")

    if filename[len(filename)-4:] != ".xls" and filename[len(filename)-5:] != ".xlsx":
        with open("templates/index_error.template", 'r') as index_error:
            index_err_message = Template(index_error.read())
            return index_err_message.substitute(error_message="Wrong file extension!")


    if filename[len(filename)-4:] == ".xls":
        workbook = xlrd.open_workbook(file_contents=file_input.read(), encoding_override='cp1252', formatting_info=True)
    else:
        workbook = xlrd.open_workbook(file_contents=file_input.read(), encoding_override='cp1252')

    list_of_sheets_name = workbook.sheet_names()

    id_tabs_list = []
    for sheet_name in list_of_sheets_name:
        sheet_name = unicodedata.normalize('NFKD', sheet_name).encode('ASCII', 'ignore')
        sheet_name = sheet_name.lower()
        sheet_name = sheet_name.replace(' ', '-')
        id_tabs_list.append(sheet_name)

    with open("templates/index.template", 'r') as index_file:
        index = Template(index_file.read())
        with open("templates/table.template", 'r') as table_file:
            table = Template(table_file.read())
            with open("templates/table_rows.template", 'r') as table_rows_file:
                table_rows = Template(table_rows_file.read())
                with open("templates/table_row_col.template", 'r') as table_row_col:
                    table_row_col = Template(table_row_col.read())
                    with open("templates/table_row_col_head.template", 'r') as table_row_col_head:
                        table_row_col_head = Template(table_row_col_head.read())
                        with open("templates/tabs.template", 'r') as tabs:
                            tabs = Template(tabs.read())
                            num_sheet = 0
                            render_table = ""
                            render_tab = ""
                            active = " in active"
                            for sheet in workbook.sheets():
                                if num_sheet != 0:
                                    render_tab += tabs.substitute(tab=list_of_sheets_name[num_sheet],
                                                                  tab_id=id_tabs_list[num_sheet]) + "\t\t\t\t"
                                    active = ""
                                render_table_rows, render_table_head = process_sheet(sheet, table_rows,
                                                                                     table_row_col, table_row_col_head)
                                render_table += table.substitute(tab_id=id_tabs_list[num_sheet],
                                                                 active=active,
                                                                 table_head=render_table_head,
                                                                 table_rows=render_table_rows) + "\t\t\t\t"
                                num_sheet += 1
            #print render_table
            return index.substitute(tab_id=id_tabs_list[0],
                                    first_tab=list_of_sheets_name[0],
                                    tab=render_tab,
                                    table=render_table)

def define_duration():
    """
    get investment duration of each institution to be invested,
    and write it to file.
    """
    data = xlrd.open_workbook('ROI.xlsx')
    table = data.sheet_by_name('data')
    unitId = table.col_values(0)[1:242]
    data_4 = xlrd.open_workbook('student_4_class.xlsx')
    table_4 = data_4.sheet_by_name('sheet1')
    id_4 = table.col_values(0)
    data_L4 = xlrd.open_workbook('student_L4_class.xlsx')
    table_L4 = data_4.sheet_by_name('sheet1')
    id_L4 = table.col_values(0)
    flag = []
    for id in unitId:
        if id in id_4:
            flag.append(1)
        else:
            flag.append(0)
    data = xlwt.Workbook()
    sheet1 = data.add_sheet(u'sheet1',cell_overwrite_ok=True) 
    for i in range(0,len(flag)):
        sheet1.write(i,0,flag[i])
    data.save('duration.xlsx') #save into a file 
    data.save('duration.csv') #save into a file

def calculateWeightOfStudent(unitId):
    """
    calculate the weight of student catagory,note we classify 
    the student by 4 and primary,respectively.
    @params:
        unitId: unitId of every institution
    @return:
        prob: weight of student field of each institution
    """
    database_4 = xlrd.open_workbook('student_L4_class.xlsx')
    table_4 = database_4.sheet_by_name('sheet1')
    id_4 = table_4.col_values(0)
    class_4 = table_4.col_values(1)

    database_L4 = xlrd.open_workbook('student_4_class.xlsx')
    table_L4 = database_L4.sheet_by_name('sheet1')
    id_L4 = table_L4.col_values(0)
    class_L4 = table_L4.col_values(1)

    prob = []
    weight = [0.167,0.167,0.167,0.167,0.167,0.167]
    for u in unitId:
        ind1 = unitId.index(u)
        if u in id_4:
            ind2 = id_4.index(u)
            prob.append(weight[int(class_4[ind2])])
        elif u in id_L4:
            ind2 = id_L4.index(u)
            prob.append(weight[int(class_L4[ind2])])
        else:
            prob.append(0)
    return prob

def calculateWeightOfCost(unitId):
    """
    calculate the weight of cost catagory,note we classify 
    the cost by public and primary,respectively.
    @params:
        unitId: unitId of every institution
    @return:
        prob: weight of cost field of each institution
    """
    database_pub = xlrd.open_workbook('cost_pub_class.xlsx')
    table_pub = database_pub.sheet_by_name('sheet1')
    id_pub = table_pub.col_values(0)
    class_pub = table_pub.col_values(1)

    database_pri = xlrd.open_workbook('cost_pri_class.xlsx')
    table_pri = database_pri.sheet_by_name('sheet1')
    id_pri = table_pri.col_values(0)
    class_pri = table_pri.col_values(1)
    
    prob = []
    weight = [0.2,0.8]
    weight_class = [0.5,0.5,0.1,0.3,0.5]
    for u in unitId:
        ind1 = unitId.index(u)
        if u in id_pub:
            ind2 = id_pub.index(u)
            prob.append(weight[0] * weight_class[int(class_pub[ind2])])
        elif u in id_pri:
            ind2 = id_pri.index(u)
            prob.append(weight[1] * weight_class[int(class_pri[ind2])])
        else:
            prob.append(0)
    return prob

    def handle(self, *args, **options):
        import glob
        import xlrd

        wb = xlrd.open_workbook('./docs/result/100.xls')
        sh = wb.sheet_by_index(0)
        for r in range(5,sh.nrows-2):
            FundGet.objects.filter(index=sh.cell(r,1).value).update(result=sh.cell(r,9).value)
            print(sh.cell(r,1).value)
            print(sh.cell(r,9).value)
            print(r)

        wb = xlrd.open_workbook('./docs/result/101.xls')
        sh = wb.sheet_by_index(0)
        for r in range(5,sh.nrows-2):
            try:
                a = FundGet.objects.get(index=sh.cell(r,1).value)
                a.result = sh.cell(r,10).value
                a.save()
                print(r)
            except Exception as e:
                print(e)



        wb = xlrd.open_workbook('./docs/result/102.xlsx')
        sh = wb.sheet_by_index(0)
        for r in range(5,sh.nrows-2):
            try:
                a = FundGet.objects.get(index=sh.cell(r,1).value)
                a.result = sh.cell(r,10).value
                a.save()
                print(r)
            except Exception as e:
                print(e)

def translations_to_questionnaire(filename, prefix, suffix):
    first_part, second_part = os.path.split(filename)
    if not second_part.startswith(prefix):
        m = '"{}" does not start with supplied prefix "{}"'
        m = m.format(second_part, prefix)
        raise QlangError(m)
    orig_filename = os.path.join(first_part,second_part[len(prefix):])
    full_file, ext = os.path.splitext(orig_filename)
    dest_filename = full_file + suffix + ext
    with xlrd.open_workbook(filename) as book:
        with xlrd.open_workbook(orig_filename) as orig:
            trans_ws = book.sheet_by_name(QLANG_WORKSHEET_NAME)
            # Copy over "survey" and "choices" after merging translations
            survey_ws = orig.sheet_by_name(SURVEY)
            new_survey = get_worksheet_w_trans(survey_ws, trans_ws)
            choices_ws = orig.sheet_by_name(CHOICES)
            new_choices = get_worksheet_w_trans(choices_ws, trans_ws)
            wb = xlsxwriter.Workbook(dest_filename)
            survey_out_ws = wb.add_worksheet(SURVEY)
            write_out_worksheet(survey_out_ws, new_survey)
            choices_out_ws = wb.add_worksheet(CHOICES)
            write_out_worksheet(choices_out_ws, new_choices)
            # Copy all other sheets over
            for sheet in orig.sheet_names():
                if sheet not in (SURVEY, CHOICES):
                    rows = get_unicode_ws(orig.sheet_by_name(sheet))
                    this_ws = wb.add_worksheet(sheet)
                    write_out_worksheet(this_ws, rows)
            wb.close()
    m = 'Translations successfully merged: "{}"'.format(dest_filename)
    print(m)

def files():
    '''打开指定文件，读取内容并返回指定内容'''
    fileName = 'NQM.xls'
    fileName2 = 'NQM.xlsx'
    if os.path.exists(fileName):
        try:
            cotent = xlrd.open_workbook(fileName)
            tables = cotent.sheet_by_index(0)
            return  tables
        except:
            print '请检查excel文档格式，文档不能有附件及链接等'
            time.sleep(3)
            sys.exit()
    elif os.path.exists(fileName2):
        try:
            cotent = xlrd.open_workbook(fileName2)
            tables = cotent.sheet_by_index(0)
            return  tables
        except:
            print '请检查excel文档格式，文档不能有附件及链接等'
            time.sleep(3)
            sys.exit()
    else:
        print '请将“NQM.XLS(x)”文件放在当前目录下或者将其更名为NQM.xls或者NQM.xlsx，然后执行！'
        time.sleep(3)
        sys.exit()

def excel_setup() -> None:
    """opens the necessary files/worksheets from excel documents"""
    global awl, aoa, tasa, subtlex, zeno
    # I tried to make a for loop for this but it never worked...
    try:
        awl = xlrd.open_workbook("AWL.xls")
        awl = awl.sheet_by_index(0)
        print("1/5")
    except:
        print("Failed to load file: AWL.xls")
    try:
        aoa = xlrd.open_workbook("AoA.xlsx")
        aoa = aoa.sheet_by_index(0)
        print("2/5")
    except:
        print("Failed to load file: AoA.xlsx")
    try:
        tasa = xlrd.open_workbook("tasa.xlsx")
        tasa = tasa.sheet_by_index(0)
        print("3/5")
    except:
        print("Failed to load file: tasa.xlsx")
    try:
        subtlex = xlrd.open_workbook("SUBTLEX.xlsx")
        subtlex = subtlex.sheet_by_index(0)
        print("4/5")
    except:
        print("Failed to load file: SUBTLEX.xlsx")

    try:
        zeno = xlrd.open_workbook("Zeno.xlsx")
        zeno = zeno.sheet_by_index(0)
    except:
        print("Failed to load file: Zeno.xlsx")
    return

    def getAndOperateData(self):
        browser=webdriver.Firefox()
        browser.get("http://www.xebest.com:8000")
        object_excel=xlrd.open_workbook(r"E:\data\objectname_locatemethod_locatedata.xls")
        object_sheet=object_excel.sheets()[0]
        object_sheet_rows=object_sheet.nrows
        object_sheet_cols=object_sheet.ncols
        object_name_list=[]#定义一个存放登录功能中需要定位的对象名称的空列表
        for i in range(object_sheet_rows):#拿到登录功能中需要定位的对象名称列表
            object_name_list.append(object_sheet.cell(i,0).value)
        object_name_list.pop(0)#去掉对象名excel中的第一行的标签项名称
        print object_name_list

        username_password_list=[]
        senddata_excel=xlrd.open_workbook(r"E:\data\username_password.xls")
        senddata_sheet=senddata_excel.sheets()[0]
        senddata_sheet_rows=senddata_sheet.nrows
        senddata_sheet_cols=senddata_sheet.ncols
        for i in range(1,senddata_sheet_rows):
            username_password_list.append(senddata_sheet.row_values(i))
        print username_password_list


        for username,password in username_password_list:
            for m in range(object_name_list.__len__()):
                self.locateObject(browser,username,password,object_name_list[m])
            browser.switch_to_alert().accept()

def get_state_crosswalks():
	"""
	Returns a dictionary matching IMPLAN industry identifiers to the sectors used in state and national forecasts.
	
	Only Oregon provides a suitable forecast now; others may be added if they become available.
	Outer keys identify the state or 'US' for national.
	For states, inner keys are IMPLAN industry identifiers (1 to 440).
	For national, second-level keys are 'output' and 'employment' and inner keys are IMPLAN industry identifiers (1 to 440).
	Values are the sector identifierss used in the individual forecasts.
	"""
	state_crosswalks = {'OR' : {}, 'WA' : {}, 'ID' : {}, 'NV' : {}, 'CA' : {}, 'US' : {'output' : {}, 'employment' : {}}}
	wb = open_workbook('IMPLAN_OEA.xls')
	sheet = wb.sheet_by_index(0)
	for i in range(1, 436):
		row = sheet.row_values(i, 0)
		state_crosswalks['OR'][row[0]] = row[2]
	wb = open_workbook('implan_gi_sectors.xls')
	sheet = wb.sheet_by_index(0)
	for i in range(1, 437):
		row = sheet.row_values(i, 0)
		state_crosswalks['US']['output'][row[0]] = row[1]
	sheet = wb.sheet_by_index(1)
	for i in range(1, 437):
		row = sheet.row_values(i, 0)
		state_crosswalks['US']['employment'][row[0]] = row[1]
	return state_crosswalks