def test_add_named_range(Workbook):
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = DefinedName('test_nr')
named_range.value = "Sheet!A1"
wb.add_named_range(named_range)
named_ranges_list = wb.get_named_ranges()
assert named_range in named_ranges_list
def write_names(self):
defined_names = copy(self.wb.defined_names)
# Defined names -> autoFilter
for idx, sheet in enumerate(self.wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase',
localSheetId=idx,
hidden=True)
name.value = u"{0}!{1}".format(
quote_sheetname(sheet.title),
absolute_coordinate(auto_filter))
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")
])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area
])
defined_names.append(name)
self.package.definedNames = defined_names
def addTableRows(wb, tableName, rowsCount):
ws = wb.active
# Определение диапазона таблицы
table = getTableObject(ws, tableName)
initRange = CellRange(table.ref)
# Определение диапазона, который включает в себя всё, что находится ниже данной таблицы
rangeToMove = CellRange(min_col=initRange.min_col,
max_col=initRange.max_col,
min_row=initRange.max_row + 1,
max_row=ws.max_row)
# Определение именованных диапазонов, находящихся на активном листе ниже данной таблицы
definedNames = {}
for name in wb.defined_names.definedName:
for dest in name.destinations:
if (dest[0] == ws.title) and (not CellRange(
dest[1]).isdisjoint(rangeToMove)):
if name.name in definedNames.keys():
definedNames[name.name].append(dest[1])
else:
definedNames[name.name] = [dest[1]]
# Переопределение всех объединённых ячеек, находящихся ниже данной таблицы
mergedCells = [a for a in ws.merged_cells.ranges]
for item in mergedCells:
if not item.isdisjoint(rangeToMove):
ws.unmerge_cells(str(item))
item.shift(row_shift=rowsCount)
ws.merge_cells(str(item))
# Дополнительное перемещение именованных диапазонов, находящихся ниже данной таблицы
for name, rangeList in definedNames.items():
newRangeList = []
for range in rangeList:
rangeObject = CellRange(range)
rangeObject.shift(row_shift=rowsCount)
newRangeList.append(
re.sub(r'([A-Z]+|[0-9]+)', r'$\1', str(rangeObject)))
newNamedRange = DefinedName(name=name,
attr_text='%s!%s' %
(ws.title, ';'.join(newRangeList)))
del wb.defined_names[name]
wb.defined_names.append(newNamedRange)
# Дополнительное перемещение таблиц, находящихся ниже данной таблицы
for tbl in ws._tables:
if not CellRange(tbl.ref).isdisjoint(rangeToMove):
newTblRange = CellRange(tbl.ref)
newTblRange.shift(row_shift=rowsCount)
tbl.ref = str(newTblRange)
# Сдвиг rangeToMove на количество добавляемых в таблицу строк
ws.move_range(rangeToMove, rows=rowsCount)
newTableRange = initRange
newTableRange.expand(down=rowsCount)
table.ref = str(newTableRange)
def test_multi_area_range_defined_name(fixture_xls_copy):
wb = Workbook()
ws = wb.active
ws['A1'] = 1
ws['A2'] = 2
ws['A3'] = 3
ws['A4'] = 4
ws['B1'] = '=SUM(A1,A2)'
ws['B2'] = '=SUM(_a2,A3)'
ws['B3'] = '=SUM(_a1_a3)'
wb.defined_names.append(
DefinedName(name='_a2', attr_text='Sheet!$A$4,Sheet!$A$1:$A$2'))
wb.defined_names.append(
DefinedName(name='_a1_a3', attr_text='Sheet!$A$1,Sheet!$A$3'))
excel_compiler = ExcelCompiler(excel=wb)
output_addrs = ['Sheet!B1:B3']
assert (3, 10, 4) == excel_compiler.evaluate(output_addrs[0])
excel_compiler.recalculate()
assert (3, 10, 4) == excel_compiler.evaluate(output_addrs[0])
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties() # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# workbook protection
root.workbookProtection = wb.security
# book views
active = get_active_sheet(wb)
if wb.views:
wb.views[0].activeTab = active
root.bookViews = wb.views
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = u"{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter)
)
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
root.definedNames = defined_names
# pivots
pivot_caches = set()
for pivot in wb._pivots:
if pivot.cache not in pivot_caches:
pivot_caches.add(pivot.cache)
c = PivotCache(cacheId=pivot.cacheId)
root.pivotCaches.append(c)
rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path)
wb.rels.append(rel)
c.id = rel.id
wb._pivots = [] # reset
root.calcPr = wb.calculation
return tostring(root.to_tree())
def test_duplicate_defined_name(self, Workbook):
wb1 = Workbook()
wb1.defined_names.append(DefinedName("dfn1"))
assert True == wb1._duplicate_name("dfn1")
assert True == wb1._duplicate_name("DFN1")
def generar_formulario_excel(file_path, save_file_path, id_empresa):
def gen_col_letter():
letter = dict(zip(range(1, 27), string.ascii_uppercase))
alphabet = string.ascii_uppercase
letters = []
numbers = []
for i in range(0,26*27):
x = i//26
if x == 0:
character = alphabet[i]
else:
y = i-(26*x)
character = letter[x] + alphabet[y]
letters.append(character)
numbers.append(i)
res = {numbers[i]+1: letters[i] for i in range(len(letters))}
return res
col_letter = gen_col_letter()
# Acceder a la base de datos
username = '******'
password = '******'
ip = '172.16.24.35'
port = '3306'
db_name = 'ubiimarket_db'
db = sqlalchemy.create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(username, password, ip, port, db_name),
pool_recycle=3600)
# Hacer query en la bd
query = text('SELECT * '
'FROM ubiimarket_db.dt_empresa_ramo as a, ubiimarket_db.tm_ramo as b '
'WHERE a.id_ramo=b.id_ramo and a.id_empresa={};'.format(id_empresa))
ramos = db.engine.execute(query).fetchall()
# Abrir el archivo excel
# Start by opening the spreadsheet and selecting the main sheet
excel_file = load_workbook(filename=file_path)
sheet = excel_file['LISTS']
# Agregar Ramos
row_ramo = 1
for ramo in ramos:
row_ramo += 1
cell = 'A' + str(row_ramo)
sheet[cell] = ramo['strnombre_ramo']
n_ramos = row_ramo - 1
# Agregar titulos de categorias
col = 1
for ramo in ramos:
col += 1
# Crear titulo
cell = col_letter[col] + '1'
table_end_cell = col_letter[col] + '2'
sheet[cell] = ramo['strnombre_ramo']
# Crear titulo de la tabla
table_name = 'T_' + ramo['strnombre_ramo']
table_name = table_name.replace(' ', '_')
table = Table(displayName=table_name, ref=cell + ':' + table_end_cell)
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = ramo['strnombre_ramo'].replace(' ', '_')
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, ramo['strnombre_ramo']))
excel_file.defined_names.append(new_range)
# Agregar categoria de cada ramo
col = 1
for ramo in ramos:
col += 1
row_categoria = 1
query_categoria = text('SELECT cat.id_ramo, ramo.strnombre_ramo, cat.strnombre_categoria '
'FROM ubiimarket_db.tm_categoria as cat, ubiimarket_db.tm_ramo as ramo '
'WHERE ramo.id_ramo=cat.id_ramo AND strnombre_ramo=\'{}\''.format(ramo['strnombre_ramo']))
categorias = db.engine.execute(query_categoria).fetchall()
for categoria in categorias:
row_categoria += 1
cell = col_letter[col] + str(row_categoria)
sheet[cell] = categoria['strnombre_categoria']
# Agregar marcas
query_marcas = text('SELECT * '
'FROM ubiimarket_db.tm_marca as a, ubiimarket_db.tm_ramo as b '
'WHERE a.id_ramo=b.id_ramo and id_empresa={}'.format(id_empresa))
marcas = db.engine.execute(query_marcas).fetchall()
row = 1
col += 1
added_marcas = []
cell = col_letter[col] + str(row)
if row == 1:
sheet[cell] = 'MARCAS'
# Crear titulo de la tabla marcas
table_name = 'T_MARCAS'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'MARCAS'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, range_name))
excel_file.defined_names.append(new_range)
for marca in marcas:
row += 1
cell = col_letter[col] + str(row)
nombre_marca = marca['strnombre_marca']
if row > 1 and nombre_marca not in added_marcas:
sheet[cell] = nombre_marca
added_marcas.append(nombre_marca)
elif row > 1 and nombre_marca in added_marcas:
row = row - 1
# Crear modelos de cada marca
added_marcas = []
for marca in marcas:
row = 1
col += 1
id_marca = marca['id_marca']
nombre_marca = marca['strnombre_marca']
query_modelos = text('SELECT * FROM ubiimarket_db.tm_modelo WHERE id_marca={}'.format(id_marca))
modelos = db.engine.execute(query_modelos).fetchall()
if row == 1 and nombre_marca not in added_marcas:
cell = col_letter[col] + str(row)
sheet[cell] = nombre_marca
# Crear titulo de la tabla marcas
table_name = 'T_' + nombre_marca
table_name = table_name.replace(' ', '_')
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
added_marcas.append(nombre_marca)
add_modelo = True
# Crear nombre de rango de valores
range_name = nombre_marca.replace(' ', '_')
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, nombre_marca))
excel_file.defined_names.append(new_range)
else:
add_modelo = False
col = col - 1
if add_modelo:
for modelo in modelos:
row += 1
cell = col_letter[col] + str(row)
nombre_modelo = modelo['strnombre_modelo']
if row > 1:
sheet[cell] = nombre_modelo
# Agregar IVAs
query_ivas = text('SELECT * FROM ubiimarket_db.tm_iva;')
ivas = db.engine.execute(query_ivas).fetchall()
col += 1
row = 1
cell = col_letter[col] + str(row)
# Agregar titulo de celda
sheet[cell] = 'IVAS'
# Agregar tabla
table_name = 'T_' + 'IVAS'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'IVAS'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, 'IVAS'))
excel_file.defined_names.append(new_range)
for iva in ivas:
row += 1
cell = col_letter[col] + str(row)
numero_iva = iva['porcentaje_iva']
sheet[cell] = numero_iva
# Agregar unidad de despacho
query_und_despacho = text('SELECT * FROM ubiimarket_db.tm_unidad_despacho;')
unidades_despacho = db.engine.execute(query_und_despacho).fetchall()
col += 1
row = 1
cell = col_letter[col] + str(row)
# Agregar titulo de celda
sheet[cell] = 'UNIDAD DESPACHO'
table_name = 'T_' + 'UNIDAD_DESPACHO'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'UNIDAD_DESPACHO'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, 'UNIDAD DESPACHO'))
excel_file.defined_names.append(new_range)
for unidad in unidades_despacho:
row += 1
cell = col_letter[col] + str(row)
nombre_unidad = unidad['strnombre_despacho'].upper()
sheet[cell] = nombre_unidad
# Agregar unidad de presentacion
query_und_presentacion = text('SELECT * FROM ubiimarket_db.tm_unidad_presentacion;')
unidades_presentacion = db.engine.execute(query_und_presentacion).fetchall()
col += 1
row = 1
cell = col_letter[col] + str(row)
# Agregar titulo de celda
sheet[cell] = 'UNIDAD PRESENTACION'
table_name = 'T_' + 'UNIDAD_PRESENTACION'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'UNIDAD_PRESENTACION'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, 'UNIDAD PRESENTACION'))
excel_file.defined_names.append(new_range)
for unidad in unidades_presentacion:
row += 1
cell = col_letter[col] + str(row)
nombre_unidad = unidad['strnombre_presentacion'].upper()
sheet[cell] = nombre_unidad
# Agregar SI/NO
col += 1
row = 1
cell = col_letter[col] + str(row)
# Agregar titulo de celda
sheet[cell] = 'SI NO'
table_name = 'T_' + 'SI_NO'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'SI_NO'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, 'SI NO'))
excel_file.defined_names.append(new_range)
# Agregar filas
for i in range(0,2):
row += 1
cell = col_letter[col] + str(row)
if i == 0:
sheet[cell] = 'SI'
elif i == 1:
sheet[cell] = 'NO'
# Agregar codigos de productos
query_cod_productos = text('SELECT * FROM ubiimarket_db.dt_productos WHERE id_empresa={};'.format(id_empresa))
codigos_prodcutos = db.engine.execute(query_cod_productos).fetchall()
col += 1
row = 1
# Agregar titulo de celda
cell = col_letter[col] + str(row)
sheet[cell] = 'CODIGOS PRODUCTOS'
table_name = 'T_' + 'CODIGOS_PRODUCTOS'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'CODIGOS_PRODUCTOS'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, 'CODIGOS PRODUCTOS'))
excel_file.defined_names.append(new_range)
for codigo in codigos_prodcutos:
row += 1
cell = col_letter[col] + str(row)
n_codigo = codigo['CodProd']
sheet[cell] = n_codigo
# Agregar RIF
query_rif = text('SELECT * FROM ubiimarket_db.dt_empresa WHERE id_empresa={};'.format(id_empresa))
rifs = db.engine.execute(query_rif).fetchall()
col += 1
row = 1
# Agregar titulo de celda
cell = col_letter[col] + str(row)
sheet[cell] = 'RIF'
table_name = 'T_' + 'RIF'
table = Table(displayName=table_name, ref=col_letter[col] + '1:' + col_letter[col] + '2')
style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=False)
table.tableStyleInfo = style
sheet.add_table(table)
# Crear nombre de rango de valores
range_name = 'RIF'
new_range = DefinedName(range_name, attr_text='{}[{}]'.format(table_name, 'RIF'))
excel_file.defined_names.append(new_range)
for rif in rifs:
row += 1
cell = col_letter[col] + str(row)
n_rif = rif['strrif_empresa']
sheet[cell] = n_rif
# Formatear todas las tablas
col = 0
for table in sheet.tables.values():
col += 1
n_rows = 0
for cell in sheet[col_letter[col]]:
if cell.value is not None:
n_rows += 1
if n_rows < 2:
n_rows = 2
table.ref = "{}1:{}{}".format(col_letter[col], col_letter[col], n_rows)
# Guardar el archivo excel (ULTIMO PASO)
excel_file.save(filename=save_file_path)
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties(
) # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# book views
active = get_active_sheet(wb)
view = BookView(activeTab=active)
root.bookViews = [view]
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title,
sheetId=idx,
id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError(
"The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(
wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase',
localSheetId=idx,
hidden=True)
name.value = u"{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter))
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")
])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([
u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area
])
defined_names.append(name)
root.definedNames = defined_names
root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True)
return tostring(root.to_tree())
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties()
if wb.code_name is not None:
props.codeName = wb.code_name
root.workbookPr = props
# book views
active = get_active_sheet(wb)
view = BookView(activeTab=active)
root.bookViews =[view]
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
rel = Relationship(
type=sheet._rel_type,
Target='{0}s/{1}'.format(sheet._rel_type, sheet._path)
)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
if wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels)
for idx, link in enumerate(wb._external_links, 1):
ext = ExternalReference(id="rId{0}".format(rId + idx))
rel = Relationship(type=link._rel_type,
Target='{0}s/{1}'.format(link._rel_type, link._path)
)
root.externalReferences.append(ext)
wb.rels.append(rel)
# Defined names
defined_names = copy(wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = "{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter)
)
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = quote_sheetname(sheet.print_titles)
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join(["{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
root.definedNames = defined_names
root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True)
return tostring(root.to_tree())