Exemplo n.º 1
0
def test_number_format(DummyWorksheet, Cell):
    ws = DummyWorksheet
    ws.parent._number_formats.add("dd--hh--mm")

    cell = Cell(ws, column="A", row=1)
    cell.number_format = "dd--hh--mm"
    assert cell.number_format == "dd--hh--mm"
Exemplo n.º 2
0
    def parse_cell(self, element):
        value = element.find(self.VALUE_TAG)
        if value is not None:
            value = value.text
        formula = element.find(self.FORMULA_TAG)
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        style_id = element.get('s')

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


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

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

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

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

        if self.guess_types or value is None:
            cell.value = value
        else:
            cell._value=value
            cell.data_type=data_type
Exemplo n.º 3
0
def test_date_format_on_non_date():
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = datetime.now()
    cell.value = 'testme'
    eq_('testme', cell.value)
Exemplo n.º 4
0
def test_date_format_on_non_date():
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, "A", 1)
    cell.value = datetime.now()
    cell.value = "testme"
    eq_("testme", cell.value)
Exemplo n.º 5
0
def test_time(value, expected):
    wb = Workbook(guess_types=True)
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = value
    assert cell.value == expected
    assert cell.TYPE_NUMERIC == cell.data_type
Exemplo n.º 6
0
def underline_border_cell(val, ws):
    underline_border = Border(bottom=Side(style='thin'))

    c = Cell(ws, value=val)
    c.font = Font(size=11, bold=True)
    c.border = underline_border
    return c
Exemplo n.º 7
0
def test_set_get_date():
    today = datetime(2010, 1, 18, 14, 15, 20, 1600)
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = today
    eq_(today, cell.value)
Exemplo n.º 8
0
def test_timedelta():

    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = timedelta(days=1, hours=3)
    assert cell.value == 1.125
    assert cell.TYPE_NUMERIC == cell.data_type
Exemplo n.º 9
0
def test_timedelta():

    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = timedelta(days=1, hours=3)
    eq_(cell.value, 1.125)
    eq_(cell.TYPE_NUMERIC, cell.data_type)
Exemplo n.º 10
0
def test_is_date():
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, "A", 1)
    cell.value = datetime.now()
    eq_(cell.is_date(), True)
    cell.value = "testme"
    eq_("testme", cell.value)
    eq_(cell.is_date(), False)
Exemplo n.º 11
0
def test_is_date():
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = datetime.now()
    eq_(cell.is_date(), True)
    cell.value = 'testme'
    eq_('testme', cell.value)
    assert cell.is_date() is False
Exemplo n.º 12
0
 def put_text(cell: Cell, text, font=None, border=None, alignment=None):
     cell.value = text
     if font:
         cell.font = font
     if border:
         cell.border = border
     if alignment:
         cell.alignment = alignment
     return cell
Exemplo n.º 13
0
def test_is_date():
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    cell.value = datetime.now()
    assert cell.is_date() == True
    cell.value = 'testme'
    assert 'testme' == cell.value
    assert cell.is_date() is False
Exemplo n.º 14
0
def test_is_not_date_color_format():

    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)

    cell.value = -13.5
    cell.style.number_format.format_code = '0.00_);[Red]\(0.00\)'

    assert cell.is_date() is False
Exemplo n.º 15
0
def test_is_not_date_color_format():

    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)

    cell.value = -13.5
    cell.style = cell.style.copy(number_format=NumberFormat('0.00_);[Red]\(0.00\)'))

    assert cell.is_date() is False
Exemplo n.º 16
0
def test_is_not_date_color_format():

    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, "A", 1)

    cell.value = -13.5
    cell.style.number_format.format_code = "0.00_);[Red]\(0.00\)"

    eq_(cell.is_date(), False)
Exemplo n.º 17
0
def setStyle(ws, value, style):
    # Silence the warning about using a composite Style object instead of Font.
    with warnings.catch_warnings():
        warnings.simplefilter("ignore", UserWarning)
        try:
            result = [Cell(ws, column='A', row = 1, value=item) for item in value]
            for cell in result:
                cell.style = style
        except TypeError:
            result = Cell(ws, column='A', row = 1, value=value)
            result.style = style
    return result
Exemplo n.º 18
0
def test_data_type_check():
    cell = Cell(None, "A", 1)
    cell.bind_value(None)
    eq_(Cell.TYPE_NULL, cell._data_type)

    cell.bind_value(".0e000")
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value("-0.e-0")
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value("1E")
    eq_(Cell.TYPE_STRING, cell._data_type)
Exemplo n.º 19
0
def test_data_type_check():
    cell = Cell(None, 'A', 1)
    cell.bind_value(None)
    eq_(Cell.TYPE_NULL, cell._data_type)

    cell.bind_value('.0e000')
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value('-0.e-0')
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value('1E')
    eq_(Cell.TYPE_STRING, cell._data_type)
Exemplo n.º 20
0
def test_data_type_check():
    ws = build_dummy_worksheet()
    cell = Cell(ws, 'A', 1)
    cell.bind_value(None)
    eq_(Cell.TYPE_NULL, cell._data_type)

    cell.bind_value('.0e000')
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value('-0.e-0')
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value('1E')
    eq_(Cell.TYPE_STRING, cell._data_type)
Exemplo n.º 21
0
def test_data_type_check():
    ws = build_dummy_worksheet()
    cell = Cell(ws, "A", 1)
    cell.bind_value(None)
    eq_(Cell.TYPE_NULL, cell._data_type)

    cell.bind_value(".0e000")
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value("-0.e-0")
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value("1E")
    eq_(Cell.TYPE_STRING, cell._data_type)
Exemplo n.º 22
0
def test_data_type_check():
    class Ws(object):
            encoding = 'utf-8'
    cell = Cell(Ws(), 'A', 1)
    cell.bind_value(None)
    eq_(Cell.TYPE_NULL, cell._data_type)

    cell.bind_value('.0e000')
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value('-0.e-0')
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value('1E')
    eq_(Cell.TYPE_STRING, cell._data_type)
Exemplo n.º 23
0
def test_data_type_check():
    class Ws(object):
        encoding = "utf-8"

    cell = Cell(Ws(), "A", 1)
    cell.bind_value(None)
    eq_(Cell.TYPE_NULL, cell._data_type)

    cell.bind_value(".0e000")
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value("-0.e-0")
    eq_(Cell.TYPE_NUMERIC, cell._data_type)

    cell.bind_value("1E")
    eq_(Cell.TYPE_STRING, cell._data_type)
Exemplo n.º 24
0
    def set_col_widths(self):
        from openpyxl.utils.cell import get_column_letter
        from openpyxl.cell import Cell
        TYPE_STRING = Cell.TYPE_STRING

        for idx, width in sorted(self._col_widths.iteritems()):
            letter = get_column_letter(idx + 1)
            self.sheet.column_dimensions[letter].width = 1 + min(width, 50)
        for row in self._rows:
            values = []
            for val in row:
                if val:
                    value = val.value
                    cell = Cell(self.sheet, column='A', row=1)
                    if isinstance(value, basestring):
                        cell.set_explicit_value(value, data_type=TYPE_STRING)
                    else:
                        cell.value = value
                    cell.style = val.style
                else:
                    cell = val
                values.append(cell)
            self.sheet.append(values)
        self._rows[:] = ()
Exemplo n.º 25
0
def print_sales_content(collection, ws):
    # Table Header
    table_headers = ['Date', 'O.R. #', 'Name', 'Amount', 'Remarks']
    table_header_cells = []

    for h in table_headers:
        c = underline_border_cell(h, ws)
        table_header_cells.append(c)
    r = [''] + table_header_cells
    ws.append(r)

    sales_total = 0
    for item in collection:
        if item.label == 'Cemetery Lot':
            amount = item.lot_area * item.price_per_sq_mtr
        # elif item.label == 'Cremation': # todo no amount for cremation yet
        #     amount = 0
        elif item.label == 'Columbary':
            amount = item.price if item.price is not None else 0

        amount_formatted = 'P {:20,.2f}'.format(amount)
        amount_formatted_cell = Cell(ws, value=amount_formatted)
        amount_formatted_cell.style = Style(alignment=Alignment(horizontal='right'))
        client_name = item.client.get_full_name() if item.client is not None else ''

        sales_total += amount
        ws.append(['', item.date_purchased, item.or_no, client_name, amount_formatted_cell, item.label])

    # Sales Total
    total_label_cell = Cell(ws, value='TOTAL')
    total_label_cell.font = Font(size=12, color='FFFF0000')

    total_cell = Cell(ws, value='P {:20,.2f}'.format(sales_total))
    total_cell.font = Font(size=12, color='FFFF0000')
    total_cell.border = total_border
    total_cell.alignment = Alignment(horizontal='right')

    ws.append(['', '', '', total_label_cell, total_cell])
Exemplo n.º 26
0
def test_illegal_chacters():
    from openpyxl.exceptions import IllegalCharacterError
    from openpyxl.compat import range
    from itertools import chain
    ws = build_dummy_worksheet()
    cell = Cell(ws, 'A', 1)

    # The bytes 0x00 through 0x1F inclusive must be manually escaped in values.

    illegal_chrs = chain(range(9), range(11, 13), range(14, 32))
    for i in illegal_chrs:
        with pytest.raises(IllegalCharacterError):
            cell.value = chr(i)

        with pytest.raises(IllegalCharacterError):
            cell.value = "A {0} B".format(chr(i))

    cell.value = chr(33)
    cell.value = chr(9)  # Tab
    cell.value = chr(10)  # Newline
    cell.value = chr(13)  # Carriage return
    cell.value = " Leading and trailing spaces are legal "
Exemplo n.º 27
0
def test_initial_value():
    cell = Cell(None, 'A', 1, value='17.5')
    eq_(cell.TYPE_NUMERIC, cell.data_type)
Exemplo n.º 28
0
def process_xls(data, config=None):
    header = data['header']
    title = header['title']
    origin = data['dataOrigin']
    book = Workbook()
    sheet = book.active
    doc_id = unique_id()
    files_path = config.get('files', 'path')

    if 'logoURL' in header:
        try:
            response = requests.get(header['logoURL'], stream=True)
            logo = Image(response.raw)
            logo = Image(logo.image.resize((100, 100)))
        except requests.ConnectionError as cerror:
            print(cerror, file=sys.stderr)

    else:
        logo = None


    hdr_bkg_color = header['backgroundColor']
    header_bkg = PatternFill(fill_type="solid",
                             start_color=hdr_bkg_color,
                             end_color=hdr_bkg_color)
    colformats = []
    coltypes = []
    has_formats = False

    columns = data.get('columns', [])

    try:
        for col in columns:
            colfmt = col.get('format', None)
            coltype = col.get('type', None)
            colformats.append(colfmt)
            coltypes.append(coltype)
        has_formats = True

    except TypeError:
        pass

    if origin == 'array':
        rows = data['rows']

        cell = Cell(sheet, value=title)
        cell.alignment = Alignment(horizontal='center',
                                   vertical='center')

        sheet.append(['', '', '', cell])

        sheet.merge_cells('A1:C1')
        sheet.merge_cells('D1:G1')

        for row in rows:
            cells = []
            for value in row:
                cell = Cell(sheet, value=value)
                cells.append(cell)
            sheet.append(cells)

    else:
        db = data['database']
        sql_query = data['sqlQuery']
        url_callback = data['urlCallback']
        title = data['title']
        columns = data['columns']

        """
        conn = pg_connect(host=db['host'],
                          database=db['name'],
                          password=db['password'],
                          user=db['user'])

        cursor = conn.cursor()
        cursor.execute(sql_query)
        """

        index = 0

        is_first = True

        for row in cursor:
            if is_first:
                sheet.merge_cells('A1:C1')
                sheet.merge_cells('D1:G1')

                sheet.append(['', '', '', cell])

                if logo:
                    sheet.add_image(logo, 'A1')

                headcells = []
                for col in columns:
                    cell = Cell(sheet, value=col['label'])
                    cell.fill = header_bkg
                    coltype = col.get('type', None)
                    colfmt = col.get('format', None)
                    columns_format.append(colfmt)
                    columns_type.append(coltype)
                    headcells.append(cell)

                sheet.append(headcells)

                is_first = False
                #sheet.row_dimensions[0].height = 300
                sheet.row_dimensions[1].height = 72
            
            sheet.append(row)

            index += 1

    outfile = '{}/{}.xlsx'.format(files_path, doc_id)
    book.save(outfile)

    return doc_id
Exemplo n.º 29
0
 def cell(self, column, row):
     return Cell(self, column, row)
Exemplo n.º 30
0
def test_set_bad_type():
    cell = Cell(None, 'A', 1)
    cell.set_value_explicit(1, 'q')
Exemplo n.º 31
0
def test_data_type_check(value, datatype):
    ws = build_dummy_worksheet()
    ws.parent._guess_types = True
    cell = Cell(ws, 'A', 1)
    cell.value = value
    assert cell.data_type == datatype
def test_data_type_check(value, datatype):
    ws = build_dummy_worksheet()
    ws.parent._guess_types = True
    cell = Cell(ws, 'A', 1)
    cell.value = value
    assert cell.data_type == datatype
 def __getitem__(self, value):
     if self.cell is None:
         self.cell = Cell(self, 'A', 1)
     return self.cell
Exemplo n.º 34
0
 def wrapper(*args, **kwargs):
     value = f(*args, **kwargs)
     cell = Cell(worksheet=Workbook().active, column=0, row=0, value=value)
     ret = openpyxl_args.copy() if openpyxl_args else {}
     ret.update(value=cell.internal_value if convert else value)
     return ret
 def setup_class(cls):
     wb = Workbook()
     ws = Worksheet(wb)
     cls.cell = Cell(ws, 'A', 1)
Exemplo n.º 36
0
def standard():
    c = Cell(None, "A", "0", None)
Exemplo n.º 37
0
                                value = value / divider
                                corrected_acids.add(
                                    str(row[start_col_idx].value))
                        if type(value) in [int, float]:
                            value = value * PARAMETERS['Multiplier']
                        if data['Type'] == 'Urine' and value != 'n.a.' and row_idx > start_row_idx + 2:
                            try:
                                value = float(value) / float(
                                    data['Creatinine'])
                            except TypeError:
                                print(
                                    'ERROR: Creatinine value for ID %s is not number.'
                                    % file_id)
                        amounts[acid_name] = value
                        if acid_name in acid_norms:
                            new_cell = Cell(new_ws)
                            new_cell.value = value
                            total_cell = Cell(total_ws)
                            total_cell.value = value

                            lbound, rbound = acid_norms[acid_name]
                            if float(value) < float(lbound):
                                new_cell.fill = yellow_fill
                                total_cell.fill = yellow_fill
                            elif float(value) <= float(rbound):
                                new_cell.fill = green_fill
                                total_cell.fill = green_fill
                            else:
                                new_cell.fill = red_fill
                                total_cell.fill = red_fill
Exemplo n.º 38
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')
        self._col_count += 1
        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:
                if formula_type != "shared":
                    self.ws.formula_attributes[coordinate] = dict(
                        formula.attrib)

                else:
                    si = formula.get(
                        'si')  # Shared group index for shared formulas

                    # 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.
                    if si in self.shared_formula_masters:
                        trans = self.shared_formula_masters[si]
                        value = trans.translate_formula(coordinate)
                    else:
                        self.shared_formula_masters[si] = Translator(
                            value, coordinate)

        style_array = None
        if style_id is not None:
            style_id = int(style_id)
            style_array = self.styles[style_id]

        if coordinate:
            row, column = coordinate_to_tuple(coordinate)
        else:
            row, column = self._row_count, self._col_count

        cell = Cell(self.ws, row=row, col_idx=column, style_array=style_array)
        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':
                child = element.find(self.INLINE_STRING)
                if child is not None:
                    data_type = 's'
                    richtext = Text.from_tree(child)
                    value = richtext.content

        if self.guess_types or value is None:
            cell.value = value
        else:
            cell._value = value
            cell.data_type = data_type
Exemplo n.º 39
0
    def setup_class(cls):
        class Ws(object):
            encoding = 'utf-8'

        cls.cell = Cell(Ws(), 'A', 1)
def test_set_bad_type():
    ws = build_dummy_worksheet()
    cell = Cell(ws, 'A', 1)
    with pytest.raises(ValueError):
        cell.set_explicit_value(1, 'q')
Exemplo n.º 41
0
def test_quote_prefix(DummyWorksheet, Cell):
    ws = DummyWorksheet

    cell = Cell(ws, column="A", row=1, quotePrefix=True)
    assert cell.quotePrefix is True
Exemplo n.º 42
0
def test_set_bad_type():
    ws = build_dummy_worksheet()
    cell = Cell(ws, 'A', 1)
    cell.set_explicit_value(1, 'q')
Exemplo n.º 43
0
def WriteOnlyCell(ws=None, value=None):
    return Cell(worksheet=ws, column='A', row=1, value=value)
Exemplo n.º 44
0
def get_value_from_placement_dict(attrib, placement_dict, week):
    black_solid_fill = PatternFill("solid", fgColor="3b3b3b")
    dark_grid_fill = PatternFill("darkGrid", fgColor="3b3b3b")
    wb = Workbook()
    ws = wb.active
    return_cell = Cell(ws)
    return_cell.value == ''
    if attrib == 'fields':
        return_cell.value == ''
    if re.search('\d{1,2}', str(attrib)):
        plan_weeks = placement_dict.get('plan_weeks')
        fact_weeks = list()
        for value in placement_dict['postclick']:
            fact_weeks.append(int(value['weeknumber']))
        if attrib in plan_weeks:
            return_cell.fill = dark_grid_fill
        if attrib in fact_weeks:
            return_cell.fill = dark_grid_fill
        if attrib in plan_weeks and attrib in fact_weeks:
            return_cell.fill = black_solid_fill
    if attrib in ('platform_site', 'description', 'format', 'plan_impressions',
                  'plan_reach', 'plan_clicks', 'plan_views', 'plan_budget'):
        return_cell.value = placement_dict.get(attrib)
    if attrib in ('fact_impressions', 'fact_clicks', "fact_reach",
                  "fact_views", 'fact_budget'):
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                return_cell.value = value.get(attrib)
    if attrib == "plan_cpm":
        if placement_dict.get(
                "plan_budget") is not None and placement_dict.get(
                    "plan_impressions"):
            return_cell.value = placement_dict.get(
                "plan_budget") * 1000 / placement_dict.get("plan_impressions")
        else:
            return_cell.value = "N/A"
    if attrib == "plan_cpt":
        if placement_dict.get(
                "plan_budget") is not None and placement_dict.get(
                    "plan_reach") is not None:
            return_cell.value = placement_dict.get(
                "plan_budget") * 1000 / placement_dict.get("plan_reach")
        else:
            return_cell.value = "N/A"
    if attrib == "plan_ctr":
        if placement_dict.get(
                "plan_clicks") is not None and placement_dict.get(
                    "plan_impressions") is not None:
            return_cell.value = placement_dict.get(
                "plan_clicks") / placement_dict.get("plan_impressions")
        else:
            return_cell.value = "N/A"
    if attrib == "plan_cpc":
        if placement_dict.get(
                "plan_budget") is not None and placement_dict.get(
                    "plan_clicks") is not None:
            return_cell.value = placement_dict.get(
                "plan_budget") / placement_dict.get("plan_clicks")
        else:
            return_cell.value = "N/A"
    if attrib == "plan_vtr":
        if placement_dict.get("plan_views") != 'N/A' and placement_dict.get(
                "plan_impressions") is not None:
            return_cell.value = placement_dict.get(
                "plan_views") / placement_dict.get("plan_impressions")
        else:
            return_cell.value = "N/A"
    if attrib == "plan_cpv":
        if placement_dict.get(
                "plan_budget"
        ) is not None and placement_dict.get("plan_views") != 'N/A':
            return_cell.value = placement_dict.get(
                "plan_budget") / placement_dict.get("plan_views")
        else:
            return_cell.value = "N/A"
    if attrib == "fact_cpm":
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                if value.get("fact_budget") is not None and value.get(
                        "fact_impressions") is not None:
                    return_cell.value = value.get(
                        "fact_budget") * 1000 / value.get("fact_impressions")
                else:
                    return_cell.value = "N/A"
    if attrib == "fact_cpt":
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                if value.get("fact_budget") is not None and value.get(
                        "fact_reach") is not None:
                    return_cell.value = value.get(
                        "fact_budget") * 1000 / value.get("fact_reach")
                else:
                    return_cell.value = "N/A"
    if attrib == "fact_ctr":
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                if value.get("fact_clicks") is not None and value.get(
                        "fact_impressions") is not None:
                    return_cell.value = value.get("fact_clicks") / value.get(
                        "fact_impressions")
                else:
                    return_cell.value = "N/A"
    if attrib == "fact_cpc":
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                if value.get("fact_budget") is not None and value.get(
                        "fact_clicks") is not None:
                    return_cell.value = value.get("fact_budget") / value.get(
                        "fact_clicks")
                else:
                    return_cell.value = "N/A"
    if attrib == 'fact_vtr':
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                if value.get("fact_views") is not None and value.get(
                        "fact_impressions") is not None:
                    return_cell.value = value.get("fact_views") / value.get(
                        "fact_impressions")
                else:
                    return_cell.value = "N/A"
    if attrib == "fact_cpv":
        for value in placement_dict['postclick']:
            if value['weeknumber'] == week:
                if value.get("fact_budget") is not None and value.get(
                        "fact_views") is not None:
                    return_cell.value = value.get("fact_budget") / value.get(
                        "fact_views")
                else:
                    return_cell.value = "N/A"
    if attrib == 'period':
        return_cell.value = str(len(
            placement_dict.get('plan_weeks'))) + " weeks"
    if attrib == 'fact_impressions_adriver':
        if placement_dict.get('adriver_id') is not None:
            return_cell.value = get_adriver_value(
                placement_dict.get('adriver_id'), week)
        else:
            return_cell.value = "N/A"
    return return_cell
Exemplo n.º 45
0
def process_xls(data, config=None):
    header = data['header']
    title = header['title']
    origin = data['dataOrigin']
    book = Workbook()
    sheet = book.active
    doc_id = unique_id()
    files_path = config.get('files', 'path')

    if 'logoURL' in header:
        try:
            response = requests.get(header['logoURL'], stream=True)
            logo = Image(response.raw)
            logo = Image(logo.image.resize((100, 100)))
        except requests.ConnectionError as cerror:
            print(cerror, file=sys.stderr)

    else:
        logo = None


    hdr_bkg_color = header['backgroundColor']
    header_bkg = PatternFill(fill_type="solid",
                             start_color=hdr_bkg_color,
                             end_color=hdr_bkg_color)
    colformats = []
    coltypes = []
    has_formats = False

    columns = data.get('columns', [])

    try:
        for col in columns:
            colfmt = col.get('format', None)
            coltype = col.get('type', None)
            colformats.append(colfmt)
            coltypes.append(coltype)
        has_formats = True

    except TypeError:
        pass

    if origin == 'array':
        rows = data['rows']

        cell = Cell(sheet, value=title)
        cell.alignment = Alignment(horizontal='center',
                                   vertical='center')

        sheet.append(['', '', '', cell])

        sheet.merge_cells('A1:C1')
        sheet.merge_cells('D1:G1')

        for row in rows:
            cells = []
            for value in row:
                cell = Cell(sheet, value=value)
                cells.append(cell)
            sheet.append(cells)

    else:
        db = data['database']
        sql_query = data['sqlQuery']
        url_callback = data['urlCallback']
        title = data['title']
        columns = data['columns']

        """
        conn = pg_connect(host=db['host'],
                          database=db['name'],
                          password=db['password'],
                          user=db['user'])

        cursor = conn.cursor()
        cursor.execute(sql_query)
        """

        index = 0

        is_first = True

        for row in cursor:
            if is_first:
                sheet.merge_cells('A1:C1')
                sheet.merge_cells('D1:G1')

                sheet.append(['', '', '', cell])

                if logo:
                    sheet.add_image(logo, 'A1')

                headcells = []
                for col in columns:
                    cell = Cell(sheet, value=col['label'])
                    cell.fill = header_bkg
                    coltype = col.get('type', None)
                    colfmt = col.get('format', None)
                    columns_format.append(colfmt)
                    columns_type.append(coltype)
                    headcells.append(cell)

                sheet.append(headcells)

                is_first = False
                #sheet.row_dimensions[0].height = 300
                sheet.row_dimensions[1].height = 72
            
            sheet.append(row)

            index += 1

    outfile = '{}/{}.xlsx'.format(files_path, doc_id)
    book.save(outfile)

    return doc_id
Exemplo n.º 46
0
 def cell(self, column, row):
     column = get_column_letter(column)
     return Cell(self, column, row)
Exemplo n.º 47
0
def test_set_bad_type():
    ws = build_dummy_worksheet()
    cell = Cell(ws, 'A', 1)
    with pytest.raises(ValueError):
        cell.set_explicit_value(1, 'q')
Exemplo n.º 48
0
def test_pivot_button(DummyWorksheet, Cell):
    ws = DummyWorksheet

    cell = Cell(ws, column="A", row=1, pivotButton=True)
    assert cell.pivotButton is True
Exemplo n.º 49
0
def get_audit_log_workbook(ws, program):

    # helper for indicator name column
    def _indicator_name(indicator):
        if indicator.results_aware_number:
            return u'{} {}: {}'.format(
                _('Indicator'),
                str(indicator.results_aware_number),
                str(indicator.name),
            )
        else:
            return u'{}: {}'.format(
                _('Indicator'),
                str(indicator.name),
            )

    # helper for result level column
    def _result_level(indicator):
        if indicator.leveltier_name and indicator.level_display_ontology:
            return u'{} {}'.format(
                str(indicator.leveltier_name),
                str(indicator.level_display_ontology),
            )
        elif indicator.leveltier_name:
            return str(indicator.leveltier_name)
        else:
            return ''

    header = [
        Cell(ws, value=_("Date and Time")),
        # Translators: Number of the indicator being shown
        Cell(ws, value=_('Result Level')),
        Cell(ws, value=_('Indicator')),
        Cell(ws, value=_('User')),
        Cell(ws, value=_('Organization')),
        # Translators: Part of change log, indicates the type of change being made to a particular piece of data
        Cell(ws, value=_('Change type')),
        # Translators: Part of change log, shows what the data looked like before the changes
        Cell(ws, value=_('Previous entry')),
        # Translators: Part of change log, shows what the data looks like after the changes
        Cell(ws, value=_('New entry')),
        # Translators: Part of change log, reason for the change as entered by the user
        Cell(ws, value=_('Rationale'))
    ]

    title = Cell(ws, value=_("Change log"))
    title.font = Font(size=18)
    ws.append([title,])
    ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=len(header))
    subtitle = Cell(ws, value=program.name)
    subtitle.font = Font(size=18)
    ws.append([subtitle,])
    ws.merge_cells(start_row=2, end_row=2, start_column=1, end_column=len(header))


    header_font = Font(bold=True)
    header_fill = PatternFill('solid', 'EEEEEE')

    for h in header:
        h.font = header_font
        h.fill = header_fill

    ws.append(header)

    alignment = Alignment(
        horizontal='general',
        vertical='top',
        text_rotation=0,
        wrap_text=True,
        shrink_to_fit=False,
        indent=0
    )

    for row in program.audit_logs.all().order_by('-date'):
        prev_string = u''
        for entry in row.diff_list:
            if entry['name'] == 'targets':
                for k, target in entry['prev'].items():
                    prev_string += str(target['name']) + u": " + str(target['value']) + u"\r\n"

            else:
                prev_string += str(entry['pretty_name']) + u": "
                prev_string += str(entry['prev'] if entry['prev'] else _('N/A')) + u"\r\n"

        new_string = u''
        for entry in row.diff_list:
            if entry['name'] == 'targets':
                for k, target in entry['new'].items():
                    new_string += str(target['name']) + u": " + str(target['value']) + u"\r\n"

            else:
                new_string += str(entry['pretty_name']) + u": "
                new_string += str(entry['new'] if entry['new'] else _('N/A')) + u"\r\n"

        xl_row = [
            Cell(ws, value=row.date),
            Cell(ws, value=str(_result_level(row.indicator)) if row.indicator else _('N/A')),
            Cell(ws, value=str(_indicator_name(row.indicator)) if row.indicator else _('N/A')),
            Cell(ws, value=str(row.user.name)),
            Cell(ws, value=str(row.organization.name)),
            Cell(ws, value=str(row.pretty_change_type)),
            Cell(ws, value=str(prev_string)),
            Cell(ws, value=str(new_string)),
            Cell(ws, value=str(row.rationale))
        ]
        for cell in xl_row:
            cell.alignment = alignment
        ws.append(xl_row)

    for rd in ws.row_dimensions:
        rd.auto_size = True

    for cd in ws.column_dimensions:
        cd.auto_size = True
    widths = [20, 12, 50, 20, 15, 20, 40, 40, 40]
    for col_no, width in enumerate(widths):
        ws.column_dimensions[utils.get_column_letter(col_no + 1)].width = width
    return ws
Exemplo n.º 50
0
def dummy_cell(DummyWorksheet, Cell):
    ws = DummyWorksheet
    cell = Cell(ws, column=1, row=1)
    return cell
Exemplo n.º 51
0
 def cell(self, column, row):
     return Cell(self, row=row, column=column)
Exemplo n.º 52
0
 def mk_cell(value):
     cell = Cell(ws)
     cell.number_format = '@'  # Ensure text cell format
     cell.set_explicit_value(value)
     return cell
def test_repr():
    wb = Workbook()
    ws = Worksheet(wb)
    cell = Cell(ws, 'A', 1)
    assert repr(cell), '<Cell Sheet1.A1>' == 'Got bad repr: %s' % repr(cell)
Exemplo n.º 54
0
def test_number_format(DummyWorksheet, Cell):
    ws = DummyWorksheet
    ws.parent._number_formats.add("dd--hh--mm")

    cell = Cell(ws, column="A", row=1, numFmtId=164)
    assert cell.number_format == "dd--hh--mm"
Exemplo n.º 55
0
from pympler.muppy import print_summary
from openpyxl.cell import Cell
d = [Cell(None, 'A', 1) for i in range(100000)]
print_summary()
Exemplo n.º 56
0
    def parse_cell(self, element):
        value = element.find(self.VALUE_TAG)
        if value is not None:
            value = value.text
        formula = element.find(self.FORMULA_TAG)
        data_type = element.get('t', 'n')
        coordinate = element.get('r')
        style_id = element.get('s')

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


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


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

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

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

        if self.guess_types or value is None:
            cell.value = value
        else:
            cell._value=value
            cell.data_type=data_type
Exemplo n.º 57
0
 def cell(value):
     return Cell(Worksheet(Workbook()), value=value)
Exemplo n.º 58
0
 def style_single_cell(self, cell: Cell, style: Style) -> None:
     cell.border = style.border
     cell.alignment = style.alignment
     cell.font = style.font
def test_initial_value():
    ws = build_dummy_worksheet()
    cell = Cell(ws, 'A', 1, value='17.5')
    assert cell.TYPE_STRING == cell.data_type
Exemplo n.º 60
0
 def setup_class(cls):
     cls.cell = Cell(None, 'A', 1)