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
Exemple #2
0
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)
Exemple #12
0
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)
Exemple #16
0
 def test_download(self):
     for file_type in FILE_TYPE_MIME_TABLE.keys():
         print(file_type)
         response = self.client.get("/polls/download/"+file_type)
         assert response['Content-Type'] == FILE_TYPE_MIME_TABLE[file_type]
         sheet = pe.get_sheet(file_type=file_type, file_content=response.content)
         sheet.format(int)
         array = sheet.to_array()
         assert array == self.data
Exemple #17
0
 def load_single_sheet(self, field_name=None, sheet_name=None, **keywords):
     file_type, file_handle = self.get_file_tuple(field_name)
     if file_type is not None and file_handle is not None:
         return pe.get_sheet(file_type=file_type,
                             file_content=file_handle.read(),
                             sheet_name=sheet_name,
                             **keywords)
     else:
         return None
def create_dictionary(sheet_list):
    '''
    creates allsheet_dict and ezsheet_dict
    '''

    for i in sheet_list:
        if i == './resources/All_output.xlsx':
            allsheet = pyexcel.get_sheet(file_name = sheet_list[0], name_columns_by_row=0)
            allsheet_od = allsheet.to_dict()#makes ordered dict
            allsheet_dict = dict(allsheet_od)#makes ordinary dict
            #print "ALL: ",allsheet_dict
        elif i == './resources/Easy_output.xlsx':
            ezsheet = pyexcel.get_sheet(file_name = sheet_list[1], name_columns_by_row=0)
            ezsheet_dict = dict(ezsheet.to_dict())
            #print "EZ: ",ezsheet_dict
        else:
            print "You don't have the appropriate sheet names"
    return (allsheet_dict, ezsheet_dict)
Exemple #19
0
 def test_ods_output_stringio(self):
     data = [[1, 2, 3], [4, 5, 6]]
     io = pyexcel.save_as(dest_file_type="ods", array=data)
     r = pyexcel.get_sheet(
         file_type="ods", file_content=io.getvalue(), library="pyexcel-ods3"
     )
     result = [1, 2, 3, 4, 5, 6]
     actual = list(r.enumerate())
     eq_(result, actual)
 def test_get_sheet_from_memory_compatibility(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     content = pe.save_as(dest_file_type="xls", array=data)
     sheet = pe.get_sheet(content=content.getvalue(), file_type="xls")
     assert sheet.to_array() == data
Exemple #21
0
def main(base_dir):
    sheet = pe.get_sheet(file_name=os.path.join(base_dir,
                                                "tutorial_datatype_01.xls"),
                         name_columns_by_row=0)
    print(sheet.to_dict())
    # {u'userid': [10120.0, 10121.0, 10122.0],
    #  u'name': [u'Adam', u'Bella', u'Cedar']}
    sheet.column.format(0, str)
    print(sheet.to_dict())
 def __init__(self, filename):
     self.sheet = pyexcel.get_sheet(file_name=filename)
     self.filename = filename
     self.get_index_start_var_def()
     self.get_index_end_gatts()
     self.get_index_start_data()
     self.get_index_end_var_def()
     self.get_index_end_data()
     self.max_data_column()
 def test_get_sheet_from_file_stream(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     content = pe.save_as(dest_file_type="xls", array=data)
     sheet = pe.get_sheet(file_stream=content, file_type="xls")
     assert sheet.to_array() == data
 def test_load_sheet_from_django_model(self):
     model=FakeDjangoModel()
     sheet = pe.Sheet(self.data, name_columns_by_row=0)
     sheet.save_to_django_model(model)
     assert model.objects.objs == self.result
     model._meta.update(["X", "Y", "Z"])
     sheet2 = pe.get_sheet(model=model)
     sheet2.name_columns_by_row(0)
     assert sheet2.to_records() == sheet.to_records()
 def test_get_sheet_from_array(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     sheet = pe.get_sheet(array=data)
     result = sheet.to_array()
     assert data == result
 def test_auto_detect_float_false(self):
     sheet = pe.get_sheet(file_name=self.test_file, auto_detect_float=False)
     self.assertEqual(sheet.to_array(), [[1, '2.0', '3.1']])
     expected = dedent("""
     test_auto_detect_init.csv:
     +---+-----+-----+
     | 1 | 2.0 | 3.1 |
     +---+-----+-----+""").strip()
     self.assertEqual(str(sheet), expected)
def main(date_start=datetime.datetime.today(), date_end=None):
    running_date = date_start
    # answered = defaultdict(list)
    # answered = namedtuple('answered', 'lessthan5 morethan5 morethan10')
    # answered(lessthan5=[], morethan5=[], morethan10=[])
    # # answered.__new__.__defaults__ = ([],) * len(answered._fields)
    # lost = namedtuple('lost', 'lessthan5 morethan5 morethan10')
    # lost(lessthan5=[], morethan5=[], morethan10=[])
    # # lost.__new__.__defaults__ = ([],) * len(lost._fields)
    # print(answered)
    # print(lost)
    answered = Counter()
    lost = Counter()
    while date_end >= running_date:
        month_date = running_date.strftime('%m%d%Y')
        # file = r'C:\Users\mscales\Desktop\Development\Attachment Archive\{0}\Call Details.xlsx'.format(month_date)
        file = r'C:\Users\mscales\Desktop\Development\Daily SLA Parser - Automated Version\Archive\{0}\{0}_Call Details.xlsx'.format(month_date)
        # print(file)
        try:
            sheet = pe.get_sheet(file_name=file)
            data = sheet.to_array()
            # print(data)
            for row in data:
                if row[6] == 7592:
                    call_duration = get_sec(row[12])
                    date = row[3]
                    if row[11] is True:
                        print("found true call")
                        if call_duration <= 30:
                            answered.morethan10.append(convert_time_stamp(call_duration) + '.' + date)
                        # elif call_duration >= 1800:
                        #     answered.morethan5.append(convert_time_stamp(call_duration) + '.' + date)
                        # else:
                        #     answered.lessthan5.append(convert_time_stamp(call_duration) + '.' + date)
                    # else:
                    #     if call_duration >= 600:
                    #         lost.morethan10.append(convert_time_stamp(call_duration) + '.' + date)
                    #     elif call_duration >= 300:
                    #         lost.morethan5.append(convert_time_stamp(call_duration) + '.' + date)
                    #     else:
                    #         lost.lessthan5.append(convert_time_stamp(call_duration) + '.' + date)
                else:
                    pass
        except Exception as e:
            print(e)
            pass
        running_date = running_date + timedelta(days=1)
    # print("Answered: less than 5 min: {}".format(len(answered.lessthan5)))
    # for duration in answered.lessthan5:
    #     print(duration)
    # print("Answered: more than 5 min: {}".format(len(answered.morethan5)))
    # for duration in answered.morethan5:
    #     print(duration)
    print("Answered: more than 60 min: {}".format(len(answered.morethan10)))
    for duration in answered.morethan10:
        print(duration.split('.'))
 def test_save_a_dict3(self):
     adict = {
         "X": [1, 4],
         "Y": [2, 5],
         "Z": [3, 6]
     }
     sheet = pe.get_sheet(adict=adict, name_columns_by_row=0)
     sheet.save_to_database(self.session, Signature)
     result = pe.get_dict(session=self.session, table=(Signature))
     assert adict == result
 def test_get_sheet_from_query_sets(self):
     session = Session()
     objects = session.query(Signature).all()
     column_names = ["X", "Y", "Z"]
     sheet = pe.get_sheet(column_names=column_names, query_sets=objects)
     assert sheet.to_array() == [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
Exemple #30
0
def do_write_stringio(file_type):
    data = [
        [1, 2, 3],
        [4, 5, 6]
    ]
    io = pe.save_as(dest_file_type=file_type, array=data)
    r = pe.get_sheet(file_type=file_type, file_content=io.getvalue())
    result=[1, 2, 3, 4, 5, 6]
    actual = pe.utils.to_array(r.enumerate())
    assert actual == result