def __init__(self, in_file, sheet_name=None):
"""
:param str|Path in_file: can be a folder, *.xlsx or *.zip
:param str sheet_name:
"""
if sheet_name is None:
sheet_name = 'flashcards'
self.modified = time()
self._sheet_name = sheet_name
if not isinstance(in_file, Path):
in_file = Path(in_file)
if in_file.exists():
if in_file.suffix != '':
self.excel = in_file
self.all_sheets = pyexcel.get_book_dict(file_name=str(self.excel))
self.data = self._load_raw_data(self.all_sheets, self._sheet_name)
else:
self.excel = in_file.joinpath(in_file.stem + '.xlsx')
self.all_sheets = pyexcel.get_book_dict(str(self.excel))
self.data = self._load_raw_data(self.all_sheets, self._sheet_name)
else:
self.excel = in_file
self.data = OrderedDict()
def test_issue_9_hidden_sheet_2():
test_file = get_fixture("hidden_sheets.xls")
book_dict = pe.get_book_dict(file_name=test_file,
skip_hidden_sheets=False)
assert "hidden" in book_dict
eq_(book_dict['shown'], [['A', 'B']])
eq_(book_dict['hidden'], [['a', 'b']])
def search():
for file in fList:
try:
book = p.get_book_dict(file_name=file)
iterateOverCells(book, file)
except KeyboardInterrupt:
# print('KeyboardInterrupt exception is caught')
sys.exit(0)
except:
print(f"Error:\tUnsupported format, password protected or corrupted file: {file}", file=sys.stderr)
pass
if count == True:
print("Total matches: ", len(countMatches),
"Cells, ", len(strMatches), "Strings")
if showFileAndSheetName or filename == True:
for x in Counter(matchFILES):
d = Counter(matchFILES)
print(str(x) + ": " + str(d[x]) + " Rows")
else:
pass
elif files_with_match:
MYset = list(set(matchFILES))
MYset.sort()
[print(fx) for fx in MYset]
elif files_without_match:
MYset = list(set(NONmatchFILES))
MYset.sort()
[print(fx) for fx in MYset]
def test_book_file(self):
data = {
"Category": [["id", "name"], [1, "News"], [2, "Sports"]],
"Post": [
["id", "title", "body", "pub_date", "category"],
[1, "Title A", "formal", datetime(2015, 1, 20, 23, 28, 29), "News"],
[2, "Title B", "informal", datetime(2015, 1, 20, 23, 28, 30), "Sports"],
],
}
for upload_file_type in ["xls"]:
print("Uploading %s" % upload_file_type)
file_name = "test.%s" % upload_file_type
io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type)
if not PY2:
if isinstance(io, BytesIO):
content = io.getvalue()
else:
content = io.getvalue().encode("utf-8")
else:
content = io.getvalue()
response = self.app.post("/upload/all", upload_files=[("file", file_name, content)])
ret = pe.get_book_dict(file_type="xls", file_content=response.body)
assert data["Category"] == ret["category"]
sheet = pe.Sheet(data["Post"], name_columns_by_row=0)
sheet.column.format("pub_date", lambda d: d.isoformat())
sheet2 = pe.Sheet(ret["post"], name_columns_by_row=0)
for key in sheet.colnames:
if key == "category":
continue
assert sheet.column[key] == sheet2.column[key]
assert sheet2.column["category_id"] == [1, 2]
def excel_data(file_path):
fl=pyexcel.get_book_dict(file_name=file_path)
data=None
for sheet in fl:
if isinstance(fl[sheet], list):
data=fl[sheet]
return data
def test_book_file(self):
data = {
"Category":[
["id", "name"],
[1, "News"],
[2, "Sports"]
],
"Post":[
["id", "title", "body", "pub_date", "category"],
[1, "Title A", "formal", datetime(2015,1,20,23,28,29), "News"],
[2, "Title B", "informal", datetime(2015,1,20,23,28,30), "Sports"]
]
}
for upload_file_type in ['xls']:
with app.app_context():
db.drop_all()
db.create_all()
print("Uploading %s" % upload_file_type)
file_name = "test.%s" % upload_file_type
io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type)
response = self.app.post('/upload/all',
buffered=True,
data={"file": (io, file_name)},
content_type="multipart/form-data")
ret = pe.get_book_dict(file_type="xls", file_content=response.data)
assert data['Category'] == ret['category']
sheet = pe.Sheet(data['Post'], name_columns_by_row=0)
sheet.column.format("pub_date", lambda d: d.isoformat())
sheet2 = pe.Sheet(ret['post'], name_columns_by_row=0)
for key in sheet.colnames:
if key == "category":
continue
assert sheet.column[key] == sheet2.column[key]
assert sheet2.column['category_id'] == [1, 2]
def test_save_book_to_memory_from_sql(self):
test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls")
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def excel_to_json(file_path: str, sheet_name: str = None, want_sheet_names: bool = False) -> dict:
"""
This function reads the excel file and converts it to JSON
:param file_path:
:param sheet_name:
:param want_sheet_names:
:return:
"""
sheet_data = {'columnDefs': [{'headerName': "", 'field': "^", 'pinned': "left"}], 'rowData': []}
column_index_map = {}
result = dict()
if not sheet_name or want_sheet_names:
result['sheetNames'] = list()
book_dict = pyexcel.get_book_dict(file_name=file_path)
for sheet in book_dict.keys():
result['sheetNames'].append(sheet)
if not sheet_name:
sheet_name = result['sheetNames'][0]
sheet = book_dict[sheet_name]
else:
result["sheetNames"] = None
sheet = pyexcel.get_sheet(sheet_name=sheet_name, file_name=file_path)
result["currSheetName"] = sheet_name
for i in range(len(sheet[0])):
column = get_column_letter(i+1)
column_index_map[i+1] = column
sheet_data['columnDefs'].append({'headerName': column_index_map[i + 1], 'field': column_index_map[i + 1]})
for row in range(len(sheet)):
r = {'^': str(row + 1)}
for col in range(len(sheet[row])):
r[column_index_map[col+1]] = str(sheet[row][col]).strip()
sheet_data['rowData'].append(r)
result['sheetData'] = sheet_data
return result
def _load_pyexcel_xlsx(self):
updated_data = pyexcel.get_book_dict(
file_name=str(self.in_file.absolute()))
self.meta.setdefault('_styles', dict())['excel'] = ExcelFormatter(
self.in_file).data
return self._set_updated_data(updated_data)
def test_issue_9_hidden_sheet_2(self):
test_file = os.path.join("tests", "fixtures", "hidden_sheets.xls")
book_dict = pe.get_book_dict(file_name=test_file,
skip_hidden_sheets=False)
assert "hidden" in book_dict
eq_(book_dict['shown'], [['A', 'B']])
eq_(book_dict['hidden'], [['a', 'b']])
def test_get_book_from_sql(self):
book_dict = pe.get_book_dict(session=Session(),
tables=[Signature, Signature2])
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def test_issue_9_hidden_sheet_2():
test_file = get_fixture("hidden_sheets.xls")
book_dict = pe.get_book_dict(file_name=test_file,
skip_hidden_sheets=False)
assert "hidden" in book_dict
eq_(book_dict['shown'], [['A', 'B']])
eq_(book_dict['hidden'], [['a', 'b']])
def test_issue_8_hidden_sheet_2():
test_file = get_fixtures("hidden_sheets.xlsx")
book_dict = pe.get_book_dict(file_name=test_file,
skip_hidden_sheets=False,
library="pyexcel-xlsx")
assert "hidden" in book_dict
eq_(book_dict["shown"], [["A", "B"]])
eq_(book_dict["hidden"], [["a", "b"]])
def get_object(self, queryset=None):
obj = super().get_object()
obj = obj.upload
try:
obj = pyexcel.get_book_dict(file_type='xlsx', file_content=obj.read())
except Exception:
raise Http404('Некорректный загружаемый файл')
return obj
def test_get_book_dict(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update({"Sheet3": [[u"X", u"Y", u"Z"], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls")
assert adict == content
def test_issue_8_hidden_sheet_2():
test_file = get_fixtures("hidden_sheets.xlsx")
book_dict = pe.get_book_dict(file_name=test_file,
skip_hidden_sheets=False,
library="pyexcel-xlsx")
assert "hidden" in book_dict
eq_(book_dict['shown'], [['A', 'B']])
eq_(book_dict['hidden'], [['a', 'b']])
def test_get_book_from_sql(self):
book_dict = pe.get_book_dict(session=Session(),
tables=[Signature, Signature2])
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update(
{'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def get_book_dict(self, **keywords):
"""Get a dictionary of two dimensional array from the file
:param keywords: additional key words
:returns: A dictionary of two dimensional arrays
"""
params = self.get_params(**keywords)
return pe.get_book_dict(**params)
def test_get_book_from_sql(self):
book_dict = pe.get_book_dict(session=Session(),
tables=[Signature, Signature2])
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update(
{"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def excel_load(file_path: str):
import pyexcel
# load workbook
wb = pyexcel.get_book_dict(file_name=file_path)
for name, item in wb.items():
# ensure from array to odict and filter empty values
wb[name] = filteritems(arrtodict(item))
return wb
def excel_to_records(input_path: str) -> Workbook:
"""Reads rows to a excel file at the specified path.
Args:
input_path (str): The path where the excel file will be read.
"""
return pyexcel.get_book_dict(file_name=input_path)
def get_book_dict(self, **keywords):
"""Get a dictionary of two dimensional array from the file
:param keywords: additional key words
:returns: A dictionary of two dimensional arrays
"""
params = self.get_params(**keywords)
return pe.get_book_dict(**params)
def get_first_sheet_name(file_path: str):
"""
This function returns the first sheet name of the excel file
:param file_path:
:return:
"""
book_dict = pyexcel.get_book_dict(file_name=file_path)
for sheet in book_dict.keys():
return sheet
def test_get_book_dict(self):
content = OrderedDict()
content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
content.update(
{"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls")
assert adict == content
def get_list(xlsxPath, sheetname, startrow):
single_dict = p.get_book_dict(file_name=xlsxPath,
sheet_name=sheetname,
start_row=startrow,
skip_empty_rows=True)
row_list = single_dict[sheetname]
# for key,item in single_dict.items():
# print(json.dumps(item, ensure_ascii=False, sort_keys=False, indent=1))
return row_list
def test_force_file_type_for_save_book_as(self):
pe.save_as(
bookdict={"sheet1": [[1, 2]]},
dest_file_name="a.bin",
dest_force_file_type="xls",
)
actual = pe.get_book_dict(file_name="a.bin", force_file_type="xls")
eq_({"sheet1": [[1, 2]]}, actual)
os.unlink("a.bin")
def showfile(request, file_name):
fs = FileSystemStorage('loanboard/files')
file_path=fs.path('')+'/'+file_name
fl=pyexcel.get_book_dict(file_name=file_path)
data=None
for sheet in fl:
if isinstance(fl[sheet], list):
data=fl[sheet]
return render(request, 'lbfile.html', {'data':data, 'filename': file_name})
def test_save_book_as_file_from_sql(self):
test_file = "book_from_sql.xls"
pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def loadExcelInDict():
import pyexcel
from pyexcel._compact import OrderedDict
file = 'C:\\Users\\r103co62\\Desktop\\Template.xlsx'
# Get your data in an ordered dictionary of lists
my_dict = pyexcel.get_dict(file_name=file, name_columns_by_row=0)
# Get your data in a dictionary of 2D arrays
book_dict = pyexcel.get_book_dict(file_name=file)
# Retrieve the records of the file
records = pyexcel.get_records(file_name=file)
def test_should_convert_json_to_excel(self):
with open(
os.path.join(self.test_data,
'household_without_fieldset_MSI_expected.json'),
'r') as input_json_file:
input_json = json.load(input_json_file,
object_pairs_hook=OrderedDict)
expected_book_dict = pe.get_book_dict(file_name=os.path.join(
self.test_data, "household_without_fieldset_MSI.xlsx"))
actual_excel_output = convert_json_to_excel(input_json)
# io.BufferedRandom()
# actual_excel_output.read = lambda n=0 : actual_excel_output.getvalue()
# actual_excel_output.readable = lambda: True
#
# bufferedReader = io.BufferedReader(actual_excel_output)
# self.assertIsNotNone(bufferedReader)
actual_book_dict = pe.get_book_dict(
file_type='xlsx', file_content=actual_excel_output)
self.assertEqual(actual_book_dict, expected_book_dict)
def test_save_book_as_file_from_sql(self):
test_file="book_from_sql.xls"
pe.save_book_as(out_file=test_file,
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def test_save_book_to_memory_from_sql(self):
test_file = pe.save_book_as(dest_file_type="xls",
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_content=test_file.getvalue(),
file_type="xls")
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update(
{'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def test_save_book_to_memory_from_sql(self):
test_file = pe.save_book_as(dest_file_type="xls",
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(
file_content=test_file.getvalue(),
file_type="xls"
)
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def test_save_book_as_file_from_sql(self):
test_file = "book_from_sql.xls"
pe.save_book_as(dest_file_name=test_file,
session=Session(),
tables=[Signature, Signature2])
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
expected.update(
{'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def read_excel_sheets(sheet_name=None, file_url=None, all_sheets=False):
#if True:
try:
book_dict = pyexcel.get_book_dict(file_name=file_url)
if all_sheets:
dict_all_sheets = {}
for name_sheet in book_dict:
records = book_dict[name_sheet]
if records:
header = records.pop(0)
else:
header = []
try:
header = [
str(col).lower().replace(u'\xa0',
u' ').strip().replace(
' ', '_')
for col in header
]
except UnicodeEncodeError:
header = [
col.lower().replace(u'\xa0',
u' ').strip().replace(' ', '_')
for col in header
]
dict_all_sheets[name_sheet.lower().replace(' ', '_')] = {
'header': header,
'records': records
}
return dict_all_sheets
if book_dict.get(sheet_name):
records = book_dict[sheet_name]
header = records.pop(0)
try:
header = [
str(col).lower().replace(u'\xa0',
u' ').strip().replace(' ', '_')
for col in header
]
except UnicodeEncodeError:
header = [
col.lower().replace(u'\xa0',
u' ').strip().replace(' ', '_')
for col in header
]
return header, records
except Exception as e:
print(
json.dumps({
'error': 'Ocurrio un error al leer el documento Excel',
'msg': str(e)
}))
def test_save_book_to_memory_from_sql(self):
test_file = pe.save_book_as(
dest_file_type="xls",
session=Session(),
tables=[Signature, Signature2],
)
book_dict = pe.get_book_dict(file_content=test_file.getvalue(),
file_type="xls")
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update(
{"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def test_save(in_file, config, request):
if isinstance(config, str):
config = Path("tests/input").joinpath(config)
assert config.exists()
config = str(config)
data = pyexcel.get_book_dict(file_name=str(Path("tests/input").joinpath(in_file)))
pyexcel_xlsxwx.save_data(
str(Path("tests/output").joinpath(request.node.name).with_suffix(".xlsx")),
data,
config=config,
)
def add_annul_propusk_to_db(file, request):
print('add_annul_propusk_to_db')
our_annul_propuska = []
annul = pyexcel.get_book_dict(file_name='workstation/static/fileupload/' + file.name)
step = 0
print('Взяли файл')
print(len(annul['Sheet']))
for row in annul['Sheet']:
print("Проверенно {} строк из {}".format(step, len(annul['Sheet'])))
if row[1] != '':
car = obj_search_in_db_car(what=row[1])
if car:
# Если машина есть
if car.is_our:
propusk = {'grz': row[1], 'date_from': row[2], 'date_to': row[3], 'status': row[4],
'proverka': datetime.today().strftime("%Y-%m-%d")}
print('Машина наша')
our_annul_propuska.append(propusk)
prop = Propusk.objects.filter(car=car)
if not prop:
print("Нет пропуска на " + car.grz)
date_from = datetime.strptime(row[2], "%d.%m.%Y").date().strftime("%Y-%m-%d")
date_to = datetime.strptime(row[3], "%d.%m.%Y").date().strftime("%Y-%m-%d")
propusk = Propusk(car=car, date_from=date_from, date_to=date_to, status=row[4])
propusk.save()
else:
#Если машина есть но не наша
print('Машина не наша')
date_from = datetime.strptime(row[2], "%d.%m.%Y").date().strftime("%Y-%m-%d")
date_to = datetime.strptime(row[3], "%d.%m.%Y").date().strftime("%Y-%m-%d")
propusk = Propusk(car=car, date_from=date_from, date_to=date_to, status=row[4])
propusk.save()
else:
# Если машины нет
car = Car(user_id=request.user, grz=row[1], is_our=False)
car.save()
date_from = datetime.strptime(row[2], "%d.%m.%Y").date().strftime("%Y-%m-%d")
date_to = datetime.strptime(row[3], "%d.%m.%Y").date().strftime("%Y-%m-%d")
propusk = Propusk(car=car, date_from=date_from, date_to=date_to, status=row[4])
propusk.save()
else:
# Если пустая ячейка
print('пустая ячейка')
step += 1
print("Записанно в propuska {}".format(len(our_annul_propuska)))
save_to_file_annul(our_annul_propuska)
return our_annul_propuska
def excel_to_json(file_path: str, sheet_name: str = None) -> str:
"""
This function reads the excel file and converts it to JSON
:param file_path:
:param sheet_name:
:return:
"""
book_dict = pyexcel.get_book_dict(file_name=file_path)
sheet_data = {
'columnDefs': [{
'headerName': "",
'field': "^",
'pinned': "left"
}],
'rowData': []
}
column_index_map = {}
file_path = file_path.lower()
is_first_excel = (
False,
True)[(file_path.endswith(".xls") or file_path.endswith(".xlsx"))
and (sheet_name == None)]
result = dict()
if not sheet_name:
result['sheetNames'] = list()
for sheet in book_dict.keys():
result['sheetNames'].append(sheet)
sheet_name = result['sheetNames'][0]
sheet = book_dict[sheet_name]
for i in range(len(sheet[0])):
column = get_column_letter(i + 1)
column_index_map[i + 1] = column
sheet_data['columnDefs'].append({
'headerName': column_index_map[i + 1],
'field': column_index_map[i + 1]
})
for row in range(len(sheet)):
r = {'^': str(row + 1)}
for col in range(len(sheet[row])):
r[column_index_map[col + 1]] = str(sheet[row][col]).strip()
sheet_data['rowData'].append(r)
if is_first_excel:
result['sheetData'] = dict()
result['sheetData'][sheet_name] = sheet_data
return result
else:
return sheet_data
def test_save_book_as_file_from_sql(self):
test_file = "book_from_sql.xls"
pe.save_book_as(
dest_file_name=test_file,
session=Session(),
tables=[Signature, Signature2],
)
book_dict = pe.get_book_dict(file_name=test_file)
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update(
{"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
os.unlink(test_file)
def main(mdd_path):
book = pyexcel.get_book_dict(file_name=os.fspath(mdd_path), skip_empty_rows=True)
# Encontrar para cada sheetname un fichero llamado FECHA_sheetname.shp coincidiendo mayúsculas, minúsculas y todo. Pensar si tiene sentido ignorar caracteres especiales.
# No estaría de más poder sacar un listado de todos los .shp en el root_path por esto de comparar con los sheetnames
# Chequear los nombres de campos y tipos del mdd frente a los de las capas
# Generar tablas a partir del mdd/capas
for b in book:
if b in ['Fontes', 'Barragens', 'Loc_Prov', 'Loc_Dis', 'Loc_PosAdm']: continue
# Convertir a rutas absolutas sin necesidad de iniciar la búsqueda con una ruta relativa
tablename = b.lower()
shp_path = find_all_by_pattern('*' + b + '.shp', './08_BD_Unica/01_BD_Cartografia_Base')[0]
compare_fields(book, b, shp_path)
encoding = calculate_encoding.main(shp_path)
command='shp2pgsql -p -I -s 32737 -g geom -W {encoding} -N abort {shp_path} cbase.{tablename} >> /tmp/foo.sql'.format(tablename=tablename, shp_path=shp_path, encoding=encoding)
os.system(command)
command='shp2pgsql -a -D -s 32737 -g geom -W {encoding} -N abort {shp_path} cbase.{tablename} >> /tmp/foo_data.sql'.format(tablename=tablename, shp_path=shp_path, encoding=encoding)
os.system(command)
rewrite_shp2pgsql_ddl_output('/tmp/foo.sql')
rewrite_shp2pgsql_data_output('/tmp/foo_data.sql')
import pyexcel
import scipy.optimize as opt
import numpy as np
import xlsxwriter
cafe_average = {'domestic':60, 'Asian': 100, 'European':70}
#book now contains the entire excel workbook
dict = pyexcel.get_book_dict(file_name="Input_Data.xls")
tgroup = np.asarray(dict['tGROUP'])
fgroup = np.asarray(dict['fGROUP'])
bgroup = np.asarray(dict['bGROUP'])
scenario = np.asarray(dict['Scenario'])
book = pyexcel.get_book(file_name="Input_Data.xls")
startYear = 2015
endYear = 2017
num_of_iterations = 50
gamma = 0.1
#order by row 0
temp = book['Scenario_2']
temp.name_columns_by_row(0)
scenario_rec = temp.to_records()
def calculate_shares(scenario_rec):
#s0 = sum of each sj
s0 = 0.0
for eachrec in scenario_rec:
s0+=eachrec['sj']
TempNewNamePath = os.path.join(dstroot,tempName)
os.rename(TempOldNamePath,TempNewNamePath)
#pathstring="C:\Python2.7.10\project_database\excel_database"
pathstring='C:\temp_read_Excel'
filepath_data=unicode(pathinfile)
print filepath_data, "database path"
FileNamestring=name
print dstroot,tempName, "read"
os.chdir(dstroot)
print os.getcwd()
while True:
try:
book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot)
break
except ValueError:
os.remove(TempNewNamePath)
print "Oops! That was no valid number. Try again..."
#book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot)
#isinstance(book_dict, OrderedDict)
con = lite.connect('database.db')
with con:
cur=con.cursor()
sheetnum=0
for key, val in book_dict.items():
#sheetName_ID
sheetnum+=1
print(key)
sql_datacheck="SELECT * FROM excel_file WHERE PATH=? AND FileName=? AND File_SheetName=?"
def test_get_book_dict(self):
pe.get_book_dict(x="something")
def test_get_book_dict(self):
content = _produce_ordered_dict()
io = pe.save_book_as(dest_file_type="xls", bookdict=content)
adict = pe.get_book_dict(file_content=io.getvalue(), file_type="xls")
assert adict == content
def test_issue_9_hidden_sheet():
test_file = get_fixture("hidden_sheets.xls")
book_dict = pe.get_book_dict(file_name=test_file)
assert "hidden" not in book_dict
eq_(book_dict['shown'], [['A', 'B']])
def WriteInDatabase(pathinfile,name,tempName):
dstroot ='C:\\temp_read_Excel'
pathstring='C:\temp_read_Excel'
filepath_data=unicode(pathinfile)
FileNamestring=name.decode("BIG5")
TempNewNamePath = os.path.join(dstroot,tempName)
os.chdir(dstroot)
print os.getcwd()
while True:
try:
book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot)
break
except ValueError:
os.remove(TempNewNamePath)
print "Oops! That was no valid number. Try again..."
#book_dict = pyexcel.get_book_dict(file_name=tempName, path=dstroot)
#isinstance(book_dict, OrderedDict)
con = lite.connect('database.db')
with con:
cur=con.cursor()
sheetnum=0
for key, val in book_dict.items():
#sheetName_ID
sheetnum+=1
sql_datacheck="SELECT * FROM excel_file WHERE PATH=? AND FileName=? AND File_SheetName=?"
sql_pathnamesheet="INSERT INTO excel_file(PATH,FileName,File_SheetName,File_Datetime) VALUES(?,?,?,?)"
now = datetime.datetime.now()
PathNameSheet_para=[filepath_data,FileNamestring,key,now]
fileCheck_Para=[filepath_data,FileNamestring,key]
for checker in con.execute(sql_datacheck, fileCheck_Para):
rownum=0
for valrow in val:
rownum+=1
colnum=0
for valcol in valrow:
colnum+=1
if valcol!='':
sql_check="SELECT * FROM excel_content WHERE Sheet_Name=? and Row_Number=? and Col_Number =?"
sql_update="UPDATE excel_content SET Cell_Data=? WHERE Sheet_Name=? and Row_Number=? and Col_Number =?"
sql_insert="INSERT INTO excel_content(Sheet_Name,Row_Number,Col_Number,Cell_Data) VALUES(?,?,?,?)"
content_check_para=[checker[0],rownum,colnum]
insert_para=[checker[0],rownum,colnum,valcol]
update_para=[valcol,checker[0],rownum,colnum]
#cell exist
for celldataval in con.execute(sql_check, content_check_para):
con.execute(sql_update, update_para)
break
else:
con.execute(sql_insert, insert_para)
#os.remove(TempNewNamePath)
break
else:
con.execute(sql_pathnamesheet, PathNameSheet_para)
for thisID in con.execute(sql_datacheck, fileCheck_Para):
rownum=0
for valrow in val:
rownum+=1
colnum=0
for valcol in valrow:
colnum+=1
if valcol!='':
sql_check="SELECT * from excel_content"
sql_insert="INSERT INTO excel_content(Sheet_Name,Row_Number,Col_Number,Cell_Data) VALUES(?,?,?,?)"
insert_para=[thisID[0],rownum,colnum,valcol]
con.execute(sql_insert, insert_para)
def test_get_book_from_sql(self):
book_dict = pe.get_book_dict(session=Session(), tables=[Signature, Signature2])
expected = OrderedDict()
expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
assert book_dict == expected
def test_get_book_dict(self):
expected = pe.get_book_dict(x="something")
assert expected == None
