Re: Excel formulas --> Mathematica
- To: mathgroup at smc.vnet.net
- Subject: [mg49252] Re: Excel formulas --> Mathematica
- From: hansjm at bellsouth.net
- Date: Fri, 9 Jul 2004 02:26:24 -0400 (EDT)
- Sender: owner-wri-mathgroup at wolfram.com
Frank:
I gave this a little effort. If you are running on a windows platform with wscript.exe or cscript.exe available then you can try the following:
Create an excel workbook, preferably with one sheet.
Add some Functions in random Cells.
Save the workbook Default is "Book1.xls".
For simplicity save to the root C:\.
Then in Mathematica paste the following:
excelworksheetfunctions = {"ABS","ACCRINT","ACCRINTM","ACOS","ACOSH","AMORDEGRC","AMORLINC","AND","AREAS","ASC","ASIN","ASINH","ATAN","ATAN2","ATANH","AVEDEV","AVERAGE","AVERAGEA","BAHTTEXT","BESSELI","BESSELJ","BESSELK","BESSELY","BETADIST","BETAINV","BIN2DEC","BIN2HEX","BIN2OCT","BINOMDIST","CEILING","CELL","CHAR","CHIDIST","CHIINV","CHITEST","CHOOSE","CLEAN","CODE","COLUMN","COLUMNS","COMBIN","COMPLEX","CONCATENATE","CONFIDENCE","CONVERT","CORREL","COS","COSH","COUNT","COUNTA","COUNTBLANK","COUNTIF","COUPDAYBS","COUPDAYS","COUPDAYSNC","COUPNCD","COUPNUM","COUPPCD","COVAR","CRITBINOM","CUMIPMT","CUMPRINC","DATE","DATEVALUE","DAVERAGE","DAY","DAYS360","DB","DCOUNT","DCOUNTA","DDB","DEC2BIN","DEC2HEX","DEC2OCT","DEGREES","DELTA","DEVSQ","DGET","DISC","DMAX","DMIN","DOLLAR","DOLLARDE","DOLLARFR","DPRODUCT","DSTDEV","DSTDEVP","DSUM","DURATION","DVAR","DVARP","EDATE","EFFECT","EOMONTH","ERF","ERFC","ERROR.TYPE","EUROCONVERT","EVEN","EXACT","EXP","EXPONDIST","FACT","FACTDOUBLE",!
"F!
ALSE","FDIST","FIND","FINV","FISHER","FISHERINV","FIXED","FLOOR","FORECAST","FREQUENCY","FTEST","FV","FVSCHEDULE","GAMMADIST","GAMMAINV","GAMMALN","GCD","GEOMEAN","GESTEP","GETPIVOTDATA","GROWTH","HARMEAN","HEX2BIN","HEX2DEC","HEX2OCT","HLOOKUP","HOUR","HYPERLINK","HYPGEOMDIST","IF","IMABS","IMAGINARY","IMARGUMENT","IMCONJUGATE","IMCOS","IMDIV","IMEXP","IMLN","IMLOG10","IMLOG2","IMPOWER","IMPRODUCT","IMREAL","IMSIN","IMSQRT","IMSUB","IMSUM","INDEX","INDIRECT","INFO","INT","INTERCEPT","INTRATE","IPMT","IRR","ISBLANK","ISERR","ISERROR","ISEVEN","ISLOGICAL","ISNA","ISNONTEXT","ISNUMBER","ISODD","ISPMT","ISREF","ISTEXT","JIS","KURT","LARGE","LCM","LEFT","LEN","LINEST","LN","LOG","LOG10","LOGEST","LOGINV","LOGNORMDIST","LOOKUP","LOWER","MATCH","MAX","MAXA","MDETERM","MDURATION","MEDIAN","MID","MIN","MINA","MINUTE","MINVERSE","MIRR","MMULT","MOD","MODE","MONTH","MROUND","MULTINOMIAL","N","NA","NEGBINOMDIST","NETWORKDAYS","NOMINAL","NORMDIST","NORMINV","NORMSDIST","NORMSINV","NOT",!
"N!
OW","NPER","NPV","OCT2BIN","OCT2DEC","OCT2HEX","ODD","ODDFPRICE","ODDFYIELD","ODDLPRICE","ODDLYIELD","OFFSET","OR","PEARSON","PERCENTILE","PERCENTRANK","PERMUT","PHONETIC","PI","PMT","POISSON","POWER","PPMT","PRICE","PRICEDISC","PRICEMAT","PROB","PRODUCT","PROPER","PV","QUARTILE","QUOTIENT","RADIANS","RAND","RANDBETWEEN","RANK","RATE","RECEIVED","REPLACE","REPT","RIGHT","ROMAN","ROUND","ROUNDDOWN","ROUNDUP","ROW","ROWS","RSQ","RTD","SEARCH","SECOND","SERIESSUM","SIGN","SIN","SINH","SKEW","SLN","SLOPE","SMALL","SQL.REQUEST","SQRT","SQRTPI","STANDARDIZE","STDEV","STDEVA","STDEVP","STDEVPA","STEYX","SUBSTITUTE","SUBTOTAL","SUM","SUMIF","SUMPRODUCT","SUMSQ","SUMX2MY2","SUMX2PY2","SUMXMY2","SYD","T","TAN","TANH","TBILLEQ","TBILLPRICE","TBILLYIELD","TDIST","TEXT","TIME","TIMEVALUE","TINV","TODAY","TRANSPOSE","TREND","TRIM","TRIMMEAN","TRUE","TRUNC","TTEST","TYPE","UPPER","VALUE","VAR","VARA","VARP","VARPA","VDB","VLOOKUP","WEEKDAY","WEEKNUM","WEIBULL","WORKDAY","XIRR","XNPV","YEAR!
",!
"YEARFRAC","YIELD","YIELDDISC","YIELDMAT","ZTEST"};
getexcelfunction[xls_] :=
Module[
{excelxmldata, excelfunctions},
Export["c:\\cexceltoxml.vbs", "Dim ExcelSheet\nSet ExcelSheet = WScript.GetObject(\"" <> xls <> "\")\nExcelSheet.Application.Visible = False\nExcelSheet.Sheets(1).SaveAs \"c:\\book1.xml\", 46 \nExcelSheet.Application.Quit\nSet ExcelSheet = Nothing", "Text"];
Run["wscript.exe //B //Nologo \"c:\\cexceltoxml.vbs\""];
excelxmldata = Import["C:\\book1.xml", ConversionOptions -> {"IncludeNamespaces" -> False}];
excelfunctions = Cases[excelxmldata, XMLElement["Cell", {___, "Formula" -> t___ /; StringMatchQ[t, "=*(*)"]}, _] :> StringReplace[t, {"=" -> "", "(*)" -> "", "R[*]" -> "", "C[*]" -> "", "+" -> "", "-" -> ""}, MetaCharacters -> Automatic], Infinity];
DeleteFile[{"C:\\book1.xml", "c:\\cexceltoxml.vbs"}];
excelfunctions
]
z = getexcelfunction["C:\\book1.xls"];
Intersection[excelworksheetfunctions, z]
{COS, DATE, NOW, SIN}
Plase note that this is a little dangerous you will be writing VBScript which if not well written can cause damage. In this case I think this is safe because I do no deletes in the script file just call excel, open book1.xls, saveas xml.
This solution fails for nested functions. You may just get the first or outer most function.
How to do this on other platforms is not so clear, except for writing a BIFF8 reader/extractor in Mathematica. The documentation for BIFF8 is hard to come by.
Hans