作者：乔山办公网日期：
返回目录：excel表格制作
一、 直接拷贝整个表格到EXCEL中二、 通过遍历表格，给EXCEL中相应的单元格赋值。三、 把表格中的内容提取出来，e68a84e8a2ade799bee5baa6e79fa5e98193364利用IE的另存为.csv的格式。各方法的好处：1. 直接拷贝表格，能够保留表格中的原有的格式，比如，列，行的合并，对齐方式，底色等等，2. 通过遍历表格，比较灵活，可以遍历表格某些需要部分的内容。3. 利用IE的另存为，不用创建ActiveXObject对象，可以处理表格合并方面的问题。各方法的缺点：1. 可能弹出脚本错误：Automation不能创建对象。解决方法：启用IE安全设置中的：对没有标记为安全的ActiveX控件进行初始化和脚本运行。由于整个表格复制到EXCEL中，给表格加个标题，并加入到EXCEL中可能会遇到麻烦。解决方法：首先在表格中加入第一行<tr><td colspan="x" align="center">&</td></tr>X，表示整个表格的列数，复制完整个表格后,加如下代码，oSheet为当前活动的sheet.oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, x)).value = "表格标题";//设置标题oSheet.Rows(1).Font.Size = 16; //设置文字大小oSheet.Rows(1).Font.Name = "宋体";//设置文字字体注：以下属性我没用着，可能有用，也可能会报错oSheet.Range(oSheet.Cells(1,1), oSheet.Cells(1,14)).mergecells=true; //合并单元格oSheet.Range(oSheet.Cells(1,1), oSheet.Cells(1,14)).Interior.ColorIndex=6;//设置底色?oSheet.Range(oSheet.Cells(1,1), oSheet.Cells(1,14)).Font.ColorIndex=5;//设置字体色?oSheet.Rows(1).RowHeight=20; //设置列高oSheet.Cells(iRow,iCol).Halignment=’2’//设置字体居中2. 可能弹出脚本错误：Automation不能创建对象（解决方法如上）。表格内容写入到EXCEL中无表格线（未解决）且有单元格合并时会有问题，解决方法：合并单元格后再写数据。oSheet.Range(oSheet.Cells(1,1), oSheet.Cells(1,14)).mergecells=true; //合并单元格3. 表格内容写入到EXCEL中无表格线（未解决）表格格式复杂时，会有问题，（rowspan>1 or colspan>1）,解决方法：一般都是表头格式比较复杂，可先把表头写死，然后再循环写其他数据。代码如下：<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD> <BODY>
<table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0"><tr><td colspan="5" align="center">WEB页面导出为EXCEL文档的方法</td></tr><tr><td>列标题1</td><td>列标题2</td><td>列标题3</td><td>列标题4</td><td>列标题5</td></tr><tr><td>aaa</td><td>bbb</td><td>ccc</td><td>ddd</td><td>eee</td></tr><tr><td>AAA</td><td>BBB</td><td>CCC</td><td>DDD</td><td>EEE</td></tr><tr><td>FFF</td><td>GGG</td><td>HHH</td><td>III</td><td>JJJ</td></tr></table>
<input type="button" onclick="javascript:method1('tableExcel');" value="第一种方法导入到EXCEL"><input type="button" onclick="javascript:method2('tableExcel');" value="第二种方法导入到EXCEL"><input type="button" onclick="javascript:getXlsFromTbl('tableExcel',null);" value="第三种方法导入到EXCEL">
<SCRIPT LANGUAGE="javascript">function method1(tableid)
{//整个表格拷贝到EXCEL中var curTbl = document.getElementById(tableid);var oXL = new ActiveXObject("Excel.Application");//创建AX对象excelvar oWB = oXL.Workbooks.Add();//获取workbook对象var oSheet = oWB.ActiveSheet;//激活当前sheetvar sel = document.body.createTextRange();sel.moveToElementText(curTbl);//把表格中的内容移到TextRange中sel.select();//全选TextRange中内容sel.execCommand("Copy");//复制TextRange中内容oSheet.Paste();//粘贴到活动的EXCEL中oXL.Visible = true;//设置excel可见属性}function method2(tableid) //读取表格中每个单元到EXCEL中
{var curTbl = document.getElementById(tableid);var oXL = new ActiveXObject("Excel.Application");//创建AX对象excelvar oWB = oXL.Workbooks.Add();//获取workbook对象var oSheet = oWB.ActiveSheet;//激活当前sheetvar Lenr = curTbl.rows.length;//取得表格行数for (i = 0; i < Lenr; i++){var Lenc = curTbl.rows(i).cells.length;//取得每行的列数for (j = 0; j < Lenc; j++){oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;//赋值}}oXL.Visible = true;//设置excel可见属性}function getXlsFromTbl(inTblId, inWindow) {try {var allStr = "";var curStr = "";//alert("getXlsFromTbl");if (inTblId != null && inTblId != "" && inTblId != "null") {curStr = getTblData(inTblId, inWindow);}if (curStr != null) {allStr += curStr;}else {alert("你要导出的表不存在！");return;}var fileName = getExcelFileName();doFileExport(fileName, allStr);}catch(e) {alert("导出发生异常:" + e.name + "->" + e.description + "!");}}
//---------------------------------------------
function getTblData(inTbl, inWindow) {var rows = 0;//alert("getTblData is " + inWindow);var tblDocument = document;if (!!inWindow && inWindow != "") {if (!document.all(inWindow)) {return null;}else {tblDocument = eval(inWindow).document;}}var curTbl = tblDocument.getElementById(inTbl);var outStr = "";if (curTbl != null) {for (var j = 0; j < curTbl.rows.length; j++) {//alert("j is " + j);for (var i = 0; i < curTbl.rows[j].cells.length; i++) {//alert("i is " + i);if (i == 0 && rows > 0) {outStr += " ";rows -= 1;}outStr += curTbl.rows[j].cells[i].innerText + " ";if (curTbl.rows[j].cells[i].colSpan > 1) {for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) {outStr += " ";}}if (i == 0) {if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) {rows = curTbl.rows[j].cells[i].rowSpan - 1;}}}outStr += " ";}}else {outStr = null;alert(inTbl + "不存在!");}return outStr;}function getExcelFileName() {var d = new Date();var curYear = d.getYear();var curMonth = "" + (d.getMonth() + 1);var curDate = "" + d.getDate();var curHour = "" + d.getHours();var curMinute = "" + d.getMinutes();var curSecond = "" + d.getSeconds();if (curMonth.length == 1) {curMonth = "0" + curMonth;}if (curDate.length == 1) {curDate = "0" + curDate;}if (curHour.length == 1) {curHour = "0" + curHour;}if (curMinute.length == 1) {curMinute = "0" + curMinute;}if (curSecond.length == 1) {curSecond = "0" + curSecond;}var fileName = "leo_zhang" + "_" + curYear + curMonth + curDate + "_"+ curHour + curMinute + curSecond + ".csv";//alert(fileName);return fileName;}function doFileExport(inName, inStr) {var xlsWin = null;if (!!document.all("glbHideFrm")) {xlsWin = glbHideFrm;}else {var width = 6;var height = 4;var openPara = "left=" + (window.screen.width / 2 - width / 2)+ ",top=" + (window.screen.height / 2 - height / 2)+ ",scrollbars=no,width=" + width + ",height=" + height;xlsWin = window.open("", "_blank", openPara);}xlsWin.document.write(inStr);xlsWin.document.close();xlsWin.document.execCommand('Saveas', true, inName);xlsWin.close();}
只要网页能显示图片，就能导出到EXCEL
<script>
var idTmr = "";
function copy(tabid){
var oControlRange = document.body.createControlRange();
oControlRange.add(tabid,0);
oControlRange.select();
document.execCommand("Copy");
}
function toExcel(tabid){
copy(tabid);
try{
var xls = new ActiveXObject( "Excel.Application" );
}catch(e){
alert( "Excel没有安装或浏览器7a64e59b9ee7ad94364设置不正确.请启用所有Active控件和插件");
return false;
}
xls.visible = true;
var xlBook = xls.Workbooks.Add;
var xlsheet = xlBook.Worksheets(1);
xlBook.Worksheets(1).Activate;
for(var i=0;i<tabid.rows(0).cells.length;i++){
xlsheet.Columns(i+1).ColumnWidth=15;
}
xlsheet.Paste;
xls=null;
idTmr = window.setInterval("Cleanup();",1);
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
</script>
<input type="button" value="导入excel" onclick="toExcel(t1)"> <!--把table的ID传过去就好了-->
<table id="t1">
你的table
</table>
//在IE浏览器里工具-》internet选项-》安全-》自定义级别-》把你禁用的都打开
什么是HTML格式的EXCEL呢？
你打开一个Excel文件，另存为Html格式，就知道了
Response.ContentType 是告诉浏览知器，服务器传递过来的内容是什么格式
这个值默认道是text/html
这个要在所有的客户机上设置浏览器权限，比如IE的修改方法：
进入Internet属性＝》安全＝》自定义级别，把ActiveX控件和插件下的所有选项都改成启专用
因为js是客户端的，所以服务器不可能控制属
我一般是用服务器生成html格式的Excel，然后设置
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "inline;filename=abc.xls");
这样来下载excel，而且这样不用修改客户端的ie设置
只要网页能显示图片，就能导出到EXCEL
<script>
var idTmr = "";
function copy(tabid){
var oControlRange = document.body.createControlRange();
oControlRange.add(tabid,0);
oControlRange.select();
document.execCommand("Copy");
}
function toExcel(tabid){
copy(tabid);
try{
var xls = new ActiveXObject( "Excel.Application" );
}catch(e){
alert( "Excel没有安装或浏览器设置不正确.请启用所有Active控件和插件");
return false;
}
xls.visible = true;
var xlBook = xls.Workbooks.Add;
var xlsheet = xlBook.Worksheets(1);
xlBook.Worksheets(1).Activate;
for(var i=0;i<tabid.rows(0).cells.length;i++){
xlsheet.Columns(i+1).ColumnWidth=15;
}
xlsheet.Paste;
xls=null;
idTmr = window.setInterval("Cleanup();",1);
}
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
</script>
<input type="button" value="导入excel" onclick="toExcel(t1)"> <!--把table的ID传过去就好了-->
<table id="t1">
你的table
</table>
//在IE浏览器里工具-》internet选项-》安全-》自定义级e68a84e79fa5e98193336别-》把你禁用的都打开
请参考