def test_issue_83_ods_file_handle():
# this proves that odfpy
# does not leave a file handle open at all
proc = psutil.Process()
test_file = get_fixtures("issue_61.ods")
open_files_l1 = proc.open_files()
# start with a csv file
data = pe.iget_array(file_name=test_file, library="pyexcel-ods")
open_files_l2 = proc.open_files()
delta = len(open_files_l2) - len(open_files_l1)
# cannot catch open file handle
assert delta == 0
# now the file handle get opened when we run through
# the generator
list(data)
open_files_l3 = proc.open_files()
delta = len(open_files_l3) - len(open_files_l1)
# cannot catch open file handle
assert delta == 0
# free the fish
pe.free_resources()
open_files_l4 = proc.open_files()
# this confirms that no more open file handle
eq_(open_files_l1, open_files_l4)
def test_issue_83_csv_file_handle():
proc = psutil.Process()
test_file = os.path.join("tests", "fixtures", "bug_01.csv")
open_files_l1 = proc.open_files()
# start with a csv file
data = pe.iget_array(file_name=test_file)
open_files_l2 = proc.open_files()
delta = len(open_files_l2) - len(open_files_l1)
# interestingly, no open file handle yet
assert delta == 0
# now the file handle get opened when we run through
# the generator
list(data)
open_files_l3 = proc.open_files()
delta = len(open_files_l3) - len(open_files_l1)
# caught an open file handle, the "fish" finally
assert delta == 1
# free the fish
pe.free_resources()
open_files_l4 = proc.open_files()
# this confirms that no more open file handle
eq_(open_files_l1, open_files_l4)
def test_get_array_from_file(self):
sheet = pe.Sheet(self.test_data)
testfile = "testfile.xls"
sheet.save_as(testfile)
result = pe.iget_array(file_name=testfile)
eq_(list(result), self.test_data)
os.unlink(testfile)
def sheet2sql(sheet, output, batch):
'''
Converts Excel sheets into SQL insert's, where SHEET is the file's
path and OUTPUT is all output files which names exists in the file.
'''
sheet_ext = os.path.splitext(sheet.name)[1][1:]
insert_layout = 'INSERT INTO {} ({}) VALUES ({})\n'
for sql_output in output:
sheet_name = os.path.splitext(os.path.basename(sql_output.name))[0]
try:
rows = pyexcel.iget_array(file_type=sheet_ext,
file_content=sheet,
row_renderer=renderer,
sheet_name=sheet_name)
except Exception:
raise Exception('Couldn\'t open %r' % sheet.name)
columns = ','.join(header.replace('\'', '') for header in next(rows))
for index, row in enumerate(rows, start=1):
if index > 1 and index % batch == 0:
sql_output.write('GO\n')
sql_output.write(
insert_layout.format(sheet_name, columns, ','.join(row)))
def test_issue_83_ods_file_handle():
# this proves that odfpy
# does not leave a file handle open at all
proc = psutil.Process()
test_file = get_fixtures("12_day_as_time.ods")
open_files_l1 = proc.open_files()
# start with a csv file
data = pe.iget_array(file_name=test_file, library="pyexcel-ods3")
open_files_l2 = proc.open_files()
delta = len(open_files_l2) - len(open_files_l1)
# cannot catch open file handle
assert delta == 0
# now the file handle get opened when we run through
# the generator
list(data)
open_files_l3 = proc.open_files()
delta = len(open_files_l3) - len(open_files_l1)
# cannot catch open file handle
assert delta == 0
# free the fish
pe.free_resources()
open_files_l4 = proc.open_files()
# this confirms that no more open file handle
eq_(open_files_l1, open_files_l4)
def test_gc():
gc.free_resources()
data = iget_array(
file_name=os.path.join("tests", "fixtures", "bug_01.csv"))
data = list(data)
eq_(len(gc.GARBAGE), 1)
gc.free_resources()
assert len(gc.GARBAGE) == 0
def test_get_array_from_dict(self):
adict = {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.iget_array(adict=adict)
eq_(list(result), self.test_data)
def test_look_at_sheet_names_decides_to_read_seond_one(self):
test_file = "test_get_book.xls"
content = _produce_ordered_dict()
book = pe.Book(content)
book.save_as(test_file)
book_stream = pe.iget_book(file_name=test_file)
data = pe.iget_array(sheet_stream=book_stream["Sheet1"])
assert isinstance(data, GeneratorType)
eq_(list(data), [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]])
os.unlink(test_file)
def iget_array(self, **keywords):
"""
Get a generator for a list of lists from the file
:param sheet_name: For an excel book, there could be multiple
sheets. If it is left unspecified, the
sheet at index 0 is loaded. For 'csv',
'tsv' file, *sheet_name* should be None anyway.
:param keywords: additional key words
:returns: A generator for a list of lists
"""
params = self.get_params(**keywords)
return pe.iget_array(**params)
def from_file(cls, file_content, filename=''):
extension = cls.get_extension(filename)
if extension == 'csv':
return cls(Spreadsheet.normalise_newlines(file_content), filename)
if extension == 'tsv':
file_content = StringIO(
Spreadsheet.normalise_newlines(file_content))
instance = cls.from_rows(
pyexcel.iget_array(file_type=extension, file_stream=file_content),
filename)
pyexcel.free_resources()
return instance
def convert(filename):
iter_row = pyexcel.iget_array(file_name=filename,
sheet_name='Slide conference')
next(iter_row)
for row in iter_row:
if not row:
break
pr_record = case.session.query(
case.PathoReport).filter_by(patho_id=row[3]).first()
if pr_record is None:
pr_record = case.PathoReport()
pr_record.patho_id = row[3]
pr_record.received = row[1]
pr_record.type = row[2]
case.session.add(pr_record)
case.session.commit()
kw_record = case.session.query(
case.Keyword).filter_by(name=row[4]).first()
if kw_record is None:
kw_record = case.Keyword()
kw_record.name = row[4]
case.session.add(kw_record)
case.session.commit()
kp_record = case.session.query(case.KeywordPatho)\
.filter_by(patho_id=pr_record.id, keyword_id=kw_record.id).first()
if kp_record is None:
kp_record = case.KeywordPatho()
kp_record.patho_id = pr_record.id
kp_record.keyword_id = kw_record.id
case.session.add(kp_record)
case.session.commit()
pyexcel.free_resources()
def test_issue_83_xls_file_handle():
proc = psutil.Process()
test_file = os.path.join("tests", "fixtures", "date_field.xls")
open_files_l1 = proc.open_files()
# start with a csv file
data = p.iget_array(file_name=test_file)
open_files_l2 = proc.open_files()
delta = len(open_files_l2) - len(open_files_l1)
# interestingly, no open file using xlrd
assert delta == 0
# now the file handle get opened when we run through
# the generator
list(data)
open_files_l3 = proc.open_files()
delta = len(open_files_l3) - len(open_files_l1)
# still no open file
assert delta == 0
p.free_resources()
open_files_l4 = proc.open_files()
eq_(open_files_l1, open_files_l4)
def check_file(file_path):
"""Checa se:
1) o arquivo existe
2) é um arquivo excel e
3) possui as colunas necessárias
"""
try:
sheet_array = pyexcel.iget_array(file_name=file_path)
except FileNotFoundError as e:
raise (Exception(('Não foi possível encontrar o arquivo %s, '
'verifique se o local está correto') % file_path))
pyexcel.free_resources()
except FileTypeNotSupported as e:
raise (Exception(
('O arquivo %s não é suportado, '
'os formatos suportados são: xls e xlsx') % file_path))
pyexcel.free_resources()
header = sheet_array.__next__()
for tipo_arquivo in tipos_arquivos:
if check_header(header, tipo_arquivo):
print('File ok!')
return tipo_arquivo
pyexcel.free_resources()
raise Exception('Não foi possível reconhecer o formato da planilha')
def test_get_sheet_from_recrods(self):
records = [{"X": 1, "Y": 2, "Z": 3}, {"X": 4, "Y": 5, "Z": 6}]
result = pe.iget_array(records=records)
eq_(list(result), self.test_data)
def test_get_array_from_array(self):
result = pe.iget_array(array=self.test_data)
eq_(list(result), self.test_data)
