parsing an Excel formula with the re module
in [Python]
|
From: vsoler on 5 Jan 2010 13:12 Hello, I am acessing an Excel file by means of Win 32 COM technology. For a given cell, I am able to read its formula. I want to make a map of how cells reference one another, how different sheets reference one another, how workbooks reference one another, etc. Hence, I need to parse Excel formulas. Can I do it by means only of re (regular expressions)? I know that for simple formulas such as "=3*A7+5" it is indeed possible. What about complex for formulas that include functions, sheet names and possibly other *.xls files? For example "=Book1!A5+8" should be parsed into ["=","Book1", "!", "A5","+","8"] Can anybody help? Any suggestions? Vicente Soler
From: MRAB on 5 Jan 2010 13:35 vsoler wrote: > Hello, > > I am acessing an Excel file by means of Win 32 COM technology. > For a given cell, I am able to read its formula. I want to make a map > of how cells reference one another, how different sheets reference one > another, how workbooks reference one another, etc. > > Hence, I need to parse Excel formulas. Can I do it by means only of re > (regular expressions)? > > I know that for simple formulas such as "=3*A7+5" it is indeed > possible. What about complex for formulas that include functions, > sheet names and possibly other *.xls files? > > For example "=Book1!A5+8" should be parsed into ["=","Book1", "!", > "A5","+","8"] > > Can anybody help? Any suggestions? > Do you mean "how" or do you really mean "whether", ie, get a list of the other cells that are referred to by a certain cell, for example, "=3*A7+5" should give ["A7"] and "=Book1!A5+8" should give ["Book1!A5"]?
From: vsoler on 5 Jan 2010 14:00 On 5 ene, 19:35, MRAB <pyt...(a)mrabarnett.plus.com> wrote: > vsoler wrote: > > Hello, > > > I am acessing an Excel file by means of Win 32 COM technology. > > For a given cell, I am able to read its formula. I want to make a map > > of how cells reference one another, how different sheets reference one > > another, how workbooks reference one another, etc. > > > Hence, I need to parse Excel formulas. Can I do it by means only of re > > (regular expressions)? > > > I know that for simple formulas such as "=3*A7+5" it is indeed > > possible. What about complex for formulas that include functions, > > sheet names and possibly other *.xls files? > > > For example "=Book1!A5+8" should be parsed into ["=","Book1", "!", > > "A5","+","8"] > > > Can anybody help? Any suggestions? > > Do you mean "how" or do you really mean "whether", ie, get a list of the > other cells that are referred to by a certain cell, for example, > "=3*A7+5" should give ["A7"] and "=Book1!A5+8" should give ["Book1!A5"]? I'd like to know how to do it, should it be possible. Vicente
From: John Posner on 5 Jan 2010 14:04 On Tue, 05 Jan 2010 13:12:00 -0500, vsoler <vicente.soler(a)gmail.com> wrote: > Hello, > > I am acessing an Excel file by means of Win 32 COM technology. > For a given cell, I am able to read its formula. I want to make a map > of how cells reference one another, how different sheets reference one > another, how workbooks reference one another, etc. > > Hence, I need to parse Excel formulas. Can I do it by means only of re > (regular expressions)? > > I know that for simple formulas such as "=3*A7+5" it is indeed > possible. What about complex for formulas that include functions, > sheet names and possibly other *.xls files? > > For example "=Book1!A5+8" should be parsed into ["=","Book1", "!", > "A5","+","8"] > > Can anybody help? Any suggestions? It seems like you want to recreate data structures that Excel, itself, must maintain in order to recalculate cells in the correct order. As long as you're using COM, you might be able to tap into those data structures. My 15-year-old (!) "Using Excel Visual Basic for Applications" book wasn't any help. :-( After a short Google session, I came up with one possible lead: http://www.decisionmodels.com/ Good luck! John
From: Mensanator on 5 Jan 2010 14:05
On Jan 5, 12:35 pm, MRAB <pyt...(a)mrabarnett.plus.com> wrote: > vsoler wrote: > > Hello, > > > I am acessing an Excel file by means of Win 32 COM technology. > > For a given cell, I am able to read its formula. I want to make a map > > of how cells reference one another, how different sheets reference one > > another, how workbooks reference one another, etc. > > > Hence, I need to parse Excel formulas. Can I do it by means only of re > > (regular expressions)? > > > I know that for simple formulas such as "=3*A7+5" it is indeed > > possible. What about complex for formulas that include functions, > > sheet names and possibly other *.xls files? > > > For example "=Book1!A5+8" should be parsed into ["=","Book1", "!", > > "A5","+","8"] > > > Can anybody help? Any suggestions? > > Do you mean "how" or do you really mean "whether", ie, get a list of the > other cells that are referred to by a certain cell, for example, > "=3*A7+5" should give ["A7"] and "=Book1!A5+8" should give ["Book1!A5] Ok, although "Book1" would be the default name of a workbook, with default worksheets labeled "Sheet1". "Sheet2", etc. If I had a worksheet named "Sheety" that wanted to reference a cell on "Sheetx" OF THE SAME WORKBOOK, it would be =Sheet2!A7. If the reference was to a completely different workbook (say Book1 with worksheets labeled "Sheet1", "Sheet2") then the cell might have =[Book1]Sheet1!A7. And don't forget the $'s! You may see =[Book1]Sheet1!$A$7. |