Services & Resources / Wolfram Forums
-----
 /
MathGroup Archive
2004
*January
*February
*March
*April
*May
*June
*July
*August
*September
*October
*November
*December
*Archive Index
*Ask about this page
*Print this page
*Give us feedback
*Sign up for the Wolfram Insider

MathGroup Archive 2004

[Date Index] [Thread Index] [Author Index]

Search the Archive

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



  • Prev by Date: what kind of a programming language is Mathematica?
  • Next by Date: a new sort of Gaussian noise
  • Previous by thread: Re: Excel formulas --> Mathematica
  • Next by thread: Re: Bug in parsing expression?