def copy_worksheet(self, from_worksheet):
"""Copy an existing worksheet in the current workbook
:warning: This function cannot copy worksheets between workbooks.
worksheets can only be copied within the workbook that they belong
:param from_worksheet: the worksheet to be copied from
:return: copy of the initial worksheet
"""
if self.__write_only or self._read_only:
raise ValueError("Cannot copy worksheets in read-only or write-only mode")
new_title = u"{0} Copy".format(from_worksheet.title)
to_worksheet = self.create_sheet(title=new_title)
cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet)
cp.copy_worksheet()
return to_worksheet
def copy_worksheet(self, from_worksheet):
"""Copy an existing worksheet in the current workbook
:warning: This function cannot copy worksheets between workbooks.
worksheets can only be copied within the workbook that they belong
:param from_worksheet: the worksheet to be copied from
:return: copy of the initial worksheet
"""
if self.__write_only or self._read_only:
raise ValueError("Cannot copy worksheets in read-only or write-only mode")
new_title = u"{0} Copy".format(from_worksheet.title)
to_worksheet = self.create_sheet(title=new_title)
cp = WorksheetCopy(source_worksheet=from_worksheet, target_worksheet=to_worksheet)
cp.copy_worksheet()
return to_worksheet
def copy_sheet(self,
source: Worksheet,
new_title: str,
index: Optional[int] = None):
target = self.__getitem__((index, new_title))
WorksheetCopy(source_worksheet=source,
target_worksheet=target).copy_worksheet()
return target
def generate(self, team_list: SaarTeamList):
self._wb = load_workbook(filename="templates/Wertungsbogen_Master.xlsx")
assert isinstance(self._wb, Workbook)
master_ws = self._wb.active
version_string = master_ws["F2"].value
check_version(version_string)
i = 0
for team in team_list:
assert isinstance(team, SaarTeam)
for apparatus_f, apparatus_m in APPARATUS:
title = "{}_{}_{}".format(team.name[:10], apparatus_f[:2], apparatus_m[:2])
ws = self._wb.create_sheet(title=title)
ws_copy = WorksheetCopy(master_ws, ws)
ws_copy.copy_worksheet()
# set smaller page margins
assert isinstance(ws, Worksheet)
ws.page_margins = PageMargins(.2, .2, .75, .75, .314, .314)
# set contents
ws["F1"] = apparatus_f
ws["F18"] = apparatus_m
ws["A2"] = team.name
ws["A19"] = team.name
offset = 4
for num, gymnast in enumerate(team.get_gymnasts(SaarGymnast.FEMALE)):
row = offset + num
ws["A{}".format(row)] = num + 1
ws["B{}".format(row)] = gymnast.name
ws["C{}".format(row)] = gymnast.surname
offset = 21
for num, gymnast in enumerate(team.get_gymnasts(SaarGymnast.MALE)):
row = offset + num
ws["A{}".format(row)] = num + 1
ws["B{}".format(row)] = gymnast.name
ws["C{}".format(row)] = gymnast.surname
i += 1
self._wb.remove(self._wb["Master"])
def append_styled_sheets(self, wb):
if '_template' not in wb.sheetnames:
wb.create_sheet('_template')
if '_template' in self.styled_wb.sheetnames:
WorksheetCopy(self.styled_wb['_template'],
wb['_template']).copy_worksheet()
# wb['_template'].copy_worksheet(wb['_template'])
return wb
def insert_akt_vh(akt_vh, road_programm, road_db, tube, km_start, km_finish,
dy_tube):
wb = load_workbook(road_programm + road_to_excel)
for i, x in enumerate(akt_vh):
current_wb = wb.create_sheet('%s. %s' % (i + 3, x['name']))
copy_ws = WorksheetCopy(wb['Empty'], current_wb)
copy_ws.copy_worksheet()
copy_ws._copy_cells()
copy_ws._copy_dimensions()
current_wb.print_area = 'A1:AB121'
current_wb.page_margins = wb['Empty'].page_margins
# --------------начинаем заполнение текущего акта
current_wb['A1'] = x['otv'][2] # служба
current_wb[
'A3'] = 'Устранение дефектов на секциях %s, %s-%s км, Ду %s мм.' % (
tube, km_start, km_finish, dy_tube) # заголовок
current_wb['K27'] = '%s, %s-%s км.' % (tube, km_start, km_finish
) # заголовок
current_wb['M11'] = x['date'] + ' г.' # дата
current_wb['P6'] = i + 3 # номер акта
current_wb['A8'] = '%s, в кол-ве %s %s.' % (
x['full_name'], x['kol'], x['marker']) # номер акта
current_wb['K13'] = ' '.join((x['otv'][1], x['otv'][2], x['otv'][0]))
current_wb['K19'] = ' '.join(
(x['contr'][1], x['contr'][2], x['contr'][0]))
current_wb['K17'] = ' '.join((x['sk'][1], x['sk'][2], x['sk'][0]))
current_wb['J32'] = x['doc']
current_wb['A35'] = x['param']
current_wb['H37'] = x['TU']
if 'OVP' in x:
current_wb['N47'] = x['OVP']
current_wb['L51'] = x['otv'][0]
current_wb['L57'] = x['contr'][0]
current_wb['L55'] = x['sk'][0]
current_wb.print_area = 'A1:AB58'
del (wb['Empty'])
# ---------------------электроды
ws = wb['1. 3.2 электроды 53.70']
ws['U4'] = akt_vh[0]['date'] + ' г.'
ws['O6'] = akt_vh[0]['otv'][0]
road = road_db.rpartition('/')[0] + '/Входной контроль'
if not os.path.exists(road):
os.makedirs(road)
wb.save(road + '/Акты входного контроля.xlsx')
# coding=utf-8
import openpyxl
from openpyxl.worksheet.copier import WorksheetCopy
workbook = openpyxl.load_workbook('test_img.xlsx') # 读取xlsx
template_worksheet = workbook['BUR']
new_worksheet = workbook.create_sheet('New_Sheet_Name')
instance = WorksheetCopy(template_worksheet, new_worksheet)
WorksheetCopy.copy_worksheet(instance)
# wb.create_sheet('test', index=0)
workbook.save('new_wb.xlsx')
def create_styled_sheet(wb, sheet_name, pos: int = None):
wb.create_sheet(sheet_name, pos)
if '_template' in wb.sheetnames and sheet_name != '_template':
WorksheetCopy(wb['_template'], wb[sheet_name]).copy_worksheet()