Python pyexcel.get_sheet函数代码示例

    def has_sheet(self):
        if self.sheetname == '':
            return True

        try:
            pyexcel.get_sheet(file_name=self.filename, sheet_name=self.sheetname)
            return True
        except:
            return False

def convert():

    #Definir formato de localização dos dados
    locale.setlocale(locale.LC_ALL, 'en_US')

    #Planilha - GND
    # Importa dados da planilha de Grandes Grupos de Despesa
    raw_sheet_gnd = pyexcel.get_sheet(file_name="/home/romulofloresta/server_app/xls2py/gnd-raw.xls")

    #Remove linhas e colunas desnecessárias
    raw_sheet_gnd.delete_rows(list(range(4))) #deleta linhas acima
    for i in range(3):
        raw_sheet_gnd.delete_rows([-1]) #deleta linhas abaixo
    raw_sheet_gnd.delete_columns([0, 2, 4, 5, 7])

    #Converte para tipo de dados python
    raw_sheet_gnd.name_columns_by_row(0)
    py_records_gnd = raw_sheet_gnd.to_records()

    #formata os valores de moeda
    for record in py_records_gnd:
        record['Autorizado'] = locale.currency(record["Autorizado"], symbol=None)
        record['Pago'] = locale.currency(record["Pago"], symbol=None)

    # Funções
    # Importa dados da planilha de Funções
    raw_sheet_func = pyexcel.get_sheet(file_name="/home/romulofloresta/server_app/xls2py/funcoes-raw.xls")

    #Remove linhas e colunas desnecessárias
    raw_sheet_func.delete_rows(list(range(4))) #deleta linhas acima
    for i in range(4):
        raw_sheet_func.delete_rows([-1]) #deleta linhas abaixo
    raw_sheet_func.delete_columns([1, 3, 4, 6])

    #Alterar título da coluna
    raw_sheet_func[0,0] = 'Funcao'

    #Converte para tipo de dados python
    raw_sheet_func.name_columns_by_row(0)
    py_records_func = raw_sheet_func.to_records()

    # Formata os campos
    for record in py_records_func:
        record['Funcao'] = record['Funcao'][4:]
        record['Autorizado'] = locale.currency(record["Autorizado"], symbol=None)
        record['Pago'] = locale.currency(record["Pago"], symbol=None)

    #Pega a versão do banco de dados
    with open('server_app/version.json') as f:
        data_version = json.load(f)
        f.close()

    # Retorna json com os dados
    response = json.dumps({'Funcao': py_records_func, 'GND': py_records_gnd, 'version': data_version['version'], 'updated':data_version['date']})
    return response

 def test_get_sheet_from_sql(self):
     sheet = pe.get_sheet(session=Session(), table=Signature)
     assert sheet.to_array() == [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]

 def test_upload_and_download(self):
     for upload_file_type in FILE_TYPE_MIME_TABLE.keys():
         file_name = 'test.%s' % upload_file_type
         for download_file_type in FILE_TYPE_MIME_TABLE.keys():
             print("Uploading %s Downloading %s" % (upload_file_type,
                                                    download_file_type))
             sheet = pe.Sheet(self.data)
             io = sheet.save_to_memory(upload_file_type).getvalue()
             if not PY2:
                 if isinstance(io, bytes):
                     content = io
                 else:
                     content = io.encode('utf-8')
             else:
                 content = io
             response = self.app.post(
                 '/switch/%s' % download_file_type,
                 upload_files=[('file', file_name, content)],
             )
             eq_(response.content_type,
                 FILE_TYPE_MIME_TABLE[download_file_type])
             sheet = pe.get_sheet(file_type=download_file_type,
                                  file_content=response.body)
             sheet.format(int)
             array = sheet.to_array()
             assert array == self.data

def parse_bounds(file_path):
    if not (os.path.isfile(file_path)):
        print (file_path + " File not found!!!")
        return {"bounds" : []}
    sheet = pyexcel.get_sheet(file_name=file_path)
    bound_dict = {
        "record"  : sheet.column[1][0],
        "channel" : sheet.column[1][1],
        "neuron"  : sheet.column[1][2],
        "bounds"  : [],    
    }
    for idx in range(len(sheet.column[0][3:])):
        idx += 3
        
        tmp_bounds = sheet.column[1][idx].split(" ")
        if (len(tmp_bounds) == 1 and tmp_bounds[0] == ""):
            continue

        if (len(tmp_bounds) != 2):
            tmp_bounds = []
            tmp_bounds.append( sheet.column[1][idx] )
            tmp_bounds.append( sheet.column[2][idx] )
            
        #tmp_bounds[0] = min(tmp_bounds)
        #tmp_bounds[1] = max(tmp_bounds)
            
        tmp_dict = {
            'name' : sheet.column[0][idx],
            'lower_bound' : float(tmp_bounds[0]),
            'upper_bound' : float(tmp_bounds[1]),        
        }
        
        bound_dict["bounds"].append(tmp_dict)
    return (bound_dict)

def test_issue_30():
    test_file = "issue_30.ods"
    sheet = pe.Sheet()
    sheet[0, 0] = 999999999999999
    sheet.save_as(test_file)
    sheet2 = pe.get_sheet(file_name=test_file)
    eq_(sheet[0, 0], sheet2[0, 0])
    os.unlink(test_file)

def main(base_dir):
    # "example.csv","example.xlsx","example.ods", "example.xlsm"
    spreadsheet = pyexcel.get_sheet(file_name=os.path.join(base_dir,
                                                           "example.xls"))

    # rows() returns row based iterator, meaning it can be iterated row by row
    for row in spreadsheet.rows():
        print(row)

 def test_auto_detect_int_false(self):
     sheet = pe.get_sheet(file_name=self.test_file, auto_detect_int=False)
     expected = dedent("""
     test_auto_detect_init.csv:
     +-----+-----+-----+
     | 1.0 | 2.0 | 3.1 |
     +-----+-----+-----+""").strip()
     self.assertEqual(str(sheet), expected)

 def test_auto_detect_int(self):
     sheet = pe.get_sheet(file_name=self.test_file, library="pyexcel-xls")
     expected = dedent("""
     pyexcel_sheet1:
     +---+---+-----+
     | 1 | 2 | 3.1 |
     +---+---+-----+""").strip()
     eq_(str(sheet), expected)

 def open_existing(self, the_file):
     if not self.finished:
         sheet = get_sheet(file_name=the_file)
         for row in sheet.rows():
             self.row += row
         self.name_columns_by_row(0)
         self.name_rows_by_column(0)
         self._finished = True

 def test_get_sheet_from_file(self):
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     sheet.save_as(testfile)
     sheet = pe.get_sheet(file_name=testfile)
     assert sheet.to_array() == data
     os.unlink(testfile)

def addExcel(path, nowtime):
    sheet = pe.get_sheet(file_name = path)
    
    oneRow = [nowtime]
    for i in range(1, len(sys.argv)):
        oneRow.append(sys.argv[i])
    sheet.row += oneRow
    sheet.save_as(path)

 def test_auto_detect_float_false(self):
     expected = [[
         '2014-12-25',
         '2014-12-25 11:11:11',
         '2014-12-25 11:11:11.000010']]
     sheet = pe.get_sheet(file_name=self.excel_filename,
                          auto_detect_datetime=False)
     self.assertEqual(sheet.to_array(), expected)

def test_writing_multiline_ods():
    content = "2\n3\n4\n993939\na"
    testfile = "writemultiline.ods"
    array = [[content, "test"]]
    pyexcel.save_as(array=array, dest_file_name=testfile)
    sheet = pyexcel.get_sheet(file_name=testfile)
    assert sheet[0, 0] == content
    os.unlink(testfile)

 def test_auto_detect_int(self):
     sheet = pe.get_sheet(file_name=self.test_file)
     expected = dedent("""
     pyexcel_sheet1:
     +---+---+-----+
     | 1 | 2 | 3.1 |
     +---+---+-----+""").strip()
     self.assertEqual(str(sheet), expected)