def append(self, iterable): """Appends a group of values at the bottom of the current sheet. * If it's a list: all values are added in order, starting from the first column * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) :param iterable: list, range or generator, or dict containing values to append :type iterable: list|tuple|range|generator or dict Usage: * append(['This is A1', 'This is B1', 'This is C1']) * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) * **or** append({1 : 'This is A1', 3 : 'This is C1'}) :raise: TypeError when iterable is neither a list/tuple nor a dict """ row_idx = self._current_row + 1 if (isinstance(iterable, (list, tuple, range)) or isgenerator(iterable)): for col_idx, content in enumerate(iterable, 1): if isinstance(content, Cell): # compatible with write-only mode cell = content if cell.parent and cell.parent != self: raise ValueError( "Cells cannot be copied from other worksheets") cell.parent = self cell.col_idx = col_idx cell.row = row_idx else: cell = Cell(self, row=row_idx, col_idx=col_idx, value=content) self._row_by_index(row_idx)[col_idx] = cell elif isinstance(iterable, dict): for col_idx, content in iterable.items(): if isinstance(col_idx, basestring): col_idx = column_index_from_string(col_idx) cell = Cell(self, row=row_idx, col_idx=col_idx, value=content) self._row_by_index(row_idx)[col_idx] = cell else: self._invalid_row(iterable) self._current_row = row_idx
def write_xlsx(writing_data, headers, output_file, color_delta=False): outfile_path = os.path.join(os.path.dirname(output_file), os.path.basename(output_file) + '.xlsx') wb = Workbook() ws = wb.active thin = Side(border_style="thin", color="000000") double = Side(border_style="medium", color="000000") header_cells = [Cell(ws, column="A", row=1, value=header) for header in headers] for cell in header_cells: cell.font = Font(bold=True) cell.border = Border(top=double, left=double, right=double, bottom=double) cell.alignment = Alignment(horizontal="center", vertical="center") ws.append(header_cells) for report_name, report in writing_data.items(): title_cell = Cell(ws, column="A", row=1, value=report['title']) title_cell.font = Font(bold=True) # title_cell.alignment = Alignment(horizontal="center", vertical="center") ws.append([title_cell]) for row in report['rows']: cells = [Cell(ws, column="A", row=1, value=(row[header] if header in row else "")) for header in headers] for i,cell in enumerate(cells): # if i == 0: # cell.font = Font(bold=True) cell.border = Border(top=thin, left=thin, right=thin, bottom=thin) cell.alignment = Alignment(horizontal="center", vertical="center") if color_delta: if str(cell.value).startswith('+'): if float(cell.value) != 0: cell.font = Font(bold=True, color='00a933') else: cell.font = Font(bold=True) elif str(cell.value).startswith('-'): if float(cell.value) != 0: cell.font = Font(bold=True, color='c9211e') else: cell.font = Font(bold=True) ws.append(cells) column_widths = [] for i,header in enumerate(headers): column_widths.append(len(header) * 1.4 + 4) for i, column_width in enumerate(column_widths): ws.column_dimensions[chr(ord('A') + i)].width = column_width wb.save(outfile_path)
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
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
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"
def CellCopy(ws, s_cell): t_cell = Cell(ws, value=s_cell.value) t_cell.font = copy(s_cell.font) t_cell.fill = copy(s_cell.fill) t_cell.alignment = copy(s_cell.alignment) t_cell.border = copy(s_cell.border) return t_cell
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)
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
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)
def __getitem__(self, value): cell = self._cells.get(value) if cell is None: cell = Cell(self, 'A', 1) self._cells[value] = cell return cell
def StyleMerge(ws, val, range): ws.merge_cells(range) cell = Cell(ws, value=val) cell.font = Font(bold=True, color=colors.WHITE) cell.fill = PatternFill(fill_type='solid', fgColor=colors.BLACK) cell.alignment = Alignment(horizontal='center') return cell
def test_quote_prefix(DummyWorksheet, Cell): ws = DummyWorksheet cell = Cell(ws, column="A", row=1) cell.style_id cell._style.quotePrefix = 1 assert cell.quotePrefix is True
def test_pivot_button(DummyWorksheet, Cell): ws = DummyWorksheet cell = Cell(ws, column="A", row=1) cell.style_id cell._style.pivotButton = 1 assert cell.pivotButton is True
def test_protection(DummyWorksheet, Cell): from openpyxl.styles import Protection prot = Protection(locked=False) ws = DummyWorksheet ws.parent._protections.add(prot) cell = Cell(ws, column="A", row=1) assert cell.protection == prot
def test_alignment(DummyWorksheet, Cell): from openpyxl.styles import Alignment align = Alignment(wrapText=True) ws = DummyWorksheet ws.parent._alignments.add(align) cell = Cell(ws, column="A", row=1) assert cell.alignment == align
def test_border(DummyWorksheet, Cell): from openpyxl.styles import Border border = Border() ws = DummyWorksheet ws.parent._borders.add(border) cell = Cell(ws, column='A', row=1) assert cell.border == border
def test_fill(DummyWorksheet, Cell): from openpyxl.styles import PatternFill fill = PatternFill(patternType="solid", fgColor="FF0000") ws = DummyWorksheet ws.parent._fills.add(fill) cell = Cell(ws, column='A', row=1) assert cell.fill == fill
def test_font(DummyWorksheet, Cell): from openpyxl.styles import Font font = Font(bold=True) ws = DummyWorksheet ws.parent._fonts.add(font) cell = Cell(ws, column='A', row=1) assert cell.font == font
def StyleRange(ws, str_list, isBold, fillColor): row_list = [] for str_i in str_list: cell = Cell(ws, value=str_i) cell.font = Font(bold=isBold) cell.fill = PatternFill(fill_type='solid', fgColor=fillColor) row_list.append(cell) return row_list
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)
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
def test_write_cell(LXMLWorksheet, value, expected): from openpyxl.cell import Cell from ..dump_lxml import write_cell ws = LXMLWorksheet c = Cell(ws, 'A', 1, value) el = write_cell(ws, c) xml = tostring(el) diff = compare_xml(xml, expected) assert diff is None, diff
def bind_cells(self): for idx, row in self.parser.parse(): for cell in row: style = self.ws.parent._cell_styles[cell['style_id']] c = Cell(self.ws, row=cell['row'], column=cell['column'], style_array=style) c._value = cell['value'] c.data_type = cell['data_type'] self.ws._cells[(cell['row'], cell['column'])] = c self.ws.formula_attributes = self.parser.array_formulae
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)
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
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='0.00_);[Red]\(0.00\)') assert cell.is_date() is False
def _get_cell(self, row, column): """ Internal method for getting a cell from a worksheet. Will create a new cell if one doesn't already exist. """ coordinate = (row, column) if not coordinate in self._cells: cell = Cell(self, row=row, column=column) self._add_cell(cell) return self._cells[coordinate]
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)
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])
def cfp() -> CellFullPath: wb = Workbook() ws = wb.active ws['B2'] = 30 ws['A2'] = 'the key' cell = Cell(row=2, column=2, worksheet=wb) cell_full_path = CellFullPath(workbook=wb, sheet=wb.active, cell=cell) return cell_full_path