怎么从网页导出execl js
展开全部
1、首先创建此jsp页面：（功能为导出页面数据到Excel）
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML><HEAD>
<TITLE>将页面中指定表格的数据导入到Excel中</TITLE>
<SCRIPT LANGUAGE="javascript">
function AutomateExcel1(){var oXL = new ActiveXObject("Excel.Application"); //创建应该对象var oWB = oXL.Workbooks.Add();//新建一个Excel工作簿var oSheet = oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表//指定要写入的数据源的idvar table = document.all.data;var hang = table.rows.length;//取数据源行数var lie = table.rows(0).cells.length;//取数据源列数
// Add table headers going cell by cell.for (i=0;i<hang;i++){//在Excel中写行for (j=0;j<lie;j++){//在Excel中写列//定义格式oSheet.Cells(i+1,j+1).NumberFormatLocal = "@";//!!!!!!!上面这一句是将单元格的格式定义为文本oSheet.Cells(i+1,j+1).Font.Bold = true;//加粗oSheet.Cells(i+1,j+1).Font.Size = 10;//字体大小oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;//向单元格写入值}}
oXL.Visible = true;oXL.UserControl = true;}
</SCRIPT></HEAD><SCRIPT LANGUAGE="javascript">
function autoExcel(){ alert("进入函数");// Start Excel and get Application object.var oXL = new ActiveXObject("Excel.Application");
oXL.Visible = true;
// Get a new workbook.var oWB = oXL.Workbooks.Add();var oSheet = oWB.ActiveSheet;
// Add table headers going cell by cell.oSheet.Cells(1, 1).Value = "First Name";oSheet.Cells(1, 2).Value = "Last Name";oSheet.Cells(1, 3).Value = "Full Name";oSheet.Cells(1, 4).Value = "Salary";
// Format A1:D1 as bold, vertical alignment = center.oSheet.Range("A1", "D1").Font.Bold = true;oSheet.Range("A1", "D1").VerticalAlignment = -4108; //-4108 xlVAlignCenteroSheet.Range("A1", "D1")//ALT+/显示// Create an array to set multiple values at once.
// Fill A2:B6 with an array of values (from VBScript).oSheet.Range("A2", "B6").Value = CreateNamesArray();
// Fill C2:C6 with a relative formula (=A2 & " " & B2).var oRng = oSheet.Range("C2", "C6");//oRng.Formula = "=A2 & " " & B2";oRng.Formula = "=RAND()*100000";// Fill D2:D6 with a formula(=RAND()*100000) and apply format.oRng = oSheet.Range("D2", "D6");oRng.Formula = "=RAND()*100000";oRng.NumberFormat = "$0.00";
// AutoFit columns A:D.oRng = oSheet.Range("A1", "D1");oRng.EntireColumn.AutoFit();
// Manipulate a variable number of columns for Quarterly Sales Data.DispalyQuarterlySales(oSheet);
// Make sure Excel is visible and give the user control// of Excel's lifetime.oXL.Visible = true;oXL.UserControl = true;}</script> <BODY><table border="0" width="300" id="data" bgcolor="black" cellspacing="1"><tr bgcolor="white"><td>编号</td><td>姓名</td><td>年龄</td><td>性别</td></tr><tr bgcolor="white"><td>0001</td><td>张三</td><td>22</td><td>女</td></tr><tr bgcolor="white"><td>0002</td><td>李四</td><td>23</td><td>男</td></tr></table><input type="button" name="out_excel" onclick="autoExcel()" value="导出到excel"><input type="button" name="out_excel" onclick="AutomateExcel1()" value="导出到excel方法二"></BODY></HTML>
2、下面的这个是导入Excel数据的源码：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html xmlns="http://www.w3.org/1999/xhtml" ><head> <title>Untitled Page</title></head><script language="javascript" type="text/javascript">function importXLS(fileName){ objCon = new ActiveXObject("ADODB.Connection"); objCon.Provider = "Microsoft.Jet.OLEDB.4.0"; objCon.ConnectionString = "Data Source=" + fileName + ";Extended Properties=Excel 8.0;"; objCon.CursorLocation = 1; objCon.Open; var strQuery; //Get the SheetName var strSheetName = "Sheet1$"; var rsTemp = new ActiveXObject("ADODB.Recordset"); rsTemp = objCon.OpenSchema(20); if(!rsTemp.EOF) strSheetName = rsTemp.Fields("Table_Name").Value; rsTemp = null; rsExcel = new ActiveXObject("ADODB.Recordset"); strQuery = "SELECT * FROM [" + strSheetName + "]"; rsExcel.ActiveConnection = objCon; rsExcel.Open(strQuery); while(!rsExcel.EOF) { for(i = 0;i<rsExcel.Fields.Count;++i) { alert(rsExcel.Fields(i).value); } rsExcel.MoveNext; } // Close the connection and dispose the file objCon.Close; objCon =null; rsExcel = null;}</script></head><body><input type="file" id="f" /><input type="button" id="b" value="import" onclick="if(f.value=='')alert('请选择xls文件');else importXLS
(f.value)" /></body></html>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML><HEAD>
<TITLE>将页面中指定表格的数据导入到Excel中</TITLE>
<SCRIPT LANGUAGE="javascript">
function AutomateExcel1(){var oXL = new ActiveXObject("Excel.Application"); //创建应该对象var oWB = oXL.Workbooks.Add();//新建一个Excel工作簿var oSheet = oWB.ActiveSheet;//指定要写入内容的工作表为活动工作表//指定要写入的数据源的idvar table = document.all.data;var hang = table.rows.length;//取数据源行数var lie = table.rows(0).cells.length;//取数据源列数
// Add table headers going cell by cell.for (i=0;i<hang;i++){//在Excel中写行for (j=0;j<lie;j++){//在Excel中写列//定义格式oSheet.Cells(i+1,j+1).NumberFormatLocal = "@";//!!!!!!!上面这一句是将单元格的格式定义为文本oSheet.Cells(i+1,j+1).Font.Bold = true;//加粗oSheet.Cells(i+1,j+1).Font.Size = 10;//字体大小oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;//向单元格写入值}}
oXL.Visible = true;oXL.UserControl = true;}
</SCRIPT></HEAD><SCRIPT LANGUAGE="javascript">
function autoExcel(){ alert("进入函数");// Start Excel and get Application object.var oXL = new ActiveXObject("Excel.Application");
oXL.Visible = true;
// Get a new workbook.var oWB = oXL.Workbooks.Add();var oSheet = oWB.ActiveSheet;
// Add table headers going cell by cell.oSheet.Cells(1, 1).Value = "First Name";oSheet.Cells(1, 2).Value = "Last Name";oSheet.Cells(1, 3).Value = "Full Name";oSheet.Cells(1, 4).Value = "Salary";
// Format A1:D1 as bold, vertical alignment = center.oSheet.Range("A1", "D1").Font.Bold = true;oSheet.Range("A1", "D1").VerticalAlignment = -4108; //-4108 xlVAlignCenteroSheet.Range("A1", "D1")//ALT+/显示// Create an array to set multiple values at once.
// Fill A2:B6 with an array of values (from VBScript).oSheet.Range("A2", "B6").Value = CreateNamesArray();
// Fill C2:C6 with a relative formula (=A2 & " " & B2).var oRng = oSheet.Range("C2", "C6");//oRng.Formula = "=A2 & " " & B2";oRng.Formula = "=RAND()*100000";// Fill D2:D6 with a formula(=RAND()*100000) and apply format.oRng = oSheet.Range("D2", "D6");oRng.Formula = "=RAND()*100000";oRng.NumberFormat = "$0.00";
// AutoFit columns A:D.oRng = oSheet.Range("A1", "D1");oRng.EntireColumn.AutoFit();
// Manipulate a variable number of columns for Quarterly Sales Data.DispalyQuarterlySales(oSheet);
// Make sure Excel is visible and give the user control// of Excel's lifetime.oXL.Visible = true;oXL.UserControl = true;}</script> <BODY><table border="0" width="300" id="data" bgcolor="black" cellspacing="1"><tr bgcolor="white"><td>编号</td><td>姓名</td><td>年龄</td><td>性别</td></tr><tr bgcolor="white"><td>0001</td><td>张三</td><td>22</td><td>女</td></tr><tr bgcolor="white"><td>0002</td><td>李四</td><td>23</td><td>男</td></tr></table><input type="button" name="out_excel" onclick="autoExcel()" value="导出到excel"><input type="button" name="out_excel" onclick="AutomateExcel1()" value="导出到excel方法二"></BODY></HTML>
2、下面的这个是导入Excel数据的源码：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html xmlns="http://www.w3.org/1999/xhtml" ><head> <title>Untitled Page</title></head><script language="javascript" type="text/javascript">function importXLS(fileName){ objCon = new ActiveXObject("ADODB.Connection"); objCon.Provider = "Microsoft.Jet.OLEDB.4.0"; objCon.ConnectionString = "Data Source=" + fileName + ";Extended Properties=Excel 8.0;"; objCon.CursorLocation = 1; objCon.Open; var strQuery; //Get the SheetName var strSheetName = "Sheet1$"; var rsTemp = new ActiveXObject("ADODB.Recordset"); rsTemp = objCon.OpenSchema(20); if(!rsTemp.EOF) strSheetName = rsTemp.Fields("Table_Name").Value; rsTemp = null; rsExcel = new ActiveXObject("ADODB.Recordset"); strQuery = "SELECT * FROM [" + strSheetName + "]"; rsExcel.ActiveConnection = objCon; rsExcel.Open(strQuery); while(!rsExcel.EOF) { for(i = 0;i<rsExcel.Fields.Count;++i) { alert(rsExcel.Fields(i).value); } rsExcel.MoveNext; } // Close the connection and dispose the file objCon.Close; objCon =null; rsExcel = null;}</script></head><body><input type="file" id="f" /><input type="button" id="b" value="import" onclick="if(f.value=='')alert('请选择xls文件');else importXLS
(f.value)" /></body></html>
本回答由提问者推荐
已赞过
已踩过<
评论
收起
你对这个回答的评价是？
推荐律师服务：
若未解决您的问题，请您详细描述您的问题，通过百度律临进行免费专业咨询
