Exemple #1
0
 def getXlsDataStartOffset(cls,
                           file,
                           startCell,
                           verticalOffset,
                           horizontalOffset,
                           list_of_lists=False):
     wb = openpyxl.load_workbook(file)
     worksheet = wb.active
     startCol = coordinate_to_tuple(startCell)[1]
     startRow = coordinate_to_tuple(startCell)[0]
     endRow = startRow + verticalOffset
     endCol = startCol + horizontalOffset
     endCell = get_column_letter(endCol) + str(endRow)
     rng = worksheet[startCell:endCell]  #str:str
     data = []
     if list_of_lists:
         for tuple in rng:
             row = ()
             for col in tuple:
                 row = row + (col.value, )
             data.append(list(row))
     else:
         for tuple in rng:
             row = ()
             for col in tuple:
                 row = row + (col.value, )
             data.append(row)
     return data
Exemple #2
0
 def iterateRowTillFirstValueCell(cls, startCell, file, targetValue=None):
     startCol = coordinate_to_tuple(startCell)[1]
     startRow = coordinate_to_tuple(startCell)[0]
     wb = openpyxl.load_workbook(file)
     worksheet = wb.active
     row = []
     #numcols=0
     for cell in worksheet.iter_cols(min_col=startCol,
                                     min_row=startRow,
                                     max_row=startRow):
         if cell[0].value == targetValue:
             break
         else:
             #numcols += 1
             row.append(
                 tuple([
                     float(i)
                     for i in ((cell[0].value).split(')'))[1].split(';')
                 ]))
     '''lastcol=get_column_letter(startCol+numcols-1)
     lastcustomerdeparture=XlsData.getXlsDataRange(file, lastcol+str(startRow),lastcol+str(startRow), list_of_lists=False)
     lastcustomerdeparture = (lastcustomerdeparture[0])[0]
     lastcustomerdeparture =(lastcustomerdeparture.split(')'))[0]
     lastcustomerdeparture.replace("(", "")
     lastcustomerdeparture = (lastcustomerdeparture.split(';'))[1]
     row=(row,lastcustomerdeparture)'''
     return row
Exemple #3
0
    def test_reports_generated(self):
        self.make_2days_of_random_models()

        # move 1 credit and 1 disbursement to a past date and make them appear in HA and NWN sheets
        credit = Credit.objects.all().order_by('?').first()
        credit.received_at -= datetime.timedelta(days=7)
        credit.amount = 12501
        credit.save()
        disbursement = Disbursement.objects.sent().order_by('?').first()
        disbursement.created = credit.received_at
        disbursement.amount = 13602
        disbursement.save()

        call_command('update_security_profiles')

        # generate report
        report_date = credit.received_at.date()
        since = report_date.strftime('%Y-%m-%d')
        until = (report_date + datetime.timedelta(days=1)).strftime('%Y-%m-%d')
        call_command('send_notification_report',
                     '*****@*****.**',
                     since=since,
                     until=until)

        email, workbook = self.assertHasExcelAttachment()
        self.assertIn(report_date.strftime('%d %b %Y'), email.body)

        credit_sheets = {'cred-high amount', 'cred-not whole'}
        disbursement_sheets = {'disb-high amount', 'disb-not whole'}
        expected_sheets = credit_sheets | disbursement_sheets
        self.assertTrue(expected_sheets.issubset(set(workbook.sheetnames)))

        for worksheet in credit_sheets:
            worksheet = workbook[worksheet]
            dimensions = worksheet.calculate_dimension()
            rows, _columns = coordinate_to_tuple(dimensions.split(':')[1])
            self.assertEqual(rows, 2)
            self.assertEqual(worksheet['F2'].value, '£125.01')
            self.assertEqual(worksheet['H2'].value, credit.prisoner_name)
            self.assertIn(f'/credits/{credit.id}/',
                          worksheet['B2'].hyperlink.target)
        for worksheet in disbursement_sheets:
            worksheet = workbook[worksheet]
            dimensions = worksheet.calculate_dimension()
            rows, _columns = coordinate_to_tuple(dimensions.split(':')[1])
            self.assertEqual(rows, 2)
            self.assertEqual(worksheet['F2'].value, '£136.02')
            self.assertEqual(worksheet['O2'].value,
                             disbursement.recipient_address)
            self.assertIn(f'/disbursements/{disbursement.id}/',
                          worksheet['B2'].hyperlink.target)
Exemple #4
0
def process_price(sheet, coordinate: tuple, lowest_price: int, current_file: str):
    def update_price(input_price: int, price_loop_index: int) -> int:
        import excr_node
        excr_node.excr_node = \
            {each_key: each_value for each_key, each_value in excr_node.excr_node.items() if each_key != ''}
        for key, value in excr_node.excr_node.items():
            if key in current_file:
                if price_loop_index == 0:
                    print(f'当前文件 ({current_file})使用的汇率为:{float(value.split(",")[0].replace("!![", "").strip())}')
                price_loop_index += 1
                return int(input_price*float(value.split(',')[0].replace('!![', '').strip()))

    price = get_column_except_none_cell(sheet, coordinate[0], coordinate[1])
    price = [x for x in price if x.value != '']

    _price_loop_index = 0
    for item in price:
        updated_price = update_price(lowest_price, _price_loop_index)
        _price_loop_index += 1
        if int(float(item.value)) < 1:
            # 删除低于最低价格的行
            print(f"表格中{item}的价格过低于1,正在删除")
            for col in range(1, COL_MAX):
                sheet.cell(coordinate_to_tuple(str(item).split('.')[-1][:-1])[0], col).value = None
        elif (updated_price is not None) and (int(float(item.value)) < updated_price):
            print(f"文件({current_file})的表格中{item}的价格为{item.value}低于{updated_price},"
                  f"更改为{updated_price},"
                  f"如果需要别的数值请在表格中自行更改")
            item.value = str(int(updated_price))
        else:
            item.value = str(int(int(str(item.value).split('.')[0])) - 1)
Exemple #5
0
    def freeze_panes(self, topLeftCell=None):
        if isinstance(topLeftCell, Cell):
            topLeftCell = topLeftCell.coordinate
        if topLeftCell == 'A1':
            topLeftCell = None

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

        row, column = coordinate_to_tuple(topLeftCell)

        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
Exemple #6
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).
    """

    ranges = list(input_ranges)

    # convert cell into row, col tuple
    raw_coords = (coordinate_to_tuple(cell) for cell in cells)

    # group by column in order
    grouped_coords = defaultdict(list)
    for row, col in sorted(raw_coords, key=itemgetter(1)):
        grouped_coords[col].append(row)

    # create range string from first and last row in column
    for col, cells in grouped_coords.items():
        col = get_column_letter(col)
        fmt = "{0}{1}:{2}{3}"
        if len(cells) == 1:
            fmt = "{0}{1}"
        r = fmt.format(col, min(cells), col, max(cells))
        ranges.append(r)

    return " ".join(ranges)
    def _write(self):
        """
        create required structure and the serialise
        """
        anchors = []
        for idx, obj in enumerate(self.charts + self.images, 1):
            if isinstance(obj, ChartBase):
                rel = Relationship(type="chart", target="../charts/chart%s.xml" % obj._id)
                row, col = coordinate_to_tuple(obj.anchor)
                anchor = OneCellAnchor()
                anchor._from.row = row - 1
                anchor._from.col = col - 1
                anchor.ext.width = cm_to_EMU(obj.width)
                anchor.ext.height = cm_to_EMU(obj.height)
                anchor.graphicFrame = self._chart_frame(idx)
            elif isinstance(obj, Image):
                rel = Relationship(type="image", target="../media/image%s.png" % obj._id)
                anchor = obj.drawing.anchor
                anchor.pic = self._picture_frame(idx)

            anchors.append(anchor)
            self._rels.append(rel)

        self.oneCellAnchor = anchors

        tree = self.to_tree()
        tree.set("xmlns", SHEET_DRAWING_NS)
        return tree
Exemple #8
0
    def translate_formula(self, dest=None, row_delta=0, col_delta=0):
        """
        Convert the formula into A1 notation, or as row and column coordinates

        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)
        if dest:
            row, col = coordinate_to_tuple(dest)
            row_delta = row - self.row
            col_delta = col - 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)
Exemple #9
0
    def freeze_panes(self, topLeftCell=None):
        if isinstance(topLeftCell, Cell):
            topLeftCell = topLeftCell.coordinate
        if topLeftCell == 'A1':
            topLeftCell = None

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

        row, column = coordinate_to_tuple(topLeftCell)

        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
Exemple #10
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):
            coordinate = cell.get('r')
            row, column = coordinate_to_tuple(coordinate)

            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,
                                   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
Exemple #11
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).
    """

    ranges = list(input_ranges)

    # convert cell into row, col tuple
    raw_coords = (coordinate_to_tuple(cell) for cell in cells)

    # group by column in order
    grouped_coords = defaultdict(list)
    for row, col in sorted(raw_coords, key=itemgetter(1)):
        grouped_coords[col].append(row)

    # create range string from first and last row in column
    for col, cells in grouped_coords.items():
        col = get_column_letter(col)
        fmt = "{0}{1}:{2}{3}"
        if len(cells) == 1:
            fmt = "{0}{1}"
        r = fmt.format(col, min(cells), col, max(cells))
        ranges.append(r)

    return " ".join(ranges)
Exemple #12
0
    def row_col(self) -> Tuple[int, int]:
        """

        Returns:
            Tuple[int, int]. row, col
        """
        return coordinate_to_tuple(self.coordinate)
Exemple #13
0
    def _write(self):
        """
        create required structure and the serialise
        """
        anchors = []
        for idx, obj in enumerate(self.charts + self.images, 1):
            if isinstance(obj, ChartBase):
                rel = Relationship(type="chart", target='../charts/chart%s.xml' % obj._id)
                row, col = coordinate_to_tuple(obj.anchor)
                anchor = OneCellAnchor()
                anchor._from.row = row -1
                anchor._from.col = col -1
                anchor.ext.width = cm_to_EMU(obj.width)
                anchor.ext.height = cm_to_EMU(obj.height)
                anchor.graphicFrame = self._chart_frame(idx)
            elif isinstance(obj, Image):
                rel = Relationship(type="image", target='../media/image%s.png' % obj._id)
                anchor = obj.drawing.anchor
                anchor.pic = self._picture_frame(idx)

            anchors.append(anchor)
            self._rels.append(rel)

        self.oneCellAnchor = anchors

        tree = self.to_tree()
        tree.set('xmlns', SHEET_DRAWING_NS)
        return tree
Exemple #14
0
    def test_reports_generated_for_counting_rules(self):
        # make just enough credits to trigger CSFREQ rule with latest credit
        rule = RULES['CSFREQ']
        count = rule.kwargs['limit'] + 1
        sender = make_sender()
        prisoner = make_prisoner()
        credit_list = make_csfreq_credits(timezone.now(), sender, count)
        for credit in credit_list:
            credit.prisoner_profile = prisoner
            credit.save()

        # generate reports for whole range
        since = credit_list[-1].received_at.date()
        until = credit_list[0].received_at.date() + datetime.timedelta(days=1)
        call_command(
            'send_notification_report',
            '*****@*****.**',
            since=since.strftime('%Y-%m-%d'),
            until=until.strftime('%Y-%m-%d'),
            rules=['CSFREQ'],
        )
        _email, workbook = self.assertHasExcelAttachment()

        worksheet = workbook['cred-freq. source']
        dimensions = worksheet.calculate_dimension()
        rows, _columns = coordinate_to_tuple(dimensions.split(':')[1])
        self.assertEqual(rows, 2)
        self.assertEqual(worksheet['B2'].value, count)
Exemple #15
0
def get_merged_cells(sheet):
    merged_cells_dict = {
        "merged_with": {},
        "merged_to": {},
    }

    mc_ranges = sheet.merged_cells.ranges
    for mc_range in mc_ranges:
        c_list = mc_range.coord.split(":")
        first_c = tuple(map(lambda c: c - 1, coordinate_to_tuple(c_list[0])))
        last_c = tuple(map(lambda c: c - 1, coordinate_to_tuple(c_list[1])))
        merged_coords_list = coords_from_range(first_c, last_c)
        merged_cells_dict["merged_with"][first_c] = merged_coords_list[1:]
        for merged_coord in merged_coords_list[1:]:
            merged_cells_dict["merged_to"][merged_coord] = first_c
    return merged_cells_dict
Exemple #16
0
    def add_comment_shape(self, root, idx, coord, height, width):
        row, col = coordinate_to_tuple(coord)
        row -= 1
        col -= 1
        shape = _shape_factory(row, col, height, width)

        shape.set('id', "_x0000_s%04d" % idx)
        root.append(shape)
Exemple #17
0
 def __getitem__(self, key):
     """Convenience access by Excel style address"""
     if isinstance(key, slice):
         return self.iter_rows("{0}:{1}".format(key.start, key.stop))
     if ":" in key:
         return self.iter_rows(key)
     row, column = coordinate_to_tuple(key)
     return self._get_cell(row, column)
Exemple #18
0
 def __getitem__(self, key):
     """Convenience access by Excel style address"""
     if isinstance(key, slice):
         return self.iter_rows("{0}:{1}".format(key.start, key.stop))
     if ":" in key:
         return self.iter_rows(key)
     row, column = coordinate_to_tuple(key)
     return self._get_cell(row, column)
Exemple #19
0
    def parse_cell(self, element):
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        self.col_counter += 1
        style_id = element.get('s', 0)
        if style_id:
            style_id = int(style_id)

        if data_type == "inlineStr":
            value = None
        else:
            value = element.findtext(VALUE_TAG, None) or None

        if coordinate:
            row, column = coordinate_to_tuple(coordinate)
        else:
            row, column = self.row_counter, self.col_counter

        if not self.data_only and element.find(FORMULA_TAG) is not None:
            data_type = 'f'
            value = self.parse_formula(element)

        elif value is not None:
            if data_type == 'n':
                value = _cast_number(value)
                if style_id in self.date_formats:
                    data_type = 'd'
                    try:
                        value = from_excel(value, self.epoch)
                    except ValueError:
                        msg = """Cell {0} is marked as a date but the serial value {1} is outside the limits for dates. The cell will be treated as an error.""".format(
                            coordinate, value)
                        warn(msg)
                        data_type = "e"
                        value = "#VALUE!"
            elif data_type == 's':
                value = self.shared_strings[int(value)]
            elif data_type == 'b':
                value = bool(int(value))
            elif data_type == "str":
                data_type = "s"
            elif data_type == 'd':
                value = from_ISO8601(value)

        elif data_type == 'inlineStr':
            child = element.find(INLINE_STRING)
            if child is not None:
                data_type = 's'
                richtext = Text.from_tree(child)
                value = richtext.content

        return {
            'row': row,
            'column': column,
            'value': value,
            'data_type': data_type,
            'style_id': style_id
        }
Exemple #20
0
async def get_letter(user_group, table):
    letter = ""
    for cellobj in table["C4":"Z4"]:
        for cell in cellobj:
            if user_group == cell.value:
                letter = get_column_letter(
                    u.coordinate_to_tuple(cell.coordinate)[1])
                break
    return letter
Exemple #21
0
    def _merge_write(self, text, worksheet, base_cell, row_offset):
        row_base, col_base = coordinate_to_tuple(base_cell)
        row_cell = row_base + row_offset

        col_cell_max = col_base + self.size[0]
        cell_str = '{}{}'.format(get_column_letter(col_base), row_cell)

        worksheet.merge_cells(start_row=row_cell, start_column=col_base, end_row=row_cell, end_column=col_cell_max)
        worksheet[cell_str] = text
        worksheet[cell_str].alignment = Alignment(horizontal='center')
    def parse_cell(self, element):
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        self.max_column += 1
        style_id = element.get('s', 0)
        if style_id is not None:
            style_id = int(style_id)

        if data_type == "inlineStr":
            value = None
        else:
            value = element.findtext(VALUE_TAG)

        if coordinate:
            row, column = coordinate_to_tuple(coordinate)
        else:
            row, column = self.max_row, self.max_column

        if not self.data_only and element.find(FORMULA_TAG) is not None:
            data_type = 'f'
            value = self.parse_formula(element)

        elif value is not None:
            if data_type == 'n':
                value = _cast_number(value)
                if style_id in self.date_formats:
                    data_type = 'd'
                    value = from_excel(value, self.epoch)
            elif data_type == 's':
                value = self.shared_strings[int(value)]
            elif data_type == 'b':
                value = bool(int(value))
            elif data_type == "str":
                try:
                    value = _cast_number(value)
                    data_type = "n"
                except ValueError:
                    data_type = "s"
            elif data_type == 'd':
                value = from_ISO8601(value)

        elif data_type == 'inlineStr':
            child = element.find(INLINE_STRING)
            if child is not None:
                data_type = 's'
                richtext = Text.from_tree(child)
                value = richtext.content

        return {
            'row': row,
            'column': column,
            'value': value,
            'data_type': data_type,
            'style_id': style_id
        }
    def cell(self, row=None, col=None, *, name=None):
        """Get a single cell from this sheet.

        :param row: the 0-based index of the row to access
        :param col: the 0-based index of the column to access
        """
        if name is not None:
            assert row is None and col is None
            row, col = coordinate_to_tuple(name)
            row -= 1
            col -= 1
        return Cell(self, self.sheet.cell(row, col))
Exemple #24
0
    def _write(self, worksheet, cell):
        super(PieChart, self)._write(worksheet, cell)
        row, col = coordinate_to_tuple(cell)

        pie = chart.PieChart()
        pie.dataLabels = DataLabelList()
        pie.dataLabels.showVal = True
        labels = Reference(worksheet, min_col=col, min_row=row + 1, max_row=row + len(self.rows))
        data = Reference(worksheet, min_col=col + 1, min_row=row, max_row=row + len(self.rows))
        pie.add_data(data, titles_from_data=True)
        pie.set_categories(labels)
        pie.title = self.title
        worksheet.add_chart(pie, cell)
Exemple #25
0
 def process_keywords(self, keywords: str):
     if keywords == SECRET_CODE:
         # 获取所有的标题
         item_name_coordinate = pasu.get_coordinate(self.item_name_cell)
         title_list = pasu.get_column_except_none_cell(
             self.sheet, item_name_coordinate[0], item_name_coordinate[1])
         self.all_titles = [each_title.value for each_title in title_list]
         # 用标题高频词处理关键字
         processed_keywords = ' '.join(
             pasu.high_frequent_words(self.all_titles))[:KW_TRIMMER]
         if pasu.cjk_detect(processed_keywords) is not None:
             processed_keywords = processed_keywords[:50]
         processed_keywords = processed_keywords.replace('  ', ' ')
         # 处理关键词
         keywords_coordinate = coordinate_to_tuple(
             str(self.keywords_cell).split('.')[-1][:-1])
         pasu.process_info(self.sheet, keywords_coordinate,
                           processed_keywords)
     else:
         keywords_coordinate = coordinate_to_tuple(
             str(self.keywords_cell).split('.')[-1][:-1])
         pasu.process_info(self.sheet, keywords_coordinate, keywords)
Exemple #26
0
    def cell(self,
             coordinate=None,
             row=None,
             column=None,
             value=None,
             create_if_missing=True):
        """Returns a cell object based on the given coordinates.

        Usage: cell(row=15, column=1, value=5)

        Calling `cell` creates cells in memory when they
        are first accessed.

        :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

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

        :param value: value of the cell (e.g. 5)
        :type value: numeric or time or string or bool or none

        :raise: InsufficientCoordinatesException when neither row nor column are not given

        :rtype: openpyxl.cell.Cell

        """

        if (row is None or column is None) and coordinate is None:
            msg = "You have to provide a value either for " \
                  "'coordinate' or for 'row' *and* 'column'"
            raise InsufficientCoordinatesException(msg)

        if coordinate is not None:
            warn(
                "Using a coordinate with ws.cell is deprecated. Use ws[coordinate] instead"
            )
            row, column = coordinate_to_tuple(coordinate)

        if row < 1 or column < 1:
            raise ValueError("Row or column values must be at least 1")

        cell = self._get_cell(row, column, create_if_missing)
        if value is not None:
            cell.value = value

        return cell
Exemple #27
0
 def __init__(self, coords):
     """
     Parameters
     ----------
     coords: str or tuple
         单元格位置, 可以传入Excel单元格的格式, eg. 'A1',也可传入单元格的坐标, eg. '(3, 1)'表示第3行第1列
     """
     if isinstance(coords, tuple):
         self.row = coords[0]
         self.col = coords[1]
         self.name = get_column_letter(coords[1]) + str(coords[0])
     elif isinstance(coords, str):
         self.name = coords.upper()
         self.row, self.col = coordinate_to_tuple(self.name)
     else:
         raise ValueError('coords type must be tuple or str')
Exemple #28
0
    def _write(self, worksheet, cell):
        super(LineChart, self)._write(worksheet, cell)
        row, col = coordinate_to_tuple(cell)

        line_chart = chart.LineChart()
        line_chart.title = self.title

        data = Reference(worksheet, min_col=col + 1, min_row=row, max_row=row + len(self.rows),
                         max_col=col + len(self.header) - 1)

        categories = Reference(worksheet, min_col=col, min_row=row + 1, max_row=row + len(self.rows))
        line_chart.add_data(data, titles_from_data=True)
        line_chart.set_categories(categories)
        line_chart.x_axis.title = self.header[0]

        worksheet.add_chart(line_chart, cell)
Exemple #29
0
    def _get_row(self, element, min_col=1, max_col=None, row_counter=None):
        """Return cells from a particular row"""
        col_counter = min_col
        data_only = getattr(self.parent, 'data_only', False)

        for cell in safe_iterator(element, CELL_TAG):
            coordinate = cell.get('r')
            if coordinate:
                row, column = coordinate_to_tuple(coordinate)
            else:
                row, column = row_counter, col_counter

            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))
                value = None

                formula = cell.findtext(FORMULA_TAG)
                if formula is not None and not data_only:
                    data_type = 'f'
                    value = "=%s" % formula

                elif data_type == 'inlineStr':
                    child = cell.find(INLINE_TAG)
                    if child is not None:
                        richtext = Text.from_tree(child)
                        value = richtext.content

                else:
                    value = cell.findtext(VALUE_TAG) or None

                yield ReadOnlyCell(self, row, column, 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
    def _get_row(self, element, min_col=1, max_col=None, row_counter=None):
        """Return cells from a particular row"""
        col_counter = min_col
        data_only = getattr(self.parent, 'data_only', False)

        for cell in safe_iterator(element, CELL_TAG):
            coordinate = cell.get('r')
            if coordinate:
                row, column = coordinate_to_tuple(coordinate)
            else:
                row, column = row_counter, col_counter

            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))
                value = None

                formula = cell.findtext(FORMULA_TAG)
                if formula is not None and not data_only:
                    data_type = 'f'
                    value = "=%s" % formula

                elif data_type == 'inlineStr':
                    child = cell.find(INLINE_TAG)
                    if child is not None:
                        richtext = Text.from_tree(child)
                        value = richtext.content

                else:
                    value = cell.findtext(VALUE_TAG) or None

                yield ReadOnlyCell(self, row, column,
                                   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
Exemple #31
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)
            coordinate = (row, column)

        else:
            coordinate = coordinate.upper().replace('$', '')
            coordinate = coordinate_to_tuple(coordinate)
            row, column = coordinate

        if row < 1 or column < 1:
            raise ValueError("Row or column values must be at least 1")

        cell = self._get_cell(row, column)
        if value is not None:
            cell.value = value

        return cell
Exemple #32
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)
            coordinate = (row, column)

        else:
            coordinate = coordinate.upper().replace('$', '')
            coordinate = coordinate_to_tuple(coordinate)
            row, column = coordinate

        if row < 1 or column < 1:
            raise ValueError("Row or column values must be at least 1")

        cell = self._get_cell(row, column)
        if value is not None:
            cell.value = value

        return cell
 def get_merged_cell_ranges(self):
     """
     :return: A list containing the ranges of merged cells. A range is
     composed with two couples, describing the up left cell and the down
     right cell. e.g. ((1, 1), (1, 2)).
     """
     merged_cell_ranges = self._workbook.active.merged_cell_ranges
     split = [i.split(':') for i in merged_cell_ranges]
     coordinates = [
         tuple([coordinate_to_tuple(i) for i in t]) for t in split
     ]
     fixed = []
     # Fix indices
     for r in coordinates:
         a, b = r[0]
         c, d = r[1]
         fixed.append(((a - 1, b - 1), (c - 1, d - 1)))
     return fixed
Exemple #34
0
 def _write(self, worksheet, cell):
     row, col = coordinate_to_tuple(cell)
     size_x, size_y = self.size
     rows = list(worksheet.get_squared_range(col, row, col + size_x - 1, row + size_y - 1))
     font = Font(color=WHITE, bold=True)
     head_fill = PatternFill(start_color=BLACK, end_color=BLACK, fill_type='solid')
     for i, header_cell in enumerate(rows[0]):
         header_cell.value = self.header[i]
         header_cell.font = font
         header_cell.fill = head_fill
         header_cell.alignment = Alignment(horizontal='center')
     colors = [WHITE, 'EAEAEA']
     for i, row in enumerate(rows[1:]):
         for j, c in enumerate(row):
             color = colors[i % 2]
             cell_fill = PatternFill(start_color=color, end_color=color, fill_type='solid')
             c.value = self.rows[i][j]
             c.fill = cell_fill
def _check_anchor(obj):
    """
    Check whether an object has an existing Anchor object
    If not create a OneCellAnchor using the provided coordinate
    """
    anchor = obj.anchor
    if not isinstance(anchor, _AnchorBase):
        row, col = coordinate_to_tuple(anchor)
        anchor = OneCellAnchor()
        anchor._from.row = row -1
        anchor._from.col = col -1
        if isinstance(obj, ChartBase):
            anchor.ext.width = cm_to_EMU(obj.width)
            anchor.ext.height = cm_to_EMU(obj.height)
        elif isinstance(obj, Image):
            anchor.ext.width = pixels_to_EMU(obj.width)
            anchor.ext.height = pixels_to_EMU(obj.height)
    return anchor
def _check_anchor(obj):
    """
    Check whether an object has an existing Anchor object
    If not create a OneCellAnchor using the provided coordinate
    """
    anchor = obj.anchor
    if not isinstance(anchor, _AnchorBase):
        row, col = coordinate_to_tuple(anchor)
        anchor = OneCellAnchor()
        anchor._from.row = row - 1
        anchor._from.col = col - 1
        if isinstance(obj, ChartBase):
            anchor.ext.width = cm_to_EMU(obj.width)
            anchor.ext.height = cm_to_EMU(obj.height)
        elif isinstance(obj, Image):
            anchor.ext.width = pixels_to_EMU(obj.width)
            anchor.ext.height = pixels_to_EMU(obj.height)
    return anchor
    def parse_cell(self, element):
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        self.col_counter += 1
        style_id = element.get('s', 0)
        if style_id:
            style_id = int(style_id)

        if data_type == "inlineStr":
            value = None
        else:
            value = element.findtext(VALUE_TAG, None) or None

        if coordinate:
            row, column = coordinate_to_tuple(coordinate)
        else:
            row, column = self.row_counter, self.col_counter

        # logger.debug(f"after parse: ({row}, {column}) {data_type}: {value}")

        if element.find(FORMULA_TAG) is not None:
            cache_type = data_type
            cache = value
            # logger.debug(f"cache before parse {cache_type}: {cache}")
            data_type = 'f'
            value = self.parse_formula(element)
            cache_type, cache = self._parse_value(element, cache_type, cache, style_id)
            # logger.debug(f"cache before parse {cache_type}: {cache}")
            # logger.debug(f"formula after parse: ({row}, {column}) {data_type}: {value}")
            return {
                'row':row, 
                'column':column, 
                'value':value, 
                'data_type':data_type, 
                'style_id':style_id,
                'cache_type': cache_type,
                'cache': cache
            }

        else:
            data_type, value = self._parse_value(element, data_type, value, style_id)
            # logger.debug(f"after parse: ({row}, {column}) {data_type}: {value}")
            return {'row':row, 'column':column, 'value':value, 'data_type':data_type, 'style_id':style_id}
Exemple #38
0
    def cell(self, coordinate=None, row=None, column=None, value=None):
        """Returns a cell object based on the given coordinates.

        Usage: cell(row=15, column=1, value=5)

        Calling `cell` creates cells in memory when they
        are first accessed.

        :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

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

        :param value: value of the cell (e.g. 5)
        :type value: numeric or time or string or bool or none

        :raise: InsufficientCoordinatesException when neither row nor column are not given

        :rtype: openpyxl.cell.Cell

        """

        if (row is None or column is None) and coordinate is None:
            msg = "You have to provide a value either for " \
                   "'coordinate' or for 'row' *and* 'column'"
            raise InsufficientCoordinatesException(msg)

        if coordinate is not None:
            warn("Using a coordinate with ws.cell is deprecated. Use ws[coordinate] instead")
            row, column = coordinate_to_tuple(coordinate)

        if row < 1 or column < 1:
            raise ValueError("Row or column values must be at least 1")

        cell = self._get_cell(row, column)
        if value is not None:
            cell.value = value

        return cell
Exemple #39
0
def cell_meta_to_dict(cell, merged_cells_dict, jsonize=False):
    cell_coords_pair = tuple(
        map(lambda c: c - 1, coordinate_to_tuple(cell.coordinate)))
    meta_dict = {
        'data_type':
        cell.data_type,
        'style_id':
        cell.style_id,
        'pivotButton':
        cell.pivotButton,
        'alignment':
        cell.alignment.__dict__,
        'merged_with':
        merged_cells_dict["merged_with"].get(cell_coords_pair, list()),
        'merged_to':
        merged_cells_dict["merged_to"].get(cell_coords_pair, None),
        'font':
        expand_attrs_dict(cell.font.__dict__, 'color'),
        'fill': {
            'bgColor': cell.fill.bgColor.__dict__,
            'fgColor': cell.fill.fgColor.__dict__,
        },
        'border': {
            'outline': cell.border.outline,
            'right': expand_attrs_dict(cell.border.right.__dict__, 'color'),
            'left': expand_attrs_dict(cell.border.left.__dict__, 'color'),
            'top': expand_attrs_dict(cell.border.top.__dict__, 'color'),
            'bottom': expand_attrs_dict(cell.border.bottom.__dict__, 'color'),
        },
    }

    if not deepdiff(d1=CELL_DEFAULT_META, d2=meta_dict):
        meta_dict = None
    #meta_dict = deepdiff(d1=XLSonHandler.cell_default_meta, d2=meta_dict)

    if jsonize:
        return json.dumps(meta_dict)
    else:
        return meta_dict
    def _write(self):
        """
        create required structure and the serialise
        """
        anchors = []
        for idx, obj in enumerate(self.charts + self.images, 1):
            if isinstance(obj, ChartBase):
                rel = Relationship(type="chart", Target=obj.path)
                anchor = obj.anchor
                if not isinstance(anchor, _AnchorBase):
                    row, col = coordinate_to_tuple(anchor)
                    anchor = OneCellAnchor()
                    anchor._from.row = row - 1
                    anchor._from.col = col - 1
                    anchor.ext.width = cm_to_EMU(obj.width)
                    anchor.ext.height = cm_to_EMU(obj.height)
                anchor.graphicFrame = self._chart_frame(idx)
            elif isinstance(obj, Image):
                rel = Relationship(type="image", Target=obj.path)
                anchor = obj.drawing.anchor
                anchor.pic = self._picture_frame(idx)

            anchors.append(anchor)
            self._rels.append(rel)

        for a in anchors:
            if isinstance(a, OneCellAnchor):
                self.oneCellAnchor.append(a)
            elif isinstance(a, TwoCellAnchor):
                self.twoCellAnchor.append(a)
            else:
                self.absoluteAnchor.append(a)

        tree = self.to_tree()
        tree.set('xmlns', SHEET_DRAWING_NS)
        return tree
Exemple #41
0
    def _write(self):
        """
        create required structure and the serialise
        """
        anchors = []
        for idx, obj in enumerate(self.charts + self.images, 1):
            if isinstance(obj, ChartBase):
                rel = Relationship(type="chart", Target=obj.path)
                anchor = obj.anchor
                if not isinstance(anchor, _AnchorBase):
                    row, col = coordinate_to_tuple(anchor)
                    anchor = OneCellAnchor()
                    anchor._from.row = row -1
                    anchor._from.col = col -1
                    anchor.ext.width = cm_to_EMU(obj.width)
                    anchor.ext.height = cm_to_EMU(obj.height)
                anchor.graphicFrame = self._chart_frame(idx)
            elif isinstance(obj, Image):
                rel = Relationship(type="image", Target=obj.path)
                anchor = obj.drawing.anchor
                anchor.pic = self._picture_frame(idx)

            anchors.append(anchor)
            self._rels.append(rel)

        for a in anchors:
            if isinstance(a, OneCellAnchor):
                self.oneCellAnchor.append(a)
            elif isinstance(a, TwoCellAnchor):
                self.twoCellAnchor.append(a)
            else:
                self.absoluteAnchor.append(a)

        tree = self.to_tree()
        tree.set('xmlns', SHEET_DRAWING_NS)
        return tree
Exemple #42
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.
     self.row, self.col = coordinate_to_tuple(origin)
     self.tokenizer = Tokenizer(formula)
Exemple #43
0
 def __delitem__(self, key):
     row, column = coordinate_to_tuple(key)
     if (row, column) in self._cells:
         del self._cells[(row, column)]
Exemple #44
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
                    if formula_type == "shared":
                        # The spec (18.3.1.40) defines shared formulae in
                        # terms of the following:
                        #
                        # `master`: "The first formula in a group of shared
                        #            formulas"
                        # `ref`: "Range of cells which the formula applies
                        #        to." It's a required attribute on the master
                        #        cell, forbidden otherwise.
                        # `shared cell`: "A cell is shared only when si is
                        #                 used and t is `shared`."
                        #
                        # Whether to use the cell's given formula or the
                        # master's depends on whether the cell is shared,
                        # whether it's in the ref, and whether it defines its
                        # own formula, as follows:
                        #
                        #  Shared?   Has formula? | In ref    Not in ref
                        # ========= ==============|======== ===============
                        #   Yes          Yes      | master   impl. defined
                        #    No          Yes      |  own         own
                        #   Yes           No      | master   impl. defined
                        #    No           No      |  ??          N/A
                        #
                        # The ?? is because the spec is silent on this issue,
                        # though my inference is that the cell does not
                        # receive a formula at all.
                        #
                        # For this implementation, we are using the master
                        # formula in the two "impl. defined" cases and no
                        # formula in the "??" case. This choice of
                        # implementation allows us to disregard the `ref`
                        # parameter altogether, and does not require
                        # computing expressions like `C5 in A1:D6`.
                        # Presumably, Excel does not generate spreadsheets
                        # with such contradictions.
                        try:
                            trans = self.shared_formula_masters[si]
                        except KeyError:
                            # This cell must be master
                            self.shared_formula_masters[si] = Translator(
                                value, coordinate)
                        else:
                            value = trans.translate_formula(coordinate)
                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]

        row, column = coordinate_to_tuple(coordinate)
        cell = Cell(self.ws, row=row, col_idx=column, **style)
        self.ws._cells[(row, column)] = cell

        if value is not None:
            if data_type == 'n':
                value = _cast_number(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
Exemple #45
0
 def __delitem__(self, key):
     row, column = coordinate_to_tuple(key)
     if (row, column) in self._cells:
         del self._cells[(row, column)]
Exemple #46
0
    def write(self, worksheet, cell='A1', offset_col=0, offset_row=0):
        row, col = coordinate_to_tuple(cell)
        cell = get_column_letter(col + offset_col) + str(row + offset_row)

        self._write(worksheet, cell)