Open Source, web based spreadsheet app with formulas
I am looking for an open source spreadsheet application to stick into my web page. The most important feature I am searching for is formulas. Specifically, I need to be able to put in a cell which calculates the total of the contents of other specified cells.
I've found some bas开发者_Python百科ic examples like Nitobi and ExtJs Extender, but they don't allow any calculations.
Is this a lost cause, or is the perfect spreadsheet app out there, waiting for me to find it?
I just found something that looks like exactly what I'm looking for: Simple Spreadsheet.
Not only does it do functions, but it does graphing as well, among many other things.
It is written in Javascript, HTML, CSS and PHP, and is open-source under the GNU GPLv2 License.
I just developed a spread sheet with javascript and html and you can view and copy the source code and paste and save as anyfile.htm and run in local machine also. http://mksugumaran.blogspot.in/2014/03/webxl-webbased-spreadsheet.html
http://mksugumaran.blogspot.in/2014/03/webxl-webbased-spreadsheet.html
The following features are covered in this spreadsheet.
1.You will see the following page in your web browser.
2.Selecting Cells in the Spread sheet.
3.Entering texts and values in the cells.
4.Doing Calculations in spreadsheet.
5.Copy and Paste functions in this spreadsheet.
6.Saving the spread sheet.
7.Loading the spread sheet from saved file.
8.Loading in Microsoft Excel spread sheet from saved file.
9.OPEN SOURCE DATA:
10.String functions:
11.Format functions:
Solution of simultaneous equations in spreadsheet:
Javascript programming within spreadsheet:
Shortcut keys:
The source code of the spreadsheet is given below:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
<HEAD>
<TITLE>WORKSHEET</TITLE>
<SCRIPT LANGUAGE="javascript">
var i1,j1,i2,j2,nshft;
var n,ng=0;
var copyx;
var nsel=0;
var gxy = new Array();
var gridxy=new Array();
var fxy="";
var x=new Array();
for (i=1;i<=10000;i++)
{
gridxy[i]="";
gxy[i]="";
}
function displayresult()
{
var x;
if(window.event) // IE8 and earlier
{
x=event.keyCode;
}
else if(event.which) // IE9/Firefox/Chrome/Opera/Safari
{
x=event.which;
}
if (x==13){label1.focus();go.click();}
}
function sum1(str)
{
val1="";
nx=0;
n1=str.indexOf("[",nx);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
ia1=parseInt(str.substr(n1+1,n3-n1-1));
ja1=parseInt(str.substr(n3+1,n2-n3-1));
nx=n2+1;
n1=str.indexOf("[",nx);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
ia2=parseInt(str.substr(n1+1,n3-n1-1));
ja2=parseInt(str.substr(n3+1,n2-n3-1));
na1=(ia1-1)*100+ja1;
na2=(ia2-1)*100+ja2;
if(na1<=na2)
{
for(iq=ia1;iq<=ia2;iq++)
{
for(jq=ja1;jq<=ja2;jq++)
{
nx=(iq-1)*100+jq;
val1=val1+'+'+cal(nx);
}
}
}
if(na1>na2)
{
for(iq=ia2;iq<=ia1;iq++)
{
for(jq=ja2;jq<=ja1;jq++)
{
nx=(iq-1)*100+jq;
val1=val1+'+'+cal(nx);
}
}
}
return(eval(val1));
}
function selcolor(i1,j1,i2,j2)
{
if(i2<i1){ix=i2;i2=i1;i1=ix;}
if(j2<j1){jx=j1;j2=j1;j1=jx;}
for(i=i1;i<=i2;i++)
{
var x =document.getElementById("tbl1").rows[i].cells;
for(j=j1;j<=j2;j++)
{
x[j].style.background="yellow";
}
}
}
function allcolor(colx)
{
if(colx==1)color1="white";
if(colx==2)color1="yellow"
for(i=1;i<=100;i++)
{
var x = document.getElementById("tbl1").rows[i].cells;
for(j=1;j<=100;j++)
{
x[j].style.background=color1;
}
}
}
function nth(str3)
{
n1=str3.indexOf("[",0);
if(n1>=0)
{
n2=str3.indexOf("]",0);
n3=str3.indexOf(",",0);
}
i=parseInt(str3.substr(n1+1,n3-n1-1));
j=parseInt(str3.substr(n3+1,n2-n3-1));
n=(i-1)*100+j;
return(n);
}
function rndnum(a,b)
{
if(ng==1)
{
tx1.value=tx1.value+'['+ a +','+ b + ']';
tx1.focus();
}
else
{
if(nshft==1)
{
str1=label1.value;
n1=str1.indexOf("]",0);
str1=str1.substr(1,n1-1);
n2=str1.indexOf(",",0);
ia1=str1.substr(0,n2);
ja1=str1.substr(n2+1);
ia2=a;
ja2=b;
iax1=Math.min(ia1,ia2);
jax1=Math.min(ja1,ja2);
iax2=Math.max(ia1,ia2);
jax2=Math.max(ja1,ja2);
label1.value='['+iax1+','+jax1+']['+iax2+','+jax2+']';
nshft=0;
}
else
{
allcolor(1);
str1='['+a+','+b+']';
label1.value=str1+str1;
}
str=label1.value;
nx=0;
n1=str.indexOf("[",nx);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
i1=parseInt(str.substr(n1+1,n3-n1-1));
j1=parseInt(str.substr(n3+1,n2-n3-1));
nx=n2+1;
n1=str.indexOf("[",nx);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
i2=parseInt(str.substr(n1+1,n3-n1-1));
j2=parseInt(str.substr(n3+1,n2-n3-1));
if(i1==0 && j1==0 && i2==0 && j2==0)
{
i1=1;
j1=1;
i2=100;
j2=100;
label1.value='['+i1+','+j1+']['+i2+','+j2+']';
selcolor(i1,j1,i2,j2)
}
if(i1==0 && i2==0 && j1>0 && j2>0)
{
i1=1;
i2=100;
j1=j1;
j2=j2;
label1.value='['+i1+','+j1+']['+i2+','+j2+']';
selcolor(i1,j1,i2,j2)
}
if(i1>0 && i2>0 && j1==0 && j2==0)
{
i1=i1;
i2=i2;
j1=1;
j2=100;
label1.value='['+i1+','+j1+']['+i2+','+j2+']';
selcolor(i1,j1,i2,j2);
}
if(i1>0 && i2>0 && j1>0 && j2>0)
{
label1.value='['+i1+','+j1+']['+i2+','+j2+']';
selcolor(i1,j1,i2,j2);
}
if(i1>0 && i2>0 && j1>0 && j2>0 && i1==i2 && j1==j2)
{
var x = document.getElementById("tbl1").rows[i1].cells;
x[j1].style.background="yellow";
n=(i1-1)*100+j1;
tx1.value=gxy[n];
if(gxy[n]=="")tx1.value=gridxy[n];
}
}
}
function cal(x)
{
if(gridxy[x]=="")return("0");
if(isNaN(gridxy[x]))
{
return("0");
}
else
{
return(gridxy[x]);
}
}
function copy1(i1,j1,i2,j2)
{
copyx=i1+','+j1+','+i2+','+j2;
}
function pastev(i1,j1,i2,j2)
{
n1=copyx.indexOf(",",0);
i11=parseInt(copyx.substr(0,n1));
n2=copyx.indexOf(",",n1+1);
j11=parseInt(copyx.substr(n1+1,n2-n1));
n3=copyx.indexOf(",",n2+1);
i21=parseInt(copyx.substr(n2+1,n3-n2));
j21=parseInt(copyx.substr(n3+1));
//alert(i11+','+j11+','+i21+','+j21);
ix1=i21-i11;
jx1=j21-j11;
ix=i2-i1;
jx=j2-j1;
ix=Math.max(ix,ix1);
jx=Math.max(jx,jx1);
ik=i1-i11;
jk=j1-j11;
for(i=i1;i<=i1+ix;i++)
{
for(j=j1;j<=j1+jx;j++)
{
np=(i-1)*100+j;
n=(i-ik-1)*100+j-jk;
gridxy[np]=gridxy[n];
gxy[np]="";
nevalpost(np);
}
}
evalpostall();
}
function paste1(i1,j1,i2,j2)
{
n1=copyx.indexOf(",",0);
i11=parseInt(copyx.substr(0,n1));
n2=copyx.indexOf(",",n1+1);
j11=parseInt(copyx.substr(n1+1,n2-n1));
n3=copyx.indexOf(",",n2+1);
i21=parseInt(copyx.substr(n2+1,n3-n2));
j21=parseInt(copyx.substr(n3+1));
ix1=i21-i11;
jx1=j21-j11;
ix=i2-i1;
jx=j2-j1;
ix=Math.max(ix,ix1);
jx=Math.max(jx,jx1);
ik=i1-i11;
jk=j1-j11;
for(i=i1;i<=i1+ix;i++)
{
for(j=j1;j<=j1+jx;j++)
{
np=(i-1)*100+j;
n=(i-ik-1)*100+j-jk;
if(gridxy[n]!="")
{
gridxy[np]=gridxy[n];
nevalpost(np);
}
}
}
for(i=i1;i<=i1+ix;i++)
{
for(j=j1;j<=j1+jx;j++)
{
np=(i-1)*100+j;
n=(i-ik-1)*100+j-jk;
if(gxy[n]!="")
{
str1=gxy[n];
str2="";
nx=0;
while (str1.indexOf("[",0)!=-1)
{
n1=str1.indexOf("[",0);
n2=str1.indexOf("]",n1+1);
n3=str1.indexOf(",",n1+1);
kstr=str1.substr(n1+1,n3-n1-1);
lstr=str1.substr(n3+1,n2-n3-1);
if(kstr.indexOf("$",0)==0){k=parseInt(kstr.substr(1));str2=str2+str1.substr(0,n1-nx)+'[$'+k+',';}
if(kstr.indexOf("$",0)!=0){k=parseInt(kstr.substr(0));k=k+ik;str2=str2+str1.substr(0,n1-nx)+'['+k+',';}
if(lstr.indexOf("$",0)==0){l=parseInt(lstr.substr(1));str2=str2+'$'+l+']';}
if(lstr.indexOf("$",0)!=0){l=parseInt(lstr.substr(0));l=l+jk;str2=str2+l+']';}
str1=str1.substr(n2+1);
}
str2=str2+str1;
gxy[np]=str2;
evalpost(np);
}
}
}
evalpostall();
}
function evalpostall()
{
for(i=1;i<=100;i++)
{
for(j=1;j<=100;j++)
{
np=(i-1)*100+j;
if(gxy[np]!="")evalpost(np);
if(gxy[np]=="" && gridxy[np]!="")nevalpost(np);
}
}
formatall();
}
function nevalpost(n)
{
str1=gridxy[n];
i=parseInt(n/100)+1;
j=n%100;
if(i>0 && j>0)
{
var x = document.getElementById("tbl1").rows[i].cells;
x[j].innerHTML=gridxy[n];
x[j].style.background="white";
}
if(isNaN(gridxy[n]))
{
x[j].align="Left";
x[j].noWrap=true;
}
else
{
x[j].align="Right";
}
}
function evalpost(n)
{
str1=gxy[n];
i=parseInt(n/100)+1;
j=n%100;
if(i>0 && j>0)
{
if(str1!="")
{
str2=str1;
if(str1.indexOf("=",0)==0)
{
str1=str1.substr(1);
while (str1.indexOf("$")!=-1)
{
str1=str1.replace("$","");
}
while (str1.indexOf("sum")!=-1)
{
n1=str1.indexOf("sum",0);
n2=str1.indexOf("(",n1);
n3=str1.indexOf(")",n1);
strx1=str1.substr(n3+1);
str1=str1.substr(0,n1)+sum1(str1.substr(n2+1,n3-n2-1))+strx1;
}
while (str1.indexOf("[",0)!=-1)
{
n1=str1.indexOf("[",0);
n2=str1.indexOf("]",n1+1);
n3=str1.indexOf(",",n1+1);
prefix=str1.substr(n1-1,1);
suffix=str1.substr(n2+1,1);
k=parseInt(str1.substr(n1+1,n3-n1-1));
l=parseInt(str1.substr(n3+1,n2-n3-1));
nx=(k-1)*100+l;
if(prefix=='"' && suffix=='"')str1=str1.substr(0,n1)+gridxy[nx]+str1.substr(n2+1);
if(prefix!='"' && suffix!='"')str1=str1.substr(0,n1)+cal(nx)+str1.substr(n2+1);
}
gridxy[n]=eval(str1);
gxy[n]=str2;
var x = document.getElementById("tbl1").rows[i].cells;
x[j].innerHTML=gridxy[n];
x[j].style.background="white";
if(isNaN(gridxy[n]))
{
x[j].align="Left";
x[j].noWrap=true;
}
else
{
x[j].align="Right";
}
}
else
{
gridxy[n]=str1;
var x =document.getElementById("tbl1").rows[i].cells;
x[j].innerHTML=gridxy[n];
x[j].style.background="white";
if(isNaN(gridxy[n]))
{
x[j].align="Left";
x[j].noWrap=true;
}
else
{
x[j].align="Right";
}
}
}
}
}
function rndnum1()
{
var str=label1.value;
n1=str.indexOf("[",0);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
a=parseInt(str.substr(n1+1,n3-n1-1));
b=parseInt(str.substr(n3+1,n2-n3-1));
var n,i,j;
i=parseInt(a);
j=parseInt(b);
if(i>0 && j>0)
{
n=(i-1)*100+j;
str1=tx1.value;
if(str1.indexOf("=",0)==0)
{
gxy[n]=tx1.value;
evalpost(n);
}
else
{
gridxy[n]=tx1.value;
gxy[n]="";
nevalpost(n);
}
}
evalpostall();
ng=0;
rndnum(i+1,j);
}
function save()
{
if(tbl2.style.zIndex==5)
{
tbl2.style.zIndex=1;
ta1.innerHTML="";
ta3.value="";
}
else
{
tbl2.style.zIndex=5
np=0;
strx="";
for(i=1;i<=10000;i++)
{
if(gxy[i]=="" && gridxy[i]!="")
{
strx1='['+(parseInt(i/100)+1)+','+i%100+']';
strx=strx+strx1+':'+gridxy[i]+'^';
if(strx.length>1000)
{
np=np+1;
ta3.value=ta3.value+strx;
strx=""
continue;
}
}
}
for(i=1;i<=10000;i++)
{
if(gxy[i]!="")
{
strx1='['+(parseInt(i/100)+1)+','+i%100+']';
strx=strx+strx1+':'+gxy[i]+'^';
if(strx.length>1000)
{
np=np+1;
ta3.value=ta3.value+strx;
strx=""
}
}
}
ta3.value=ta3.value+strx;
ta1.innerHTML="Copy and save in a notepad file";
}
}
document.writeln("<table id=tbl1 bgColor=#ffffff border=1px bordercolor=Black style=position:absolute;left:0px;top:60px;border-collapse:collapse;z-index:2;>");
for(i=0;i<=100;i++)
{
document.writeln('<tr>');
for (j =0; j <= 100; j++)
{
if(i==0)
{
if (j==0)document.writeln('<td bgcolor=#eeeeee onclick=rndnum(' + i + ',' + j + ');></td>');
if (j>0)document.writeln('<td bgcolor=#eeeeee onclick=rndnum(' + i + ',' + j + ');>'+'COLUMN' + j +'</td>');
}
if(j==0 && i>0)document.writeln('<td bgcolor=#eeeeee onclick=rndnum(' + i + ',' + j + ');>'+'ROW' + i +'</td>');
if(i>0 && j>0)
{
document.writeln('<td onclick=rndnum(' + i + ',' + j + ');>' + '</td>');
}
}
document.writeln('</tr>');
}
document.writeln("</table>");
document.write("<table width=500px id=tbl2 bgcolor=#fefefe border=1px style=position:fixed;left:100px;top:100px;border-collapse:collapse;z-index:1;><tr><td bgcolor=yellow>Copy and save in Notepad</td></tr><tr><td>");
document.write("<textarea id=ta3 style='width:500px;height:300px'></textarea></td></tr><tr><td><p id=ta1></p></td></tr></table>");
document.write("<table width=200px id=tbl3 bgcolor=#fefefe border=1px style=position:fixed;left:100px;top:100px;border-collapse:collapse;z-index:1;><tr><td>Type no of decimal places</td></tr><tr><td>");
document.write("<input type=text id=fmt1 name=fmt1></td></tr><tr><td><input type=button id=fmt2 value='ok' onclick='format2();'></td></tr></table>");
function format1()
{
tbl3.style.zIndex=5;
}
function format2()
{
fxy=fxy+label1.value+fmt1.value+'^';
tbl3.style.zIndex=1;
formatall();
}
function nformat()
{
fxy="";
evalpostall();
}
function formatall()
{
if(fxy!="")
{
lnx=fxy.length;
nx1=0;
while(nx1<lnx-2)
{
nx2=fxy.indexOf("^",nx1);
str=fxy.substr(nx1,nx2-nx1)
nx=0;
n1=str.indexOf("[",nx);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
i1=parseInt(str.substr(n1+1,n3-n1-1));
j1=parseInt(str.substr(n3+1,n2-n3-1));
nx=n2+1;
n1=str.indexOf("[",nx);
n2=str.indexOf("]",n1+1);
n3=str.indexOf(",",n1+1);
i2=parseInt(str.substr(n1+1,n3-n1-1));
j2=parseInt(str.substr(n3+1,n2-n3-1));
ndx=parseInt(str.substr(n2+1));
for(i=i1;i<=i2;i++)
{
for(j=j1;j<=j2;j++)
{
n=(i-1)*100+j;
if(gridxy[n]!="")
{
if(isNaN(gridxy[n])==false)
{
var x =document.getElementById("tbl1").rows[i].cells;
val1=Math.pow(10,ndx);
str1=Math.round(gridxy[n]*val1)/val1;
str1=str1.toString(10);
n4=str1.indexOf(".",0);
if(n4<0)
{
str1=str1+'.';
for(i1=1;i1<=ndx;i1++)
{
str1=str1+'0';
}
}
if(n4>=0)
{
str2=str1.substr(n4+1);
n5=str2.length;
if(n5<ndx)
{
n6=ndx-n5;
for(i1=1;i1<=n6;i1++)
{
str1=str1+'0';
}
}
}
x[j].innerHTML=str1;
}
}
}
}
nx1=nx2+1;
}
}
}
function dele1()
{
for(i=i1;i<=i2;i++)
{
for(j=j1;j<=j2;j++)
{
n=(i-1)*100+j;
gxy[n]="";
gridxy[n]="";
tx1.value="";
var x =document.getElementById("tbl1").rows[i].cells;
x[j].innerHTML=gridxy[n];
//evalpost(n);
}
}
evalpostall();
}
function load2()
{
str1=ta3.value;
while(str1.length>0)
{
n1=str1.indexOf("^",0);
str5=str1.substr(n1+1);
str2=str1.substr(0,n1);
n2=str2.indexOf(":",0);
str3=str2.substr(0,n2);
np=nth(str3);
str4=str2.substr(n2+2);
if(str4.substr(0,1)!="="){gridxy[np]=str4;nevalpost(np);}
if(str4.substr(0,1)=="="){gxy[np]=str4;evalpost(np);}
str1=str5;
}
ta3.value="";
ta1.innerHTML="";
tbl2.style.zIndex=1;
}
function load1()
{
if(tbl2.style.zIndex==5)
{
ta1.innerHTML=""
tbl2.style.zIndex=1;
}
else
{
tbl2.style.zIndex=5
ta1.innerHTML="<input type=button value='ok' onclick='load2();'>";
}
}
</SCRIPT>
</head>
<BODY>
<table id=text1 style=position:fixed;top:0;left:0;z-index:3; bgcolor=yellow><tr><td>
<input name=label1 id=label1 onfocus="ng=0;" bgcolor=yellow type=text></td><td>
<input type=text id=tx1 name=tx1 onfocus="ng=1;" onkeypress=displayresult(); style=width:500px;height:15px; accesskey=i></td><td>
<input type=button id=go value='go' onclick='rndnum1();'></td><td>
<a href='../webxl/manualforwebxl.pdf' target=_blank>Help</a></td></tr></table><table style=position:fixed;top:25px;left:0px;z-index:3; bgcolor=yellow><tr><td>
<input type=button value='=' onclick="tx1.value=tx1.value+'=';tx1.focus();" accesskey==></td><td>
<input type=button value='+' onclick="tx1.value=tx1.value+'+';tx1.focus();" accesskey=t></td><td>
<input type=button value='-' onclick="tx1.value=tx1.value+'-';tx1.focus();" accesskey=-></td><td>
<input type=button value='*' onclick="tx1.value=tx1.value+'*';tx1.focus();" accesskey=*></td><td>
<input type=button value='/' onclick="tx1.value=tx1.value+'/';tx1.focus();" accesskey=/></td><td>
<input type=button value='sum' onclick="tx1.value=tx1.value+'sum';tx1.focus();" accesskey=2></td><td>
<input type=button value='pow' onclick="tx1.value=tx1.value+'Math.pow(';tx1.focus();" accesskey=6></td><td>
<input type=button value='sqrt' onclick="tx1.value=tx1.value+'Math.sqrt(';tx1.focus();" accesskey=q></td><td>
<input type=button value='(' onclick="tx1.value=tx1.value+'(';tx1.focus();" accesskey=9></td><td>
<input type=button value=')' onclick="tx1.value=tx1.value+')';tx1.focus();" accesskey=0></td><td>
<input type=button id=sft value='Range' onclick="nshft=1;" accesskey=r></td><td>
<input type=button id=del1 value='Del' onclick='dele1();' accesskey=x></td><td>
<input type=button id=fmt value='Fmt' onclick='format1();' accesskey=f></td><td>
<input type=button id=nfmt value='NFmt' onclick='nformat();' accesskey=n></td><td>
<input type=button value='Copy' onclick='copy1(i1,j1,i2,j2);' accesskey=c></td><td>
<input type=button value='Paste' onclick='paste1(i1,j1,i2,j2);' accesskey=v></td><td>
<input type=button value='Pasv' onclick='pastev(i1,j1,i2,j2);' accesskey=p></td><td>
<input type=button value='Save' onclick='save();' accesskey=s></td><td>
<input type=button value='load' onclick='load1();' accesskey=l></td>
</tr></table>
</BODY>
</HTML>
I would recommend jquery sheet - this works as a javascript
加载中,请稍侯......
精彩评论