def testSheets(self):
workbook = Workbook(self.filename)
assert len(workbook) > 0, "No worksheets found in %s"%self.filename
# assert each index and name
for index in range(len(workbook)):
assert workbook[index] != None, "Missing worksheet at index %d"%index
sheet = workbook[index]
assert sheet.id == workbook[sheet.name].id, "No name reference for sheet %s at %d"%(sheet.name, index)
assert sheet.name in workbook.keys()
def getXlsxSheetList(filename):
workbook = Workbook(filename)
sheetList = []
for sheet in workbook:
rows = sheet.rows()
row_items = rows.items()
rowsList = []
for row, cells in row_items:
cellsList = []
length = len(cells)
for i, cell in enumerate(cells):
cellsList.append(cell)
pass
i = 0
for cell in cellsList:
value = cell.value.strip(' ')
i += len(value)
pass
if i == 0:
continue
pass
rowsList.append(cellsList)
pass
sheetList.append((sheet.name, rowsList))
pass
return sheetList
pass
def getXlsxData(filename):
workbook = Workbook(filename)
rowsList = []
rows = workbook[1].rows()
row_items = rows.items()
#row_items.sort()
for row, cells in row_items:
cellsList = []
length = len(cells)
for i, cell in enumerate(cells):
if cell.value is None:
continue
pass
cell_value = cell.value.strip()
if cell_value != "":
cellsList.append(cell_value)
pass
pass
rowsList.append(cellsList)
pass
return rowsList
pass
def setUp(self):
""" Getting all file from fixtures dir """
self.workbooks = {}
fixtures_dir = os.path.abspath(
os.path.join(os.path.dirname(__file__), 'fixtures'))
xlsx_files = os.listdir(fixtures_dir)
for filename in xlsx_files:
filepath = os.path.join(fixtures_dir, filename)
self.workbooks[filename] = Workbook(open(filepath, 'rb'))
def excel_to_rows(excel_bytes):
book = Workbook(excel_bytes)
# load all sheets in document
for sheet in book:
# load all rows from sheet
rows = []
for row_number, cells in sheet.rowsIter():
rows.append(map(lambda cell: cell.value or cell.formula, cells))
yield sheet.name, rows
def excel_to_rows(excel_file, sheet=None):
excel_book = Workbook(excel_file)
# load all sheets in document
for excel_sheet in excel_book:
if sheet is None or sheet == excel_sheet.name:
# load all rows from excel_sheet
rows = []
for row_number, cells in excel_sheet.rowsIter():
rows.append(map(lambda cell: cell.value or cell.formula, cells))
yield excel_sheet.name, rows
def upload_sheet(name):
book = Workbook("input_data/%s.xlsx" %name)
for sheet in book:
worksheet = sh.add_worksheet(title=name, rows=str(len(sheet.rows())), cols=str(len(sheet.cols())))
row_count = 0
for row, cells in sheet.rows().iteritems():
row_count += 1
col_count = 0
for cell in cells:
col_count += 1
if cell.value == "":
continue
worksheet.update_cell(row_count, col_count, cell.value)
print "%s is successfully uploaded" %name
def post(self, request, *args, **kwargs):
# post -
context = self.get_context_data(**kwargs) # prepare context data (kwargs from URL)
template_name = "nmap/import_result.html"
import_file = request.FILES['file']
if import_file:
book = Workbook(import_file) #Open xlsx file
sheets = []
for sheet in book:
print sheet.name
sheets.append(sheet)
content = import_file.read()
context.update({"sheets": sheets})
return render(request, template_name, context)
else:
raise Exception("nmap_import failed")
def upload_sheet(name):
book = Workbook("input_data/%s.xlsx" % name)
for sheet in book:
worksheet = sh.add_worksheet(title=name,
rows=str(len(sheet.rows())),
cols=str(len(sheet.cols()) + 2))
row_count = 1
session_flag = False
for row, cells in sheet.rows().iteritems():
row_count += 1 if not session_flag else 0
col_count = 2
for cell in cells:
if session_flag:
session_flag = False
if cell.value.find("Session chair") != -1:
worksheet.update_cell(
row_count, 3, cell.value.strip("Session chair:"))
break
else:
row_count += 1
try:
if col_count == 2 and cell.value.find("SESSION") != -1:
index = cell.value.find("SESSION")
worksheet.update_cell(row_count, 1,
cell.value[:index].lower())
worksheet.update_cell(
row_count, 2, string.capwords(cell.value[index:]))
session_flag = True
break
else:
worksheet.update_cell(row_count, col_count, cell.value)
col_count += 1
except:
worksheet.update_cell(row_count, col_count, "Error")
col_count += 1
print "%s is successfully uploaded" % name
def setUp(self):
fixtures_dir = os.path.abspath(
os.path.join(os.path.dirname(__file__), 'fixtures'))
self.workbook = Workbook(os.path.join(fixtures_dir,
'richtextrun.xlsx'))
return
def excel_to_sheets(excel_file):
excel_book = Workbook(excel_file)
for excel_sheet in excel_book:
yield excel_sheet.name
def createDatabaseORM(xlsxFilename, paramsFilename, name):
workbook = Workbook(xlsxFilename)
rows = workbook[1].rows()
if len(rows) == 0:
for page in workbook:
pagerows = page.rows()
pagerows_items = pagerows.items()
if len(pagerows_items) != 0:
msg = "xlsx %s empty first page %s but have not empty other page %s" % (
xlsxFilename, workbook[1].id, page.id)
print(msg)
return
pass
pass
pass
row_items = rows.items()
indent = " "
result = ""
result += "from GOAP2.Database import Database\n"
result += "\n"
result += "class Database%s(Database):\n" % (name)
result += " def __init__(self):\n"
result += " super(Database%s, self).__init__()\n" % (name)
def makeRecordsValueORM(bb_cell, bb_ord, cells, legend_cells):
DatabaseParams = []
DatabaseContract = {}
DatabaseContractHas = False
for legend in legend_cells:
true_cell = [
cell for cell in cells if cell.column == legend.column
]
legend_value = legend.value.strip()
splitKey = legend_value.split('.')
if len(splitKey) > 1:
DatabaseContractHas = True
if len(true_cell) == 0:
continue
pass
keyType = splitKey[0]
keyResource = splitKey[1]
contract = DatabaseContract
for key in splitKey[:-1]:
if key not in contract:
contract[key] = {}
pass
contract = contract[key]
pass
lastKey = splitKey[-1]
cell = true_cell[0]
cell_value = cell.value.strip()
if cell_value == "":
continue
pass
printValue = getPrintValue(cell_value)
contract[lastKey] = printValue
pass
elif legend_value[0] == "[":
if len(true_cell) == 0:
params = "[]"
DatabaseParams.append(params)
continue
pass
true_cell = true_cell[0]
index2 = cells.index(true_cell)
for_cells = cells[index2:]
printValue = ""
for_comma = False
for for_cell in for_cells:
for_cell_value = for_cell.value.strip()
if for_cell_value == "":
break
pass
if for_comma is True:
printValue += ", "
pass
else:
for_comma = True
pass
printValue += getPrintValue(for_cell_value)
pass
params = "[%s]" % (printValue)
DatabaseParams.append(params)
break
pass
elif legend_value[0] == "!":
cell_value = "" if len(
true_cell) == 0 else true_cell[0].value.strip()
#printValue = getPrintValue(cell_value)
if cell_value == "0":
params = "False"
pass
else:
params = "True"
pass
DatabaseParams.append(params)
pass
elif legend_value[0] == "?":
cell_value = "" if len(
true_cell) == 0 else true_cell[0].value.strip()
#printValue = getPrintValue(cell_value)
if cell_value == "1":
params = "True"
pass
else:
params = "False"
pass
DatabaseParams.append(params)
pass
else:
cell_value = "" if len(
true_cell) == 0 else true_cell[0].value.strip()
printValue = getPrintValue(cell_value)
params = "%s" % (printValue)
DatabaseParams.append(params)
pass
pass
if len(DatabaseParams) == 0 and len(DatabaseContract) == 0:
if DatabaseContractHas is True:
return "{}"
pass
return None
pass
printRecords = ""
comma = False
for value in DatabaseParams:
if comma is True:
printRecords += ", "
pass
else:
comma = True
pass
printRecords += value
pass
if len(DatabaseContract) == 0:
if DatabaseContractHas is True:
if len(DatabaseParams) != 0:
printRecords += ", "
pass
printRecords += "{}"
pass
return printRecords
pass
if len(DatabaseParams) != 0:
printRecords += ", "
pass
printRecords += getPrintDictValue(DatabaseContract)
return printRecords
pass
def makeRecordsKeyORM(legend_cells):
DatabaseParams = []
DatabaseContract = []
for cell in legend_cells:
cell_value = cell.value.strip()
splitKey = cell_value.split('.')
if len(splitKey) > 1:
keyType = splitKey[0]
if keyType in DatabaseContract:
continue
pass
DatabaseContract.append(keyType)
pass
elif cell_value[0] == "[":
param = cell_value[1:-1]
DatabaseParams.append(param)
break
pass
elif cell_value[0] == "!":
param = cell_value[1:]
DatabaseParams.append(param)
pass
elif cell_value[0] == "?":
param = cell_value[1:]
DatabaseParams.append(param)
pass
else:
DatabaseParams.append(cell_value)
pass
pass
if len(DatabaseParams) == 0 and len(DatabaseContract) == 0:
return None
pass
return [(param, None)
for param in DatabaseParams] + [(param, {})
for param in DatabaseContract]
pass
rows = []
for row_index, row_zip in enumerate(row_items):
row_empty = True
for cell in row_zip[1]:
try:
cell_value = cell.value.strip()
except AttributeError as ex:
print("row %s - %s %s %s invalid error %s" %
(row_index, xlsxFilename, paramsFilename, name,
cell.value))
continue
pass
if len(cell_value) != 0:
row_empty = False
pass
pass
if row_empty is True:
continue
pass
rows.append(row_zip)
pass
if len(rows) != 0:
rows.sort(key=lambda x: x[0])
legend_row, legend_cells = rows.pop(0)
bb_cell = None
bb_ord = None
for row, cells in rows:
if bb_cell is None:
for cell in cells:
cell_value = cell.value.strip()
if cell_value == "":
continue
bb_ord = cell.column
bb_cell = getCellIndex(bb_ord)
break
pass
pass
pass
legend_cells = [
cell for cell in legend_cells if cell.value.strip() != ""
]
legend_keys = makeRecordsKeyORM(legend_cells)
result += " class Record%s(object):\n" % (name)
result += " def __init__(self"
for key, default in legend_keys:
if default is None:
result += ", %s" % (key)
pass
else:
result += ", %s = %s" % (key, default)
pass
pass
result += "):\n"
for key, default in legend_keys:
result += " self.%s = %s\n" % (key, key)
pass
result += " pass\n"
result += " pass\n"
result += "\n"
for row, cells in rows:
#print("row_index, row %s:%s"%(row_index, row))
#result += indent
cell_skip = False
for cell in cells:
value = cell.value.strip()
if value != "":
if value == "#":
cell_skip = True
pass
else:
break
pass
pass
pass
if cell_skip is True:
continue
pass
printRecords = makeRecordsValueORM(bb_cell, bb_ord, cells,
legend_cells)
if printRecords is None:
continue
pass
result += " self.addORM(Record%s(%s))\n" % (name,
printRecords)
pass
pass
result += " pass\n"
result += " pass\n"
with codecs.open(paramsFilename, "w", "utf-8") as f:
f.write(result)
pass
pass
def createDatabase2(xlsxFilename, paramsFilename, name):
workbook = Workbook(xlsxFilename)
rows = workbook[1].rows()
if len(rows) == 0:
for page in workbook:
pagerows = page.rows()
pagerows_items = pagerows.items()
if len(pagerows_items) != 0:
msg = "xlsx %s empty first page %s but have not empty other page %s" % (
xlsxFilename, workbook[1].id, page.id)
print(msg)
return
pass
pass
pass
row_items = rows.items()
indent = " "
result = ""
result += "from GOAP2.Database import Database\n"
result += "\n"
result += "class Database%s(Database):\n" % (name)
result += " def __init__(self):\n"
result += " super(Database%s, self).__init__()\n" % (name)
def makeRecordsValueORM(bb_cell, bb_ord, cells, legend_cells):
DatabaseParams = []
for legend in legend_cells:
true_cell = [
cell for cell in cells if cell.column == legend.column
]
legend_value = legend.value.strip()
if legend_value[0] == "[":
if len(true_cell) == 0:
params = "[]"
DatabaseParams.append(params)
continue
pass
true_cell = true_cell[0]
index2 = cells.index(true_cell)
for_cells = cells[index2:]
printValue = ""
for_comma = False
for for_cell in for_cells:
for_cell_value = for_cell.value.strip()
if for_cell_value == "":
break
pass
if for_comma is True:
printValue += ", "
pass
else:
for_comma = True
pass
printValue += getPrintValue(for_cell_value)
pass
if printValue != "":
params = "%s = [%s]" % (legend_value[1:-1], printValue)
DatabaseParams.append(params)
pass
break
pass
else:
cell = None if len(true_cell) == 0 else true_cell[0]
if cell is None:
continue
pass
if cell.value is None:
continue
pass
cell_value = cell.value.strip()
if cell.value == "":
# Empty string
continue
pass
printValue = getPrintValue(cell_value)
params = "%s = %s" % (legend_value, printValue)
DatabaseParams.append(params)
pass
pass
if len(DatabaseParams) == 0:
return None
pass
printRecords = ""
comma = False
for value in DatabaseParams:
if comma is True:
printRecords += ", "
pass
else:
comma = True
pass
printRecords += value
pass
return printRecords
pass
rows = []
for row_index, row_zip in enumerate(row_items):
row_empty = True
for cell in row_zip[1]:
if cell.value is None:
continue
pass
cell_value = cell.value.strip()
if len(cell_value) != 0:
row_empty = False
pass
pass
if row_empty is True:
continue
pass
rows.append(row_zip)
pass
if len(rows) != 0:
rows.sort(key=lambda x: x[0])
legend_row, legend_cells = rows.pop(0)
bb_cell = None
bb_ord = None
for row, cells in rows:
if bb_cell is None:
for cell in cells:
cell_value = cell.value.strip()
if cell_value == "":
continue
pass
bb_ord = cell.column
bb_cell = getCellIndex(bb_ord)
break
pass
pass
pass
legend_cells = [
cell for cell in legend_cells if cell.value.strip() != ""
]
for row, cells in rows:
#print("row_index, row %s:%s"%(row_index, row))
#result += indent
cell_skip = False
for cell in cells:
value = cell.value.strip()
if value != "":
if value == "#":
cell_skip = True
pass
else:
break
pass
pass
pass
if cell_skip is True:
continue
pass
printRecords = makeRecordsValueORM(bb_cell, bb_ord, cells,
legend_cells)
if printRecords is None:
continue
pass
result += " self.addRecord(%s)\n" % (printRecords)
pass
pass
result += " pass\n"
result += " pass\n"
with codecs.open(paramsFilename, "w", "utf-8") as f:
f.write(result)
pass
pass
def createScenario(xlsxFilename, generateFilename, name):
workbook = Workbook(xlsxFilename)
rows = workbook[1].rows()
row_items = rows.items()
indent = " "
result = ""
result += "from GOAP2.Scenario import Scenario\n"
result += "\n"
result += "class Scenario%s(Scenario):\n" % (name)
result += " def __init__(self):\n"
result += " super(Scenario%s, self).__init__()\n" % (name)
result += " pass\n"
result += "\n"
result += " def _onInitialize(self):\n"
# bb_cell = None
# bb_ord = None
rows = []
for row_index, row_zip in enumerate(row_items):
rows.append(row_zip)
pass
if len(rows) != 0:
rows.sort(key=lambda x: x[0])
row, cells = rows.pop(0)
desc_cell = cells[0]
bb_cell = getCellIndex(desc_cell.column)
state_0 = "None"
state_1 = "None"
state_2 = "None"
macro_count = None
macro_id = None
for row_index, (row, cells) in enumerate(rows):
# print("row_index, row %s:%s"%(row_index, row))
printValue = ""
for cell_index in range(32):
# print ("cell_index %d"%(cell_index))
cell = findCell(cell_index, bb_cell, cells)
cell_value = ""
if cell is not None:
cell_value = cell.value.strip()
# print ("%s:%s = %s"%(row, cell.column, cell_value))
pass
if cell_index < 0:
# print("cell_index column - %d:%s - %d:%s"%(bb_cell, bb_ord, cell_index, cell.column))
continue
# print ("state %s %s %s"%(state_0, state_1, state_2))
if cell_index == 0:
if cell_value == "Paragraph":
if state_0 != "None":
result += " pass\n"
pass
state_0 = "Paragraph"
state_1 = "Init"
state_2 = "None"
elif cell_value == "Repeat":
if state_0 != "None":
result += " pass\n"
pass
state_0 = "Repeat"
state_1 = "Init"
state_2 = "None"
pass
elif cell_value == "Until":
if state_0 != "None":
result += " pass\n"
pass
state_0 = "Until"
state_1 = "Init"
state_2 = "None"
pass
elif cell_value == "#":
break
pass
elif len(cell_value) >= 1 and cell_value[0] == "#":
break
pass
elif cell_value == "":
pass
else:
Error(
"Invalid Scenario '%s' first level command '%s' is not supported [%s]"
% (xlsxFilename, cell_value,
["Paragraph", "Repeat", "Until"]))
return False
pass
if cell_index == 1:
if state_1 == "Init":
if state_0 == "Paragraph":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 1)
elif state_0 == "Repeat":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 1)
elif state_0 == "Until":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 1)
pass
elif state_1 == "None":
if cell_value == "Preparation":
state_1 = "Preparation"
state_2 = "Macro"
elif cell_value == "Initial":
state_1 = "Initial"
state_2 = "Macro"
elif cell_value == "Parallel":
state_1 = "Parallel"
state_2 = "Init"
elif cell_value == "Race":
state_1 = "Race"
state_2 = "Init"
elif cell_value == "ShiftCollect":
state_1 = "ShiftCollect"
state_2 = "Init"
elif cell_value == "":
state_1 = "Skip"
break
else:
state_1 = "Active"
state_2 = "Macro"
pass
elif state_1 in ["Parallel", "Race", "ShiftCollect"]:
if cell_value.isdigit() is True:
state_2 = "Macro"
macro_id = int(cell_value)
pass
elif cell_value == "":
state_2 = "Skip"
pass
else:
if cell_value in [
"Parallel", "Race", "ShiftCollect"
]:
state_1 = cell_value
state_2 = "Init"
else:
state_1 = "Active"
state_2 = "Macro"
pass
pass
else:
pass
if state_1 == "Active":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 1)
pass
pass
if cell_index > 1:
if state_1 == "Init":
if state_0 == "Paragraph":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 1)
pass
if state_1 == "Active":
if state_2 == "Macro":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 1)
pass
pass
else:
if state_2 == "Macro":
if cell_value == "":
break
printValue += writeValue(cell_value, cell_index, 2)
pass
pass
pass
if cell_index == 2:
if state_1 in ["Parallel", "Race", "ShiftCollect"]:
if state_2 == "Init":
try:
macro_count = int(cell_value)
except ValueError:
Error("Invalid %s Count %s" %
(state_1, cell_value))
return False
pass
pass
pass
pass
pass
# print ("state_0 %s %s %s %s"%(state_0, state_1, state_2, printValue))
if state_0 == "Paragraph":
if state_1 == "Init":
result += " with self.addParagraph(%d, %s) as paragraph:\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Preparation":
result += " paragraph.addPreparation(%d, %s)\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Initial":
result += " paragraph.addInitial(%d, %s)\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Active":
result += " paragraph.addActive(%d, %s)\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Skip":
state_1 = "None"
pass
elif state_1 == "Parallel":
if state_2 == "Init":
result += " with paragraph.addParallel(%d, %d) as parallel:\n" % (
row_index, macro_count)
state_2 = "None"
pass
elif state_2 == "Macro":
result += " parallel.addActive(%d, %d, %s)\n" % (
row_index, macro_id - 1, printValue)
state_2 = "None"
pass
pass
elif state_1 == "Race":
if state_2 == "Init":
result += " with paragraph.addRace(%d, %d) as race:\n" % (
row_index, macro_count)
state_2 = "None"
pass
elif state_2 == "Macro":
result += " race.addActive(%d, %d, %s)\n" % (
row_index, macro_id - 1, printValue)
state_2 = "None"
pass
pass
elif state_1 == "ShiftCollect":
if state_2 == "Init":
result += " with paragraph.addShiftCollect(%d, %d) as shiftcollect:\n" % (
row_index, macro_count)
state_2 = "None"
pass
elif state_2 == "Macro":
result += " shiftcollect.addActive(%d, %d, %s)\n" % (
row_index, macro_id - 1, printValue)
state_2 = "None"
pass
pass
pass
elif state_0 == "Repeat":
if state_1 == "Init":
result += " with self.addRepeat(%d, %s) as repeat:\n" % (
row_index, printValue)
state_1 = "None"
pass
if state_1 == "Preparation":
result += " repeat.addPreparation(%d, %s)\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Initial":
result += " repeat.addInitial(%d, %s)\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Active":
result += " repeat.addActive(%d, %s)\n" % (
row_index, printValue)
state_1 = "None"
pass
elif state_1 == "Skip":
state_1 = "None"
pass
elif state_1 == "Parallel":
if state_2 == "Init":
result += " with repeat.addParallel(%d, %d) as parallel:\n" % (
row_index, macro_count)
state_2 = "None"
pass
elif state_2 == "Macro":
result += " parallel.addActive(%d, %d, %s)\n" % (
row_index, macro_id - 1, printValue)
state_2 = "None"
pass
pass
elif state_1 == "Race":
if state_2 == "Init":
result += " with repeat.addRace(%d, %d) as race:\n" % (
row_index, macro_count)
state_2 = "None"
pass
elif state_2 == "Macro":
result += " race.addActive(%d, %d, %s)\n" % (
row_index, macro_id - 1, printValue)
state_2 = "None"
pass
pass
elif state_1 == "ShiftCollect":
if state_2 == "Init":
result += " with repeat.addShiftCollect(%d, %d) as shiftcollect:\n" % (
row_index, macro_count)
state_2 = "None"
pass
elif state_2 == "Macro":
result += " shiftcollect.addActive(%d, %d, %s)\n" % (
row_index, macro_id - 1, printValue)
state_2 = "None"
pass
pass
pass
pass
elif state_0 == "Until":
result += " repeat.addUntil(%d, %s)\n" % (
row_index, printValue)
result += " pass\n"
state_0 = "None"
state_1 = "None"
state_2 = "None"
pass
pass
if state_0 != "None":
result += " pass\n"
pass
pass
result += " pass\n"
result += " pass\n"
directory = os.path.dirname(generateFilename)
if not os.path.exists(directory):
os.makedirs(directory)
pass
f = open(generateFilename, "w")
f.write(result)
f.close()
return True
pass