jsp 转 excel

来源：互联网发布：海岛奇兵兵种等级数据编辑：程序博客网时间：2024/06/05 19:57

1、用poi等在服务器生成excel然后下载下来

2、用JS生成excel

3、用jsp表格来生成excel

==============================================

例JS

/**

* 生成Excel文件并保存

* @param {String} 要生成的数据所在的表单名称

* @param {String} 要生成的数据所在的table名称

function exportexcel(formName,tableName){

var oXL = new ActiveXObject("Excel.Application");

oXL.Visible = true;

var oWB = oXL.Workbooks.Add();

var oSheet = oWB.ActiveSheet;

var div1=document.all.item(formName);

var table1=div1.all.item(tableName);

var table=new Array();

var retArr = getTableRowsCols(table1);

var rlen=retArr[0];

var clen=retArr[1];

var flagTable=new Array(rlen);

for(var i=0;i<rlen;i++){

flagTable[i]=new Array(clen);

for(var j=0;j<clen;j++){

flagTable[i][j]=0;

}

var rowBegin=1, colBegin=1;//导出的表格的起始位置

var c1=oSheet.Cells(rowBegin,colBegin);

var c2=oSheet.Cells(rlen+rowBegin-1,clen+colBegin-1);

oSheet.Range(c1,c2).VerticalAlignment = -4108;

oSheet.Range(c1,c2).HorizontalAlignment = -4108;

exportTable(oSheet, flagTable, table1, rowBegin, colBegin);

oSheet.Range(oSheet.Cells(rowBegin,colBegin),oSheet.Cells(rlen+rowBegin-1,clen+colBegin-1)).EntireColumn.AutoFit();

oXL.UserControl = true;

oXL.Quit();

}

/**

* 返回当前表格的行数和列数

* 被exportexcel调用

* @param {Object} 表格对象

* @param {Array} 数组retArr, retArr[0], 行数；retArr[1], 列数

function getTableRowsCols(objTable)

{

var rowCnt=0, colCnt=0;

for (var i=0; i<objTable.rows.length; i++) {

var row = objTable.rows[i];//当前行

var rowRows=1, rowCols=0;//当前行的初始行数和列数

for (var j=0; j<row.cells.length; j++) {

var cell = row.cells[j];//当前单元格

if (cell.firstChild !=null && cell.firstChild.tagName == "TABLE") {

var inRetArr = getTableRowsCols(cell.firstChild);

rowCols +=inRetArr [1];

rowRows = rowRows<inRetArr[0] ? inRetArr[0] : rowRows;

} else {//不是表格

rowCols += parseInt(cell.colSpan);

rowRows = rowRows<parseInt(cell.rowSpan) ? parseInt(cell.rowSpan) : rowRows;

}

colCnt = colCnt<rowCols ? rowCols : colCnt;

rowCnt += rowRows;

}

var retArr = new Array();

retArr[0] = rowCnt;

retArr[1] = colCnt;

return retArr;

}

/**

* 导出表格

* 被exportexcel调用

function exportTable(oSheet, flagTable, objTable, rowBegin, colBegin){

var flagRow=0, flagCol=0;//跟踪当前的表示表中的相对位置

for(var i=0; i<objTable.rows.length; i++) {

var row = objTable.rows[i];//当前行

flagCol = 0;

var subTableRows = 1;//当前行若有表格，则记录其中最大的行数

for (var j=0; j<row.cells.length; j++) {

while (flagTable[rowBegin-1+flagRow][colBegin-1+flagCol]==1) {

flagCol++;

}

var cell=row.cells[j];//当前单元格

if (cell.firstChild !=null && cell.firstChild.tagName == "TABLE") {

var retArrRows = getTableRowsCols(cell.firstChild)[0];

subTableRows = subTableRows<retArrRows ? retArrRows : subTableRows;

exportTable(oSheet, flagTable, cell.firstChild, rowBegin+flagRow, colBegin+flagCol);

} else {

oSheet.Cells(rowBegin+flagRow, colBegin+flagCol).Font.Bold=1;

var rs=parseInt(cell.rowSpan);

var cs=parseInt(cell.colSpan);

oSheet.Cells(rowBegin+flagRow, colBegin+flagCol).NumberFormatLocal="@";

oSheet.Cells(rowBegin+flagRow, colBegin+flagCol).Value = cell.innerText;

oSheet.Range(oSheet.Cells(rowBegin+flagRow, colBegin+flagCol),oSheet.Cells(rowBegin+flagRow+rs-1,colBegin+flagCol+cs-1)).MergeCells = 1;

//填写当前单元格在标志表格中的标志

for(var k=0; k<rs; k++) {

for(var l=0; l<cs; l++) {

flagTable[rowBegin-1+flagRow+k][colBegin-1+flagCol+l]=1;

}

flagCol =flagCol + cs - 1;//加速标志表格的列的移动，因为当前表格已经标识了cs个标志单元

}

flagRow += subTableRows;

}

==============================================

例jsp

jsp 页面downExcel.jsp

<%@ page contentType="application/vnd.ms-excel;charset=utf-8"%>
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
<!-- 以上这行设定本网页为excel格式的网页 -->
<html>
<head>
<meta http-equiv="Content-Type" content="application/msexcel; charset=utf-8">
<title>Excel档案呈现方式</title>
</head>
<body>
<table border="1" id="the-table" width="100%" >
<thead>
<tr>
<th width="45px">
应收帐款日期</th>
<th width="100px">
客户名称</th>
<th width="100px">
销售合同号</th>
<th width="100px">
借方本币</th>
<th width="100px">
贷方本币</th>
</tr>
</thead>
<tbody>
<c:forEach var="record" items="${pageinfo.records}" varStatus="i">
<tr>
<td>
<c:out value="${record[0]}" />
</td>
<td>
<c:out value="${record[1]}" />
</td>
<td>
<c:out value="${record[2]}" />
</td>
<td>
<c:out value="${record[3]}" />
</td>
<td>
<c:out value="${record[4]}" />
</td>
</tr>
</c:forEach>
</tbody>
</table>
</body>
</html>

java代码

  public  void downLoad(String filePath, HttpServletResponse response, boolean isOnLine) throws Exception {  File f = new File(filePath);  if (!f.exists()) {  response.sendError(404, "File not found!");  return;  }  BufferedInputStream br = new BufferedInputStream(new FileInputStream(f));  byte[] buf = new byte[2048];  int len = 0;  response.reset(); // 非常重要  if (isOnLine) { // 在线打开方式  URL u = new URL("file:///" + filePath);  response.setContentType(u.openConnection().getContentType());  response.setHeader("Content-Disposition", "inline; filename=" + URLEncoder.encode(f.getName(), "UTF-8"));  // 文件名应该编码成UTF-8  } else { // 纯下载方式  response.setContentType("application/x-msdownload");  response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(f.getName(), "UTF-8"));  }  OutputStream out=null;  try {  out = response.getOutputStream();  while ((len = br.read(buf)) > 0) {  out.write(buf, 0, len);  }  } catch (Exception e) {  //e.printStackTrace();  }  finally{  try {  br.close();  out.close();  } catch (Exception e) {  }  }  }

/******************

若在是用struts2 可以只接设置下response的属性便可

<action name="" method="" class="">

<!--要生成ecxel的jsp的页面路径--><result>/downExcel.jsp</result>

</action>

******************/