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
Beispiel #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_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 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 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_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 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_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 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)
Beispiel #11
0
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)
Beispiel #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)
Beispiel #13
0
 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_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)
Beispiel #16
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)
Beispiel #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 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()
Beispiel #19
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
 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_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
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)
Beispiel #23
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 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 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 __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 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('.'))
Beispiel #28
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
 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]
     ]
 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
Beispiel #31
0
def make_response_from_a_table(
    model, file_type, status=200, file_name=None, **keywords
):
    """
    Produce a single sheet Excel book of *file_type*

    :param model: a Django model
    :param file_type: same as :meth:`~django_excel.make_response`
    :param status: same as :meth:`~django_excel.make_response`
    """
    sheet = pe.get_sheet(model=model, **keywords)
    return make_response(
        sheet, file_type, status, file_name=file_name, **keywords
    )
Beispiel #32
0
 def test_array(self):
     for struct_type in ["array", "dict", "records"]:
         print("Testing %s" % struct_type)
         io = pe.save_as(dest_file_type="xls", array=self.data)
         io.seek(0)
         response = self.app.post('/exchange/%s' % struct_type,
                                  buffered=True,
                                  data={"file": (io, "test.xls")},
                                  content_type="multipart/form-data")
         assert response.content_type == "application/vnd.ms-excel"
         sheet = pe.get_sheet(file_type='xls',
                              file_content=response.data)
         assert sheet.to_array() == self.data
         eq_(sheet.name, 'test_array')
Beispiel #33
0
    def __init__(self, archivo):

        ## Obtiene los registros en el archivo de carga masiva proporcionado por el usuario en un arreglo, en caso de un archivo csv la longitud de cada arreglo es de 1 elemento
        datos = pyexcel.get_sheet(file_name=archivo)

        for dat in datos:
            if dat.__len__() == 0:
                # Es un archivo csv
            else:
                # Esta en otro formato ods, xls, xlsx, etc

    	pass

	#def validar_string()
Beispiel #34
0
def test_issue_92_incomplete_records():
    records = [{"a": 1, "b": 2, "c": 3}, {"b": 2}, {"c": 3}]
    sheet = pe.get_sheet(records=records)
    content = dedent("""
    +---+---+---+
    | a | b | c |
    +---+---+---+
    | 1 | 2 | 3 |
    +---+---+---+
    |   | 2 |   |
    +---+---+---+
    |   |   | 3 |
    +---+---+---+""").strip("\n")
    eq_(str(sheet.content), content)
Beispiel #35
0
 def test_url_source_via_file_suffix_get_sheet(self, mock_open):
     m = MagicMock()
     x = MagicMock()
     x.type.return_value = "text"
     m.info.return_value = x
     m.read.return_value = "1,2,3"
     mock_open.return_value = m
     sheet = pe.get_sheet(url="xx.csv")
     content = dedent("""
     csv:
     +---+---+---+
     | 1 | 2 | 3 |
     +---+---+---+""").strip('\n')
     self.assertEqual(str(sheet), content)
 def test_auto_detect_int_false(self):
     sheet = pe.get_sheet(
         file_name=self.test_file,
         auto_detect_int=False,
         library="pyexcel-ods",
     )
     expected = dedent(
         """
     pyexcel_sheet1:
     +-----+-----+-----+
     | 1.0 | 2.0 | 3.1 |
     +-----+-----+-----+"""
     ).strip()
     eq_(str(sheet), expected)
    def test_greek_db(self):

        # old_row_names = greek_example_sheet.row[1]

        process_greek_db(self.fields_correspondences)
        greek_db_to_test = pe.get_sheet(file_name="data/processed-greek-db.xlsx")
        new_row_names = greek_db_to_test.row[0]

        # Check first and last name fields
        self.assertEqual(new_row_names[0], "food-name", "The content of the fields doesn't match")
        self.assertEqual(new_row_names[-1], "copper-mg", "The content of the fields doesn't match")


        pass
Beispiel #38
0
 def test_download_attachment(self):
     test_file_name = "test"
     for file_type in FILE_TYPE_MIME_TABLE.keys():
         print(file_type)
         response = self.client.get("/polls/download_attachment/" +
                                    file_type + "/" + test_file_name)
         assert response['Content-Type'] == FILE_TYPE_MIME_TABLE[file_type]
         assert response['Content-Disposition'] == (
             "attachment; filename=%s.%s" % (test_file_name, 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
Beispiel #39
0
def test_issue_92_non_uniform_records():
    records = [{"a": 1}, {"b": 2}, {"c": 3}]
    sheet = pe.get_sheet(records=records, custom_headers=['a', 'b', 'c'])
    content = dedent("""
    +---+---+---+
    | a | b | c |
    +---+---+---+
    | 1 |   |   |
    +---+---+---+
    |   | 2 |   |
    +---+---+---+
    |   |   | 3 |
    +---+---+---+""").strip("\n")
    eq_(str(sheet.content), content)
def xsl_to_json(url):
    sheet = pe.get_sheet(url=url)
    sheet_array = sheet.array
    new_data = []
    col_name = sheet_array[0]
    i = 0
    for c in sheet_array:
        if i != 0:
            data = {}
            for i in range(len(col_name)):
                data[col_name[i]] = c[i]
            new_data.append(data)
        i = i + 1
    return new_data
Beispiel #41
0
def do_read_stringio(file_name):
    create_sample_file1(file_name)
    file_type = file_name.split('.')[-1]
    open_flag = 'rb'
    if file_type in ['csv', 'tsv']:
        open_flag = 'r'
    with open(file_name, open_flag) as f:
        content = f.read()
        r = pe.get_sheet(file_type=file_type, file_content=content)
        result = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 1.1, 1]
        actual = pe.utils.to_array(r.enumerate())
        assert result == actual
    if os.path.exists(file_name):
        os.unlink(file_name)
Beispiel #42
0
def do_read_stringio(file_name):
    create_sample_file1(file_name)
    file_type = file_name.split(".")[-1]
    open_flag = "rb"
    if file_type in ["csv", "tsv"]:
        open_flag = "r"
    with open(file_name, open_flag) as f:
        content = f.read()
        r = pe.get_sheet(file_type=file_type, file_content=content)
        result = ["a", "b", "c", "d", "e", "f", "g", "h", "i", "j", 1.1, 1]
        actual = list(r.enumerate())
        eq_(result, actual)
    if os.path.exists(file_name):
        os.unlink(file_name)
Beispiel #43
0
 def test_issue_29(self):
     a=[
         ['2016-03-31 10:59', '0123', 'XS360_EU', '04566651561653122'], # error case
         [datetime(2016, 4, 15, 17, 52, 11), 123, False, 456193284757]  #  python types
     ]
     s=pe.get_sheet(array=a)
     content = dedent("""
     pyexcel_sheet1:
     +------------------+------+----------+-------------------+
     | 2016-03-31 10:59 | 0123 | XS360_EU | 04566651561653122 |
     +------------------+------+----------+-------------------+
     | 15/04/16         | 123  | false    | 456193284757      |
     +------------------+------+----------+-------------------+""").strip('\n')
     self.assertEqual(str(s), content)
 def test_merge_two_csv_files_2(self):
     file1 = os.path.join("tests", "fixtures",
                          "test_cook_book_merge_numbers_2.csv")
     file2 = os.path.join("tests", "fixtures",
                          "test_cook_book_merge_alphabets_2.csv")
     file_list = [file1, file2]
     pe.cookbook.merge_files(file_list)
     r = pe.get_sheet(file_name="pyexcel_merged.csv")
     actual = [
         [1, 2, 3, "", "", "", "", "a", "b", "c", "", ""],
         [4, 5, 6, 10, 11, 12, "", "d", "e", "f", "m", "q"],
         [7, 8, 9, "", "", "", 9, "g", "h", "I", "n", "p"],
     ]
     eq_(actual, r.array)
Beispiel #45
0
 def test_writing_date_format(self):
     excel_filename = "testdateformat.xls"
     data = [[datetime.date(2014, 12, 25),
             datetime.time(11, 11, 11),
             datetime.datetime(2014, 12, 25, 11, 11, 11)]]
     pe.save_as(dest_file_name=excel_filename, array=data)
     r = pe.get_sheet(file_name=excel_filename)
     assert isinstance(r[0, 0], datetime.date) is True
     assert r[0, 0].strftime("%d/%m/%y") == "25/12/14"
     assert isinstance(r[0, 1], datetime.time) is True
     assert r[0, 1].strftime("%H:%M:%S") == "11:11:11"
     assert isinstance(r[0, 2], datetime.date) is True
     assert r[0, 2].strftime("%d/%m/%y %H:%M:%S") == "25/12/14 11:11:11"
     os.unlink(excel_filename)
Beispiel #46
0
 def test_url_source_via_file_suffix_get_sheet(self, mock_open):
     m = MagicMock()
     x = MagicMock()
     x.type.return_value = "text"
     m.info.return_value = x
     m.read.return_value = "1,2,3"
     mock_open.return_value = m
     book = pe.get_sheet(url="xx.csv")
     content = dedent("""
     Sheet Name: csv
     +---+---+---+
     | 1 | 2 | 3 |
     +---+---+---+""").strip('\n')
     assert str(book) == content
Beispiel #47
0
    def generate_hash_tables(self,
                             file_path: str,
                             excel_filepath: str,
                             sheet_name: str = None,
                             header: bool = True) -> None:
        """
		This function processes the wikified output file uploaded by the user to build self.other dictionary
		:param file_path:
		:param excel_filepath:
		:param sheet_name:
		:param header:
		:return:
		"""
        cell_to_qnode = dict()
        value_to_qnode = dict()
        with open(file_path, encoding='utf-8') as file:
            csv_reader = csv.reader(file, delimiter=',')
            for row in csv_reader:
                if header:
                    header = False
                    continue
                if not check_if_string_is_invalid(
                        row[0]) and not check_if_string_is_invalid(row[1]):
                    cell_to_qnode[(int(row[0]), int(row[1]))] = row[3]
                if row[2] is not None:
                    value_to_qnode[str(row[2]).strip()] = row[3]

        sheet = pyexcel.get_sheet(sheet_name=sheet_name,
                                  file_name=excel_filepath)
        for cell, qnode in cell_to_qnode.items():
            try:
                cell_value = str(sheet[cell[1], cell[0]]).strip()
                if not check_if_string_is_invalid(
                        cell_value) and cell_value not in value_to_qnode:
                    value_to_qnode[cell_value] = qnode
            except IndexError:
                pass

        for row in range(len(sheet)):
            for col in range(len(sheet[0])):
                try:
                    cell_value = str(sheet[row, col]).strip()
                    if value_to_qnode.get(cell_value, None):
                        cell_to_qnode[(col, row)] = value_to_qnode[cell_value]
                except IndexError:
                    pass
        cell_to_qnode = self.serialize_cell_to_qnode(cell_to_qnode)
        self.other["qnodes"] = cell_to_qnode
        self.other["region"] = list(cell_to_qnode.keys())
Beispiel #48
0
def upload():
    if request.method == 'POST':
        # Check if the post request has the file part
        if 'file' not in request.files:
            return 'No file received.'
        input_file = request.files['file']

        # If the user doesn't select a file, the browser also
        # submit an empty part without a filename.
        if input_file.filename == '':
            return 'No file selected.'

        if not input_file.filename.lower().endswith('.xls'):
            return 'File must be XLS format.'

        try:
            sheet = pyexcel.get_sheet(file_content=input_file.read(),
                                      file_type='xls',
                                      name_columns_by_row=1)
            del sheet.row[filter_row]  # Delete empty rows
            records = sheet.to_records()
        except XLRDError as e:
            return 'File is corrupt.'

        cal = vobject.iCalendar()
        for record in records:
            event = build_event(record)
            vevent = cal.add('vevent')
            vevent.add('uid').value = str(uuid.uuid4()).upper()
            vevent.add('summary').value = event['title']
            vevent.add('description').value = event['description']
            vevent.add('location').value = event['location']
            vevent.add('dtstart').value = event['event_start']
            vevent.add('dtend').value = event['event_end']

            ruleset = rruleset()
            ruleset.rrule(
                rrule(WEEKLY,
                      byweekday=event['day_index'],
                      until=event['until']))

            for exdate in event['excludes']:
                ruleset.exdate(exdate)

            vevent.rruleset = ruleset

        return cal.serialize()

    return ''
Beispiel #49
0
def read_create():
    wb = openpyxl.Workbook()
    wb.save("src/tmp/baskets/" + "left_over" + ".xlsx")

    file_name = "src/data/Courselist.ods"

    sheet = pyexcel.get_sheet(file_name=file_name)
    name = []
    i = 0
    for row in sheet:
        lists = str(row[3])
        lists = lists.split('-')
        if (lists[0] != "0"):
            check = 0
            i += 1
            if (i == 2):
                continue
            if (i > 186):
                break
            # print(row)
            # print("sdjhfjdsgf hjdshf hds              ddvfdvhghjd d f                        ",i)

            for file in os.listdir(file_path):
                if file.endswith(
                        '.xlsx'
                ) and file != ".xlsx" and file != "course_faculty_main.xlsx" and file != 'course_faculty_optional.xlsx':

                    a = str(os.path.join(file_path, file))
                    if (check == 1 or row[1] != ""):
                        break
                    wb = openpyxl.load_workbook(a)
                    ws = wb.worksheets[0]
                    j = 0
                    for j in range(2, ws.max_row + 1):
                        # print(row[1],ws.cell(j,1).value)
                        if (ws.cell(j, 1).value == row[1]):
                            check = 1
                            break
                    wb.save(a)
            if (check == 0):
                wb = openpyxl.load_workbook("src/tmp/baskets/" + "left_over" +
                                            ".xlsx")
                ws = wb.worksheets[0]
                rows = ws.max_row + 1
                # print(rows)
                for l in range(1, 6):
                    ws.cell(rows, l).value = row[l]

                    wb.save("src/tmp/baskets/" + "left_over" + ".xlsx")
Beispiel #50
0
    def load_sheet(self):
        """
        Return a pyexcel sheet read from the uploaded file.
        """
        f = self.files['spreadsheet']

        # Remove BOM
        if f.read(len(codecs.BOM_UTF8)).startswith(codecs.BOM_UTF8):
            f = io.TextIOWrapper(f, encoding='utf-8-sig')

        # Convert byte-stream to strings
        elif self.extension == 'csv':
            f = io.TextIOWrapper(f)

        return pyexcel.get_sheet(file_type=self.extension, file_stream=f)
Beispiel #51
0
def importar_gene(var):
    if 'username' in session:
        form = ImportExcel(request.form)
        if request.method=='POST':
            archivo = Manejo_archivos(request.files["archivo"])
            archivo.guardar()
            success_message = 'Tu archivo se ha guardado exitosamente'
            flash(success_message)
            cantidad = archivo.contar_lineas()
            leidos = pyexcel.get_sheet(file_name=archivo.archivo.filename, name_columns_by_row=0)
            return render_template("table.html", datos = leidos, cantidad = cantidad, tipo = "0", variables = var)
        else:
            return render_template('importar.html', form = form)
    else:
        return redirect('/users/login')
Beispiel #52
0
def main(base_dir):
    # Simple give the file name to **Reader**
    # "example.xls","example.xlsx","example.ods", "example.xlsm"
    spreadsheet = pe.get_sheet(file_name=os.path.join(base_dir, "example.csv"))

    # row_range() gives [0 .. number of rows]
    for r in spreadsheet.row_range():
        # column_range() gives [0 .. number of ranges]
        for c in spreadsheet.column_range():
            # cell_value(row_index, column_index)
            # return the value at the specified
            # position
            # please note that both row_index
            # and column_index starts from 0
            print(spreadsheet.cell_value(r, c))
Beispiel #53
0
def test_simple_option():
    runner = CliRunner()
    file_fixture = os.path.join("tests", "fixtures", "multiple-sheets.xls")
    prefix = "output"
    result = runner.invoke(
        split, ['--output-file-type', 'xlsx', file_fixture, prefix])
    eq_(result.exit_code, 0)
    book = get_book(file_name=file_fixture)
    count = 0
    for sheet in book:
        splitted_file = "%s_%s_%s.xlsx" % (prefix, sheet.name, count)
        count += 1
        written_sheet = get_sheet(file_name=splitted_file)
        eq_(str(sheet), str(written_sheet))
        os.unlink(splitted_file)
Beispiel #54
0
 def test_save_file_as_another_one(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     testfile2 = "testfile2.csv"
     sheet.save_as(testfile)
     pe.isave_as(file_name=testfile, dest_file_name=testfile2)
     sheet = pe.get_sheet(file_name=testfile2)
     eq_(sheet.to_array(), data)
     os.unlink(testfile)
     os.unlink(testfile2)
Beispiel #55
0
    def process(self):
        file_type = self.excel_file.name.split(".")[1]
        if file_type not in self.file_types:
            self.errors.append('Файл недопустимого формата, допустимые форматы - ' + str(self.file_types))
            return

        sheet = pyexcel.get_sheet(file_type=file_type, file_stream=self.excel_file.read())

        logger.info('***********************************************************')
        logger.info('Начинаем обработку файла %s %s' % (self.excel_file.name, self.start_log_message))
        logger.info('***********************************************************')
        self.process_business_logic(sheet.rows())
        logger.info('*******************************************************')
        logger.info('Обработка файла %s %s' % (self.excel_file.name, self.end_log_message))
        logger.info('*******************************************************')
Beispiel #56
0
 def _download_and_verify_file_name(self, file_name):
     for file_type in FILE_TYPE_MIME_TABLE.keys():
         url_encoded_file_name = urllib_quote(file_name)
         response = self.client.get(("/polls/download_attachment/%s/%s" %
                                     (file_type, url_encoded_file_name)))
         assert response["Content-Type"] == FILE_TYPE_MIME_TABLE[file_type]
         assert response["Content-Disposition"] == (
             "attachment; filename=%s.%s;filename*=utf-8''%s.%s" %
             (url_encoded_file_name, file_type, url_encoded_file_name,
              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
Beispiel #57
0
 def test_sql_sheet(self):
     sheet = pe.get_sheet(session=Session(),
                          table=Pyexcel,
                          sheet_name='custom sheet',
                          export_columns=['weight', 'birth'])
     content = dedent("""
     custom sheet:
     +--------+------------+
     | weight | birth      |
     +--------+------------+
     | 11.25  | 2014-11-11 |
     +--------+------------+
     | 12.25  | 2014-11-12 |
     +--------+------------+""").strip('\n')
     self.assertEqual(str(sheet), content)
Beispiel #58
0
def get_data(file: str) -> dict:
    """
    Will return a simple set of data, without too much validation. Deduplicates data per unique category.

    data{'category': set('url1, url2')}

    Compound records are decompounded and placed in the correct categories. Below might result in four additions:
    category, category - url, url

    :param file:
    :return:
    """

    data: Dict[str, set] = {}

    try:
        sheet = p.get_sheet(file_name=file, name_columns_by_row=0)
    except XLRDError:
        # xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'thisfile'
        return data

    # Skips the first entry
    for row in sheet:
        # Data is parsed to python-like datatype. In this case we only expect strings and cast them as such.
        found_categories = str(row[0]).lower().strip().split(',')
        found_urls = str(row[1]).lower().strip().split(',')

        for found_category in found_categories:
            found_category = found_category.strip()

            for found_url in found_urls:
                found_url = found_url.strip()

                # create new category
                if found_category not in data:
                    data[found_category] = set()

                data[found_category].add(found_url)

    # During editing, it might happen there are some 'left over' cells that are also added.
    # These left overs contain no urls. If they do, and something has been attempted to be added to
    # 'empty', it is discarded. We require urls to be in a list / category.
    if '' in data:
        data.pop('')

    p.free_resources()

    return data
Beispiel #59
0
def exportarDatosExcel(
        request):  ##Se exportan los datos a un archivo de excel organizado

    lock.acquire()
    sheet = pyexcel.get_sheet(array=[
        vaca1[10], vaca1[6], vaca1[7], vaca1[0], vaca1[1], vaca1[2], vaca1[3],
        vaca1[4], vaca1[5], vaca1[8], Vacio, vaca2[10], vaca2[6], vaca2[7],
        vaca2[0], vaca2[1], vaca2[2], vaca2[3], vaca2[4], vaca2[5], vaca2[8],
        Vacio, vaca3[10], vaca3[6], vaca3[7], vaca3[0], vaca3[1], vaca3[2],
        vaca3[3], vaca3[4], vaca3[5], vaca3[8]
    ])
    lock.release()

    sheet.transpose()

    return excel.make_response(sheet, "xlsx")
 def test_write_irregular_arrays_to_file(self):
     r = pe.get_sheet(file_name=self.testfile, skip_empty_rows=False)
     actual = [
         [1, "", "", "", "", ""],
         ["", 2, "", "", "", ""],
         ["", 2, 3, "", "", ""],
         ["", 2, 3, 4, "", ""],
         ["", 2, 3, 4, "", ""],
         ["", 2, 3, 4, 5, 6],
         ["", "", "", "", 5, ""],
         ["", "", "", "", "", ""],
         ["", "", 3, "", "", ""],
         ["", "", 3, 4, "", ""],
         ["", "", 3, 4, "", ""],
     ]
     eq_(actual, r.array)