def merged_cells(self):
"""Utility for checking whether a cell has been merged or not"""
cells = set()
for _range in self._merged_cells:
for row in rows_from_range(_range):
cells = cells.union(set(row))
return cells
def get(self, request):
selected_date = datetime.datetime.fromtimestamp(
float(request.GET.get('date')) / 1000)
file_path = os.path.dirname(
__file__) + '/../files/' + selected_date.strftime(
'%Y-%m-%d') + '.xlsx'
try:
wb = load_workbook(filename=file_path)
# wb.get_sheet_names()
response = {}
sheet_names = wb.get_sheet_names()
for item in sheet_names:
if item == 'Introduction':
continue
ws = wb.get_sheet_by_name(item)
sheet_data = {}
for m_range in ws.merged_cell_ranges:
merged_cells = list(pyxl_utils.rows_from_range(m_range))
table_title, table_data, array_data = get_table_data(
ws, merged_cells)
sheet_data[table_title] = array_data
response[item] = sheet_data
return JsonResponse(dict(state=True, res=response))
except Exception as e:
return JsonResponse(dict(state=False))
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if (start_row is None
or start_column is None
or end_row is None
or end_column is None):
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
cells = rows_from_range(range_string)
# only the top-left cell is preserved
for c in islice(chain.from_iterable(cells), 1, None):
if c in self._cells:
del self._cells[c]
if c in self.hyperlinks:
del self._hyperlinks[c]
def merged_cells(self):
"""Utility for checking whether a cell has been merged or not"""
cells = set()
for _range in self._merged_cells:
for row in rows_from_range(_range):
cells = cells.union(set(row))
return cells
def merge_cells(self,
range_string=None,
start_row=None,
start_column=None,
end_row=None,
end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
if not range_string:
if (start_row is None or start_column is None or end_row is None
or end_column is None):
msg = "You have to provide a value either for "\
"'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise InsufficientCoordinatesException(msg)
else:
range_string = '%s%s:%s%s' % (
get_column_letter(start_column), start_row,
get_column_letter(end_column), end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell
msg = "Range must be a cell range (e.g. A1:E1)"
raise InsufficientCoordinatesException(msg)
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
cells = rows_from_range(range_string)
# only the top-left cell is preserved
for c in islice(chain.from_iterable(cells), 1, None):
if c in self._cells:
del self._cells[c]
if c in self.hyperlinks:
del self._hyperlinks[c]
def get(self, request):
selected_date = datetime.datetime.strptime(request.GET.get('date'),
'%a %b %d %Y')
selected = ExcelFile.objects.filter(date=selected_date).first()
if not selected:
return JsonResponse(dict(state=False))
file_path = selected.excel_file.path
if file_path:
wb = load_workbook(filename=file_path)
# wb.get_sheet_names()
response = {}
sheet_names = wb.get_sheet_names()
for item in sheet_names:
if item == 'Introduction':
continue
ws = wb.get_sheet_by_name(item)
sheet_data = {}
for m_range in ws.merged_cell_ranges:
merged_cells = list(pyxl_utils.rows_from_range(m_range))
table_title, table_data, array_data = get_table_data(
ws, merged_cells)
sheet_data[table_title] = array_data
response[item] = sheet_data
return JsonResponse(dict(state=True, res=response))
return JsonResponse(dict(state=False))
def getMergedCellValue(sheet,cell):
cellidx=cell.coordinate
for range in sheet.merged_cells.ranges:
merged_cells = list(utils.rows_from_range(str(range)))
for row in merged_cells:
if cellidx in row:
return sheet[merged_cells[0][0]].value
return cell.value
def getMergedCellPresent(sheet, cell):
idx = cell.coordinate
for range_ in sheet.merged_cell_ranges:
merged_cells = list(rows_from_range(range_))
for row in merged_cells:
if idx in row:
return sheet[merged_cells[0][0]]
return cell
def format_selection(selection,
sheet,
style,
font_a=Font(bold=True, size=12, color='FFFFFF')):
for row in rows_from_range(selection):
for cell in row:
sheet[cell].style = style
sheet[cell].font = font_a
def get_cell_height(self, cell):
cell_range = self.get_merged_cell(cell)
if cell_range:
height = 0
for row in list(rows_from_range(str(cell_range))):
height += self.ws.row_dimensions[self.ws[row[0]].row].height
else:
return round(height)
else:
return round(self.ws.row_dimensions[cell.row].height)
def expand_cell_ranges(range_string):
"""
Expand cell ranges to a sequence of addresses.
Reverse of collapse_cell_addresses
Eg. converts "A1:A2 B1:B2" to (A1, A2, B1, B2)
"""
cells = []
for rs in range_string.split():
cells.extend(rows_from_range(rs))
return set(chain.from_iterable(cells))
def expand_cell_ranges(range_string):
"""
Expand cell ranges to a sequence of addresses.
Reverse of collapse_cell_addresses
Eg. converts "A1:A2 B1:B2" to (A1, A2, B1, B2)
"""
cells = []
for rs in range_string.split():
cells.extend(rows_from_range(rs))
return set(chain(cells))
def expand_cell_ranges(range_string):
"""
Expand cell ranges to a sequence of addresses.
Reverse of collapse_cell_addresses
Eg. converts "A1:A2 B1:B2" to (A1, A2, B1, B2)
"""
# expand ranges to rows and then flatten
rows = (rows_from_range(rs) for rs in range_string.split()) # list of rows
cells = (chain(*row) for row in rows) # flatten rows
return set(chain(*cells))
def getValueWithMergeLookup(sheet, cell):
idx = cell.coordinate
for range_ in sheet.merged_cell_ranges:
merged_cells = list(rows_from_range(range_))
for row in merged_cells:
if idx in row:
# If this is a merged cell,
# return the first cell of the merge range
return sheet[merged_cells[0][0]].value
return sheet.cell(idx).value
def get_cell_height_list(self, cell, to_px=False):
ret = []
cell_range = self.get_merged_cell(cell)
if cell_range:
height = 0
for row in list(rows_from_range(str(cell_range))):
ret.append(self.ws.row_dimensions[self.ws[row[0]].row].height)
else:
ret.append(self.ws.row_dimensions[cell.row].height)
if to_px:
ret = [round(height * 1.33) for height in ret]
return ret
def post(self, request, *args, **kwargs):
uploaded = request.FILES.get('file', None)
if not uploaded:
return JsonResponse(dict(status=False))
wb = load_workbook(filename=BytesIO(uploaded.read()))
# wb.get_sheet_names()
ws = wb.get_sheet_by_name('vCenter')
values = []
response = {}
for m_range in ws.merged_cell_ranges:
merged_cells = list(pyxl_utils.rows_from_range(m_range))
table_title, table_data, array_data = get_table_data(
ws, merged_cells)
response[table_title] = array_data
return JsonResponse(response)
def worksheet_to_data(ws, locale=None, fs=None, default_cell_border="none"):
merged_cell_map = {}
if OPENPYXL_24:
merged_cell_ranges = ws.merged_cell_ranges
excluded_cells = set(ws.merged_cells)
else:
merged_cell_ranges = [
cell_range.coord for cell_range in ws.merged_cells.ranges
]
excluded_cells = set([
cell for cell_range in merged_cell_ranges
for rows in rows_from_range(cell_range) for cell in rows
])
for cell_range in merged_cell_ranges:
cell_range_list = list(ws[cell_range])
m_cell = cell_range_list[0][0]
merged_cell_map[m_cell.coordinate] = {
'attrs': {
'colspan': len(cell_range_list[0]),
'rowspan': len(cell_range_list),
},
'cells': [c for rows in cell_range_list for c in rows],
}
excluded_cells.remove(m_cell.coordinate)
max_col_number = 0
data_list = []
for row_i, row in enumerate(ws.iter_rows()):
data_row = []
data_list.append(data_row)
for col_i, cell in enumerate(row):
row_dim = ws.row_dimensions[cell.row]
if cell.coordinate in excluded_cells or row_dim.hidden:
continue
if col_i > max_col_number:
max_col_number = col_i
height = 19
if row_dim.customHeight:
height = round(row_dim.height, 2)
f_cell = None
if fs:
f_cell = fs[cell.coordinate]
cell_data = {
'column': cell.column,
'row': cell.row,
'value': cell.value,
'formatted_value': format_cell(cell,
locale=locale,
f_cell=f_cell),
'attrs': {
'id': get_cell_id(cell)
},
'style': {
"height": "{}px".format(height),
},
}
merged_cell_info = merged_cell_map.get(cell.coordinate, {})
if merged_cell_info:
cell_data['attrs'].update(merged_cell_info['attrs'])
cell_data['style'].update(
get_styles_from_cell(cell, merged_cell_info,
default_cell_border))
data_row.append(cell_data)
col_list = []
max_col_number += 1
column_dimensions = sorted(ws.column_dimensions.items(),
key=lambda d: column_index_from_string(d[0]))
for col_i, col_dim in column_dimensions:
if not all([col_dim.min, col_dim.max]):
continue
width = 0.89
if col_dim.customWidth:
width = round(col_dim.width / 10., 2)
col_width = 96 * width
for _ in six.moves.range((col_dim.max - col_dim.min) + 1):
max_col_number -= 1
col_list.append({
'index': col_dim.index,
'hidden': col_dim.hidden,
'style': {
"width": "{}px".format(col_width),
}
})
if max_col_number < 0:
break
return {'rows': data_list, 'cols': col_list}
def worksheet_to_data(ws, locale=None, fs=None, default_cell_border="none"):
merged_cell_map = {}
if OPENPYXL_24:
merged_cell_ranges = ws.merged_cell_ranges
excluded_cells = set(ws.merged_cells)
else:
merged_cell_ranges = [cell_range.coord for cell_range in ws.merged_cells.ranges]
excluded_cells = set(
[
cell
for cell_range in merged_cell_ranges
for rows in rows_from_range(cell_range)
for cell in rows
]
)
for cell_range in merged_cell_ranges:
cell_range_list = list(ws[cell_range])
m_cell = cell_range_list[0][0]
colspan = len(cell_range_list[0])
rowspan = len(cell_range_list)
merged_cell_map[m_cell.coordinate] = {
"attrs": {
"colspan": None if colspan <= 1 else colspan,
"rowspan": None if rowspan <= 1 else rowspan,
},
"cells": [c for rows in cell_range_list for c in rows],
}
excluded_cells.remove(m_cell.coordinate)
max_col_number = 0
data_list = []
for row_i, row in enumerate(ws.iter_rows()):
data_row = []
data_list.append(data_row)
for col_i, cell in enumerate(row):
row_dim = ws.row_dimensions[cell.row]
if cell.coordinate in excluded_cells or row_dim.hidden:
continue
if col_i > max_col_number:
max_col_number = col_i
height = 19
if row_dim.customHeight:
height = round(row_dim.height, 2)
f_cell = None
if fs:
f_cell = fs[cell.coordinate]
cell_data = {
"column": cell.column,
"row": cell.row,
"value": cell.value,
"formatted_value": format_cell(cell, locale=locale, f_cell=f_cell),
"attrs": {"id": get_cell_id(cell)},
"style": {"height": f"{height}pt"},
}
merged_cell_info = merged_cell_map.get(cell.coordinate, {})
if merged_cell_info:
cell_data["attrs"].update(merged_cell_info["attrs"])
cell_data["style"].update(
get_styles_from_cell(cell, merged_cell_info, default_cell_border)
)
data_row.append(cell_data)
col_list = []
max_col_number += 1
column_dimensions = sorted(
ws.column_dimensions.items(), key=lambda d: column_index_from_string(d[0])
)
for col_i, col_dim in column_dimensions:
if not all([col_dim.min, col_dim.max]):
continue
width = 0.89
if col_dim.customWidth:
width = round(col_dim.width / 10.0, 2)
col_width = 96 * width
for _ in six.moves.range((col_dim.max - col_dim.min) + 1):
max_col_number -= 1
col_list.append(
{
"index": col_dim.index,
"hidden": col_dim.hidden,
"style": {"width": "{}px".format(col_width)},
}
)
if max_col_number < 0:
break
return {"rows": data_list, "cols": col_list, "images": images_to_data(ws)}
def get_print_area_last_row(self):
print_area = rows_from_range(self.get_print_area())
return self.ws[list(print_area)[-1][0]].row
def get_merged_cell(self, cell):
for cell_range in self.get_merged_cells():
for row in list(rows_from_range(str(cell_range))):
if cell.coordinate in row:
return cell_range
def copy_range(range_str, src, dst):
for row in rows_from_range(range_str):
for cell in row:
dst[cell].value = src[cell].value
return
