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
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
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)
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)
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
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
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)
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
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
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 row_col(self) -> Tuple[int, int]:
"""
Returns:
Tuple[int, int]. row, col
"""
return coordinate_to_tuple(self.coordinate)
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
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)
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
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)
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)
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)
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
}
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
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))
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)
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)
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
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')
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)
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
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 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
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}
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
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
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
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)
def __delitem__(self, key):
row, column = coordinate_to_tuple(key)
if (row, column) in self._cells:
del self._cells[(row, column)]
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
def __delitem__(self, key):
row, column = coordinate_to_tuple(key)
if (row, column) in self._cells:
del self._cells[(row, column)]
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)