def setCellColorByCharAndRow(self,stringColumn,row,R=255,G=255,B=255):
        if(utils.column_index_from_string(stringColumn.strip())<0 ):
            raise ColumnIsNotVaild("列 不能小于0");
        if(row<0 ):
            raise ColumnIsNotVaild(str(row)+"不能小于0行");

        if(R==255 and G==255 and B == 255):
            fill = PatternFill(patternType = fills.FILL_NONE,fgColor=Color(rgb=self.rgbConvertToHex(R,G,B)));
        else:
            fill = PatternFill(patternType = fills.FILL_SOLID,fgColor=Color(rgb=self.rgbConvertToHex(R,G,B)));
        stringColumn = str(stringColumn).upper().strip();
        # print("hdsjafaksldf");
        # print(self.mergeCellsColumnAndRow);
        for i in self.mergeCellsCharAndIndex:
            # print("stringcolumn  "+str(stringColumn));
            if stringColumn>=str(i[0]).upper() and stringColumn<=str(i[2]).upper() and row>=i[1] and row <=i[3]:
                for neiHang in range((i[1]),i[3]+1):
                    mybegin = utils.column_index_from_string(i[0]);
                    myend = utils.column_index_from_string(i[2]);
                    for neiCol in range(mybegin,myend+1):
                        self.sheet.cell(row=neiHang,column=neiCol).fill = fill;
                self.wb.save(self.path);
                return;

        self.sheet.cell(str(stringColumn)+str(row)).fill = fill;
        self.wb.save(self.path);
Example #2
0
def read_dimension(source):
    if hasattr(source, "encode"):
        return
    min_row = min_col =  max_row = max_col = None
    DIMENSION_TAG = '{%s}dimension' % SHEET_MAIN_NS
    DATA_TAG = '{%s}sheetData' % SHEET_MAIN_NS
    it = iterparse(source, tag=[DIMENSION_TAG, DATA_TAG])
    for _event, element in it:
        if element.tag == DIMENSION_TAG:
            dim = element.get("ref")
            m = ABSOLUTE_RE.match(dim.upper())
            if m is None:
                return
            min_col, min_row, sep, max_col, max_row = m.groups()
            min_row = int(min_row)
            if max_col is None or max_row is None:
                max_col = min_col
                max_row = min_row
            else:
                max_row = int(max_row)
            return (
                column_index_from_string(min_col),
                min_row,
                column_index_from_string(max_col),
                max_row
                )

        elif element.tag == DATA_TAG:
            # Dimensions missing
            break
        element.clear()
Example #3
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
Example #4
0
def search_data(ws):
    row_start = None
    row_end = None
    column_start = None
    for row in ws.rows:
        for cell in row:
            cell_content = cell.value
            column_index = cell.column
            row_index = cell.row
            if cell_content == KEY_CELL_NAME:
                row_start = row_index
                column_start = column_index
            if (row_start is not None and column_start == cell.column and
                    cell_content is None):

                row_end = cell.row - 1
                if column_start is None or row_start is None:
                    raise RuntimeError('No data found in excel')
                return (row_start, row_end,
                        column_index_from_string(column_start))
    else:
        row_end = cell.row
        if column_start is None or row_start is None:
            raise RuntimeError('No data found in excel')
        return row_start, row_end, column_index_from_string(column_start)
Example #5
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
Example #6
0
    def get_excel_data(self):

        all_transactions = []

        for ws in self.workbook:
            program = ws.cell(row = 1, column = 1).value
            transaction = ws.cell(row = 2, column = 1).value

            transaction_grid = []
            last_field = None


            for row in ws.get_squared_range(2, 4, self.max_columns, self.rows):
                if row[0].value:
                    grid_index = row[0].row - 4
                    transaction_grid.append([])
                    for field in row:
                        if field.value and not last_field:
                            transaction_grid[grid_index].append(field.value)
                        elif not last_field:
                            last_field = utils.column_index_from_string(field.column) - 1
                        elif utils.column_index_from_string(field.column) <= last_field:
                            transaction_grid[grid_index].append(field.value)

            process_rows = []

            for i in range(len(transaction_grid[0])):
                for j in range(len(transaction_grid)):

                    if i == 0 and j > 0:
                        process_rows.append({})
                        process_rows[j-1]['excel_cell'] = {'column': 1, 'row': j + 4}
                    elif j == 0:
                        field_name = transaction_grid[j][i]
                    elif transaction_grid[j][i]:
                        process_rows[j - 1][field_name] = transaction_grid[j][i]


            for process_row in process_rows:
                cell_ref = process_row.pop('excel_cell', {})
                all_transactions.append({'program': program,
                                         'transaction': transaction,
                                         'parameters': process_row,
                                         'result_cell': cell_ref,
                                         'ws': ws
                                         })

        return all_transactions
    def getCellColorByCharAndRow(self,stringColumn,row):
        if(utils.column_index_from_string(stringColumn.strip())<0 ):
            raise ColumnIsNotVaild("列 不能小于0");
        if(row<0 ):
            raise ColumnIsNotVaild(str(row)+"不能小于0行");
        stringColumn = str(stringColumn).upper().strip();
        cell = self.sheet.cell(str(stringColumn)+str(row));
        for i in self.mergeCellsCharAndIndex:
            # print("stringcolumn  "+str(stringColumn));
            if stringColumn>=str(i[0]).upper() and stringColumn<=str(i[2]).upper() and row>=i[1] and row <=i[3]:
                cell = self.sheet.cell(str(i[0])+str(i[1]));
                if(cell.fill.fgColor.type =='rgb'):
                    return self.__colorHexStringToRGB(cell.fill.fgColor.rgb) ;
                elif(cell.fill.fgColor.type =='theme'):
                    fgColor = cell.fill.fgColor;
                    return {'theme':fgColor.value,'persentage':fgColor.tint};
        # for i in self.sheet.cell(str(stringColumn)+str(row)).fill.fgColor:
        #     print (i);
        # print(self.sheet.cell(str(stringColumn)+str(row)).fill.fgColor.tint);


        if(cell.fill.fgColor.type=='rgb'):
            return self.__colorHexStringToRGB(cell.fill.fgColor.rgb);
        elif(cell.fill.fgColor.type=='theme'):
            fgColor = cell.fill.fgColor;
            return {'theme':fgColor.value,'persentage':fgColor.tint};
Example #8
0
def write_cols(worksheet):
    """Write worksheet columns to xml.

    <cols> may never be empty -
    spec says must contain at least one child
    """
    cols = []
    for label, dimension in iteritems(worksheet.column_dimensions):
        col_def = dict(dimension)
        if col_def == {}:
            continue
        idx = column_index_from_string(label)
        cols.append((idx, col_def))

    if not cols:
        return

    el = Element('cols')

    for idx, col_def in sorted(cols):
        v = "%d" % idx
        cmin = col_def.get('min') or v
        cmax = col_def.get('max') or v
        col_def.update({'min': cmin, 'max': cmax})
        el.append(Element('col', col_def))
    return el
Example #9
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)
Example #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)
Example #11
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
Example #12
0
    def anchor(self):
        """ returns the expected position of a cell in pixels from the top-left
            of the sheet. For example, A1 anchor should be (0,0).

            :rtype: tuple(int, int)
        """
        left_columns = (column_index_from_string(self.column) - 1)
        column_dimensions = self.parent.column_dimensions
        left_anchor = 0
        default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH)

        for col_idx in range(left_columns):
            letter = get_column_letter(col_idx + 1)
            if letter in column_dimensions:
                cdw = column_dimensions.get(letter).width or default_width
                if cdw > 0:
                    left_anchor += points_to_pixels(cdw)
                    continue
            left_anchor += default_width

        row_dimensions = self.parent.row_dimensions
        top_anchor = 0
        top_rows = (self.row - 1)
        default_height = points_to_pixels(DEFAULT_ROW_HEIGHT)
        for row_idx in range(1, top_rows + 1):
            if row_idx in row_dimensions:
                rdh = row_dimensions[row_idx].height or default_height
                if rdh > 0:
                    top_anchor += points_to_pixels(rdh)
                    continue
            top_anchor += default_height

        return (left_anchor, top_anchor)
Example #13
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)
Example #14
0
 def __init__(self, col, row, sheet='', excel=None, value=None):
     self.row = row
     self.col = col
     self.col_idx = column_index_from_string(col)
     self.sheet = sheet
     self.excel = excel
     self.address = AddressCell('{}{}'.format(col, row), sheet=sheet)
     self.value = value
Example #15
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)
Example #16
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
Example #17
0
    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)
Example #18
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
Example #19
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)
Example #20
0
def grab_value(sheet, reference, field='title'):
    sel = reference[0]
    if target_titles[sel]['direction'] == 'row':  # reference is (sel, (row, None))
        column = column_index_from_string(target_titles[sel][field])
        return get_merged_cell(sheet, row=reference[1][0], col=column)
    elif target_titles[sel]['direction'] == 'column':  # reference is (sel, (None, col))
        return get_merged_cell(sheet, row=target_titles[sel][field], col=reference[1][1])
    else:
        raise ValueError
Example #21
0
    def get_highest_column(self):
        """Get the largest value for column currently stored.

        :rtype: int
        """
        if self.column_dimensions:
            return max([column_index_from_string(column_index)
                        for column_index in self.column_dimensions])
        else:
            return 1
Example #22
0
def __copy_col_cells(in_ws, out_ws, in_data_range, out_col, has_header=True):

    out_col_index = column_index_from_string(out_col)
    row_offset = 2 if has_header else 1

    for (i,row) in enumerate(in_ws.iter_rows(in_data_range)):
        for cell in row:
            out_ws.cell(row=i+row_offset,column=out_col_index,value=cell.value)

    return out_ws
Example #23
0
 def _calculate_dimension(self):
     """
     Loop through all the cells to get the size of a worksheet.
     Do this only if it is explicitly requested.
     """
     max_col = 0
     for r in self.rows:
         cell = r[-1]
         max_col = max(max_col, column_index_from_string(cell.column))
     self.max_row = cell.row
     self.max_col = get_column_letter(max_col)
def get_cell_range(start_cell, num_row = 1, num_col = 1):
    start_cell = start_cell.upper()
    m = re.match(r"([a-zA-Z]+)([0-9]+)", start_cell)
    letter_value = m.group(1)
    number_value = m.group(2)
    start_addr = "$" + letter_value + "$" + number_value
    letter_value = get_column_letter(
        column_index_from_string(letter_value) + num_col - 1)
    number_value = str(eval(number_value) + num_row - 1)
    end_addr = "$" + letter_value + "$" + number_value
    ref_addr = start_addr + ":" + end_addr
    return ref_addr
Example #25
0
 def translate_col(col_str, cdelta):
     """
     Translate a range col-snippet by the given number of columns
     """
     if col_str.startswith('$'):
         return col_str
     else:
         try:
             return get_column_letter(
                 column_index_from_string(col_str) + cdelta)
         except ValueError:
             raise TranslatorError("Formula out of range")
Example #26
0
 def autoscale_col_width(self, sheet, column):
     if type(column) is int:
         column_letter = get_column_letter(column)
         column_id = column
     else:
         column_letter = column
         column_id = column_index_from_string(column)
     cells_len = []
     for row_id in range(1, len(sheet.rows)+1):
         cell = sheet.cell(row=row_id, column=column_id)
         if cell.value:
             cells_len.append(len(cell.value))
     sheet.column_dimensions[column_letter].width = max(cells_len) * 1.5
Example #27
0
 def __init__(self, worksheet, column=None, row=None, value=None, col_idx=None, style_array=None):
     super(Cell, self).__init__(worksheet, style_array)
     self.row = row
     # _value is the stored value, while value is the displayed value
     self._value = None
     self._hyperlink = None
     self.data_type = 'n'
     if value is not None:
         self.value = value
     self._comment = None
     if column is not None:
         col_idx = column_index_from_string(column)
     self.col_idx = col_idx
    def offset(self, row=0, column=0):
        """Returns a cell location relative to this cell.

        :param row: number of rows to offset
        :type row: int

        :param column: number of columns to offset
        :type column: int

        :rtype: :class:`openpyxl.cell.Cell`
        """
        offset_column = column_index_from_string(self.column) + column
        offset_row = self.row + row
        return self.parent.cell(column=offset_column, row=offset_row)
Example #29
0
 def anchor(self, cell, anchortype="absolute"):
     """ anchors the image to the given cell
         optional parameter anchortype supports 'absolute' or 'oneCell'"""
     self.drawing.anchortype = anchortype
     if anchortype == "absolute":
         self.drawing.left, self.drawing.top = cell.anchor
         return ((cell.column, cell.row),
                 cell.parent.point_pos(self.drawing.top + self.drawing.height,
                                       self.drawing.left + self.drawing.width))
     elif anchortype == "oneCell":
         self.drawing.anchorcol = column_index_from_string(cell.column) - 1
         self.drawing.anchorrow = cell.row - 1
         return ((self.drawing.anchorcol, self.drawing.anchorrow), None)
     else:
         raise ValueError("unknown anchortype %s" % anchortype)
Example #30
0
    def append(self, iterable):
        """Appends a group of values at the bottom of the current sheet.

        * If it's a list: all values are added in order, starting from the first column
        * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)

        :param iterable: list, range or generator, or dict containing values to append
        :type iterable: list|tuple|range|generator or dict

        Usage:

        * append(['This is A1', 'This is B1', 'This is C1'])
        * **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
        * **or** append({1 : 'This is A1', 3 : 'This is C1'})

        :raise: TypeError when iterable is neither a list/tuple nor a dict

        """
        row_idx = self._current_row + 1

        if (isinstance(iterable, (list, tuple, range))
            or isgenerator(iterable)):
            for col_idx, content in enumerate(iterable, 1):
                if isinstance(content, Cell):
                    # compatible with write-only mode
                    cell = content
                    if cell.parent and cell.parent != self:
                        raise ValueError("Cells cannot be copied from other worksheets")
                    cell.parent = self
                    cell.col_idx = col_idx
                    cell.row = row_idx
                else:
                    cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
                self._cells[(row_idx, col_idx)] = cell

        elif isinstance(iterable, dict):
            for col_idx, content in iterable.items():
                if isinstance(col_idx, basestring):
                    col_idx = column_index_from_string(col_idx)
                cell = Cell(self, row=row_idx, col_idx=col_idx, value=content)
                self._cells[(row_idx, col_idx)] = cell

        else:
            self._invalid_row(iterable)

        self._current_row = row_idx
Example #31
0
def write2excel(SheetTitle: str, MSGTestReportList: list,
                PathName: str) -> None:
    wb = Workbook()

    sheet = wb.worksheets[0]

    sheet.title = SheetTitle
    for tmp in MSGTestReportList:
        sheet.append(tmp)
    for i in range(sheet.max_row):
        for j in range(column_index_from_string('M')):
            if sheet[get_column_letter(j + 1) + str(i + 1)].value == "None":
                sheet[get_column_letter(j + 1) + str(i + 1)].value = ''

    #font = Font(color = colors.RED)
    #sheet["A1"].font = font
    wb.save(PathName)
Example #32
0
    def append(self, iterable):
        """Appends a group of values at the bottom of the current sheet.

        * If it's a list: all values are added in order, starting from the first column
        * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)

        :param iterable: list, range or generator, or dict containing values to append
        :type iterable: list|tuple|range|generator or dict

        Usage:

        * append(['This is A1', 'This is B1', 'This is C1'])
        * **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
        * **or** append({1 : 'This is A1', 3 : 'This is C1'})

        :raise: TypeError when iterable is neither a list/tuple nor a dict

        """
        row_idx = self._current_row + 1

        if (isinstance(iterable, (list, tuple, range))
            or isgenerator(iterable)):
            for col_idx, content in enumerate(iterable, 1):
                if isinstance(content, Cell):
                    # compatible with write-only mode
                    cell = content
                    if cell.parent and cell.parent != self:
                        raise ValueError("Cells cannot be copied from other worksheets")
                    cell.parent = self
                    cell.column = col_idx
                    cell.row = row_idx
                else:
                    cell = Cell(self, row=row_idx, column=col_idx, value=content)
                self._cells[(row_idx, col_idx)] = cell

        elif isinstance(iterable, dict):
            for col_idx, content in iterable.items():
                if isinstance(col_idx, basestring):
                    col_idx = column_index_from_string(col_idx)
                cell = Cell(self, row=row_idx, column=col_idx, value=content)
                self._cells[(row_idx, col_idx)] = cell

        else:
            self._invalid_row(iterable)

        self._current_row = row_idx
Example #33
0
def keywordExtraction(path, columnName):
    # jieba分词提取关键词
    jieba.load_userdict("dict.txt")  # 加载字典

    wb = load_workbook(path)
    sheet = wb.active

    # 这个算法好像没tf-idf好,好像
    #textRank = analyse.textrank

    countItems = 2
    contentInColumn = str(columnName)  # 内容所在列
    contentTheOutputColumns = get_column_letter(
        column_index_from_string(contentInColumn) + 1)
    sheet["{}1".format(contentTheOutputColumns)] = "MachineToExtract"  # 内容输出列
    for i in sheet["{0}2:{1}{2}".format(contentInColumn, contentInColumn,
                                        sheet.max_row)]:
        for j in i:

            # 这段提取标签
            #keywords = textRank(j.value)
            try:
                # 到底用什么词性才是最贴切呢?
                keywords = jieba.analyse.extract_tags(j.value,
                                                      3,
                                                      allowPOS=('n', 'v', 'a'))
                keywordsConvert = " ".join(keywords)
                print(j.value, keywords)
                sheet["{0}{1}".format(contentTheOutputColumns,
                                      countItems)].value = keywordsConvert
            except:
                pass
            countItems += 1
            #for keyword in keywords:
            #    print(j.value,keyword)

            # 这段用来比较句子的相似程度,但是词库缺乏词条
            #for i2 in sheet["{}{}:{}{}".format(contentInColumn,j.row+1,contentInColumn,sheet.max_row)]:
            #    for j2 in i2:
            #        r = synonyms.compare(j.value,j2.value)
            #        if r>0.7:
            #            sheet.delete_rows(j2.row,1)
            #print(j.value)

    wb.save(filename=path)
Example #34
0
    def append(self, iterable):
        """Appends a group of values at the bottom of the current sheet.

        * If it's a list: all values are added in order, starting from the first column
        * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)

        :param iterable: list, range or generator, or dict containing values to append
        :type iterable: list/tuple/range/generator or dict

        Usage:

        * append(['This is A1', 'This is B1', 'This is C1'])
        * **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
        * **or** append({1 : 'This is A1', 3 : 'This is C1'})

        :raise: TypeError when iterable is neither a list/tuple nor a dict

        """
        row_idx = self.max_row + 1

        if (isinstance(iterable, (list, tuple, range))
                or isgenerator(iterable)):
            for col_idx, content in enumerate(iterable, 1):
                col = get_column_letter(col_idx)
                if isinstance(content, Cell):
                    # compatible with write-only mode
                    cell = content
                    cell.parent = self
                    cell.column = col
                    cell.row = row_idx
                    cell.coordinate = "%s%s" % (col, row_idx)
                    self._add_cell(cell)
                else:
                    cell = self._new_cell(col, row_idx, content)

        elif isinstance(iterable, dict):
            for col_idx, content in iteritems(iterable):
                if isinstance(col_idx, basestring):
                    col_idx = column_index_from_string(col_idx)
                self.cell(row=row_idx, column=col_idx, value=content)

        else:
            self._invalid_row(iterable)
        self.row_dimensions[row_idx] = RowDimension(worksheet=self,
                                                    index=row_idx)
Example #35
0
def getTableHeaders(filepath):
    wb = load_workbook(filename=filepath, read_only=True)
    sheet = wb.active
    col_headings = []
    index = 0
    dim = sheet.calculate_dimension()
    dim = dim.split(':')
    xy = coordinate_from_string(dim[1])
    max_col = column_index_from_string(xy[0])
    for row in sheet.iter_rows(min_row=1,
                               min_col=1,
                               max_row=1,
                               max_col=max_col):
        for cell in row:
            index += 1
            if cell.value != None:
                col_headings.append((index, cell.value))
    return col_headings
Example #36
0
def resolve(
        d
):  #Function: erase marks from same column, row and box as given number
    change = True
    x = column_index_from_string(
        d[0]) - 1  #converts xcel x coord to marks x coord
    xstart = x // 3 * 3  #y conversion
    y = int(d[1]) - 1
    ystart = y // 3 * 3
    boxes[(y // 3) * 3 + x // 3] = boxes[(y // 3) * 3 + x // 3].replace(
        str(sheets[coords].value), '')
    rows[y] = rows[y].replace(str(sheets[coords].value), '')
    columns[x] = columns[x].replace(str(sheets[coords].value), '')
    marks[x][y] = ''  #Erases all marks in box with known value
    for i in range(0, 9):
        remove(i, y, d)  #Row
        remove(x, i, d)  #Column
        remove(xstart + i % 3, ystart + i // 3, d)  #Box
Example #37
0
 def build_des_column_data_all(self):
     LineNumber = 1
     diag_LineNumber = 1
     for num in range(len(self.src_row_data_all)):
         if self.src_row_data_all[num][column_index_from_string('Q') -
                                       1] != 'Y':
             TxChList, ChangeIDList = self.get_TxChannal(num)
             #print(TxChList)
             if len(TxChList) > 0:
                 for index, TxCh in enumerate(TxChList):
                     self.des_column_data_all.append(
                         self.build_des_column_data(LineNumber, TxCh, num,
                                                    ChangeIDList[index]))
                     LineNumber += 1
             else:
                 self.des_column_data_all.append(
                     self.build_des_column_data(LineNumber, 0, num, '0'))
                 LineNumber += 1
Example #38
0
def load_from_workbook(wb):
    ret = excel_config()
    ws_data = [ws for ws in wb.worksheets if ws.title == "data"]
    if ws_data.__len__() == 0:
        raise (Exception("Invalid workbook. The workbook must contains one worksheet with name is 'data'"))
    ret.workbook = wb
    ret.data_sheet = ws_data[0]
    ret.names = []
    for x in wb.defined_names.definedName:
        from openpyxl.utils import coordinate_from_string, column_index_from_string
        if hasattr(x, "name") and x.value!='data!#REF!':
            item = excel_name_range()
            item.address = x.value
            item.name = x.name
            item.col = column_index_from_string(x.value.split("!")[1].split(":")[0].replace("$", "")) - 1
            ret.names.append(item)
    ret.names = sorted(ret.names, key=lambda x: x.name)
    return ret
Example #39
0
def xlsx_to_txt(file):
    """Converts the given excel spreadsheet to text files. Splits into files for each column."""

    wb = openpyxl.load_workbook(file)

    workingsheet = wb.active

    maxcolumn_letter = get_column_letter(workingsheet.max_column)

    maxcolumn_index = column_index_from_string(maxcolumn_letter)

    # Loops over every column with content and writes each individual column to a file.
    # TODO - Make this work for rows too, but not sure how I'd detect that. Maybe ask the user?
    for i in range(1, maxcolumn_index + 1):
        with open("output/column{0}.txt".format(i), "w") as f:
            for cell in workingsheet[get_column_letter(i)]:
                f.write(str(cell.value))
                f.write("\n")
 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)
Example #41
0
def _apply_styles_to_ws(ws, column_styles=None, cell_styles=None):
    # dict de las columnas que corresponden a cada campo
    header_row = next(ws.rows)
    headers_cols = {cell.value: cell.column for cell in header_row}

    # aplica estilos de columnas
    if column_styles:
        for col, properties in iteritems(column_styles):
            # la col puede ser "A" o "nombre_campo"
            col = headers_cols.get(col, col)
            for prop_name, prop_value in iteritems(properties):
                setattr(ws.column_dimensions[col], prop_name, prop_value)

    # aplica estilos de celdas
    if cell_styles:
        for i in moves.xrange(1, ws.max_row + 1):
            for j in moves.xrange(1, ws.max_column + 1):
                cell = ws.cell(row=i, column=j)

                # si el valor es una URL válida, la celda es un hyperlink
                if helpers.validate_url(cell.value):
                    cell.hyperlink = cell.value
                    cell.font = Font(underline='single', color='0563C1')

                for cell_style in cell_styles:
                    match_all = (
                        "col" not in cell_style and
                        "row" not in cell_style
                    )
                    match_row = (
                        "row" in cell_style and
                        cell_style["row"] == i
                    )
                    match_col = (
                        "col" in cell_style and
                        column_index_from_string(
                            headers_cols.get(cell_style["col"],
                                             cell_style["col"])) == j
                    )
                    if match_all or match_row or match_col:
                        for prop_name, prop_value in iteritems(cell_style):
                            if prop_name != "col" and prop_name != "row":
                                setattr(cell, prop_name, prop_value)
Example #42
0
    def set_title(self, field, pattern_fill=None, font=None, alignment=None,
                  new_line=True, passed_title=None, merge_list=None):
        """
        設定資料的 title, 會上底色以及字體加粗
        :param field:
        :param pattern_fill:
        :param font:
        :param alignment:
        :param new_line:
        :param passed_title:
        :param merge_list:
        :return:
        """
        next_col_index = None
        title = passed_title or self.titles[field]
        _pattern_fill = pattern_fill or self.pattern_fill
        _font = font or self.font
        # _alignment = alignment or self.alignment

        # 如果有要 merge 欄位
        if merge_list is not None:
            self.__set_column_merge(merge_list)

            # merge 過後的欄位, 只有合併欄位的第一欄能給值
            # e.g. A 與 B 欄合併, 只有 A 欄能給值, 若嘗試給 B 欄值會 Error
            # AB欄合併後,下一個能給值的欄位就是C欄, 若C欄合併到E欄
            # CDE欄只有C欄能給值,接著下一個能給值的欄位是F欄
            # merge_list 每個元素是 Tuple, tuple[0] 為合併欄位的起始欄位
            for val, col_index in zip(title, [column_index_from_string(t[0]) for t in merge_list]):
                if alignment is not None:
                    self.set_cell(self.row_index, col_index, val, _pattern_fill, _font, alignment)
                else:
                    self.set_cell(self.row_index, col_index, val, _pattern_fill, _font)
                next_col_index = col_index
        else:
            for col_index, val in enumerate(title, start=1):
                self.set_cell(self.row_index, col_index, val, _pattern_fill, _font, alignment)
                next_col_index = col_index

        if new_line:
            self.row_index = 1

        return next_col_index + 1
Example #43
0
    def set_sample_info(self, field, data):
        """
        設定調查名冊戶長的基本資料
        :param field:
        :param data:
        :return:
        """
        merge_list = [('A', 'B'), ('C', 'C'), ('D', 'D'), ('E', 'E'),
                      ('F', 'G'), ('H', 'I'), ('J', 'Q')]
        col_idex_list = [column_index_from_string(t[0]) for t in merge_list]
        self.set_title(field, merge_list=merge_list)

        # 因為 Excel 不需要寫入 ID 因此先 pop 出來
        data.pop('id')
        for val, col_index in zip(data.values(), col_idex_list):
            self.set_cell(row=self.row_index, col=col_index, value=val)

        self.__set_column_merge(merge_list)
        self.row_index = 2
Example #44
0
    def styling_cells(self, ws: Worksheet, columns: ColumnPattern):
        """Applies the referenced style to the cells of a column.

        :param Worksheet ws: worksheet into write headers
        :param ColumnPattern columns: column table
        """
        # wrap
        for v in columns.values():
            # ignore empties columns
            if v.letter is None or v.style is None:
                continue
            # apply wrap style depending on value
            col_idx = column_index_from_string(v.letter)
            for row_cols in ws.iter_rows(min_row=2,
                                         min_col=col_idx,
                                         max_col=col_idx):
                for cell in row_cols:
                    # print(cell.style)
                    cell.style = v.style
Example #45
0
 def __init__(self,
              worksheet,
              column=None,
              row=None,
              value=None,
              col_idx=None,
              style_array=None):
     super(Cell, self).__init__(worksheet, style_array)
     self.row = row
     # _value is the stored value, while value is the displayed value
     self._value = None
     self._hyperlink = None
     self.data_type = 'n'
     if value is not None:
         self.value = value
     self._comment = None
     if column is not None:
         col_idx = column_index_from_string(column)
     self.col_idx = col_idx
Example #46
0
def loadxlsxTest():
    wb = openpyxl.load_workbook('./automate_online-materials/example.xlsx')
    print(type(wb))
    print(wb.get_sheet_names())

    # 每个表由一个 Worksheet 对象表示,可以通过向工作簿方法 get_sheet_by_name()传递表名字符串获得
    sheet = wb.get_sheet_by_name('Sheet1')
    print(sheet)

    # 可以通过 title 属性取得它的名称
    print(sheet.title)
    # 取得工作簿的活动表
    antherSheet = wb.get_active_sheet()
    print(antherSheet)

    print(sheet['A1'])
    print(sheet['A1'].value)

    c = sheet['B1']
    print(c.value)
    print('Row ' + str(c.row) + ', Column ' + ' is  ' + str(c.column) +
          str(c.value))
    print('Cell ' + str(c.coordinate) + ' is ' + str(c.value))

    # 可以通过 Worksheet 对象的 get_highest_row()和 get_highest_column()方法,确定 表的大小
    # get_highest_row()和get_highest_column()在最新版的openpyxl模块中已经被删除了,取而代之的是max_row和max_column两个方法
    rows = sheet.max_row
    colunms = sheet.max_column
    print(colunms)
    for i in range(1, rows, 2):
        print(i, sheet.cell(row=i, column=2).value)

    print(get_column_letter(900))
    print(get_column_letter(colunms))
    print(column_index_from_string('AA'))

    # 取得电子表格中一行、一列或一个矩形区域中的所有 Cell 对象
    print(tuple(sheet['A1':'C3']))
    for rowOfCellObjects in sheet['A1':'C3']:
        for cellObj in rowOfCellObjects:
            print(cellObj.coordinate, cellObj.value)
        print('--- END OF ROW ---')
Example #47
0
 def _getCells(self, strCell):
     if type(strCell) == str:
         expTemp = strCell.upper();
         if ":" in expTemp:
             min_col, min_row, max_col, max_row = utils.cell.range_boundaries(expTemp);
             return (min_col-1, min_row-1, max_col, max_row);
         else:
             column,row=utils.cell.coordinate_from_string(expTemp);
             column = utils.column_index_from_string(column);
             return (column-1, row-1);
     elif type(strCell) == list and len(strCell) == 2:
         valueTemp = strCell[0];
         if type(valueTemp) == int:
             return (strCell[1]-1,strCell[0]-1);
         elif type(valueTemp) == list:
             return (strCell[0][1]-1, strCell[0][0]-1, strCell[1][1], strCell[1][0]);
         else:
             self._getException(strCell)
     else:
         self._getException(strCell);
Example #48
0
 def update_mysheet(self, wb):
     ws = wb.active
     for row in ws.rows:
         if not isinstance(row[0].value, str): continue
         key = key_map.get(row[0].value.strip())
         if not key: continue
         datarow = self.calculate_datarow(key, row[0].value)
         for idx, datacol in enumerate(datarow):
             if not idx: continue
             cell = row[idx + 1]
             col = column_index_from_string(cell.column)
             if type(datacol) != float:
                 newcell = ws.cell(row=cell.row,
                                   column=col,
                                   value=float(datacol.replace(',', '')))
             else:
                 newcell = ws.cell(row=cell.row,
                                   column=col,
                                   value=float(datacol))
                 self.copy_cellformat(cell, newcell)
Example #49
0
    def load_selector(self, selector_csv_path):
        selector = OutputDataSelector(selector_csv_path)
        xl_labels = [i.value for i in self.data_sheet[self.label_row]]
        selector_index = xl_labels.index(selector.label)
        selector_index_letter = get_column_letter(selector_index+1)

        xl_selector_col = self.data_sheet[selector_index_letter]
        xl_selector_col_str = [str(i.value) for i in xl_selector_col]

        row_selected = []
        for select_set in selector.selectors:
            data_set = []
            for i in select_set:
                i_index = xl_selector_col_str.index(i)
                data_set.append(xl_selector_col[i_index].row)
            row_selected.append(data_set)

        data_column_range = [column_index_from_string(i) for i in selector.data_cols_range]

        return [data_column_range, row_selected]
Example #50
0
 def _write_leaguesheet_headers(self, worksheet, no_of_weeks):
     worksheet['A2'].value = "Name"
     worksheet['B2'].value = "Weeks Played"
     worksheet['A2'].fill = self.blue_fill
     worksheet['B2'].fill = self.blue_fill
     for week in range(1, no_of_weeks + 1):
         week_header_cell = worksheet.cell(row=2, column=week + 2)
         week_header_cell.value = week
         week_header_cell.fill = self.blue_fill
         week_header_cell.alignment = self.center_alignment
         worksheet.column_dimensions[week_header_cell.column].width = 3
         last_column = column_index_from_string(week_header_cell.column)
     worksheet.merge_cells(start_row=1,
                           start_column=3,
                           end_row=1,
                           end_column=last_column)
     week_title_cell = worksheet.cell(row=1, column=3)
     week_title_cell.value = "Weeks"
     week_title_cell.fill = self.orange_fill
     week_title_cell.alignment = self.center_alignment
Example #51
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
Example #52
0
    def update_user_bonuses(self, nickname_row, session_column):

        how_much_to_go_back = 6

        for col in self.sheet.iter_cols(min_col=session_column,
                                        min_row=nickname_row,
                                        max_row=nickname_row):

            cell = col[0]

            if column_index_from_string(
                    cell.column) - session_column >= how_much_to_go_back:
                break

            #skip if colored
            if cell.fill.start_color.index != self.cell_no_bonus_color:
                break
            #if has value, start counting
            count = 0
            if cell.value:
                count += cell.value
Example #53
0
def count(arr, c):
    # 科学学位定向单考统计
    middle = []
    for index, v in enumerate(colleges):
        middle.append(0)
    # del middle[0]
    # print(len(middle))
    for i in arr:
        j = sheet_read.cell(row=i, column=column_index_from_string('F'))
        for index, v in enumerate(colleges):
            if j.value == v:
                middle[index] += 1
    # 从 A4 开始,前面三行标题信息
    # print(middle)
    for i, v in enumerate(middle):
        if v == 0:
            middle[i] = ''
    for i, v in enumerate(middle):
        # print(i, v)
        # 从 A4 开始,前面三行标题信息
        sheet_write[(c + "%d") % (i + 4)].value = v
Example #54
0
def setstyle(PathName: str, SheetTitle: str) -> None:
    # 默认可读写,若有需要可以指定write_only和read_only为True
    wb = load_workbook(PathName)
    sheet = wb[SheetTitle]
    for i in range(sheet.max_row):
        for j in range(column_index_from_string('U')):
            sheet[get_column_letter(j + 1) + str(i + 1)].alignment = Alignment(
                horizontal='center', vertical='center')
            sheet[get_column_letter(j + 1) +
                  str(i + 1)].font = Font(name='Times New Roman')
            if i < 2:
                sheet[get_column_letter(j + 1) + str(i + 1)].font = Font(
                    name='楷体', size=12, bold=True)
        if sheet['U' + str(i + 1)].value == "Success":
            sheet['U' + str(i + 1)].fill = styles.fills.GradientFill(
                stop=["00FF00", "00FF00"])
            sheet[get_column_letter(j) + str(i + 1)].font = Font(size=12,
                                                                 bold=True)
        elif sheet['U' + str(i + 1)].value == "Fail":
            sheet['U' + str(i + 1)].fill = styles.fills.GradientFill(
                stop=["FF0000", "FF0000"])
            sheet[get_column_letter(j + 1) + str(i + 1)].font = Font(size=12,
                                                                     bold=True)

    #设置行高
    for i in range(sheet.max_row):
        sheet.row_dimensions[i + 1].height = 18

    #设置列宽
    sheet.column_dimensions['B'].width = 20
    for i in range(column_index_from_string('G'),
                   column_index_from_string('U')):
        sheet.column_dimensions[get_column_letter(i + 1)].width = 10
    sheet.column_dimensions['U'].width = 15

    #合并单元格
    for i in range(column_index_from_string('H')):
        sheet.merge_cells(
            get_column_letter(i + 1) + '1:' + get_column_letter(i + 1) + '2')
    for i in range(column_index_from_string('L'),
                   column_index_from_string('P')):
        sheet.merge_cells(
            get_column_letter(i + 1) + '1:' + get_column_letter(i + 1) + '2')
    sheet.merge_cells("I1:L1")
    sheet.merge_cells("Q1:T1")
    sheet.merge_cells("U1:U2")

    wb.save(PathName)
Example #55
0
def take_info_bridge():
    info_original_1.clear()
    for i in range(0, len(source_sheet3_info_loc)):
        val = sheet_doc_3[source_sheet3_info_loc[i]].value
        print("val",val)
        if type(val) == str:
            val = val.replace('\n', '')

        if val == None and '2018年桥梁记录表(昆西)/武昆(176座)' in open_contentfile:
            loc_col = re.sub(r'\d', '', source_sheet3_info_loc[i])
            loc_col = column_index_from_string(loc_col)
            loc_row = re.sub(r'[A-Z]', '', source_sheet3_info_loc[i])
            loc_row = int(loc_row) + 1
            val = sheet_doc_3.cell(row=loc_row, column=loc_col).value

        if val == None and open_contentfile == '2018年桥梁记录表(昆西)/永武(433座)/K2611+051上行线(波纹管外露).xlsx':
            val = '4×20m+4×19m+18.5m+24m+18.5m现浇箱梁'
        if type(val) == str:
            val = val.replace(' ', '')
        info_original_1.append(val)
    return info_original_1
Example #56
0
    def find_session_column(self, id):
        """Find column where to place current session."""
        # check if it exists already
        # place it if it doesn't
        tokens_first_column = self.nicknames_column + 1
        session_column = None
        new_column_placement = tokens_first_column

        for col in self.sheet.iter_cols(min_col=tokens_first_column,
                                        max_row=1):
            column_session_number = int(col[0].value.replace('#', ''))
            current_column_index = column_index_from_string(col[0].column)

            if id < column_session_number:
                new_column_placement = current_column_index + 1

            if column_session_number == int(id):
                session_column = current_column_index
                break

        return session_column, new_column_placement
Example #57
0
def rows_with_data(df_avancor, banks, all_razdels):
    """ Находим номера строк с данными, которые будем копировать"""

    avancoreTitle = '1.1. Виды и суммы операций, совершенных по банковским счетам некредитной \n' \
                    'финансовой организации'
    col = 'B'

    # файл-Аванкор: интервал строк, в пределах которых ищем строки с разделами
    row_start = 32
    row_end = 91

    # Кол-во строк и столбцов в файле-Аванкор
    index_max = df_avancor.shape[0]
    collumn_max = df_avancor.shape[1]

    title_row = razdel_name_row(df_avancor,
                                avancoreTitle,
                                title_col=column_index_from_string(col),
                                row_start=row_start,
                                row_end=row_end)

    # номер строки с названиями валют
    row_currency = title_row[0] + 3

    # номера строк с разделами
    rows_of_title = rows_of_razdel(df_avancor,
                                   all_razdels,
                                   col='D',
                                   row_start=row_start,
                                   row_end=row_end)
    rows_itogi = rows_of_razdel(df_avancor,
                                all_razdels,
                                col='B',
                                row_start=row_start,
                                row_end=row_end,
                                rows_under=False)
    # устанавливаем нижнюю строку последнего раздела перед итогами
    rows_of_title[-1]['row_razdel_end'] = rows_itogi[0]['row_razdel_begin'] - 1

    return rows_of_title
Example #58
0
    def addsheet (self, hols):
        ws = self._month.tag ()
        self._table.copy_sheet (self._wsMonth, ws)
        # 1st row
        self._table.setCell (CONF.ATTENDANCE.attendance_cell_monthM,
                "%s %04d" % (str (self._month), self._month.year ()), sheet=ws)
        col0x = column_index_from_string (self._daystartcol) - 1
        # Build a list of style items for the space to fill, according to the calendar
        year = self._month.year ()
        mon = self._month.month ()
        stlist = []
        for day in range (33):
            col = get_column_letter (col0x + day)
            try:
                date = datetime.date (year, mon, day)
                if date.weekday () > 4:
                    # Weekend
                    stlist.append (self._st_W)
                elif date in hols:
                    # Holiday weekday
                    stlist.append (self._st_F)
                else:
                    # A normal schoolday
                    stlist.append (self._st_N)
                    self._table.setCell (col + self._validdaysrow, "+" , sheet=ws)
            except:
                # Invalid date (or other "error" ...)
                stlist.append (self._st_X)

        # For each pupil, style the attendance cells, and the margin cells
        for row in range (self._row0, self._rowlimit):
            col = col0x
            for st in stlist:
                self._table.setCell (get_column_letter (col) + str (row),
                        style = st, sheet = ws)
                col += 1

        self._table.page_setup (ws, landscape=True, fitHeight=True, fitWidth=True)
        self._month.increment ()
        return ws
Example #59
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
    def _resolve_cell_coordinates(self, locator):
        """Resolves the cell coordinates based on several possible forms
        of `locator` parameter.

        Currently supports the following locator forms:
        - __Coordinates__
        - __A1 Notation__

        For more detail see the keyword documentation of the `Read From Cell`
        keyword.
        """

        locator = locator.strip()
        if "," in locator:  # Coordinates.
            if locator.startswith("coords:"):
                locator = locator[7:]
            locator = locator.lstrip('(').rstrip(')')
            coords_parts = locator\
                           .lstrip('(')\
                           .rstrip(')')\
                           .split(',')
            row_nr, col_nr = coords_parts[1].strip(),\
                             coords_parts[0].strip()

        else:  # Assume A1 notation.
            if locator.startswith("a1:"):
                locator = locator[3:]

            a1_col = ""
            a1_row = ""
            for char in locator:
                if char.isdigit():
                    a1_row += char
                else:
                    a1_col += char

            col_nr = column_index_from_string(a1_col)
            row_nr = a1_row

        return int(row_nr), int(col_nr)