# leegao/pyXLSX

Switch branches/tags
Nothing to show
Fetching contributors…
Cannot retrieve contributors at this time
701 lines (598 sloc) 12.9 KB
 from __future__ import division from xlsx import workbook, _flatten import math, cmath, fractions, random extend = workbook.extend #Decorator for adding new formulas flatten = workbook.flatten #Formula doesn't care if a list is one or multi dimensional singular = workbook.singular #Only takes in a single value plural = workbook.plural #Takes multiple arguments, does not check for first param void = workbook.void #Takes no arguments def integrate(fn, lower, upper, threshold): dx = float(upper-lower)/threshold ddx = dx + lower fn_dx = 0 while ddx < upper: fn_dx += fn(ddx)*dx ddx += dx return fn_dx def im(z): return complex(str(z).replace("i", "j")) def _round(x, sig, key=round): sign = 1 if x>0 else -1 sig = -math.log10(abs(sig)) x = abs(x) return sign*float(key(x*(10**sig)))/(10**sig) def _roman(input): if type(input) != type(1): raise TypeError, "expected integer, got %s" % type(input) if not 0 < input < 4000: raise ValueError, "Argument must be between 1 and 3999" ints = (1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1) nums = ('M', 'CM', 'D', 'CD','C', 'XC','L','XL','X','IX','V','IV','I') result = "" for i in range(len(ints)): count = int(input / ints[i]) result += nums[i] * count input -= ints[i] * count return result @extend @flatten def SUM(list): "Sums up the list of numbers" sigma = 0 for n in list: sigma += n return sigma @extend @flatten def AVERAGE(list): "Find the average of the list of numbers" sigma = 0.0 i = 0 for n in list: sigma += n i+=1 return sigma/i @extend @singular def CODE(char): "Return the first char code" return ord(str(char)[0]) @extend @plural def BESSELI(base, nu): """ Returns the Modified BESSEL of First Kind function of parameters base and nu respectively. BESSELI ~ (z,v): 1/pi integral_0^pie^(zcos(t)) cos(vt) dt """ dfn_dx = lambda x: (math.e**(base*math.cos(x)))*math.cos(nu*x) return (1./math.pi)*integrate(dfn_dx, 0, math.pi, 5000) @extend @plural def BESSELJ(base, nu): """ Returns the BESSEL Function of First Kind of parameters base and nu respectively. BESSELJ ~ (z,v): 1/pi integral_0^pie^cos(zsin(t) - vt) dt """ dfn_dx = lambda x: math.cos(base*math.sin(x)-nu*x) return (1./math.pi)*integrate(dfn_dx, 0, math.pi, 5000) @extend @plural def BESSELK(base, nu): """ Returns the BESSEL Function of Second Kind of parameters base and nu respectively. BESSELJ ~ (z,v): pi/2 (I-z(v)-Iz(v))/sin(z*pi) BROKEN IMPLEMENTATION """ I = lambda n,x: (1./math.pi)*integrate(lambda x: (math.e**(base*math.cos(x)))*math.cos(nu*x), 0, math.pi, 5000) #print 2*I(-1.5, 1)/(math.sin(base*math.pi)) K = (math.pi/2)*(I(-base, nu)-I(base, nu))/(math.sin(base*math.pi)) return K @extend @singular def BIN2DEC(binary): bin = list(str(binary)) dec = 0 for i in range(len(bin)): dec += int(bin[::-1][i])*2**i return dec @extend @singular def DEC2BIN(dec): return bin(dec).replace("0b","") @extend @singular def BIN2HEX(binary): bin = list(str(binary)) dec = 0 for i in range(len(bin)): dec += int(bin[::-1][i])*2**i return "%X" % dec @extend @singular def DEC2HEX(dec): return "%X"%dec @extend @singular def BIN2OCT(binary): bin = list(str(binary)) dec = 0 for i in range(len(bin)): dec += int(bin[::-1][i])*2**i return "%o" % dec @extend @singular def DEC2OCT(dec): return "%o"%dec @extend @singular def HEX2DEC(dec): return int(dec, 16) @extend @singular def HEX2OCT(dec): return "%o"%int(dec, 16) @extend @singular def HEX2BIN(dec): return bin(dec).replace("0b","") @extend @singular def OCT2DEC(dec): return int(dec, 8) @extend @singular def OCT2HEX(dec): return "%X"%int(dec, 8) @extend @singular def OCT2BIN(dec): return "%b"%int(dec, 8) @extend @plural def COMPLEX(real, imaginary): return complex(real, imaginary) @extend @plural def DELTA(a, b): return 1 if a == b else 0 @extend @plural def ERF(a, b = 0): """http://office.microsoft.com/en-us/excel/HP052090771033.aspx""" def _erf(z): return (2./math.sqrt(math.pi))*integrate(lambda x: math.e**(-x**2), 0, z, 5000) if not b: return _erf(a) else: return _erf(b) - _erf(a) @extend @singular def ERFC(z): def _erfc(z): return (2./math.sqrt(math.pi))*integrate(lambda x: math.e**(-x**2), 0, z, 5000) return 1 - _erfc(z) @extend @plural def GESTEP(a,b): return 1 if a >= b else 0 @extend @singular def IMABS(z): c = im(z) return abs(c) @extend @singular def IMAGINARY(z): c = im(z) return c.imag @extend @singular def IMARGUMENT(z): c = im(z) return cmath.phase(c) @extend @singular def IMCONJUGATE(z): c = im(z) return c.conjugate() @extend @singular def IMCOS(z): c = im(z) return cmath.cos(c) @extend @plural def IMDIV(a,b): a = im(a) b = im(b) return a/b @extend @singular def IMEXP(z): c = im(z) return cmath.exp(c) @extend @singular def IMLN(z): c = im(z) return cmath.log(c) @extend @singular def IMLOG10(z): c = im(z) return cmath.log10(c) @extend @singular def IMLOG2(z): c = im(z) return cmath.log(c, 2) @extend @plural def IMPOWER(a,b): a = im(a) return a**b @extend @plural def IMPRODUCT(a,b): a = im(a) b = im(b) return a*b @extend @singular def IMREAL(z): c = im(z) return c.real @extend @singular def IMSIN(z): c = im(z) return cmath.sin(c) @extend @singular def IMSQRT(z): c = im(z) return cmath.sqrt(c) @extend @plural def IMSUB(a,b): a = im(a) b = im(b) return a-b @extend @plural def IMSUM(a,b): a = im(a) b = im(b) return a+b ###################################### @extend @singular def ABS(x): return abs(x) @extend @singular def ACOS(x): return math.acos(x) @extend @singular def ACOSH(x): return math.acosh(x) @extend @singular def ASIN(x): return math.asin(x) @extend @singular def ASINH(x): return math.asinh(x) @extend @singular def ATAN(x): return math.atan(x) @extend @singular def ATANH(x): return math.atanh(x) @extend @plural def ATAN2(x, y): return math.atan2(x, y) @extend @plural def CEILING(x, sig): sign = 1 if x>0 else -1 sig = -math.log10(abs(sig)) x = abs(x) return float(math.ceil(x*(10**sig)))/(10**sig) @extend @plural def COMBIN(n, k): def _prod(fn, lower, upper, n = 1): for i in range(lower, upper+1):n *= fn(i) return n x = lambda x: x if x else 1 return _prod(x, k+1, n)/_prod(x, 1, n-k) @extend @singular def COS(x): return math.cos(x) @extend @singular def COSH(x): return math.cosh(x) @extend @singular def DEGREES(theta): return theta/math.pi*180 @extend @singular def EVEN(x): sign = 1 if x>0 else -1 if not _round(x, sign)%2: return int(_round(x, sign)) x = abs(x) return sign*int(math.ceil(float(x)/2)*2) @extend @singular def EXP(x): return math.exp(x) @extend @singular def FACT(n): def _prod(fn, lower, upper, n = 1): for i in range(int(lower), int(upper+1)):n *= fn(i) return n x = lambda x: x if x else 1 return _prod(x, 1, n) @extend @singular def FACTDOUBLE(n): x = 1 while n > 0: x *= n n -= 2 return x @extend @plural def FLOOR(x, sig): sign = 1 if x>0 else -1 sig = -math.log10(abs(sig)) x = abs(x) return sign*float(math.floor(x*(10**sig)))/(10**sig) @extend @plural def GCD(x, y): return fractions.gcd(x, y) @extend @singular def INT(x): return int(math.floor(x)) @extend @plural def LCM(x, y): return (x*y)/fractions.gcd(x, y) @extend @singular def LN(x): return math.log(x) @extend @plural def LOG(x, y): return math.log(x, y) @extend @singular def LOG10(x): return math.log10(x) @extend def MDETERM(matrix): if len(matrix) is not len(matrix[0]): return matrix = [[float(e) for e in m] for m in matrix] def _det(matrix): def _minor(matrix, i, j): _matrix = [[e for e in m] for m in matrix] _matrix.pop(i) for _m in _matrix: _m.pop(j) return _matrix n = 0 if len(matrix) == 2: return matrix[0][0]*matrix[1][1] - matrix[0][1]*matrix[1][0] #ad-bc for j in range(len(matrix)): n += matrix[0][j]*(-1)**j*_det(_minor(matrix,0,j)) return n return _det(matrix) @extend def MINVERSE(matrix): if len(matrix) is not len(matrix[0]): return matrix = [[float(e) for e in m] for m in matrix] def _minor(matrix, i, j): _matrix = [[e for e in m] for m in matrix] _matrix.pop(i) for _m in _matrix: _m.pop(j) return _matrix def _det(matrix): n = 0 if len(matrix) == 2: return matrix[0][0]*matrix[1][1] - matrix[0][1]*matrix[1][0] #ad-bc for j in range(len(matrix)): n += matrix[0][j]*(-1)**j*_det(_minor(matrix,0,j)) return n A_inv = 1./_det(matrix) inverse = [[0 for e in m] for m in matrix] if len(matrix) == 2: matrix[0][0],matrix[1][1] = -matrix[1][1],-matrix[0][0] return [[-A_inv*float(e) for e in m] for m in matrix] for i in range(len(matrix)): for j in range(len(matrix)): inverse[j][i] = A_inv*(-1)**(i+j)*_det(_minor(matrix, i, j)) return inverse @extend @plural def MMULT(matrix1, matrix2): if len(matrix1[0]) is not len(matrix2[0]): return _m = [] for row1 in matrix1: _r = [] for col2 in zip(*matrix2): _r += [sum([row1[i]*col2[i] for i in range(len(row1))])] _m += [_r] return _m @extend @plural def MOD(a,b): return a%b @extend @plural def MROUND(a,b): n = min(a%b, a%b-b, key = lambda a: abs(a)) if int(2*a%b - b) else a%b-b return a-n @extend @plural def MULTINOMIAL(*all): def _fact(n): return n*_fact(n-1) if n else 1 n = 1 for i in all: n *= _fact(i) return float(_fact(sum(all)))/n @extend @singular def ODD(x): sign = 1 if x>0 else -1 if _round(x, sign)%2: return int(_round(x, sign)) x = abs(x) return sign*int(math.ceil(float(x)/2)*2 + 1) @extend @void def PI(): return math.pi @extend @plural def POWER(a,b): return a**b @extend @flatten def PRODUCT(list): n = 1 for i in list: n*=i return n @extend @plural def QUOTIENT(a,b): return int(_round(a/b, 1, key=math.floor)) @extend @singular def RADIANS(theta): return math.pi*theta/180 @extend @void def RAND(): return random.random() @extend @plural def RANDBETWEEN(a,b): return random.randint(a,b) @extend @plural def ROMAN(a,b): del b return _roman(a) @extend @plural def ROUND(n,sig): return _round(n, 10**-sig) @extend @plural def ROUNDDOWN(n,sig): return _round(n, 10**-sig, key=math.floor) @extend @plural def ROUNDUP(n,sig): return _round(n, 10**-sig, key=math.ceil) @extend @plural def SERIESSUM(x, n, m, a): a = _flatten(a) r = 0 for i in range(len(a)): r += a[i]*(x**(n+m*i)) return r @extend @singular def SIGN(x): return 0 if not x else 1 if x>0 else -1 @extend @singular def SIN(x): return math.sin(x) @extend @singular def SINH(x): return math.sinh(x) @extend @singular def SQRT(x): return math.sqrt(x) @extend @singular def SQRTPI(x): return math.sqrt(math.pi*x) @extend @flatten def SUBTOTAL(all): fn = all.pop(0) fn_dict = { 1:workbook.AVERAGE, 2:workbook.COUNT, 3:workbook.COUNTA, 4:workbook.MAX, 5:workbook.MIN, 6:workbook.PRODUCT, 7:workbook.STDEV, 8:workbook.STDEVP, 9:workbook.SUM, 10:workbook.VAR, 11:workbook.VARP } return fn_dict[int(fn)](all) @extend @flatten def SUM(list): n = 0 for i in list: n+=float(i) return n @extend @plural def SUMIF(a, cond, b = []): a,b = _flatten(a),_flatten(b) if not b: b = a n= 0 o_cond = cond if "<" not in str(cond) and ">" not in str(cond): cond = " == "+str(cond) for i in range(len(a)): try: if eval(str(a[i]) + cond): n+= float(b[i]) except: if eval(str(a[i]) + "== '"+o_cond+"'"): n+= float(b[i]) return n @extend def SUMPRODUCT(*list): n = 0 for i in range(len(list)): _n = 1 for l in list: _n*=_flatten(l)[i] n+=_n return n if __name__ == "__main__": # I = lambda b,n: (1./cmath.pi)*integrate(lambda x: (cmath.e**(b*cmath.cos(x)))*cmath.cos(n*x), 0, cmath.pi, 5000) # # J = lambda b,n: (1./cmath.pi)*integrate(lambda x: cmath.cos(b*cmath.sin(x)-n*x), 0, cmath.pi, 5000) # # i = complex(0,1.) # z = 2.5 # v = 1 # print math.sin(v*math.pi) import demo pass