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)
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)
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)
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
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
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
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
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)
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)
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
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
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')
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
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
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
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)
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()
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'))
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'))
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/")
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'))
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'))