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
