class XLSXOpenExcel(IO):
def read(self, filename):
self.sheet = OpenExcel(filename)
self.row_count = self.sheet.read().nrows
self.col_count = self.sheet.read().ncols
def _float_to_string(self, sheet, col):
values = sheet.read(col)
if values[0] in self.column_names:
return values
for i, value in enumerate(values):
if isinstance(value, float):
values[i] = str(int(value))
elif isinstance(value, unicode):
values[i] = str(value)
return values
def column_heading_letter(self, heading_name):
for letter in self.column_letters():
if self.sheet.read(letter)[0] == heading_name:
return letter
@property
def column_names(self):
return tuple([
col[0] for col in [
self.sheet.sheets.col_values(i)
for i in range(self.sheet.getCols())
]
])
@property
def column_letters(self):
_letters = letters[26:]
num_cols = self.sheet.read().ncols
double_letters, triple_letters = ([], ) * 2
for l in _letters:
for m in _letters:
double_letters += [l + m]
for l in _letters:
for m in _letters:
for n in _letters:
triple_letters += [l + m + n]
_letters = list(_letters) + double_letters + triple_letters
res = zip(_letters, range(1, num_cols))
return tuple([r[0] for r in res])
def analise_cover_search(path):
random_ref = hex(random.randint(1, 4294967296)).upper().strip("0X")
cover_search = OpenExcel(path)
data = cover_search.read()
# Find Table Starts
found_start = False
table_starts = []
sheet_width = cover_search.getCols()
sheet_height = cover_search.getRows()
for c in range(sheet_width):
for r in range(sheet_height):
cell_data = cover_search.read("{}{}".format(ALPHABET[c], r + 1))
if ("SORTIE" in str(cell_data).upper()) and (
"TOTAL" not in str(cell_data).upper()):
found_start = True
table_starts.append((c, r))
if found_start:
break
else:
pass
items = []
for start in table_starts:
headers = [] # To be list of (column, "header text")
current_row = start[1]
data_started = False
while not data_started:
current_row += 1
first_cell_address = "{}{}".format(ALPHABET[start[0]],
current_row + 1)
try:
cell_data = cover_search.read(first_cell_address)
except IndexError:
print("Failed at: {}".format(first_cell_address))
raise
if cell_data.count("/") > 0:
data_started = True
else:
pass
empty_cols = []
for c in range(sheet_width):
cell_data = cover_search.read("{}{}".format(
ALPHABET[c], current_row + 1))
if cell_data == "":
empty_cols.append(c)
else:
headers.append([ALPHABET[c], ""])
for c, h in enumerate(headers):
cell_data = cover_search.read("{}{}".format(h[0], start[1] + 1))
if cell_data == "":
headers[c][1] = "{}:{}".format(random_ref, [c][0])
else:
headers[c][1] = cell_data.strip()
data_started = False
data_finished = False
current_row = start[1] + 1
while not data_finished:
first_cell_address = "{}{}".format(headers[0][0], current_row + 1)
try:
cell_data = cover_search.read(first_cell_address)
except IndexError:
data_finished = True
break
if cell_data.count("/") > 0:
data_started = True
else:
pass
new_item = dict()
for i, h in enumerate(headers):
cell_address = "{}{}".format(h[0], current_row + 1)
cell_data = cover_search.read(cell_address)
if not data_started:
if cell_data != "":
headers[i][1] += " {}".format(cell_data)
else:
pass
else:
if (i == 0) and (cell_data.count("/") < 1):
data_finished = True
break
else:
new_item[h[1]] = cell_data
if new_item == dict():
pass
else:
new_item["originating_file"] = path
items.append(new_item)
current_row += 1
return items