How can I make a web-based "table" select like a spreadsheet? (rectangular area vs row-wrap selection)
I want to make a table that displays on a webpage, but one requirement is to make it easy to copy and paste into a spreadsheet. Normal HTML tables selection behavior is obviously different from how a spreadsheet like Excel selects -- when you select multiple rows it wraps around instead of selecting a rectangular area. Is there any way to make HTML table behave like a sprea开发者_开发问答dsheet in this regard, or is the only way to resort to a Flash table or something similar?
Google Docs manages it, so it is possible. You'll need to fake it. Something like the following:
- Prevent normal text selection
- Use mouse events to detect the first cell the user selected and the most recent cell entered, and apply selection-like styling to the rectangle of cells with those cells as opposite corners
- The tricky part is allowing the user to copy the fake selection, since when the user does a copy (either via the keyboard or the browser edit or context menus), the real selection will be empty. What you could do is copy the selected cells into a table hidden off-screen (using a large negative value for CSS
left, for example) when the selection changes and have the real selection always surrounding the off-screen table.
It's not a real solution, but I think it should be mentioned:
Firefox supports this kind of selection on a HTML table by holding down the Control key while click-dragging the mouse.
No easy solution comes into my mind. You could...
- Make rectangular "fake selection" over the table using mousedown/up events.
- With javascript, parse values of selected cells into CSV structure.
- Send the CSV data to server via ajax and have the server send it back to you as CSV file with proper HTTP headers.
- Now you can import the downloaded CSV file into Excel.
Ugly? Oh yeah.
I would hack the Jquery Plugin Tablesorter:
http://tablesorter.com/docs/
Here is a script that implements the advice in these answers: https://github.com/jablko/cellect
- When the user mousedowns in a cell and moves to another cell, it draws a rectangular selection.
- When the user mouseups it converts the selection to tab separated values and puts that in an offscreen textarea, so "Copy" in the Edit menu or pressing Ctrl-C will copy it to a spreadsheet.
- If the user leaves the table or stays in the same cell then select behavior is as normal.
- Pressing Shift while clicking expands the selection.
- Users can select rows, columns, or the whole table by double clicking.
- If the user right clicks on the selection, they can choose "Copy" from the context menu, thanks to this trick: Allow copy current selection (vs. deselect) when right click outside the selection?
- It ignores clicks if the Ctrl key is pressed, so as not to interfere with Firefox default Ctrl select behavior.
There are two versions, one that uses jQuery and one that doesn't.
It's minimal, no work has been done on interaction with colspans or rowspans, or interaction with nested tables.
This may help and I developed in javascript and 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:
12.Solution of simultaneous equations in spreadsheet:
13.Javascript programming within spreadsheet:
14.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>
加载中,请稍侯......
精彩评论