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 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