Exemple #1
0
 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()
Exemple #2
0
    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)
Exemple #3
0
    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)
Exemple #4
0
 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))