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