open - Obtenga la fórmula de la celda de Excel con python xlrd

xlrd python example

Question

Debo portar un algoritmo de una hoja de Excel a un código de Python, pero tengo que realizar una ingeniería inversa del algoritmo desde el archivo de Excel .

La hoja de Excel es bastante complicada, contiene muchas celdas en las que hay fórmulas que hacen referencia a otras celdas (que también pueden contener una fórmula o una constante).

Mi idea es analizar con una secuencia de comandos python la hoja construyendo una especie de tabla de dependencias entre las celdas, es decir:

A1 depende de la fórmula B4, C5, E7: "= sqrt (B4) + C5 * E7"
A2 depende de la fórmula B5, C6: "= sin (B5) * C6"
...

El módulo xlrd python permite leer un libro de XLS pero en este momento puedo acceder al valor de una celda, no a la fórmula .

Por ejemplo, con el siguiente código puedo obtener simplemente el valor de una celda:

import xlrd

#open the .xls file
xlsname="test.xls"
book = xlrd.open_workbook(xlsname)

#build a dictionary of the names->sheets of the book
sd={}
for s in book.sheets():
    sd[s.name]=s

#obtain Sheet "Foglio 1" from sheet names dictionary
sheet=sd["Foglio 1"]

#print value of the cell J141
print sheet.cell(142,9)

De todos modos, parece que no hay forma de obtener la formul del objeto Cell devuelto por el método .cell (...) . En la documentation , dicen que es posible obtener una versión de cadena de la fórmula (en inglés porque no hay información sobre la traducción del nombre de la función almacenada en el archivo de Excel). Hablan de fórmulas (expresiones) en las clases de Nombre y Operando . De todos modos, no puedo entender cómo obtener las instancias de estas clases por la instancia de la clase Cell que debe contenerlas.

¿Podría sugerir un fragmento de código que obtenga el texto de fórmula de una celda?

Answer 1

Así que sé que esta es una publicación muy antigua, pero encontré una forma decente de obtener las fórmulas de todas las hojas de un libro de trabajo y de que el libro de trabajo recién creado conservara todo el formato.

El primer paso es guardar una copia de su archivo .xlsx como .xls: use los .xls como nombre de archivo en el código siguiente

Usando Python 2.7

from lxml import etree
from StringIO import StringIO
import xlsxwriter
import subprocess
from xlrd import open_workbook
from xlutils.copy import copy
from xlsxwriter.utility import xl_cell_to_rowcol
import os



file_name = ''<YOUR-FILE-HERE>''
dir_path = os.path.dirname(os.path.realpath(file_name))

subprocess.call(["unzip",str(file_name+"x"),"-d","file_xml"])


xml_sheet_names = dict()

with open_workbook(file_name,formatting_info=True) as rb:
    wb = copy(rb)
    workbook_names_list = rb.sheet_names()
    for i,name in enumerate(workbook_names_list):
        xml_sheet_names[name] = "sheet"+str(i+1)

sheet_formulas = dict()
for i, k in enumerate(workbook_names_list):
    xmlFile = os.path.join(dir_path,"file_xml/xl/worksheets/{}.xml".format(xml_sheet_names[k]))
    with open(xmlFile) as f:
        xml = f.read()

    tree = etree.parse(StringIO(xml))
    context = etree.iterparse(StringIO(xml))

    sheet_formulas[k] = dict()
    for _, elem in context:
        if elem.tag.split("}")[1]==''f'':
            cell_key = elem.getparent().get(key="r")
            cell_formula = elem.text
            sheet_formulas[k][cell_key] = str("="+cell_formula)

sheet_formulas

Estructura del diccionario ''sheet_formulas''

{''Worksheet_Name'': {''A1_cell_reference'':''cell_formula''}}

Resultados de ejemplo:

{u''CY16'': {''A1'': ''=Data!B5'',
  ''B1'': ''=Data!B1'',
  ''B10'': ''=IFERROR(Data!B12,"")'',
  ''B11'': ''=IFERROR(SUM(B9:B10),"")'',

Answer 2

Parece que ahora es imposible hacer lo que quieras con xlrd. Puede echar un vistazo a esta publicación para obtener una descripción detallada de por qué es tan difícil implementar la funcionalidad que necesita.

Tenga en cuenta que el equipo de desarrollo hace un gran trabajo de soporte en el grupo de python-excel google.

Answer 3

Sé que esta publicación es un poco tarde, pero hay una sugerencia que no se ha cubierto aquí. Corta todas las entradas de la hoja de trabajo y pégalas usando paste special (OpenOffice). Esto convertirá las fórmulas a números para que no haya necesidad de programación adicional y esta es una solución razonable para libros pequeños.

Answer 4

[Dis] claimer: soy el autor / mantenedor de xlrd .

Las referencias de documentación al texto de fórmula son sobre fórmulas de "nombre"; lea la sección "Referencias, constantes, fórmulas y macros con nombre" cerca del inicio de los documentos. Estas fórmulas están asociadas a todo el contenido de la hoja o del libro a un nombre; no están asociados con células individuales. Ejemplos: PI maps to =22/7 =Mktng!$A$2:$Z$99 , SALES maps to =Mktng!$A$2:$Z$99 . El descompilador de fórmula de nombre se escribió para permitir la inspección de los usos más simples y / o comunes de los nombres definidos.

Las fórmulas en general son de varios tipos: celda, compartida y matriz (todas asociadas con una celda, directa o indirectamente), nombre, validación de datos y formato condicional.

Descompilar las fórmulas generales de bytecode a texto es un "trabajo en progreso", lentamente. Tenga en cuenta que, suponiendo que estuviera disponible, debería analizar la fórmula de texto para extraer las referencias de celda. Analizar las fórmulas de Excel correctamente no es un trabajo fácil; como con HTML, usar expresiones regulares parece fácil pero no funciona. Sería mejor extraer las referencias directamente de la fórmula bytecode.

También tenga en cuenta que las fórmulas basadas en celdas pueden hacer referencia a nombres, y las fórmulas de nombre pueden referirse tanto a celdas como a otros nombres. Por lo tanto, sería necesario extraer las referencias de celda y nombre de las fórmulas basadas en celdas y nombres. Puede serle útil tener disponible información sobre fórmulas compartidas; de lo contrario, haber analizado lo siguiente:

B2 =A2
B3 =A3+B2
B4 =A4+B3
B5 =A5+B4
...
B60 =A60+B59

necesitaría deducir la similitud entre las fórmulas B3:B60 usted mismo.

En cualquier caso, ninguno de los anteriores es probable que esté disponible en el xlrd plazo; xlrd prioridades xlrd encuentran en otro lugar.

Answer 5

Actualización : he ido e implementado una pequeña biblioteca para hacer exactamente lo que describes: extraer las celdas y dependencias de una hoja de cálculo de Excel y convertirlas en código python. El código está en github , los parches son bienvenidos :)

Solo para agregar que siempre puedes interactuar con Excel usando win32com (no muy rápido pero funciona). Esto te permite obtener la fórmula. Un tutorial se puede encontrar aquí y se pueden encontrar detalles en este capítulo [copia en caché] .

Esencialmente solo haces:

app.ActiveWorkbook.ActiveSheet.Cells(r,c).Formula

En cuanto a la construcción de una tabla de dependencias celulares, algo complicado es analizar las expresiones de Excel. Si mal no recuerdo, el código Trace que mencionaste no siempre hace esto correctamente. Lo mejor que he visto es el algoritmo de EW Bachtal , del cual está disponible una implementación de Python que funciona bien.