예제 #1
0
    def test_pyexcel_issue_8_with_memory_file(self):
        """pyexcel issue #8

        formular got lost
        """
        tmp_file = "issue_8_save_as.xlsx"
        f = open(os.path.join("tests",
                              "test-fixtures",
                              "test8.xlsx"),
                 "rb")
        s = pe.load_from_memory('xlsx', f.read())
        s.save_as(tmp_file)
        s2 = pe.load(tmp_file)
        assert str(s) == str(s2)
        content = dedent("""
        Sheet Name: CNY
        +----------+----------+------+---+--------+
        | 01/09/13 | 02/09/13 | 1000 | 5 | 13.890 |
        +----------+----------+------+---+--------+
        | 02/09/13 | 03/09/13 | 2000 | 6 | 33.330 |
        +----------+----------+------+---+--------+
        | 03/09/13 | 04/09/13 | 3000 | 7 | 58.330 |
        +----------+----------+------+---+--------+""").strip("\n")
        assert str(s2) == content
        os.unlink(tmp_file)
예제 #2
0
    def test_pyexcel_issue_8_with_memory_file(self):
        """pyexcel issue #8

        formular got lost
        """
        tmp_file = "issue_8_save_as.xlsx"
        f = open(os.path.join("tests",
                              "test-fixtures",
                              "test8.xlsx"),
                 "rb")
        s = pe.load_from_memory('xlsx', f.read())
        s.save_as(tmp_file)
        s2 = pe.load(tmp_file)
        self.assertEqual(str(s), str(s2))
        content = dedent("""
        CNY:
        +----------+----------+------+---+-------+
        | 01/09/13 | 02/09/13 | 1000 | 5 | 13.89 |
        +----------+----------+------+---+-------+
        | 02/09/13 | 03/09/13 | 2000 | 6 | 33.33 |
        +----------+----------+------+---+-------+
        | 03/09/13 | 04/09/13 | 3000 | 7 | 58.33 |
        +----------+----------+------+---+-------+""").strip("\n")
        self.assertEqual(str(s2), content)
        os.unlink(tmp_file)
예제 #3
0
def do_write_stringio2(file_type):
    data = [[1, 2, 3], [4, 5, 6]]
    r = pe.Sheet(data)
    getter = getattr(r, "get_" + file_type)
    content = getter()
    r = pe.load_from_memory(file_type, content)
    result = [1, 2, 3, 4, 5, 6]
    actual = list(r.enumerate())
    eq_(result, actual)
예제 #4
0
 def test_csv_output_stringio2(self):
     data = [[1, 2, 3], [4, 5, 6]]
     r = pe.Sheet(data)
     io = StringIO()
     r.save_to_memory("csv", io)
     r = pe.load_from_memory("csv", io.getvalue())
     result = ['1', '2', '3', '4', '5', '6']
     actual = pe.utils.to_array(r.enumerate())
     assert actual == result
예제 #5
0
def do_write_stringio2(file_type):
    data = [[1, 2, 3], [4, 5, 6]]
    r = pe.Sheet(data)
    getter = getattr(r, "get_" + file_type)
    content = getter()
    r = pe.load_from_memory(file_type, content)
    result = [1, 2, 3, 4, 5, 6]
    actual = pe.utils.to_array(r.enumerate())
    assert actual == result
예제 #6
0
 def test_xlsm_output_stringio(self):
     data = [[1, 2, 3], [4, 5, 6]]
     io = BytesIO()
     w = pe.Writer(("xlsm", io))
     w.write_rows(data)
     w.close()
     r = pe.load_from_memory("xlsm", io.getvalue())
     result = [1, 2, 3, 4, 5, 6]
     actual = pe.utils.to_array(r.enumerate())
     assert result == actual
예제 #7
0
 def test_xlsm_output_stringio(self):
     data = [
         [1, 2, 3],
         [4, 5, 6]
     ]
     io = pe.save_as(dest_file_type="xlsm",array=data)
     r = pe.load_from_memory("xlsm", io.getvalue())
     result=[1, 2, 3, 4, 5, 6]
     actual = pe.utils.to_array(r.enumerate())
     assert result == actual
예제 #8
0
 def test_xls_stringio(self):
     csvfile = "cute.xls"
     create_sample_file1(csvfile)
     with open(csvfile, "rb") as f:
         content = f.read()
         r = pe.load_from_memory("xls", 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(csvfile):
         os.unlink(csvfile)
예제 #9
0
 def test_csvz_stringio(self):
     csvfile = "cute.csvz"
     create_sample_file1(csvfile)
     with open(csvfile, "rb") as f:
         content = f.read()
         r = pe.load_from_memory("csvz", 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(csvfile):
         os.unlink(csvfile)
예제 #10
0
def do_write_stringio2(file_type):
    data = [
        [1, 2, 3],
        [4, 5, 6]
    ]
    r = pe.Sheet(data)
    getter = getattr(r, "get_"+file_type)
    content = getter()
    r = pe.load_from_memory(file_type, content)
    result=[1, 2, 3, 4, 5, 6]
    actual = pe.utils.to_array(r.enumerate())
    assert actual == result
예제 #11
0
 def test_csv_output_stringio2(self):
     data = [
         [1, 2, 3],
         [4, 5, 6]
     ]
     r = pe.Sheet(data)
     io = StringIO()
     r.save_to_memory("csv", io)
     r = pe.load_from_memory("csv", io.getvalue())
     result=['1', '2', '3', '4', '5', '6']
     actual = pe.utils.to_array(r.enumerate())
     assert actual == result
예제 #12
0
def upload():
    if request.method == 'POST' and 'excel' in request.files:
        # handle file upload
        filename = request.files['excel'].filename
        extension = filename.split(".")[1]
        # Obtain the file extension and content
        # pass a tuple instead of a file name
        sheet = pe.load_from_memory(extension, request.files['excel'].read())
        # then use it as usual
        data = sheet.to_dict()
        # respond with a json
        return jsonify({"result":data})
    return render_template('upload.html')
예제 #13
0
 def test_xlsm_output_stringio(self):
     data = [
         [1, 2, 3],
         [4, 5, 6]
     ]
     io = BytesIO()
     w = pe.Writer(("xlsm",io))
     w.write_rows(data)
     w.close()
     r = pe.load_from_memory("xlsm", io.getvalue())
     result=[1, 2, 3, 4, 5, 6]
     actual = pe.utils.to_array(r.enumerate())
     assert result == actual
예제 #14
0
 def test_array(self):
     for struct_type in ["array", "dict", "records"]:
         print("Testing %s" % struct_type)
         io = BytesIO()
         sheet = pe.Sheet(self.data)
         sheet.save_to_memory('xls', io)
         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.load_from_memory('xls', response.data)
         assert sheet.to_array() == self.data
예제 #15
0
    def _excel_file_to_dict(file):
        """
        Loads an Excel file and transforms it into a dictionary. It uses the first row's cells
        as keys for the dictionary and the columns are the contents.
        :param file: The Excel file to transform.
        :return: A dictionary of arrays.
        """
        valid_extensions = ['.xls', '.xlsx']

        validate_file_extension(file, valid_extensions)

        file_extension = file.name.split(".")[1]
        sheet = pyexcel.load_from_memory(file_extension,
                                         file.read(),
                                         name_columns_by_row=0,
                                         name_rows_by_column=-1)

        return pyexcel.to_dict(sheet)
 def test_download(self):
     for upload_file_type in ['xlsx']:#FILE_TYPE_MIME_TABLE.keys():
         file_name = 'test.%s' % upload_file_type
         for download_file_type in ['tsv']:#FILE_TYPE_MIME_TABLE.keys():
             print("Uploading %s Downloading %s" % (upload_file_type, download_file_type))
             io = BytesIO()
             sheet = pe.Sheet(self.data)
             sheet.save_to_memory(upload_file_type, io)
             io.seek(0)
             response = self.app.post('/switch/%s' % download_file_type,
                                      buffered=True,
                                      data={"file": (io, file_name)},
                                      content_type="multipart/form-data")
             assert response.content_type == FILE_TYPE_MIME_TABLE[download_file_type]
             sheet = pe.load_from_memory(download_file_type, response.data)
             sheet.format(int)
             array = sheet.to_array()
             assert array == self.data
 def test_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))
             io = pe.get_io(upload_file_type)
             sheet = pe.Sheet(self.data)
             sheet.save_to_memory(upload_file_type, io)
             io.seek(0)
             if not PY2:
                 if not isinstance(io, BytesIO):
                     io = BytesIO(io.getvalue().encode('utf-8'))
             response = self.app.post('/switch/%s' % download_file_type,
                                      buffered=True,
                                      data={"file": (io, file_name)},
                                      content_type="multipart/form-data")
             assert response.content_type == FILE_TYPE_MIME_TABLE[download_file_type]
             sheet = pe.load_from_memory(download_file_type, response.data)
             sheet.format(int)
             array = sheet.to_array()
             assert array == self.data
예제 #18
0
def test_pyexcel_issue_8_with_memory_file():
    """pyexcel issue #8

    formular got lost
    """
    tmp_file = "issue_8_save_as.xlsx"
    f = open(get_fixtures("test8.xlsx"), "rb")
    s = pe.load_from_memory("xlsx", f.read())
    s.save_as(tmp_file)
    s2 = pe.load(tmp_file)
    eq_(str(s), str(s2))
    content = dedent("""
    CNY:
    +----------+----------+------+---+-------+
    | 01/09/13 | 02/09/13 | 1000 | 5 | 13.89 |
    +----------+----------+------+---+-------+
    | 02/09/13 | 03/09/13 | 2000 | 6 | 33.33 |
    +----------+----------+------+---+-------+
    | 03/09/13 | 04/09/13 | 3000 | 7 | 58.33 |
    +----------+----------+------+---+-------+""").strip("\n")
    eq_(str(s2), content)
    os.unlink(tmp_file)
예제 #19
0
Tokelau,TK,TKL,TOKELAU
Tonga,TO,TON,TONGA
Trinidad and Tobago,TT,TTO,TRINIDAD AND TOBAGO
Tunisia,TN,TUN,TUNISIA
Turkey,TR,TUR,TURKEY
Turkmenistan,TM,TKM,TURKMENISTAN
Turks and Caicos Islands,TC,TCA,TURKS AND CAICOS ISLANDS
Tuvalu,TV,TUV,TUVALU
Uganda,UG,UGA,UGANDA
Ukraine,UA,UKR,UKRAINE
United Arab Emirates,AE,ARE,UNITED ARAB EMIRATES
United Kingdom of Great Britain and Northern Ireland,GB,GBR,UNITED KINGDOM OF GREAT BRITAIN AND NORTHERN IRELAND
United Republic of Tanzania,TZ,TZA,"TANZANIA, UNITED REPUBLIC OF"
United States Minor Outlying Islands,UM,UMI,UNITED STATES MINOR OUTLYING ISLANDS
United States Virgin Islands,VI,VIR,VIRGIN ISLANDS (U.S.)
United States of America,US,USA,UNITED STATES OF AMERICA
Uruguay,UY,URY,URUGUAY
Uzbekistan,UZ,UZB,UZBEKISTAN
Vanuatu,VU,VUT,VANUATU
Venezuela (Bolivarian Republic of),VE,VEN,VENEZUELA (BOLIVARIAN REPUBLIC OF)
Viet Nam,VN,VNM,VIET NAM
Wallis and Futuna Islands,WF,WLF,WALLIS AND FUTUNA
Western Sahara,EH,ESH,WESTERN SAHARA
Yemen,YE,YEM,YEMEN
Zambia,ZM,ZMB,ZAMBIA
Zimbabwe,ZW,ZWE,ZIMBABWE
Åland Islands,AX,ALA,ÅLAND ISLANDS
"""

country_codes = pe.load_from_memory("csv", countrycodes.lower()).to_array()
예제 #20
0
파일: data_import.py 프로젝트: od-5/crm
def client_list_import(request):
    if request.method == 'POST' and 'file' in request.FILES:
        filename = request.FILES['file'].name
        extension = filename.split(".")[1]
        sheet = pyexcel.load_from_memory(extension, request.FILES['file'].read())
        data = pyexcel.to_dict(sheet)
        user = request.user
        error = []
        success = []
        i = 1
        for row in data:
            if row != 'Series_1':
                city = data[row][0]
                name = data[row][1]
                kind_of_activity = data[row][2]
                actual_address = data[row][3]
                site = data[row][4]
                contact_name = data[row][5]
                contact_function = data[row][6]
                contact_phone = data[row][7]
                contact_email = data[row][8]
                try:
                    # проверяем город
                    city_instance = City.objects.get(name__iexact=city)
                    try:
                        incomingclient = IncomingClient.objects.get(name__iexact=name)
                        error.append(u'Строка: %s .Клиент %s уже есть в системе' % (i, incomingclient.name))
                    except:
                        incomingclient = IncomingClient(
                            city=city_instance,
                            name=name,
                            manager=user.manager,
                        )
                        if kind_of_activity:
                            incomingclient.kind_of_activity = kind_of_activity
                        if actual_address:
                            incomingclient.actual_address = actual_address
                        if site:
                            incomingclient.site = site
                        incomingclient.save()
                        success.append(u'Клиент %s добавлен в систему' % name)
                        if contact_name:
                            incomingcontact = IncomingClientContact(
                                incomingclient=incomingclient,
                                name=contact_name
                            )
                        if contact_email:
                            incomingcontact.email = contact_email
                        if contact_phone:
                            incomingcontact.phone = contact_phone
                        if contact_function:
                            incomingcontact.function = contact_function
                        incomingcontact.save()
                        incomingclientmanager = IncomingClientManager(
                            manager=user.manager,
                            incomingclient=incomingclient
                        )
                        incomingclientmanager.save()
                except:
                    error.append(u'Строка %s, Город %s не доступен для модератора %s' % (i, city, user.manager.moderator))
            i += 1
        context = {
            'error_list': error,
            'error_count': len(error),
            'success_list': success,
            'success_count': len(success)
        }
        return render(request, 'incoming/import_error.html', context)
    else:
        return HttpResponseRedirect(reverse('incoming:list'))
예제 #21
0
def address_list_import(request):
    if request.method == 'POST' and 'file' in request.FILES:
        filename = request.FILES['file'].name
        extension = filename.split(".")[1]
        sheet = pyexcel.load_from_memory(extension,
                                         request.FILES['file'].read())
        data = pyexcel.to_dict(sheet)
        for row in data:
            if row != 'Series_1':
                city = data[row][0]
                area = data[row][1]
                street = data[row][2]
                house_number = ''
                point_flag = False
                for i in unicode(data[row][3]):
                    if i == '.':
                        point_flag = True
                    if point_flag:
                        if i != '0' and i != '.':
                            house_number += i
                    else:
                        house_number += i

                # try:
                #     porch_list = [int(data[row][4])]
                # except:
                #     r_porch_list = data[row][4].strip().split(',')
                #     porch_list = []
                #     for porch in r_porch_list:
                #         try:
                #             porch_list.append(int(porch))
                #         except:
                #             pass
                raw_porch_list = str(data[row][4]).replace(
                    '.', ',').strip().split(',')
                # import pdb; pdb.set_trace()
                porch_list = []
                for i in raw_porch_list:
                    if i.strip().isdigit(
                    ) and i.strip() != '0' or i.strip() != '':
                        porch_list.append(int(i.strip()))
                try:
                    # пробуем получить город
                    city_instance = City.objects.get(name__iexact=city)
                    try:
                        # пробуем получить район
                        area_instance = Area.objects.get(city=city_instance,
                                                         name__iexact=area)
                    except:
                        # создаём новый район
                        area_instance = Area(city=city_instance, name=area)
                        area_instance.save()
                    try:
                        # пробуем получить улицу
                        street_instance = Street.objects.get(
                            city=city_instance,
                            area=area_instance,
                            name__iexact=street)
                    except:
                        # создаём новую улицу
                        street_instance = Street(city=city_instance,
                                                 area=area_instance,
                                                 name=street)
                        street_instance.save()
                    try:
                        # пробуем получить поверхность
                        surface_instance = Surface.objects.get(
                            city=city_instance,
                            street=street_instance,
                            house_number=house_number)
                    except:
                        # создаём поверхность
                        surface_instance = Surface(city=city_instance,
                                                   street=street_instance,
                                                   house_number=house_number)
                        surface_instance.save()
                    for i in porch_list:
                        # пробегаемся по списку подъездов
                        try:
                            Porch.objects.get(surface=surface_instance,
                                              number=i)
                        except:
                            porch = Porch(surface=surface_instance, number=i)
                            porch.save()
                except:
                    pass
                    # print u'Город не найден'
    return HttpResponseRedirect(reverse('surface:list'))
예제 #22
0
파일: data_import.py 프로젝트: od-5/elite
def address_list_import(request):
    if request.method == "POST" and "file" in request.FILES:
        filename = request.FILES["file"].name
        extension = filename.split(".")[1]
        sheet = pyexcel.load_from_memory(extension, request.FILES["file"].read())
        data = pyexcel.to_dict(sheet)
        for row in data:
            # print row
            if row != "Series_1":
                city = data[row][0]
                area = data[row][1].strip()
                street = data[row][2].strip()
                house_number = ""
                point_flag = False
                for i in unicode(data[row][3]):
                    if i == ".":
                        point_flag = True
                    if point_flag:
                        if i != "0" and i != ".":
                            house_number += i
                    else:
                        house_number += i

                try:
                    # пробуем получить город
                    city_instance = City.objects.get(name__iexact=city)
                    # print 'has city %s' % city_instance
                    try:
                        # пробуем получить район
                        area_instance = CityArea.objects.get(city=city_instance, name__iexact=area)
                        # print 'has area %s' % area_instance
                    except:
                        # создаём новый район
                        area_instance = CityArea(city=city_instance, name=area)
                        area_instance.save()
                        # print 'create area %s' % area_instance
                    try:
                        # пробуем получить улицу
                        street_instance = CityStreet.objects.get(
                            city=city_instance, area=area_instance, name__iexact=street
                        )
                        # print 'has street %s' % street_instance
                    except:
                        # создаём новую улицу
                        street_instance = CityStreet(city=city_instance, area=area_instance, name=street)
                        street_instance.save()
                        # print 'create street %s' % street_instance
                    try:
                        # пробуем получить поверхность
                        house_instance = CityHouse.objects.get(
                            city=city_instance, area=area_instance, street=street_instance, number=house_number
                        )
                        # print 'has house %s' % house_instance
                    except:
                        # создаём поверхность
                        house_instance = CityHouse(
                            city=city_instance, area=area_instance, street=street_instance, number=house_number
                        )
                        house_instance.save()
                        # print 'create house %s' % house_instance
                except:
                    pass
                    return HttpResponseRedirect(reverse("admin-index"))
        return HttpResponseRedirect("/admin/city/cityhouse/")
예제 #23
0
파일: data_import.py 프로젝트: od-5/crm
def address_list_import(request):
    if request.method == 'POST' and 'file' in request.FILES:
        filename = request.FILES['file'].name
        extension = filename.split(".")[1]
        sheet = pyexcel.load_from_memory(extension, request.FILES['file'].read())
        data = pyexcel.to_dict(sheet)
        for row in data:
            if row != 'Series_1':
                city = data[row][0]
                area = data[row][1]
                street = data[row][2]
                house_number = ''
                point_flag = False
                for i in unicode(data[row][3]):
                    if i == '.':
                        point_flag = True
                    if point_flag:
                        if i != '0' and i != '.':
                            house_number += i
                    else:
                        house_number += i

                # try:
                #     porch_list = [int(data[row][4])]
                # except:
                #     r_porch_list = data[row][4].strip().split(',')
                #     porch_list = []
                #     for porch in r_porch_list:
                #         try:
                #             porch_list.append(int(porch))
                #         except:
                #             pass
                raw_porch_list = str(data[row][4]).replace('.', ',').strip().split(',')
                # import pdb; pdb.set_trace()
                porch_list = []
                for i in raw_porch_list:
                    if i.strip() != '0' or i.strip() != '0':
                        porch_list.append(int(i.strip()))
                try:
                    # пробуем получить город
                    city_instance = City.objects.get(name__iexact=city)
                    try:
                        # пробуем получить район
                        area_instance = Area.objects.get(city=city_instance, name__iexact=area)
                    except:
                        # создаём новый район
                        area_instance = Area(city=city_instance, name=area)
                        area_instance.save()
                    try:
                        # пробуем получить улицу
                        street_instance = Street.objects.get(city=city_instance, area=area_instance, name__iexact=street)
                    except:
                        # создаём новую улицу
                        street_instance = Street(city=city_instance, area=area_instance, name=street)
                        street_instance.save()
                    try:
                        # пробуем получить поверхность
                        surface_instance = Surface.objects.get(city=city_instance, street=street_instance,
                                                               house_number=house_number)
                    except:
                        # создаём поверхность
                        surface_instance = Surface(city=city_instance, street=street_instance, house_number=house_number)
                        surface_instance.save()
                    for i in porch_list:
                        # пробегаемся по списку подъездов
                        try:
                            Porch.objects.get(surface=surface_instance, number=i)
                        except:
                            porch = Porch(surface=surface_instance, number=i)
                            porch.save()
                except:
                    pass
                    # print u'Город не найден'
    return HttpResponseRedirect(reverse('surface:list'))
예제 #24
0
def client_list_import(request):
    if request.method == 'POST' and 'file' in request.FILES:
        filename = request.FILES['file'].name
        extension = filename.split(".")[1]
        sheet = pyexcel.load_from_memory(extension,
                                         request.FILES['file'].read())
        data = pyexcel.to_dict(sheet)
        user = request.user
        error = []
        success = []
        i = 1
        for row in data:
            if row != 'Series_1':
                city = data[row][0]
                name = data[row][1]
                kind_of_activity = data[row][2]
                actual_address = data[row][3]
                site = data[row][4]
                contact_name = data[row][5]
                contact_function = data[row][6]
                contact_phone = data[row][7]
                contact_email = data[row][8]
                try:
                    # проверяем город
                    city_instance = City.objects.get(name__iexact=city)
                    try:
                        incomingclient = IncomingClient.objects.get(
                            name__iexact=name)
                        error.append(
                            u'Строка: %s .Клиент %s уже есть в системе' %
                            (i, incomingclient.name))
                    except:
                        incomingclient = IncomingClient(
                            city=city_instance,
                            name=name,
                            manager=user.manager,
                        )
                        if kind_of_activity:
                            incomingclient.kind_of_activity = kind_of_activity
                        if actual_address:
                            incomingclient.actual_address = actual_address
                        if site:
                            incomingclient.site = site
                        incomingclient.save()
                        success.append(u'Клиент %s добавлен в систему' % name)
                        if contact_name:
                            incomingcontact = IncomingClientContact(
                                incomingclient=incomingclient,
                                name=contact_name)
                        if contact_email:
                            incomingcontact.email = contact_email
                        if contact_phone:
                            incomingcontact.phone = contact_phone
                        if contact_function:
                            incomingcontact.function = contact_function
                        incomingcontact.save()
                        incomingclientmanager = IncomingClientManager(
                            manager=user.manager,
                            incomingclient=incomingclient)
                        incomingclientmanager.save()
                except:
                    error.append(
                        u'Строка %s, Город %s не доступен для модератора %s' %
                        (i, city, user.manager.moderator))
            i += 1
        context = {
            'error_list': error,
            'error_count': len(error),
            'success_list': success,
            'success_count': len(success)
        }
        return render(request, 'incoming/import_error.html', context)
    else:
        return HttpResponseRedirect(reverse('incoming:list'))