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