def sheet_to_inventory(group_by_col, hostname_col, sheet):
    if isinstance(group_by_col, six.string_types):
        group_by_col = column_index_from_string(
            coordinate_from_string(group_by_col + '1')[0]) - 1
    if isinstance(hostname_col, six.string_types):
        hostname_col = column_index_from_string(
            coordinate_from_string(hostname_col + '1')[0]) - 1

    groups = {'_meta': {'hostvars': {}}}
    rows = list(sheet.rows)

    for row in rows[1:]:
        host = row[hostname_col].value
        if host is None:
            continue
        group = row[group_by_col].value
        if group is None:
            group = default_group
        if group not in groups.keys():
            groups[group] = {'hosts': [], 'vars': {}}
        groups[group]['hosts'].append(row[hostname_col].value)
        groups['_meta']['hostvars'][row[hostname_col].value] = {}
        for xlsx_head in rows[:1]:
            for idx, var_name in enumerate(xlsx_head):
                if var_name.value is None:
                    var_name.value = "xlsx_" + var_name.coordinate
                if row[idx].value is not None:
                    groups['_meta']['hostvars'][row[0].value][
                        var_name.value.lower().replace(' ',
                                                       '_')] = row[idx].value

    return groups
示例#2
0
    def style_range(ws, cell_range, alignment=None, font=None, fill=None, border=None):
        """
        :param ws:  Excel worksheet instance
        :param range: An excel range to style (e.g. A1:F20)
        :param alignment: An openpyxl Alignment object
        :param font: An openpyxl Font object
        :param fill: An openpyxl Fill object
        :param border: An openpyxl Border object
        """

        start_cell, end_cell = cell_range.split(':')
        start_coord = coordinate_from_string(start_cell)
        start_row = start_coord[1]
        start_col = column_index_from_string(start_coord[0])
        end_coord = coordinate_from_string(end_cell)
        end_row = end_coord[1]
        end_col = column_index_from_string(end_coord[0])

        for row in range(start_row, end_row + 1):
            for col_idx in range(start_col, end_col + 1):
                col = get_column_letter(col_idx)
                if alignment:
                    ws.cell('%s%s' % (col, row)).alignment = alignment
                if font:
                    ws.cell('%s%s' % (col, row)).font = font
                if fill:
                    ws.cell('%s%s' % (col, row)).fill = fill
                if border:
                    ws.cell('%s%s' % (col, row)).border = border
示例#3
0
    def __init__(self, filename=filename):
        with open(filename, 'r+') as f:
            self.func_list = f.readlines()
            print('range는 ' + self.func_list[0].strip('\n')+ ' 입니다.')
            self.range = self.func_list[2].strip('\n')
            self.func_list = self.func_list[3:]

            input_range = self.range    # 'a1:d5'
            _ranges = input_range.split(':')
            _ranges_start = _ranges[0]
            _ranges_end = _ranges[1]

            start_xy = coordinate_from_string(_ranges_start)
            start_col = column_index_from_string(start_xy[0])  # returns 1
            start_row = start_xy[1]

            end_xy = coordinate_from_string(_ranges_end)
            end_col = column_index_from_string(end_xy[0])  # returns 1
            end_row = end_xy[1]

            self.start_col = start_col
            self.start_row = start_row
            self.end_col = end_col
            self.end_row = end_row

            self.alist = [ start_col, start_row, end_col, end_row]


        # print('TODO : 엑셀에서 범위를 읽어 와야 합니다.')
        print(self.range)
        print('range 를 숫자로 하면')
        print(self.alist)
示例#4
0
def load_from_wb(workbook_name):
    """
    Function used to load the matrix data from a xlsx archive. It is faster then generating it from text
    :param workbook_name: path and name of the xlsx file to use to extract information
    :return: Returns a dictionary containing the information to create a 'CoocMatrix' object
    """
    # Load workbook
    wb = load_workbook(filename=workbook_name, read_only=True)
    print('wb loaded')

    # Load worksheets
    ws = wb['cooc_matrix_full']
    ws_soc_pmi = wb['soc_pmi_matrix']

    # Calculate the matrix dimensions and transform the excel's coordinates to matrix coordinates (to only integers)
    rows = ws.rows
    matrix_dim = ws.calculate_dimension().split(':')
    rows_count = coordinate_from_string(matrix_dim[1])[1] - 1
    column_count = column_index_from_string(
        coordinate_from_string(matrix_dim[1])[0]) - 1

    matrix = np.empty((rows_count, column_count))

    print('matrix allocated')

    noun_rows = {}
    verb_columns = {}

    get_column_names(rows, verb_columns)
    print('get_column_names completed')
    complete_the_loading(rows, noun_rows, matrix)
    print('complete_the_loading completed')

    rows = ws_soc_pmi.rows
    matrix_dim = ws_soc_pmi.calculate_dimension().split(':')
    rows_count = coordinate_from_string(matrix_dim[1])[1] - 1
    column_count = column_index_from_string(
        coordinate_from_string(matrix_dim[1])[0]) - 1

    soc_pmi_matrix = np.empty((rows_count, column_count))

    soc_pmi_noun_rows = {}
    soc_pmi_verb_columns = {}

    get_column_names(rows, soc_pmi_verb_columns)
    print('get_column_names completed 2')
    complete_the_loading(rows, soc_pmi_noun_rows, soc_pmi_matrix)
    print('complete_the_loading completed 2')

    content = {
        'matrix': matrix,
        'noun_rows': noun_rows,
        'verb_columns': verb_columns,
        'soc_pmi_matrix': soc_pmi_matrix,
        'soc_pmi_noun_rows': soc_pmi_noun_rows,
        'soc_pmi_verb_columns': soc_pmi_verb_columns
    }

    return content
示例#5
0
def check_location(cell_location):
    logger.debug("Checking location for " + str(cell_location))

    try:
        coordinate_from_string(cell_location)
        return cell_location
    except Exception:
        logger.error("Not a valid cell location '" + str(cell_location) + "'")
        sys.exit()
def Crawler(wb, writer, name):
    print name
    startRow = 0
    endRow = 0
    startCol = 0
    endCol = 0
    for sheet in wb.worksheets:
        title = sheet.title
        bEndRowFound = False
        for row in sheet.iter_rows():
            for cell in row:
                cellValue = cell.value
                if cellValue == 'SL #':
                    coord = cell.coordinate
                    t = utils.coordinate_from_string(coord)
                    startCol = utils.column_index_from_string(t[0]) - 1
                    startRow = t[1] + 3

                if type(cellValue) is unicode:
                    if cellValue.strip() == 'Total':
                        bEndRowFound = True
                        coord = cell.coordinate
                        t = utils.coordinate_from_string(coord)
                        endCol = utils.column_index_from_string(t[0]) + 8
                        endRow = t[1] - 1
            if (bEndRowFound):
                break

        for irow in range(startRow, endRow):
            print name
            row = sheet[irow]
            rowOut = []
            rowOut.append(title)
            rowOut.append(name)
            for icell in range(startCol, endCol):
                cell = row[icell]
                cellValue = cell.value
                coord = cell.coordinate
                t = utils.coordinate_from_string(coord)
                cellColumn = t[0]
                cellColumNumber = utils.column_index_from_string(cellColumn)
                if cellColumNumber == startCol + 2:
                    rowOut.append(cellValue)
                    print coord
                    print cellValue
                elif cellColumNumber == startCol + 5:
                    rowOut.append(cellValue)
                    print coord
                    print cellValue
                elif cellColumNumber == startCol + 10:
                    rowOut.append(cellValue)
                    print coord
                    print cellValue

            writer.writerow(rowOut)
示例#7
0
def getColRowSpan(mergeDef):
    #merge def is like A1:B2
    cells = mergeDef.split(":")
    sC, sR = coordinate_from_string(cells[0])
    sC = column_index_from_string(sC)
    eC, eR = coordinate_from_string(cells[1])
    eC = column_index_from_string(eC)
    colspan = int(eC) - int(sC) + 1
    rowspan = int(eR) - int(sR) + 1
    if colspan == 1:
        colspan = None
    if rowspan == 1:
        rowspan = None
    return colspan, rowspan
示例#8
0
def getColRowSpan(mergeDef):
    #merge def is like A1:B2
    cells = mergeDef.split(":")
    sC,sR=coordinate_from_string(cells[0])
    sC = column_index_from_string(sC)
    eC,eR=coordinate_from_string(cells[1])
    eC = column_index_from_string(eC)
    colspan= int(eC) - int(sC) + 1
    rowspan = int(eR) - int(sR) + 1
    if colspan == 1:
        colspan=None
    if rowspan == 1:
        rowspan=None
    return colspan, rowspan
示例#9
0
    def parse_cell(self, element):
        value = element.find(self.VALUE_TAG)
        if value is not None:
            value = value.text
        formula = element.find(self.FORMULA_TAG)
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        style_id = element.get('s')

        # assign formula to cell value unless only the data is desired
        if formula is not None and not self.data_only:
            data_type = 'f'
            if formula.text:
                value = "=" + formula.text
            else:
                value = "="
            formula_type = formula.get('t')
            if formula_type:
                self.ws.formula_attributes[coordinate] = {'t': formula_type}
                si = formula.get(
                    'si')  # Shared group index for shared formulas
                if si:
                    self.ws.formula_attributes[coordinate]['si'] = si
                ref = formula.get('ref')  # Range for shared formulas
                if ref:
                    self.ws.formula_attributes[coordinate]['ref'] = ref

        style = {}
        if style_id is not None:
            style_id = int(style_id)
            style = self.styles[style_id]

        column, row = coordinate_from_string(coordinate)
        cell = Cell(self.ws, column, row, **style)
        self.ws._add_cell(cell)

        if value is not None:
            if data_type == 'n':
                value = cell._cast_numeric(value)
            elif data_type == 'b':
                value = bool(int(value))
            elif data_type == 's':
                value = self.shared_strings[int(value)]
            elif data_type == 'str':
                data_type = 's'

        else:
            if data_type == 'inlineStr':
                data_type = 's'
                child = element.find(self.INLINE_STRING)
                if child is None:
                    child = element.find(self.INLINE_RICHTEXT)
                if child is not None:
                    value = child.text

        if self.guess_types or value is None:
            cell.value = value
        else:
            cell._value = value
            cell.data_type = data_type
示例#10
0
    def write_comments_vml(self):
        root = Element("xml")
        shape_layout = SubElement(root, "{%s}shapelayout" % officens,
                                  {"{%s}ext" % vmlns: "edit"})
        SubElement(shape_layout, "{%s}idmap" % officens, {
            "{%s}ext" % vmlns: "edit",
            "data": "1"
        })
        shape_type = SubElement(
            root, "{%s}shapetype" % vmlns, {
                "id": "_x0000_t202",
                "coordsize": "21600,21600",
                "{%s}spt" % officens: "202",
                "path": "m,l,21600r21600,l21600,xe"
            })
        SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
        SubElement(shape_type, "{%s}path" % vmlns, {
            "gradientshapeok": "t",
            "{%s}connecttype" % officens: "rect"
        })

        for idx, comment in enumerate(self.comments, 1026):

            shape = _shape_factory()
            col, row = coordinate_from_string(comment.ref)
            row -= 1
            column = column_index_from_string(col) - 1

            shape.set('id', "_x0000_s%04d" % idx)
            client_data = shape.find("{%s}ClientData" % excelns)
            client_data.find("{%s}Row" % excelns).text = str(row)
            client_data.find("{%s}Column" % excelns).text = str(column)
            root.append(shape)

        return tostring(root)
示例#11
0
 def __init__(self, formula, origin):
     # Excel errors out when a workbook has formulae in R1C1 notation,
     # regardless of the calcPr:refMode setting, so I'm assuming the
     # formulae stored in the workbook must be in A1 notation.
     col, self.row = coordinate_from_string(origin)
     self.col = column_index_from_string(col)
     self.tokenizer = Tokenizer(formula)
示例#12
0
    def parse_cell(self, element):
        value = element.find(self.VALUE_TAG)
        if value is not None:
            value = value.text
        formula = element.find(self.FORMULA_TAG)
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        style_id = element.get('s')

        # assign formula to cell value unless only the data is desired
        if formula is not None and not self.data_only:
            data_type = 'f'
            if formula.text:
                value = "=" + formula.text
            else:
                value = "="
            formula_type = formula.get('t')
            if formula_type:
                self.ws.formula_attributes[coordinate] = {'t': formula_type}
                si = formula.get('si')  # Shared group index for shared formulas
                if si:
                    self.ws.formula_attributes[coordinate]['si'] = si
                ref = formula.get('ref')  # Range for shared formulas
                if ref:
                    self.ws.formula_attributes[coordinate]['ref'] = ref


        style = {}
        if style_id is not None:
            style_id = int(style_id)
            style = self.styles[style_id]

        column, row = coordinate_from_string(coordinate)
        cell = Cell(self.ws, column, row, **style)
        self.ws._add_cell(cell)

        if value is not None:
            if data_type == 'n':
                value = cell._cast_numeric(value)
            elif data_type == 'b':
                value = bool(int(value))
            elif data_type == 's':
                value = self.shared_strings[int(value)]
            elif data_type == 'str':
                data_type = 's'

        else:
            if data_type == 'inlineStr':
                data_type = 's'
                child = element.find(self.INLINE_STRING)
                if child is None:
                    child = element.find(self.INLINE_RICHTEXT)
                if child is not None:
                    value = child.text

        if self.guess_types or value is None:
            cell.value = value
        else:
            cell._value=value
            cell.data_type=data_type
示例#13
0
def collapse_cell_addresses(cells, input_ranges=()):
    """ Collapse a collection of cell co-ordinates down into an optimal
        range or collection of ranges.

        E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation
        object applied, attempt to collapse down to a single range, A1:B3.

        Currently only collapsing contiguous vertical ranges (i.e. above
        example results in A1:A3 B1:B3).
    """
    keyfunc = lambda x: x[0]

    # Get the raw coordinates for each cell given
    raw_coords = [coordinate_from_string(cell) for cell in cells]

    # Group up as {column: [list of rows]}
    grouped_coords = OrderedDict((k, [c[1] for c in g]) for k, g in
                          groupby(sorted(raw_coords, key=keyfunc), keyfunc))
    ranges = list(input_ranges)

    # For each column, find contiguous ranges of rows
    for column in grouped_coords:
        rows = sorted(grouped_coords[column])
        grouped_rows = [[r[1] for r in list(g)] for k, g in
                        groupby(enumerate(rows),
                        lambda x: x[0] - x[1])]
        for rows in grouped_rows:
            if not rows:
                continue
            fmt = "{0}{1}:{2}{3}"
            if len(rows) == 1:
                fmt = "{0}{1}"
            ranges.append(fmt.format(column, rows[0], column, rows[-1]))

    return " ".join(ranges)
示例#14
0
 def __init__(self, formula, origin):
     # Excel errors out when a workbook has formulae in R1C1 notation,
     # regardless of the calcPr:refMode setting, so I'm assuming the
     # formulae stored in the workbook must be in A1 notation.
     col, self.row = coordinate_from_string(origin)
     self.col = column_index_from_string(col)
     self.tokenizer = Tokenizer(formula)
示例#15
0
    def _get_row(self, element, min_col=1, max_col=None):
        """Return cells from a particular row"""
        col_counter = min_col

        for cell in safe_iterator(element, CELL_TAG):
            coord = cell.get('r')
            column_str, row = coordinate_from_string(coord)
            column = column_index_from_string(column_str)

            if max_col is not None and column > max_col:
                break

            if min_col <= column:
                if col_counter < column:
                    for col_counter in range(max(col_counter, min_col), column):
                        # pad row with missing cells
                        yield EMPTY_CELL

                data_type = cell.get('t', 'n')
                style_id = int(cell.get('s', 0))
                formula = cell.findtext(FORMULA_TAG)
                value = cell.find(VALUE_TAG)
                if value is not None:
                    value = value.text
                if formula is not None:
                    if not self.parent.data_only:
                        data_type = 'f'
                        value = "=%s" % formula

                yield ReadOnlyCell(self, row, column_str,
                                   value, data_type, style_id)
            col_counter = column + 1
        if max_col is not None:
            for _ in range(col_counter, max_col+1):
                yield EMPTY_CELL
示例#16
0
    def write_comments_vml(self):
        root = Element("xml")
        shape_layout = SubElement(root, "{%s}shapelayout" % officens,
                                  {"{%s}ext" % vmlns: "edit"})
        SubElement(shape_layout,
                   "{%s}idmap" % officens,
                   {"{%s}ext" % vmlns: "edit", "data": "1"})
        shape_type = SubElement(root,
                                "{%s}shapetype" % vmlns,
                                {"id": "_x0000_t202",
                                 "coordsize": "21600,21600",
                                 "{%s}spt" % officens: "202",
                                 "path": "m,l,21600r21600,l21600,xe"})
        SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
        SubElement(shape_type,
                   "{%s}path" % vmlns,
                   {"gradientshapeok": "t",
                    "{%s}connecttype" % officens: "rect"})

        for idx, comment in enumerate(self.comments, 1026):

            shape = _shape_factory()
            col, row = coordinate_from_string(comment.ref)
            row -= 1
            column = column_index_from_string(col) - 1

            shape.set('id',  "_x0000_s%04d" % idx)
            client_data = shape.find("{%s}ClientData" % excelns)
            client_data.find("{%s}Row" % excelns).text = str(row)
            client_data.find("{%s}Column" % excelns).text = str(column)
            root.append(shape)

        return tostring(root)
示例#17
0
def split_cell_story(string):
    if args.debug:
        log.info("#=coordinate=> [ %s ]" % (string))

    char_split_1 = '='
    char_split_2 = ','
    string_coordinate = string.split(char_split_1)[0]
    string_cell_story = string.split(char_split_1)[1:]
    coordinate = string_coordinate.split(char_split_2)
    cell_story = char_split_1.join(string_cell_story)

    coordinate_XY = None
    if len(coordinate) == 1:
        from openpyxl.utils import coordinate_from_string, column_index_from_string
        x_y = coordinate_from_string(coordinate[0])  # returns ('A',4)
        col = column_index_from_string(x_y[0])  # returns 1
        row = x_y[1]
        coordinate_XY = coordinate[0]
        coordinate[0] = row
        coordinate.append(col)

    if args.debug:
        log.info("#=coordinate=> [ %s | %s ]=[%s]" %
                 (coordinate[0], coordinate[1], cell_story))

    return (coordinate[0], coordinate[1], coordinate_XY, cell_story)
示例#18
0
    def __init__(self, row, WBCOLORS, ws, preserve_width=True):
        celllist = []
        styleList = []
        for cell in row:
            wcell = wikiCell(cell, WBCOLORS, ws)
            celllist.append(wcell)
            styleList.append(wcell.style)
        # resolve common styles
        self.style = commonStyle(styleList)
        col, rownum = coordinate_from_string(cell.coordinate)
        if preserve_width == True and rownum == 1:
            colwidths = getColumnWidths(ws)
            if col in colwidths:
                width = colwidths[col]
        else:
            colwidths = None

        self.rowwiki = "| "
        cellList = []
        for cell in celllist:
            if not cell.merged:
                cellList.append(
                    cell.getWikiStr(list(self.style.keys()), colwidths))
        # also handle the special case of using single pipe when a return character is encountered
        self.rowwiki += ("|| ".join(cellList) + "\n").replace("\n||", "\n|")
示例#19
0
def plot_graphs(wb_names, title_parts, cell_starts, dirs):
    '''Побудова контурних графіків
    '''
    
    for i, wb_name in enumerate(wb_names):
        wb = load_workbook(filename=os.path.join(DIR_WORK, 
                             wb_name), read_only=True, data_only=True)
        title_part = title_parts[i]
        print((' Побудова контурних графіків для "{}": '.format(title_part)
               .center(80, '*')))
        
        cell_start = cell_starts[i]
        cell_start_column, cell_start_row = coordinate_from_string(cell_start)
        cell_start_column_index = column_index_from_string(cell_start_column)
        
        target_dir = os.path.join(DIR_WORK, dirs[i])
        try:
            # Спочатку видаляється папка.
            shutil.rmtree(target_dir)
        except FileNotFoundError: pass
        os.mkdir(target_dir)   # Потім створюється папка.
        
        sheets = wb.get_sheet_names()
        sheets_count = len(sheets)
        for i, sheet_name in enumerate(sheets):
            print(u'{}/{} = {:.1%} => WB: {}, WS: {}'.format(i+1, sheets_count,
                                                             (i+1)/sheets_count,
                                                             title_part, sheet_name))
            sheet_name_data = dict(re_pattern_sheet_data.findall(sheet_name))
            if 'l' in sheet_name_data.keys():
                sheet_name_data['L'] = sheet_name_data.pop('l')
            subtitle = (r'd = {d} мм, L = {L} мм, x = {x} см, v = {v} л/(хв$\cdot$м${{}}^2$), '
                        r'$V$ = {V} м${{}}^3$, $I$ = {I} Вт/м${{}}^2$'.format(
                            **sheet_name_data) )
            ws = wb.get_sheet_by_name(sheet_name)
            
            ci = 0  # Індекс стовпця на робочому аркуші.
            while True:
                # прохід по стовпцях робочого аркуша.
                t = []
                cell_start_column_new = get_column_letter(cell_start_column_index+ci)
                cell_start_value_begin = '{}{}'.format(cell_start_column_new,
                                                       cell_start_row+1)
                cell_start_value_end = '{}{}'.format(cell_start_column_new,
                                                     cell_start_row+30)
                for k, c in enumerate(ws[cell_start_value_begin: cell_start_value_end]):
                    cell_value = c[0].value
                    if not k and (cell_value is None):
                        # Припинити читання, якщо перша кл. стовпця - нішо.
                        break
                    t.append(float(cell_value))
                
                if not t:
                    break   # Перехід до наступного аркуша, якщо нічого будувати.
                
                tau = ws['{}{}'.format(cell_start_column_new, cell_start_row)].value
                gen_contour(t, plot_type.imshow_p, r'{} для $\tau$ = {}'.format(
                    title_part, tau), subtitle, out_fname=os.path.join(target_dir, 
                                        '{}-TAU={}'.format(sheet_name, tau)))
                ci += 1
示例#20
0
    def translate_formula(self, dest):
        """
        Convert the formula into A1 notation.

        The formula is converted into A1 assuming it is assigned to the cell
        whose address is `dest` (no worksheet name).

        """
        tokens = self.get_tokens()
        if not tokens:
            return ""
        elif tokens[0].type == Token.LITERAL:
            return tokens[0].value
        out = ['=']
        # per the spec:
        # A compliant producer or consumer considers a defined name in the
        # range A1-XFD1048576 to be an error. All other names outside this
        # range can be defined as names and overrides a cell reference if an
        # ambiguity exists. (I.18.2.5)
        dcol, drow = coordinate_from_string(dest)
        dcol = column_index_from_string(dcol)
        row_delta = drow - self.row
        col_delta = dcol - self.col
        for token in tokens:
            if token.type == Token.OPERAND and token.subtype == Token.RANGE:
                out.append(self.translate_range(token.value, row_delta,
                                                col_delta))
            else:
                out.append(token.value)
        return "".join(out)
示例#21
0
def _move(cellstr, nrows, ncols):
    """Internal method for adding rows/columns to cell coordinate.

    'A1' moved by 1 row, 1 column => 'B2'

    Args:
        cellstr (str): Cell coordinate (A1,B2)
        nrows (int): Number of rows to move (usually down)
        ncols (int): Number of columns to move (usually right)
    Returns:
        str: New cell coordinate.
    """
    # WARNING! This will only work up to column Z!
    # colidx is a string, rowidx is a number
    col_str_idx, rowidx = utils.coordinate_from_string(cellstr)
    letters = string.ascii_uppercase
    try:
        colidx = letters.index(col_str_idx)
        newcolidx = colidx + ncols
        newrowidx = rowidx + nrows
        newcellstr = '%s%i' % (letters[newcolidx], newrowidx)
        return newcellstr
    except ValueError:
        raise ValueError('Could not add %i columns to column %s.' %
                         (ncols, col_str_idx))
示例#22
0
    def translate_formula(self, dest):
        """
        Convert the formula into A1 notation.

        The formula is converted into A1 assuming it is assigned to the cell
        whose address is `dest` (no worksheet name).

        """
        tokens = self.get_tokens()
        if not tokens:
            return ""
        elif tokens[0].type == Token.LITERAL:
            return tokens[0].value
        out = ['=']
        # per the spec:
        # A compliant producer or consumer considers a defined name in the
        # range A1-XFD1048576 to be an error. All other names outside this
        # range can be defined as names and overrides a cell reference if an
        # ambiguity exists. (I.18.2.5)
        dcol, drow = coordinate_from_string(dest)
        dcol = column_index_from_string(dcol)
        row_delta = drow - self.row
        col_delta = dcol - self.col
        for token in tokens:
            if token.type == Token.OPERAND and token.subtype == Token.RANGE:
                out.append(
                    self.translate_range(token.value, row_delta, col_delta))
            else:
                out.append(token.value)
        return "".join(out)
示例#23
0
 def _readMatrix (self,xlsxFilename,xlsxSheetname,**kwargs):
     workBook = load_workbook(xlsxFilename)
     inSheet = workBook[xlsxSheetname]
     returnMatrix = []
     if 'range' in kwargs.keys():
         rangeString = kwargs["range"] 
         startRange, endRange = rangeString.split (":")
         startColumn, startRow = coordinate_from_string (startRange)
         endColumn, endRow = coordinate_from_string (endRange)
         for row in range (startRow,endRow + 1):
             lineArray = []
             for column in range (column_index_from_string(startColumn),column_index_from_string(endColumn) + 1):
                 #print inSheet['%s%s'%(get_column_letter(column), row)].value
                 lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value)
                 column += 1
             returnMatrix.append(lineArray)
             column = 1
             row += 1
         return returnMatrix
     else:
         if 'start' in kwargs.keys():
             startCell = kwargs["start"] 
             startColumnLetter, startRow = coordinate_from_string (startCell)
             startColumn = column_index_from_string(startColumnLetter)
         else:
             startColumn, startRow = 1,1  
         if 'cellsPerRow' in kwargs.keys():
             numberOfCellsPerRow = kwargs["cellsPerRow"]      
         row = startRow
         column = startColumn
         while inSheet['%s%s'%(get_column_letter(column), row)].value:
             lineArray = []
             if numberOfCellsPerRow: 
                 while column <= startColumn + numberOfCellsPerRow - 1:
                     #print inSheet['%s%s'%(get_column_letter(column), row)].value
                     lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value)
                     column += 1
             else:
                 while inSheet['%s%s'%(get_column_letter(column), row)].value:
                     #print inSheet['%s%s'%(get_column_letter(column), row)].value
                     lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value)
                     column += 1
             returnMatrix.append(lineArray)
             column = startColumn
             row += 1
         return returnMatrix
示例#24
0
 def _get_cell(self, coordinate):
     """Cells are returned by a generator which can be empty"""
     col, row = coordinate_from_string(coordinate)
     col = column_index_from_string(col)
     cell = tuple(self.get_squared_range(col, row, col, row))[0]
     if cell:
         return cell[0]
     return EMPTY_CELL
示例#25
0
文件: writer.py 项目: CometHale/lphw
    def add_shape_vml(self, root, idx, comment):
        col, row = coordinate_from_string(comment.ref)
        row -= 1
        column = column_index_from_string(col) - 1
        shape = _shape_factory(row, column)

        shape.set('id',  "_x0000_s%04d" % idx)
        root.append(shape)
示例#26
0
    def add_comment_shape(self, root, idx, coord):
        col, row = coordinate_from_string(coord)
        row -= 1
        column = column_index_from_string(col) - 1
        shape = _shape_factory(row, column)

        shape.set('id', "_x0000_s%04d" % idx)
        root.append(shape)
示例#27
0
 def _get_cell(self, coordinate):
     """Cells are returned by a generator which can be empty"""
     col, row = coordinate_from_string(coordinate)
     col = column_index_from_string(col)
     cell = tuple(self.get_squared_range(col, row, col, row))[0]
     if cell:
         return cell[0]
     return EMPTY_CELL
示例#28
0
    def add_comment_shape(self, root, idx, coord, height, width):
        col, row = coordinate_from_string(coord)
        row -= 1
        column = column_index_from_string(col) - 1
        shape = _shape_factory(row, column, height, width)

        shape.set('id', "_x0000_s%04d" % idx)
        root.append(shape)
示例#29
0
    def read_excel_adv(self, io, is_preview=False, **kwds):
        """
        # TODO: Handle multiple sheets at once. Each sheet may have difference col and rows range.
        Args:
            io (string): excel file name or pandas ExcelFile object
            is_preview (boolean): to get the dataframe with preview rows only.
        Returns:
             dataframe
        """
        header = []
        if self.header_xls_start and self.header_xls_end:
            scol, srow = coordinate_from_string(self.header_xls_start)
            ecol, erow = coordinate_from_string(self.header_xls_end)

            # header, skiprows, usecols
            header = [x for x in range(erow - srow + 1)]
            usecols = scol + ":" + ecol
            skiprows = srow - 1

            if is_preview:
                workbook = pd.ExcelFile(io)

                rows = workbook.book.sheet_by_index(0).nrows

                # Get only preview rows. Way to implement nrows (in read_csv)
                skip_footer = (rows - skiprows - self.nrows_preview)

                df = pd.read_excel(io, header=header, skiprows=skiprows, usecols=usecols,
                                   skip_footer=skip_footer, **kwds)
            else:
                df = pd.read_excel(io, header=header, skiprows=skiprows, usecols=usecols, **kwds)
        else:
            df = pd.read_excel(io, **kwds)

        if self.remove_blank_cols:
            df = df.dropna(axis='columns', how='all')
        if self.remove_blank_rows:
            df = df.dropna(axis='rows', how='all')
        if self.collapse_header:
            if len(header) > 1:
                df.columns = [' '.join([s for s in col if not 'Unnamed' in s]).strip().replace("\n", ' ')
                              for col in df.columns.values]
            else:
                df.rename(columns=lambda x: x.strip().replace("\n", ' '), inplace=True)
        return df
示例#30
0
def row_col_from_cell(cell):
    """

    :param cell: a cell string e.g. 'B12'
    :return: row, col in numerical index
    """
    from openpyxl.utils import column_index_from_string, coordinate_from_string
    (col,row) = coordinate_from_string(cell)
    return row, column_index_from_string(col)
示例#31
0
def surround_border(ws, top_left, bottom_right):
    """
    This method surrounds the range with a thin border because openpyxl
    can't apply a style to a range of cells.
    This only works for a range of cells whose width is greater than 1
    and height is greater than 1 :(

    :param ws:                      (Object) Worksheet
    :param top_left:                (String) Cell location ie 'A1'
    :param bottom_right:            (String) Cell location ie 'A1'
    :return:                        (Object) Worksheet
    """

    top_left = list(coordinate_from_string(top_left))
    bottom_right = list(coordinate_from_string(bottom_right))
    top_left[0] = column_index_from_string(top_left[0])
    bottom_right[0] = column_index_from_string(bottom_right[0])

    for column in range(top_left[0], bottom_right[0] + 1):
        for row in range(top_left[1], bottom_right[1] + 1):
            if column == top_left[0]:
                ws.cell(row=row, column=column).border = Border(left=Side(border_style='thin'))
            if column == bottom_right[0]:
                ws.cell(row=row, column=column).border = Border(right=Side(border_style='thin'))
            if row == top_left[1]:
                ws.cell(row=row, column=column).border = Border(top=Side(border_style='thin'))
            if row == bottom_right[1]:
                ws.cell(row=row, column=column).border = Border(bottom=Side(border_style='thin'))

            # the four corners
            if column == top_left[0] and row == top_left[1]:
                ws.cell(row=row, column=column).border = Border(left=Side(border_style='thin'),
                                                                top=Side(border_style='thin'))
            if column == top_left[0] and row == bottom_right[1]:
                ws.cell(row=row, column=column).border = Border(left=Side(border_style='thin'),
                                                                bottom=Side(border_style='thin'))
            if column == bottom_right[0] and row == bottom_right[1]:
                ws.cell(row=row, column=column).border = Border(right=Side(border_style='thin'),
                                                                bottom=Side(border_style='thin'))
            if column == bottom_right[0] and row == top_left[1]:
                ws.cell(row=row, column=column).border = Border(right=Side(border_style='thin'),
                                                                top=Side(border_style='thin'))

    return ws
示例#32
0
 def _get_cell(self, coordinate):
     """
     Internal method for getting a cell from a worksheet.
     Will create a new cell if one doesn't already exist.
     """
     coordinate = coordinate.upper()
     if not coordinate in self._cells:
         column, row = coordinate_from_string(coordinate)
         self._new_cell(column, row)
     return self._cells[coordinate]
示例#33
0
def row_and_column(cells_address: str) -> (int, int):
    """
    Converts the string address of the cell (e.g. A1) into the row's number (starting from 1) and column's number
    (starting from 1).
    """

    column_as_letter, row_number = coordinate_from_string(cells_address)
    column_number = column_index_from_string(column_as_letter)

    return row_number, column_number
示例#34
0
def _update_style(ws, cell_range):
    start_cell, end_cell = str(cell_range).split(':')
    start_coord = coordinate_from_string(start_cell)
    end_coord = coordinate_from_string(end_cell)

    start_row = start_coord[1]
    end_row = end_coord[1]

    start_col = column_index_from_string(start_coord[0])
    end_col = column_index_from_string(end_coord[0])

    border_style = ws.cell(row=start_row, column=start_col).border.left.style
    color = ws.cell(row=start_row, column=start_col).border.left.color

    cellborder = _get_border(border_style, color)

    for row in range(start_row, end_row + 1):
        for col in range(start_col, end_col + 1):
            ws.cell(row=row, column=col).border = cellborder
示例#35
0
def coordinate_to_index(coordinate_string, zero_based=True):
    # returns ('A',4) for example
    xy = coordinate_from_string(coordinate_string)
    # openpyxl will assume column A returns 1
    col = column_index_from_string(xy[0])
    row = xy[1]
    if zero_based:
        col = col - 1
        row = row - 1
    return (col, row)
 def last_column_of_table(self, format):
     for i, table in enumerate(self.sheet._tables):
         if table.displayName == self.tableName:
             old_table_reference = table.ref
     last_cell_of_table = old_table_reference[old_table_reference.find(':') + 1:]
     last_column_letter = coordinate_from_string(last_cell_of_table)[0]
     if format == 'letter':
         return last_column_letter
     elif format == 'number':
         return column_index_from_string(last_column_letter)
示例#37
0
 def _get_cell(self, coordinate):
     """
     Internal method for getting a cell from a worksheet.
     Will create a new cell if one doesn't already exist.
     """
     coordinate = coordinate.upper()
     if not coordinate in self._cells:
         column, row = coordinate_from_string(coordinate)
         self._new_cell(column, row)
     return self._cells[coordinate]
示例#38
0
def calc_and_limit_sheet_dim(sheet, max_col, max_row):
    dim = sheet.calculate_dimension()
    dim = dim.split(':')
    xy = coordinate_from_string(dim[1])
    count_col = column_index_from_string(xy[0])
    count_row = xy[1]
    if max_col and (count_col > max_col):
        count_col = max_col
    if max_row and (count_row > max_row):
        count_row = max_row
    return (count_col, count_row)
def convert_coordinates_alphanum_to_num(alphanum_coordinates_str, zero_indexed=False):
    """
    Function that converts coordinates from a alphanumeric inde to a numeric one (e.g. 'A1' -> [1,1])
    """
    xy = coordinate_from_string(alphanum_coordinates_str) # returns ('A',1)
    if zero_indexed:
        col = column_index_from_string(xy[0]) - 1
        row = xy[1] - 1
    else:
        col = column_index_from_string(xy[0])
        row = xy[1]
    return [row, col]
示例#40
0
def mergedcell_info(merged_ranges):
    '''
	returns head merged cells and hidden cells under the merged ones
	'''

    global cell_dict
    global head_cells
    global hidden_cells
    ind = 0
    for mrange in merged_ranges:
        '''
		to convert merged range, say A1:B3 to (0,0) to (1,2)
		'''

        mrange = str(mrange)
        cell = mrange.split(':')

        xy = coordinate_from_string(cell[0])
        col1 = column_index_from_string(xy[0])
        row1 = xy[1]

        head_cells[str((row1 - 1, col1 - 1))] = str(ind)

        xy = coordinate_from_string(cell[1])
        col2 = column_index_from_string(xy[0])
        row2 = xy[1]

        for i in range(row1 - 1, row2):
            for j in range(col1 - 1, col2):
                hidden_cells.append((i, j))

        rowspan = (row2 - row1) + 1
        colspan = (col2 - col1) + 1

        cell_dict[str(ind)] = {}
        cell_dict[str(ind)]["rowspan"] = str(rowspan)
        cell_dict[str(ind)]["colspan"] = str(colspan)

        ind += 1
示例#41
0
 def __try_use_cell(self, line):
     if not line.startswith('['):
         return False
     test = line.replace('[', '')
     test = test.replace(']', '')
     test = test.replace(' ', '')
     try:
         xy = coordinate_from_string(test)
         self.r = xy[1]
         self.c = column_index_from_string(xy[0])
         return True
     except:
         return False
示例#42
0
def applyFmt(tblStyle, trStyle, tdStyle, cell, ws):
    # resolve all the styles
    finalStyle = deepcopy(tblStyle)
    if finalStyle == None:
        finalStyle = {}
    for s in [trStyle, tdStyle]:
        if s == None:
            continue
        for k, v in s.iteritems():
            if v == False:
                continue
            finalStyle[k] = v
    font = Font()
    for k, v in finalStyle.iteritems():
        if k == "italic" and v != False:
            font.i = True
        if k == "underline" and v != False:
            font.u = Font.UNDERLINE_SINGLE
        if k == "line-through" and v != False:
            font.strikethrough = True
        if k == "font_name" and v != False:
            font.name = v
        if k == "bold" and v == True:
            font.bold = True
        if k == 'width' and v != "" and v != False:
            c, r = coordinate_from_string(cell.coordinate)
            m = re.match("([\d\.]+)(\D+)", v)
            if m != None:
                w = m.group(1)
                units = m.group(2)
                if units == "in":
                    w = float(w) * 12
            ws.column_dimensions[c].width = w
        if k == "color" and v != False:
            if v[1] == "#":
                font.color = v[1:]
            else:
                try:
                    hxcol = webcolors.name_to_hex(v)
                    font.color = hxcol[1:]
                except:
                    pass

        if k == "background-color" and v != False:
            c = Color(v[1:])
            fill = PatternFill(patternType=fills.FILL_SOLID, fgColor=c)
            cell.fill = fill

    cell.font = font
示例#43
0
def applyFmt(tblStyle, trStyle,tdStyle, cell,ws):
    # resolve all the styles
    finalStyle=deepcopy(tblStyle)
    if finalStyle == None:
        finalStyle ={}
    for s in [trStyle,tdStyle]:
        if s==None:
            continue
        for k,v in s.iteritems():
            if v == False:
                continue
            finalStyle[k]=v
    font=Font()
    for k,v in finalStyle.iteritems():
        if k == "italic" and v!=False:
            font.i=True
        if k == "underline" and v!=False:
            font.u=Font.UNDERLINE_SINGLE
        if k == "line-through" and v!=False:
            font.strikethrough=True
        if k == "font_name" and v!=False:
            font.name=v
        if k=="bold" and v==True:
            font.bold=True
        if k=='width' and v != "" and v != False:
            c,r=coordinate_from_string(cell.coordinate)
            m=re.match("([\d\.]+)(\D+)",v)
            if m != None:
                w=m.group(1)
                units=m.group(2)
                if units == "in":
                    w=float(w)*12
            ws.column_dimensions[c].width=w
        if k == "color" and v != False:
            if v[1]=="#":
                font.color = v[1:]
            else:
                try:
                    hxcol=webcolors.name_to_hex(v)
                    font.color=hxcol[1:]
                except:
                    pass

        if k == "background-color" and v != False:
            c=Color(v[1:])
            fill=PatternFill(patternType=fills.FILL_SOLID,fgColor=c)
            cell.fill = fill
            
    cell.font=font        
示例#44
0
 def _fill_bi(self, workbook, data_dict, worksheet_range):
     for sheet_name in data_dict:
         worksheet = data_dict[sheet_name]
         if isinstance(sheet_name, str):
             st = get_sheet_by_name(workbook, sheet_name)
         elif isinstance(sheet_name, int):
             st = workbook.worksheets[sheet_name - 1]
         if not st:
             raise ValidationError(
                 _('Sheet %s not found!') % sheet_name)
         if not worksheet.get('_BI_', False):
             continue
         for rc, bi_dict in worksheet.get('_BI_', {}).iteritems():
             req_field = ['df', 'oper_code']
             key_field = bi_dict.keys()
             if set(req_field) != set(key_field):
                 raise ValidationError(
                     _('_BI_ requires \n'
                       ' - df: initial DataFrame from worksheet\n'
                       ' - oper_code: pandas operation code'))
             # Get dataframe
             src_df = bi_dict['df']
             src_st = get_sheet_by_name(workbook, src_df)
             df = load_workbook_range(worksheet_range[src_df], src_st)
             eval_context = {'df': df, 'pd': pd, 'np': np}
             # Get DF using safe_eval method
             df = safe_eval(bi_dict['oper_code'], eval_context,
                            mode="exec", nocopy=True)
             if 'result' in eval_context:  # use result=...
                 df = eval_context['result']
             if df is None:
                 df = eval(bi_dict['oper_code'], eval_context)
             if df.empty:
                 continue
             df = df.reset_index()
             rows = dataframe_to_rows(df, index=False, header=False)
             # Get init cell index
             xy = coordinate_from_string(rc)
             c = column_index_from_string(xy[0])
             r = xy[1]
             for r_idx, row in enumerate(rows, r):
                 for c_idx, value in enumerate(row, c):
                     st.cell(row=r_idx, column=c_idx, value=value)
示例#45
0
    def cell(self, coordinate=None, row=None, column=None, value=None):
        """Returns a cell object based on the given coordinates.

        Usage: cell(coodinate='A15') **or** cell(row=15, column=1)

        If `coordinates` are not given, then row *and* column must be given.

        Cells are kept in a dictionary which is empty at the worksheet
        creation.  Calling `cell` creates the cell in memory when they
        are first accessed, to reduce memory usage.

        :param coordinate: coordinates of the cell (e.g. 'B12')
        :type coordinate: string

        :param row: row index of the cell (e.g. 4)
        :type row: int

        :param column: column index of the cell (e.g. 3)
        :type column: int

        :raise: InsufficientCoordinatesException when coordinate or (row and column) are not given

        :rtype: :class:openpyxl.cell.Cell

        """
        if coordinate is None:
            if (row is None or column is None):
                msg = "You have to provide a value either for " \
                    "'coordinate' or for 'row' *and* 'column'"
                raise InsufficientCoordinatesException(msg)
            else:
                column = get_column_letter(column)
                coordinate = '%s%s' % (column, row)
        else:
            coordinate = coordinate.upper().replace('$', '')

        if coordinate not in self._cells:
            if row is None or column is None:
                column, row = coordinate_from_string(coordinate)
            self._new_cell(column, row, value)

        return self._cells[coordinate]
示例#46
0
    def _writeMatrix (self,xlsxFilename,xlsxSheetname,contentMatrix,**kwargs):


        def checkFileAndSheet():
            if os.path.isfile(xlsxFilename):
                outWorkbook = load_workbook(xlsxFilename)
                if xlsxSheetname in outWorkbook.get_sheet_names():
                    outSheet = outWorkbook[xlsxSheetname]
                else:
                    outSheet = outWorkbook.create_sheet()
                    outSheet.title = xlsxSheetname
            else:
                outWorkbook = Workbook()
                outWorkbook.remove_sheet(outWorkbook.worksheets[0])
                outSheet = outWorkbook.create_sheet()
                outSheet.title = xlsxSheetname
            return outWorkbook, outSheet

        def writeLine(lineRow,columnOffset,contentList):
            for myColumn in range (1,len(contentList) +1 ):
                myCell = self.outSheet['%s%s'%(get_column_letter(myColumn + columnOffset), lineRow)]
                #print contentList[myColumn - 1 ]   ###FIXME###
                myCell.value = contentList[myColumn - 1 ]
                #myCell.style = cellStyle

        self.outWorkbook, self.outSheet = checkFileAndSheet()

        if 'start' in kwargs.keys():
            startCell = kwargs["start"] 
            startColumn, startRow = coordinate_from_string (startCell)
            rowOffset = startRow - 1
            columnOffset = column_index_from_string(startColumn) - 1
        else: 
            rowOffset = 0
            columnOffset = 0

        for myRowIndex in range (1 ,len(contentMatrix) +1 ):
                writeLine (rowOffset + myRowIndex,columnOffset,contentMatrix[myRowIndex - 1])
        self.outWorkbook.save(filename = xlsxFilename)
        return True
示例#47
0
    def freeze_panes(self, topLeftCell):
        if not topLeftCell:
            topLeftCell = None
        elif isinstance(topLeftCell, str):
            topLeftCell = topLeftCell.upper()
        else:  # Assume a cell
            topLeftCell = topLeftCell.coordinate
        if topLeftCell == 'A1':
            topLeftCell = None

        if not topLeftCell:
            self.sheet_view.pane = None
            return

        if topLeftCell is not None:
            colName, row = coordinate_from_string(topLeftCell)
            column = column_index_from_string(colName)

        view = self.sheet_view
        view.pane = Pane(topLeftCell=topLeftCell,
                        activePane="topRight",
                        state="frozen")
        view.selection[0].pane = "topRight"

        if column > 1:
            view.pane.xSplit = column - 1
        if row > 1:
            view.pane.ySplit = row - 1
            view.pane.activePane = 'bottomLeft'
            view.selection[0].pane = "bottomLeft"
            if column > 1:
                view.selection[0].pane = "bottomRight"
                view.pane.activePane = 'bottomRight'

        if row > 1 and column > 1:
            sel = list(view.selection)
            sel.insert(0, Selection(pane="topRight", activeCell=None, sqref=None))
            sel.insert(1, Selection(pane="bottomLeft", activeCell=None, sqref=None))
            view.selection = sel
示例#48
0
    def __init__(self,row, WBCOLORS, ws,preserve_width=True):
        celllist=[]
        styleList=[]
        for cell in row:
            wcell=wikiCell(cell,WBCOLORS,ws)
            celllist.append(wcell)
            styleList.append(wcell.style)
        # resolve common styles
        self.style = commonStyle(styleList)
        col,rownum=coordinate_from_string(cell.coordinate)
        if preserve_width ==True and rownum==1:
            colwidths = getColumnWidths(ws)
            if colwidths.has_key(col):
                width=colwidths[col]
        else:
            colwidths=None

        self.rowwiki="| "
        cellList=[]
        for cell in celllist:
            if not cell.merged:
                cellList.append(cell.getWikiStr(self.style.keys(),colwidths))
        # also handle the special case of using single pipe when a return character is encountered
        self.rowwiki+=("|| ".join(cellList)+"\n").replace("\n||","\n|") 
示例#49
0
    def _write_comment_shape(self, comment, idx):
        # get zero-indexed coordinates of the comment
        col, row = coordinate_from_string(comment._parent.coordinate)
        row -= 1
        column = column_index_from_string(col) - 1

        style = ("position:absolute; margin-left:59.25pt;"
                 "margin-top:1.5pt;width:%(width)s;height:%(height)s;"
                 "z-index:1;visibility:hidden") % {'height': comment._height,
                                                   'width': comment._width}
        attrs = {
            "id": "_x0000_s%04d" % idx ,
            "type": "#_x0000_t202",
            "style": style,
            "fillcolor": "#ffffe1",
            "{%s}insetmode" % officens: "auto"
        }
        shape = Element("{%s}shape" % vmlns, attrs)

        SubElement(shape, "{%s}fill" % vmlns,
                   {"color2": "#ffffe1"})
        SubElement(shape, "{%s}shadow" % vmlns,
                   {"color": "black", "obscured": "t"})
        SubElement(shape, "{%s}path" % vmlns,
                   {"{%s}connecttype" % officens: "none"})
        textbox = SubElement(shape, "{%s}textbox" % vmlns,
                             {"style": "mso-direction-alt:auto"})
        SubElement(textbox, "div", {"style": "text-align:left"})
        client_data = SubElement(shape, "{%s}ClientData" % excelns,
                                 {"ObjectType": "Note"})
        SubElement(client_data, "{%s}MoveWithCells" % excelns)
        SubElement(client_data, "{%s}SizeWithCells" % excelns)
        SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
        SubElement(client_data, "{%s}Row" % excelns).text = "%d" % row
        SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
        return shape
    def translate_template(self, xml_path, params):
        
        #Counter for the input parameters
        param_counter = 0
        
        #Counter for the segments within pages
        segment_counter = 0
        
        #Wild Card Counter
        wc_counter = 0
        
        #Clear the sheets out of the workbook
        sn = self.wb.get_sheet_names()
        for s in sn:
            self.wb.remove_sheet(self.wb.get_sheet_by_name(s))
        self.wb.create_sheet('Header', 0)
        
        #Connect to the DB
        self.con = lite.connect(self.db_path)
        self.cur = self.con.cursor()
        
        with self.con:
        
            #Read the XML Template
            self.tree = ET.parse(xml_path)
            self.root = self.tree.getroot()
            header_ws = self.wb.get_sheet_by_name('Header')
            for child in self.root:
                if child.tag == 'Header':
                    for row in child:
                        #Process each header row
                        for element in row:
                            #Process each element
                            #We expect two property values:
                            #cell, the first cell of the merge
                            #end_cell, the last cell in the merge
                            if '?' not in element.text:
                                header_ws[element.attrib['start_cell']] = element.text
                            #If a wildcard is encountered, we need to replace it with the
                            #correct parameter
                            else:
                                text = list(element.text)
                                text = self.process_for_wildcard(text, params, wc_counter, param_counter)
                                header_ws[element.attrib['start_cell']] = ''.join(text)
                            self.set_header_font(header_ws[element.attrib['start_cell']])
                            header_ws.merge_cells('%s:%s' % (element.attrib['start_cell'], element.attrib['end_cell']))
                            print('Header element placed')
                elif child.tag == 'Body':
                    #Process the body segment
                    for page in child:
                        #Process each page
                        segment_counter = 0
                        body_ws = self.wb.create_sheet(page.attrib['name'])
                        for segment in page:
                            if segment.tag == 'TestScript':
                                #Execute the specialized Test Script Steps Export
                                #This segment needs to be hardcoded due to the ability to construct
                                #nonlinear workflows.
                                #Here, we process a full testscript and output each workflow in an optimized state
                            
                                start_cell = segment.attrib['cell']
                                flow_list=[]
                                header_list=[]
                                db_column_list=[]
                                for query_segment in segment:
                                    if query_segment.tag == 'Query':
                                        #Execute the query and place the results into the flow list, 
                                        #we need the first column in the query to be workflow.id and this should be unique
                                        if '?' not in query_segment.text:
                                            self.cur.execute(query_segment.text)
                                        #If a wildcard is encountered, we need to replace it with the
                                        #correct parameter
                                        else:
                                            text = list(query_segment.text)
                                            text = self.process_for_wildcard(text, params, wc_counter, param_counter)
                                            self.cur.execute(text)
                                        print('query %s executed' % (text))
                                        workflows = self.cur.fetchall()
                                        
                                        #Find the flow list for the testscript
                                        for workflow in workflows:
                                            flow = self.generate_workflow_export(workflow[0])
                                            flow_list.append(flow)
                                            
                                    if query_segment.tag == 'KeyActionBlock':
                                        for header_segment in query_segment:
                                            if header_segment.tag == 'Column':
                                                header_list.append(header_segment.text)
                                                db_column_list.append(header_segment.text)
                                            elif header_segment.tag == 'Blank':
                                                header_list.append(header_segment.text)
                                        
                                #Find the row and col of the start cell
                                col = Utils.column_index_from_string(start_cell[0])
                                row = int(float(start_cell[1]))
                                
                                for f in flow_list:
                                    #Build the key action trees and store them in memory
                                    f.build_keyactiontree()
                                    
                                    #TO-DO: Write the workflow to the sheet
                                    
                                    #Write the header
                                    self.write_workflow_header(header_list, row, col)
                                    
                                    #TO-DO: Call the Iterator and write out the key actions

                            else:
                                for child in segment:
                                    if child.tag == 'Title':
                                        if '?' not in child.text:
                                            body_ws[segment.attrib['cell']] = child.text
                                        #If a wildcard is encountered, we need to replace it with the
                                        #correct parameter
                                        else:
                                            text = list(child.text)
                                            text = self.process_for_wildcard(text, params, wc_counter, param_counter)
                                            body_ws[segment.attrib['cell']] = ''.join(text)
                                        self.set_header_font(body_ws[segment.attrib['cell']])
                                        print('Data Title element %s placed in cell %s' % (child.text, segment.attrib['cell']))
                                        segment_counter+=1
                                    elif child.tag == 'Header':
                                        i=0
                                        for column in child:
                                            #Place the column header for each query column
                                            cell = Utils.coordinate_from_string(segment.attrib['cell'])
                                            col = Utils.column_index_from_string(cell[0])
                                            body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)] = column.text
                                            self.set_base_font(body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)])
                                            print('Data Header element %s placed in cell %s%s' % (column.text, Utils.get_column_letter(col+i), 2))
                                            i+=1
                                        segment_counter+=1
                                    elif child.tag == 'Query':
                                        #Execute the query and place the results into the page
                                        if '?' not in child.text:
                                            self.cur.execute(child.text)
                                        #If a wildcard is encountered, we need to replace it with the
                                        #correct parameter
                                        else:
                                            text = list(child.text)
                                            text = self.process_for_wildcard(text, params, wc_counter, param_counter)
                                            self.cur.execute(text)
                                        data = self.cur.fetchall()
                                        print('query %s executed' % (text))
                                        i=3
                                        for row in data:
                                            j=0
                                            segment_counter+=1
                                            #Place the data into the report
                                            for e in row:
                                                cell = Utils.coordinate_from_string(segment.attrib['cell'])
                                                col = Utils.column_index_from_string(cell[0])
                                                body_ws['%s%s' % (Utils.get_column_letter(col+j), i)] = e
                                                self.set_base_font(body_ws['%s%s' % (Utils.get_column_letter(col+j), i)])
                                                print('Data Element %s placed in column %s%s' % (e, Utils.get_column_letter(col+i), j))
                                                j+=1
                                            i+=1
            self.wb.save('Export.xlsx')
示例#51
0
    def __init__(self,cell,WBCOLORS,ws):
        colspan=None
        rowspan=None
        self.col,self.rownum=coordinate_from_string(cell.coordinate)
        self.style={}
        self.width=None
#         if rownum == 1:
#             if colwidths.has_key(col):
#                 self.width=colwidths[col]
            
        if cell.coordinate in ws.merged_cells:
            for mrange in ws.merged_cell_ranges:
                if mrange.startswith(cell.coordinate):
                    colspan,rowspan=getColRowSpan(mrange)
                    break
            else:
                self.merged=True
                return
                
        self.cell = cell
        cval=""
            
        try:
            if cell.value == None:
                cval="&nbsp;"
            else:
                if isinstance(cell.value, unicode):
                    cval=cell.value.strip()
                if cell.is_date:
                    cval=self.__doDateFmt()
                elif cell.number_format.endswith("%"):
                    dotind = cell.number_format.find(".")
                    if dotind == -1:
                        fmtStr = "%0d%%"
                    else:
                        fmtStr = "%" + str(dotind) + ".%d"%(len(cell.number_format) - dotind - 2) + "f%%"
                    cval = unicode(fmtStr % (cell.value * 100),'utf-8')
                else:
                    cval=unicode(str(cell.value),'utf-8').strip()
        except:
                cval="" # not sure what to do here
            
        # if value starts with http, then wrap it in []s,otherwise it is used verbatim
        if cval.startswith("http://"):
            cval="["+cval+"]"
        
       # cval=cval.replace(u"\n", "<br/>")
        
        self.value = cval
        self.bg=getCellColor(cell.fill.fgColor, WBCOLORS)
        self.fg=getCellColor(cell.font.color, WBCOLORS)
        if self.fg== "#000000":
            self.fg=None
        self.bold = cell.font.b
        self.italics = cell.font.i
        self.underline = cell.font.u
        self.strike=cell.font.strikethrough
        self.font_name= cell.font.name

        self.style["bg"]=self.bg
        self.style["fg"]=self.fg
        self.style["underline"]=self.underline
        self.style["strike"]=self.strike
        self.style["italics"]=self.italics
        self.style["font_name"]=self.font_name
        self.style["bold"]=self.bold
        self.style["colspan"]=colspan
        self.style["rowspan"]=rowspan
        self.style["width"]=self.width
        self.style["align"]=self.cell.alignment.horizontal
示例#52
0
 def test_roundtrip(self, value):
     wb = Workbook()
     ws = wb.active
     assert ws.point_pos(*ws.cell(value).anchor) == coordinate_from_string(value)
示例#53
0
 def translate_template(self, xml_path, params):
     
     #Counter for the input parameters
     param_counter = 0
     
     #Counter for the segments within pages
     segment_counter = 0
     
     #Wild Card Counter
     wc_counter = 0
     
     #Clear the sheets out of the workbook
     sn = self.wb.get_sheet_names()
     for s in sn:
         self.wb.remove_sheet(self.wb.get_sheet_by_name(s))
     self.wb.create_sheet('Header', 0)
     
     #Connect to the DB
     self.con = lite.connect(self.db_path)
     self.cur = self.con.cursor()
     
     with self.con:
     
         #Read the XML Template
         self.tree = ET.parse(xml_path)
         self.root = self.tree.getroot()
         header_ws = self.wb.get_sheet_by_name('Header')
         for child in self.root:
             if child.tag == 'Header':
                 for row in child:
                     #Process each header row
                     for element in row:
                         #Process each element
                         #We expect two property values:
                         #cell, the first cell of the merge
                         #end_cell, the last cell in the merge
                         if '?' not in element.text:
                             header_ws[element.attrib['start_cell']] = element.text
                         #If a wildcard is encountered, we need to replace it with the
                         #correct parameter
                         else:
                             text = list(element.text)
                             wc_counter = 0
                             for i in text:
                                 if i == '?':
                                     text[wc_counter] = params[int(text[wc_counter + 1])]
                                     print('Parameter %s used' % (params[int(text[wc_counter + 1])]))
                                     del text[wc_counter + 1]
                                     param_counter+=1
                                 wc_counter+=1
                             header_ws[element.attrib['start_cell']] = ''.join(text)
                         if platform.system() == 'Windows':
                             header_ws[element.attrib['start_cell']].font = self.header_font
                             header_ws[element.attrib['start_cell']].fill = self.header_fill
                             header_ws[element.attrib['start_cell']].border = self.header_border
                             header_ws[element.attrib['start_cell']].alignment = self.header_alignment
                             header_ws[element.attrib['start_cell']].number_format = self.header_number_format
                         header_ws.merge_cells('%s:%s' % (element.attrib['start_cell'], element.attrib['end_cell']))
                         print('Header element placed')
             elif child.tag == 'Body':
                 #Process the body segment
                 for page in child:
                     #Process each page
                     segment_counter = 0
                     body_ws = self.wb.create_sheet(page.attrib['name'])
                     for segment in page:
                         if segment.tag == 'TestScriptSteps':
                             #Execute the specialized Test Script Steps Export
                             #This segment needs to be hardcoded due to the ability to construct
                             #nonlinear workflows.
                             #Here, we process a full testscript and output each workflow in an optimized state
                         
                             #We expect the first input parameter to be Test Script, then Project, then Client, 
                             #After those, others can be used and added to the template
                         
                             start_cell = segment.attrib['cell']
                             
                             #Set the parameter counter so that it doesn't break after running this
                             param_counter+=3
                             
                             #Find the workflows associated with the test script
                             self.cur.execute('select wf.id, wf.name from workflow wf left join testscript ts on ts.id = wf.testscriptid) left join project p on ts.projectid = p.id) left join client c on p.clientid = c.id where ts.name = %s and p.name = %s and c.name = %s order by w.id;' % (params[0], params[1], params[2]))
                             workflows = self.cur.fetchall()
                             
                             #Iterate over the cells
                             col = Utils.column_index_from_string(start_cell[0])
                             row = int(float(start_cell[1]))
                             
                             flow_counter = 0
                             
                             for workflow in workflows:
                                 w_row = row + flow_counter
                                 w_col = col
                                 flow_counter += self.generate_workflow_export(workflow[1], params[0], params[1], params[2], body_ws, w_row, w_col)
                         elif segment.tag == 'WorkflowSteps':
                             #Execute a single workflow export
                         
                             start_cell = segment.attrib['cell']
                             
                             col = Utils.column_index_from_string(start_cell[0])
                             row = int(float(start_cell[1]))
                         
                             #We expect the first input parameter to be Test Script, then Project, then Client, then Workflow
                             #After those, others can be used and added to the template
                             self.generate_workflow_export(params[3], params[0], params[1], params[2], row, col)
                         else:
                             for child in segment:
                                 if child.tag == 'Title':
                                     if '?' not in child.text:
                                         body_ws[segment.attrib['cell']] = child.text
                                     #If a wildcard is encountered, we need to replace it with the
                                     #correct parameter
                                     else:
                                         text = list(child.text)
                                         wc_counter = 0
                                         for i in text:
                                             if i == '?':
                                                 text[wc_counter] = params[int(text[wc_counter + 1])]
                                                 print('Parameter %s used' % (params[int(text[wc_counter + 1])]))
                                                 del text[wc_counter + 1]
                                                 param_counter+=1
                                             wc_counter+=1
                                         body_ws[segment.attrib['cell']] = ''.join(text)
                                     if platform.system() == 'Windows':
                                         body_ws[segment.attrib['cell']].font = self.header_font
                                         body_ws[segment.attrib['cell']].fill = self.header_fill
                                         body_ws[segment.attrib['cell']].border = self.header_border
                                         body_ws[segment.attrib['cell']].alignment = self.header_alignment
                                         body_ws[segment.attrib['cell']].number_format = self.header_number_format
                                     print('Data Title element %s placed in cell %s' % (child.text, segment.attrib['cell']))
                                     segment_counter+=1
                                 elif child.tag == 'Header':
                                     i=0
                                     for column in child:
                                         #Place the column header for each query column
                                         cell = Utils.coordinate_from_string(segment.attrib['cell'])
                                         col = Utils.column_index_from_string(cell[0])
                                         body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)] = column.text
                                         if platform.system() == 'Windows':
                                             body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].font = self.base_font
                                             body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].fill = self.base_fill
                                             body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].border = self.base_border
                                             body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].alignment = self.base_alignment
                                             body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].number_format = self.base_number_format
                                         print('Data Header element %s placed in cell %s%s' % (column.text, Utils.get_column_letter(col+i), 2))
                                         i+=1
                                     segment_counter+=1
                                 elif child.tag == 'Query':
                                     #Execute the query and place the results into the page
                                     if '?' not in child.text:
                                         self.cur.execute(child.text)
                                     #If a wildcard is encountered, we need to replace it with the
                                     #correct parameter
                                     else:
                                         text = list(child.text)
                                         wc_counter = 0
                                         for i in text:
                                             if i == '?':
                                                 text[wc_counter] = params[int(text[wc_counter + 1])]
                                                 print('Parameter %s used' % (params[int(text[wc_counter + 1])]))
                                                 del text[wc_counter + 1]
                                                 param_counter+=1
                                             wc_counter+=1
                                         self.cur.execute(''.join(text))
                                     data = self.cur.fetchall()
                                     print('query %s executed' % (child.text))
                                     i=3
                                     for row in data:
                                         j=0
                                         segment_counter+=1
                                         #Place the data into the report
                                         for e in row:
                                             cell = Utils.coordinate_from_string(segment.attrib['cell'])
                                             col = Utils.column_index_from_string(cell[0])
                                             body_ws['%s%s' % (Utils.get_column_letter(col+j), i)] = e
                                             if platform.system() == 'Windows':
                                                 body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].font = self.base_font
                                                 body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].fill = self.base_fill
                                                 body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].border = self.base_border
                                                 body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].alignment = self.base_alignment
                                                 body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].number_format = self.base_number_format
                                             print('Data Element %s placed in column %s%s' % (e, Utils.get_column_letter(col+i), j))
                                             j+=1
                                         i+=1
         self.wb.save('Export.xlsx')
示例#54
0
def Home(request,mid):
    form = TestForm(request.POST or None)
    if form.is_valid():
        n = request.POST['bins']
        s = request.POST['binint']
        try:
            freeze = request.POST['freeze']
        except:
            freeze = False
        bins = []
        if n:
            k = 100/int(n)
            for i in range(0,100,k):
                bins.append(i)

            if not (k*int(n)) == 100:
                bins[-1] = 100
            else:
                bins += [100]
        elif s:
            a = s.split(',')
            arr = map(int,a)
            bins = [0]
            bins +=arr
            bins.append(100)
        else:
            raise Http404
        print bins

        m = Marksheet.objects.get(pk = mid)
        wb = o.load_workbook(filename = m.f.url[1:],data_only=True)
        #sheet = wb.get_sheet_by_name('Sheet1')
        sheet = wb.active
        marks = []
        xy = coordinate_from_string(m.c) # returns ('A',4)
        print xy
        col = column_index_from_string(xy[0]) # returns 1
        row = xy[1]
        for i in range(row,row + m.n):
            if sheet.cell(row = i,column = col).value:
                val = int(sheet.cell(row = i,column = col).value)
                marks.append(val)
            else:
                marks.append(0)
        print "Before3 Plot"
        print marks
        if not freeze:
            plt.hist(marks,bins,histtype= 'bar',rwidth='0.9',alpha = 0.7)
            plt.xlabel('mark')
            plt.ylabel('frequency')
            r = randint(10000,99999)
            plt.savefig('media/images/' +  str(mid) + '.png')
            plt.hold(False)
            return HttpResponseRedirect('/media/images/' +  str(mid) + '.png')
        else:
            grade = []
            if not len(bins) == 10:
                return render_to_response("error.html",{'mid':mid },context_instance = RequestContext(request))
            for mark in marks:
                if mark <= bins[1]:
                    grade.append('NC')
                elif mark <= bins[2]:
                    grade.append('E')
                elif mark <= bins[3]:
                    grade.append('D')
                elif mark <= bins[4]:
                    grade.append('C-')
                elif mark <= bins[5]:
                    grade.append('C')
                elif mark <= bins[6]:
                    grade.append('B-')
                elif mark <= bins[7]:
                    grade.append('B')
                elif mark <= bins[8]:
                    grade.append('A-')
                elif mark <= bins[9]:
                    grade.append('A')
            print len(marks),len(grade)
            sheet.cell(row=row-1,column=col+1,value= "Grades")
            for i,g in enumerate(grade):
                sheet.cell(row = row+i,column = col + 1, value = g)
            wb.save("media/files/graded"+str(mid) + ".xlsx")
            return HttpResponseRedirect('/media/files/graded'+str(mid) +'.xlsx')

    context = {'form': form}
    return render_to_response("home.html",context,context_instance = RequestContext(request))