def extractMarkingFromXLS(self, mrkFileName):
'''
Process the current Excel file to extract the marking annotation
and write the output to mrkFileName
'''
outputHandle = bz2.BZ2File(
mrkFileName +
".bz2", 'wb', compresslevel=9) if config.isCompress() else open(
mrkFileName, "w")
for n in range(self.workbook.nsheets):
sheet = self.workbook.sheet_by_index(n)
colns = number_of_good_cols(sheet)
rowns = number_of_good_rows(sheet)
self.log.debug("Process %d columns and %d rows" % (colns, rowns))
for i in range(0, rowns):
for j in range(0, colns):
cell = sheet.cell(i, j)
cell_name = cellname(i, j)
style_name = self.styles[cell].name
# print self.styles[cell].xf
if style_name.startswith('TL '):
mrk_line = "%d;%s;%s" % (n, cell_name, style_name)
outputHandle.write(mrk_line)
outputHandle.write("\n")
outputHandle.close()
def injectMarkingIntoXLS(self, mrkFileName, targetFileName):
'''
Load marking instructions from mrkFileName and process the current
Excel file to generate the annotated targetFileName
'''
# Prepare the output
self.log.debug("Copy book")
target_workbook = copy(self.workbook)
#print Styles(target_workbook)
self.log.debug("Load marking from %s" % mrkFileName)
# Load marking information
marking = {}
for mrk in open(mrkFileName):
(index_str, cell, style) = mrk.strip().split(';')
index = int(index_str)
marking.setdefault(index, {})
marking[index][cell] = style
# Create the styles in the target
styles = {}
for s in [('TL Metadata', 'yellow'), ('TL ColHeader', 'blue'),
('TL Data', 'white'), ('TL RowHeader', 'yellow'),
('TL RowProperty', 'green'), ('TL Title', 'pink'),
('TL RowLabel', 'blue'), ('TL HRowHeader', 'blue')]:
name, colour = s
xf = xlwt.easyxf(
'name: test; pattern: pattern solid, fore_colour %s;' % colour)
styles[name] = xf
print target_workbook.add_style(xf)
#target_workbook.xf_list.append(xf)
#index = len(target_workbook.xf_list)
#target_workbook.style_name_map[name] = (0, index)
# Process the source workbook
for n in range(self.workbook.nsheets):
sheet = self.workbook.sheet_by_index(n)
target_sheet = target_workbook.get_sheet(n)
colns = number_of_good_cols(sheet)
rowns = number_of_good_rows(sheet)
self.log.debug("Process %d columns and %d rows" % (colns, rowns))
for i in range(0, rowns):
for j in range(0, colns):
cell = sheet.cell(i, j)
cell_name = cellname(i, j)
#cell_xf_index = sheet.cell_xf_index(i, j)
#print cell_xf_index
if cell_name in marking[n]:
style = styles[marking[n][cell_name]]
# TODO Use matching style defined earlier
#print target_sheet
target_sheet.write(i, j, label=cell.value, style=style)
target_workbook.save(targetFileName)
def injectMarkingIntoXLS(self, mrkFileName, targetFileName):
'''
Load marking instructions from mrkFileName and process the current
Excel file to generate the annotated targetFileName
'''
# Prepare the output
self.log.debug("Copy book")
target_workbook = copy(self.workbook)
#print Styles(target_workbook)
self.log.debug("Load marking from %s" % mrkFileName)
# Load marking information
marking = {}
for mrk in open(mrkFileName):
(index_str, cell, style) = mrk.strip().split(';')
index = int(index_str)
marking.setdefault(index, {})
marking[index][cell] = style
# Create the styles in the target
styles = {}
for s in [('TL Metadata', 'yellow'), ('TL ColHeader', 'blue'),
('TL Data', 'white'), ('TL RowHeader', 'yellow'),
('TL RowProperty', 'green'), ('TL Title', 'pink'),
('TL RowLabel', 'blue'), ('TL HRowHeader', 'blue')]:
name, colour = s
xf = xlwt.easyxf('name: test; pattern: pattern solid, fore_colour %s;' % colour)
styles[name] = xf
print target_workbook.add_style(xf)
#target_workbook.xf_list.append(xf)
#index = len(target_workbook.xf_list)
#target_workbook.style_name_map[name] = (0, index)
# Process the source workbook
for n in range(self.workbook.nsheets):
sheet = self.workbook.sheet_by_index(n)
target_sheet = target_workbook.get_sheet(n)
colns = number_of_good_cols(sheet)
rowns = number_of_good_rows(sheet)
self.log.debug("Process %d columns and %d rows" % (colns, rowns))
for i in range(0, rowns):
for j in range(0, colns):
cell = sheet.cell(i, j)
cell_name = cellname(i, j)
#cell_xf_index = sheet.cell_xf_index(i, j)
#print cell_xf_index
if cell_name in marking[n]:
style = styles[marking[n][cell_name]]
# TODO Use matching style defined earlier
#print target_sheet
target_sheet.write(i, j, label=cell.value, style=style)
target_workbook.save(targetFileName)
def extractMarkingFromXLS(self, mrkFileName):
'''
Process the current Excel file to extract the marking annotation
and write the output to mrkFileName
'''
outputHandle = bz2.BZ2File(mrkFileName + ".bz2", 'wb', compresslevel=9) if config.isCompress() else open(mrkFileName, "w")
for n in range(self.workbook.nsheets):
sheet = self.workbook.sheet_by_index(n)
colns = number_of_good_cols(sheet)
rowns = number_of_good_rows(sheet)
self.log.debug("Process %d columns and %d rows" % (colns, rowns))
for i in range(0, rowns):
for j in range(0, colns):
cell = sheet.cell(i, j)
cell_name = cellname(i, j)
style_name = self.styles[cell].name
# print self.styles[cell].xf
if style_name.startswith('TL '):
mrk_line = "%d;%s;%s" % (n, cell_name, style_name)
outputHandle.write(mrk_line)
outputHandle.write("\n")
outputHandle.close()
def handle_excel(self):
period = self.context.period
file = period.file
dbsession = self.request.dbsession
owner = self.request.owner
owner_id = owner.id
content = self.content
try:
book = xlrd.open_workbook(file_contents=content)
except EntitiesForbidden:
raise HTTPBadRequest(
json_body={
'error':
'xee_forbidden',
'error_description': (
"Please upload a file with no complex XML entities."),
})
statement = self.add_statement("Spreadsheet")
for sheetx, sheet in enumerate(book.sheets()):
# Look for a heading row.
# The heading must contain at least "date" and "amount".
column_names = None
heading_rowx = -1
for rowx, row in enumerate(sheet.get_rows()):
texts = [str(cell.value).strip().lower() for cell in row]
if 'date' in texts and 'amount' in texts:
# Found the heading.
column_names = tuple(texts)
heading_rowx = rowx
if not column_names:
# No heading row found. Assume default columns.
column_names = ('date', 'amount', 'description')
if 'date' not in column_names or 'amount' not in column_names:
# Skip this sheet.
continue
sheet_name = sheet.name.strip() or str(sheetx + 1)
if sheet_name.lower().startswith('sheet'):
# Remove the redundant word.
sheet_name = sheet_name[5:].strip()
# Parse the sheet and add AccountEntry rows.
for rowx, row in enumerate(sheet.get_rows()):
if rowx <= heading_rowx:
continue
attrs = {
'sheet': sheet_name,
'row': rowx + 1,
}
for colx, cell in enumerate(row):
if not cell.value:
continue
column_name = column_names[colx]
try:
info = self.parse_excel_cell(book, cell, column_name)
except Exception as e:
error_description = (
"Unable to parse %s cell %s on sheet %s. "
"Cell contents: '%s', error: %s, %s" % (
column_name,
cellname(rowx, colx),
sheet_name,
cell.value,
type(e),
e,
))
log.exception(error_description)
raise HTTPBadRequest(
json_body={
'error': 'parse_error',
'error_description': error_description,
})
else:
if info:
k, v = info
attrs[k] = v
if 'delta' not in attrs or 'entry_date' not in attrs:
# Empty or incomplete row.
continue
if not attrs['delta']:
# Ignore zero amount rows.
continue
sign = attrs.pop('sign', None)
if sign:
# Force the sign of the amount.
attrs['delta'] = abs(attrs['delta']) * sign
if not attrs.get('description'):
attrs['description'] = ''
dbsession.add(
AccountEntry(owner_id=owner_id,
file_id=period.file_id,
period_id=period.id,
statement_id=statement.id,
loop_id='0',
currency=file.currency,
reco_id=None,
**attrs))
