def coordinates(self, coords): """ set shape coordinates in percentages (left, top, right, bottom) """ # this needs refactoring to reflect changes in charts self.axis_coordinates = coords (x1, y1), (x2, y2) = coords # bottom left, top right drawing_width = pixels_to_EMU(self.chart.drawing.width) drawing_height = pixels_to_EMU(self.chart.drawing.height) plot_width = drawing_width * self.chart.width plot_height = drawing_height * self.chart.height margin_left = self.chart._get_margin_left() * drawing_width xunit = plot_width / self.chart.get_x_units() margin_top = self.chart._get_margin_top() * drawing_height yunit = self.chart.get_y_units() x_start = (margin_left + (float(x1) * xunit)) / drawing_width y_start = (margin_top + plot_height - (float(y1) * yunit)) / drawing_height x_end = (margin_left + (float(x2) * xunit)) / drawing_width y_end = (margin_top + plot_height - (float(y2) * yunit)) / drawing_height # allow user to specify y's in whatever order # excel expect y_end to be lower if y_end < y_start: y_end, y_start = y_start, y_end self._coordinates = ( self._norm_pct(x_start), self._norm_pct(y_start), self._norm_pct(x_end), self._norm_pct(y_end), )
def coordinates(self, coords): """ set shape coordinates in percentages (left, top, right, bottom) """ # this needs refactoring to reflect changes in charts self.axis_coordinates = coords (x1, y1), (x2, y2) = coords # bottom left, top right drawing_width = pixels_to_EMU(self.chart.drawing.width) drawing_height = pixels_to_EMU(self.chart.drawing.height) plot_width = drawing_width * self.chart.width plot_height = drawing_height * self.chart.height margin_left = self.chart._get_margin_left() * drawing_width xunit = plot_width / self.chart.get_x_units() margin_top = self.chart._get_margin_top() * drawing_height yunit = self.chart.get_y_units() x_start = (margin_left + (float(x1) * xunit)) / drawing_width y_start = ((margin_top + plot_height - (float(y1) * yunit)) / drawing_height) x_end = (margin_left + (float(x2) * xunit)) / drawing_width y_end = ((margin_top + plot_height - (float(y2) * yunit)) / drawing_height) # allow user to specify y's in whatever order # excel expect y_end to be lower if y_end < y_start: y_end, y_start = y_start, y_end self._coordinates = (self._norm_pct(x_start), self._norm_pct(y_start), self._norm_pct(x_end), self._norm_pct(y_end))
def calc_img_anchor(self, cell, img): ## 対象セルのwidth / heightを取得 width_list = self.get_cell_width_list(cell, to_px=True) height_list = self.get_cell_height_list(cell, to_px=True) self.logger.info("width_list: %s" % width_list) self.logger.info("height_list: %s" % height_list) img_width = img.width img_height = img.height self.logger.info("img_width: %s" % img_width) self.logger.info("img_height: %s" % img_height) ## imgのwidth / heightを使って、+何セルかを計算 for i, cell_width in enumerate(width_list): if 0 < (img_width - cell_width): img_width -= cell_width self.logger.info("calclating...: %s" % img_width) else: break horizontal_cell_shift = i - 1 self.logger.info("horizontal_cell_shift: %s" % horizontal_cell_shift) for i, cell_height in enumerate(height_list): if 0 < (img_height - cell_height): img_height -= cell_height print("calclating...: %s" % img_height) else: break vertical_cell_shift = i - 1 self.logger.info("vertical_cell_shift: %s" % vertical_cell_shift) ## 渡されたcellの縦横に足す anchor_cell_column = cell.column + horizontal_cell_shift anchor_cell_row = cell.row + vertical_cell_shift ## 余りをEMUに変換 anchor_cell_column_offset = pixels_to_EMU(img_width) anchor_cell_row_offset = pixels_to_EMU(img_height) self.logger.info("anchor_cell_column: %s" % anchor_cell_column) self.logger.info("anchor_cell_column_offset: %s" % anchor_cell_column_offset) self.logger.info("anchor_cell_row: %s" % anchor_cell_row) self.logger.info("anchor_cell_row_offset: %s" % anchor_cell_row_offset) ## anchorのcellとoffsetをreturn return anchor_cell_column, anchor_cell_column_offset, anchor_cell_row, anchor_cell_row_offset
def anchor(self): from .spreadsheet_drawing import (OneCellAnchor, TwoCellAnchor, AbsoluteAnchor) if self.anchortype == "absolute": anchor = AbsoluteAnchor() anchor.pos.x = pixels_to_EMU(self.left) anchor.pos.y = pixels_to_EMU(self.top) elif self.anchortype == "oneCell": anchor = OneCellAnchor() anchor._from.col = self.anchorcol anchor._from.row = self.anchorrow anchor.ext.width = pixels_to_EMU(self._width) anchor.ext.height = pixels_to_EMU(self._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 _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 anchor(self): from .spreadsheet_drawing import ( OneCellAnchor, TwoCellAnchor, AbsoluteAnchor) if self.anchortype == "absolute": anchor = AbsoluteAnchor() anchor.pos.x = pixels_to_EMU(self.left) anchor.pos.y = pixels_to_EMU(self.top) elif self.anchortype == "oneCell": anchor = OneCellAnchor() anchor._from.col = self.anchorcol anchor._from.row = self.anchorrow anchor.ext.width = pixels_to_EMU(self._width) anchor.ext.height = pixels_to_EMU(self._height) return anchor
def get_emu_dimensions(self): """ return (x, y, w, h) in EMU """ return (pixels_to_EMU(self.left), pixels_to_EMU(self.top), pixels_to_EMU(self._width), pixels_to_EMU(self._height))
def main(cell_w_str, cell_h_str, files): wb = Workbook() ws = wb.active # some constants, may be different from each languages and versions # cell native size multiplier (native cell size -> openpyxl cell size) # can be calibrated by checking applied cell size in excel cwnm, chnm = 24.3 / 23.6, 1 # cell pixel size multiplier (openpyxl cell size -> pixel cell size) # can be calibrated by checking displayed cell size in pixels on 100% zoom in excel cwm, chm = 1800 / 180, 582 / 350 # image pixel size multiplier (pixel image size -> openpyxl image size) # can be calibrated by checking displayed image size in pixels on 100% zoom in excel iwm, ihm = 4 / 5, 4 / 5 # image pixel offset multiplier (pixel image offset -> openpyxl image offset) # can be calibrated by checking displayed image offset in pixels on 100% zoom in excel iwom, ihom = 4 / 5, 4 / 5 cell_w_native = float(cell_w_str) * cwnm cell_h_native = float(cell_h_str) * chnm file_count = len(files) for i, file in enumerate(files): print('Image %02d/%02d' % (i + 1, file_count)) #set target row, col, and their size row, col = i, 0 ws.column_dimensions[get_column_letter(col + 1)].width = cell_w_native ws.row_dimensions[row + 1].height = cell_h_native cell_w, cell_h = cell_w_native * cwm, cell_h_native * chm #open image and get dimensions img = Image(file) img_w, img_h = img.width, img.height # calculate image dimensions to cell size cell_ratio = cell_w / cell_h image_ratio = img_w / img_h if image_ratio > cell_ratio: scale = cell_w / img_w x_offset = 0 y_offset = (cell_h - img_h * scale) / 2 else: scale = cell_h / img_h x_offset = (cell_w - img_w * scale) / 2 y_offset = 0 img_size = XDRPositiveSize2D(pixels_to_EMU(img_w * iwm * scale), pixels_to_EMU(img_h * ihm * scale)) # insert image using OneCellAnchor (no stretching error) marker = AnchorMarker(col=col, colOff=pixels_to_EMU(x_offset * iwom), row=row, rowOff=pixels_to_EMU(y_offset * ihom)) img.anchor = OneCellAnchor(_from=marker, ext=img_size) ws.add_image(img) # save file wb.save('images.xlsx')
def get_y_units(self): """ calculate one unit for y axis in EMU """ dh = pixels_to_EMU(self.drawing.height) return (dh * self.height) / self.y_axis.max